JDBC

Java Database Connectivity (JDBC)

JDBC is the industry standard for database agnostic connectivity, where developer can read/write data from/to DBMS, without worry about the platform, DBMS and its implementation. SQL or non-SQL based possible to connect using this JDBC. We have to make a call using JDBC where it takes responsibility of how it has to be translated to DB calls and vice versa.

Types of JDBC Drivers

JDBC-ODBC bridge

access through ODBC drivers. Performance of this connection is not good compare with others

Native-API partly Java technology-enabled driver

Converts JDBC calls into direct DB API calls. Some DB binaries has to be placed in client system.

Net-protocol fully Java technology-enabled driver

JDBC API calls converted into middleware understanable calls, same will convert back to DBMS understandable call by middleware server. Hence, DB vendors has to support and adhere with middleware and vice versa.

Native-protocol fully Java technology-enabled driver

converts JDBC API calls to network protocol which is supported by DBMS vendor. Hence, client machine directly access DB without middleware.

Connection Establishment

After successful installation of Database, We have to start the Database with listening port number, default port numbers MySQL(3306), Oracle(1521). Following steps has to be catered in our implementation to establish connection.

1. Load and Register Driver class, which has static block to set DriverInfo (driver, driverClass, driverClassName) using java.sql.DriverManager.registerDriver.
   

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

2. Form Connection URL, which helps to identify which DB driver has to be used as like jdbc:subprotocol:subname
   

    String url = "jdbc:oracle:thin:@xyz.oracle.com:1521/xyz" ;

3. Set values for properties like username and password, and etc.,or pass credentials while creating/fetching connection from DriverManager.

   
    java.util.Properties info = new java.util.Properties();
    info.put("user", user);
    info.put("password", password);

4. Fetch/create connection from DriverManager

   
   java.sql.Connection conn=getConnection(url, info);
   or
   java.sql.Connection conn=getConnection(url, user, password);
   

Loading Class

Class.forName(className) is equivalent to Class.forName(className, true, ClassLoader.getCallerClassLoader()), where true says that className class can be intialized(static blocks get executed). Almost all the the Driver has the following code to register DriverInfo to DriverManager

static 
{
 try
 {
 DriverManager.registerDriver(new Driver());
 }
 catch(SQLException E)
 {
 throw new RuntimeException("Can't register driver!");
 }
}

DataSource

Instead of creating connection from DriverManager and maintaing it, we can fetch the unused connection from javax.sql.DataSource, which will take care of lifecycle of connection. DataSource comes with three different implementation flavour Basic, ConnectionPool, Distributed

In server based application, we can access DataSource object through JNDI look up.

InitialContext ic = new InitialContext(); 
DataSource ds = ic.lookup("java:jdbc/mydb/MyDataSource");
Connection con = ds.getConnection(); 

In standalone application also, we can

DataSource ds = new OracleDataSource();
 ds.setURL(url);
 ds.setUser(user)
 ds.setPassword(password); 
Connection con = ds.getConnection(); 

Statement

Once Connection establishment is completed, we can get start with reading/writing to Database. To input our requirement to DB, we need an interface that is nothing but a java.sql.Statement.

Statement comes in three different flavour

1. Statement - to run Dynamic SQL queries, which will be parsed and compiled everytime it gets executed.
2. Prepare Statement - to run parameterized SQL query with getting benefit of precompilation of SQL
3. Callable Statement - to execute stored procedures

Statement can executed using the following API's

1. execute - executes SQL query and returns true if found first result as ResultSet
2. executeQuery - used to execute SQL SELECT query and returns ResultSet
3. executeBatch - executes all the SQL queries which are added using Statement.addBatch(sqlQuery)
4. executeUpdate - used to execute SQL UPDATE, DELETE, INSERT and TRUNCATE queries and results number of impacted records count

Auto Generated Keys

Generally, we specify primary key columns to auto generate value by DB to avoid duplications. We may need this value in caller place to utilize in other business. Best example would be, in PurchaseOrder scenario, where master entry created with buyer details and date of order in PurchaseOrderMaster entry, where item and quantity lists are captured in PurchaseOrderChild table.

We can request DB driver to send back the generated key to caller by passing Statement.RETURN_GENERATED_KEYS in execute, and executeUpdate APIs.

Statement stmt = con.createStatement();
if(stmt.execute(insertStmt, Statement.RETURN_GENERATED_KEYS))
{
ResultSet rs=stmt.getGeneratedKeys();
while (rs.next()) {
System.out.println(rs.getString(1));
}
}

Maximum Rows and Timeout

Additionally, We can set property like maximum number of rows need to be fetched, maximum time to wait for query to process(Timeout), maximum field size, cursor name, fetching direction and etc.,

stmt.setMaxRows(50);
stmt.setFetchDirection(ResultSet.FETCH_REVERSE);

ResultSet

java.sql.ResultSet captures outcome from DB call. It gives details about the column, and its value. If SQL SELECT statement executed using executeQuery API then we do get ResultSet object as output. This resulted object has cursor which positioned before the first row of result. We have to call next() API to get into first row of result. If no argument passed, while creating connection then we can move the cursor only towards forward

If we wants to move cursor forward and backward then we have to pass ResultSet.TYPE_SCROLL_INSENSITIVE or ResultSet.TYPE_SCROLL_SENSITIVE. We have following APIs to ease the cursor movement - next(), previous(), first(), last(), beforeFirst(), afterLast(), relative(int rows), and absolute(int row)

We can eventually update ResultSet which will be updated in DB. To enable this, we have pass second argument as ResultSet.CONCUR_UPDATABLE, default is ResultSet.CONCUR_READ_ONLY

Statement stmt = con.createStatement(
 ResultSet.TYPE_SCROLL_SENSITIVE,
 ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("SELECT 1 FROM DUAL");

ResultSet comes with row manipulating APIs like insertRow(), deleteRow(), updateRow() and refreshRow().

Similarly, ResultSet helps to get value of columns either passing number or column name. First column is refered using 1 and its go on.

int Id=rs.getInt(1);
float amt= rs.getFloat("amount");
Blob blob=rs.getBlob("policy");
Clob clob=rs.getClob("policyinclob");
Reader reader=rs.getCharacterStream("policy");
InputStream is=rs.getBinaryStream("policy");

Release resource

We have established a connection and ran Statement and retrieved result. What next ?

We have to release used resource like Sonnection, statement, and ResultSet. Since these are making physical connection to DB, it is application responsibility to notify Driver to release or pool the resources. close() api called in these resources to release. if connection pooling is enabled, then connection will be collected for reuse.

rs.close();
stmt.close();
conn.close();

Read more : JDBC Basic | JDBC Overview

How to read data from Blob?

java.sql.Blob interface exposes two APIs to read bytes from Blob object.

Blob blob=rs.getBlob("policy");

InputStream is = blob.getBinaryStream() ; //option 1
byte[] bytes = blob.getBytes(1, (int) blob.length()); //option 2