JDBC

alt

 

Introduction As Per My Knowledge

Java Database Connectivity (JDBC) is a standard database access interface with SQL. It provides programmers with a uniform interface to various relational database systems regardless of differences in implementations. JDBC is part of Java 2 (the java.sql package). Database vendors ship JDBC drivers that enable accessing their databases from Java program through JDBC.

For example, DB2, IBM's RDBMS, provides a JDBC driver class, COM.ibm.db2.jdbc.app.DB2Driver, that translates JDBC calls into native DB2 calls and converts the DB2 results into a JDBC data structure. The class COM.ibm.db2.jdbc.net.DB2Driver is used for remote access of the database. Thus application programs that use JDBC can run with different RDBMSs without modification. A list of more than 150 JDBC drivers supplied by database vendors is maintained at Sun's Web site (http://industry.java.sun.com/products/jdbc/drivers).

If you want to use Open Database Connectivity (ODBC), a standard API for accessing a database, to connect Java applications to Microsoft Access or Excel, you can use the JDBC-ODBC bridge.

In this part of article, assume there is a sample database that contains a table named employee. The employee table has the schema shown in Table LOHIT1. The sample database and the employee table are included in DB2 as a sample, so you do not have to create them.

TABLE LOHIT1. SCHEMA FOR THE EMPLOYEE TABLE

COLUMN NAME

DATA TYPE

EMPNO

CHARACTER(6)

FIRSTNME

VARCHAR(12)

LASTNAME

VARCHAR(15)

JOB

CHARACTER(8)

EDLEVEL

SMALLINT

SEX

CHARACTER(1)

BIRTHDATE

DATE

SALARY

DECIMAL

BONUS

DECIMAL

Symbols such as CHARACTER and DATE show the data types of each column. For example, the data type CHARACTER(6)represents a six-letter character string, while VARCHAR(12) represents the data type of a character string that is at most 12 characters long. We use the data in this table as shown in Table LOHIT2, which is based on the schema in Table LOHIT1

TABLE LOHIT2. CONTENT OF THE EMPLOYEE TABLE

EMPNO

FIRSTNME

LASTNAME

PHONENO

JOB

SEX

BIRTHDATE

SALARY

BONUS

000010

SHILI

HAAS

3978

PRES

F

1933-08-24

52750.00

1000.00

000020

MICHAEL

THOMPSON

3476

MANAGER

M

1948-02-02

41250.00

800.00

000030

SALLY

KWAN

4738

MANAGER

F

1941-05-11

38250.00

800.00

000050

JOHN

GEYER

6789

MANAGER

M

1925-09-15

40175.00

800.00

000060

IRVING

STERN

6423

MANAGER

M

1945-07-07

32250.00

500.00

000070

EVA

PULASKI

7831

MANAGER

F

1953-05-26

36170.00

700.00

000090

EILEEN

HENDERSON

5498

MANAGER

F

1941-05-15

29750.00

600.00

000100

THEODORE

SPENSER

0972

MANAGER

M

1956-12-18

26150.00

500.00

000110

VINCENZO

LUCCHESSI

3490

SALESREP

M

1929-11-05

46500.00

900.00

000120

SEAN

O'CONNELL

2167

CLERK

M

1942-10-18

29250.00

600.00

Before creating a sample program that accesses the employee table by using JDBC, let's look at the functions of JDBC

 

 

JDBC Package

JDBC is provided by the java.sql package. Tables Here is the lists of classes, interfaces, and exceptions, respectively.

 

Classes in the java.sql Package

  • Date: A subclass of java.util.Date and a thin wrapper of the SQL DATE value.
  • DriverManager: A class that provides basic services for managing multiple JDBC drivers, such as loading drivers and logging on.
  • DriverPropertyInfo:  A class for accessing the properties of loading drivers.
  • SQLPermission: A class used when Security Manager inspects an applet that calls the setLogWriter() method.
  • Time: A subclass of java.util.Date and a thin wrapper of the SQL TIME value.
  • Timestamp: A subclass of java.util.Date and a thin wrapper of the SQL TIMESTAMP value.
  • Types: A class that defines SQL data types such as INTEGER and VARCHAR.

Interfaces in the java.sql Package INTERFACE

  • Array: An interface that represents the SQL ARRAY type.
  • BLOB: An interface that represents the Binary Large Object (BLOB) type.
  • CLOB: An Interface that represents the SQL Character Large Object (CLOB) type.
  • CallableStatement: An interface used for executing a stored procedure of SQL.
  • Connection: An interface that represents a session with a specific database. It is created by calling the Driver#connection() method.
  • DatabaseMetaData: An interface used for accessing the properties of a database as a whole
  • Driver: This is the main body of a JDBC driver. An implementation-specific driver must implement this interface.
  • Ref: An interface used to access the SQL structured type.
  • PreparedStatement: A subinterface of Statement that represents a compiled query for efficiently executing the same query many times.
  • ResultSet: An interface that provides access to a table of data generated by executing an SQL query using a Statement object.
  • ResultSetMetaData: An interface used for accessing the properties of query results, such as data types and column names.
  • SQLData: An interface used to access an SQL user-defined type.
  • SQLInput: An interface that represents an input stream to get an SQL user-defined type stored in database.
  • SQLOutput: An interface that represents an output stream to write attributes of a user-defined data type to the database.
  • Statement: An interface used for executing an SQL statement.
  • Struct: An interface that represents an SQL structured type.

Exceptions in the java.sql Package EXCEPTION

  • SQLException:  An exception that provides information on a database access error.
  • SQLWarning: An exception that provides information on a database access warning.
  • BatchUpdateException: An exception that occurs during a batch update operation.
  • DataTruncation: An exception that occurs when JDBC unexpectedly truncates data.
Let me write a sample program by using JDBC. First, I need to import the JDBC package as follows:
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.SQLException;

Loading a JDBC Driver

The second step in writing our sample program is to load a JDBC driver. We do this by telling DriverManager, which manages JDBC drivers and provides an appropriate driver for the database. There are a few different ways to achieve this, but the simplest is to use the Class.forName() method as follows:
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");

In this case, we used the JDBC driver for DB2. The class name must be replaced when you use other database implementations. The coding style is something strange. What happens when the method is called?

The static method forName(), defined in the class Class, generates a class object of the specified class. How is the JDBC driver object registered with DriverManager? Any JDBC driver has to have a static initialization part that is executed when the class is loaded, as shown next. As soon as the class loader loads this class, the static initialization is executed, which automatically registers it as a JDBC driver with DriverManager.

public class DB2Driver {
public DB2Driver() {
...
}
static {
try  {
DriverManager.registerDriver(new DB2Driver());
return;
} catch(SQLException sqlexception)
}}
Some drivers do not automatically create an instance when the class is loaded. If forName() alone does not create a driver instance for you, you may need to explicitly create an instance as follows:
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance();

Connecting to a Database

With the driver in place, we next need to specify the data source we want to access. In JDBC, a data source is specified by a URL with a scheme jdbc.

Subprotocol represents the type of the data source, which is normally the name of the database system, such as db2 and oracle. Subname is used to specify information for the database. The contents and syntax of subname depend on the subprotocol.

For example, to access a table named sample stored in IBM's DB2 on a local machine, you need to create a URL as follows:

String url = "jdbc:db2:sample";
If the database is on a remote machine, the URL would look like this:
String url = "jdbc:db2:monet.trl.ibm.com/sample";
You connect the database using this URL by calling the getConnection() method.
// Userid and password are specified as
// System properties
String userid = System.getProperty("chap11.userid");
String password =System.getProperty("chap11.password");
// Connects with default username and password
Connection con = DriverManager.getConnection(url,userid,password);
Often databases are protected by a user name and password for proper access control. With JDBC, you can specify your user name and password when connecting to a database as follows. If a user name and password are not specified (see the previous code fragment), a default user (login user) name and password are used.
// Default username is used
Connection con = DriverManager.getConnection(url);

Submitting a Query

Once a connection is established, we can submit queries to the database. However, we first need to create a Statement object by calling the createStatement() method of the Connection object:
Statement stmt = con.createStatement();
Now we are ready to submit an SQL query. You may need to be familiar with SQL to do this. Call the executeQuery() method with an SQL statement as its only argument, and you get the Resultset object that contains the result. In this case, all records in the employee database are returned.
String SQLquery = "SELECT * FROM EMPLOYEE";
// Gets result of the query
ResultSet rs = stmt.executeQuery(SQLquery);

The class ResultSet defines a number of methods for accessing the result. The result set is basically a sequence of rows, over which we can iterate using the next() method. The result set maintains a cursor to remember the current row in the result set. A call of the next() method advances this cursor to the next row until the end of data, where next() returns null. Within the cursor row, you can access the value of each column by specifying either the index number of the column or the name of the column. The getXX methods, where XX represents data types such as Int and String, can be used to access each column.

The following code fragment shows how to retrieve the first column (EMPNO) and second column (FIRSTNAME) of the result set.

while (rs.next()) {
String firstColumn  = rs.getString(1);
String secondColumn = rs.getString(2);
System.out.print(firstColumn);
System.out.print(" " + secondColumn);
System.out.print("\n");
}
Listing LOHIT1 shows a sample program that accesses the employee table in the sample table.
Listing LOHIT1 Program to access the employee table, appendixD/JDBCSample.java
package appendixD;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.SQLException;
class JDBCSample {
static {
try {
// Register the driver with DriverManager
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String argv[]) {
try {
// URL is jdbc:db2:dbname
String url = "jdbc:db2:sample";
// Userid and password are specified as
// System properties
String userid = System.getProperty("appendixD.userid")
String password = System.getProperty("appendixD.password");
// Connects with default username and password
Connection con = DriverManager.getConnection(url,userid,password);
// Creates statement
Statement stmt = con.createStatement();
String SQLquery = "SELECT * FROM EMPLOYEE";
// Gets result of the query
ResultSet rs = stmt.executeQuery(SQLquery);
// Displays Result
while (rs.next()) {
String firstColumn  = rs.getString(1);
String secondColumn = rs.getString(2);
System.out.print(firstColumn);
System.out.print(" " + secondColumn);
System.out.print("\n");
}
rs.close();
stmt.close();
} catch(SQLException e) {
e.printStackTrace();
}}}

Executing this program generates the following output.

R:\samples>java -DappendixD.userid="db2admin" -DappendixD.
password="db2admin" appendixD.JDBCSample
000010 SHILI
000020 MICHAEL
000030 SALLY
000050 JOHN
000060 IRVING
000070 EVA
000090 EILEEN
000100 THEODORE
000110 VINCENZO
000120 SEAN

This section of article introduced a program using JDBC. This program is quite simple but shows a typical pattern appearing in large applications. The next section describes other functions of JDBC by using more complex programs.

Using a Connection Pool

We showed how to connect to a database in the previous section. However, connecting to a database is expensive work for most Web applications. Therefore, application servers provide various ways for managing database connections and reducing the overhead for the connections. However, the implementation is vendor-specific, and it is an obstacle to developing vendor-independent systems.

To resolve this issue, JDBC supports the javax.sql.DataSource and javax.sql.ConnectionPoolDataSource classes, which abstract low-level connection information. These classes are distributed separately as an optional package of JDBC .

By using the connection pool, you do not need to specify the database URI in a program. Instead, you use the Java Naming and Directory Interface (JNDI) to bind a declared name to a database. It makes a program more robust for changing database settings. The connection pool also provides a way to control the number of connections. We recommend that you use the connection pool if it is available in your middleware, such as an application server.

However, the detailed setting of connection information still depends on implementations of JDBC drivers. The function is now supported mainly by J2EE-compliant application servers, so it may be available when you are developing applications on an application server. In the other sections in this appendix, we use sample programs that connect to database without using the connection pool, as shown in Listing LOHIT1.

On the server side, we should bind an actual database name with a JNDI name. If we are using DB2, the code for the binding looks like this.
import javax.naming.Context;
import javax.naming.InitialContext;
import COM.ibm.db2.jdbc.DB2DataSource;
// Connects with default username and password
DataSource ds = (DataSource)Class.forName(
"COM.ibm.db2.jdbc.DB2DataSource").newInstance();
// Sets database-specific info. It depends on JDBC driver
// implementations
(DB2DataSource)ds).setDatabaseName("sample");
Context ctx = new InitialContext();
// Binds JNDI name and DataSource instance
ctx.bind("jdbc/SampleDataSource", ds);
...

The Context interface is used to specify a name context. A JNDI name is managed by the context. The Context object is initiated by calling a constructor of the InitContext class. An implementation of JNDI is provided as a provider. There are some implementations of the JNDI provider based on Lightweight Directory Access Protocol (LDAP), an ordinal file system, and so on.

The class COM.ibm.db2.jdbc.DB2DataSource is an implementation of DataSource and provides some (implementation-specific) methods to set database information. After setting the information, you register a JNDI name by using the bind() method. To learn the details of the connection pool, please consult the vendor-specific APIs for the DataSource implementation, or consult the manuals for the application server you are using.

The following is a code fragment for the client side to get a database connection.
import javax.naming.Context;           // JNDI
import javax.naming.InitialContext;
import java.sql.Conncection;
import javax.sql.DataSource;           // JDBC 2.0 Optional
import COM.ibm.db2.jdbc.DB2DataSource; // DB2 specific
...
// Connects with default username and password
Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("jdbc/SampleDataSource");
Connection con = ds.getConnection("db2admin", "db2admin");
...
You can get a DataSource object with the JNDI API, and it supplies a Connection object. You can see there is no database URL like jdbc:db2: sample in the previous code fragment. The string jdbc/SampleDataSource shows a JNDI name for the database, and it is used to address the database instead of the database URL.



Like it on Facebook, +1 on Google, Tweet it or share this article on other bookmarking websites.

Comments (0)

There are no comments posted here yet