JDBC is a standard and open application programming interface for accessing databases from the JAVA programming language. It allows JAVA programs to use SQL requests that will access databases without having to know the proprietary interfaces to the database. A JDBC driver handles the SQL requests submitting through the JDBC interface and converts them into request the databases the driver supports will understand.
The JDBC classes and interfaces are declared in java.sql. The classes are the common components of JDBC. The main class is the DriverManager. You use the DriverManager class to make a connection to a database. Other classes in java.sql are used for date handling, exception handling, and common constants. The interfaces in java.sql are the blueprints used by JDBC driver developers to create a JDBC driver. Each JDBC driver must contain classes that implement the interfaces in java.sql. A typical JDBC driver is a set of these classes, plus some support classes, contained in an archive.
Basic data access through JDBC can be accomplished using only 4 JDBC classes. They are the DriverManager class, the driver’s Connection class, the driver’s Statement class, and the driver’s ResultSet class. Because each of the driver’s classes are implementations of the interfaces described in java.sql, you can access these classes through the interfaces. This means that you only need to specify which driver to use during the connection request. After that the code is driver independent.
The first step in using a JDBC driver is to register it with the DriverManager. A common way to do this is shown in figure 1a.
The forName method of the Class class returns a Class object associated with the class with the given string name. You really don’t need this, but this code will force the class named by the string to be loaded. Each driver is written to register itself with the DriverManager when loaded.
Fig 1a. Registering the
driver.
Class.forName(
"com.minisoft.jdbc.MSJDBCDriver" );
Fig 1b. Connecting using a datasource.
Connection
con=DriverManager.getConnection("jdbc:MSJDBC://127.0.0.1:30504/MSDB");
Fig 1c. Connecting using connection properties.
String url = "jdbc:MSJDBC://127.0.0.1:30504/";
Properties p = new Properties();
p.put( "Server", "data.minisoft.com" );
p.put( "Server Port", "31100" );
p.put( "User", "MGR" );
p.put( "User Password", "HEREYAGO" );
p.put( "Account", "MINISOFT" );
p.put( “Group”, “MM” );
p.put( "Database0", "MSDB,DBPSWD,1,5" );
Connection con = DriverManager.getConnection( url, p );
Fig 1d. Creating a Statement object.
Statement stmt = con.createStatement();
Fig 1e.
Executing a query that creates a ResultSet object.
ResultSet rs = stmt.executeQuery( “SELECT * FROM CUSTOMERS” );
Fig 1f.
Retriving data from the result set.
while ( rs.next() ) {
s = rs.getString( “CUSTOMER_NUMBER” );
System.out.println( s );
s = rs.getString( “CUSTOMER_NAME” );
System.out.println( s );
s = rs.getString( “ADDRESS1” );
System.out.println( s );
s = rs.getString( “ADDRESS2” );
System.out.println( s );
s = rs.getString( “CITY” );
System.out.println( s );
s = rs.getString( “STATE” );
System.out.println( s );
s = rs.getString( “COUNTRY” );
System.out.println( s );
s = rs.getString( “ZIP” );
System.out.println( s );
s = rs.getString( “DATE” );
System.out.println( s );
}
Fig 1g.
Cleaning up.
rs.close();
stmt.close();
con.close();
Figure 1. Using the basic 4 classes to retrieve data from an Turbo Image database
Now that the driver is registered with the DriverManager, you can request a connection to a database. The getConnection method of the DriverManager class will do this. This is illustrated in figure 1b.
The string parameter is a url which describes the connection. The first part “jdbc” is the main protocol. The second part “MSJDBC” is a sub-protocol that identifies the driver. The DriverManager will use the sub-protocol to query each registered driver to see if the driver handles this sub-protocol. The third and subsequent parts identify how to connect to the database. This is driver specific. In this case, using MiniSoft’s JDBC driver, the third part is specifying an IP address and port number, while the fourth part is specifying a datasource. The datasource contains the HP3000 specific information to connect to the database(s).
Drivers may also support another form of getConnection, in which connection properties can be passed. This form of getConnection is shown in figure 1c.
Each property and its meaning are driver defined. By using this form of getConnection you don’t need a datasource set up somewhere. You can specify everything that is needed to connect to the database as a property.
The getConnection method returns an object from the selected driver that conforms to the Connection interface. From now on, objects created by the driver that conform to an interface in java.sql, will be referred to by their interface name, not the internal name of the class in the driver. This is because you never need to know what the internal name is, you always access the object through its interface. The Connection object can now be used to create a Statement object as shown with figure 1d.
The createStatement method of a Connection object returns a Statement object. The Statement object can now be used to execute a SQL query with the executeQuery method. This is illustrated in figure 1e.
This will execute the SQL statement passed as a parameter and create a ResultSet object. The ResultSet object can now be used to retrieve the results of the SQL statement as shown in figure 1f.
The Next method fetches the next record from the result set. The getString method is used to return data from a column. The parameter can be either a column name as a string or a column number ( 1 based ). The getString method is normally used to return alphanumeric columns, although some drivers will return any data type as a string. If you need to retrieve the data in its normal data type, there are get... methods for all the various data types.
It is good practice to exit a program gracefully, although the JDBC driver should take care of things if you don’t. Figure 1g demonstrates the code needed to clean up.
Typically when the close method of a Connection object is called it will call the close method of any Statement objects it created. Similarly, when the close method of a Statement object is called it will call the close method of any ResultSet objects it created. Given this scenario, you could just call con.close.
A complete example that puts together all the above code pieces with added exception handling is shown in figure 2.
import
java.sql.*;
public class FirstDBAccess
{
public static void main(String[] args)
{
try {
Class.forName(
"com.minisoft.jdbc.MSJDBCDriver" );
String url =
"jdbc:MSJDBC://127.0.0.1:30504/MSDB";
Connection con =
DriverManager.getConnection( url );
try {
Statement stmt =
con.createStatement();
String query = "SELECT *
FROM CUSTOMERS";
ResultSet rs =
stmt.executeQuery( query );
while ( rs.next() ) {
s = rs.getString(
“CUSTOMER_NUMBER” );
System.out.println( s );
}
stmt.close();
}
catch ( SQLException e2 )
{
System.out.println( e2 );
}
con.close();
}
catch ( SQLException e0 )
{
System.out.println( e0 );
}
catch ( ClassNotFoundException e1 )
{
System.out.println( e1 );
}
}
}
Figure 2. A complete application that retrieves data from a TurboImage database.
The exception handling catches any exceptions thrown by the JDBC methods. Many of the methods in JDBC can throw a SQLException. Printing out the exception will typically show the error message.
In many situations you will want to execute essentially the same statement a number of times. The only difference between each execution of the statement might be some selection criteria or update values. For example, if you needed to retrieve customer information by customer number based upon a customer number entered by the user. When the user requested customer number ‘000001’, you could build a string that contained “SELECT * FROM CUSTOMERS WHERE CUSTOMER_NUMBER = ‘000001’” and execute it. Then when the user requested customer number ‘000002’, you would build a string that contained “SELECT * FROM CUSTOMERS WHERE CUSTOMER_NUMBER = ‘000001’” and execute it. This method is very inefficient because the driver will have to compile essentially the same statement many times. The preferred way to accomplish this task is to use a statement that contains parameters and prepared ( compile ) it. Once prepared, you can supply values for the parameters and execute the statement as many times as are needed without compiling it again. Instead of using the createStatement method of a Connection object, you use the prepareStatement method. Its one parameter is a string that contains the SQL statement to prepare. The prepareStatement method returns a PreparedStatement object, which is a subclass of the Statement class. The PreparedStatement class has set… methods to set the values of parameters. Figure 3 illustrates preparing a statement, setting its parameters, and executing it many times.
void
DoneOneTime()
{
...
Connection con = DriverManager.getConnection(
url );
String query = "SELECT * FROM
CUSTOMERS WHERE CUSTOMER_NUMBER = ?";
PreparedStatement stmt =
con.prepareStatement( query );
…
}
void DoneManyTimes( String CustomerNumber )
{
stmt.setString( 1, CustomerNumber );
ResultSet rs = stmt.executeQuery();
...
}
Figure 3. A prepared statement with parameters.
SQL statements use the question mark (‘?’) to mark the position of parameters. The parameters are numbered, starting at 1, in the order they appear in the statement. The setString method’s first parameter is the parameter number, and its second is the value for the parameter. All parameter values can be cleared with the clearParameters method.
Metadata is data which describes data. There are two types of metadata objects that a JDBC driver can provide. One is based upon the DatabaseMetaData interface and the other is based upon the ResultSetMetaData interface.
The DatabaseMetaData class mainly contains methods to get information about a database. The most common information is the names of the tables in the database, and the layout of those tables. Other less frequently used information is access privileges and the relationships between tables. A DatabaseMetaData object is created with the getMetaData method of a Connection object. Figure 4 illustrates using a DatabaseMetaData object to retrieve the names of all the tables in a database.
The getTables method returns a ResultSet object that is used to retrieve the information about the tables. Each row in the result set has 5 columns as follows:
Depending to the driver, the TABLE_CAT and/or the TABLE_SCHEM columns may be null indicating that these are not attributes of the database the driver supports. Figure 4 illustrates loading table names into a list box.
...
Connection con = DriverManager.getConnection( url );
Choice tableNames = new Choice();
DatabaseMetaData md = con.getMetaData();
String[] types = { "TABLE"
};
ResultSet mrs = md.getTables( null,
"", "", types );
while ( mrs.next() ) {
tableNames.addItem( mrs.getString( 3
) );
}
}
...
Figure 4. Loading table names into a list box.
The ResultSetMetaData class contains methods to get information about a result set. This information can be such things as the number of columns in each row of the result set and the layout of each column. This information is very useful to programs that need to dynamically create the layout for displaying data from a database. A ResultSetMetaData object is created with the getMetaData method of a ResultSet object. Figure 5 shows how to get and use the data type of a result set column.
String s;
int i;
...
Connection con = DriverManager.getConnection( url );
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery( “SELECT * FROM CUSTOMERS” );
ResultSetMetaData md = rs.getMetaData();
while ( rs.next() ) {
int col;
for ( col = 1; col <=
md.getColumnCount; ++col ) {
switch ( md.getColumnType( col ) ) {
case Types.CHAR:
s = rs.getString( col );
...
break;
case Types.INTEGER:
i = rs.getInt( col );
...
break;
...
}
}
}
Figure 5. Using ResultSetMetaData to get a column’s data type.
JDBC can be used to add, update, and/or delete records in a table. The executeUpdate method of a Statement ( or PreparedStatement ) object is used to execute SQL INSERT, UPDATE, and DELETE statements. The return of the executeUpdate method indicates the number of records affected by the SQL statement. The setting of auto-commit for the Connection object determines if each statement is committed automatically, or if an explicit commit or rollback must be done. If auto-commit is on, then each statement executed with executeUpdate method will be committed immediately. If auto-commit is off, a commit or rollback will only be done when a commit or rollback method of the Connection object is called. By default, new connections start with auto-commit on. Different drivers handle locking, transaction isolation, and concurrency differently. The driver’s documentation will need to be consulted to determine how the driver behaves, and how compatible it will be with other applications that are accessing the database. Figure 6 demonstrates adding a customer record to the customers table, while querying and updating the next value for customer numbers.
Connection con = DriverManager.getConnection( url );
con.setAutoCommit( false );
...
Statement stmt1 = con.createStatement();
ResultSet rs = stm1.executeQuery( “SELECT NEXT_NUMBER FROM NEXT_NUMBERS WHERE
CATAGORY = ‘CU’” );
rs.next();
int nextCust = getInt( 1 );
PreparedStatement stmt2 = con.prepareStatement( “INSERT INTO CUSTOMERS (CUSTOMER_NUMBER,
...) VALUES (?,...)”);
stmt2.setInt( 1, nextCust );
...
stmt2.executeUpdate();
PreparedStatement stmt3 = con.prepareStatement( “UPDATE NEXT_NUMBERS SET
NEXT_NUMBER = ? WHERE CATAGORY = ‘CU’”);
stmt3.setInt( 1, ++nextCust );
stmt3.executeUpdate();
con.commit();
...
Figure 6. An INSERT and UPDATE in a single transaction.
Different drivers have different methods of deployment. Also some drivers allow a number of different deployment options. Also a driver may or may not be pure JAVA. It is important to have a pure JAVA driver when you need to use the driver in applets, since the driver will need to be downloaded to the client, just as the applet is. Actually if all of the code that makes up a driver is not pure JAVA, it still may be possible to use the driver in an applet. All that is needed is the portion of the code that implements the JDBC interfaces must be pure JAVA and not call other functions that are not JAVA. Typically drivers that use this approach use a network connection to access non-JAVA code on machines other than the client.
The basic models for deploying a JDBC driver are 1-tier, 2-tier, and 3-tier. The 1-tier method is used for JAVA applications that execute on the same machine that the database resides on. In this mode, it is not necessary for the JDBC driver to be pure JAVA. An example of using a JDBC driver in this model, would be to write CGI scripts in JAVA that access databases on a HP3000 web server.
The 2-tier model can apply to a couple of different scenarios. The principle is that the JAVA code using the JDBC driver is executing on machine that the database does not reside on, but the machine that the database does reside on is reachable via a network connection. The most common use would be for applets that are served from a HP3000 web server. The portion of the driver that implements the JDBC interfaces, and hence must be downloaded to the client with the applet, must be pure JAVA. The driver makes connections back to the HP3000 web server to access databases that reside on it. Since the driver is making connections back to the machine that served the applet, JAVA’s security restricting how applets can access the network are satisfied. This scenario can be turned into a n-tier deployment by using proxy servers between the client and the HP3000, but conceptually it operates the same, just slower. Another scenario is creating an application or servlet that connects to the HP3000 via the network to access its databases. In the application form, this is a typical client/server application. A servlet would connect the HP3000 via the network to access its databases, but could serve many different clients in conjunction with a web server. The clients only need to be aware of the web server, not the HP3000. This means that the HP3000 can be isolated on a network that is reachable by the web server, but not by the clients. In this form, there is no applet to download, therefore the driver would not have to be pure JAVA. Eliminating downloading of applets will likely improve the responsiveness of a web application and may reduce network traffic.
The 3-tier model, like the 2-tier model can apply to the applet, client/server application, and servlet scenarios. The main difference is that a 3-tier driver, will divide itself up to make the best use of running on 3 tiers. The part of the driver that implements the JDBC interfaces will be very small, which will decrease download time when used from an applet. The middle tier part of the driver will handle many tasks without the aid of the HP3000. Typical tasks suited for the middle tier are statement parsing and compiling, data caching, transaction control, and metadata caching. The HP3000 tier’s main task is accessing the databases. The two main benefits of a 3-tier deployment are security and reducing the load on the HP3000. Like clients connecting to a servlet using the 2-tier model, all clients using the 3-tier model only need have access to the middle tier machine. The network between the middle-tier machine and the HP3000, can therefore be very restrictive and secure. The middle tier also relieves the HP3000 from performing many tasks. For some applications that execute alot of different SQL statements, having this task performed by the middle tier can have a very significant effect on the HP3000.
Until recently JAVA development has consisted of using old style text editors and command-line commands for compiling and testing. Recent releases of some JAVA development tools have introduced visual-oriented JAVA development. The most recent releases of these tools now include wizards and other visual design tools for creating JAVA code that uses JDBC. Many of the tools now include data-aware controls as components that use JDBC to automatically transfer data between the controls and a database. Many of these advances have been made in a rather short time, and the expectation is that it will continue. End-user tools, however, have not yet shown up in the market. It is reasonable to expect that a number of JAVA based tools that use JDBC, such as report writers, will come to market in the near future. Something that might be very useful is a JAVA version of Crystal Reports able to run as a servlet on any kind of web server!