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. 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.

5. 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.

6. 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.

7. 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

8. 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.

9. 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.

10. 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

11. 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

12. 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/

13. 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) ...

14. 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.

15. 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.

16. 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

17. 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.