jdbc(2)

Chapter 6

Java Database Connectivity

Information & Database Systems Lab.

2009-04-30 Information & Database Systems Lab. 2

Contents

• Overview− History of JDBC− JDBC Model− JDBC Driver Type

• JDBC Programming Steps− Step 1 : Loading a JDBC Driver− Step 2 : Connecting to a Database− Step 3 : Executing SQL − Step 4 : Processing the Results− Step 5 : Closing Database Connection

• The PreparedStatement Object• Transaction and JDBC• Summary• Online Resources


Overview (1/2)

• JDBC− JDBC is a standard interface for connecting to relational

databases from Java− The JDBC Classes and Interfaces are in the java.sql

package− JDBC API

Provides a standard API for tool/database developers Possible to write database applications using a pure Java API Easy to send SQL statements to virtually any relational

database

• What does JDBC do?− Establish a connection with a database− Send SQL statements− Process the results

JDBC DriverJAVA Applet/Application Database

JDBC CallDatabaseCommand


• Reason for JDBC− Database vendors (Microsoft Access, Oracle etc.) provide

proprietary (non standard) API for sending SQL to the server and receiving results from it

− Languages such as C/C++ can make use of these proprietary APIs directly High performance Can make use of non standard features of the database All the database code needs to be rewritten if you change

database vendor or product− JDBC is a vendor independent API for accessing relational

data from different database vendors in a consistent way

Overview (2/2)


History of JDBC (1/2)

• JDBC 1.0 released 9/1996.− Contains basic functionality to connect to database, query

database, process results− JDBC classes are in java.sql package− Comes with JDK 1.1

• JDBC 2.0 released 5/1998− Comes with JDK 1.2− javax.sql contains additional functionality− Additional functionality:

Scroll in result set or move to specific row Update database tables using Java methods instead of SQL

commands Send multiple SQL statements to the database as a batch Use of SQL3 datatypes as column values


History of JDBC (2/2)

• JDBC 3.0 released 2/2002− Comes with Java 2, J2SE 1.4− Support for:

Connection pooling Multiple result sets Prepared statement pooling Save points in transactions


JDBC Model

• JDBC consists of two parts:− JDBC API, a purely Java-

based API− JDBC driver manager

Communicates with vendor-specific drivers

JAVA Applet/Application

JDBC API

Driver Manager

Driver API

Vendor SpecificJDBC Driver

JDBC-ODBC Bridge

Database

Vender SpecificODBC Driver

Database

Java Application Developer

JDBC Developer

Vender Specific JDBC

developer


JDBC Driver Type

• JDBC-ODBC bridge plus ODBC driver• Native-API partly-Java driver• JDBC-Net pure Java driver• Native Protocol pure Java API driver


JDBC Programming Steps

Connect

Query

Process Results

Close

1) Register the driver2) Create a connection to the database

1) Create a statement2) Query the database

1) Get a result set2) Assign results to Java variables

1) Close the result set2) Close the statement3) Close the connection


Skeleton Code

Class.forName(DRIVERNAME);

Connection con = DriverManager.getConnection( CONNECTIONURL, DBID, DBPASSWORD);

Statement stmt = con.createStatement();ResultSet rs = stmt.executeQuery(“SELECT a, b, c FROM member”);

While(rs.next()){

Int x = rs.getInt(“a”);String s = rs.getString(“b”);Float f = rs.getFloat(“c”);

}

rs.close();stmt.close();con.close();

Loading a JDBC driver

Connecting to a database

Processing the result set

Closing the connections

Executing SQL


Step 1 : Loading a JDBC Driver

• A JDBC driver is needed to connect to a database• Loading a driver requires the class name of the driver

Ex) JDBC-ODBC: sun.jdbc.odbc.JdbcOdbcDriver Oracle driver: oracle.jdbc.driver.OracleDriver MySQL: com.mysql.jdbc.Driver

• Loading the driver class

Class.forName("com.mysql.jdbc.Driver");

• It is possible to load several drivers.• The class DriverManager manages the loaded driver(s)


Step 2 : Connecting to a Database (1/2)

• JDBC URL for a database− Identifies the database to be connected− Consists of three-part:

jdbc:<subprotocol>:<subname>

Protocol: JDBC is the only protocol in

JDBC

Protocol: JDBC is the only protocol in

JDBC

Subname: indicates the location and name of the database to be

accessed. Syntax is driver specific

Subname: indicates the location and name of the database to be

accessed. Syntax is driver specific

Sub-protocol: identifies a database

driver

Sub-protocol: identifies a database

driver

Ex) jdbc:mysql://db.pknu.ac.kr/mydb

The syntax for the name of the database is a little messy and is

unfortunately vendor specific


JDBC URLVendor of database, Location of

database server and name of database

Username Password

Step 2 : Connecting to a Database

(2/2)

• The DriverManager allows you to connect to a database using the specified JDBC driver, database location, database name, username and password.

• It returns a Connection object which can then be used to communicate with the database.

Connection connection = DriverManager.getConnection("jdbc:mysql://db.pknu.ac.kr/mydb",“userid",“password");

JDBC URLVendor of database, Location of

database server and name of database

Username Password


Step 3 : Executing SQL (1/2)

• Statement object− Can be obtained from a Connection object

− Sends SQL to the database to be executed• Statement has three methods to execute a SQL

statement:− executeQuery() for QUERY statements

Returns a ResultSet which contains the query results− executeUpdate() for INSERT, UPDATE, DELETE, or DDL

statements Returns an integer, the number of affected rows from the

SQL− execute() for either type of statement

Statement statement = connection.createStatement();


Statement stmt = conn.createStatement();

ResultSet rset = stmt.executeQuery

("select RENTAL_ID, STATUS from ACME_RENTALS");

Statement stmt = conn.createStatement();

int rowcount = stmt.executeUpdate

("delete from ACME_RENTAL_ITEMS

where rental_id = 1011");

Step 3 : Executing SQL (2/2)

• Execute a select statement

• Execute a delete statement


Step 4 : Processing the Results (1/2)

• JDBC returns the results of a query in a ResultSet object− ResultSet object contains all of the rows which satisfied the

conditions in an SQL statement• A ResultSet object maintains a cursor pointing to its current

row of data− Use next() to step through the result set row by row

next() returns TRUE if there are still remaining records− getString(), getInt(), and getXXX() assign each value to a Java

variable

Record 1 Record 2 Record 3 Record 4

ResultSetInternal Pointer

The internal pointer starts one before the first record


Step 4 : Processing the Results (2/2)

• ExampleStatement stmt = con.createStatement();ResultSet rs = stmt.executeQuery(“SELECT ID, name, score FROM table1”);

while (rs.next()){int id = rs.getInt(“ID”);String name = rs.getString(“name”);float score = rs.getFloat(“score”);System.out.println(“ID=” + id + “ ” + name + “ ” + score);}

NOTE You must step the cursor to the first record before read the results This code will not skip the first record

ID name score

1 James 90.5

2 Smith 45.7

3 Donald 80.2

Table1

OutputID=1 James 90.5ID=2 Smith 45.7ID=3 Donald 80.2


Step 5 : Closing Database Connection

• It is a good idea to close the Statement and Connection objects when you have finished with them

• Close the ResultSet objectrs.close();

• Close the Statement objectstmt.close();

• Close the connectionconnection.close();


The PreparedStatement Object

• A PreparedStatement object holds precompiled SQL statements

• Use this object for statements you want to execute more than once

• A PreparedStatement can contain variables (?) that you supply each time you execute the statement

// Create the prepared statementPreparedStatement pstmt = con.prepareStatement(“

UPDATE table1 SET status = ? WHERE id =?”)// Supply values for the variablespstmt.setString (1, “out”);pstmt.setInt(2, id);// Execute the statementpstmt.executeUpdate();


Transactions and JDBC (1/2)

• Transaction: more than one statement that must all succeed (or all fail) togetherEx) updating several tables due to customer purchase

• If one fails, the system must reverse all previous actions

• Also can’t leave DB in inconsistent state halfway through a transaction

• COMMIT = complete transaction• ROLLBACK = cancel all actions


Transactions and JDBC (2/2)

• The connection has a state called AutoCommit mode− If AutoCommit is true, then every statement is

automatically committed− If AutoCommit is false, then every statement is added to

an ongoing transaction− Default: true

con.setAutoCommit(false);try {

PreparedStatement pstmt = con.prepareStatement("update BankAccount set amount = amount + ? where accountId = ?");pstmt.setInt(1,-100); pstmt.setInt(2, 13);pstmt.executeUpdate();pstmt.setInt(1, 100); pstmt.setInt(2, 72);pstmt.executeUpdate();con.commit();

catch (SQLException e) { con.rollback(); }


Summary

• JDBC− Standard interface for connecting to relational databases

from Java− Vendor independent API for accessing relational data− JDBC has four driver type

JDBC-ODBC bridge plus ODBC driver Native-API partly-Java driver JDBC-Net pure Java driver Native Protocol pure Java API driver

− JDBC support transaction and PreparedStatement


Online Resources

• Sun’s JDBC site− http://java.sun.com/products/jdbc/

• JDBC tutorial− http://java.sun.com/docs/books/tutorial/jdbc/

• List of available JDBC drivers− http://developers.sun.com/product/jdbc/drivers

• API for java.sql− http://java.sun.com/j2se/1.5.0/docs/api/java/sql/package-summa

ry.html

jdbc(2)

Documents

query database

database location

database applications

database code

p jdbc urlvendor of

jdbc drivera jdbc driver

location of database

different database vendors