Simple Database Access (MySQL & mSQL)

14.2. Simple Database Access

The Connect example did not do much. It simply showed you how to connect to a database. 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 naturally represents any kind of SQL statement. Example 14-1 shows how to insert a row into a database using a Statement.

Example 14-1. Inserting a Row into mSQL 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, "borg", ""); 
            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 verified 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 performing an insert is. 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 be modifying the database in some way. You can use it to insert new rows as shown earlier, or instead to delete rows, update rows, create new tables, or do any other sort of database modification.

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(), however, you should be aware that JDBC supports the retrieval of multiple result sets for databases that support multiple result sets. Neither MySQL or mSQL support multiple result sets. It is nevertheless important for you to be aware of this issue in case you are ever looking at someone else's code written against another database engine. Example 14-2 shows a simple query. Figure 14-1 shows the data model behind the test table.

Example 14-2. 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, "borg", ""); 
            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 14-1. The test table from the sample database

The Select application executes the query and then 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. JDBC 2.0 introduces the concept of a scrollable result set. If your ResultSet instance is set to be scrollable, you can also make calls to previous() to navigate backwards through the results. You are done processing rows when next() returns false.

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 whatever Java datatype you like. 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 at all costs since retrieving results by column name is many, many times slower than retrieving them by column number.

14.2.1. 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 process 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 go ahead and 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.