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.