Home

Architecture

WebApps

Database

Administration

Web Search

News

Java/J2EE Servers

Web Services

Enterprise

Learning

Contact Us

Java - EJB - J2EE -Servlets- JSP - JMS - JNDI- JDBC - RMI

Learn: JDBC

JDBC: How do I learn JDBC? Experiencing database power..

Updated Mar 15, 2002

QuickStart
Tutorials
Examples
Metadata
Prepared and Callable Statements
Connectivity and Drivers
Performance
OO Applications
Servlet Database Applications
RDBMS Vendor Specific
SQL-J
Book References		 Packages:
java.sql
javax.sql		 See also
Java Skyline: DataBase jGuru: JDBC FAQ . FirstSQL: Java and Database Synergy
  Real's Java How-to Database  Ken North: Articles by
JDBC, Java Data Base Connectivity, provide Java programs with a way to connect to and use relational databases. JDBC 1.0 was originally included in the JDK 1.1. Now, JDBC 2.0, included in Java 2 has been significantly enhanced.

Ken North provides this excellent overview of JDBC, in WebTechniques, August 1999. In April 1999, java.sun.com featured an introduction to JDBC 2.0: Business-Critical Connections: The JDBC API by Laura Geele Wang. A general introduction JDBC 2 is in the Sun Java Tutorial: JDBC Database Access by Maydene Fisher.

What you get out of learning JDBC is different for people with different backgrounds. If you know SQL and ODBC, you'll find JDBC is very straight forward and even easier. However, JDBC is the basis for nearly all interaction with relational data. So if you use Java and RDBMS, you will find JDBC highly important.

Quick Start

Very little code is required to make JDBC work. For a query you need to Load the driver, create a Connection, create a Statement, run the statement, and then use the ResultSet. How you get the driver loaded and get a connection depends on whether you are writing a "vanilla" JDBC program or writing inside a container - such as for a BMP Entity Bean. Here is an quick overview of both.

Vanilla JDBC To run a query you need to Load the driver, create a Connection, create a Statement, run the statement, and then use the ResultSet. This looks like this: JDBC 2.0 in a container When running inside an EJB container, you need to do a lot less work. The container creates all connections and places them in a connection pool. You retrieve a connection from a DataSource which you get by looking it up in the JNDI registry.


try {


Class.forName(JDBC Driver Class);

Connection con =
DriverManager.getConnection(DB URL);

Statement stmt = con.createStatement();
ResultSet rs= stmt.executeQuery(SQL Statement);
while (rs.next()) {

String mykeyfield= rs.getString(1);
String mydatafield= rs.getString(2);
// Do something like System.out.println(mykeyfield);
System.out.println(mydatafield);

}

} catch (Exception e) {
// Exception processing
} finally {

try { con.close(); }
catch (Exception e) {// Close exception}

}

ArrayList arrayList= new ArrayList();
try {

Context jndiContext = new InitialContext();
DataSouce ds =
jndiContext.lookup("java:/ConnectionPool");
Connection con =
ds.getConnection();

Statement stmt = con.createStatement();
ResultSet rs= stmt.executeQuery(SQL Statement);
while (rs.next()) {

String myfield= rs.getString(1);
String mydatafield= rs.getString(2);
// Do something like
arrayList.add(myfield, mydatafield);

}

} catch (Exception e) {
// Exception processing
} finally {

try { con.close(); }
catch (Exception e) {// Close exception}

}

Notes: JDBC Driver Class is the class name for the JDBC driver you are using. DB URL is the protocol, driver, location, port, instance name, for the database.
SQL Statement is a SQL query such as
"SELECT key, data FROM mytable."		 When using an EJB container, deployment descriptors outside of the application specify the JDBC Driver Class and DB URL for the connection pool you use called ConnectionPoolName. This way the container can be configured and a different database selected independently from your source code. When finished withthe

Tutorials

For just the basics (vanilla JDBC) - check out this example with the four basic steps of JDBC by Craig Kane at U. Colorado.

A three-step simple introduction is provided by Developer's Daily:

On WDVL, the Illustrated Encyclopedia of Web Technology, Selena Sol presents a very nice step-by-step tutorial on JDBC. It's easy to follow, after an unfortunate rough start consisting of caveats about Java you really don't need to read. The link provided starts just at the right place in the tutorial. (If you want to read all the disclaimers about Java, just click on the word "Previous Page").

         

Examples

The JavaSoft Press book JDBC Access with Java by Hamilton, Cattell, and Fisher also allows you to download JDBC examples. Also try the downloadable exampleAdhoc SQL using servlets from Java Skyline.

Metadata

Metadata is a highly useful feature of JDBC. Metadata provides you with information about the RDBMS or about a resultset that you have retrieved. Brett Spell gives us an article on the subject: Enhancing Database Code with Metadata in JavaPro, June 1999. Also, the Adhoc SQL servlet provides you with examples of both database and resultset metadata calls.

ResultSetMetadata and DatabaseMetadata enables, among other things to determine the data types of various database fields and columns. JDBC Recipe from Developers's Daily shows how to use ResultSetMetadata. Java Tip 82: Build data-type-independent JDBC applications by Sesh Venugopal from JavaWorld shows how to construct type-independent database tables using DatabaseMetadata.

Prepared and Callable Statements

To really understand the power and simplicity (compared with C/C++ ODBC for instance) of JDBC, just take a look at prepared statements and callable statements. The National Centre for Software Technology (India) provides very terse descriptions of Prepared Statement and Callable Statement. For a simple PreparedStatement example see San Diego State University CS 596: Client-Server Programming slides on JDBC - slide number 16 on this page.

Batch inserts One very common use of PreparedStatement is for doing large batches of inserts from an external data source. JDBC 2.0 supports this with addBatch and executeBatch(). The following sample reads a tab separated file and inserts it into an RDBMS table:

// Build ?,?,? section of insert command.
int ntokens=5; String qsep=""; String qstring="";

for (int i=1;i<=ntokens;i++) { qstring+=(qsep+qstring); qsep+","; }
String insertcommand="INSERT INTO MYTABLE VALUES ("+qstring+")";

String tabsep="\t"; // Set token separator to tab character. \t == (char) 9 in ASCII.

try {

PreparedStatement pstmt=con.prepareStatement(insertcommand);
BufferedReader binpt = new BufferedReader(new FileReader("myfile.txt"));
String record;
while ( (record=binpt.readLine())!=null ) {

StringTokenizer stkn = new StringTokenizer(record,tabsep);
int i=1;
while (stkn.hasMoreTokens() ) { pstmt.setString(i++,stkn.nextToken()); }
pstmt.execute(); // For JDBC 2.0: use pstmt.addBatch() here instead.

}
// For JDBC 2.0 insert "pstmt.executeBatch();" here.

} catch (Exception e) {
System.out.println("Exception "+e);
}

Check with the JDBC and database vendor to determine whether JDBC 2.0 has been implemented. There are also PreparedStatement insert and update examples in ClassicCity's Forum: JAVA and Oracle section. There are some additional examples and a CallableStatement example in this JDBC Presentation by Shitlesh Patel of the Dallas SCJD Study Group.

Note that if you are inserting into all columns, the column names are not required in the INSERT statement.

Large Objects: Many RDBMs's now support storage of large objects. PreparedStatements are required if you want to insert large objects into the database. Here's an excellent example of large object insertion from IBM (1999).


CallableStatements. Ken North provides an excellent overview on CallableStatements and stored procedures in Java, JDBC, Stored Procedures, And Server-mania, WebTechniques, May 1999. A discussion and several examples of CallableStatement are included JDBC for industrial-strength performance: Part 1 (Jan 2000) and Part 2 (Feb 2000) by Lennart Jörelid, JavaWorld. ClassicCity also has an Oracle Callable Statement example.

Third Eye Consulting provides a simple tutorial called JDBC Intricacies: PreparedStatements and CallableStatements, Sept 1998, by Christopher Bonham.

Performance

In a world where scalability is everything, performance is king. You may get relatively good performance by using a good Type 4 driver.

However, with a driver that is especially designed for the RDBMS you are using you can boost performance by following certain procedures. For instance:

  • Don't use "SELECT * FROM" in a production environment.
  • When performing frequent or repetitive operations, use and re-use a Prepared Statement.
  • Consider using stored procedures for operations that take place largely within the database.

By reusing Prepared Statements your program can operate much more efficiently. When the JDBC driver initially prepares a statement, it parses and error checks the statement and then sends execution plan information to the RDBMS, including the bindings of columns that you use in the statement. These operations are not repeated each time you re-use the PreparedStatement, resulting both in significant processing reductions and communication port traffic reductions. Richard Dettinger and Mark Megerian explain how important this is when using the IBM native driver in an IBM AS/400 article: JDBC Performance Tips.

You should also avoid constantly creating new connections and destroying connections by using connection pooling. Hans Bergsten provides ways to do connection pooling in Improved Performance with a Connection Pool, Sep 1998 from Web Developers Journal. Connection pooling is now a standard part of JDBC 2.

Database applications often operate best with several tiers that the work among several servers. Chad Darby explains the 3-tired JDBC Web architecture in Developing 3-Tiered Applications with Java Servlets by Chad Darby, Jan 1999 from Java Developers' Journal. I-Kinetics also presents some multi-tiered architecture material that involves JDBC with EJB and Corba.

OO Applications

For an OO perspective, Spilling the Beans: JDBC is Easy! by Danial Brookshier from Java Report provides some guidelines on encapsulation to isolate JDBC specific code. And Thomas Davis explains how to package JDBC into a facade pattern so that you don't get mired down in it in Clever Facade makes JDBC look easy from JavaWorld.

But, there are still things worth exploring in JDBC, such as metadata and connection controls even beneath such a facade. On JavaCats,Qusay H. Mahmoud presents A Persistant Storage for Java Objects.

Connectivity and the JDBC Driver:

The JDBC API is quite extensive but even so, most of its actual work goes on under the hood inside the JDBC driver. Which driver you use depends not only on which RDBMS you are using, but also on what kind of architecture you have or want to support. It's worth knowing about the four types of drivers and Ahmad Abualsamid explains them in Java Connectivity With JDBC from Network Computing, May, 1999. For production, you will probably want to use Type 4 or Type 3. Briefly, Type 4 JDBC drivers are generally single-tiered database clients. They are pure Java and generally the easiest to install.

Type 3 drivers provide additional networking and performance services. They are generally two-tiered or multi-tiered and they may or may not be pure Java. The JDataConnnect FAQ provides a useful comparison of Type 3 and Type 4 JDBC drivers. Merants Type 3 drivers additionally provides XA compatibility to enable distributed transaction processing.

Special usages: Connecting to an Excel spreadsheet: Important information on how to connect to an Excel Spreadsheet using the JDBC ODBC bridge can be found in this article entitled The truth about using ODBC with Excel (ScanSoft)

Loading the JDBC driver. To use the JDBC driver, you must 1) tell your program to load and activate the driver by class name and 2) specify where the driver is on the classpath.

1) Loading and activation is fairly simple. Each JDBC driver has a class name. For instance, Oracle's is "oracle.jdbc.driver.OracleDriver." Most recent versions of the JDBC specification require that all JDBC drivers must be loaded and activated with the Java program code:

Class.forName("jdbcdriverclassname");

2) To get to the driver class, the JDBC driver must be accessible from your program's class path. For a typical application, this just means specifying the driver's jar or zip file. For example:

java -classpath :.:<directory path>/jdbcdriver.jar MyProgram

Servlet and Enterprise Applications

Setup. To run JDBC from a servlet or EJB server you need to set the classpath for the servlet engine or EJB server to include the JDBC driver. This may seem obvious (especially if you just read the previous paragraph above), but you still have to remember to do it and that can require some getting used to. Also setting the classpath is different for each kind of servlet engine and EJB server.

For Tomcat, just copy the JDBC driver (jar or zip) into Tomcat's lib directory. Tomcat's startup script automatically includes the lib directory and all jars.

For other servlet engines refer to the instructions for the servlet engine. For example, IBM provides this overview and set of instruction for configuring S/390 Websphere to run JDBC with their DB2 database.

For servlets, when you get the "class not found" exception, just to make sure that you have actually remembered to set the classpath - and done it right, you may find this servlet program useful. It's called SysParam. All SysParam does is display System Properties from within your servlet engine. And one of the system parameters happens to be - for joy for joy - the class path - java.classpath. SysParam segments long strings that contain semi-colons or colons so that you can read the class path more easily.

Designing a servlet database application. When you write a servlet database application there are a number of design decisions you will need to make - such as:

  • How many database connections to have open
  • When to load the JDBC driver
  • Whether it's better to use EJBs or not
  • How to deal with and avoid long-running queries
  • What to do with database user-ids and passwords
  • Will this be an Intranet, Extranet, or public Internet application
  • How much database control do you want to give to the web user

Using EJB Servers. EJB servers are most beneficial if you are building an on-line transaction application. For applications that are strictly reporting or OLAP, you may find an EJB server may not as important.

Connections, Drivers, and User IDs. If you decide to use EJBs, many of the connection questions are taken care of by the EJB server - one of their advantages. EJB servers typically use connection pooling software to manage connections and provide setup tools or XML configuration files to manage JDBC drivers, connection URLs and passwords.

Servlet-only database applications. You can also use a connection pool manager with servlet-only database applications. Also, take a look at the preface and this tutorial from Java-beans.de on building a servlet-database application. Skyline Blvd provides a training and testing tool servlet called AdhocSQL.

DB Vendor Specific

Much JDBC material is to be found after you download a JDBC driver. It is usually worth your while to look at vendor specific training material since JDBC drivers ar likely to have some idiosyncracies about how you need to use them.
  IBM DB2 In Java Support in DB2 IBM provides some examples for using DB2.
  Informix See especially Working with JDBC by Jaya Khera (2001). See also the Informix by Example pages, and this online copy of the Informix JDBC Driver Programming Guide (1998).
  MySQL For instance, You can find tutorial information for MySQL here. TECHVA U. Geneva's Java CoffeeBreak provides a number of MySQL tutorial and connection examles.
  Oracle Oracle Technet provides several sample JDBC programs. The University of Pennsylvania CIS 550 course covers the Oracle JDBC driver and has discussion on Oracle cursors that show how to use the Oracle ROWID and RefCursor extensions. For Oracle 8i specific examples see Oracle 8i, Java, and the Mustang Index, Ken North, WebTechniques, Nov 1999. And for using JDeveloper, see Oracle 8i and JDeveloper Suilte by Ken North, WebTechniques, Dec 1999.
  PostgreSQL PostgreSQL Chapter 58. JDBC Interface Peter T. Mount
  BEA jdbcKona Using the jdbcKona Drivers for Weblogic 5.0 explains basic setup, shows PreparedStatement and CallableStatement examples and extended SQL examples using BEA's jdbcKona drivers for Oracle and SQL Server.

SQLJ

SQLJ provides Embedded SQL functionality for the Java language. Embedded SQL provides a way to bind a Database server procedure so that no interpretation is necessary and the SQL execution plan is known to the server ahead of time.

To get started see: SQL Embedded in Java by Ekkehard Rohwedder, JDJ May 2000. ACM SIGMOD 98 presents some learning materials and tutorials on SQLJ here. IBM provides some DB2 examples in DB2 UDB SQLJ Support.

Books

Highly Recommended: If you're serious about JDBC, I also recommend that you get the JDBC Developers Resource, 2nd Ed. by Art Taylor. Not exactly bed time reading, but it provides a complete reference to JDBC 1.0 with examples throughout. There is also extensive JDBC coverage in Java Enterprise in a Nutshell by William Crawford and Jim Farley, O'Reilly 1999.