What Is JDBC Programming? JDBC

JDBC programming can be explained in the following simple steps:

  • Importing required packages
  • Registering the JDBC drivers
  • Opening a connection to a database
  • Creating a Statement/PreparedStatement/CallableStatement object
  • Executing a SQL query and returning a ResultSet object
  • Processing the ResultSet object
  • Closing the ResultSet and Statement objects
  • Closing the connection

The first hands-on experience with JDBC in this book involves a basic and complete example to illustrate the overall concepts related to creating and accessing data in a database. Here, we assume that a database (MySQL or Oracle) is created and available for use. Database creation is DBMS-specific. This means that each vendor has a specific set of commands for creating a database. For this obvious reason, database creation commands are not portable (this is the main reason why JDBC has stayed away from database creation commands).

Step 1: Import the Required Packages.

Before you can use the JDBC driver to get and use a database connection, you must import the following packages: java.sql and javax.sql.

Step 2: Register the JDBC Drivers

A JDBC driver is a set of database-specific implementations for the interfaces defined by JDBC. These driver classes come into being through a bootstrap process. This is best shown by stepping through the process of using JDBC to connect to a database,

Let’s use Oracle’s type 4 JDBC driver as an example. First, the main driver class must be loaded into the Java virtual machine (VM):

Class.forName("oracle.jdbc.driver.OracleDriver");

The specified driver(i.e., the oracle.jdbc.driver.OracleDriver class) must implement the java.sql.Driver interface. A class initializer(a static code block) within the oracle.jdbc. driver.OracleDriver class registers the driver with the java.sql.DriverManager.

Now let’s use MySQL’s JDBC driver as an example. First, the main driver class must be loaded into the Java VM:

Class.forName("com.mysql.jdbc.Driver");

The specified driver(i.e., the com.mysql.jdbc.Driver class) must implement the java.sql.Driver interface. A class initializer (a static code block) with in the com.mysql.jdbc.Driver class registers the driver with the java.sql.DriverManager. Note that when loading a JDBC driver, you must make sure that the database-specific driver API(usually a JAR file) is in your CLASSPATH environment variable. Each database vendor has its own specific implementation of the JDBC driver.

Step 3: Opening a Connection to a Database

Next, we need to obtain a connection to the database. To get a connection object to a database, you need at least three pieces of information: the database URL, the database username, and the database user’s password.

Step 4: Creating a Statement/PreparedStatement/CallableStatement Object

Once you have a valid java.sql.Connection object, you can create statement objects(such as Statement, PreparedStatement, and CallableStatement). The bootstrap process continues when you create a statement:

In order to do something useful with a database, we create the following table:

Step 5: Executing a Query and Returning a ResultSet Object

Invoking the execute() method of this statement object will execute the database-specific code necessary to issue a SQL statement against the Oracle database and retrieve the results(as a table):

The result is a table(as a ResultSet object) returned by executing the SELECT statement. Again, what is actually returned is an instance of OracleResultSet, which is an Oracle-specific implementation of the java.sql.ResultSet interface. By iterating the result, we can get all of the selected records.

So the purpose of a JDBC driver is to provide these implementations that hide all the database-specific details behind standard Java interfaces.

Step 6: Processing the ResultSet Object

ResultSet.next() returns a boolean: true if there is a next row or record and false if not (meaning the end of the data or set has been reached). Conceptually, a pointer or cursor is positioned just before the first row when the ResultSet is obtained. Invoking the next() method moves to the first row, then the second, and so on.

Once positioned at a row, the application can get the data on a column-by-column basis using the appropriate ResultSet.getXXX() method. Here are the methods used in the example to collect the data:

or we may use the column names(instead of column positions):

The order of the getString(columnNumber) should be the same as the order of columns selected in the SQL SELECT statement; otherwise, we could run into an error.

Step 7: Closing JDBC Objects

Releasing or closing JDBC resources(such as ResultSet, Statement, PreparedStatement, and Connection objects) immediately instead of waiting for it to happen on its own can improve the overall performance of your application. From a good software engineering point of view, you should put close()statements in a finally clause, because it guarantees that the statements in the finally clause will be executed as the last step regardless of whether an exception has occurred.

Closing ResultSet

ResultSet has a close() method that releases the ResultSet object’s database and JDBC resourcesimmediately instead of waiting for that to happen when it is automatically closed. Another major reason to close the ResultSet objects immediately after they are done is that we increase concurrency; as long as the ResultSet object is open, the DBMS internally holds a lock.

Here is some sample code for closing a ResultSet object. It is always a good idea to have utility classes to close these JDBC resources, and the following method can do the job:

Closing Statement

Statement has a close() method, which releases this Statement object’s database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed. Here is some sample code for closing a Statement object. It is always a good idea to have utility classes to close these JDBC resources, and the following method can do the job:

Closing PreparedStatement

PreparedStatement does not have a direct close() method, but since PreparedStatement extends Statement, then you may use the Statement.close() method for PreparedStatement objects. It is always a good idea to have utility classes to close these JDBC resources, and the following method can do the job:

Closing Connection

If you are using a connection pool manager to manage a set of database connection objects, then you need to release the Connection object to the connection pool manager (this is called a “soft” close).Alternatively, you can use the close() method, which releases the Connection object’s database and JDBC resources immediately instead of waiting for them to be automatically released. Here is some sample code for closing a Connection object. It is always a good idea to have utility classes to close these JDBC resources, and the following method can do the job:



Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

JDBC Topics