1 / 37

JDBC – J ava D ata b ase C onnectivity

JDBC – J ava D ata b ase C onnectivity. CS 236369, Spring 2010. Today’s Menu. JDBC Architecture Using JDBC Timeout ResultSet Object Null Values Prepared Statements. JDBC (Java Database Connectiveity) is an API (Application Programming Interface)

Download Presentation

JDBC – J ava D ata b ase C onnectivity

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

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

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

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

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

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

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

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

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

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

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

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

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

  20. Mapping Java Types to SQL Types

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

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

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

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

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

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

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

  28. Another Example

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

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

More Related