1 / 44

Databases and JDBC

Databases and JDBC. Not a Y2K lecture. 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. JDBC.

tamar
Download Presentation

Databases and JDBC

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Databases and JDBC Not a Y2K lecture

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

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

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

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

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

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

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

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

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

  11. JDBC overview • JDBC is based on the X/Open SQL CLI (Call Level Interface) and Microsoft'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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  28. interfaces • 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()

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

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

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

  32. 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) { …}

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

  34. 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) { …}

  35. 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,…]

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

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

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

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

  40. The future • Keep an eye open regarding ODMG • ODMG is a cooperative development between Baan Co, Sun Microsystems, and Spree Software Technology. • The DMG binding for Java offers native language object storage.

  41. ODMG Binding • With JDBC the developer has the responsibility for mapping objects from Java to tables and back. • ODMG bindings allow application developers to make Java object persistence transparently.

  42. Java Blend • The ODMG binding is being supported with Sun Microsoft System’s Java Blend, built on top of JDBC. • At run time Java Blend creates objects from a relational database that correspond to Java objects. • Rows of the table become objects • Foreign keys become references ...

  43. Sell your Oracle stock? • Are we at a point of transition from the relational database model to the oo model? • The possible advantage is that objects store behavior along with data, and simplify the actual storage process (db manager view)

  44. Follow-up? • “Solving the Java Object Storage Problem”, Douglas Barry and Torsten Stamienda, IEEE Computer, November 1998

More Related