MySQL JDBC Connection Example

MySQL JDBC Connection Example

MySQL  

In one of our earlier tutorial we have seen about PostgreSQL database connection with simple JSP login page example. In this tutorial we will see about MySQL JDBC connection with example. To test this code we assume already MySQL database has been installed properly and its in running state. Also we have create a test database to verify the code. 
Any Java IDE like Eclipse, IntelliJ  etc., can be used or even we can test from command prompt. 
Next we need MySQL JDBC jar file or MySQL connector jar file. If you are using any IDE then downloaded jar file can be added to your libraries.

In below example we will see how to create MySQL DB connection, how to use simple select query and finally closing the connection. 

public class TestConnection {
 
 public static void main(String[] args) {
  
  // Creating DB connection
  Connection conn = createConnection();
  
  // Simple fetch query
  fetchEmailId(conn);
  
  // Closing connection
  closeConnection(conn);
 }

 public static Connection createConnection(){
  
  Connection conn = null;
   
  try {
   Class.forName("com.mysql.jdbc.Driver");
   System.out.println("MySQL JDBC Driver Registered successfully :)");
   conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbname","username", "password");
  
  } catch (SQLException e) {
   System.err.println("Connection failed !!!");
   e.printStackTrace();
   return null;
  
  } catch (ClassNotFoundException e) {
   System.err.println("Please configure MySQL driver file !!!");
   e.printStackTrace();
  }
  return conn;
 }
 
 public static void fetchEmailId(Connection conn){
  Statement st = null;
  if(conn == null){
   System.err.println("Connection failed !!!");
   return;
  }
  
  System.out.println("Database connection is successfull :)");
  
  //Querying simple select query
  try {
   String qry = "SELECT email_id FROM tbl_email_master";
   st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
   ResultSet rs = st.executeQuery(qry);
   while(rs != null && rs.next()){
    System.out.println(rs.getString("email_id"));
   }
   
  } catch (SQLException e) {
   e.printStackTrace();
  
  } finally{
   try{
    if(st != null) st.close();
   }catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }
 
 public static void closeConnection(Connection conn){
  if(conn != null){
   try {
    conn.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }
}