Connecting to MySQL Using JDBC Driver?

In this tutorial, you will learn how to connect to MySQL database using JDBC Connection object.

To connect to MySQL database from a Java program, you need to do the following steps:

1. Load the MySQL Connector/J into your program.
2. Create a new Connection object from the DriverManager class. Then you can use this Connection object to execute queries.

Loading MySQL Connector/J into your program

To load MySQL Connector/J into your program you follow three steps below:

First, in NetBeans IDE, from project name, right mouse click and choose properties menu item. The project properties dialog will appear.

Second, on the left hand side of the project properties dialog, from the Categories section, choose Libraries item.

Third, click on the Add JAR folder button, browse to the location where you installed MySQL Connector/J, and choose the JAR file as screenshot below; after that click OK button.

Connecting to MySQL database

First, you need to import three classes: SQLException, DriverManager, and Connection from the java.sql.* package.

1 2 3

import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException;

Second, you call the getConnection() method of the DriverManager class to get the Connection object. There are three parameters you need to pass to the getConnection() method:

1. url: the database URL in the form jdbc:subprotocol:subname. For MySQL, you use the jdbc:mysql://localhost:3306/mysqljdbc i.e., you are connecting to the MySQL with server name localhost, port 3006, and database mysqljdbc.
2. user: the database user that will be used to connect to MySQL.
3. password: the password of the database user.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

Connection conn = null; try {     // db parameters     String url       = "jdbc:mysql://localhost:3306/mysqljdbc";     String user      = "root";     String password  = "secret";     // create a connection to the database     conn = DriverManager.getConnection(url, user, password);     // more processing here     // ... } catch(SQLException e) {    System.out.println(e.getMessage()); } finally { try{            if(conn ! null)              conn.close() }catch(SQLException ex){            System.out.println(ex.getMessage()) } }

When connecting to MySQL, anything could happens e.g., database server is not available, wrong user name or password, etc. in such cases, JDBC throws a SQLException . Therefore, when you create a Connection object, you should always put it inside a try catch block. Also you should always close the database connection once you complete interacting with database by calling close() method of the Connection object.

From Java 7, there is another nice statement called try-with-resources that allows you to simplify the code above as follows:

1 2 3 4 5 6 7 8 9 10 11 12

// db parameters String url       = "jdbc:mysql://localhost:3306/mysqljdbc"; String user      = "root"; String password  = "secret";   Connection conn = null;   try(conn = DriverManager.getConnection(url, user, password);) { // processing here } catch(SQLException e) {    System.out.println(e.getMessage()); }

It is automatically calls the close() method of the Connection object once program finishes. As you can see it’s cleaner and more elegant. However…

It is not secure as well as flexible when you hard coded the database parameters inside the code like above. In case you change the database server or password; you have to change the code, compile it again, which is not a good design.

To avoid hard coding all the database parameters in the code, you can use a Java properties file to store them. In case of changes, you just need to change them in the properties file and you don’t have to recompile the code.

Let’s take a look at the properties file named db.properties:

1 2 3 4

# MySQL DB parameters user=root password=secret url=jdbc:mysql://localhost:3306/mysqljdbc

You can rewrite the code for creating a Connection object with parameters from a properties file as follows:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

Connection conn = null;   try(FileInputStream f = new FileInputStream("db.properties")) {     // load the properties file     Properties pros = new Properties();     pros.load(f);       // assign db parameters     String url       = pros.getProperty("url");     String user      = pros.getProperty("user");     String password  = pros.getProperty("password");     // create a connection to the database     conn = DriverManager.getConnection(url, user, password); } catch(IOException e) {    System.out.println(e.getMessage()); } finally {     try{         if(conn != null)             conn.close();     }catch(SQLException ex){         System.out.println(ex.getMessage());     }      }

For each interaction with MySQL database, you need to create a new connection. You would have the same piece of code for doing this in all places. Rather than doing this, you can create a new class for handing connection creation:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

package org.mysqltutorial;   import java.io.FileInputStream; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Properties;   /** * * @author mysqltutorial.org */ public class MySQLJDBCUtil {       /**      * Get database connection      *      * @return a Connection object      * @throws SQLException      */     public static Connection getConnection() throws SQLException {         Connection conn = null;           try (FileInputStream f = new FileInputStream("db.properties")) {               // load the properties file             Properties pros = new Properties();             pros.load(f);               // assign db parameters             String url = pros.getProperty("url");             String user = pros.getProperty("user");             String password = pros.getProperty("password");                          // create a connection to the database             conn = DriverManager.getConnection(url, user, password);         } catch (IOException e) {             System.out.println(e.getMessage());         }         return conn;     }   }

From next tutorial, we will use this MySQLJDBCUtil class for creating a new connection to MySQL as follows:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

package org.mysqltutorial;   import java.sql.Connection; import java.sql.SQLException;   /** * * @author mysqltutorial.org */ public class Main {       public static void main(String[] args) {         // create a new connection from MySQLJDBCUtil         try (Connection conn = MySQLJDBCUtil.getConnection()) {                          // print out a message             System.out.println(String.format("Connected to database %s "                     + "successfully.", conn.getCatalog()));         } catch (SQLException ex) {             System.out.println(ex.getMessage());         }     } }

In this tutorial, we have shown you step by step how to connect to MySQL using JDBC Connection object and use properties file to store database parameters. At the end of the tutorial, we developed a utility class that you can reuse it every time you create a connection to the database.