Java Database Connectivity

Java Database Connectivity (JDBC) provides a standard library for accessing databases. The JDBC API contains number of interfaces and classes that are extensively helpful while communicating with a database.

The java.sql package contains basic & most of the interfaces and classes.  We automatically get this package when we download J2SE. We have to import this package whenever there is requirement to interact with a relational database.

Here i am showing how to connect and communicate with Microsoft Access.

Connecting With Microsoft Access
=================================

Create Database

In start create a database “EmployeeRecord” using Microsoft Access. Create one table named “Employees”. Save the database.

Add some records into Employees table:

Setup System DSN

After this, setup a system Data Source Name (DSN). DSN is a name through which system recognizes the underlying data source. Navigation:
Start -> Control Panel -> Administrative Tools -> Data Sources (ODBC).
The ODBC Data Source Administrator window would be opened as shown below. Select System DSN tab.

Press Add… button and choose Microsoft Access Driver (*.mdb) from Create New Data Source window and press Finish button.

After that, ODBC Microsoft Access Setup window would be opened as shown in following diagram. Enter the Data Source Name employeeDSN and select the DB by pressing Select button. The browsing window would be opened, select the desired folder that contains the database (The database .mdb file) Press Ok button.

Basic Steps in using JDBC
=================================
There are 8 basic steps that must be followed in order to successfully communicate with a DB. Let’s have a look over all these steps:

1) Import required package

Import the package java.sql.* that contains useful classes and interfaces to access & work with database.

2) Load Drivers

Need to load suitable driver for underlying database.  Different drivers & types for different databases are available. For MS Access, load following driver available with j2se. 

Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

For Oracle, load the following driver.

Class.forName(“oracle.jdbc.driver.OracleDriver”); 

3) Define Connection URL

To get a connection, we need to specify the URL of a database (Actually we need to specify the address of the database which is in the form of URL). As we are using Microsoft Access database  and we have loaded a JDBC-ODBC driver. Using JDBC-ODBC driver requires a DSN which we have created earlier and named it employeeDSN. So the URL of the database will be 

String conURL = “jdbc:odbc:employeeDSN”; 

4) Establish Connection with Database 

Use DriverManager to get the connection object. The URL of the database is passed to the getConnection method.

Connection con = DriverManager.getConnection(conURL);

If Database requires username & password, you can use the overloaded version as shown below:

String usr = “username”;

String pwd = “pass”;   

Connection con = null;

con = DriverManager.getConnection(conURL, usr, pwd);

5) Create Statement

A Statement object is obtained from a Connection object.

Statement stmt = con.createStatement( ); 

6) Execute a Query

The next step is to pass the SQL statements & to execute them. Two methods are generally used for executing SQL queries. These are: 

executeQuery(sql) method: Used for SQL SELECT queries. Returns the ResultSET object that contains the results of the query and can be used to access the query results.

String sql   = “SELECT * from sometable”;

ResultSet rs = stmt.executeQuery(sql);

executeUpdate(sql) method: This method is used for executing an update statement like INSERT, UPDATE or DELETE. Returns an Integer value representing the number of rows updated.

String sql   = “INSERT INTO tablename ” + 

               “(columnNames) Values (values)” ;

int count = stmt.executeUpdate(sql);

7) Process Results of the Query

The ResultSet provides various getXXX methods that takes a column index or name and returns the data. The ResultSet maintains the data in the form of tables (rows & columns). The next method of ResultSet returns true or false depending upon whether the next row is available (exist) or not and moves the cursor.

while ( rs.next() ){ 

       //by using column name

       String name = rs.getString(“columnName”); 

       // or by using column index     

       String name = rs.getString(1);  }

8) Close the Connection

An opening connection is expensive; postpone this step if additional database operations are expected.

 con.close();

Result Set
=================================
A ResultSet contains the results of the SQL query. Represented by a table with rows and columns. It Maintains a cursor pointing to its current row of data and initially the cursor positioned before row (0). First row has index 1.

ResultSet Meta data
=================================
ResultSet Meta Data will help to find out things like: How many columns are in the ResultSet?, What is the name of given column?, Are the column name case sensitive?, What is the data type of a specific column?, What is the maximum character size of a column?

From a ResultSet (the return type of executeQuery() ), derive a ResultSetMetaData object by calling getMetaData() method as shown below:

ResultSetMetaData rsmd = rs.getMetaData();  

Now, rsmd can be used to look up number, names & types of columns

• getColumnCount ( ): Returns the number of columns in the result set.
• getColumnDisplaySize (int): Returns the maximum width of the specified column in characters.
• getColumnName(int) / getColumnLabel (int): The  getColumnName() method returns the database name of the column.
• getColumnType (int): Returns the SQL type for the column to compare against types in java.sql.Types

DataBaseMetaData
=================================
DataBase Meta Data  will help to find out things like: What SQL types are supported by DBMS to create table?, What is the name of a database product?, What is the version number of this database product?, What is the name of the JDBC driver that is used? Is the database in a read-only mode?.

From a Connection object, a DataBaseMetaData object can be derived.

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

DataBaseMetaData dbMetaData = con.getMeataData(); 

• getDatabaseProductName( ): Returns the name of the database’s product name.
• getDatabaseProductVersion( ): Returns the version number of this database product.
• getDriverName( ): Returns the name of the JDBC driver used to established the connection.
• isReadOnly( ): Retrieves whether this database is in read-only mode. Returns true if so, false otherwise.

JDBC Driver Types
=================================
JDBC Driver Types are divided into four types. Each type defines a JDBC driver implementation with increasingly higher level of platform independence, performance, deployment and administration. The four types are: 

JDBC – ODBC Bridge

Translates all JDBC calls into ODBC (Open Database Connectivity) calls and send them to the ODBC Driver. Generally used for Microsoft database. 

Native – API/partly Java driver

Converts JDBC calls into database-specific calls such as SQL Server, Informix, Oracle or Sybase. Partly-Java drivers communicate with database-specific API (which may be in C/C++) using the Java Native Interface. It has significantly better Performance than the JDBC-ODBC Bridge.

Net – protocol/all–Java driver

Follows a three-tiered approach whereby the JDBC database requests ()are passed through the network to the middle-tier server. Pure Java client to server drivers which send requests that are not database-specific to a server that translates them into a database-specific protocol. If the middle-tier server is written in java, it can use a type 1or type 2JDBC driver to do this.

Native – protocol / all – java driver

Converts JDBC calls into the vendor-specific DBMS protocol so that client application can communicate directly with the database server. Completely implemented in Java to achieve platform independence and eliminate deployment issues. Performance is typically very good.