JDBC implementation steps

The basic implementation steps involved in developing a JDBC application are:

1. Load and register the driver
2. Establish a Connection between Java application and database
3. Create a Statement object
4. Execute the query on the database
5. Process the result using ResultSet
6. Close the connection

 

Before loading and registering the driver you need to import java.sql package for connecting with a database and performing queries on the data in the database.

 

Load and register the driver

The implementation of JDBC interfaces is provided by a third-party database vendor in the form of a driver. This driver needs to be loaded into main memory for it to be used by a Java application for communicating with a database. This process is known as registering a driver. You can register a driver in one of two ways.

 

Class.forName()

The forName() method is used to register a driver, which dynamically loads the driver’s class file into memory and registers it. This method is the most commonly used one as it makes driver registration configurable and portable. Sample for registering Oracle driver is given below.

try
{
	Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch(ClassNotFoundException ex) 
{
	System.out.println("Error: unable to load driver class!");
	System.exit(1);
}

 

DriverManager.registerDriver()

You can use the static method registerDriver() if you are using non-JDK compliant JVM, like the one provided by Microsoft. Sample code for registering Oracle driver using registerDriver() method is given below.

try 
{
	Driver oracleDriver = new oracle.jdbc.driver.OracleDriver();
	DriverManager.registerDriver(oracleDriver);
}
catch(ClassNotFoundException ex) 
{
	System.out.println("Error: unable to load driver class!");
	System.exit(1);
}

 

Establish a connection between Java application and database

Once the driver is loaded, the DriverManager.getConnection() method can be used to create a connection between the Java program and the database. The overloaded versions of getConnection() method are as follows:

• getConnection(String URL)
• getConnection(String URL, Properties prop)
• getConnection(String URL, String user, String password)

 

The connection URL will be different for each DBMS. The JDBC driver name and the connection URL for some popular databases are given below.

DBMS	JDBC driver name	URL format
MySQL	com.mysql.jdbc.Driver	jdbc:mysql://hostname/databaseName
ORACLE	oracle.jdbc.driver.OracleDriver	jdbc:oracle:thin:@hostname:port number:database name
DB2	com.ibm.db2.jdbc.net.DB2Driver	jdbc:db2:hostname:port number/database name
Sybase	com.sybase.jdbc.SybDriver	jdbc:sybase:Tds:hostname: port number/database name

 

Create a statement object

Once a connection has been established with the database, we can use Statement, PreparedStatement or CallableStatement interface for interacting with the database. These JDBC interfaces define methods for executing SQL and PL/SQL commands and retrieve data from the database.

 

To use a Statement object, we need to create it by using createStatement() method available on connection object. If connection object is conn, we can write conn.createStatement() to create a Statement object.

 

Execute the query on the database

Once the Statement object is created, we can use it to execute SQL statements by using one of the following three methods:

• ResultSet executeQuery(String SQL): Returns a ResultSet object. Use this method when the query is SELECT statement.
• int executeUpdate(String SQl): Returns number of rows affected by executing the given SQL query. Use this method when the query contains INSERT, UPDATE or DELETE statement.
• boolean execute(String SQL): Returns true if a ResultSet object can be retrieved. Otherwise, it returns false. Use this method when the SQL query contains a DDL command or if you want to execute truly dynamic SQL.

 

Process the result using ResultSet

After executing a SELECT query on the database, the result of the query is represented by a ResultSet object. The ResultSet interface in JDBC defines several methods for navigating the result, retrieving the values in a row from the dataset and updating the values in a row or the entire row in result set.

 

Some of the navigational methods defined in ResultSet interface are as follows:

• beforeFirst(): Moves the cursor just before the first row.
• afterLast(): Moves the cursor just after the last row.
• first(): Moves the cursor to first row.
• last(): Moves the cursor to the last row.
• previous(): Moves the cursor to the previous row.
• next(): Moves the cursor to the next row.

 

All the above methods throw SQLException.

 

To retrieve individual values from a row in the result set, there are several methods for each of the eight basic data types in Java and for other predefined types like String, Object, URL, Date, Time, etc. For example, the methods available for int datatype are given below:

• getInt(String columnname): Returns the integer value in the current row under given column name.
• getInt(int columnindex): Returns the integer value in the current row under given column index.

 

To update the values in a row, there are several methods available like the get methods discussed above. For example, the methods available for String type are given below:

• updateString(int columnname, String s): Updates the string value at the given column name with the specified string value.
• updateString(int columnindex, String s): Updates the string value at the given index with the specified string value.

 

Close the connection

At the end of the JDBC program, it is good practice to close the opened connection to a database. This can be done by calling the close() method available on the Connection reference. If the reference name of Connection is conn, then we can write conn.close().

 

You can write the connection closing code inside the finally block. If programmer does not provide any connection closing code, the garbage collector will recycle the connection object once it gets marked.

 

You can also close the Statement object explicitly by calling the close() method. For example, if the Statement object is stmt, we can write stmt.close() to close the Statement object.

 

For more information on JDBC, refer the following links:

• https://www.tutorialspoint.com/jdbc/index.htm