Java - Accessing databases with jdbc

25AccessingDatabases withJDBC1It is a capital mistake to theorize before one has data.Arthur Conan DoyleNow go, write it before them in a table, and note it in a book, that it may be for the time to come for ever and ever.The Holy Bible, Isaiah 30:82Get your facts first, and then you can distort them as much as you please.Mark TwainI like two kinds of men: domestic and foreign.Mae West3OBJECTIVESIn this chapter you will learn: Relational database concepts.To use Structured Query Language (SQL) to retrieve data from and manipulate data in a database.To use the JDBC™ API of package java.sql to access databases.To use the RowSet interface from package javax.sql to manipulate databases.To use JDBC 4.0’s automatic JDBC driver discovery.To use PreparedStatements to create precompiled SQL statements with parameters.How transaction processing makes database applications more robust.425.1 Introduction25.2 Relational Databases25.3 Relational Database Overview: The books Database25.4 SQL 25.4.1 Basic SELECT Query 25.4.2 WHERE Claus 25.4.3 ORDER BY Claus 25.4.4 Merging Data from Multiple Tables: INNER JOIN 25.4.5 INSERT Statement 25.4.6 UPDATE Statement 25.4.7 DELETE Statement25.5 Instructions for installing MySQL and MySQL Connector/J525.6 Instructions for Setting Up a MySQL User Account25.7 Creating Database book in MySQL25.8 Manipulating Databases with JDBC 25.8.1 Connecting to and Querying a Database 25.8.2 Querying the books Database25.9 RowSet Interface25.10 Java DB/Apache Derby25.11 PreparedStatements25.12 Stored Procedures25.13 Transaction Processing25.14 Wrap-Up25.15 Web Resources and Recommended Readings625.1 IntroductionDatabaseCollection of dataDBMSDatabase management systemStoring and organizing dataSQLRelational databaseStructured Query Language725.1 Introduction (Cont.)RDBMSRelational database management systemMySQLOpen sourceAvailable for both Windows and Linuxdev.mysql.com/downloads/mysql/4.0.hmlJDBCJava Database ConnectivityJDBC driverEnable Java applications to connect to databaseEnable programmers to manipulate databases using JDBC8Software Engineering Observation 25.1Using the JDBC API enables developers to change the underlying DBMS without modifying the Java code that accesses the database. 925.2 Relational DatabasesRelational databaseTableRows, columnsPrimary keyUnique dataSQL queriesSpecify which data to select from a table10Fig. 25.1 | Employee table sample data. 11Fig. 25.2 | Result of selecting distinct Department and Locationdata from table Employee.1225.3 Relational Database Overview: The books DatabaseSample books databaseFour tablesauthorsauthorID, firstName, lastNametitlesisbn, title, editionNumber, copyright, publisherID, imageFile, priceauthorISBNauthorID, isbn13Fig. 25.3 | authors table from the books database. 14Fig. 25.4 | Sample data from the authors table.1525.3 Relational Database Overview: The books Database (Cont.)Foreign keyA column matches the primary key column in another tableHelps maintain the Rule of Referential IntegrityEvery foreign key value must appear as another table’s primary key value16Fig. 25.5 | authorISBN table from the books database. 17Fig. 25.6 | Sample data from the authorISBN table of books. 18Fig. 25.7 | titles table from the books database. 19Fig. 25.8 | Sample data from the titles table of the books database. 2025.3 Relational Database Overview: The books Database (Cont.)Entity-relationship (ER) diagramTables in the databaseRelationships among tablesRule of Entity IntegrityPrimary key uniquely identifies each rowEvery row must have a value for every column of the primary keyValue of the primary key must be unique in the table21Fig. 25.9 | Table relationships in the books database. 22Common Programming Error 25.1Not providing a value for every column in a primary key breaks the Rule of Entity Integrity and causes the DBMS to report an error.23Common Programming Error 25.2Providing the same value for the primary key in multiple rows causes the DBMS to report an error.24Common Programming Error 25.3Providing a foreign-key value that does not appear as a primary-key value in another table breaks the Rule of Referential Integrity and causes the DBMS to report an error. 2525.4 SQLSQL keywordsSQL queries and statements26Fig. 25.10 | SQL query keywords. 2725.4.1 Basic SELECT QuerySimplest format of a SELECT querySELECT * FROM tableNameSELECT * FROM authorsSelect specific fields from a tableSELECT authorID, lastName FROM authors28Fig. 25.11 | Sample authorID and lastName data from the authors table. 29Software Engineering Observation 25.2For most queries, the asterisk (*) should not be used to specify column names. In general, you process results by knowing in advance the order of the columns in the result—for example, selecting authorID and lastName from table authors ensures that the columns will appear in the result with authorID as the first column and lastName as the second column. Programs typically process result columns by specifying the column number in the result (starting from number 1 for the first column). Selecting columns by name also avoids returning unneeded columns and protects against changes in the actual order of the columns in the table(s).30Common Programming Error 25.4If you assume that the columns are always returned in the same order from a query that uses the asterisk (*), the program may process the results incorrectly. If the column order in the table(s) changes or if additional columns are added at a later time, the order of the columns in the result would change accordingly.3125.4.2 WHERE Clausespecify the selection criteriaSELECT columnName1, columnName2, FROM tableName WHERE criteriaSELECT title, editionNumber, copyrightFROM titlesWHERE copyright > 200232Portability Tip 25.1See the documentation for your database system to determine whether SQL is case sensitive on your system and to determine the syntax for SQL keywords (i.e., should they be all uppercase letters, all lowercase letters or some combination of the two?).33Fig. 25.12 | Sampling of titles with copyrights after 2005 from table titles. 3425.4.2 WHERE Clause (Cont.)WHERE clause condition operators, =, =, LIKEwildcard characters % and _SELECT authorID, firstName, lastNameFROM authorsWHERE lastName LIKE ‘D%’35Fig. 25.13 | Authors whose last name starts with D from the authors table. 36Portability Tip 25.2Read your database system’s documentation carefully to determine whether your system supports the LIKE operator. The SQL we discuss is supported by most RDBMSs, but it is always a good idea to check the features of SQL that are supported by your RDBMS.3725.4.2 WHERE Clause (Cont.)SELECT authorID, firstName, lastNameFROM authorsWHERE lastName LIKE ‘_i%’38Fig. 25.14 | The only author from the authors table whose last name contains o as the second letter. 3925.4.3 ORDER BY ClauseOptional ORDER BY clauseSELECT columnName1, columnName2, FROM tableName ORDER BY column ASCSELECT authorID, firstName, lastNameFROM authorsORDER BY lastName ASCSELECT columnName1, columnName2, FROM tableName ORDER BY column DESCSELECT authorID, firstName, lastNameFROM authorsORDER BY lastName DESC40Fig. 25.15 | Sample data from table authors in ascending order by lastName. 41Fig. 25.16 | Sample data from table authors in descending order by lastName. 4225.4.3 ORDER BY Clause (Cont.)ORDER BY multiple fieldsORDER BY column1 sortingOrder, column2 sortingOrder, SELECT authorID, firstName, lastNameFROM authorsORDER BY lastName, firstName43Fig. 25.17 | Sample data from authors in ascending orderby lastName and firstName. 4425.4.3 ORDER BY Clause (Cont.)Combine the WHERE and ORDER BY clausesSELECT isbn, title, editionNumber, copyright, priceFROM titles WHERE title LIKE ‘%How to Program’ORDER BY title ASC45Fig. 25.18 | Sampling of books from table titles whose titles end withHow to Program in ascending order by title. 4625.4.4 Merging Data from Multiple Tables: INNER JOINSplit related data into separate tablesJoin the tablesMerge data from multiple tables into a single viewINNER JOINSELECT columnName1, columnName2, FROM table1 INNER JOIN table2 ON table1.columnName = table2.column2NameSELECT firstName, lastName, isbn FROM authors, authorISBN INNER JOIN authorISBN ON authors.authorID = authorISBN.authorID ORDER BY lastName, firstName47Fig. 25.19 | Sampling of authors and ISBNs for the books they have written in ascending order by lastName and firstName. 48Software Engineering Observation 25.3If a SQL statement includes columns with the same name from multiple tables, the statement must precede those column names with their table names and a dot (e.g., authors.authorID). 49Common Programming Error 25.5Failure to qualify names for columns that have the same name in two or more tables is an error.5025.4.5 INSERT StatementInsert a row into a tableINSERT INTO tableName ( columnName1, , columnNameN ) VALUES ( value1, , valueN )INSERT INTO authors ( firstName, lastName ) VALUES ( ‘Sue’, ‘Smith’ )51Fig. 25.20 | Sample data from table Authors after an INSERT operation. 52Common Programming Error 25.6It is normally an error to specify a value for an autoincrement column.53Common Programming Error 25.7SQL uses the single-quote (') character as a delimiter for strings. To specify a string containing a single quote (e.g., O’Malley) in a SQL statement, the string must have two single quotes in the position where the single-quote character appears in the string (e.g., 'O''Malley'). The first of the two single-quote characters acts as an escape character for the second. Not escaping single-quote characters in a string that is part of a SQL statement is a SQL syntax error.5425.4.6 UPDATE StatementModify data in a tableUPDATE tableName SET columnName1 = value1, , columnNameN = valueN WHERE criteriaUPDATE authors SET lastName = ‘Jones’ WHERE lastName = ‘Smith’ AND firstName = ‘Sue’55Fig. 25.21 | Sample data from table authors after an UPDATE operation. 5625.4.7 DELETE StatementRemove data from a tableDELETE FROM tableName WHERE criteria DELETE FROM authors WHERE lastName = ‘Jones’ AND firstName = ‘Sue’57Fig. 25.22 | Sample data from table authors after a DELETE operation. 5825.5 Instructions to Install MySQL and MySQL Connector/JInstall MySQLPlatform-specific installation requirements: dev.mysql.com/doc/refman/5.0/en/general-installation-issues.htmlDownload your platform’s installer from:dev.mysql.com/downloads/mysql/5.0.htmlNeed only the Windows Essentials package on Microsoft WindowsFollow installation instructions for your platform:dev.mysql.com/doc/refman/5.0/en/installing.html5925.5 Instructions to Install MySQL and MySQL Connector/JMySQL Server Instance Configuration WizardClick Next > then select Standard Configuration and click Next > again.Not necessary to install MySQL as a Windows service for our examples Uncheck Install as a Windows ServiceCheck Include Bin Directory in Windows PATHClick Next > then click Execute to perform the server configuration. Click Finish to close the wizard.6025.5 Instructions to Install MySQL and MySQL Connector/JInstall MySQL Connector/JMust install Connector/J JDBC driver from:dev.mysql.com/downloads/connector/j/5.0.htmlDownload mysql-connector-java-5.0.4.zipExtract mysql-connector-java-5.0.4.zip to your hard disk into the folder mysql-connector-java-5.0.4Documentation for MySQL Connector/J is in connector-j.pdf in the docs subdirectory of mysql-connector-java-5.0.4 Docs also online at dev.mysql.com/doc/connector/j/en/connector-j.html6125.6 Instructions on Setting MySQL User AccountSet up a user accountStart database server mysqld-nt.exe on WindowsStart the MySQL monitor mysql –h localhost –u rootSelect the built-in database mysql USE mysql; Add the user account jhtp7 and specify privilegescreate user 'jhtp7'@'localhost' identified by 'jhtp7';grant select, insert, update, delete, create, drop, references, execute on *.* to 'jhtp7'@'localhost';Exit the MySQL Monitorexit; 6225.7 Creating Database books in MySQLCreate books databaseOpen Command Prompt and change to the directory containing the SQL script books.sqlStart the MySQL monitor mysql –h localhost –u jhtp7 –pExecute the scriptsource books.sql;Exit the MySQL Monitorexit;6325.8 Manipulating Databases with JDBCConnect to a databaseQuery the databaseDisplay the results of the query in JTable6425.8.1 Connecting to and Querying a DatabaseDisplayAuthorsRetrieves the entire authors tableDisplays the data in the standard output streamExample illustratesConnect to the databaseQuery the databaseProcess the result65OutlineDisplayAuthors .java(1 of 3 ) Imports for the JDBC classes and interfaces from package java.sqlDeclare a String constant that specifies the JDBC driver’s class nameLoads the class definition for the database driver.Declare a String constant that specifies the database URL66OutlineDisplayAuthors .java(2 of 3 ) Invokes Connection method createStatement to obtain an object that implements interface Statement.Use the Statement object’s executeQuery method to execute a query that selects all the author information from table authors.Obtains the metadata for the ResultSet.Uses ResultSetMetaData method getColumnCount to retrieve the number of columns in the ResultSet.Obtain column name using method getColumnNamePosition the ResultSet cursor to the first row in the ResultSet with method nextExtract the contents of one column in the current rowInitialize a Connection reference called connection.67OutlineDisplayAuthors .java(3 of 3 ) Catch SQLException, which is thrown if the query execution or ResultSet process failsClassNotFoundException is thrown if the class loader cannot locate the driver classClose the Statement and the database Connection.68Software Engineering Observation 25.4Most major database vendors provide their own JDBC database drivers, and many third-party vendors provide JDBC drivers as well. For more information on JDBC drivers, visit the Sun Microsystems JDBC Web site, servlet.java.sun.com/products/ jdbc/drivers.69Software Engineering Observation 25.5Most database management systems require the user to log in before accessing the database contents. DriverManager method getConnection is overloaded with versions that enable the program to supply the user name and password to gain access.70Fig. 25.24 | Popular JDBC database URL formats. 71Software Engineering Observation 25.6Metadata enables programs to process ResultSet contents dynamically when detailed information about the ResultSet is not known in advance. 72Common Programming Error 25.8Initially, a ResultSet cursor is positioned before the first row. Attempting to access a ResultSet’s contents before positioning the ResultSet cursor to the first row with method next causes a SQLException.73Performance Tip 25.1If a query specifies the exact columns to select from the database, the ResultSet contains the columns in the specified order. In this case, using the column number to obtain the column’s value is more efficient than using the column name. The column number provides direct access to the specified column. Using the column name requires a search of the column names to locate the appropriate column. 74Common Programming Error 25.9Specifying column number 0 when obtaining values from a ResultSet causes a SQLException.75Common Programming Error 25.10Attempting to manipulate a ResultSet after closing the Statement that created the ResultSet causes a SQLException. The program discards the ResultSet when the corresponding Statement is closed.76Software Engineering Observation 25.7Each Statement object can open only one ResultSet object at a time. When a Statement returns a new ResultSet, the Statement closes the prior ResultSet. To use multiple ResultSets in parallel, separate Statement objects must return the ResultSets.7725.8.2 Querying the books DatabaseAllow the user to enter any query into the programDisplay the results of a query in a JTable78OutlineResultSetTable Model.java(1 of 7 ) Class ResultSetTableModel extends class AbstractTableModel, which implements interface TableModel.Instance variable keeps track of database connection status79OutlineResultSetTable Model.java(2 of 7 ) Establishes a connection to the database.Invokes Connection method createStatement to create a Statement object.Constructor accepts five String arguments—the driver class name, the database URL, the username, the password and the default query to performIndicate that connect to database is successfulInvokes ResultSetTableModel method setQuery to perform the default query.80OutlineResultSetTable Model.java(3 of 7 ) Override method getColumnClass to obtain a Class object that represents the superclass of all objects in a particular columnVerify database connection statusLoads the class definition for the class and returns the corresponding Class object.Returns the default type.Obtains the fully qualified class name for the specified column.Override method getColumnCount to obtain the number of columns in the model’s underlying ResultSet81OutlineResultSetTable Model.java(4 of 7 ) Obtains the number of columns in the ResultSet.Override method getColumnName to obtain the name of the column in the model’s underlying ResultSetObtains the column name from the ResultSet.82OutlineResultSetTable Model.java(5 of 7 ) Override method getColumnCount to obtain the number of rows in the model’s underlying ResultSetUses ResultSet method absolute to position the ResultSet cursor at a specific row.Override method getValueAt to obtain the Object in a particular row and column of the model’s underlying ResultSetUses ResultSet method getObject to obtain the Object in a specific column of the current row.83OutlineResultSetTable Model.java(6 of 7 ) Executes the query to obtain a new ResultSet.Uses ResultSet method last to position the ResultSet cursor at the last row in the ResultSet.Uses ResultSet method getRow to obtain the row number for the current row in the ResultSet.Invokes method fireTableAStructureChanged to notify any JTable using this ResultSetTableModel object as its model that the structure of the model has changed.84OutlineResultSetTable Model.java(7 of 7 ) Method disconnectFromDatabase implement an appropriate termination method for class ResultSetTableModelVerify whether the connection is already terminatedClose the Statement and Connection if a ResultSetTableModel object is garbage collected.Set connectedToDatabase to false to ensure that clients do not use an instance of ResultSetTableModel after that instance has already been terminated85Fig. 25.26 | ResultSet constants for specifying ResultSet type. 86Portability Tip 25.3Some JDBC drivers do not support scrollable ResultSets. In such cases, the driver typically returns a ResultSet in which the cursor can move only forward. For more information, see your database driver documentation.87Portability Tip 25.4Some JDBC drivers do not support updatable ResultSets. In such cases, the driver typically returns a read-only ResultSet. For more information, see your database driver documentation.88Common Programming Error 25.11Attempting to update a ResultSet when the database driver does not support updatable ResultSets causes SQLFeatureNotSupportedExceptions.89Common Programming Error 25.12Attempting to move the cursor backward through a ResultSet when the database driver does not support backward scrolling causes a SQLException.90Fig. 25.27 | ResultSet constants for specifying result properties. 91OutlineDisplayQuery Results.java(1 of 8 ) Declare the database driver class name, database URL, username and password for accessing the database92OutlineDisplayQuery Results.java(2 of 8 ) Declare the default queryDeclare tableModel to be a reference to ResultSetTableModelCreate TableModel for results of default query “SELECT * FROM authors”93OutlineDisplayQuery Results.java(3 of 8 ) Create JTable delegate for tableModel94OutlineDisplayQuery Results.java(4 of 8 ) Register an event handler for the submitButton that the user clicks to submit a query to the databaseInvoke ResultSetTableModel method setQuery to execute the new query95OutlineDisplayQuery Results.java(5 of 8 ) Ensure that the database connection is closedSet up TableRowSorterNo filter initially96OutlineDisplayQuery Results.java(6 of 8 ) Set filter using regular expression97OutlineDisplayQuery Results.java(7 of 8 ) Ensure that the database connection is closedEnsure that the database connection is closed when window is closed98OutlineDisplayQuery Results.java(8 of 8 ) 9925.10 RowSet InterfaceInterface RowSetConfigures the database connection automaticallyPrepares query statements automaticallyProvides set methods to specify the properties needed to establish a connectionPart of the javax.sql packageTwo types of RowSetConnected RowSetConnects to database once and remain connectedDisconnected RowSetConnects to database, executes a query and then closes connection10025.10 RowSet Interface (Cont.)Package javax.sql.rowsetJdbcRowSetConnected RowSetWrapper around a ResultSetScrollable and updatable by defaultCachedRowSetDisconnected RowSetCache the data of ResultSet in memoryScrollable and updatable by defaultSerializableCan be passed between Java applicationLimitationAmount of data that can be stored in memory is limited101Portability Tip 25.5A RowSet can provide scrolling capability for drivers that do not support scrollable ResultSets.102OutlineJdbcRowSetTest .java(1 of 3 ) 103OutlineJdbcRowSetTest .java(2 of 3 ) Use Sun’s reference implementation of JdbcRowSet interface (JdbcRowSetImpl) to create a JdbcRowSet objectInvoke JdbcRowSet method setUrl to specify the database URLInvoke JdbcRowSet method setUsername to specify the usernameInvoke JdbcRowSet method setUsername to specify the passwordInvoke JdbcRowSet method setCommand to specify the queryInvoke JdbcRowSet method execute to execute the query104OutlineJdbcRowSetTest .java(3 of 3 ) 10525.11 Java DB/Apache DerbyAs of JDK 6, Sun Microsystems now bundles the open-source, pure Java database Java DB (the Sun branded version of Apache Derby) with the JDKWe use the embedded version of Java DBThere is also a network version that executes similarly to the MySQL DBMS introduced earlier in the chapter10625.11 Java DB/Apache DerbyJava DB comes with several batch files to configure and run itFirst set the environment variable JAVA_HOME to refer to the JDK’s C:\Program Files\Java\jdk1.6.0 installation directoryOpen the batch file setEmbeddedCP.bat (located in C:\Program Files\Java\jdk1.6.0\db\frameworks\embedded\bin) in a text editor such as NotepadLocate the line rem set DERBY_INSTALL= and change it to set DERBY_INSTALL=C:\Program Files\Java\jdk1.6.0\dbAlso, comment out the line @FOR %%X in ("%DERBY_HOME%") DO SET DERBY_HOME=%%~sX by preceding it with REMSave your changes and close this file10725.11 Java DB/Apache DerbyChange directories to C:\Program Files\Java\ jdk1.6.0\db\frameworks\embedded\bin\. Then, type setEmbeddedCP.bat and press Enter to set the environment variables required by Java DB.Embedded Java DB database must reside in the same location as the application that manipulates the databaseChange to the directory that contains the code for Figs. 25.30–25.32 Execute the command "C:\Program Files\Java\jdk1.6.0\db\frameworks\embedded\bin\ij" to start the command-line tool for interacting with Java DB. The double quotes are necessary because the path contains a space. 10825.11 Java DB/Apache DerbyAt the ij> prompt type connect 'jdbc:derby:AddressBook;create=true; user=jhtp7;password=jhtp7'; to create the AddressBook database in the current directory. This command also creates the user jhtp7 with the password jhtp7 for accessing the database.To create the database table and insert sample data in the database type run 'address.sql';To terminate the Java DB command-line tool, type exit;10925.12 PreparedStatementsPreparedStatements execute more efficiently than Statement objectsPreparedStatements can specify parameters11025.12 PreparedStatementsPreparedStatement to locate all book titles for an author with a specific last name and first name, and to execute that query for several authors:PreparedStatement authorBooks = connection.prepareStatement(  "SELECT lastName, firstName, title " + "FROM authors INNER JOIN authorISBN " +  "ON authors.authorID=authorISBN.authorID " +  "INNER JOIN titles " +  "ON authorISBN.isbn=titles.isbn " +  "WHERE lastName = ? AND firstName = ?" );Question marks (?) are placeholders for values that will be passed as part of the query to the database11125.12 PreparedStatementsProgram must specify the parameter values by using the PreparedStatement interface’s set methods. For the preceding query, both parameters are strings that can be set with PreparedStatement method setString as follows: authorBooks.setString( 1, "Deitel" );authorBooks.setString( 2, "Paul" );setString automatically escapes String parameter values as necessary (e.g., the quote in the name O’Brien)More info at java.sun.com/javase/6/docs/api/java/sql/PreparedStatement.html112Performance Tip 25.2PreparedStatements are more efficient than Statements when executing SQL statements multiple times and with different parameter values.113Error-Prevention Tip 25.1Use PreparedStatements with parameters for queries that receive String values as arguments to ensure that the Strings are quoted properly in the SQL statement.114OutlinePerson.java(1 of 3 ) 115OutlinePerson.java(2 of 3 ) 116OutlinePerson.java(3 of 3 ) 117OutlinePersonQueries .java(1 of 7 ) All program to use PreparedStatementsDeclare PreparedStatementsNote that we do not load the Java DB driver first. JDBC 4’s automatic driver discovery is used here.118OutlinePersonQueries .java(2 of 7 ) Configure each PreparedStatement. Each ? represents a parameter.119OutlinePersonQueries .java(3 of 7 ) Executes the query in PreparedStatement selectAllPeople.Process the ResultSet.120OutlinePersonQueries .java(4 of 7 ) Specify the parameter to PreparedStatement selectPeopleByLastName.Executes the query in PreparedStatement selectPeopleByLastName.121OutlinePersonQueries .java(5 of 7 ) Process the ResultSet.122OutlinePersonQueries .java(6 of 7 ) Specify the parameters to PreparedStatement insertNewPerson.Executes the insert operation in PreparedStatement insertNewPerson.123OutlinePersonQueries .java(7 of 7 ) 124OutlineAddressBook Display.java(1 of 14 ) 125OutlineAddressBook Display.java(2 of 14 ) 126OutlineAddressBook Display.java(3 of 14 ) 127OutlineAddressBook Display.java(4 of 14 ) 128OutlineAddressBook Display.java(5 of 14 ) 129OutlineAddressBook Display.java(6 of 14 ) 130OutlineAddressBook Display.java(7 of 14 ) 131OutlineAddressBook Display.java(8 of 14 ) 132OutlineAddressBook Display.java(9 of 14 ) 133OutlineAddressBook Display.java(10 of 14 ) Executes the query in PreparedStatement selectPeopleByLastName from class PersonQueries.134OutlineAddressBook Display.java(11 of 14 ) 135OutlineAddressBook Display.java(12 of 14 ) Executes the query in PreparedStatement selectAllPeople from class PersonQueries.136OutlineAddressBook Display.java(13 of 14 ) Executes the insert operation in PreparedStatement insertNewPerson from class PersonQueries.137OutlineAddressBook Display.java(14 of 14 ) 13825.12 Stored ProceduresStored proceduresStore SQL statements in a databaseInvoke SQL statements by programs accessing the databaseInterface CallableStatementReceive argumentsOutput parameters139Portability Tip 25.6Although the syntax for creating stored procedures differs across database management systems, the interface CallableStatement provides a uniform interface for specifying input and output parameters for stored procedures and for invoking stored procedures.140Portability Tip 25.7According to the Java API documentation for interface CallableStatement, for maximum portability between database systems, programs should process the update counts or ResultSets returned from a CallableStatement before obtaining the values of any output parameters.14125.13 Transaction ProcessingMany applications require guarantees that a series of database insertions, updates and deletions executes properly before the applications continue processing the next database operationEnables a program that interacts with a database to treat a database operation (or set of operations) as a single operationKnown as an atomic operation or a transactionAt the end of a transaction, decide to commit or roll back14225.13 Transaction ProcessingCommitting a transaction finalizes the database operation(s); all insertions, updates and deletions performed as part of the transaction cannot be reversed without performing a new database operationRolling back a transaction leaves the database in its state prior to the database operation14325.13 Transaction ProcessingMethods of interface ConnectionsetAutoCommit specifies whether each SQL statement commits after it completes (a true argument) or if several SQL statements should be grouped as a transaction (a false argument)If the argument to setAutoCommit is false, the program must follow the last SQL statement in the transaction with a call to Connection method commit or rollback getAutoCommit determines the autocommit state for the Connection. 144