Transcript JDBC and SQLJ

JDBC and SQLJ
CIS 612 Spring 2009
JDBC
JDBC is an API that enables database
access from Java programs
 JDBC for DB access provides

◦ Portability across database servers
◦ Portability across hardware architectures

JDBC drivers have been implemented for
a number of DB servers (Oracle, SQL
Server, DB2, Access, etc.)
JDBC

Java Database Connectivity (JDBC) has
been part of the standard Java standard
edition since JDK 1.1
◦ Current version is JDBC 3.0 (as of J2SE 1.4)
◦ JDBC classes are contained in the Java
package java.sql
◦ A JDBC-ODBC bridge is included so that any
ODBC data source available in the JVM host
environment can be accessed
JDBC Drivers

There are 4 types of JDBC drivers
◦
◦
◦
◦
Type 1 JDBC-ODBC Bridge
Type 2 Native API Driver
Type 3 Network Protocol Driver
Type 4 Native Protocol Driver
Types 1 and 2, use platform specific code
 Types 3 and 4 are all-Java

Type 1
Type 2
Type 3
Type 4
JDBC

Steps in JDBC applications
◦ Load JDBC classes (java.sql.*)
◦ Load the JDBC drivers
 Class.forName(“oracle.jdbc.driver.OracleDriver”)
◦ Connect to the database
 Connection conn =
DriverManager.getConnection(url,userid,passwd)
 jdbc:oracle:drivertype@database
◦ Interact with DB using JDBC
◦ Disconnect from DB
JDBC Example
/**********************************************/
/* A Simple JDBC Program (Section 5.2)
*/
/* Chapter 5; Oracle Programming -- A Primer
*/
/*
*/
by R. Sunderraman
/**********************************************/
import java.sql.*;
import java.io.*;
class simple {
public static void main (String args [])
throws SQLException, IOException {
try { Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e {
System.out.println ("Could not load the
driver");
}
String user, pass;
user = readEntry("userid
: ");
JDBC Example
Connection conn = DriverManager.getConnection(
"jdbc:oracle:oci8:"+user+"/"+pass);
Statement stmt = conn.createStatement ();
ResultSet rset = stmt.executeQuery
("select distinct eno,ename,zip,hdate from
employees");
while (rset.next ()) {
System.out.println(rset.getInt(1) + "
rset.getString(2) + "
rset.getInt(3) + "
rset.getDate(4));
}
stmt.close();
conn.close();
}
" +
" +
" +
JDBC Example
//readEntry function -- to read input string
static String readEntry(String prompt) {
try {
StringBuffer buffer = new StringBuffer();
System.out.print(prompt);
System.out.flush();
int c = System.in.read();
while(c != '\n' && c != -1){
buffer.append((char)c);
c = System.in.read();
}
return buffer.toString().trim();
} catch (IOException e) {
return "";
}
}
}
The Connection Object

A Connection represents a session with a
particular database
◦ All SQL statements are executed and results
returned in the context of a Connection
◦ A Java program can have multiple
Connections to one or more DBs
◦ Default behavior is autocommit
◦ Connection object is used to send
statements to the DB server
JDBC SQL Statement Classes

There are three classes for sending SQL
to the DB server
◦ Statement. Used for SQL statements with no
parameters.
◦ PreparedStatement. Used when the same
statement, possibly with different parameters
is to be called multiple times.
◦ CallableStatement. Used for calling stored
procedures.
Other Connection Class Methods

public abstract void close() throws
SQLException
◦ Immediately release the Connection’s
database and JDBC resources

public abstract void setAutoCommit (boolean
autoCommit) throws SQLException
◦ Pass true to enable autocommit, false to disable it

public abstract void rollback() throws SQLException
◦ Useful when autocommit is false

public abstract DatabaseMetaData getMetaData() throws
SQLException
◦ Get metadata from the DB
PreparedStatement Object

PreparedStatement is compiled, can be
reused, may contain parameters
◦ Example:
PreparedStatement stmt = conn.prepareStatement(“insert into students
values (?, ?, ?, ?)” );
…
stmt.setString(1, id);
stmt.setString(2, fn);
stmt.setString(3, ln);
stmt.setString(4, mi);
stmt.executeUpdate(); /* executeQuery for Select */
ResultSet Class

The result of an executeQuery is a single
ResultSet object
◦ This class provides access to the table resulting
from the query
◦ A cursor points to the current row (initially
positioned before the first row.
◦ Use the next method to fetch the next row
◦ The current row’s column values are retrieved
using the getXXX methods, where XXX is a Java
type.
 Pass either an index or the column name to specify
which column
ResultSet Class
Use the wasNull method to check
whether the previously read column value
was Null.
 Use getMetaData to obtain info like
number, types and properties of the
columns of a ResultSet

Errors and Warnings

The SQLException class provides
information about errors while accessing
the db
◦ Contains the error message as a String object
 Retrieve it using the getMessage method
◦ SQLState string identifies the exception
according to the X/Open SQL spec
 Retrieve it using getSQLState method
◦ Link to the next exception
 Retrieve it using getNextException method
Errors and Warnings
try {
some JDBC statement to access the DB;
} catch (SQLException e) {
System.out.println(“SQL Exception caught!”);
while (e!= null) {
System.out.println(“Error Message = “ +
e.getMessage());
System.out.println(“SQL State = “ +
e.getSQLState());
System.out.println(“Error Code = “ +
e.getErrorCode());
e = e.getNextException();
}
}
Errors and Warnings

The SQLWarning class provides info
about warnings generated during DB
access
◦ Use getMessage to get the warning string
◦ Use getSQLState to get warning according to
X/Open SQL Spec
◦ Use getErrorCode for vendor specific code
◦ Use getNextWarning for next warning
generated
Scrollable ResultSet

Since JDBC 2.0, ResultSet objects are now
updatable and scrollable
◦ The Statement object must be created using the
following Connection class method
◦ Statement createStatement(int resultSetType,
 Int resultSetConcurrency)
◦ Where resultSetType is one of:
TYPE_FORWARD_ONLY (default – old
behavior), TYPE_SCROLL_INSENSITIVE (can
scroll forwards and back, don’t see changes to
ResultSet object in the DB),
TYPE_SCROLL_SENSITIVE (changes are visible)
Scrollable ResultSet

The resultSetConcurrency parameter takes
one of the following values:
◦ CONCUR_READ_ONLY (default – old
behavior) data is read only
◦ CONCUR_UPDATABLE – the Java program can
make changes to the database based on the new
methods and positioning of the cursor

Create the scrollable ResultSet using the
usual executeQuery method of the
Statement object
Scrollable ResultSet

Methods for cursor positioning include
◦
◦
◦
◦
◦
◦
◦
◦
absolute(int row)
relative(int row)
first()
last()
previous()
next()
beforeFirst()
afterLast()
More on Statements

The Statement interface provides three
different methods for executing SQL
statements
◦ We have already seen executeQuery for
statements that produce a result set
◦ The method executeUpdate is used to
execute INSERT, UPDATE, DELETE or DDL
statements
 The return value is an integer indicating the number
of rows affected by an update (0 for a DDL
statement)
More on Statements

The execute method should be used only
when it is possible that a statement may
return more than one ResultSet object,
more than one update count, or a
combination of these
◦ Possibly through executing a stored
procedure call, or through executing a SQL
statement which is unknown at compile time
(e.g. use JDBC to implement SQL*Plus)
◦ Returns true if first result is a result set, false
if it is an update count
CallableStatement

A CallableStatement object provides a
way to call stored procedures in a
standard way for all vendor DBs
◦ Written using “escape syntax” (syntax the
driver must translate for a particular DB) with
one of two forms
 Without result parameter – {call proc_name[(?,?,
…)]}
 With result parameter – {? = call proc_name[(?,?,
…)]}
CallableStatement

Passing IN parameters is done using the
setXXX methods inherited from
PreparedStatement
◦ pstmt.setLong(1, 12345); pstmt.setLong(2, 345);

If OUT parameters are used, the JDBC type
of each OUT parameter must be registered
before execution
◦ cstmt.registerOutParameter(1,
java.sql.Types.TINYINT);
◦ Then use getXXX methods to retrieve OUT
parameter values
 byte x = cstmt.getByte(1);