Java JDBC Connection Tutorial With Programming Example

This JDBC Connection tutorial explains basic steps to a database with examples and provides JDBC connection strings for different databases:

In the previous tutorial of the JDBC tutorial series, we learned components, architecture, and types of drivers in Java Database Connectivity (JDBC).

In this tutorial, we will discuss the steps to connect with databases using JDBC. This tutorial will show you how to do JDBC connection and the perform database operations. JDBC API acts as an interface between the Java program and Database.

At the end of this tutorial, you will be able to write Java programs to connect with databases and perform DB operations.

JDBC Connection Steps

There are 6 basic steps to connect with JDBC. They are enlisted in the below image:

#1) Import Packages

First, we need to import the existing packages to use it in our Java program. Import will make sure that JDBC API classes are available for the program. We can then use the classes and subclasses of the packages.

Irrespective of the JDBC Driver, add the following import statement in the Java program.

import java.sql.*;

Import the other classes based on the functionality which you will use in the program. Download the appropriate Jar files for the database which you will use in the program.

Refer to the previous tutorial for the links to download the Jar files for your database.

JDBC API 4.0 mainly provides 2 important packages:

• java.sql
• javax.sql

(i) java.sql package

This package provides classes and interfaces to perform most of the JDBC functions like creating and executing SQL queries.

(ii) javax.sql package

It is a JDBC extension API and provides server-side data access and processing in Java Program.

#2) Load Driver

First, we should load/register the driver in the program before connecting to the Database. You need to register it only once per database in the program.

We can load the driver in the following 2 ways:

1. Class.forName()
2. DriverManager.registerDriver()

(i) Class.forName()

In this way, the driver’s class file loads into the memory at runtime. It implicitly loads the driver. While loading, the driver will register with JDBC automatically.

Note: forName() method is valid only for JDK Compliant Virtual Machines.

(ii) DriverManager.registerDriver()

DriverManager is an inbuilt class that is available in the java.sql package. It acts as a mediator between Java application and database which you want to connect. Before you connect with the database, you need to register the driver with DriverManager. The main function of DriverManager is to load the driver class of the Database and create a connection with DB.

Public static void registerDriver(driver) – This method will register the driver with the Driver Manager. If the driver is already registered, then it won’t take any action.

• It will throw SQLException if the database error occurs.
• It will throw NullPointerException if the driver is null.

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver())
DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver())

Like this, you can register the driver for your Database by passing it as a parameter.

#3) Establish Connection

After loading the driver, the next step is to create and establish the connection. Once required, packages are imported and drivers are loaded and registered, then we can go for establishing a Database connection.

DriverManager class has the getConnection method, we will use this method to get the connection with Database. To call getConnection() method, we need to pass 3 parameters. The 3 parameters are string data type URL, a username, and a password to access the database.

The getConnection() method is an overloaded method. The 2 methods are:

• getConnection(URL,username,password); – It has 3 parameters URL, username, password.
• getConnection(URL); – It has only one parameter. URL has a username and password also.

The following table lists the JDBC connection strings for the different databases:

Example:

Connection con = DriverManager.getConnection(jdbc:oracle:thin:@localhost:1521:xe,System,Pass123@)

Here in this example,

• thin refers to the Driver type.
• localhost is where the Oracle database is running.
• 1521 is the port number to connect to DB.
• xe – SID
• System – User name to connect to the Oracle Database.
• Pass123@ – Password

#4) Create And Execute Statement

Once the connection has established, we can interact with the connected Database. First, we need to create the statement to perform the SQL query and then execute the statement.

(i) Create Statement

Now we will create the statement object that runs the query with the connected database. We use the createStatement method of the Connection class to create the query.

There are 3 statement interfaces are available in the java.sql package. These are explained below:

a) Statement

This interface is used to implement simple SQL statements with no parameter. It returns the ResultSet object.

Statement statemnt1 = conn.createStatement();

b) PreparedStatement

This PreparedStatement interface extends the Statement interface. So, it has more features than the Statement interface. It is used to implement parameterized and precompiled SQL statements. The performance of the application increases because it compiles the query only once.

It is easy to reuse this interface with a new parameter. It supports the IN parameter. Even we can use this statement without any parameter.

String select_query = “Select * from states where state_id = 1”;
PreparedStatement prpstmt = conn.prepareStatement(select_query);

c) CallableStatement

CallableStatement interface extends the PreparedStatement interface. So, it has more features than the PreparedStatement interface. It is used to implement a parameterized SQL statement that invokes procedure or function in the database. A stored procedure works like a method or function in a class. It supports the IN and OUT parameters.

The CallableStatement instance is created by calling the prepareCall method of the Connection object.

CallableStatementcallStmt = con.prepareCall("{call procedures(?,?)}");

(ii) Execute The Query

There are 4 important methods to execute the query in Statement interface. These are explained below:

• ResultSet executeQuery(String sql)
• int executeUpdate(String sql)
• boolean execute(String sql)
• int []executeBatch()

a) ResultSet executeQuery(String sql)

The executeQuery() method in Statement interface is used to execute the SQL query and retrieve the values from DB. It returns the ResultSet object. Normally, we will use this method for the SELECT query.

b) executeUpdate(String sql)

The executeUpdate() method is used to execute value specified queries like INSERT, UPDATE, DELETE (DML statements), or DDL statements that return nothing. Mostly, we will use this method for inserting and updating.

c) execute(String sql)

The execute() method is used to execute the SQL query. It returns true if it executes the SELECT query. And, it returns false if it executes INSERT or UPDATE query.

d) executeBatch()

This method is used to execute a batch of SQL queries to the Database and if all the queries get executed successfully, it returns an array of update counts. We will use this method to insert/update the bulk of records.

#5) Retrieve Results

When we execute the queries using the executeQuery() method, the result will be stored in the ResultSet object. The returned ResultSet object will never be null even if there is no matching record in the table. ResultSet object is used to access the data retrieved from the Database.

ResultSet rs 1= statemnt1.executeQuery(QUERY));

We can use the executeQuery() method for the SELECT query. When someone tries to execute the insert/update query, it will throw SQLExecption with the message “executeQuery method can not be used for update”.

A ResultSet object points to the current row in the Resultset. To iterate the data in the ResultSet object, call the next() method in a while loop. If there is no more record to read, it will return FALSE.

ResultSet can also be used to update data in DB. We can get the data from ResultSet using getter methods such as getInt(), getString(), getDate(). We need to pass the column index or column name as the parameter to get the values using Getter methods.

We will get to know more about the ResultSet in the next tutorial.

#6) Close Connection

Finally, we are done with manipulating data in DB. Now we can close the JDBC connection. We need to make sure that we have closed the resource after we have used it. If we don’t close them properly we may end up out of connections.

When we close the connection object, Statement and ResultSet objects will be closed automatically.

conn.close();

From Java 7 onwards, we can close the JDBC connections automatically using a try-catch block. JDBC connection should be opened in the parenthesis of the try block. Inside the try block, you can do the database connections normally as we do.

Once the execution exits the try block, it will automatically close the connection. In this case, we don’t need to close the connection by calling conn.close method in the Java program.

try(Connection conn = DriverManager.getConnection(url, user, password))
{	
	//database connection and operation
}

Java JDBC Connection Example

In this example, you will see how to implement the 6 basic steps to connect with database using JDBC in Java program.

Create Table

Before that, first, create one table and add some entries into it.

Below is the SQL query to create a table.

create table employee_details (empNum number(10), lastName varchar(50),
 firstName varchar(50), email varchar(255) , deptNum number(10), salary number(10));

Created the “employee_details” table in Oracle DB.

Insert Data Into Table

Using the following queries, insert the data into the “employee_details” table.

insert into employee_details values (1001, 'Luther', 'Martin', 'ml@gmail.com', 1, 13000);
insert into employee_details values (1002, 'Murray', 'Keith', 'km@gmail.com', 2, 25000);
insert into employee_details values (1003, 'Branson', 'John', 'jb@gmail.com', 3, 15000);
insert into employee_details values (1004, 'Martin', 'Richard', 'rm@gmail.com', 4, 16000);
insert into employee_details values (1005, 'Hickman', 'David', 'dh@gmail.com', 5, 17000);

Java Program

Download the JDBC jar file and import it into the Java project.

package com.STH.JDBC; 
	// import sql package to use it in our program
	import java.sql.*;

	public class Sample_JDBC_Program {

	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		// store the SQL statement in a string
		String QUERY = "select * from employee_details";
		//register the oracle driver with DriverManager
		Class.forName("oracle.jdbc.driver.OracleDriver"); 
		//Here we have used Java 8 so opening the connection in try statement
		try(Connection conn = DriverManager.getConnection("jdbc:oracle:thin:system/pass123@localhost:1521:XE"))
		{
				Statement statemnt1 = conn.createStatement();
				//Created statement and execute it 
				ResultSet rs1 = statemnt1.executeQuery(QUERY); 
				{	
					//Get the values of the record using while loop
					while(rs1.next())
					{
						int empNum = rs1.getInt("empNum");
						String lastName = rs1.getString("lastName");
						String firstName = rs1.getString("firstName");
						String email = rs1.getString("email");
						String deptNum = rs1.getString("deptNum");
						String salary = rs1.getString("salary");
						//store the values which are retrieved using ResultSet and print it
					System.out.println(empNum + "," +lastName+ "," +firstName+ "," +email +","+deptNum +"," +salary);
					}
				} 
		}
		catch (SQLException e) {
			//If exception occurs catch it and exit the program
			e.printStackTrace();
		}
	   }
	}

Output:

Key points to be noted:

• First, we need to import the packages which we will be using in our Java program for the JDBC connection. So we can use the classes, subclasses, and interfaces in the packages.
• We need to register or load the driver with DriverManager before establishing a connection.
• After registering the driver, we can establish the connection and perform the operations.
• Using a statement interface we can create and execute the SQL query. For a simple SQL query, we can use the Statement interface. For insert/update/delete, we can use the PreparedStatement interface.
• After the statement execution, the results will be stored in the ResultSet object. We get the results from the ResultSet object using the next() method for more than 1 record.
• Once we are done with the database operation, we need to close the connection. So that the resource will be available for others to use.

Frequently Asked Questions

Q #1) What are the fundamental steps to connect to DB in Java?

Answer: There are 6 basic steps to connect to DB in Java.

They are:

1. Import-Package
2. Load driver
3. Establish Connection
4. Create and execute the statement
5. Retrieve results
6. Close Connection

Q #2) What is the difference between BLOB and CLOB data types in JDBC?

Answer:

BLOB is used to hold the binary type of data. The storage size may vary based on the databases Example: images, voice, video.

CLOB is used to hold the character type of data. Like CLOB, the storage space may vary based on the DB. Example: files.

Q #3) Which method of DriverManager class is used to establish a connection with DB?

Answer: DriverManager class has a getConnection() method which is used to establish a connection with DB.

Q #4) What is the difference between Class.forName() and DriverManager.registerDriver()?

Answer: Class.forName() – First it loads the driver into the memory and then registers the appropriate driver with the Driver Manager. Then it will create an object of the driver to perform the JDBC connection. It will load the driver explicitly.

DriverManager.registerDriver() – It registers the driver implicitly.

Q #5) What is a connection leak?

Answer: This situation occurs when a connection is opened and you didn’t close it. If there is a block of code, which opens the connection and doesn’t close the connection. Whenever that block of code is executed, a connection will leak from the Connection Pool.

Once all the available connections are leaked, no connection will be available and the application will hang. How much it is important to open the connection that much it is important to close the connection.

Q #6) Is it mandatory to close the connection?

Answer: If you are using Java versions below 7, you need to close the connection manually.

From versions above Java 7, we can close the connection automatically by opening the JDBC connection code in the parenthesis of the try block. Once the program exits the try block, it will close the connection automatically.

Conclusion

In this tutorial, we have discussed how to establish a JDBC connection. Now you can perform DB operations using JDBC in the Java Program. We explored the 6 basics steps to connect with Database. We need to import the packages in our Java Program to use it.

Once we register the driver, we can establish the connection. We can create and execute the SQL statement using a Statement interface and retrieve the results in the ResultSet object. As the last step, we should close the connection.

<<PREV Tutorial | NEXT Tutorial>>