Transcript Lecture 2 - California State University, East Bay

CS6320 - JDBC
Introducing JDBC



JDBC: is an API that provides
“universal data access for the
Java2 platform”
Allows you to connect to a
known data source using
common OO semantics using
Java
Allows you to issue standard SQL
commands on that data source
Provides you with classes to
facilitate access to and
manipulation of:
• returned data and,
• generated exceptions
JDBC Driver Types




Type 1 (JDBC-ODBC Bridge
Technology)
Type 2 (JNI drivers for native
connection libraries)
Type 3 (Socket-level Middleware
Translator)
Type 4 (Pure Java-DBMS driver)
Driver Types
Type 1
Type 2
Type 3
Type 4
Type 1 Drivers:
JDBC-ODBC Bridges



JDBC driver translates call into
ODBC and redirects ODBC call to
an ODBC driver on the DBMS
ODBC binary code must exist on
every client
Translation layer compromises
execution speed to small degree
Type 2 Drivers:
Native-API + Java Driver





Java driver makes JNI calls on the
client API (usually written in C or C++)
• eg: Sybase dblib or ctlib
• eg: Oracle Call Interface libs (OCI)
Requires client-side code to be
installed
Often the fastest solution available
Native drivers are usually delivered by
DBMS vendor
Bug in driver can crash JVMs
Type 3 Drivers:
JDBC-Middleware Pure Java Driver






JDBC driver translates JDBC calls into a
DBMS-independent protocol
Then, communicates over a socket with a
middleware server that translates Java code
into native API DBMS calls
No client code need be installed
Single driver provides access to multiple
DBMSs, eg. WebLogic, Tengah drivers
Type 3 drivers auto-download for applets.
Communication is indirect via a middleware
server
Type 4 Drivers:
Pure Java Drivers





Java drivers talk directly to the DBMS
using Java sockets
No Middleware layer needed, access is
direct.
Simplest solution available.
No client code need be installed.
Type 4 drivers auto-download for applets
JDBC Drivers

JDBC drivers exist for every major
database including: Oracle, SQL
Server, Sybase, and MySQL.
Six Steps to Using JDBC (simple
program…no connection
pooling/middleware)
1.
2.
3.
4.
5.
6.
Load the JDBC Driver
Establish the Database Connection
Create a Statement Object
Execute a Query
Process the Results
Close the Connection
A standard simple JDBC application
// Load the JDBC driver
Class.forName("oracle.jdbc.OracleDriver").newInstance();
// Connect to the database
Connection conn = DriverManager.getConnection
(connect-string,user, pass);
// Create a statement
Statement stmt = conn.createStatement ();
// Execute the statement: select data from the emp table
boolean results = stmt.execute("select * from emp");
ResultSet rset = null;
if (results) rset = stmt.getResultSet();
// Process results: walk through the result set
while (rset.next ()) {
System.out.println (rset.getString (1) + rset.getString(2));
….
}
Overview: Connecting to a
Database
// Load the Oracle JDBC driver
Class.forName("oracle.jdbc.OracleDriver").newInstance();
// Connect to the database
Connection conn = DriverManager.getConnection
(connect-string,user, pass);
// Create a statement
Statement stmt = conn.createStatement ();
// Select data from the emp table
boolean results = stmt.execute("select * from emp");
ResultSet rset = null;
if (results) rset = stmt.getResultSet();
// Walk through the result set
while (rset.next ()) {
System.out.println (rset.getString (1) + rset.getString(2));
….
}
The JDBC Driver Manager

Management layer of JDBC, interfaces
between the client and the driver.
• Keeps a list of available drivers
• Manages driver login time limits and
printing of log and tracing messages


Secure because manager will only allow
drivers that come from local file system
or the same initial class loader
requesting a connection
Most popular use:
• Connection getConnection(url, id, passwd);
Create a Connection to the
database

Call the getConnection method on
the DriverManager.
Connection conn =
DriverManager.getConnection(url, login, password)

URLs:
• “jdbc:sybase:Tds:skunk:4100/myDB”
• "jdbc:oracle:thin:@limani.cs.uchicago.e
du:1521:cs51024";

Only one requirement: the relevant
Drivers must be able to recognize
their own URL
Overview: Statements
// Load the Oracle JDBC driver
Class.forName("oracle.jdbc.OracleDriver").newInstance();
// Connect to the database
Connection conn = DriverManager.getConnection
(connect-string,user, pass);
// Create a statement
Statement stmt = conn.createStatement ();
// Select data from the emp table
boolean results = stmt.execute("select * from emp");
ResultSet rset = null;
if (results) rset = stmt.getResultSet();
// Walk through the result set
while (rset.next ()) {
System.out.println (rset.getString (1) + rset.getString(2));
….
}
SQL Statements
Types of statements:

Class Statement
• Represents a basic SQL statement
• Statement stmt = conn.createStatement();


Class PreparedStatement
• A precompiled SQL statement, which can offer
improved performance, especially for
large/complex SQL statements
Class CallableStatement
• Allows JDBC programs access to stored
procedures
Can be used for both DDL and DML
commands
Execute an SQL Statement


executeQuery(): execute a query and get a
ResultSet back
executeUpdate(): execute an update and get
back an int specifying number of rows acted
on
• UPDATE [table] set [column_name] = value where […]
• DELETE from [table] where [column_name] = 5

execute(): exec. unknown SQL, returns true if
a resultSet is available:
Statement genericStmt = conn.createStatement();
if( genericStmt.execute(SQLString)) {
ResultSet rs = genericStmt.getResultSet();
process(); }
else {
int updated = genericStmt.getUpdateCount();
processCount();
Prepared Statements





Use for complex queries or repeated queries
Features:
• precompiled at database (statement usually sent to
database immediately on creation for compilation)
• supply with new variables each time you call it
Example:
• PreparedStatement ps =
conn.prepareStatement(“update table set sales = ?
Where custName = ?”);
Set with values (use setXXX() methods on
PreparedStatement:
• ps.setInt(1, 400000);
• ps.setString(2, “United Airlines”);
Then execute:
• int count = ps.executeUpdate();
Overview: ResultSets and
Cursors
// Load the Oracle JDBC driver
Class.forName("oracle.jdbc.OracleDriver").newInstance();
// Connect to the database
Connection conn = DriverManager.getConnection
(connect-string,user, pass);
// Create a statement
Statement stmt = conn.createStatement ();
// Select data from the emp table
boolean results = stmt.execute("select * from emp");
ResultSet rset = null;
if (results) rset = stmt.getResultSet();
// Walk through the result set
while (rset.next ()) {
System.out.println (rset.getString (1) + rset.getString(2));
….
}
Result Sets and Cursors




Result Sets are returned from
queries.
Possible number of rows: zero, one,
or more
Cursors are ‘iterators’ that can be
user to ‘walk’ through a result set
JDBC 2.0 allows for backward as well
as forward cursors, including the
ability to go to a specific row or a
relative row
Result Sets

Iterate over all rows:
• ResultSet rs = stmt.executeQuery(“select id, price from
inventory”);
• rs.next(), rs.previous(), rs.first(), …
 call once to access first row: while(rs.next()) {}

Extract data from the ResultSet
• getXXX(columnName/indexVal)
 getInt()
 getDouble()
 getString() (highly versatile, inclusive of others;
automatic conversion to String for most types)
 getObject() (returns a generic Java Object)
• rs.wasNull() - returns true if last get was Null
Example :
Inserting Data via JDBC
import java.sql.*;
public class InsertCoffees {
public static void main(String args[]) throws SQLException {
System.out.println ("Adding Coffee Data");
ResultSet rs = null;
PreparedStatement ps = null;
String url = "jdbc:mysql://localhost/cerami";
Connection con;
Statement stmt;
try {
Class.forName("org.gjt.mm.mysql.Driver"); 1
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
3
con = DriverManager.getConnection(url);
2
stmt = con.createStatement();
stmt.executeUpdate ("INSERT INTO COFFEES " +
"VALUES('Amaretto', 49, 9.99, 0, 0)");
4 stmt.executeUpdate ("INSERT INTO COFFEES " +
"VALUES('Hazelnut', 49, 9.99, 0, 0)");
stmt.executeUpdate ("INSERT INTO COFFEES " +
"VALUES('Amaretto_decaf', 49, 10.99, 0, 0)");
stmt.executeUpdate ("INSERT INTO COFFEES " +
"VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)");
6 stmt.close();
con.close();
System.out.println ("Done");
} catch(SQLException ex) {
System.err.println("-----SQLException-----");
System.err.println("SQLState: " + ex.getSQLState());
System.err.println("Message: " + ex.getMessage());
System.err.println("Vendor: " + ex.getErrorCode());
}
}
}
Example :
Querying Data via JDBC
import java.sql.*;
public class SelectCoffees {
public static void main(String args[]) throws SQLException {
ResultSet rs = null;
PreparedStatement ps = null;
String url = "jdbc:mysql://localhost/cerami";
Connection con;
Statement stmt;
try {
Class.forName("org.gjt.mm.mysql.Driver"); 1
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url); 2
3 stmt = con.createStatement();
4
5
6
ResultSet uprs = stmt.executeQuery("SELECT * FROM COFFEES");
System.out.println("Table COFFEES:");
while (uprs.next()) {
String name = uprs.getString("COF_NAME");
int id = uprs.getInt("SUP_ID");
float price = uprs.getFloat("PRICE");
int sales = uprs.getInt("SALES");
int total = uprs.getInt("TOTAL");
System.out.print(name + " " + id + " " + price);
System.out.println(" " + sales + " " + total);
}
uprs.close();
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("-----SQLException-----");
System.err.println("SQLState: " + ex.getSQLState());
System.err.println("Message: " + ex.getMessage());
System.err.println("Vendor: " + ex.getErrorCode());
}
}
}
Exception Handling

SQL Exceptions
• Nearly every JDBC method can throw a
SQLException in response to a data access
error
• If more than one error occurs, they are
chained together
• SQL exceptions contain:




Description of the error, getMessage
The SQLState (Open Group SQL specification)
identifying the exception, getSQLState
A vendor-specific integer, error code, getErrorCode
A chain to the next SQLException,
getNextException
SQL Exception Example
try {
... // JDBC statement.
} catch (SQLException sqle) {
while (sqle != null) {
System.out.println("Message: " +
sqle.getMessage());
System.out.println("SQLState: " +
sqle.getSQLState());
System.out.println("Vendor Error: " +
sqle.getErrorCode());
sqle.printStrackTrace(System.out);
sqle = sqle.getNextException();
}
}
Using the JDBC MetaData
Interface



ResultSet: ResultSetMetaData m = rs.getMetaData()
ResultSetMetaData provides information about the types and
properties of the DDL properties of a ResultSet object
ResultSetMetaData provides various methods for finding out
information about the structure of a ResultSet:
• getColumnClassName(int col): gets fully-qualified Java class
name to which a column value will be mapped; eg.
Java.lang.Integer, etc.
• getColumnCount(): gets the number of columns in the
ResultSet
• getColumnName(int col): gets the name of column
• int getColumnType(int col): gets the JDBC type
(java.sql.Types) for the value stored in col; eg. Value 12 =
JDBC VARCHAR, etc.
• getPrecision(int col): for numbers, gets the mantissa length,
for others, gets the number of bytes for column