CS 596: Client-Server Programming
Spring Semester, 1997
Doc 30, JDBC

Client/Server Programming with Java and CORBA, Orfali and Harkey, John Wiley and Sons, Inc. 1997

---

Doc 30, JDBC Slide # 1

SQL and Java

SQL Call Level Interface (CLI) is the SAG standard for connecting remotely to a database from a program

CLI uses a driver which connects to the database

Program uses a driver manager to talk to the driver

The driver is database specific

In 1994 X/Open adopted SQL CLI to produce X/Open CLI

In 1996 X/Open CLI was adapted by ISO to become ISO 9075-3 Call level Interface

Microsoft's Open Database Connectivity (ODBC) is an extensions of the SAG CLI

ODBC 2.0 (32 bit) has three conformance levels

Core - 23 API calls to perform basic SQL stuff

Level 1 - 19 calls relating to large object (BLOBs) and driver-specific

Level 2 - 19 API calls supporting scrolling (cursors)

Doc 30, JDBC Slide # 2

JDBC

Sun also states that some people think JDBC is an abbreviation for Java Database Connectivity

JDBC is a portable SQL CLI written in Java.

Like all CLIs, JDBC does not replace or extend SQL, it just allows your program to make SQL calls to a database

Most references claim JDBC is easier to use/understand than ODBC

JDBC Architecture

Doc 30, JDBC Slide # 3

Doc 30, JDBC Slide # 4

Using JDBC

Outline

Loading a Driver

To load the Oracle driver

import java.sql.*; 

class JdbcTest
{ 
   public static void main (String args [])  throws
                        ClassNotFoundException
   { 
      Class.forName ("oracle.jdbc.OracleDriver");
   }
}

This requires that oracle package be in your path

A properly written driver will register itself with the DriverManager class

Doc 30, JDBC Slide # 5

getLoginTimeout()
setLoginTimeout(int)

Print a message to the current JDBC log stream

Doc 30, JDBC Slide # 6

Connecting to a Database

import java.sql.*; 

class JdbcTest
{ 
   static String ARS_URL = "jdbc:oracle:@PutDatabaseNameHere";

   public static void main (String args [])  throws 
         SQLException, ClassNotFoundException
   { 
      Class.forName ("oracle.jdbc.OracleDriver");
      Connection ARS;
      ARS =DriverManager.getConnection(ARS_URL, 
                        "whitney", 
                        "secret");
   } 
} 

<subprotocol> is usually the driver or database connectivity mechanism

<subname> depends on the <subprotocol>, can vary with vender

<subname> can include Internet address for remote databases

Doc 30, JDBC Slide # 7

static String ARS_URL = "jdbc:oracle:@PutDatabaseNameHere";

DriverManager.getConnection(ARS_URL, "whitney","secret");

DriverManager.getConnection(
   "jdbc:oracle:whitney/secret@PutDatabaseNameHere");

java.util.Properties info = new java.util.Properties();
info.addProperty ("user", "whitney");
info.addProperty ("password","secret");

DriverManager getConnection (ARS_URL ,info );

Doc 30, JDBC Slide # 8

Issue Queries and Receive Results

import java.sql.*; 
import java.io.*; 

class JdbcTest
{ 
   static String ARS_URL = "jdbc:oracle:@PutDatabaseNameHere";

   public static void main (String args [])  throws 
         SQLException, ClassNotFoundException, IOException
   { 
      Class.forName ("oracle.jdbc.OracleDriver");
      Connection ARS;
      ARS =DriverManager.getConnection(ARS_URL, 
                        "whitney", "secret");

      Statement schedule = ARS.createStatement (); 

      String flightOut =    "SELECT * FROM AirlineSchedule 
                        WHERE from = SAN";

      ResultSet fromSanDiego = schedule.executeQuery (flightOut);
      while ( fromSanDiego.next() ) 
      { 
         System.out.println (fromSanDiego.getString ("to")); 
      } 
   } 
} 

Doc 30, JDBC Slide # 9

Doc 30, JDBC Slide # 10

ResultSet - Result of a Query

have either a column name or column index as argument

return the data in that column converted to type XXX

Doc 30, JDBC Slide # 11

SQL Data Types and Java

Doc 30, JDBC Slide # 12

Statement Methods

Connection Methods

Doc 30, JDBC Slide # 13

Transactions

A transaction ends when a commit or rollback is sent

Connections are opened in auto commit mode:

when a statement is completed, it is committed

What happens to data that is changed in a transaction, but not yet committed?

Can other programs access the old or new values?

Use setTransactionIsolation(int) in Connection class to set access levels

Access levels are given as static fields of Connection class

TRANSACTION_NONE
TRANSACTION_READ_COMMITTED
TRANSACTION_READ_UNCOMMITTED
TRANSACTION_REPEATABLE_READ
TRANSACTION_SERIALIZABLE

Doc 30, JDBC Slide # 14

import java.sql.*; 
import java.io.*; 

class JdbcTest
{ 
   static String ARS_URL = "jdbc:oracle:@PutDatabaseNameHere";

   public static void main (String args [])  throws 
         SQLException, ClassNotFoundException, IOException
   { 
      Class.forName ("oracle.jdbc.OracleDriver");
      Connection ARS;
      ARS =DriverManager.getConnection(ARS_URL, 
                        "whitney", "secret");

      ARS.setAutoCommit(false);

     String floodProblem = DELETE FROM AirlineSchedule WHERE
                                         from = 'FAR';

      String newflight =   INSERT INTO AirlineSchedule VALUES 
                               ( 'DE', 'SAN', '8:00', '12:00', '909', 'A');

      Statement schedule = ARS.createStatement (); 

      schedule.executeUpdate (floodProblem);
      schedule.executeUpdate (newflight);

      ARS.commit();
      ARS.close();
  } 
}

Doc 30, JDBC Slide # 15

PreparedStatement

The SQL statements contains variables (IN parameters) which are given values before statement is executed

Only makes sense to use if database and driver keeps statements open after they have been committed

IN parameters are indicated by a ?

Values are set by position

String flightOut =    "SELECT * FROM AirlineSchedule 
                    WHERE from = ?";

Doc 30, JDBC Slide # 16

import java.sql.*; 
import java.io.*; 

class JdbcTest
{ 
   static String ARS_URL = "jdbc:oracle:@PutDatabaseNameHere";

   public static void main (String args [])  throws 
         SQLException, ClassNotFoundException, IOException
   { 
      Class.forName ("oracle.jdbc.OracleDriver");
      Connection ARS;
      ARS =DriverManager.getConnection(ARS_URL, 
                        "whitney", "secret");

      String flightOut =    "SELECT * FROM AirlineSchedule 
                        WHERE from = ?";

      PreparedStatement schedule;
      schedule = ARS.preparedStatement (flightOut); 

      schedule.setObject( 1, "SAN" );
      ResultSet fromSanDiego = schedule.executeQuery ();

      schedule. clearParameters();
      schedule.setObject( 1, "LAX" );
      ResultSet fromLA = schedule.executeQuery ();

   } 
} 

Doc 30, JDBC Slide # 17

CallableStatement

Some databases have stored procedures (a procedure of SQL statements)

CallableStatement allows a Java program to invoke a stored procedure in a database

DatabaseMetaData

The 113 methods in DatabaseMetaData gives you more information than you thought possible