CS 580 Client-Server Programming Fall Semester, 2002 JDBC |
||
---|---|---|
© 2002, All Rights Reserved, SDSU & Roger Whitney San Diego State University -- This page last updated 21-Nov-02 |
SQL and Java
Some Jargon
SQL Access Group (SAG) - multivendor "Standards" group
SQL Call Level Interface (CLI)
Microsoft's Open Database Connectivity (ODBC)
JDBC
Java Database Connectivity
Sun states
JDBC 2.1 Core
Standard part of JDK 1.2
JDBC drivers must implement JDBC 2.x before you can use it
MySQL driver for JDBC 2.x is in pre-beta release
Additional Features
JDBC 2.0 Package
Now java.sql
Once was optional Java package javax.sql
JDBC 3.0
java.sql & javax.sql in JDK 1.4
Most advanced features are in javax.sql
JDBC Architecture
JDBC driver provides connections to database via drivers
JDBC Drivers, JDBC Versions & Java API
java.sql.* is mainly interfaces for JDBC Drivers
The driver for the database determines the actual functionality
Sample JDBC Use
import java.sql.*; public class SampleConnection { public static void main (String args[]) throws Exception { String dbUrl = "jdbc:postgresql://rugby.sdsu.edu/cs580"; String user = "whitney"; String password = "don’tyouwish"; System.out.println("Load Driver!"); Class.forName("org.postgresql.Driver"); Connection rugby; rugby = DriverManager.getConnection( dbUrl, user, password); Statement getTables = rugby.createStatement(); ResultSet tableList = getTables.executeQuery("SELECT * FROM pg_tables"); while (tableList.next() ) System.out.println("Result: " + tableList.getString(1)); rugby.close(); } }
Using JDBC
Step 1. Load the driver(s)
Step 2. Connect to the database
Step 3. Issue queries and receive results
Loading a Driver
The most commonly used way
A well-written JDBC driver is loaded using Class.forName
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
Loading a Driver
The Recommended Way
Use the command line to specify the driver
java –Djdbc.drivers=org.postgresql.Driver yourProgramName
Makes it easier to change database vendors with out recompiling the code
Long command lines need script to run
JDBC Drivers
Java supports four types of JDBC drivers
- JDBC-ODBC bridge plus ODBC driver
JDBC URL Structure
jdbc:<subprotocol>:<subname>
<subprotocol>
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
PostgreSQL Subprotocol
jdbc:postgresql:database jdbc:postgresql://host/database jdbc:postgresql://host:port/database
DriverManager.getConnection - Using JDBC URL
Three forms:
getConnection(URL, Properties) getConnection(URL, userName, Password) getConnection(URLWithUsernamePassword)
Form 1
static String ARS_URL = "jdbc:oracle:@PutDatabaseNameHere"; DriverManager.getConnection(ARS_URL, "whitney","secret");Form 2
DriverManager.getConnection( "jdbc:oracle:whitney/secret@PutDatabaseNameHere");Form 3
java.util.Properties info = new java.util.Properties(); info.addProperty ("user", "whitney"); info.addProperty ("password","secret"); DriverManager getConnection (ARS_URL ,info );
java.sql.DriverManager
Driver related methods
Print a message to the current JDBC log stream
Queries
Connection toFargo = DriverManager.getConnection(database, user, password); Statement namesTable = toFargo.createStatement(); ResultSet namesFound = namesTable.executeQuery("SELECT * FROM name");
executeUpdate
Some Result Set Issues
What happens when we call next() too many time?
What happens before we call next
Example Name Table
first |
last |
roger |
whitney |
pete |
stanley |
rat |
cat |
col |
a |
b |
Two Queries public class SampleMySQL { public static void main(String[] args) throws Exception { Class.forName("org.gjt.mm.mysql.Driver").newInstance(); String database = "jdbc:mysql://fargo.sdsu.edu:5555/foo"; Connection toFargo = DriverManager.getConnection(database, "foo", "bar"); Statement namesTable = toFargo.createStatement(); ResultSet namesFound = namesTable.executeQuery("SELECT * FROM name"); for (int k = 0;k< 3;k++) { System.out.println( "first: " + namesFound.getString( 1)); namesFound.next(); } for (int k = 0;k< 3;k++) { sample.next(); System.out.println( "col: " + sample.getString( 1)); } toFargo.close(); } }Result first: roger first: roger first: pete col: a col: b col: b
Mixing ResultSets
Can't have two active result sets on same statement
Statement namesTable = toFargo.createStatement(); ResultSet namesFound = namesTable.executeQuery("SELECT * FROM name"); ResultSet sample = namesTable.executeQuery("SELECT * FROM sample"); for (int k = 0;k< 3;k++) { namesFound.next(); sample.next(); System.out.println( "first: " + namesFound.getString( 1)); System.out.println( "col: " + sample.getString( 1)); }Result first: roger col: a first: roger col: b first: roger col: b
Use Two Statements
Connection toFargo = DriverManager.getConnection(database, user, password); Statement namesTable = toFargo.createStatement(); Statement exampleTable = toFargo.createStatement(); ResultSet namesFound = namesTable.executeQuery("SELECT * FROM name"); ResultSet sample = exampleTable.executeQuery("SELECT * FROM sample"); for (int k = 0;k< 3;k++) { namesFound.next(); sample.next(); System.out.println( "first: " + namesFound.getString( 1)); System.out.println( "col: " + sample.getString( 1)); }
Result first: roger col: a first: pete col: b first: rat col: b
Threads & Connections
Some JDBC drivers are not thread safe
SQL Data Types and Java
SQL type |
Java type |
CHAR |
String |
VARCHAR |
String
|
LONGVARCHAR |
String |
NUMERIC |
java.math.BigDecimal |
DECIMAL |
java.math.BigDecimal |
BIT |
boolean |
TINYINT |
byte |
SMALLINT |
short |
INTEGER |
int |
BIGINT |
long |
REAL |
float |
FLOAT |
double |
DOUBLE |
double |
BINARY |
byte[] |
VARBINARY |
byte[] |
LONGVARBINARY |
byte[] |
DATE |
java.sql.Date |
TIME |
java.sql.Time |
TIMESTAMP |
java.sql.Timestamp |
Transactions
A transaction consists of one or more statements that have been executed and completed
A transaction ends when a commit or rollback is sent
Connections are opened in auto commit mode:
Transaction Example
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(); } }
PreparedStatement
PreparedStatement objects contain SQL statements that have been sent to the database to be prepared for execution
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 = ?";
PreparedStatement Example
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 (); } }
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 class DatabaseMetaData allows you to obtain information about the database
The 113 methods in DatabaseMetaData give you more information than you thought possible
Copyright ©, All rights reserved.
2002 SDSU & Roger Whitney, 5500 Campanile Drive, San Diego, CA 92182-7700 USA.
OpenContent license defines the copyright on this document.
Previous    visitors since 21-Nov-02    Next