Introduction to Java Database Connectivity

Saikat Goswami's picture
articles: 

In this article, we discuss the different ways in which Java can communicate with a database. We will talk about the traditional different kinds of drivers and also discuss some code to connect to a database in order to run queries.

Introduction

Java runs on a Java Virtual Machine (JVM). JVM translates your application code to bytecodes. A database is a separate software system. In order for these two to talk, they need to have a communication channel. Sun offers, through its J2SE, a package entirely dedicated for performing database-related operations. This is the JDBC package.

Part I - Concept of a Driver

Just the same way, your printer needs a driver, or maybe, your new soundcard needs a driver for the underlying hardware to understand, a database needs a driver. A database can be accessed by proprietary API's (Application Programming Interfaces). The Java Programmer Joe does not know the intricate API's of the database. Here is where the concept of driver comes in. The Java programmer makes JDBC calls from his program. The JVM translates the code to the database API. One needs a database driver either from a database vendor, or a J2EE server vendor.

There are four kinds of industry-standard JDBC drivers:

Type 1 - JDBC-ODBC Bridge

The JDBC-ODBC bridge (this type of driver is called the JDBC-ODBC bridge) is responsible for translating JDBC communication to ODBC language calls.
23_jdbc_1.gif

Type 2 - Both Native and Java Driver

Type 2 Drivers are a layer less than Type 1. Consider the diagram below:

23_jdbc_2.gif

The driver translates JDBC calls to database-specific API's. A Type 2 driver needs to be installed on both the JVM and the machine which hosts the database. The Oracle OCI JDBC driver is a Type 2 driver. This OCI driver requires an installation of Oracle's SQL*Net to provide communication to the database server.

Type 3 - Database Server Driver

Type 3 drivers connect multiple clients to multiple databases by being an intermediate database server.

23_jdbc_3.gif

Type 4 - 100% Java Driver

23_jdbc_4.gif

As you see, this driver goes through the least number of stopping stations before talking to the database. Consequently, this is the most efficient in terms of database-access time. Oracle provides a Type 4 driver, which is commonly referred to as the "thin" JDBC driver. One can connect with the Thin Driver without SQL*Net installed on the client side.

Part II - Making the Connection

Now that you have installed a driver, let us see how we can access the database using core JDBC API.

A driver implements the java.sql.Driver interface. Drivers have to be registered with the DriverManager (java.sql.DriverManager class). The DriverManager is a static class, and a driver is registered using the method:

public static void registerDriver(java.sql.Driver dr);

A driver is loaded in your application using reflection:

try{
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   ...
   ...
}
catch (ClassNotFoundException cnfe){ }

Concept of a JDBC URL

URL (Universal Resource Locator) is a unique string to identify a resource. Hence, a JDBC URL is a string that uniquely identifies a database. A JDBC URL looks like:

jdbc: weblogic: oracle

The first part will always be "jdbc". The second token is the name of the driver or database. You will get the exact URL from the application server documentation.

In your code, you will do something like this:

String url = "jdbc:weblogic:oracle";
String user= "java_developer";
String pwd = "oracle";

Connection con =  DriverManager.getConnection(url, user, pwd);

The getConnection() method is overloaded. Other method signatures are:

public static Connection getConnection(String url) throws SQLException;
public static Connection getConnection(String url, java.util.Properties prop)
	throws SQLException;

The Properties object will have properties such as auto-commit, etc.

Executing Queries

Now that you have a hold of a database connection, you will need to run queries against your database. The query has to be a statement. The connection object has methods:

Statement createStatement() throws SQLException;
public PreparedStatement prepareStatement(String sql) throws SQLException;

On the Statement and PreparedStatement, you can call:

execute();
executeQuery();

Although there are other variants of Statements and executes, the scope of this article is to give the basic overall of java connectivity to the database.

Executing the queries bring back a ResultSet object, which is then enumerated to retrieve results.

Summary

Sun maintains a searchable list of drivers at http://servlet.java.sun.com/products/jdbc/drivers. When you download a JDK, it ships with a Type 1 Driver.

More information about the Oracle specific JDBC drivers can be found in the Oracle/Java FAQ at http://www.orafaq.com/faq/jdbc

Prepared by Saikat Goswami, Boston, Massachussets, sai_nyc@hotmail.com

Comments

hi guys,
i don't see any harm explaining things once again and in a very 'simplified' manner. definitely it helps a beginner as the starting point rather than going through all the good old stories in a prep book or documentation. defintely interesting, whoever says the 'insight' of JVM communication why not write a article. i would not be surprised if it was 'taken' from documentation or a prep book :-))

Cheers,
deb

Hi Deb ,
people come to orafaq to troubleshoot the problems they are facing in projects (if you know what I am talking about, its good for you), rather than finding stuffs on tutorial for beginers.If you want to learn some basic stuff like ponting your url to Oracle Sid and manupulating through resultset, go to any tutorial site, and learn it. and if you understand what I meant by interJVM communications for the Oci Client (Which I doubt ;-) ). then to clarify you , what I meant is to write about stuff where in distributed architecture how the calls get wired along the JVM, and write about it from your experience. If you still do not understand , stick to the basic. till i post that article.

Have Fun
cute

after fetching the records from the database, and storing records in the resultset, is it necessary to have database connection to move back and forth in the resultset object?or i can close the connection object?

which driver will be used in which situation? could u please tell me clearly?

Hi,

I think before writing any article, you must know exactly what you are writing about. The information you have given can be found in sun's document/or any prep book. But, you should given the insight of the inter JVM communications and how these driver worked on the ditributed environment.

Thanks for your effort, whatever you came up with.

-cute

Hello,

I am trying the same code, but getting error "Default driver not installed".

Can you pls help me out?

Abhi

Hai Sai,

A great Job.Truely helpful for me.

Pl give links to all your articles in a serail order.Like previous and next links of your article.

So that people like me can get the total concepts perfectly.

Thanks Sai and orafaq.

Hi Sai,
I really wonder y some people take pleasure in pointing out mistakes when there are none. No offense to who ever has a "cute" name but posting evil notes ;-)
Ray

Hi Cute,

No matter where we are, we always have room for improvement.

(1)I know *exactly* what I wrote about.

(2)The information presented in my articles are explained in *my* way. Anyone trying to understand technology has to consult documentation as a part of the learning process; I cannot think of any other way.

Thanks.

hi deb,

simply great job...

Thanks
amit