JDBC Connectivity

The JDBC ( Java Database Connectivity) API defines interfaces and classes for writing database applications in Java by making database connections. Using JDBC you can send SQL, PL/SQL statements to almost any relational database. JDBC is a Java API for executing SQL statements and supports basic SQL functionality. It provides RDBMS access by allowing you to embed SQL inside Java code. Because Java can run on a thin client, applets embedded in Web pages can contain downloadable JDBC code to enable remote database access.

Although JDBC was designed specifically to provide a Java interface to relational databases, you may find that you need to write Java code to access non-relational databases as well.

JDBC Architecture

Java application calls the JDBC library. JDBC loads a driver which talks to the database. We can change database engines without changing database code.

JDBC Basics - Java Database Connectivity Steps

Before you can create a java jdbc connection to the database, you must first import the

java.sql package.

import java.sql.*; The star ( * ) indicates that all of the classes in the package java.sql are to be imported.

Below is a list of classes and interfaces that are commonly used for writing JDBC code:

java.sql.Connection
java.sql.PreparedStatement
java.sql.ResultSet
java.sql.SQLException
java.sql.DriverManager
javax.sql.DataSource

Following are the classes / interfaces that will be used for doing a lookup for the DataSource in the servers context (JNDI Lookup).

javax.naming.Context
javax.naming.InitialContext
javax.naming.NamingException

Using Java there are two ways for connecting to a database. Either the DataManager class can be used or JNDI lookup can be done. JNDI lookup is mostly used in web based applications. Let us first look at how DataManager class can be used to connect to the database.

import java.sql.Connection;
import java.sql.Statement;           
import java.sql.ResultSet;           
import java.sql.SQLException;           
import java.sql.DriverManager;

public class TestDBConnectivity
{
public static void main(String argv[])
{
Connection con = null;
Statement stmt = null;
ResultSet rs = null;

String url = "jdbc:oracle:thin:@server:1521:TESTDB"
String sql = "Select count(*) as total from Books";
int totalBooks=0;

try
{
Class.forName("oracle.jdbc.driver.OracleDriver"); //load the driver
con = DriverManager.getConnection(url, "dbuser", "dbpasswd");
stmt = con.CreateStatement();
rs = stmt.executeQuery(sql);

if(rs.next())
totalBooks = rs.getInt("total");

System.out.println("Total number of books : " + total);
}
catch(ClassNotFoundException ex)
{System.out.println("ClassNotFoundException:\n"+ex);}
catch(SQLException ex)
{System.out.println("SQLException:\n"+ex);}
finally
{
try
{
stmt.close();
rs.close();
con.close();
}
catch(SQLException ex)
{System.out.println("SQLException:\n"+ex);} 
}
}
}

Now, let us look at how JNDI lookup works. For a small tutorial on JNDI please visit here.

For using a JNDI lookup first a resource has to be defined which can be done in the '\META-INF\context.xml' file. A sample of the 'context.xml' file is shown below:

import java.sql.Connection;
import java.sql.SQLException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class DAGConnection 
{
private DAGConnection(){}

public static java.sql.Connection getConnection()
{
Connection con=null;
Context ctx=null;
DataSource ds=null;
try
{
ctx = new InitialContext();
if(ctx == null)
{
System.out.println("Lookup failed!");
throw new RuntimeException("JNDI resource can not be found!");
}

ds = (DataSource) ctx.lookup("java:comp/env/jdbc/DBTEST");
if(ds==null)
{
System.out.println("DataSource could not be found!");
throw new RuntimeException("DataSource could not be found!");
}

con = ds.getConnection();
}
catch(NamingException ex)
{
System.out.println(ex);
return null;
}
catch(SQLException ex)
{
System.out.println(ex);
return null;
}

return con;
}
}

In the above example, the method getConnection() does a JNDI lookup and returns an object of type java.sql.Connection. Doing  JNDI lookup returns an object of type DataSource which in turn is used to create a connection to the database. You will need to place the JDBC drivers for the database in the classpath while using DriverManager and in the '\WEB-INF\lib' folder while doing a JNDI lookup.