JDBC

1. JDBC • JDBC is an acronym for Java DataBase Connectivity • This is a specification for connecting programs written in Java to data in databases. • JDBC acts as a database bridge, passing Structured Query Language (SQL) text strings to a database.

2. JDBC • Handles details such as • connecting to a database, • fetching query results • committing or rolling back transactions • converting SQL types to and from Java program variables.

3. JDBC is like ODBC • JDBC’s programming level interface is similar to Microsoft’s Open Database Connectivity (ODBC) which is a standard for personal computer databases and LANs. • JDBC and ODBC are based on the X/Open SQL Call Level Interface.

4. Relational Data Model • by “Database” we mean a relational db. • Main elements are Tables representing enties, and columns representing the primitive data types stored and Relationships. Rows represent the objects. • Meta-data is information about the database (size of tables, etc.)

5. SQL • The Standard Query Language is the usual way to define and manipulate data in a relational database. (SQL-92 is an ANSI standard) and has 3 components • Data Definition Language (DDL) • Data Manipulation Language (DML) • Data Query Language (DQL)

6. SQL • Data Definition Language (DDL) is used to manipulate meta-information; define tables, alter tables, … • Data Manipulation Language (DML) is used to manipulate data within the tables; insert, update, … • Data Query Language (DQL) is used to retrieve data from tables. The major command is SELECT

7. java.sql • The java.sql package assumes a working database that handles SQL. No vendor database dependence is associated with the java client. • Vendor databases are expected to provide a JDBC driver to interface with the java.sql classes.

8. jdbc over odbc • Most vendors already have a odbc driver for their databases. • JDBC to ODBC drivers (that is a jdbc driver bridge on top of odbc driver) allow JDBC to be used with any database system having an odbc driver.

9. JDBC overview • JDBC consists of two main layers • an API which supports application communication with the next lower layer. • a Driver Manager which interfaces with the JDBC Driver API • The reason for the driver manager is to support communication with multiple drivers - i.e. concurrent sessions with multiple databases.

10. JDBC overview • JDBC is based on the X/Open SQL CLI (Call Level Interface) and Mocrosoft’s ODBC • The ODBC API is a C (procedural) interface. JDBC is intended to be used from Java (OO) Java Program JDBC Driver DB JDBC driver mgr JDBC Driver API db protocols JDBC API

11. Important JDBC classes • JDBC has 4 main classes: • java.sql.DriverManager: • This class is used to open a connection to a database via a JDBC driver. The database driver must be registered with the DriverManager before a connection can be established. When connection is attempted, the DriverManager chooses from the list of available drivers the appropriate database connecton

12. 4 classes continued • java.sql.connection: This class represents a single instance of a database session. After a connection is formed the calls to the database are made to the driver avoiding the manager layer. • java.sql.Statement: This class’ purpose is to pass to the database the SQL string for execution and to retrieve any results from the database in the form of a ResultSet. Only one ResultSet can be opened per statement.

13. 4 classes continued • java.sql.ResultSet : A ResultSet is a row of data returned from a currently executed SQL statement. The rows of the table are returned in sequence. Within any row the columns may be accessed in any order.

14. Putting things together? • A three tier architecture for database access. • Tier 1 • HTML form communicates with web server • Tier 2 • Web server runs cgi program that deals with ODBC bridge • Tier 3 • The database server

15. Simplifying the Tier 2 middleware? • Tier 1 • HTML form communicates with web server • Tier 2 • Server runs a servlet using jdbc classes • Tier 3 • Database server

16. Trusted applets • Trusted applets can directly participate in a three tier database framework Web Server Trusted Applet DB

17. Implementation: • mSQL database engine • developed by Hughes Technologies and available without cost to universities http://www.Hughes.com.au/ • Imaginary JDBC Driver for mSQL • produced by Center for Imaginary Environments http://www.imaginary.com/

18. JDBC Drivers • must supply concrete implementation for the JDBC API interfaces • java.sql.Connetion • java.sql.Statement • java.sql.PreparedStatement • java.sql.CallableStatement • java.sql.ResultSet • and must supply a class which implements the java.sql.Driver interface

19. import java.net.URL; import java.sql.*; class Select { public void main(String argv[]) { try { Class.forName(“COM.imaginary.sql.msql.MsqlDriver”); String url = “jdbc:msql://saratoga.eng.auburn.edu:4333/test” Connection con = DriverManager.getConnection( url,”username”,”passwd”); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(“Select * from test ORDER BY name”); while(rs.next()) { int a = rs.getInt(1); String str = rs.getString(2); System.out.print(“key = “ +a); System.out.print(“str = “ + str + ‘\n’); } stmt.close(); con.close(); } catch (Exception e) ...

20. Things to note about previous code: Class.forName(“COM.imaginary.sql.msql.MsqlDriver”); When a driver class is loaded using Class.forName() the driver is registered with the DriverManager. Usage of this driver comes later. String url = “jdbc:msql://saratoga.eng.auburn.edu:4333/test” the protocol “jdbc” and subprotocol “msql” are defined here. host and port are specified, and the database name follows. Connection con = DriverManager.getConnection( url,”username”,”passwd”); this step establishes a connection and logs a user into the database.

21. Statement stmt = con.createStatement(); this creates an object to communicate with the database ResultSet rs = stmt.executeQuery(“Select * from test ORDER BY name”); this uses that object to form and send a query and place the results in a ResultSet object. Note that other methods of the statement class such as execute() or executeUpdate() return different values.

22. while(rs.next()) { the ResultSet object is now critical to appropriate processing of the information lookup. The result is a logical table, and commands such as ResultSetMetadata md = rs.getMetaData(); can be used to find out information such as how many columns, the column labels, etc. A DatabaseMetaData object, created from a connection, can be useful in establishing information about the database itself; the names of tables, the columns of a table, etc. A

23. JDBC-ODBC Bridge • Drivers need not adapt directly to a database access protocol • JDBC-ODBC bridge is a driver which adapts the JDBC API to the ODBC API • This bridge permits interfacing with ODBC drivers that existed prior to the release of JDBC direct drivers JDBC ODBC bridge ODBC driver DB

24. Nuts and Bolts: JDBC • DriverManager • Class.forName() makes the driver available. • The JDBC DriverManager is responsible for installing this driver • The driver and database is specified as a URL with the form jdbc:<subprotocol>:<subname> e.g. jdbc:db2 jdbc:odbc • Drivers are searched first from classes named in a system property sql.drivers then from

25. Nuts and Bolts: JDBC • DriverManager • Drivers are searched first from classes named in a system property sql.drivers then from currently loaded drivers checked via a call to acceptsURL(String)

26. Connecting • Connections are made via the DriverManager but performed by the Driver • Method is: • getConnection(String url, Properties props) • Properties objet is a standard way of passing information to the database (session info such as username, passwd)

27. iterfaces • The two most important interfaces are Statement and ResultSet • Statement represents an SQL statement (DDL,DML or DQL command) • Statement stmt = conn.createStatement(); • ResultSet rs=stmt.executeQuery( “ Select..”); • stmt.close()

28. DQL commands use “executeQuery()” • DDL and DML commands use “executeUpdate”

29. ResultSet • ResultSet represents a set of rows resulting from a DQL query • Methods of the class provide a way to access the next row of data of the returned object. “next()” • ResultSet also provides a set of access methods get<type>(int columnNo) get<type>(String columnName)

30. Mapping examples SQLtype resultSetMethod Java Type CHAR getString String VARCHAR “ ” NUMERIC getBigDecimal BigDecimal INTEGER getInt int DATE getDate Date

31. try { String url = “jdbc:odbc:ob74”; Connection cn = DriverManager.getConnection(url,”user”,””); Statement stmt = cn.createStatement(); ResultSet rs = stmt.executeQuery ( “SELECT last, first, age FROM Students”); while(rs.next()) { String lname = rs.getString(1); String fname = rs.getString(“first”); int age = rs.getInt(3); System.out.println(fname + “ “ + lname); } stmt.close() // also closes rs cn.close(); } catch ( Exception ex) { …}

32. Prepared Statement • The preparedStatement supplies set methods to pass parameters to SQL statements • Once a parameter is set it can be used for multiple executions of that statement until • a subsequent “set on the parameter or • a call to clearParameters • A ? acts as a placeholder for parameters.

33. try { String url = “jdbc:odbc:ob74”; Connection cn = DriverManager.getConnection(url,”user”,””); PreparedStatement stmt = cn.prepareStatement( “UPDATE Students SET age=? WHERE id=?”); stmt.setInt(1,45); stmt.setInt(2,123455678) int upd = stmt.executeUpdate(); System.out.println( upd + “rows updated”); stmt.close(); cn.close(); } catch ( Exception ex) { …}

34. Callable Statements • JDBC provides a standard syntax for executing SQL stored procedures • The CallableStatement comes in two forms • ? = call procedure_name[arg1,…] • call procedure_name[arg1,…]

35. Callable Statements • In parameters are set using PreparedStatements setXXX methods • Out parameters must have their SQL type registered via the registerOutParameter(int,int) method

36. Multiple Results/Metadata • Multiple ResultSets result from some SQL statements. The execute() and getMoreResult() methods of Statement are used in these cases • Use DatabaseMetaData and ResultSetMetaData interfaces to inspect table names, column names, etc.

37. Concurrency issues: • As with all distributed programming, concurrency becomes a major issue to address. • By default, each connection is in an “auto commit” mode, meaning each update to a database is an atomic transaction. • If the database system (and driver) support transactions, then this mode may be changed and commit() and rollback() used as a consistency mechanism.

38. Transactions • To execute several statements within a single transaction • Disable auto-commit • setAutoCommit(false); • The connection is the implicit transaction • To commit or abort Connection has • commit() • rollback() • A commit or rollback starts a new transaction.