CS 580 Client-Server Spring Semester, 2004 JDBC
	Previous    Lecture Notes Index    Next     © 2004, All Rights Reserved, SDSU & Roger Whitney San Diego State University -- This page last updated 10-Feb-04

Doc 9, JDBC Slide # 2

SQL and Java

SAG standard for remote connects to a database

Doc 9, JDBC Slide # 3

• Core

23 API calls for basic SQL stuff

• Level 1

19 API calls for large objects (BLOBs) and driver-specific

• Level 2

19 API calls for scrolling (cursors)

• Unicode
• Aligns ODBC closer to ISO SQL-92 CLI

Doc 9, JDBC Slide # 4

JDBC

JDBC is a trademark and

Not an abbreviation for Java Database Connectivity

• JDBC 1.x
• JDBC 2.x
• JDBC 3.0 (In JDK 1.4)
• JDBC 4.0

Doc 9, JDBC Slide # 5

• Scrollable result sets
• Updateable result sets

Can change the result of a query locally & in database

• Batch updates
• BLOB, CLOB support

Doc 9, JDBC Slide # 6

• Java Naming and Directory Interface (JNDI) support
• Connection pooling
• Distributed transactions
• JavaBean RowSets

Access any tabular data (files, spreadsheets)

Make old drivers scrollable & updateable

Wraps JDBC driver for use in GUI

Doc 9, JDBC Slide # 7

• Set, release, or rollback a transaction to designated savepoints
• Reuse of prepared statements by connection pools
• Connection pool configuration
• Retrieval of parameter metadata
• Retrieval of auto-generated keys
• Ability to have multiple open ResultSet objects
• Passing parameters to CallableStatement objects by name
• Holdable cursor support
• BOOLEAN data type
• Making internal updates to the data in Blob and Clob objects
• Retrieving and updating the object referenced by a Ref object
• Updating of columns containing BLOB, CLOB, ARRAY and REF types
• DATALINK/URL data type
• Transform groups and type mapping
• DatabaseMetadata APIs

Doc 9, JDBC Slide # 8

JDBC Architecture

Doc 9, JDBC Slide # 9

Doc 9, JDBC Slide # 10

import java.sql.*;
public class SampleConnection 
   {
    public static void main (String args[]) throws Exception
      {
      String dbUrl = "jdbc:mysql://rugby.sdsu.edu:8777/test";
      String user = "whitney";
      String password = "mylittleSecret";
      System.out.println("Load Driver!");
      Class.forName("com.mysql.jdbc.Driver");
      Connection rugby;
      rugby = DriverManager.getConnection( dbUrl, user, password);
      Statement getTables = rugby.createStatement();
      ResultSet tableList = 
         getTables.executeQuery("SELECT * FROM name");
      while (tableList.next() )
         System.out.println("Last Name: " + tableList.getString(1) + '\t' + 
                       "First Name: " + tableList.getString( "first_name"));
      rugby.close();
      }
   }
Doc 9, JDBC Slide # 11

Doc 9, JDBC Slide # 12

Using JDBC

Doc 9, JDBC Slide # 13

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

Doc 9, JDBC Slide # 14

java –Djdbc.drivers=org.postgresql.Driver yourProgramName

Doc 9, JDBC Slide # 15

JDBC Drivers

1. JDBC-ODBC bridge plus ODBC driver

Java code access ODBC native binary drivers

ODBC driver accesses databases

ODBC drivers must be installed on each client

1. Native-API partly-Java driver

Java code accesses database specific native binary drivers

1. JDBC-Net pure Java driver

Java code accesses database via DBMS-independent net protocol

1. Native-protocol pure Java driver

Java code accesses database via DBMS-specific net protocol

Doc 9, JDBC Slide # 16

Name of the driver or database connectivity mechanism

Depends on the <subprotocol>, can vary with vender

If connection goes over Internet subname is to contain net URL

jdbc:mysql://fargo.sdsu.edu:5555/WHITNEYR

jdbc:odbc:<data-source-name>[;<attribute-name>=<attribute-value>]*

jdbc:odbc:qeor7
jdbc:odbc:wombat
jdbc:odbc:wombat;CacheSize=20;ExtensionCase=LOWER
jdbc:odbc:qeora;UID=kgh;PWD=fooey
Doc 9, JDBC Slide # 17

jdbc:postgresql:database
jdbc:postgresql://host/database
jdbc:postgresql://host:port/database

jdbc:mysql://[host][,failoverhost...][:port]/[database]
[?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...

Doc 9, JDBC Slide # 18

getConnection(URL, Properties) 
getConnection(URL, userName, Password) 
getConnection(URLWithUsernamePassword) 

   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 9, JDBC Slide # 19

• deregisterDriver(Driver)
• getDriver(String)
• getDrivers()
• registerDriver(Driver)

• getConnection(String, Properties)
• getConnection(String, String, String)
• getConnection(String)

• getLoginTimeout()
• setLoginTimeout(int)

• getLogStream()
• setLogStream(PrintStream)
• println(String)

   Print a message to the current JDBC log stream 

Doc 9, JDBC Slide # 20

Doc 9, JDBC Slide # 21

Queries

      Connection toFargo = 
         DriverManager.getConnection(database, user, password);
      
      Statement namesTable = toFargo.createStatement();
      ResultSet namesFound = 
         namesTable.executeQuery("SELECT * FROM name");

Use for INSERT, UPDATE, DELETE or SQL that return nothing

Use for SQL (SELECT) that return a result set

Use for SQL that return multiple result sets

Uncommon

Doc 9, JDBC Slide # 22

   public static void main (String args[])  throws Exception
      {
      String dbUrl = "jdbc:mysql://rugby.sdsu.edu:8777/test";
      String user = "whitney";
      String password = "SCECmysql";
      System.out.println("Load Driver!");
      Class.forName("com.mysql.jdbc.Driver");
      Connection rugby;
      rugby = DriverManager.getConnection( dbUrl, user, password);
      Statement getTables = rugby.createStatement();
      int rowsModified = 
         getTables.executeUpdate("UPDATE name 
            SET first_name = 'foo' WHERE last_name='Whitney'");
      System.out.println( "Number of rows modified: " + rowsModified);
      
      rugby.close();
    }

Doc 9, JDBC Slide # 23

Doc 9, JDBC Slide # 24

      ResultSet tableList = 
         getTables.executeQuery("SELECT * FROM name");
      String firstName = tableList.getString( 1);

      ResultSet tableList = 
         getTables.executeQuery("SELECT * FROM name");
      String firstName = (String) tableList.getObject( 1);

Doc 9, JDBC Slide # 25

SQL Data Types and Java

Doc 9, JDBC Slide # 26

Doc 9, JDBC Slide # 27

Statement namesTable = toFargo.createStatement();
      Connection rugby;
      rugby = DriverManager.getConnection( dbUrl, user, password);
      Statement getTables = rugby.createStatement();
      ResultSet count = 
         getTables.executeQuery("SELECT COUNT(*) FROM name");
      ResultSet tableList = 
         getTables.executeQuery("SELECT * FROM name");
      
      while (tableList.next() )
         System.out.println("Last Name: " + tableList.getObject(1) + '\t' + 
                       "First Name: " + tableList.getObject( "first_name"));
      // Raises java.sql.SQLException
      count.getObject(1);
      rugby.close();

Doc 9, JDBC Slide # 28

      Connection rugby;
      rugby = DriverManager.getConnection( dbUrl, user, password);
      Statement getTables = rugby.createStatement();
      Statement tableSize = rugby.createStatement();
      ResultSet count = 
         getTables.executeQuery("SELECT COUNT(*) FROM name");
      ResultSet tableList = 
         tableSize.executeQuery("SELECT * FROM name");
      
      while (tableList.next() )
         System.out.println("Last Name: " + tableList.getObject(1) + '\t' + 
                       "First Name: " + tableList.getObject( "first_name"));
      count.next();
      System.out.println("Count: " + count.getObject(1) );
      count.close();
      tableList.close();
      rugby.close();
Doc 9, JDBC Slide # 29

Threads & Connections

If two threads access the same connection results may get mixed up

• The second thread blocks until the first thread get it its results

• Each connection requires a process on the database

Doc 9, JDBC Slide # 30

Transactions

when a statement is completed, it is committed

Doc 9, JDBC Slide # 31

import java.sql.*; 
public static void main (String args[])  throws Exception
      {
      String dbUrl = "jdbc:mysql://rugby.sdsu.edu:8777/test";
      String user = "whitney";
      String password = "SCECmysql";
      System.out.println("Load Driver!");
      Class.forName("com.mysql.jdbc.Driver");
      
      Connection rugby;
      rugby = DriverManager.getConnection( dbUrl, user, password);
      rugby.setAutoCommit(false);
      Statement update  = rugby.createStatement();
      update.executeUpdate("INSERT INTO name 
            (first_name, last_name) VALUES ( 'Donald', 'Duck') ");
      update.executeUpdate("INSERT INTO name 
            (first_name, last_name) VALUES ( 'Micky', 'Mouse') ");
      rugby.rollback();
      update.close();
      Statement getTables  = rugby.createStatement();
      ResultSet tableList = 
         getTables.executeQuery("SELECT * FROM name");
      while (tableList.next() )
         System.out.println("Last Name: " + tableList.getObject(1) + '\t' + 
                       "First Name: " + tableList.getObject( "first_name"));
      rugby.close();
    }
Doc 9, JDBC Slide # 32

      Connection rugby;
      rugby = DriverManager.getConnection( dbUrl, user, password);
      rugby.setAutoCommit(false);
      boolean hasTransactions = 
         ((com.mysql.jdbc.Connection)rugby).supportsTransactions();
      System.out.println( "Supports transctions? " + hasTransactions);

Doc 9, JDBC Slide # 33

PreparedStatement

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

Doc 9, JDBC Slide # 34

import java.sql.*; 
import java.io.*; 
      Connection rugby;
      rugby = DriverManager.getConnection( dbUrl, user, password);
      
      String findPerson = 
         "SELECT * FROM name WHERE last_name = ?";
      PreparedStatement  find  = rugby.prepareStatement( findPerson );
      find.setObject( 1, "whitney" );
      ResultSet person =  find.executeQuery();
      while (person.next() )
         System.out.println("Last Name: " + person.getObject(1) + '\t' + 
                       "First Name: " + person.getObject( "first_name"));
      find.clearParameters();
      find.setObject( 1, "olson" );
      person = find.executeQuery();
      while (person.next() )
         System.out.println("Last Name: " + person.getObject(1) + '\t' + 
                       "First Name: " + person.getObject( "first_name"));
      rugby.close();

Doc 9, JDBC Slide # 35

Batch Updates

   Connection rugby;
   rugby = DriverManager.getConnection( dbUrl, user, password);
      
   Statement  batch  = rugby.createStatement();
   batch.addBatch("INSERT INTO name (first_name, last_name) 
            VALUES ('Lion', 'King')");
   batch.addBatch("INSERT INTO name (first_name, last_name) 
            VALUES ('Little', 'Nemo')");
   int[] updates = batch.executeBatch();
   for( int k = 0; k < updates.length;k++)
      System.out.println( "Update " + k + " rows affected " + updates[k]);
   rugby.close();

Doc 9, JDBC Slide # 36

CallableStatement

Previous    visitors since 10-Feb-04    Next