Lesson 6: Database Programming with Java

1. Lesson 6: Database Programming with Java

2. Course Objective JDBC Basics Basic steps in using JDBC Statement ResultSet PreparedStatement JDBC Advanced Handling SQLExceptions Batch update Transaction RowSet Stored Procedures Connection pool Guide to use HSQL & MySQL

3. Learning Approach • The following are strongly suggested for a better learning and understanding of this course: • Noting down the key concepts in the class • Analyze all the examples / code snippets provided • Study and understand the self study topics • Completion and submission of all the assignments, on time • Completion of the self review questions in the lab guide • Study and understand all the artifacts including the reference materials / e-learning / supplementary materials specified • Completion of the project (if application for this course) on time inclusive of individual and group activities • Taking part in the self assessment activities • Participation in the doubt clearing sessions

4. JDBC BASICS Session 1

5. JDBC JDBC (Java DataBase Connectivity) provides a standard library for accessing relational databases JDBC consists of two parts: JDBC API, a purely Java-based API JDBC Driver Manager,which communicates with vendor-specific drivers that perform the real communication with the database. JDBC classes are in the java.sql package Current JDBC version: 3.0

6. Advantages of JDBC • Continued usage of existing data • Vendor independent • Platform independent • Ease of use

7. JDBC Driver type • Left side, Type 1: JDBC-ODBC Bridge plus ODBC DriverThis combination provides JDBC access via ODBC drivers. ODBC binary code -- and in many cases, database client code -- must be loaded on each client machine that uses a JDBC-ODBC Bridge. Sun provides a JDBC-ODBC Bridge driver, which is appropriate for experimental use and for situations in which no other driver is available. • Right side, Type 2: A native API partly Java technology-enabled driverThis type of driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMS. Note that, like the bridge driver, this style of driver requires that some binary code be loaded on each client machine.

8. JDBC Driver type (cont) • Right side, Type 3: Pure Java Driver for Database MiddlewareThis style of driver translates JDBC calls into the middleware vendor's protocol, which is then translated to a DBMS protocol by a middleware server. The middleware provides connectivity to many different databases. • Left side, Type 4: Direct-to-Database Pure Java DriverThis style of driver converts JDBC calls into the network protocol used directly by DBMSs, allowing a direct call from the client machine to the DBMS server and providing a practical solution for intranet access.

9. JDBC Driver Types

10. Basic Steps in using JDBC 1. Load the driver 2. Define the connection URL and establish the connection 3. Create a Statement object 4. Executing the Statement 5. Getting data from executing result 6. Process the results. 7. Close the connection

11. Loading JDBC drivers DriverManager.registerDriver(new DriverXYZ()); Create an instance of Driver class Register with DriverManager Class.forName("DriverXYZ"); Use Class.forName() to load the Driver class No need to register with DriverManager. How to specify the Driver class. Refer to the driver documentation. Some examples. Class.forName("oracle.jdbc.driver.OracleDriver"); Class.forName("org.gjt.mm.mysql.Driver"); Class.forName(“com.mysql.jdbc.Driver"); Class.forName(“com.ibm.db2j.jdbc.DB2jDriver"); Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

12. Establish the DB connection Need to know which database to connect to In JDBC you need to specify the URL Need username, password Get a java.sql.Connection object Connection con = DriverManager.getConnection(url, “user”, “password”); How to specify the URL. Refer to the driver documentation. Examples: jdbc:oracle:thin:@localhost:1521:orcl jdbc:mysql://127.0.0.1:3306/books

13. JDBC Statement Object A Statement object is what sends your SQL statement to the DBMS Create a Statement object and then execute it, supplying appropriate method with the SQL statement you want to send. Statement stmt = con.createStatement(); For a SELECT statement, the method to use is executeQuery. stmt.executeQuery( “SELECT * FROM XXX"); For statements that create or modify tables, the method to use is executeUpdate. stmt.executeUpdate( "INSERT INTO XXX… “);

14. ResultSet object • A ResultSet object is a table of data representing a database result set, which is usually generated by executing a statement that queries the database. • You access the data in a ResultSet object through a cursor. This cursor is a pointer that points to one row of data in the ResultSet. Initially, the cursor is positioned before the first row.

15. ResultSet type • TYPE_FORWARD_ONLY: The result set cannot be scrolled; its cursor moves forward only, from before the first row to after the last row. The rows contained in the result set depend on how the underlying database generates the results. That is, it contains the rows that satisfy the query at either the time the query is executed or as the rows are retrieved. • TYPE_SCROLL_INSENSITIVE: The result can be scrolled; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position. The result set is insensitive to changes made to the underlying data source while it is open. It contains the rows that satisfy the query at either the time the query is executed or as the rows are retrieved. • TYPE_SCROLL_SENSITIVE: The result can be scrolled; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position. The result set reflects changes made to the underlying data source while the result set remains open. • The default ResultSet type is TYPE_FORWARD_ONLY.

16. ResultSet Concurrency • The concurrency of a ResultSet object determines what level of update functionality is supported. • There are two concurrency levels: • CONCUR_READ_ONLY: The ResultSet object cannot be updated using the ResultSet interface. • CONCUR_UPDATABLE: The ResultSet object can be updated using the ResultSet interface. • The default ResultSet concurrency is CONCUR_READ_ONLY.

17. Cursor Holdability • Calling the method Connection.commit can close the ResultSet objects that have been created during the current transaction. In some cases, however, this may not be the desired behavior. The ResultSet property holdability gives the application control over whether ResultSet objects (cursors) are closed when commit is called. • HOLD_CURSORS_OVER_COMMIT: ResultSet cursors are not closed; they are holdable: they are held open when the method commit is called. Holdable cursors might be ideal if your application uses mostly read-only ResultSet objects. • CLOSE_CURSORS_AT_COMMIT: ResultSet objects (cursors) are closed when the commit method is called. Closing cursors when this method is called can result in better performance for some applications. • The default cursor holdability varies depending on your DBMS.

18. ResultSet navigation

19. ResultSet navigation (cont)

20. Methods of returning Scrollable ResultSet • public StatementcreateStatement(int resultSetType, int resultSetConcurrency) throws SQLException • public PreparedStatementprepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException • public CallableStatementprepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException

21. Retrieving Column Values from Rows • The ResultSet interface declares getter methods (example: getBoolean, getLong, …) for retrieving column values from the current row. • You can retrieve values using either the index number of the column or the alias or name of the column. • The column index is usually more efficient. Columns are numbered from 1. • For maximum portability, result set columns within each row should be read in left-to-right order, and each column should be read only once.

22. Updating Rows in ResultSet Objects • With CONCUR_UPDATABLE ResultSet, you can update values using updateXXX() method. • You can update values using either the index number of the column or the name of the column. • However, none of these updater methods modifies the database; you must call the method ResultSet.updateRow to update the database.

23. Scrollable Resultset and Rowsets / Session2 / 3 of 27 Updating a row • Step 1: Positioning the Cursor Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE) ResultSet rs = st.executeQuery(“SELECT NAME, EMPLOEE_ID FROM EMPLOYEES”); rs.first(); • Step 2: Updating the columns rs.updateInt(2,2345); //rs.update<Type> • Step 3: Committing the update rs.updateRow();

24. Inserting a row • Step 1: Positioning the Cursor Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE) ResultSet rs = st.executeQuery(“SELECT NAME, EMPLOEE_ID FROM EMPLOYEES”); rs.first(); • Step 2: Updating the columns rs.update<Type> • Step 3: inserting a row rs.insertRow();

25. Deleting a row Step 1: Positioning the cursor // Move the cursor to the last row of the result set rs.last(); Step 2: Deleting the row // Deleting the row from the result set rs.deleteRow();

26. Example: Inserting Rows try { stmt = con.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet uprs = stmt.executeQuery( "SELECT * FROM " + dbName + ".COFFEES"); uprs.moveToInsertRow(); uprs.updateString("COF_NAME", coffeeName); uprs.updateInt("SUP_ID", supplierID); uprs.updateFloat("PRICE", price); uprs.updateInt("SALES", sales); uprs.updateInt("TOTAL", total); uprs.insertRow(); uprs.beforeFirst(); } catch (SQLException e ) {

27. Connecting and Querying sample Database DisplayAuthors Retrieves the entire authors table Displays the data in a JTextArea

28. DisplayAuthors.java 1 // DisplayAuthors.java 2 // Displaying the contents of the authors table. 3 4 import java.awt.*; 5 import java.sql.*; 6 import java.util.*; 7 import javax.swing.*; 8 9 publicclass DisplayAuthors extends JFrame { 10 11 // JDBC driver name and database URL 12 static String JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver"; 13 String DATABASE_URL = "jdbc:oracle:thin:@localhost:1521:orcl"; 14 15 // declare Connection and Statement for accessing 16 // and querying database 17 private Connection connection; 18 private Statement statement; 19 20 // constructor connects to database, queries database, processes 21 // results and displays results in window 22 public DisplayAuthors() 23 { 24 super( "Authors Table of Books Database" ); 25

29. DisplayAuthors.java 26 // connect to database books and query database 27 try { 28 29 30 32 // load database driver class 33 Class.forName( JDBC_DRIVER ); 34 35 // establish connection to database 36 connection = DriverManager.getConnection( DATABASE_URL ); 37 38 // create Statement for querying database 39 statement = connection.createStatement(); 40 41 // query database 42 ResultSet resultSet = 43 statement.executeQuery( "SELECT * FROM authors" ); 44 45 // process query results 46 StringBuffer results = new StringBuffer(); 47 ResultSetMetaData metaData = resultSet.getMetaData(); 48 int numberOfColumns = metaData.getColumnCount(); 49

30. DisplayAuthors.java 50 for ( int i = 1; i <= numberOfColumns; i++ ) 51 results.append( metaData.getColumnName( i ) + "\t" ); 52 53 results.append( "\n" ); 54 55 while ( resultSet.next() ) { 56 57 for ( int i = 1; i <= numberOfColumns; i++ ) 58 results.append( resultSet.getObject( i ) + "\t" ); 59 60 results.append( "\n" ); 61 } 62 63 // set up GUI and display window 64 JTextArea textArea = new JTextArea( results.toString() ); 65 Container container = getContentPane(); 66 67 container.add( new JScrollPane( textArea ) ); 68 69 setSize( 300, 100 ); // set window size 70 setVisible( true ); // display window 71 72 } // end try 73

31. DisplayAuthors.java 74 // detect problems interacting with the database 75 catch ( SQLException sqlException ) { 76 JOptionPane.showMessageDialog( null, sqlException.getMessage(), 77 "Database Error", JOptionPane.ERROR_MESSAGE ); 78 79 System.exit( 1 ); 80 } 81 82 // detect problems loading database driver 83 catch ( ClassNotFoundException classNotFound ) { 84 JOptionPane.showMessageDialog( null, classNotFound.getMessage(), 85 "Driver Not Found", JOptionPane.ERROR_MESSAGE ); 86 87 System.exit( 1 ); 88 } 89 90 // ensure statement and connection are closed properly 91 finally { 92 93 try { 94 statement.close(); 95 connection.close(); 96 } 97

32. DisplayAuthors.java 98 // handle exceptions closing statement and connection 99 catch ( SQLException sqlException ) { 100 JOptionPane.showMessageDialog( null, 101 sqlException.getMessage(), "Database Error", 102 JOptionPane.ERROR_MESSAGE ); 103 104 System.exit( 1 ); 105 } 106 } 107 108 } // end DisplayAuthors constructor 109 110 // launch the application 111 publicstaticvoid main( String args[] ) 112 { 113 DisplayAuthors window = new DisplayAuthors(); 114 window.setDefaultCloseOperation( JFrame.EXIT_ON_CLOSE ); 115 } 116 117 } // end class DisplayAuthors

33. PreparedStatement Object An object that represents a precompiled SQL statement. PreparedStatement object is given an SQL statement when it is created. This SQL statement is sent to the DBMS right away, where it will be compiled. When the PreparedStatement is executed, the DBMS can just run the PreparedStatement 's SQL statement without having to compile it first. PreparedStatement objects are used more for SQL statements that take parameters. The advantage of using SQL statements that take parameters is that you can use the same statement and supply it with different values each time you execute it.

34. PreparedStatement Example PreparedStatement pstmt = con.prepareStatement( "UPDATE authors SET lastName = ? WHERE authorID = ?"); pstmt.setString(1, “John”); pstmt.setInt(2, 3); pstmt.executeUpdate(); Set value “John” to the first placeholder. Set value 3 to the second placeholder.

35. JDBC ADVANCED Session 2

36. Handling SQLExceptions • When JDBC encounters an error during an interaction with a data source, it throws an instance of SQLException as opposed to Exception. The SQLException instance contains the following information: • A description of the error. • Retrieve the String object that contains this description by calling the method SQLException.getMessage. • A SQLState code. • These codes and their respective meanings have been standardized by ISO/ANSI and Open Group (X/Open), although some codes have been reserved for database vendors to define for themselves. This String object consists of five alphanumeric characters. Retrieve this code by calling the method SQLException.getSQLState. • An error code. • This is an integer value identifying the error that caused the SQLException instance to be thrown. Its value and meaning are implementation-specific and might be the actual error code returned by the underlying data source. Retrieve the error by calling the method SQLException.getErrorCode. • A cause. • A SQLException instance might have a causal relationship, which consists of one or more Throwable objects that caused the SQLException instance to be thrown. To navigate this chain of causes, recursively call the method SQLException.getCause until a null value is returned. • A reference to any chained exceptions. • If more than one error occurs, the exceptions are referenced through this chain. Retrieve these exceptions by calling the method SQLException.getNextException on the exception that was thrown.

37. Retrieving Exceptions • The following method, JDBCTutorialUtilities.printSQLException outputs the SQLState, error code, error description, and cause (if there is one) contained in the SQLException as well as any other exception chained to it: public static void printSQLException(SQLException ex) { for (Throwable e : ex) { if (e instanceof SQLException) { if (ignoreSQLException(((SQLException)e).getSQLState()) == false) { e.printStackTrace(System.err); System.err.println("SQLState: " + ((SQLException)e).getSQLState()); System.err.println("Error Code: " + ((SQLException)e).getErrorCode()); System.err.println("Message: " + e.getMessage()); Throwable t = ex.getCause(); while(t != null) { System.out.println("Cause: " + t); t = t.getCause(); } } } } }

38. Retrieving Exceptions • For example, if you execute a SQL statement but the table does not exist, the output will be similar to the following: SQLState: 42Y55 Error Code: 30000 Message: 'DROP TABLE' cannot be performed on ‘BOOKS.SAMPLEAUTHORS' because it does not exist.

39. Batch updates A set of multiple update statements that is submitted to the database for processing as a batch Statement, PreparedStatement and CallableStatement can be used to submit batch updates

40. Implementing Batch Update using “Statement” interface

41. Introduction to Transactions • Concept of Transaction: • One or more statement executed together • Indivisible unit of work • Properties of Transaction • Atomicity • Consistency • Isolation • Durability

42. Transaction • A transaction is a set of one or more statements that is executed as a unit, so either all of the statements are executed, or none of the statements is executed.

43. Transaction Processing • Start transaction • Disable auto-commit mode • The way to allow two or more statements to be grouped into a transaction is to disable the auto-commit mode. con.setAutoCommit(false); • Perform transaction • Execute queries • Committing transaction • Commit • After the auto-commit mode is disabled, no SQL statements are committed until you call the method commit() explicitly. All statements executed after the previous call to the method commit() are included in the current transaction and committed together as a unit. • Enable auto-commit mode con.setAutoCommit(true); • Rollback transaction • If any error of abnormal case happened, you may want to aborts this transaction and restores values to what they were before the attempted update: con.rollback()

44. Setting and Rolling Back to Savepoints • An SQL savepoint represents the state of data and schemas at a particular point in time within a unit of work. • To create a savepoint: Savepoint savepoint = con.setSavepoint() Savepoint savepoint = con.setSavepoint(String name) • To remove a savepoint: con.releaseSavepoint(Savepoint savepoint) • To rollback to a savepoint: rollback(Savepoint savepoint)

45. RowSet • A JDBC RowSet object holds tabular data in a way that makes it more flexible and easier to use than a result set. • All RowSet objects are derived from the ResultSet interface and therefore share its capabilities. What makes JDBC RowSet objects special is that they add these new capabilities: • Function as JavaBeans Component • Add Scrollability or Updatability • Oracle has defined five RowSet interfaces for some of the more popular uses of a RowSet, and standard reference are available for these RowSet interfaces: • JdbcRowSet • CachedRowSet • WebRowSet • JoinRowSet • FilteredRowSet

46. Function as JavaBeans Component • All RowSet objects are JavaBeans components. This means that they have the following: • Properties • All RowSet objects have properties. A property is a field that has corresponding getter and setter methods. • JavaBeans Notification Mechanism • RowSet objects use the JavaBeans event model, in which registered components are notified when certain events occur. For all RowSet objects, three events trigger notifications: • A cursor movement • The update, insertion, or deletion of a row • A change to the entire RowSet contents • The notification of an event goes to all listeners, components that have implemented the RowSetListener interface and have had themselves added to the RowSet object's list of components to be notified when any of the three events occurs.

47. Setting Up Listeners • A listener for a RowSet object is a component that implements the following methods from the RowSetListener interface: • cursorMoved: Defines what the listener will do, if anything, when the cursor in the RowSet object moves. • rowChanged: Defines what the listener will do, if anything, when one or more column values in a row have changed, a row has been inserted, or a row has been deleted. • rowSetChanged: Defines what the listener will do, if anything, when the RowSet object has been populated with new data. • An example of a component that might want to be a listener is a BarGraph object that graphs the data in a RowSet object. As the data changes, the BarGraph object can update itself to reflect the new data. • The following line of code means that every time the cursor for the crs objects moves, values in crs are changed, or crs as a whole gets new data, the BarGraph object bar will be notified: crs.addRowSetListener(bar); • You can also stop notifications by removing a listener: crs.removeRowSetListener(bar);

48. Add Scrollability or Updatability • Some DBMSs do not support result sets that can be scrolled (scrollable), and some do not support result sets that can be updated (updatable). If a driver for that DBMS does not add the ability to scroll or update result sets, you can use a RowSet object to do it. • A RowSet object is scrollable and updatable by default, so by populating a RowSet object with the contents of a result set, you can effectively make the result set scrollable and updatable.

49. Different types of RowSet

50. Kinds of RowSet Objects • A RowSet object is considered either connected or disconnected. • A connected RowSet object uses a JDBC driver to make a connection to a relational database and maintains that connection throughout its life span. • JdbcRowSet • A disconnected RowSet object makes a connection to a data source only to read in data from a ResultSet object or to write data back to the data source. After reading data from or writing data to its data source, the RowSet object disconnects from it, thus becoming "disconnected." During much of its life span, a disconnected RowSet object has no connection to its data source and operates independently. • CachedRowSet • WebRowSet • JoinRowSet • FilteredRowSet