Ilp J2ee Stream J2ee 06 Jdbc V0.3

JDBC Version 1.0

1. What is JDBC? •JDBC is an interface which allows Java code to execute SQL statements inside relational databases – the databases must follow the ANSI SQL-2 standard Latest Release : JDBC 3.0

TCS Internal

September 3, 2009

JDBC in use Application

JDBC

Driver

•Java code calls JDBC library •JDBC loads a driver •Driver talks to a particular database

TCS Internal

September 3, 2009

2. The JDBC-ODBC Bridge •ODBC (Open Database Connectivity) is a Microsoft standard from the mid 1990’s. •It is an API that allows C/C++ programs to execute SQL inside databases •ODBC is supported by many products.

TCS Internal

Continued

September 3, 2009

•The JDBC-ODBC bridge allows Java code to use the C/C++ interface of ODBC – it means that JDBC can access many different database products •The layers of translation (Java --> C --> SQL) can slow down execution.

TCS Internal

Continued

September 3, 2009

JDBC Architectures Java Application

JDBC driver manager

JDBC/native bridge

JDBC/ODBC bridge

Native driver (DBMS specific)

ODBC Driver

JDBC Driver (DBMS Specific)

JDBC middleware (various DBMS)

DBMS TCS Internal

September 3, 2009

3. Four Kinds of JDBC Driver •1. JDBC-ODBC Bridge – translate Java to the ODBC API – requires ODBC driver installed on client •2. Native API – translate Java to the database’s own API(c, c++) – access database’s API through JNI.

TCS Internal

Continued

September 3, 2009

• 3.

Native Protocol – use Java to access the database more directly using its low level protocols (database specific protocols)

•4. Net Protocol – use Java to access the database via networking middleware (usually TCP/IP) – takes JDBC requests and translates into a network protocol that is not database specific. – required for networked applications TCS Internal

September 3, 2009

4. JDBC PseudoCode – six steps •All JDBC programs do the following: – 1) import the packages •import java.sql.*; •import oracle.jdbc.driver.*; – 2) load the JDBC driver •Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver"); – 3) Specify the name and location of the database being used •String url = "jdbc:oracle:thin:@aardvark:1526:teach“; TCS Internal

Continued

September 3, 2009

4. JDBC PseudoCode – six steps (cont..) –

4) Connect to the database with a Connection object •Connection conn = DriverManager.getConnection (url,user, password);

– 5) Execute a SQL query using a Statement object • Statement statement = conn.createStatement(); – 6) Get the results in a ResultSet object •ResultSet rs = statement.executeQuery( TCS Internal

September 3, 2009

4. JDBC PseudoCode – six steps (cont..) – 7) Finish by closing the ResultSet,

Statement and Connection objects statement.close(); conn.close();

TCS Internal

September 3, 2009

4.1. Pseudocode as a Diagram creates DriverManager

Connection

creates

Statement

creates

ResultSet

SQL make link to driver

Driver

SQL

TCS Internal

data

data

September 3, 2009

4.2. DriverManager •It is responsible for establishing the connection to the database through the driver. •e.g. Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver"); Connection conn = DriverManager.getConnection(url);

TCS Internal

September 3, 2009

4.3. Name the Database •The name and location of the database is given as a URL – the details of the URL vary depending on the type of database that is being used

TCS Internal

September 3, 2009

Database URLs

Jdbc:odbc://

The comms protocol

host.domain.com

The machine holding the database.

TCS Internal

2048

The port used for the connection.

/data/file

The path to the database on the machine

September 3, 2009

Database URLs contd… •jdbc:oracle:thin:@127.0.0.1:1521:UAT02 •jdbc:db2:DWCTRLDB •jdbc:odbc:Books

TCS Internal

September 3, 2009

4.4. Statement Object •The Statement object provides a ‘workspace’ where SQL queries can be created, executed, and results collected. •e.g. Statement st = conn.createStatement(): ResultSet rs = st.executeQuery( “ select * from Authors” ); : st.close();

TCS Internal

September 3, 2009

4.5. ResultSet Object •Stores the results of a SQL query. •A ResultSet object is similar to a ‘table’ of answers, which can be examined by moving a ‘pointer’ (cursor).

TCS Internal

Continued

September 3, 2009

cursor 23 John 5 Mark 17 Paul •Cursor operations: 98 Peter – first(), last(), next(), previous(), etc.

•Typical code: while( rs.next() ) { // process the row; }

TCS Internal

September 3, 2009

5. simpJDBC.java // simpJDBC.java // Displays the firstnames and lastnames // of the Authors table in the Books db. import java.sql.*; public class simpJDBC { public static void main(String[] args) { // The URL for the Books database. // ’Protected' by a login and password. String url = "jdbc:odbc:Books"; String username = "anonymous"; String password = "guest"; :

TCS Internal

September 3, 2009

try { // load the JDBC-ODBC Bridge driver Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // connect to db using DriverManager Connection conn = DriverManager.getConnection( url, username, password ); // Create a statement object Statement statement = conn.createStatement(); // Execute the SQL query ResultSet rs = statement.executeQuery( "SELECT lastName, firstName FROM Authors" ); :

TCS Internal

September 3, 2009

// Print the result set while( rs.next() ) System.out.println( rs.getString("lastName") + ", " + rs.getString("firstName") ); // Close down statement.close(); conn.close(); } :

TCS Internal

September 3, 2009

catch ( ClassNotFoundException cnfex ) { System.err.println( "Failed to load JDBC/ODBC driver." ); cnfex.printStackTrace(); System.exit( 1 ); // terminate program } catch ( SQLException sqlex ) { System.err.println( sqlex ); sqlex.printStackTrace(); } } // end of main() } // end of simpJDBC class

TCS Internal

September 3, 2009

Output

TCS Internal

September 3, 2009

5.1. Username & Password •The database’s link to the outside (e.g. its ODBC interface) must be configured to have a login and password – details for ODBC are given later

TCS Internal

September 3, 2009

5.2. Accessing a ResultSet •The ResultSet class contains many methods for accessing the value of a column of the current row – can use the column name or position – e.g. get the value in the lastName column: rs.getString("lastName")

TCS Internal

Continued

September 3, 2009

•The ‘tricky’ aspect is that the values are SQL data, and so they must be converted to Java types/objects. •There are many methods for accessing/converting the data, e.g. – getString(), getDate(), getInt(), getFloat(), getObject()

TCS Internal

September 3, 2009

6. Transaction Management •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

TCS Internal

September 3, 2009

AutoCommit Connection.setAutoCommit(boolean val) •If you set AutoCommit to false, you must explicitly commit or rollback the transaction using Connection.commit() and Connection.rollback()

TCS Internal

September 3, 2009

Fixed Example con.setAutoCommit(false); try { // Create a statement object Statement statement = conn.createStatement(); // Execute the SQL query ResultSet rs = statement.executeQuery( "SELECT lastName, firstName FROM Authors" ); con.commit(); catch (Exception e) { con.rollback(); }

TCS Internal

September 3, 2009

Reference •Stephanie Bodoff, et. al., The J2EE Tutorial, Sun Microsystems. •James McGovers, et. al., J2EE1.4 Bible, Wiely Publishing Inc.

TCS Internal

September 3, 2009