Object-Oriented Enterprise Application Development

1. Object-Oriented Enterprise Application Development Introduction to JDBC

2. Topics • During this class we will examine: • What JDBC is and isn't • Installing JDBC • The JDBC Object Model • Transaction Control

3. JDBC Introduction

4. JDBC Fact and Fiction • Contrary to popular belief, JDBC is not an acronym for anything. • Nevertheless it is often interpreted as standing for Java Data Base Connectivity. • JDBC is a Java API that is used to access relational databases.

5. JDBC Goals • The goal of JDBC is to provide a consistent interface that an application can use to perform data access. • This allows the data source to be changed without requiring significant re-work of existing code.

6. Common JDBC Tasks • We can use JDBC to perform common database access tasks such as: • Establish connections • Send SQL requests • Process SQL results • Transaction control • This class assumes a working knowledge of relational database concepts and SQL.

7. Architecture • The web server acts as our front end. • The application server uses JDBC to access a back-end data source. • The back-end data source can be any kind of data source that supports JDBC. Web Server Application Server JDBC Data Source

8. Versions • The current version of JDBC is 2.0. • For this class we'll use version 1.0. • There are very few changes between the revisions that impact the mechanics we'll be discussing. • The biggest change is in the way that connections to the database are established.

9. JDBC & Java

10. Required Packages • JDBC is part of the JDK available from Sun. • The only package required to use JDBC is java.sql.*. • You'll find, with few exceptions, that all of the JDBC elements we use are interfaces and not classes.

11. Common Interfaces • We won't use all of the interfaces provided by JDBC in this class. We'll focus on the most critical: • DriverManager • Connection • Statement • PreparedStatement • ResultSet

12. Tasks • There is a consistent set of steps to be followed when writing an application that accesses a data source: • Connect to the data source • Manipulate the data source • Disconnect from the data source • While conceptually these are very simple tasks, care must be taken to do them well.

13. Database Connectivity

14. Database Connectivity Evolution • The process of connecting to a database has undergone a gradual evolution: • Native API • ODBC • JDBC • All database connectivity takes place through the use of something called a driver.

15. Native API • The most efficient connections use the native database API. • This is the fastest approach but the least portable. • If we move to a new database we need to modify our code base. Native API

16. ODBC • ODBC was created to "wrap" each vendor's native API within a common interface. • Code was written to use ODBC rather than the native API. • This was less efficient but more portable. ODBC Native API

17. JDBC • JDBC is similar to ODBC in that it wraps a vendor's native API. • The JDBC object model is much simpler than ODBC or most native APIs. • Because it's Java-based, it's portable. JDBC Native API

18. JDBC-ODBC Bridge • For this class we'll use the JDBC-ODBC Bridge driver. • This is the driver shipped with the JDK from Sun. • It isn't very efficient, but it's free and easy to install. JDBC ODBC Native API

19. Configure ODBC • The first step is to configure ODBC with an appropriate Data Source Name or DSN. • The process for creating this DSN is outlined in the ODBC document available on the course web site. • For this course, please use a DSN of se452 for all of your assignments.

20. Loading a JDBC Driver • The next step is to load an appropriate JDBC driver. • To do this, we force the JVM to load the driver using the forName() method of the Class class: Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver");

21. Opening a Connection (1 of 4) • The next step is to open a connection to the database using the driver that was just loaded. • This step must be completed before any other work can be performed against the database by our application. • The connection to the database is held by an instance of the Connection interface.

22. Opening a Connection (2 of 4) • We create a physical connection to the data source by using the getConnection() method of the DriverManager class: Connection conn = DriverManager. getConnection(String URL, String UID, String PWD);

23. Opening a Connection (3 of 4) • The following code segment connects the application to an ODBC data source name called se452 with no user id or password: Connection conn = DriverManager. getConnection("jdbc:odbc:se452", "", "");

24. Opening a Connection (4 of 4) • Creating database connections is an expensive process requiring significant database resources. • We typically create connections as late in our processing as possible and close them as soon as we can to minimize our resource usage against the database.

25. Sample Code – Connect (1 of 2) • import java.sql.*; • public class Connect { • Connection conn = null; • public static void main(String [] args) { • Connect myConnect = new Connect(); • myConnect.doConnect(); • }

26. Sample Code – Connect (2 of 2) • public void doConnect() { • try { • Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver"); • conn = DriverManager.getConnection( "jdbc:odbc:se452", "", "" ); • } • catch (ClassNotFoundException clfe) { • } • catch (SQLException sqle) { • } • } • }

27. Error Detection • Notice that the loading of the JDBC driver and the creation of the connection are performed within a try…catch block. • TheClass.forName() method can generate a ClassNotFoundException. • All JDBC operations can result in a SQLException.

28. Closing a Connection (1 of 2) • Database connections consume resources on both the client and the database server. • We need to close the open connections in a to ensure that these resources are returned to the client or database in a timely manner. • Do not wait for the garbage collector to free these resources for you.

29. Closing a Connection (2 of 2) • We don't need to do anything to close the DriverManager. • However, every connection that was opened using the getConnection() method must be closed using the close() method on the Connection interface: conn.close();

30. Sample Code – Connect (1 of 3) • import java.sql.*; • public class Connect { • Connection conn = null; • public static void main(String [] args) { • Connect myConnect = new Connect(); • myConnect.doConnect(); • // do stuff • myConnect.doDisconnect(); • }

31. Sample Code – Connect (2 of 3) • public void doConnect() { • try { • Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" ); • conn = DriverManager.getConnection( "jdbc:odbc:se452", "", "" ); • } • catch (ClassNotFoundException clfe) { • } • catch (SQLException sqle) { • } • }

32. Sample Code – Connect (3 of 3) • public void doDisconnect() { • try { • conn.close(); • } • catch (SQLException sqle) { • } • } • }

33. Making the Code More Robust • While the code I've show you is adequate as written, it isn't really robust or user-friendly. • In this context a user is any other developer who is using the code you are writing. • We're going to re-write the code so that it demonstrates better software engineering principles of maintainability and reusability.

34. Sample Code – Connect(1 of 3) • import java.sql.*; • public class Connect { • Connection conn = null; • public static void main(String [] args) { • Connect myConnect = new Connect(); • myConnect.doConnect(); • // do stuff • myConnect.doDisconnect(); • }

35. Sample Code – Connect (rev.)(1 of 3) • import java.sql.*; • public class Connect { • Connection conn = null; • public static void main(String [] args) { • Connect myConnect = new Connect(); • try { • myConnect.doConnect(); • // do stuff • } • finally { • myConnect.doDisconnect(); • } • }

36. Sample Code – Connect(2 of 3) • public void doConnect() { • try { • Class.forName( • "sun.jdbc.odbc.JdbcOdbcDriver" ); • conn = DriverManager.getConnection( • "jdbc:odbc:se452", "", "" ); • } • catch (ClassNotFoundException clfe) { • } • catch (SQLException sqle) { • } • }

37. Sample Code – Connect (rev.)(2 of 3) • public void doConnect() { • try { • Class.forName( • "sun.jdbc.odbc.JdbcOdbcDriver" ); • if (conn != null) { • this.doDisconnect(); • } • conn = DriverManager.getConnection( • "jdbc:odbc:se452", "", "" ); • } • catch (ClassNotFoundException clfe) { • } • catch (SQLException sqle) { • } • }

38. Sample Code – Connect (3 of 3) • public void doDisconnect() { • try { • conn.close(); • } • catch (SQLException sqle) { • } • } • }

39. Sample Code – Connect (rev.)(3 of 3) • public void doDisconnect() { • try { • if (conn != null) { • conn.close(); • conn = null; • } • } • catch (SQLException sqle) { • } • } • }

40. Error Detection • In the sample code there are exceptions that are caught but nothing is done about it. • Within those blocks I'd likely embed code to display the stack trace of the exception so an effective post-mortem could be done. • If you can't handle an exception, you shouldn't catch it.

41. Retrieving Data

42. Select Statements • One we have a connection to the data source, we can begin to issues queries and process the results. • This requires three (3) new interfaces: • Statement • PreparedStatement • ResultSet

43. Tasks • To issue read requests to the data source we will perform the following tasks: • Create the statement • Execute the statement • Process the results

44. Creating the Statement • A Statement object is used to send SQL queries to the database. • It's created using a Connection object: Statement stmt =conn.createStatement();

45. Executing the Statement(1 of 2) • Creating a Statement object doesn't itself execute queries against the database. • To do this, we pass a SQL statement to the database using the executeQuery() method on that Statement object: String SQL = "select * from STATE"; stmt.executeQuery( SQL );

46. Executing the Statement (2 of 2) • The call to the executeQuery() method returns a ResultSet object containing the results of the query: String SQL = "select * from STATE"; ResultSet rs =stmt.executeQuery( SQL );

47. Processing the Result • Processing a ResultSet is similar to processing a sequential file in that we process each individual row until we hit the end of the ResultSet object. • This loop is accomplished using the next() method of the ResultSet: while ( rs.next() ) { … }

48. Anatomy of a ResultSet • A ResultSet is nothing more than a two-dimensional table. • There is a "pointer" showing the current row. • Each call to next() moves this pointer to the next row. Start of ResultSet Row 1 Row 2 … Row n

49. Columns(1 of 2) • In addition to rows, each ResultSet contains one column for each column specified by the underlying select statement. • Each column can be accessed by either its name or relative position within the ResultSet.

50. Columns (2 of 2) • To retrieve a column's value for the current row in the ResultSet we use one of many column accessor methods. • Each accessor method is overloaded. • One variation accepts a string that corresponds to the column's name. • One variation accepts an integer that corresponds to the column's relative position.