JDBC MySQL database Connection example

By | July 14, 2018

In previous couple of posts What is Java Database Connectivity (JDBC)? and Types of JDBC drivers in java?
We got to know some Jdbc basics.

In this post we will learn how to connect java program with database. Here I am using MYSQL database but you are free to choose any database you like to use. Steps would be similar for other database as well.

Project structure  in Eclipse:

JDBC connection steps for MySQL database.

  1. Register JDBC driver class
  2. Creating Jdbc connection object
  3. Creating Jdbc statement object
  4. Executing SQL queries
  5. Closing connection object

1. Register JDBC driver class

The forName() method of java.lang.Class class is used to register the JDBC driver class. This method is used to dynamically load the JDBC driver class. Note that forName method is static so directly we can refer this method my class name.
Syntax of forName() method

public static Class<?> forName(String className) throws ClassNotFoundException

Note: From JDBC 4.0 onwards explicitly registering the Jdbc driver is optional. We just need to put database vender’s  provided Jar in the project classpath, and then JDBC driver manager can detect and load the driver automatically.

Blow code snippet shows how we load MySQL database driver to establish database connection
Class.forName(“com.mysql.jdbc.Driver”);

2. Creating Jdbc connection object

We have three Jdbc methods available in java.sql.DriverManager class to connect jdbc program with any database.

  1. public static Connection getConnection(String url,String user, String password) throws SQLException
  2. public static Connection getConnection(String url,java.util.Properties info) throws SQLException
  3. public static Connection getConnection(String url) throws SQLException

These all methods are static  so we can refer it directly by Class Name.

All above three variant of getConnection() methods you can easily understand by looking in blow example covered.

3. Creating Jdbc statement object
Once we get Connection object using any one of method mentioned in step 2 we can create an object of jdbc statement.
We have hands full of methods available in Connection interface

Like below code snippet shows how we can create java.sql.Statement object using createStatement() method.
Statement st=con.createStatement();

Where con is an object or reference of java.sql.Connection Interface

4. Executing SQL queries

We have three execute(),executeUpdate() and executeQuery methods available in Jdbc to execute SQL query.
To learn how to make use of these method you can refer CRUD(Create,Read,Update and Delete) Operation using JDBC Statement and CRUD(Create,Read,Update and Delete) Operation using JDBC PreparedStatement

5.Closing connection object

If we close connection object statement then Statement and ResultSet objects will be closed automatically.The  java.sql. Connection interface is used to close the connection by calling close() method.

Syntax of close() method

void close() throws SQLException;

Code snippet to close connection

conn.close();

Where conn is an object of java.sql.Connection Interface.

Note: Fron Java 7, JDBC can be used to  try-with-resources statement to automatically close resources of type java.sql .Connection, java.sql.ResultSet, and java.sql.Statement.

As in this program we are going to connect java program with MySQL database and not going to execute any SQL query so steps 3 and 4 are not involved.

Approach 1:

In this example we have used  below getConnection method of  java.sql.DriverManager class
public static Connection getConnection(String url,String user, String password) throws SQLException

This method takes three parameters database URL,username and password.

Here in all examples i have used MySQL database username =”root”,password  = “root”  what password had supplied during MYSQL database installation time and finally database URL = “jdbc:mysql://localhost:3306/jdbcdb” Where jdbc is the API, mysql is the database, localhost is the server name on which mysql  database server is running, we may also use IP address instead machine name, 3306 is the port number and jdbcdb(Make you you have created this schema in MySQL database) is the database name. You may use any database name, in that case, You need to replace the jdbcdb with your database name.

Approach 2:

In this approach we have used  below getConnection method of  java.sql.DriverManager class
public static Connection getConnection(String url,java.util.Properties info) throws SQLException

This approach is very much similar to approach 1 except we have used here getConnection() method which takes two parameters where first parameter is the database URL and second parameter is java.util.Properties reference  where we are just setting database username & password 

Approach 3:

In this approach we have used  below getConnection method of  java.sql.DriverManager class
public static Connection getConnection(String url) throws SQLException

This approach is very much similar to above two approaches except we have used here getConnection() method which takes one String parameter in which we have concatenated database URL,username and password as below and passed to getConnection(String url) method.
jdbc:mysql://localhost:3306/jdbcdb?user=root&password=root

Now We have Client program which makes use of above DBUtil classes to retrieve database connection.

When you run ClientTest .java in your favorite IDE then you will get below out.

This shows  we successfully connected our java program with MYSQL database.

That’s all about  this topic JDBC MySQL database Connection example
If you have any feedback or suggestion please feel free to drop in blow comment box. 

Leave a Reply

Your email address will not be published. Required fields are marked *