JDBC MySQL database Connection example

By | July 14, 2018

In the 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 a java program with a database. Here I am using the MYSQL database but you are free to choose any database you like to use. Steps would be similar for another 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 variants of getConnection() methods you can easily understand by looking at 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 the java program with the 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 the second parameter is java.util.Properties reference  where we are just setting database username & password 

Approach 3:

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

This approach is very much similar to the 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

You May Also Like:

What is Java Database Connectivity (JDBC)?
Types of JDBC drivers in java?
JDBC database connection using property resource(property file)
CRUD(Create,Read,Update and Delete) Operation using JDBC Statement
Reading data from database using JDBC Statement
CRUD(Create,Read,Update and Delete) Operation using JDBC PreparedStatement

If you have any feedback or suggestion please feel free to drop them in below comment box. 

Leave a Reply

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