Java JDBC

 

1.       MS ODBC and JavaSoft ODBC

 

JDBC borrowed the idea from successful ODBC. Both are based on X/Open Call level interface specification.

 

 

In both methods, the following architecture is used

Application Program

 
 


 


JDBC Driver Manager

 

 

 

 

 

 


ODBC Driver

 
|

 

Database Server

 

 

 

JavaSoft supplies a JDBC-ODBC bridge for Solaries and MS Window 95 and NT. If you already have ODBC drive, then you can connect to database without buying database supplier's JDBC driver.

 

There are two group of API for database connectivity. JDBC API is for programmer who wants to write application based on database. JDBC Driver API is for programmers who want to develop JDBC drivers for different databases.

 

2.       Connection to Database Server

 

There are several classes for programmer to connect to database, issue SQL statement and create result set, and retrieve data from the result set.

 

The major steps and the corresponding classes are given below. (Given a supplier's table S(Snum, SName, Status, City))

 

Step Statements and Classes

========================== ============================

I. For retrieve a database data

1. Load Driver into DriverManager: Class.forName("driverName");

Class.forName("oralce.jdbc.driver.OracleDriver");

ForName() throws ClassNotFoundException, or

DriverManager.registerDriver(new );

2. Connecting to database Connection cnn = DriverManager.getConnection(

url, usr, password);

1-1. Specify the JDBC driver String url = "jdbc:odbc:datasourcename";

The url consists of (1) protocol, (2) driver and (3) database source name. Ex.

String url = "jdbc:oracle:thin:@cheval.cs.csubak.edu:1521:TEST";

1-2. Specify user name String usr = "xxxx" ;

1-3. Specify the password String password = "yyyy" ;

3. Create a statement object Statement stmt = cnn.createStatement();

from connection object.

4. Make and execute SQL statement from String sql = "SELECT * FROM s" ;

and create result set. ResultSet rs = stmt.executeQuery(sql);

5. Use boolean rs.next() to move to the first record and the next record.

6. Use result set's get String sno = rs.getString(1); //1st col.

functions to retrieve data or rs.getString("Snum");

in table's columns. int st = rs.getInt("Status");

 

User ResultSet.getMetaData() to get column count, column names, length, types and etc.

 

II. For update statements (UPDATE, DELETE, INSERT) ,

1. Make a delete, update or String ups =

insert statement string "DELETE * FROM s WHERE Status < 10" ;

2. Execute the update the int cnt = stmt.executeUpdate(ups)

update statement

cnt is the number of records affected

 

III. To execute a SQL statement with parameters

 

1. Make a SQL string String sql = "Select * from s where status = ?";

 

Use placeholder (?) for each host variable that you want to use in

parameterized SQL. The position of the first placeholder is 1.

 

2. Create a PreparedStatement PreparedStatement pstmt = new

cnn.prepareStatement(sql) ;

3. Link host variable to

place holder(?) by its pstmt.setString(1, hostVar) ;

position number

4. Execute the prepared ResultSet rs = pstmt.executeQuery();

statement.

 

IV.                 To execute a Stored procedure:

1. Make a SQL string String sql = "{ call storedProc(?, ?, )}";

String sql = "{?=call storedProc(?, ?, )}";

2. Create a PreparedStatement CallableStatement cstmt = new

cnn.prepareCall(sql) ;

3. Register each of all OUT parameters: cstmt.registerOutParameter(2, Types.INTEGER);

4. Link host variable to

placeholder(?) by its sstmt.setInt(1, hostInt) ;

position number

5. Execute the prepared ResultSet rs = cstmt.execute();

statement.

 

3. Java and SQL Data Types

 

To pass paramters to prepared SQL statement or get fields out of result set, java variables are used. The type of java variable must match the type of variable of SQL. The following table shows the correspondance between Java data type and SQL data types.

 

SQL Data Types Java Data Types

INTEGER or INT int

SMALLINT short

 

NUMERIC(w, d) java.sql.Numeric

DECIMAL(w, d)

DEC(w, d)

 

FLOAT(n) double

REAL float

DOUBLE double

 

CHARACTER(n) String

CHAR(n)

VARCHAR(n)

 

DATE java.sql.Date

TIME java.sql.Time

TIMESTAMP java.sql.Timestamp

 

4. JDBC Related Class and interfaces

 

From the example statements, the main classes used to access database include

 

Class Purposes

Connection - Connects to database

- Creates a statement to execute SQL statement.

Statement - Regulate objects used to executes a static query or static update statement. The query statement may return multiple result sets.

SQL statements are represented as string.

ResultSet - Contains a set of records result from

executing a query statement. No result set is

created for update statements.

- Retrieve meta data (column definitions).

Combined with ResultSetMetaData class, you can

retrieve column label, width and etc.

PreparedStatement - Regulate objects that take SQL statement with parameters, compile the statement once and executed many times with different argument.

- The setXX() functions allow you set the values of parameters.

-          Creates result set from parameteried query.

CallableStatement - Regulate objects that call a stored procedures and that can return one or more result sets.

DatabaseMetaData - Retrieves information about database structure

- Retrieves information about exotic features

such what is the result of null + non-null.

 

4.1 Connection Class

 

See API for Connection class definition.

 

 

4.2   Statement Class

 

-          See API for Statement Class

 

 

4.3   ResultSet Class

 

- See API for ResultSet class.

 

 

4.4   PreparedStatement Class

 

The prepared statements allow statements be prepared/compiled once and execute many times with different set of actual parameters. The prepared statements increase performance in cases such load a table rows with text file. Mainly, the prepared statements allow program to

         Execute query or update SQL statements with parameters.

         Use getXXX() to link host variables to SQL parameters (placeholders ?).

 

See API for PreparedStatement class definition.

 

4.5                CallableStatement Class

 

The callable statement is a Java class object that allows you make a call to a stored procedure or function. The calls to stored subprogram will be more efficient executed than Java Statement and PreparedStatement object since the compilation is done, and the executions of stored procedures share the same program.

 

4.6                ResultSetMetaData

The result set meta data class provides programmers with data about a result set. The data includes column names, data types and lengths, and etc. See Java API for class definition.

 

4.7                DatabaseMetaData Class

 

- Member Function Summaries

         Get tables in a schema.

         Many more data structures related functions.

 

5.                   Examples

 

See examples in java_jdbc subdirectory

 

6.                   Connection Pool in JDBC 2.0 Standard Extension (JDBC 2.0 SE)

 

6.1                 What is connection pool?

6.2                 Why do we connection pool?

6.3                 When do we use connection pool?

6.4                 Connection Pool Related Classes

6.4.1            javax.sql.DataSource Interface

 

         The implmenting class of DataSource interface is a factory for creating connection objects. The object implementing DataSource interface will typically be registered with JNDI service provider. A JDBC driver that is accessed via the DataSource API does not automatically register itself with the DriverManager.

 

6.4.2            javax.sql.ConnectionPoolDataSource Interface

 

         The implementing class of ConnectionPoolDataSource will be used to create connection objects that will be stored in a connection pool.

         The implementing class will typically registered with JNDI service.

 

6.4.3            javax.sql.PooledConnection Interface

 

         A pooled connection object represents a physical connection to data source. The pooled connection objet also provide hooks for connection pool management.