JDBC Architecture and Usage Guide for Java Database Connectivity

1. JDBC Java Database Connectivity

2. Resources used for this presentation • http://www.cs.huji.ac.il/~dbi/recitations/JDBC-PSQL-c.pdf • http://java.sun.com/docs/books/tutorial/jdbc/ • http://www.java-samples.com/showtutorial.php?tutorialid=202

3. Today’s Menu • JDBC Architecture • Using JDBC • Timeout • ResultSet Object • Null Values • Transactions

4. JDBC (Java Database Connectiveity) is an API (Application Programming Interface) • That is, a collection of classes and interfaces • JDBC is used for accessing databases from Java applications • Information is transferred from relations to objects and vice-versa

5. JDBC Architecture

6. JDBC Architecture • Java code calls JDBC library • JDBC loads a driver • The driver talks to a particular DBMS • An application can work with several DBMS by using corresponding drivers

7. “Movies” Relation

8. 7 Steps for Using JDBC • Load the driver • Define the connection URL • Establish the connection • Create a Statement object • Execute a query using the Statement • Process the result • Close the connection

10. Loading the Driver Class.forName(“com.mysql.jdbc.Driver ”); • Class.forName loads the given class dynamically • When the driver is loaded, it automatically • creates an instance of itself • registers this instance within DriverManager • Another way: Driver driver = newcom.mysql.jdbc.Driver(); DriverManager.registerDriver(driver); • MySql JDBC driver can be downloaded from here.

12. Define the connection URL • Every database is identified by a URL • Given a URL, DriverManager looks for the driver that can talk to the corresponding database • DriverManager tries all registered drivers,until a suitable one is found

14. An Example // A driver for imaginary1 Class.forName("ORG.img.imgSQL1.imaginary1Driver"); // A driver for imaginary2 Driver driver = new ORG.img.imgSQL2.imaginary2Driver(); DriverManager.registerDriver(driver); //A driver for PostgreSQL Class.forName("org.postgresql.Driver");

15. Establish the connection Connection con = DriverManager.getConnection("jdbc:imaginaryDB1");

17. 4. Create a Statement object • We use Statement objects in order to • Query the DB • Update the db(insert, update, create, drop, …) • executeQuery returns a ResultSet object representing the query result (discussed later…)

19. 5. Execute a query using the Statement • executeQuery returns a ResultSet object representing the query result (discussed later…)

21. Manipulating DB with Statement • executeUpdate is for data manipulation: insert, delete, update, create table, etc. • executeUpdate returns the number of rows modified (or 0 for DDL commands)

22. 6. Process the result • We will discuss ResultSet in a while…

24. 7. Close the connection • Close Connections, Statements, and Result Sets • con.close(); • stmt.close(); • rs.close()

26. ResultSet • ResultSet objects provide access to the tables generated as results of executing Statement queries. • Only one ResultSet per Statement can be open at a given time! • The table rows are retrieved in sequence: • A ResultSet maintains a cursor pointing to its current row. • next() moves the cursor to the next row

27. ResultSet Methods • boolean next() • Activates the next row • First call to next() activates the first row • Returns false if there are no more rows • Not all of the next calls actually involve the DB • void close() • Disposes of the ResultSet • Allows to re-use the Statement that created it • Automatically called by most Statement methods • Type getType(int columnIndex) • Returns the given field as the given type • Indices start at 1 and not 0! • Add the column name as a comment if it is known! • Type getType(String columnName) • Same, but uses name of field • int findColumn(String columnName) • Looks up column index given column name

28. Timeout • Use setQueryTimeOut(int seconds) of Statement to set a timeout for the driver to wait for a query to be completed. • If the operation is not completed in the given time, an SQLException is thrown • What is it good for?

29. Mapping Java Types to SQL Types

30. Null Values • In SQL, NULL means the field is empty • Not the same as 0 or “”! • In JDBC, you must explicitly ask if the last read field was null • ResultSet.wasNull(column) • For example, getInt(column) will return 0 if the value is either 0 or NULL!

31. Database Time • Times in SQL are notoriously non-standard • 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

32. Exceptions • An SQLException is actually a list of exceptions

33. Prepared Statements • The PreparedStatement object contains not just an SQL statement, but an SQL statement that has been precompiled. • This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement SQL statement without having to compile it first. • Most often used for SQL statements that take parameters.

34. Creating a PreparedStatement Object • As with Statement objects, you create PreparedStatement objects with a Connection method. • The following code create a PreparedStatement object that takes two input parameters:

35. Supplying Values for PreparedStatement Parameters • You need to supply values to be used in place of the question mark placeholders (if there are any) before you can execute a PreparedStatement object. You do this by calling one of the setXXX methods defined in the PreparedStatement class.

36. Example • the following line of code sets the first question mark placeholder to a Java int with a value of 75: • updateSales.setInt(1, 75); • The next example sets the second placeholder parameter to the string " Colombian": • updateSales.setString(2, "Colombian");

37. Another Example

38. Callable Statements • Execute a call to a database stored procedure.