13.2 Simple Database Access

only for RuBoard - do not distribute or recompile

The Connect example did not do much. It simply showed you how to connect to MySQL. A database connection is useless unless you actually talk to the database. The simplest forms of database access are SELECT , INSERT , UPDATE , and DELETE statements. Under the JDBC API, you use your database Connection instance to create Statement instances. A Statement represents any kind of SQL statement. Example 13-4 shows how to insert a row into a database using a Statement .

Example 13-4. Inserting a row into MySQL using a JDBC Statement object

 import java.sql.*; import java.util.*; public class Insert {     // We are inserting into a table that has two columns: TEST_ID (int)     // and TEST_VAL (char(55))     // args[0] is the TEST_ID and args[1] the TEST_VAL     public static void main(String argv[]) {         Connection con = null;         ResourceBundle bundle = ResourceBundle.getBundle("SelectResource");         try {             String url = bundle.getString("URL");             Statement stmt;             Class.forName(bundle.getString("Driver"));             // here is where the connection is made                con = DriverManager.getConnection(url, "user", "pass");              stmt = con.createStatement(  );             stmt.executeUpdate("INSERT INTO TEST (TEST_ID, TEST_VAL) " +                                "VALUES(" + args[0] + ", '" + args[1] + "')");         }         catch( SQLException e ) {             e.printStackTrace(  );         }         finally {             if( con != null ) {                 try { con.close(  ); }                 catch( Exception e ) { }             }         }     } }

If this were a real application, we would of course verify that the user entered an INT for the TEST_ID , that it was not a duplicate key, and that the TEST_VAL entry did not exceed 55 characters . This example nevertheless shows how simple it is to perform an insert. The createStatement( ) method does just what it says: it creates an empty SQL statement associated with the Connection in question. The executeUpdate( ) method then passes the specified SQL on to the database for execution. As its name implies, executeUpdate( ) expects SQL that will modify the database in some way. You can use it to insert new rows, as shown earlier, or to delete rows, update rows, create new tables, or do any other database modification.

13.2.1 Queries and Result Sets

Queries are a bit more complicated than updates because queries return information from the database in the form of a ResultSet . A ResultSet is an interface that represents zero or more rows matching a database query. A JDBC Statement has an executeQuery( ) method that works like the executeUpdate( ) method, except it returns a ResultSet from the database. Exactly one ResultSet is returned by executeQuery( ) . JDBC supports the retrieval of multiple result sets, but MySQL does not. You may notice code for multiple result sets if you look at code written for another database.

Example 13-5 shows a simple query. Figure 13-2 shows the data model behind the test table.

Example 13-5. A simple query

 import java.sql.*; import java.util.*; public class Select {     public static void main(String argv[]) {         Connection con = null;         ResourceBundle bundle =               ResourceBundle.getBundle("SelectResource");         try {             String url = bundle.getString("URL");             Statement stmt;             ResultSet rs;               Class.forName(bundle.getString("Driver"));             // here is where the connection is made                con = DriverManager.getConnection(url, "user", "pass");              stmt = con.createStatement(  );             rs = stmt.executeQuery("SELECT * from TEST ORDER BY TEST_ID");              System.out.println("Got results:");             while(rs.next(  )) {                 int a= rs.getInt("TEST_ID");                 String str = rs.getString("TEST_VAL");                 System.out.print(" key= " + a);                 System.out.print(" str= " + str);                 System.out.print("\n");             }             stmt.close(  );         }         catch( SQLException e ) {             e.printStackTrace(  );         }         finally {             if( con != null ) {                 try { con.close(  ); }                 catch( Exception e ) { }             }         }     } }

Figure 13-2. The test table from the sample database

The Select application executes the query and loops through each row in the ResultSet using the next( ) method. Until the first call to next( ) , the ResultSet does not point to any row. Each call to next( ) points the ResultSet to the subsequent row. You are done processing rows when next( ) returns false .

You can specify that your result set is scrollable, meaning you can move around in the result set ”not just forward on a row-by-row basis. The ResultSet instances generated by a Statement are scrollable if the statement was created to support scrollable result sets. Connection enables this through an alternate form of the createStatement( ) method:

 Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,                                        ResultSet.CONCUR_READ_ONLY);

The first argument says that any result sets of the newly created statement should be scrollable. By default, a statement's result sets are not scrollable. The second argument relates to an advanced feature of JDBC, updatable result sets, which lie beyond the scope of this book. They are described in Database Programming with JDBC and Java , by George Reese (O'Reilly).

With a scrollable result set, you can make calls to previous( ) to navigate backwards through the results and to absolute( ) and relative( ) to move to arbitrary rows. Like next( ) , previous( ) moves one row through the result set, except in the opposite direction. The previous( ) method returns false when you attempt to move before the first row. Finally, absolute( ) moves the result set to a specific row, whereas relative( ) moves the result set a specific number of rows before or after the current row.

Dealing with a row means getting the values for each of its columns. Whatever the value in the database, you can use the getter methods in the ResultSet to retrieve the column value as the Java data type you want. In the Select application, the call to getInt( ) returned the TEST_ID column as an int , and the call to getString( ) returned the TEST_VAL column as a String . These getter methods accept either the column number ”starting with column 1 ”or the column name. You should, however, avoid retrieving values using a column name because it is much slower than retrieving them by column number.

One area of mismatch between Java and MySQL lies in the concept of an SQL NULL . Specifically, SQL is able to represent some data types as null that Java cannot represent as null. In particular, Java has no way of representing primitive data types as nulls. As a result, you cannot immediately determine whether a 0 returned from MySQL through getInt( ) means a 0 is in that column or no value is in that column.

JDBC addresses this mismatch through the wasNull( ) method. As its name implies, wasNull( ) returns true if the last value fetched was SQL NULL . For calls returning a Java object, the value will generally be NULL when an SQL NULL is read from the database. In these instances, wasNull( ) may appear somewhat redundant. For primitive datatypes, however, a valid value ”such as 0 ”may be returned on a fetch. The wasNull( ) method gives you a way to see if that value was NULL in the database.

13.2.2 Error Handling and Clean Up

All JDBC method calls can throw SQLException or one of its subclasses if something happens during a database call. Your code should be set up to catch this exception, deal with it, and clean up any database resources that have been allocated. Each of the JDBC classes mentioned so far has a close( ) method associated with it. Practically speaking, however, you only really need to make sure you close things whose calling processes might remain open for a while. In the examples we have seen so far, you only really need to close your database connections. Closing the database connection closes any statements and result sets associated with it automatically. If you intend to leave a connection open for any period of time, however, it is a good idea to close the statements you create using that connection when you finish with them. In the JDBC examples you have seen, this clean up happens in a finally clause. You do this since you want to make sure to close the database connection no matter what happens.

only for RuBoard - do not distribute or recompile