JDBC – J ava D ata b ase C onnectivity

1. JDBC – Java Database Connectivity CS 236369, Spring 2010

2. Today’s Menu • JDBC Architecture • Using JDBC • Timeout • ResultSet Object • Null Values • Prepared Statements

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

4. JDBC Architecture

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

6. “Movies” Relation

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

9. 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 = new com.mysql.jdbc.Driver(); DriverManager.registerDriver(driver); • MySql JDBC driver can be downloaded from here.

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

12. 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");

13. Establish the connection Connection con = DriverManager.getConnection("jdbc:imaginaryDB1://localhost:3306/");

15. 4. Create a Statement object • We use Statement objects in order to • Query the DB • Update the DB(insert, update, create, drop, …)

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

19. Manipulating DB with Statement String deleteStr = “delete from movies where movieName=‘Movie1’ ”; Statement stmt = con.createStatement(); int rowsDeleted = stmt.executeUpdate(deleteStr); • executeUpdate is for data manipulation: insert, delete, update, create table, etc. • executeUpdate returns the number of rows modified (or 0 for DDL commands)

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

22. 7. Close the connection • Close Connections, Statements, and Result Sets • con.close(); • stmt.close(); • rs.close(); • ‘finally’ block is a good place…

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

25. 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 Type = int || double || long || boolean || byte || time || date …

26. Timeout • Use setQueryTimeOut(int seconds) of Statement class 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?

27. Mapping Java Types to SQL Types

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

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

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

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

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

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

34. 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");

35. Another Example

36. Callable Statements • Execute a call to a database stored procedure. • We will not go into details

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