WISETEL CONSULTING INC

1. What Is JDBC?

JDBC is a set of Java APIs that wrap SQL statements so that Java programmers can deal with any type of rational database in a unified “Java way”. No matter the underlying database is Oracle, Sybase or Infomix, as long as the vendor provides JDBC driver for its proprietary database, program written in JDBC can work with the database without code change. However, JDBC users still need to have enough knowledge about database and SQL to be able to write SQL statements and manage database correctly and efficiently. JDBC is considered low-level APIs. It is merely a different way of constructing SQL statements.

2. The First Step - Connect to Database

Let us start with a simple example.

import java.sql.*;

public class JdbcDemo {

   public static void main (String[] args) {

      String url = “jdbc:odbc://test.companyA.com:4880/MyDb”;

      String user_name = “Scooby”;

      String password = “Doo!”

      try {

         Class.forName (“jdbc.DriverX”);

      catch (Exception e) {

         System.err.println (“Failed to load database driver jdbc.DriverX.”);

         return;

      try {

         /* Use the driver that has been registered to DriverManager to connect to the database. */

         Connection conn = DriverManager.getConnection (url, user_name, password);

      catch (SQLException e) {

         System.err.println(“Failed to connect to database.”);

      finally {

         try {

            conn.close();

         catch (SQLException e) {

            e.printBackTrace();

In this example, url defines the location and name of the database to be connected to, where “jdbc” stands for JDBC protocol, “odbc” stands for ODBC sub-protocol, most likely it is JDBC-ODBC bridge driver, “test.companyA.com” is the machine address, “4880” indicates a particular TCP port, and “MyDb” identifies the database. Define user name and password as those used to log on to the database.

Class.forName is to get the class of name “jdbd.DriverX”. Since the class represents a database driver, this method actually loads the driver and registers it to the DriverManager. There can be more than one driver registered to the DriverManager.

After register all the drivers to DriverManager, we can open connections to the database, and DriverManager.getConnection does just that. Any loaded driver that can interpret the url will establish a connection to the database. The connection stays open until the close method is called.

3. Construct SQL Statements

Basically, we write the whole SQL statement as a Java string.

Create Table

String create_table_Person =

        “CREATE TABLE Person ” +

        “(Employee_Id SystemId NOT NULL,” +

        “ Last_Name CHAR(20) NOT NULL,” +

        “ First_Name CHAR(20) NOT NULL,” +

        “ Middle_Name CHAR(20) NULL,” +

        “ Age INT NULL)”;

Insert Data

String insert_data_person =

        “INSERT INTO Person ” +

  “(Employee_Id, Last_Name, First_Name, Middle_Name, Age) ” +

  “VALUES ” +

        “(101, ‘Bond’, ‘James’, ‘F’, 48)”;

Get Data

String get_data_person =

        “SELECT Last_Name, First_Name FROM Person ” +

        “WHERE Age < 65”;

Update Data

String update_data_person =

        “UPDATE Person ” +

        “Set Age = 49 ” +

        “WHERE Last_Name = ‘Bond’ and First_Name = ‘James’”;

Delete Data

String delete_data_person =

        “DELETE FROM Person ” +

        “WHERE Employee_Id = 101”;

4. Execute SQL Statements

There are two types of SQL statements, update and query. Update statements change the table structures and data on the database. Query statements retrieve data from the database without making any permanent changes.

To execute SQL statement using JDBC, first obtain a Statement object from the Connection object with the name, let’s say, conn.

Statement stmt = conn.createStatement();

Then call the executeUpdate method to execute an update statement, which returns the number of rows that have been modified.

int number_of_rows = stmt.executeUpdate(create_table_person);

/* Retrieve warnings here if any. */

stmt.close();

Or call the executeQuery method to execute a query statement, which returns a result set.

ResultSet rs = stmt.executeQuery(get_data_person);

/* Retrieve data from the result set here. */

/* Retrieve warnings here if any. */

stmt.close();

If part of the statement are variables, the statement string can be partial prepared, and before execute the statement, use the setXXX method to pass along the values for the variables.

PreparedStatement prepared_stmt = conn.PrepareStatement(

        “UPDATE Person ” +

        “Set Age = ? ” +

        “WHERE Last_Name = ? and First_Name = ?”);

prepared_stmt.setInt(1, 49);

prepared_stmt.setString(2, “Bond”);

prepared_stmt.setString(3, “James”);

boolean has_result_set = prepared_stmt.execute();

/* If result set returned, retrieve data from result set here. */

/* Retrieve warnings here if any. */

prepared_stmt.close();

The method execute is used when it is not clear whether the statement is an update or query. It returns true if there is a result set to process.

After a statement is executed, use the method close to release the database and JDBC resources associated with the statement, e.g. the result set. Otherwise, the statement will be automatically closed with corresponding database connection is closed.

5. Process Result Sets

Query statement retrieves data from database and returns the values in the form of result set. Result set is a set of rows; each row contains the data values. In JDBC, a result set is modeled as a ResultSet object, and there is an invisible curser moving through the rows. The next method moves the cursor from one row to the next, and the row with the cursor (the current row) can be used to retrieve the value. Cursor can be moved forward, backward, to any specific position or a position relative to the current row. Initially the cursor locates just before the first row, so usually the first thing to do is to call the next method to move the cursor to the first row.

After a particular row is located, we use the getXXX method to retrieve the value, where XXX stands for a appropriate type.

ResultSet rs = stmt.executeQuery(get_data_person);

while (rs.next()) {

   String lname = rs.getString(“Last_Name”);

   String fname = rs.getString(“First_Name”);

Here we use the table column name to retrieve the value. Another way is to use the table column number.

String lname = rs.getString(1);

String fname = rs.getString(2);

The following table is copied from Sun’s online Java Tutorial, which summarizes the legal (“L”) and recommended (“R”) values of XXX to retrieve each column type.

T
I
N
Y
I
N
T

S
M
A
L
L
I
N
T

I
N
T
E
G
E
R

B
I
G
I
N
T

R
E
A
L

F
L
O
A
T

D
O
U
B
L
E

D
E
C
I
M
A
L

N
U
M
E
R
I
C

B
I
T

C
H
A
R

V
A
R
C
H
A
R

L
O
N
G
V
A
R
C
H
A
R

B
I
N
A
R
Y

V
A
R
B
I
N
A
R
Y

L
O
N
G
V
A
R
B
I
N
A
R
Y

D
A
T
E

T
I
M
E

T
I
M
E
S
T
A
MP

`getByte`

`getShort`

`getInt`

`getLong`

`getFloat`

`getDouble`

`getBigDecimal`

`getBoolean`

`getString`

`getBytes`

`getDate`

`getTime`

`getTimestamp`

`getAsciiStream`

`getUnicodeStream`

`getBinaryStream`

`getObject`

6. Handle Transactions

A transaction is a set of atomic actions that either all of them or none of them take effect. The default mode of JDBC is auto-commit, which means each individual SQL statement is treated as a transaction and gets committed to database after the statement is executed. To create a transaction with multiple statements, auto-commit mode must be turned off.

Connection conn = DriverManager.getConnection (“jdbc:msql:MyDb, “”, “”);

conn.setAutoCommit (false);

To move back to the default mode,

conn.setAutoCommint (true);

When the set of statements is executed, use conn.commit() to make all the changes permanent in database, or use conn.rollback() to cancel all the changes. A transaction starts when the last setAutoCommit(false), commit() or rollback() is issued.

Another purpose of transaction is to keep the integrity of data in database. When one transaction starts, database server uses internal locks to block changes attended by other transactions to the data that are accessed by the current transaction. While the other transactions cannot change the data, they may still be able to read them (the dirty read). Based on ANSI/ISO SQL standard (SQL99), JDBC defines five levels of such transaction isolation (see the following table), which can be set to database connection using the setTransactionIsolation() method. Note that if the underlying database server does not support a particular isolation level, JDBC’s setting of isolation level does not have any effect.

TRANSACTION_NONE	Transaction is not supported.
TRANSACTION_READ_UNCOMMITTED	Dirty read, non-repeatable read and phantom read OK.
TRANSACTION_READ_COMMITTED	Dirty read prevented; non-repeatable read and phantom read OK.
TRANSACTION_RELEASABLE_READ	Dirty read and non-repeatable read prevented; phantom read OK.
TRANSACTION_SERIALIZABLE	Dirty read, non-repeatable read and phantom read prevented.

Dirty read means one transaction reads the data that have been modified by another yet-to-be-committed transaction. Non-repeatable read, or fuzzy read, means one transaction reads the data again and finds that the data have been modified by another committed transaction. Phantom read means one transaction executes a query again and finds that the result set contains additional rows inserted by another committed transaction.

7. Use Stored Procedures

A stored procedure is a logical set of SQL statements that can be reused to perform a particular function. It is usually created by the database user, stored on the database server and later on invoked by the user to perform a task. Since stored procedures can define variables, when they are invoked, users can pass values to those variables.

Creating a stored procedure is considered update to database, therefore is treated as a SQL update statement. First define the statement string.

String create_sp_person = “create procedure Get_NAME ” +

        “@employee_id int = null ” +

        “as ” +

        “select Last_Name, First_Name ” +

        “from Person” +

        “where Employee_Id = @employee_id”;

Then execute the update statement through the database connection to store the procedure on database server.

Statement stmt = conn.createStatement();

stmt.executeUpdate(create_sp_person);

To invoke the stored procedure with hard-coded value,

CallableStatement cs = conn.prepareCall(“Begin Get_Name(101) End”);

ResultSet rs = cs.executeQuery();

Or we can set the variable dynamically.

Statement stmt = conn.prepareCall(“Begin Get_Name(?) End”);

stmt.setInt(1, 101);

stmt.execute();

stmt.close();

8. Catch Exceptions

Exceptions generated by database server and Java interpreter can be caught as SQLException objects with the usual catch block.

SQLException provides the following information:

(1) A “reason” string describing the error, which can be obtained by the getMessage method.

(2) A “SQLstate” string that is defined by the database vendor’s specifications, following either the XOPEN SQLstate conventions or the SQL 99 conventions. This information can be obtained by the getSQLState method.

(3) An “error code” integer that is defined by the specific database vendor, which can be obtained by the getErrorCode method.

(4) A chain to the next exception, which provides additional error information. The next exception can be obtained by the getNextException method.

9. Retrieve Warnings

While exceptions stop the execution of an application, warnings do not. Warnings are issued by database server to alert that something happens in an unexpected way.

Warnings generated by a statement can be retrieved on Connection object, Statement object or ResultSet object. Multiple warnings can happen on a single statement and they are chained. The chain of warnings is automatically cleared when the next statement is issued. Each of these objects has a getWarnings through which users can get the first warning in the warning chain. Each SQLWarning object has a getNextWarning method to retrieve the next warning on the chain. Try to retrieve warning from a Connection object after it has been closed, from a Statement object after it has been closed, or from a ResultSet object after the corresponding Statement object has been closed, will throw a SQLException object.


Home	Company	Services	Experience	Process	Articles	FAQs	Contact Us

1. What Is JDBC?

2. The First Step - Connect to Database

3. Construct SQL Statements

Create Table

Insert Data

Get Data

Update Data

Delete Data

4. Execute SQL Statements

5. Process Result Sets

getByte

getShort

getInt

getLong

getFloat

getDouble

getBigDecimal

getBoolean

getString

getBytes

getDate

getTime

getTimestamp

getAsciiStream

getUnicodeStream

getBinaryStream

getObject