JDBC

JDBC (Java Database Connectivity) is a standard interface for connecting from java to relational database. JDBC provides programmers a facility to generate runtime SQL commands.

The architecture of JDBC has four main components.

1. Application
   
2. Driver Manager
3. Drivers
4. Data Source
   

Application initiates and terminates the connection with the data source. It also set transaction boundaries , submits SQL statements and retries the results.

Driver Manager load the JDBC driver and pass JDBC function calls from application to the correct driver. It also perform the error checking.

Driver establishes the connection with data source

Data source processes the commands from the driver and returns the results.

So one can say that JDBC is nothing but a collection of Java classes and interfaces that enables database access from programs written in the Java language. An Java application works in following steps:

1. Creates a database connection object
2. Create a statement object to execute SQL statement by using database connection object.
3. Access the result from Result Set Object by executing statement object.

This API has a SQLException class to handle the exception at runtime.

Note : In this article all example are MySQL specific

Create database connection

To establish a database connection JDBC API provides Data Source interface and Driver Manager class which has a getConnection() method which returns a Connection object. Here programmer is suppose to register the suitable driver before calling getConnection() method. Driver is nothing but a software program that translate the JDBC calls into DBMS specific calls. There are two ways to register the driver.

DriverManager.registerDriver(new com.mysql.jdbc.Driver());

or

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

Calling the Class.forName automatically creates an instance of a driver and registers it with the DriverManager, so you don't need to create an instance of the class. This way the second way is better than the first because here you will not face any kind of duplication.

After registering the driver first you are suppose to create a URL, this requires info related to authentication to the connection string. Either you can embed the authentication info to the url string or you can use java.util.Property class to pass the info to the getConnection().

Here is the example for both

String url= " jdbc:mysql: //localhost:" + portNumber+ "/dbName? user= dbUser & password ="+ dbPassword ;
Connection connectionObj = DriverManager.getConnection(url);

or

java.util.Properties dbInfo = new java.util.Properties();
info.put ("defaultRowPrefetch","5");
info.put ("user", dbUser);
info.put ("password", dbPassword );

String url= "jdbc:mysql://localhost:"+ portNumber+"/dbName";
Connection connectionObj = DriverManager.getConnection(url , info);

Second method is better than the first because here one can also set the value of number of rows to be fetched while returning the ResultSet object. Here one thing is very important that this feature is driver specific, driver may ignore this count.

In above example I am providing dbName to the Connection URL, in case one doesn't provide the dbName to the connection URL one is suppose to call static SQL statement with following query string to select the database "use database dbName".

Create a statement

JDBC API provides Statement interface to create a static SQL statement. This interface has two sub interfaces PreparedStatement and CallableStatement.

Object of Statement can be get from Connection object. Here is the code fragment to get the Statement object

Statement stmt = connectionObj.createStatement();

This interface has execute(), executeQuery() and executeUpdate() methods to execute SQL statements.

String myQuery = "select * from my_table";
ResultSet rs = stmt.executeQuery(myQuery );

Its good habit to call the close() method to release the statement object as it also clears the ResultSet object automatically.

If one wants to execute the query statement multiple times then it is better to use prepared statements. Because by using Statement every time it does some kind of validation of SQL syntax and pseudo code generation but by using PreparedStatement it will be executed only once and this precompiled SQL statement is stored in PreparedStatement object. Due to this the RDBMS caches the query plan for this execution and makes it performance wise better then the Statement.

Here is pseudo code to create a PreparedStatement

String myQuery = "select * from my_table where id = ?";
PreparedStatement pstmt = connectionObj.prepareStatement(myQuery);

This will create the PreparedStatement object which can be used multiple times. For executing a query initially one is suppose to set the variable value (here it is id) according to its type. Suppose it is a int then one will call

pstmt.setInt(1,idValue);

After setting the value one will call

ResultSet rs = pstmt.executeQuery();

This will execute the query and store the result in ResultSet object. Once you are done with all execution call close() method to release the PreparedStatement object.

JDBC also provide the facility to execute Procedure from your Java program for that it has CallableStatement interface. CallableStatement object can be created from connection object and pseudo code for this is

CallableStatement cstmt = connectionObj.prepareCall("{call my_procedure(?,?)}");

Here we have two input parameters so before calling the execute command one is suppose to set the value of both parameter

cstmt.setInt(1, intValue);
cstmt.setString(2, strValue);

If this procedure is suppose to returns some value in that case programmer has to register that output parameter by calling following command.

cstmt.registerOutParameter(1, Types.INTEGER);
cStmt.registerOutParameter(2, Types.VARCHAR);

After this initialization call execute() method like

boolean hadResults = cstmt.execute();

Here the return type is boolean this will be true if the first result is a ResultSet object and false if the first result is an update count or there is no result and to access the ResultSet

ResultSet rs = cstmt.getResultSet();

In similar manner CallableStatement interface has close() method to release the object of CallableStatement.

Access the result

By execute() method one can get the object of ResultSet.

ResultSet object maintains the cursor to get the data from it. Initially this cursor is positioned before the first row. ResultSet interface provides next() method to access the next row in it.

It has getter method to access the value of the columns from row and input type to these methods are int or String. By this way it provides programmers a facility to access the column value by the column name or by column number. Here is the pseudo code to access the ResultSet.

while(rs.next())
{
emp_id = rs.getInt(1);
emp_name = rs.getString(2);
emp_type = rs.getString("emp_type");
}

Suppose two column have same name in that case whichever column it access first the value of that column will be returned so unless you are sure with the column name its good habit to access the column value by number.

ResultSet interface also provides the facility of updater method by which you can insert and update the data in databse. But the default ResultSet is not updatable and has a cursor that moves forwards only. Thus one can iterate on this only once from first row to last row. It is possible to make this scrollable and updatable. Here is the pseudo code for the same

Statement stmt = connectionObj.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

String myQuery = "select * from my_table";
ResultSet rs = stmt.executeQuery(myQuery);

By creating Statement like this one can scroll and update it.