JDBC

1. JDBC Olivier de Pertat

2. JDBC Overview • JDBC is specified as an Interface • API to manage and query RDBMS • New standard to access RDMS : single API to access envry single product • JDBC Allows to : • Run SQL Queries (Static or dynamic, DML or DDL instructions) • Process the results • Specification based on the X/Open SQL CLI standard (like ODBC • Alternative to proprietary solutions (Oracle, Sybase, DB2…)

3. Simplified DBMS Architecture

4. DBMS Goals • Efficient data Management (faster than files) • Larger amout of data • High reliability • Information sharing (multiple users) • DBMS Users : • Banks, Insurances, airlines, E-commerce companies, transportation companies, corporate DB, government agencies… • Everybody nowadays!

5. DBMS Concepts • Database modeling • E/R model, Relational model, ER to Relational Model • Relational Algebra • SQL: Subqueries, Joins, Modifications, NULLs, Constraints, Triggers, stored procedures • Embedded SQL • Views • Relational Design: BCNF, 2NF, 3NF, 4NF, 5NF…

6. JDBC & SQL • SQL-3 Entry Level Support • Dynamic SQL, most SQL types • Transactions, basic cursors • Meta-data (Database Dictionary access) • Extension mechanism • Syntax inspired from ODBC: { keywords... parameters ... } • ODBC conversions functions, mathematics, etc. • Long terme: • Support de SQL-3 complet

7. JDBC Versions • JDBC 1.0 : Integrated to Java 1.1 • JDBC 2.0 API : • JDBC 2.1 Core API : since J2SE 1.2 (java.sql) • JDBC 2.0 Optional Package : since J2EE 1.2 (javax.sql) • JDBC 3.0 : • JDBC 3.0 Core API : since J2SE 1.4 • JDBC 3.0 Optional Package :

8. New in JDBC 2.0 • JDBC 2.1 Core API • Scrollable ResultSets • Update through ResultSets is possible • Minimal support of BLOB & CLOB data types • User-defined types support • Bacth updates. • JDBC 2.0 Optional Package • DataSource • Distributed Transactions (JTA/XA) • Connection Pools • RowSet Technology .

9. JDBC 3.0 Newbie • Statements Pools • SavePoints in transaction • MetaData for PreparedStatements • Enhancement of CallableStatements (types of parameters, multiple ResultSet... ) • Management of object types : BLOB, CLOB, ARRAY et REF.

10. J2EE integration - 1

11. J2EE Integration - 2

12. Drivers Topology • JDBC interacts with the RDMS through a DriverManager • Drivers are available for most commercial & OpenSource Databases :Oracle, DB2, Sybase, MySQL, PostgreSQL, ODBC... • 4 drivers topology: • 1. Bridge ODBC (given with JDBC) • 2. Native-API partly-Java driver • 3. JDBC-Net all-Java driver • 4. Native-protocol all-Java driver • 1. et 2 are written with Native code. .

13. JDBC Type 1 : JDBC-ODBC Brigde

14. JDBC Type 2: Native-API Partly-Java Driver

15. JDBC Type 3 : Net Protocol All-Java Driver

16. JDBC Type 4 : All Java Driver

17. JDBC : Architecture Objet

18. JDBC Components • Driver Manager: loads database drivers and manages the connection between application & driver. • Driver: translates API class to operations for a specific data source. • Connection: A session between an application and a driver. • Statement: A SQL statement to perform a query or an update operation. • Metadata: Information about the returned data, driver and the database. • ResultSet : logical set of columns and rows returned by executing a statement.

19. Java.sql • JDBC is implemented via classes in the java.sql package • Defines object for: • Remote connection to DB • Executing query • 8 interfaces to define objects • Statement, CallableStatement, PreparedStatement, DatabaseMetaData, ResultSetMetaData, Connection, Driver

20. JDBC Classes • Java supports DB facilities b y providing classes and interfaces for its components. • DriverManager : class • Connection : Abstract Class • Statement : Interface (to be instantiated with values from the actual SQL Statement) • ResulSet : Interface

21. Seven steps to heaven (or a DBMS ;-)) • Load the Driver • Define the Connection URL • Establish the Connection • Create a Statement object • Execute a query • Process the result • Close the connection

22. Loading the driver v1 • Registering the driver directly • Use the method forName from the class: Class • Creates an instance of the Driver • Registers the Driver with the DriverManager • Class.forName("sun.jdbc.odbc.JdbcOrdbDriver"); • Class.forName("oracle.jdbc.driver.OracleDriver"); • Example : Try { Class.forName(« oracle.jdbc.driver.OracleDriver ») ; } catch(ClassNotFoundException e) { e.printStackTrace(); ; }

23. Loading the driver v2 • Anther option is to creates an instance of the driver then manually register it : Driver driver = new oracle.jdbc.driver.OracleDriver(); DriverManager.registerDriver(driver);

24. Identifying the Data Source • Gives the required information for making the connection to the databases • URL : • <scheme>:<sub scheme>:<scheme-specific part> • Scheme : Protocol => JDBC • Sub Scheme : indicates driver & driver type • Scheme Specific part : Server Address & Database name • Examples : • jdbc:odbc:my_database • jdbc:oracle:thin:@localhost:1521:ODP

25. Connection • A connection represents a session with a specific database • Within the context of a connection SQL statements are executed and results returned (or an Integer if Query is not a SELECT statement) • There can be multiple connections to a database • A connection provides ”metadata” i.e., information about the database, tables, fields. • Connection object has methods to deal with the transactions. • Connection creation : Connection conn = DriverManager.getConnection(url, user, password); • .

26. Connection Metadata • getMetaData() method returns a JDBC Metadata connection • Returned type if DatabaseSetMetaData. • Information returned by this object: • What tables are available? • What's our user name as known to the database? • Is the database in read-only mode? • If table correlation names are supported (association of a column with the table it comes from, when multiple columns of the same name appear in the same query - multi-table queries) , are they restricted to be different from the names of the tables? • and so on…

27. Statements • A statement object is used for executing a static SQL statement and obtaining the results produced by it. • 3 kinds of statements: • statement: • for general queries • <Connection>.createStatement() method • prepared statement: • For a statement called multiple times with different values (precompiled to reduce parsing time) • <Connection>.preparedStatement() method • callable statement • for stored procedures • <Connection>.preparedCall() method • Sample: Statement stmt = conn.createStatement();

28. Executing Queries and updates • ResultSet executeQuery(String) • Execute a SQL statement that returns a single ResultSet • int executeUpdate(String) • Execute a SQL INSERT, UPDATE or DELETE statement • Used for CREATE TABLE, DROP TABLE and ALTER TABLE • Returns the number of rows changed • setQueryTimeOut • to set a timeout for the driver to wait for a statement to be completed • If the operation is not compeleted in the given time, an SQLException is thrown

29. Executing a request • Execution of a request: String myQuery = "SELECT prenom, nom, email " + "FROM employe " + "WHERE (nom='Dupont') AND (email IS NOT NULL"+ "ORDER BY nom"; ResultSet rs = stmt.executeQuery(myQuery);

30. Result Set - 1 • A Result Set provides access to a table of data generated by executing a Statement. • Only one ResultSet per Statement can open at once. • The table rows are retrieved in sequence. • A ResultSet maintains a cursor pointing to its current row of data. • The next method moves the cursor forward (to the next row). • Columns are indexed by name or number. • .

31. Result Set - 2 • Data is retrieved from a column via the getXXX methods where XXX is the type of the returned Java object. • The content Cells containing a “large amount” of data must be retrieved via streams. • Type getType(int columnIndex) • returns the given field as the given type • E.g., int getInt(5); string getString(3); • fields indexed starting at 1 (not 0) • Type getType(String columnName) • same, but uses name of field • less efficient • int findColumn(String columnName) • looks up column index given column name

32. Result Set – 3 – isNull • In SQL, NULL means the field is empty • Not the same as 0 or “” • In JDBC, you must explicitly ask if a field is null by callingResultSet.isNull(column)

33. Example java.sql.Statement stmt = conn.createStatement(); ResultSetrs = stmt.executeQuery("SELECT a, b, c FROM Table1"); while (rs.next()) { // print the values for the current row. int i = rs.getInt("a"); String s = rs.getString("b"); byte b[] = rs.getBytes("c"); System.out.println("ROW = " + i + " " + s + " " + b[0]); }

34. ResultSet’s metadata • getMetaData() method returns a ResultSetMetaData object. • Information: • What's the number of columns in the ResultSet? • What's a column's name? • What's a column's SQL type? • What's the column's normal max width in chars? • What's the suggested column title for use in printouts and displays? • What's a column's number of decimal digits? • Does a column's case matter? • and so on...

35. Full example public class TestJDBC { public static void main(String[] args) throws Exception { Class.forName("oracle.jdbc.driver.OracleDriver"); Connection conn = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:ODP", "user", ""); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SELECT * from employe"); while (rs.next()) { String nom = rs.getString("nom"); String prenom = rs.getString("prenom"); String email = rs.getString("email"); } } }

36. Mapping Java types to SQL Types SQL type Java Type CHAR, VARCHAR, LONGVARCHAR String NUMERIC, DECIMAL java.math.BigDecimal BIT boolean TINYINT byte SMALLINT short INTEGER int BIGINT long REAL float FLOAT, DOUBLE double BINARY, VARBINARY, LONGVARBINARY byte[] DATE java.sql.Date TIME java.sql.Time TIMESTAMP java.sql.Timestamp

37. Types Oracle – JDBC 1.0

38. Types Oracle – JDBC 2.0

39. Database time • Times in SQL are nonstandard • Java defines three classes to help • java.sql.Date • year, month, day • java.sql.Time • hours, minutes, seconds • java.sql.Timestamp • year, month, day, hours, minutes, seconds, nanoseconds • usually use this one

40. Optimized Statements • Prepared Statements • SQL calls that you make again and again • allows driver to optimize (compile) queries • created with Connection.prepareStatement() • Stored Procedures • written in DB-specific language • stored inside database • accessed with Connection.prepareCall()

41. Prepared Statement example PreparedStatement pstmt = con.prepareStatement( "UPDATE PEOPLE SET PHOTO_FILENAME=?,PHOTO_DATA=? WHERE ID=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); pstmt.setString(1, myFile.getName()); pstmt.setBinaryStream(2, myFileInput, myFileInput.available()); pstmt.setString(3, "27"); pstmt.execute(); pstmt.close();

42. JDBC Class Diagram

43. Transaction management • A transaction: a sequence of SQL statements betwwen • BEGIN_TRANSACTION : début • Et COMMIT_TRANSACTION ou ROLLBACK_TRANSACTION • Transactions are not explicitly opened and closed • Instead, the connection has a state called AutoCommit mode • if AutoCommit is true, then every statement is automatically committed • default case: true • Example : CompteA += 100 CompteB -= 100

44. Auto commit Connection.setAutoCommit(boolean) • if AutoCommit is false, then every statement is added to an ongoing transaction • you must explicitly commit or rollback the transaction using Connection.commit() and Connection.rollback()

45. Transactions isolation levels • TRANSACTION_NONE • No transaction support • TRANSACTION_READ_UNCOMMITED • minimal support • Dirty read i.e. no warranty • TRANSACTION_READ_COMMITED • Dirty read impossible • TRANSACTION_REPEATABLE_READ • Repeatable read protected • And TRANSACTION_READ_COMMITED support. • TRANSACTION_SERIALIZABLE • Insertion protectection • And TRANSACTION_REPEATABLE_READ support.

46. Example • Transaction mode activated via setAutoCommit method of Connection. • Example :setTransactionIsolation. Try { con.setAutoCommit(false); con.setTransactionIsolation( Connection.TRANSACTION_READ_COMMITED); //SQL stuff con.commit(); } Catch(Exception e) { con.rollback(); } • .

47. Transactional modes • Transactional support is database specific material. • DB2 UDB : all levels • Oracle : 2 levels supported • MySQL : only one level is supported • Transaction information relatives are specified in the class DatabaseMetaData: • supportsTransactions() • supportsDataDefinitionAndDataManipulationTransactions() • getDefaultTransactionIsolation() • dataDefinitionCausesTransactionCommit() • supportsTransactionIsolationLevel(int) • o

48. Connection Manager • For a large threaded database server, create a Connection Manager object • It is responsible for maintaining a certain number of open connections to the database • When your applications need a connection, they ask for one from the CM’s pool • Why? Because opening and closing connections takes a long time • Warning: the CM should always setAutoCommit(false) when a connection is returned • .

49. Thanks to… • O’reilly Editions • Eyrolles editions • A. Chambrey (BD2 - Eyrolles) • Professor Chen Li – University of California, Irvine