Problem

You need to have the ability to utilize a database from your application, so you need to obtain drivers and configure the databases for your application.

Solution

Download the appropriate drivers for the database that you will be working with, and add them to the CLASSPATH for your application. In this solution, I will assume you are going to develop an enterprise-level web application and deploy it to the GlassFish application server. The application will utilize Oracle Database for persistence. In this case, you will need to download the most current Oracle database driver for Java Database Connectivity (JDBC). At the time of this writing, the driver is ojdbc6.jar, but you can find the latest online at www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html.

Once you have downloaded the required drivers for your database, add them to the application CLASSPATH. If using an IDE, you can adjust the project properties for your application project accordingly to include the JAR that contains your database driver. If you are working from the command line or terminal, you can add the driver to your CLASSPATH by issuing one of the following commands, depending upon the OS platform you are using.

Use the following on Unix-based systems or OS X:

export CLASSPATH=/path-to-jar/ojdbc6.jar

Use the following on Windows:

set CLASSPATH=C:path-to-jarojdbc6.jar

You should now be able to work with the database from your application, but in order to deploy to the GlassFish application server, you will need to make the database driver available for GlassFish. You can do this by copying the JAR containing the database driver into the GlassFish lib directory. If using GlassFish v4, the database driver JAR should be placed within a domain rather than at the application server level. Therefore, if your domain is named domain1 (the default), then the path to where the JAR should be placed would be as follows:

/GlassFish_Home/glassfish4/glassfish/domains/domain1/lib/databases

Restart the application server, and you are ready to deploy your database application.

How It Works

The first step to working with any database from an application is to configure the database driver for the specific vendor of your choice. Whether you decide to use MySQL, PostgreSQL, Oracle, Microsoft SQL, or another database, most enterprise-level databases have a JDBC driver available. This driver must be added to the application CLASSPATH and integrated development environment (IDE) project CLASSPATH if using one. If working from the command line or terminal, you will need to set the CLASSPATH each time you open a new session. If using an IDE, your settings can usually be saved so that you need to configure them only one time. After the driver for your database has been added to the application or project CLASSPATH, you are ready to begin working with the database.

When it comes time to deploy the application to a server, you will need to ensure that the server has access to the database driver. If using GlassFish v4, you can simply add the driver JAR for your database to the domain’s lib directory and restart the server. Once you’ve done this, then you can either deploy your JDBC-based application or set up a database connection pool for your database. Please see Recipe 7-2 for more information on how to connect to your database from within an application using standard JDBC connectivity or how to set up a JDBC connection pool via the GlassFish application server.

Problem

You need to connect to a database so that your application can perform database transactions.

Solution #1

Perform a JDBC connection to the database from within your application. Do this by creating a new Connection object, and then load the driver that you need to use for your particular database. Once the Connection object is ready, call its getConnection() method. The following code demonstrates how to obtain a connection to an Oracle database:

public final static class OracleConnection {
     
    private String hostname = "myHost";
    private String port = "1521";
    private String database = "myDatabase";
    private String username = "user";
    private String password = "password";
     
    public static Connection getConnection() throws SQLException {
        Connection conn = null;
        String jdbcUrl = "jdbc:oracle:thin:@" + this.hostname + ":"
                + this.port + ":" + this.database;
        conn = DriverManager.getConnection(jdbcUrl, username, password);
        System.out.println("Successfully connected");
        return conn;
    }
}

The method portrayed in this example returns a Connection object that is ready to be used for database access.

Solution #2

Configure a database connection pool within an application server, and connect to it from your application. Use a DataSource object to create a connection pool. The DataSource object must have been properly implemented and deployed to an application server environment. After a DataSource object has been implemented and deployed, it can be used by an application to obtain a connection to a database.

To create a connection pool using the GlassFish administrative console, first log into the console by visiting http://localhost:4848 (assuming you are on the same machine as the server and that your GlassFish installation is using the default port numbers). Once successfully logged into the console, click the JDBC menu under Resources, and then expand the JDBC Connection Pools menu, as shown in Figure 7-1.

Click the New button on the JDBC Connection Pools screen, and it will then navigate you to the New JDBC Connection Pool (Step 1 of 2) screen. There, you can name the pool, select a resource type, and select a database driver vendor. For this example, I am using Oracle Database 11gR2. Therefore, the entries should be specified like those shown in Figure 7-2, although you could change the pool name to something you like better.

When the next screen opens, it should automatically contain the mappings for your Oracle database DataSource Classname as oracle.jdbc.pool.OracleDataSource. If it does not look like Figure 7-3, then you may not yet have the ojdbc6.jar database driver in the application server lib directory. Be sure to check the Enabled check box next to the Ping option.

Lastly, go down to the bottom of the second screen, and check all the properties within the Additional Properties table with the exception of User, Password, and URL. Please specify the information for these properties according to the database you will be connecting against, as shown in Figure 7-4. Once you populated them accordingly, click the Finish button.

After clicking Finish, you should see a message indicating that the “ping” has succeeded. Now you can set up your JDBC resource by clicking the JDBC Resources menu within the left tree menu. When the JDBC Resources screen appears, click the New… button. Enter a JNDI name for your resource, beginning with jdbc/, and then select the pool name for the database connection pool you just created. The screen should resemble Figure 7-5. Once you’ve populated it accordingly, click the OK button to complete the creation of the resource.

You can use the following code to obtain a database connection via a DataSource object:

public static Connection getDSConnection() {
    Connection conn = null;
    try {
        Context ctx = new InitialContext();
        DataSource ds = (DataSource)ctx.lookup("jdbc/OracleConnection");
        conn = ds.getConnection();
    } catch (NamingException | SQLException ex) {
        ex.printStackTrace();
    }
        return conn;
}

Notice that the only information required in the DataSource implementation is the name of a valid DataSource object. All the information that is required to obtain a connection with the database is managed within the application server.

How It Works

You have a couple of options for creating database connections for use within Java applications. If you are writing a stand-alone or desktop application, usually a standard JDBC connection is the best choice. However, if working with an enterprise-level or web-based application, DataSource objects may be the right choice. Solution #1 for this recipe covers the former option, and it is the easiest way to create a database connection in a stand-alone environment. I will cover the creation of a JDBC Connection via Solution #1 first.

Once you’ve determined which database you are going to use, you will need to obtain the correct driver for the database vendor and release of your choice. Please see Recipe 7-1 for more information on obtaining a driver and placing it into your CLASSPATH for use. Once you have the JAR file in your application CLASSPATH, you can use a JDBC DriverManager to obtain a connection to the database. As of JDBC version 4.0, drivers that are contained within the CLASSPATH are automatically loaded into the DriverManager object. If you are using a JDBC version prior to 4.0, the driver will have to be manually loaded.

To obtain a connection to your database using the DriverManager, you need to pass a String containing the JDBC URL to it. The JDBC URL consists of the database vendor name, along with the name of the server that hosts the database, the name of the database, the database port number, and a valid database user name and password that has access to the schema you want to work with. Many times, the values used to create the JDBC URL can be obtained from a properties file so that the values can be easily changed if needed. To learn more about using a properties file to store connection values, please see Recipe 7-4. The code that is used to create the JDBC URL for Solution #1 looks like the following:

String jdbcUrl = "jdbc:oracle:thin:@" + this.hostname + ":" +
this.port + ":" + this.database;

Once all the variables have been substituted into the String, it will look something like the following:

jdbc:oracle:thin:@hostname:1521:database

Once the JDBC URL has been created, it can be passed to the DriverManager.getConnection() method to obtain a java.sql.Connection object. If incorrect information has been passed to the getConnection method, a java.sql.SQLException will be thrown; otherwise, a valid Connection object will be returned.

If running on an application server, such as GlassFish, the preferred way to obtain a connection is to use a DataSource. To work with a DataSource object, you need to have an application server to deploy it to. Any compliant Java application server such as Apache Tomcat, GlassFish, Oracle WebLogic, or JBoss will work. Most of the application servers contain a web interface that can be used to easily deploy a DataSource object, such as demonstrated via GlassFish in Solution #2 to this recipe. However, you can manually deploy a DataSource object by using code that will look like the following:

org.javaeerecipes.chapter7.recipe07_02.FakeDataSourceDriver ds =
new org.javaeerecipes.chapter7.recipe07_02.FakeDataSourceDriver();
ds.setServerName("my-server");
ds.setDatabaseName("JavaEERecipes");
ds.setDescription("Database connection for Java EE 7 Recipes");

This code instantiates a new DataSource driver class, and then it sets properties based upon the database you want to register. DataSource code such as that demonstrated here is typically used when registering a DataSource in an application server or with access to a JNDI server. Application servers usually do this work behind the scenes if you are using a web-based administration tool to deploy a DataSource. Most database vendors will supply a DataSource driver along with their JDBC drivers, so if the correct JAR resides within the application or server CLASSPATH, it should be recognized and available for use. Once a DataSource has been instantiated and configured, the next step is to register the DataSource with a JNDI naming service.

The following code demonstrates the registration of a DataSource with JNDI:

try {
    Context ctx = new InitialContext();
    DataSource ds =
    (DataSource) ctx.bind("jdbc/OracleConnection");
} catch (NamingException ex) {
    ex.printStackTrace();
}

Once the DataSource has been deployed, any application that has been deployed to the same application server will have access to it. The beauty of working with a DataSource object is that your application code doesn’t need to know anything about the database; it needs to know only the name of the DataSource. Usually the name of the DataSource begins with a jdbc/ prefix, followed by an identifier. To look up the DataSource object, an InitialContext is used. The InitialContext looks at all the DataSources available within the application server, and it returns a valid DataSource if it is found; otherwise, it will throw a java.naming.NamingException exception. In Solution #2, you can see that the InitialContext returns an object that must be cast as a DataSource.

Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("jdbc/OracleConnection");

If the DataSource is a connection pool cache, the application server will send one of the available connections within the pool when an application requests it. The following line of code returns a Connection object from the DataSource:

conn = ds.getConnection();

Of course, if no valid connection can be obtained, a java.sql.SQLException is thrown. The DataSource technique is preferred over manually specifying all details and passing to the DriverManager because database connection information is stored in only one place: the application server, not within each application. Once a valid DataSource is deployed, it can be used by many applications.

After a valid connection has been obtained by your application, it can be used to work with the database. To learn more about working with the database using a Connection object, please see the recipes within this chapter regarding working with the database.

Problem

A database activity in your application has thrown an exception. You need to handle that SQL exception so your application does not crash.

Solution

Use a try-catch block to capture and handle any SQL exceptions that are thrown by your JDBC connection or SQL queries. The following code demonstrates how to implement a try-catch block in order to capture SQL exceptions:

try {
// perform database tasks
} catch (java.sql.SQLException) {
// perform exception handling
}

How It Works

A standard try-catch block can be used to catch java.sql.Connection or java.sql.SQLException exceptions. Your code will not compile if these exceptions are not handled, and it is a good idea to handle them in order to prevent your application from crashing if one of these exceptions is thrown. Almost any work that is performed against a java.sql.Connection object will need to perform error handling to ensure that database exceptions are handled correctly. In fact, nested try-catch blocks are often required to handle all the possible exceptions. You need to ensure that connections are closed once work has been performed and the Connection object is no longer used. Similarly, it is a good idea to close java.sql.Statement objects for memory allocation cleanup.

Because Statement and Connection objects need to be closed, it is common to see try-catch-finally blocks used to ensure that all resources have been tended to as needed. It is not unlikely that you will see JDBC code that resembles the following style:

try {
    // perform database tasks
} catch (java.sql.SQLException ex) {
    // perform exception handling
} finally {
    try {
    // close Connection and Statement objects
    } catch (java.sql.SQLException ex) {
    // perform exception handling
    }
}

As shown in the previous pseudo-code, nested try-catch blocks are often required in order to clean up unused resources. Proper exception handling sometimes makes JDBC code rather laborious to write, but it will also ensure that an application requiring database access will not fail, causing data to be lost.

Problem

Your application requires the use of a database. To work with the database, you need to open a connection. Rather than code the logic to open a database connection every time you need to access the database, you want to simplify the connection process.

Solution

Write a class to handle all the connection management within your application. Doing so will allow you to call that class in order to obtain a connection, rather than setting up a new Connection object each time you need access to the database. Perform the following steps to set up a connection management environment for your JDBC application:

1. Create a class named CreateConnection.java that will encapsulate all the connection logic for your application.
2. Create a properties file to store your connection information. Place the file somewhere on your CLASSPATH so that the CreateConnection class can load it.
3. Use the CreateConnection class to obtain your database connections.

The following code is the org.javaeerecipes.chapter07 .CreateConnection class that can be used for centralized connection management:

package org.javaeerecipes.chapter07;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.FileSystems;
import java.nio.file.Files;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public final class CreateConnection {
     
    static Properties props = new Properties();
    static String hostname = null;
    static String port = null;
    static String database = null;
    static String username = null;
    static String password = null;
    static String jndi = null;

    public CreateConnection() {
         
    }

    public static void loadProperties() {
        // Return if the host has already been loaded
        if(hostname != null){
            return;
        }
         
        try(InputStream in = Files.newInputStream(FileSystems.getDefault().getPath(System.getProperty("user.dir")
+ File.separator + "db_props.properties"));) {
            // Looks for properties file in the root of the src directory in Netbeans project
             
            System.out.println(FileSystems.getDefault().getPath(System.getProperty("user.dir")
                    + File.separator + "db_props.properties"));
            props.load(in);
       
        } catch (IOException ex) {
            ex.printStackTrace();
        }
         
        hostname = props.getProperty("host_name");
        port = props.getProperty("port_number");
        database = props.getProperty("db_name");
        username = props.getProperty("username");
        password = props.getProperty("password");
        jndi = props.getProperty("jndi");
        System.out.println(hostname);
    }

    /**
     * Demonstrates obtaining a connection via DriverManager
     *
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        Connection conn = null;
        String jdbcUrl = "jdbc:oracle:thin:@" + hostname + ":"
                + port + ":" + database;
        conn = DriverManager.getConnection(jdbcUrl, username, password);
        System.out.println("Successfully connected");
        return conn;
    }

    /**
     * Demonstrates obtaining a connection via a DataSource object
     *
     * @return
     */
    public static Connection getDSConnection() {
        Connection conn = null;
        try {
            Context ctx = new InitialContext();
            DataSource ds = (DataSource) ctx.lookup(jndi);
            conn = ds.getConnection();
        } catch (NamingException | SQLException ex) {
            ex.printStackTrace();
        }
        return conn;
    }

    public static void main(String[] args) {
        Connection conn = null;
        try {
            CreateConnection.loadProperties();
            System.out.println("Beginning connection..");
            conn = CreateConnection.getConnection();
            //performDbTask();
        } catch (java.sql.SQLException ex) {
            System.out.println(ex);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }

    }
}

Next, the following lines of code are an example of what should be contained in the properties file that is used for obtaining a connection to the database. For this example, the properties file is named db_props.properties.

host_name=your_db_server_name
db_name=your_db_name
username=db_username
password=db_username_password
port_number=db_port_number
jndi=jndi_connection_string

Finally, use the CreateConnection class to obtain connections for your application. The following code demonstrates this concept:

Connection conn = null;
try {
     CreateConnection.loadProperties();
     System.out.println("Beginning connection..");
     conn = CreateConnection.getConnection();
     //performDbTask();
} catch (java.sql.SQLException ex) {
     System.out.println(ex);
} finally {
     if (conn != null) {
         try {
             conn.close();
         } catch (SQLException ex) {
             ex.printStackTrace();
         }
     }
}

To run the code for testing, execute the class org.javaeerecipes.chapter07.CreateConnection.java because it contains a main method for testing purposes.

How It Works

Obtaining a connection within a database application can be code intensive. Moreover, the process can be prone to error if you retype the code each time you need to obtain a connection. By encapsulating database connection logic within a single class, you can reuse the same connection code each time you require a connection to the database. This increases your productivity, reduces the chances of typing errors, and also enhances manageability because if you have to make a change, it can occur in one place rather than in several different locations.

Creating a strategic connection methodology is beneficial to you and others who might need to maintain your code in the future. Although data sources are the preferred technique for managing database connections when using an application server or JNDI, the solution to this recipe demonstrates how to use standard JDBC DriverManager connections. One of the security implications of using the DriverManager is that you will need to store the database credentials somewhere for use by the application. It is not safe to store those credentials in plain text anywhere, and it is also not safe to embed them in application code, which might be decompiled at some point in the future. As seen in the solution, a properties file that resides on disk is used to store the database credentials. Assume that this properties file will be encrypted at some point before deployment to a server.

As shown in the solution, the code reads the database credentials, host name, database name, and port number from the properties file. That information is then pieced together to form a JDBC URL that can be used by DriverManager to obtain a connection to the database. Once obtained, that connection can be used anywhere and then closed. Similarly, if using a DataSource that has been deployed to an application server, the properties file can be used to store the JNDI connection. That is the only piece of information that is needed to obtain a connection to the database using the DataSource. To the developer, the only difference between the two types of connections would be the method name that is called in order to obtain the Connection object, those being getDsConnection or getConnection in the example.

You could develop a JDBC application so that the code that is used to obtain a connection needs to be hard-coded throughout. Instead, this solution enables all the code for obtaining a connection to be encapsulated by a single class so that the developer does not need to worry about it. Such a technique also allows the code to be more maintainable. For instance, if the application were originally deployed using the DriverManager but then later had the ability to use a DataSource, very little code would need to be changed.

Problem

You have a table that contains authors within the company database, and you want to query that table to retrieve the records.

Solution

Obtain a JDBC connection using one of the techniques covered in Recipe 7-2 or Recipe 7-4; then use the java.sql.Connection object to create a Statement object. A java.sql.Statement object contains the executeQuery method, which can be used to parse a String of text and use it to query a database. Once you’ve executed the query, you can retrieve the results of the query into a ResultSet object. The following example, excerpted from the org.javaeerecipes.chapter07.dao.AuthorDAO class, queries a database table named BOOK_AUTHOR and prints the results to the server log:

public void queryBookAuthor() {
    String qry = "select id, first, last, bio from book_author";
    CreateConnection.loadProperties();
    try (Connection conn = CreateConnection.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(qry);) {

  
        while (rs.next()) {
            int author_id = rs.getInt("ID");
            String first_name = rs.getString("FIRST");
            String last_name = rs.getString("LAST");
            String bio = rs.getString("BIO");
            System.out.println(author_id + "	" + first_name
                    + " " + last_name + "	" + bio);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }

}

Executing this method against the database schema that ships with this book will produce the following results, considering that the BIO column is null for each author record:

Successfully connected
2    JOSH JUNEAU    null
3    CARL DEA    null
4    MARK BEATY    null
5    FREDDY GUIME    null
6    OCONNER JOHN    null

How It Works

One of the most commonly performed operations against a database is a query. Performing database queries using JDBC is quite easy, although there is a bit of boilerplate code that needs to be used each time a query is executed. First, you need to obtain a Connection object for the database and schema that you want to run the query against. You can do this by using one of the solutions in Recipe 7-2. Next, you need to form a query and store it in String format. The CreateConnection properties are then loaded via a call to the loadProperties method, which ensures that the db_props.properties file is used to populate database connection information. Next, a try-with-resources clause is used to create the objects that are necessary for querying the database. Since the objects are instantiated within the try-with-resources, then they will be closed automatically once they are no longer being used. The Connection object is then used to create a Statement. Your query String will be passed to the Statement object’s executeQuery method in order to actually query the database.

String qry = "select id, first, last, bio from book_author";
        CreateConnection.loadProperties();
        try (Connection conn = CreateConnection.getConnection();
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(qry);) {
...

As you can see, the Statement object’s executeQuery method accepts a String and returns a ResultSet object. The ResultSet object makes it easy to work with the query results so that you can obtain the information you need in any order. If you take a look at the next line of code, a while loop is created on the ResultSet object. This loop will continue to call the ResultSet object’s next method, obtaining the next row that is returned from the query with each iteration. In this case, the ResultSet object is named rs, so while rs.next() returns true, the loop will continue to be processed. Once all the returned rows have been processed, rs.next() will return a false to indicate that there are no more rows to be processed.

Within the while loop, each returned row is processed. The ResultSet object is parsed to obtain the values of the given column names with each pass. Notice that if the column is expected to return a String, you must call the ResultSet getString method, passing the column name in String format. Similarly, if the column is expected to return an int, you’d call the ResultSet getInt method, passing the column name in String format. The same holds true for the other data types. These methods will return the corresponding column values. In the example in the solution to this recipe, those values are stored into local variables.

int author_id = rs.getInt("ID");
String first_name = rs.getString("FIRST");
String last_name = rs.getString("LAST");
String bio = rs.getString("BIO");

Once the column value has been obtained, you can do what you want to do with the values you have stored within local variables. In this case, they are printed out using the System.out() method. Notice that there is a try-catch-finally block used in this example. A java.sql.SQLException could be thrown when attempting to query a database (for instance, if the Connection object has not been properly obtained or if the database tables that you are trying to query do not exist). You must provide exception handling to handle errors in these situations. Therefore, all database-processing code should be placed within a try block. The catch block then handles a SQLException, so if one is thrown, the exception will be handled using the code within the catch block. Sounds easy enough, right? It is, but you must do it each time you perform a database query. That means lots of boilerplate code. Inside the finally block, you will see that the Statement and Connection objects are closed if they are not equal to null.

Problem

You need to have the ability to perform standard database operations from within your enterprise application. That is, you need to have the ability to create, retrieve, update, and delete (CRUD) database records.

Solution

Create a Connection object and obtain a database connection using one of the solutions provided in Recipe 7-2; then perform the CRUD operation using a java.sql.Statement object that is obtained from the java.sql.Connection object. The following code, taken from org.javaeerecipes.chapter07.recipe07_06.AuthorDAOStandard.java, demonstrates how to perform each of the CRUD operations against the BOOK_AUTHORS table using JDBC, with the exception of the query (retrieve) since that is already covered in Recipe 7-5.

/**
     * Do not use this method in production, instead make use of
     * PreparedStatements
     *
     * @param first
     * @param last
     * @param bio
     */
    private void performCreate(String first, String last, String bio) {
        String sql = "INSERT INTO BOOK_AUTHOR VALUES("
                + "BOOK_AUTHOR_S.NEXTVAL, "
                + "'" + last.toUpperCase() + "', "
                + "'" + first.toUpperCase() + "', "
                + "'" + bio.toUpperCase() + "')";
        try (Connection conn = CreateConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {
            // Returns row-count or 0 if not successful
            int result = stmt.executeUpdate();
            if (result > 0) {
                System.out.println("-- Record created --");
            } else {
                System.out.println("!! Record NOT Created !!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void performUpdate(String first, String last, String bio) {
        String sql = "UPDATE BOOK_AUTHOR "
                + "SET bio = '" + bio.toUpperCase() + "' "
                + "WHERE last = '" + last.toUpperCase() + "' "
                + "AND first = '" + first.toUpperCase() + "'";
        try (Connection conn = CreateConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {
            int result = stmt.executeUpdate();
            if (result > 0) {
                System.out.println("-- Record Updated --");

            } else {
                System.out.println("!! Record NOT Updated !!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void performDelete(String first, String last) {
        String sql = "DELETE FROM BOOK_AUTHOR WHERE LAST = '" + last.toUpperCase() + "' "
                + "AND FIRST = '" + first.toUpperCase() + "'";
        try (Connection conn = CreateConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {
            int result = stmt.executeUpdate();
            if (result > 0) {
                System.out.println("-- Record Deleted --");
            } else {
                System.out.println("!! Record NOT Deleted!!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

Executing the following main method will produce the results that follow:

public static void main(String[] args) {
    AuthorDAO authorDao = new AuthorDAO();
    authorDao.queryBookAuthor();
    authorDao.performCreate("Joe", "Blow", "N/A");
    authorDao.performUpdate("Joe", "Blow", "Joes Bio");
    authorDao.queryBookAuthor();
    authorDao.performDelete("Joe", "Blow");
}

The results from running the main method should be similar to the following:

Successfully connected
2    JOSH JUNEAU    null
3    CARL DEA    null
4    MARK BEATY    null
5    FREDDY GUIME    null
6    OCONNER JOHN    null
Successfully connected
-- Record created --

Successfully connected
-- Record Updated --
Successfully connected
2    JOSH JUNEAU    null
3    CARL DEA    null
4    MARK BEATY    null
5    FREDDY GUIME    null
6    OCONNER JOHN    null
105    JOE BLOW    JOES BIO

Successfully connected
-- Record Deleted --

How It Works

The same basic code format is used for performing just about every database task. The format is as follows:

1. Obtain a connection to the database within the try clause.
2. Create a statement from the connection within the try clause.
3. Perform a database task with the statement.
4. Do something with the results of the database task.
5. Close the statement (and database connection if finished using it). This step is done automatically for you if using the try-with-resources clause, as demonstrated in the solution to this recipe.

The main difference between performing a query using JDBC and using Data Manipulation Language (DML) is that you will call different methods on the Statement object, depending on which operation you want to perform. To perform a query, you need to call the Statement executeQuery method. To perform DML tasks, such as insert, update, and delete, call the executeUpdate method.

The performCreate method in the solution to this recipe demonstrates the operation of inserting a record into a database. To insert a record in the database, you will construct a SQL insert statement in String format. To perform the insert, pass the SQL string to the Statement object’s executeUpdate method. If the insert is performed, an int value will be returned that specifies the number or rows that have been inserted. If the insert operation is not performed successfully, either a zero will be returned or a SQLException will be thrown, indicating a problem with the statement or database connection.

The performUpdate method in the solution to this recipe demonstrates the operation of updating record(s) within a database table. First, you will construct a SQL update statement in String format. Next, to perform the update operation, you will pass the SQL string to the Statement object’s executeUpdate method. If the update is successfully performed, an int value will be returned, which specifies the number of records that were updated. If the update operation is not performed successfully, either a zero will be returned or a SQLException will be thrown, indicating a problem with the statement or database connection.

The last database operation that is covered in the solution is the delete operation. The performDelete method in the solution to this recipe demonstrates how to delete records from the database. First, you will construct a SQL delete statement in String format. Next, to execute the deletion, you will pass the SQL string to the Statement object’s executeUpdate method. If the deletion is successful, an int value specifying the number of rows deleted will be returned. Otherwise, if the deletion fails, a zero will be returned, or a SQLException will be thrown, indicating a problem with the statement or database connection.

Almost every database application uses at least one of the CRUD operations at some point. This is foundational JDBC that you need to know if you are working with databases within Java applications. Even if you will not work directly with the JDBC API, it is good to know these basics.

Problem

Your application performs database tasks. To reduce the chances of a SQL injection attack, you need to ensure that no unfiltered Strings of text are being appended to SQL statements and executed against the database.

Solution

Utilize PreparedStatements for performing the database tasks. PreparedStatements send a precompiled SQL statement to the DBMS rather than a clear-text String. The following code demonstrates how to perform a database query and a database update using a java.sql.PreparedStatement object. The following code excerpts are taken from a new data access object named org.javaeerecipes.chapter07.dao.AuthorDAO, which utilizes PreparedStatement objects rather than String concatenation for executing SQL statements:

...
   /**
     * Queries the database for a particular author based upon ID and returns
     * the Author object if found.
     *
     * @param id
     * @return
     */
    public Author performFind(int id) {
        String qry = "SELECT ID, LAST, FIRST, BIO "
                + "FROM BOOK_AUTHOR "
                + "WHERE ID = ?";

        Author author = null;
        CreateConnection.loadProperties();
        try (Connection conn = CreateConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(qry)) {
            stmt.setInt(1, id);
            try (ResultSet rs = stmt.executeQuery();) {

  
                if (rs.next()) {
                    int author_id = rs.getInt("ID");
                    String first_name = rs.getString("FIRST");
                    String last_name = rs.getString("LAST");
                    String bio = rs.getString("BIO");
                    author = new Author(author_id,
                            first_name,
                            last_name,
                            bio);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return author;

  
    }

    /**
     * Queries the database for a particular author based upon first and last
     * name and returns a list of Author objects if found.
     *
     * @param id
     * @return
     */
    public List<Author> performFind(String first, String last) {
        String qry = "SELECT ID, LAST, FIRST, BIO "
                + "FROM BOOK_AUTHOR "
                + "WHERE LAST = ? "
                + "AND FIRST = ?";

        List authorList = new ArrayList();
        try (Connection conn = CreateConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(qry)) {
            stmt.setString(1, last.toUpperCase());
            stmt.setString(2, first.toUpperCase());
            try (ResultSet rs = stmt.executeQuery();) {

                while (rs.next()) {
                    int author_id = rs.getInt("ID");
                    String first_name = rs.getString("FIRST");
                    String last_name = rs.getString("LAST");
                    String bio = rs.getString("BIO");
                    Author author = new Author(author_id,
                            first_name,
                            last_name,
                            bio);
                    authorList.add(author);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return authorList;

  
    }

    /**
     * Do not use this method in production, instead make use of
     * PreparedStatements
     *
     * @param first
     * @param last
     * @param bio
     */
    private void performCreate(String first, String last, String bio) {
        String sql = "INSERT INTO BOOK_AUTHOR VALUES("
                + "BOOK_AUTHOR_S.NEXTVAL, ?, ?, ?)";
        try (Connection conn = CreateConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, last.toUpperCase());
            stmt.setString(2, first.toUpperCase());
            stmt.setString(3, bio.toUpperCase());

  
                // Returns row-count or 0 if not successful
                int result = stmt.executeUpdate();
                if (result > 0) {
                    System.out.println("-- Record created --");
                } else {
                    System.out.println("!! Record NOT Created !!");
                }
             
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void performUpdate(int id, String first, String last, String bio) {
        String sql = "UPDATE BOOK_AUTHOR "
                + "SET bio = ?,"
                + "   last = ?,"
                + "   first = ? "
                + "WHERE ID = ?";
        try (Connection conn = CreateConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, bio.toUpperCase());
            stmt.setString(2, last.toUpperCase());
            stmt.setString(3, first.toUpperCase());
            stmt.setInt(4, id);

                int result = stmt.executeUpdate();
                if (result > 0) {
                    System.out.println("-- Record Updated --");

                } else {
                    System.out.println("!! Record NOT Updated !!");
                }
             
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void performDelete(int id) {
        String sql = "DELETE FROM BOOK_AUTHOR WHERE ID = ?";
        try (Connection conn = CreateConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, id);

                int result = stmt.executeUpdate();
                if (result > 0) {
                    System.out.println("-- Record Deleted --");
                } else {
                    System.out.println("!! Record NOT Deleted!!");
                }
             
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
...

The methods displayed previously exhibit the use of PreparedStatement objects rather than using standard JDBC Statement objects and String concatenation for appending variables into SQL statements.

How It Works

While standard JDBC statements will get the job done, the harsh reality is that they can sometimes be insecure and difficult to work with. For instance, bad things can occur if a dynamic SQL statement is used to query a database and a user-accepted String is assigned to a variable and concatenated with the intended SQL String. In most ordinary cases, the user-accepted String would be concatenated, and the SQL String would be used to query the database as expected. However, an attacker could decide to place malicious code inside the String (aka SQL injection), which would then be inadvertently sent to the database using a standard Statement object. Using PreparedStatements prevents such malicious Strings from being concatenated into a SQL string and passed to the DBMS because they use a different approach. PreparedStatements use substitution variables rather than concatenation to make SQL strings dynamic. They are also precompiled, which means that a valid SQL string is formed prior to the SQL being sent to the DBMS. Moreover, PreparedStatements can help your application perform better because if the same SQL has to be run more than one time, it has to be compiled only once per Oracle session. After that, the substitution variables are interchangeable, but the PreparedStatement can execute the SQL very quickly.

Let’s take a look at how a PreparedStatement works in practice. If you look at the example in the solution to this recipe, you can see that the database table BOOK_AUTHOR is being queried in the performFind method, sending the author’s ID as a substitution variable and retrieving the results for the matching record. The SQL string looks like the following:

String qry = "SELECT ID, LAST, FIRST, BIO "
                    + "FROM BOOK_AUTHOR "
                    + "WHERE ID = ?";

Everything looks standard with the SQL text except for the question mark (?) at the end of the string. Placing a question mark within a string of SQL signifies that a substitute variable will be used in place of that question mark when the SQL is executed. The next step for using a PreparedStatement is to declare a variable of type PreparedStatement. You can see this with the following line of code:

PreparedStatement stmt = null;

Now that a PreparedStatement has been declared, it can be put to use. However, using a PreparedStatement might or might not cause an exception to be thrown. Therefore, any use of a PreparedStatement should occur within a try-catch block so that any exceptions can be handled gracefully. For instance, exceptions can occur if the database connection is unavailable for some reason or if the SQL string is invalid. Rather than crashing an application because of such issues, it is best to handle the exceptions wisely within a catch block. The following try-catch block includes the code that is necessary to send the SQL string to the database and retrieve results:

try (Connection conn = CreateConnection.getConnection();
        PreparedStatement stmt = conn.prepareStatement(qry)) {
    stmt.setInt(1, id);
    try (ResultSet rs = stmt.executeQuery();) {
    if (rs.next()) {
        int author_id = rs.getInt("ID");
        String first_name = rs.getString("FIRST");
        String last_name = rs.getString("LAST");
        String bio = rs.getString("BIO");
        author = new Author(author_id,
                                    first_name,
                                    last_name,
                                    bio);
    }
  }
} catch (SQLException e) {
    e.printStackTrace();
}

First, you can see that the Connection object is used to instantiate a PreparedStatement object. The SQL string is passed to the PreparedStatement object’s constructor upon creation. Next, the PreparedStatement object is used to set values for any substitution variables that have been placed into the SQL string. As you can see, the PreparedStatement setString method is used in the example to set the substitution variable at position 1 equal to the contents of the id variable. The positioning of the substitution variable is associated with the placement of the question mark (?) within the SQL string. The first question mark within the string is assigned to the first position, the second one is assigned to the second position, and so forth. If there were more than one substitution variable to be assigned, there would be more than one call to the PreparedStatement setter methods, assigning each of the variables until each one has been accounted for. PreparedStatements can accept substitution variables of many different data types. For instance, if an Date value were being assigned to a substitution variable, a call to the setDate(position, variable) method would be in order. Please see the online documentation or your IDE’s code completion for a complete set of methods that can be used for assigning substitution variables using PreparedStatement objects.

Once all the variables have been assigned, the SQL string can be executed. The PreparedStatement object contains an executeQuery method that is used to execute a SQL string that represents a query. The executeQuery method returns a ResultSet object, which contains the results that have been fetched from the database for the particular SQL query. Next, the ResultSet can be traversed to obtain the values retrieved from the database. There are two different ways to retrieve the results from the ResultSet. Positional assignments can be used to retrieve the results by calling the ResultSet object’s corresponding getter methods and passing the position of the column value, or the String identifier of the column value that you want to obtain can be passed to the getter methods. If passing the position, it is determined by the order in which the column names appear within the SQL string. In the example, String-based column identifiers are used to obtain the values. As you can see from the example, passing the column identifier to the appropriate getter method will retrieve the value. When the record values from the ResultSet are obtained, they are stored into local variables. Once all the variables have been collected for a particular author, they are stored into an Author object, which will eventually be returned from the method. Of course, if the substitution variable is not set correctly or if there is an issue with the SQL string, an exception will be thrown. This would cause the code that is contained within the catch block to be executed.

If you do not use the try-with-resources clause, as demonstrated in the solution, you should be sure to clean up after using PreparedStatements by closing the statement when you are finished using it. It is a good practice to put all the cleanup code within a finally block to be sure that it is executed even if an exception is thrown. For example, a finally block that is used to clean up unused Statement and Connection objects may look like the following:

finally {
    if (stmt != null) {
        try {
            stmt.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

    }
    if (conn != null) {
        try {
            conn.close();
            conn = null;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    return author;

}

You can see that the PreparedStatement object that was instantiated, stmt, is checked to see whether it is NULL. If not, it is closed by calling the close method. Working through the code in the solution to this recipe, you can see that similar code is used to process database insert, update, and delete statements. The only difference in those cases is that the PreparedStatement executeUpdate method is called rather than the executeQuery method. The executeUpdate method will return an int value representing the number of rows affected by the SQL statement.

The use of PreparedStatement objects is preferred over JDBC Statement objects. This is because they are more secure and perform better. They can also make your code easier to follow and easier to maintain.

Problem

Your application works with an underlying database for storing and retrieving data. You would prefer to code your business logic using Java objects, rather than working directly with JDBC and SQL for performing database activities.

Solution

Create a data access object (DAO) for each database table that will be used to perform the mundane JDBC and SQL work. Within the DAO, create façade methods that accept Java objects to represent a single record of data for the database table for which the DAO has been created. Use the Java objects to pass record data to and from the DAO, while the DAO breaks the objects apart and utilizes the data fields within standard SQL statements.

The following class excerpts demonstrate a data access object for the AUTHOR database table, which is used for storing book author data (a main method has been included merely for testing purposes within this DAO).

...
public class AuthorDAO implements java.io.Serializable {

    public AuthorDAO() {
    }

    public void queryBookAuthor() {
        String qry = "select id, first, last, bio from book_author";
        CreateConnection.loadProperties();
        try (Connection conn = CreateConnection.getConnection();
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(qry);) {

  
            while (rs.next()) {
                int author_id = rs.getInt("ID");
                String first_name = rs.getString("FIRST");
                String last_name = rs.getString("LAST");
                String bio = rs.getString("BIO");
                System.out.println(author_id + "	" + first_name
                        + " " + last_name + "	" + bio);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public List<Author> obtainCompleteAuthorList() {
        String qry = "select id, first, last, bio from book_author";
        List<Author> authors = new ArrayList();
        CreateConnection.loadProperties();
        try (Connection conn = CreateConnection.getConnection();
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(qry);) {
            while (rs.next()) {
                int author_id = rs.getInt("ID");
                String first_name = rs.getString("FIRST");
                String last_name = rs.getString("LAST");
                String bio = rs.getString("BIO");
                Author author = new Author(author_id, first_name,
                        last_name, bio);
                authors.add(author);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return authors;
    }

    /**
     * Queries the database for a particular author based upon ID and returns
     * the Author object if found.
     *
     * @param id
     * @return
     */
    public Author performFind(int id) {
        String qry = "SELECT ID, LAST, FIRST, BIO "
                + "FROM BOOK_AUTHOR "
                + "WHERE ID = ?";

        Author author = null;
        CreateConnection.loadProperties();
        try (Connection conn = CreateConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(qry)) {
            stmt.setInt(1, id);
            try (ResultSet rs = stmt.executeQuery();) {

  
                if (rs.next()) {
                    int author_id = rs.getInt("ID");
                    String first_name = rs.getString("FIRST");
                    String last_name = rs.getString("LAST");
                    String bio = rs.getString("BIO");
                    author = new Author(author_id,
                            first_name,
                            last_name,
                            bio);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return author;

  
    }

    /**
     * Queries the database for a particular author based upon first and last
     * name and returns a list of Author objects if found.
     *
     * @param id
     * @return
     */
    public List<Author> performFind(String first, String last) {
        String qry = "SELECT ID, LAST, FIRST, BIO "
                + "FROM BOOK_AUTHOR "
                + "WHERE LAST = ? "
                + "AND FIRST = ?";

        List authorList = new ArrayList();
        try (Connection conn = CreateConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(qry)) {
            stmt.setString(1, last.toUpperCase());
            stmt.setString(2, first.toUpperCase());
            try (ResultSet rs = stmt.executeQuery();) {

                while (rs.next()) {
                    int author_id = rs.getInt("ID");
                    String first_name = rs.getString("FIRST");
                    String last_name = rs.getString("LAST");
                    String bio = rs.getString("BIO");
                    Author author = new Author(author_id,
                            first_name,
                            last_name,
                            bio);
                    authorList.add(author);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return authorList;

  
    }

    /**
     * Do not use this method in production, instead make use of
     * PreparedStatements
     *
     * @param first
     * @param last
     * @param bio
     */
    private void performCreate(String first, String last, String bio) {
        String sql = "INSERT INTO BOOK_AUTHOR VALUES("
                + "BOOK_AUTHOR_S.NEXTVAL, ?, ?, ?)";
        try (Connection conn = CreateConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, last.toUpperCase());
            stmt.setString(2, first.toUpperCase());
            stmt.setString(3, bio.toUpperCase());

  
                // Returns row-count or 0 if not successful
                int result = stmt.executeUpdate();
                if (result > 0) {
                    System.out.println("-- Record created --");
                } else {
                    System.out.println("!! Record NOT Created !!");
                }
             
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void performUpdate(int id, String first, String last, String bio) {
        String sql = "UPDATE BOOK_AUTHOR "
                + "SET bio = ?,"
                + "   last = ?,"
                + "   first = ? "
                + "WHERE ID = ?";
        try (Connection conn = CreateConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, bio.toUpperCase());
            stmt.setString(2, last.toUpperCase());
            stmt.setString(3, first.toUpperCase());
            stmt.setInt(4, id);

                int result = stmt.executeUpdate();
                if (result > 0) {
                    System.out.println("-- Record Updated --");

                } else {
                    System.out.println("!! Record NOT Updated !!");
                }
             
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void performDelete(int id) {
        String sql = "DELETE FROM BOOK_AUTHOR WHERE ID = ?";
        try (Connection conn = CreateConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, id);

                int result = stmt.executeUpdate();
                if (result > 0) {
                    System.out.println("-- Record Deleted --");
                } else {
                    System.out.println("!! Record NOT Deleted!!");
                }
             
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Returns the next ID in the BOOK_AUTHOR_S sequence
     *
     * @return
     */
    public int getNextId() {
        String qry = "select book_author_s.currval as ID from dual";

        int returnId = -1;
        CreateConnection.loadProperties();
        try (Connection conn = CreateConnection.getConnection();
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(qry);) {

            while (rs.next()) {
                int author_id = rs.getInt("ID");
                returnId = author_id + 1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return returnId;

  
    }

    /**
     * Facade method for inserting Author objects into the BOOK_AUTHOR table
     *
     * @param author
     */
    public void insert(Author author) {
        performCreate(author.getFirst(),
                author.getLast(),
                author.getBio());
    }

    /**
     * Facade method for updating Author objects in the BOOK_AUTHOR table
     *
     * @param author
     */
    public void update(Author author) {
        this.performUpdate(author.getId(), author.getFirst(), author.getLast(), author.getBio());
    }

    /**
     * Facade method for deleting Author objects from the BOOK_AUTHOR table
     *
     * @param args
     */
    public void delete(Author author) {
        performDelete(author.getId());
    }

    public static void main(String[] args) {
        AuthorDAO authorDao = new AuthorDAO();
        authorDao.queryBookAuthor();
        authorDao.performCreate("Joe", "Blow", "N/A");

        // Find any author named Joe Blow and store in authList
        List<Author> authList = authorDao.performFind("Joe", "Blow");
        // Update the BIO for any author named Joe Blow
        for (Author auth : authList) {
            auth.setBio("New Bio");
            authorDao.update(auth);
        }
        authorDao.queryBookAuthor();

        // Delete any author named Joe Blow
        for (Author auth : authList) {
            authorDao.delete(auth);
        }
    }
}

How It Works

It can be advantageous for developers to separate different types of work into different classes within an application code base. In the same way that you separate web views from Java code within a Java web application, you should also separate JDBC from classes that are used to perform business logic. Have you ever had to maintain or debug a class that was riddled with business logic and SQL statements? It can be a nightmare! Simplifying code by breaking it down into smaller, more manageable pieces can oftentimes make maintenance and debugging much easier on a developer. The idea of separating JDBC and database-specific code from other business logic within an application falls within this same concept. Creating data access objects that are used solely for accessing the database can allow developers to code against Java objects rather than database tables.

A DAO is not a standard Java enterprise object. There is no framework that is used for creating DAOs. A DAO is simply a class that contains all of the JDBC code that is relevant for working with a single database table for your application. If there are twenty database tables that are required for use, then there should be that same number of DAOs. A DAO should contain minimally eight different methods. There should be at least one method for each of the four possible database transactions that could take place, those being CREATE, READ, UPDATE, and DELETE. These methods would contain specific JDBC code for connecting to the database, performing JDBC calls, and then closing the connection. The DAO should also contain four façade methods that will be used directly by classes containing the business logic. These methods should accept Java objects that correspond to the database table for which the DAO was written, and they should break down the object into separate fields and pass them to the JDBC methods to perform the actual database transaction.

In the solution to this recipe, the AuthorDAO class contains more than eight methods. This is because there is more than one way to search for author records within the database, and therefore, there is more than one find method within the class. A couple of different performFind methods are available, each with a different method signature. These methods allow one to find an author based upon ID or by name. Once a matching author record is found in the database, the values for that record are retrieved using standard JDBC methods, and they are stored into the corresponding fields within a new Author object. In the end, either a list of Author objects or a single Author object is returned to the caller. These finder methods contain public modifiers, so a managed bean can call them directly to retrieve a list of Author objects or a single Author object.

The performCreate, performUpdate, and performDelete methods are private, and therefore they can be accessed only by other methods within the same class. A managed bean should not work directly with these private methods, nor will it be allowed to do so. Instead, there are public methods named insert, update, and delete, which are to be used by the managed beans in order to access the private methods. The insert, update, and delete methods accept Author objects, and they perform the task of breaking down the Author object by field and passing the appropriate fields to their corresponding private methods in order to perform database activities. For instance, a bean can call the AuthorDAO insert method, passing an Author object. The insert method then calls the performCreate method, passing the fields of the Author object in their respective positions. Each of the CRUD operations can be performed in the same manner, allowing the business logic to interact directly with Author objects rather than deal with SQL.

Problem

You have written the JDBC to query a database and obtain a list of objects. Now you want to display that list of objects within one of the views of your web application.

Solution

Populate a JSF dataTable component with database record results by storing the database results into a List of objects and then setting the value of the dataTable to that List. In this example, the Acme Bookstore menu contains a listing of books that are currently for sale, and when a book is clicked, then the user can see more detail. The book titles that are displayed in the menu are read from the BOOK database table and then stored into Book objects. The resulting menu will display the books using a dataTable component that is populated from a List of Book objects. The corresponding List will be obtained via JDBC from the database using the BookDAO class.

The following view source is taken from the web/chapter07/layout/custom_template_search.xhtml file within the sources. It is the source for the Acme Bookstore application template, and the menu of book listings that appears on the left side of the application has been updated from previous recipes to utilize a dataTable component, rather than static links. Each book record within the BOOK database table is then traversed using the dataTable, and its title is displayed.

<?xml version='1.0' encoding='UTF-8' ?>
<!--
Book:  Java EE7 Recipes
Author: J. Juneau
-->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"
      xmlns:ui="http://xmlns.jcp.org/jsf/facelets"
      xmlns:h="http://xmlns.jcp.org/jsf/html"
      xmlns:f="http://xmlns.jcp.org/jsf/core"
      xmlns:a4j="http://richfaces.org/a4j"
      xmlns:s="http://xmlns.jcp.org/jsf/composite/components/util">

    <h:head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
        <h:outputStylesheet library="css" name="default.css"/>
        <h:outputStylesheet library="css" name="cssLayout.css"/>
        <h:outputStylesheet library="css" name="styles.css"/>

        <title>#{ch7AuthorController.storeName}</title>
    </h:head>

    <h:body>
            <!-- UNCOMMENT TO APPLY PHASE LISTENER TO ALL APPLICATION PAGES -->
            <!--f:phaseListener type="org.javaeerecipes.chapter06.BookstorePhaseListener" /-->
            <h:form>
                <a4j:poll id="poll" interval="1000" render="dayAndTime"/>
            </h:form>

        <div id="top">
            <h2>#{ch7AuthorController.storeName}</h2>
            <br/>
            <h:panelGrid width="100%" columns="2">
                <s:search_ch7 id="searchAuthor"/>
                <h:outputText id="dayAndTime" value="#{ch7BookstoreController.dayAndTime}"/>
            </h:panelGrid>
        </div>
        <div>
            <div id="left">
                <h:form id="navForm">
                    <h:dataTable id="books"
                                 value="#{ch7BookController.getCompleteBookList()}"
                                 var="book">
                        <h:column>
                            <h:commandLink value="#{book.title}" action="#{ch7BookController.populateBookList(book.id)}" />
                        </h:column>
                     
                    </h:dataTable>
                </h:form>
            </div>
            <div id="content" class="left_content">
                <ui:insert name="content">Content</ui:insert>
            </div>
        </div>
        <div id="bottom">
            Written by Josh Juneau, Apress Author
        </div>

    </h:body>

</html>

The dataTable component references a method named getCompleteBookList, located within the ch7BookController managed bean. This method returns a List of Book objects that has been obtained from the BookDAO class. The following excerpt is the code for the getCompleteBookList method:

List<Book> completeBookList;
...
public List<Book> getCompleteBookList() {
        System.out.println("Querying books");
        completeBookList = bookDao.queryBooks();
        System.out.println("Querying books");
        return completeBookList;
}

Following in suit with the concept of the DAO (see Recipe 7-8 for details), the managed bean method does not perform any JDBC; that is handled by the bookDao.queryBooks method. The following excerpt is taken from the org.javaeerecipes.chapter07.BookDAO class, and it is the code for the queryBooks method:

public List<Book> queryBooks() {
    String qry = "select id, title, image, description from book";

    List books = new ArrayList();
    CreateConnection.loadProperties();
    try (Connection conn = CreateConnection.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(qry);) {
        while (rs.next()) {
            int book_id = rs.getInt("ID");
            String title = rs.getString("TITLE");
            String image = rs.getString("IMAGE");
            String description = rs.getString("DESCRIPTION");
            Book book = new Book(book_id,
                                 title,
                                 image,
                                 description);
            books.add(book);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
        return books;
     

}

When the site is rendered, the left menu will display a list of book titles, retrieved from the BOOK database table. The application will resemble that of Figure 7-6.

How It Works

Perhaps the easiest way to display database table data to a web view is to load it into a JSF dataTable component. The dataTable component is a great way to display rows of data at a time, and custom dataTable components can even allow options such as sorting, column resizing, and inline editing. The key to displaying database records within a dataTable is to obtain the data first; store it into a Collection, List, or DataModel; and then expose it to the view.

In the solution to this recipe, standard JDBC is used to query the database and retrieve the rows of data. Each row of data is subsequently stored into an object so that the JSF managed beans can work with objects rather than invoke JDBC calls. The JSF managed bean named BookController contains a method named getCompleteBookList that is used to access the BookDAO and retrieve a List of Book objects. The List, in turn, is used to populate the dataTable within the JSF view. There is no magic being performed in this example, although it should be noted that the dataTable in this solution is displaying a List<Book> of data. This is not the only solution, and in fact it is sometimes more suitable to utilize a DataModel or Collection of data rather than a List.

Problem

You have queried the database and obtained some results. You want to store those results in an object that will allow you to traverse forward and backward through the results, updating values as needed.

Solution

Create a scrollable ResultSet object, and then you will have the ability to read the next, first, last, and previous records. Using a scrollable ResultSet allows the results of a query to be fetched in any direction so that the data can be retrieved as needed. The following method, taken from the org.javaeerecipes.chapter07.dao.ChapterDAO class, demonstrates the creation of a scrollable ResultSet object:

private void queryBookChapters() {
    String sql = "SELECT ID, CHAPTER_NUMBER, TITLE, DESCRIPTION "
            + "FROM CHAPTER";

    int id = 0;
    int chapterNumber = 0;
    String title;

    CreateConnection.loadProperties();
    try (Connection conn = CreateConnection.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = stmt.executeQuery();) {

  
    rs.first();
    id = rs.getInt("ID");
    chapterNumber = rs.getInt("CHAPTER_NUMBER");
    title = rs.getString("TITLE");
    System.out.println(id + " - " + chapterNumber + ": " + title);

        rs.next();
        id = rs.getInt("ID");
        chapterNumber = rs.getInt("CHAPTER_NUMBER");
        title = rs.getString("TITLE");
        System.out.println(id + " - " + chapterNumber + ": " + title);

        rs.last();
        id = rs.getInt("ID");
        chapterNumber = rs.getInt("CHAPTER_NUMBER");
        title = rs.getString("TITLE");
        System.out.println(id + " - " + chapterNumber + ": " + title);

        rs.previous();
        id = rs.getInt("ID");
        chapterNumber = rs.getInt("CHAPTER_NUMBER");
        title = rs.getString("TITLE");
        System.out.println(id + " - " + chapterNumber + ": " + title);

    } catch (SQLException ex) {
        ex.printStackTrace();
    }
}

Executing this method will result in the following output using the data that resides within the CHAPTER table (your results will vary depending upon the contents of the table in your database):

1 – 1: Getting Started with Java 7
2 – 2: Strings
18 – 18: JavaFX in the Enterprise
17 – 17: HTML5 APIs

How It Works

Ordinary ResultSet objects allow results to be fetched in a forward direction. That is, an application can process a default ResultSet object from the first record retrieved forward to the last. Sometimes an application requires more functionality when it comes to traversing a ResultSet. For instance, let’s say you want to write an application that allows for someone to display the first or last record that was retrieved or perhaps page forward or backward through results. You could not do this very easily using a standard ResultSet. However, by creating a scrollable ResultSet, you can easily move backward and forward through the results.

To create a scrollable ResultSet, you must first create an instance of a Statement or PreparedStatement that has the ability to create a scrollable ResultSet. That is, when creating the Statement, you must pass the ResultSet scroll type constant value to the Connection object’s createStatement method. Likewise, you must pass the scroll type constant value to the Connection object’s prepareStatement method when using a PreparedStatement. Three different scroll type constants can be used. Table 7-1 displays those three constants.

You must also pass a ResultSet concurrency constant to advise whether the ResultSet is intended to be updatable. The default is ResultSet.CONCUR_READ_ONLY, which means that the ResultSet is not updatable. The other concurrency type is ResultSet.CONCUR_UPDATABLE, which signifies an updatable ResultSet object.

In the solution to this recipe, a PreparedStatement object is used, and the code to create a PreparedStatement object that has the ability to generate a scrollable ResultSet looks like the following line:

pstmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);

Once the PreparedStatement has been created as such, a scrollable ResultSet is returned. You can traverse in several different directions using a scrollable ResultSet by calling the ResultSet methods indicating the direction you want to move or the placement that you want to be. The following line of code will retrieve the first record within the ResultSet:

ResultSet rs = pstmt.executeQuery();
rs.first();

The solution to this recipe demonstrates a few different scroll directions. Specifically, you can see that the ResultSet first, next, last, and previous methods are called in order to move to different positions within the ResultSet. For a complete reference to the ResultSet object, please see the online documentation at http://download.oracle.com/javase/7/docs/api/java/sql/ResultSet.html.

Scrollable ResultSet objects have a niche in application development. They are one of those niceties that are there when you need them, but they are also something you might not need very often.

Problem

Some logic that is required for your application is written as a stored procedure residing in the database. You require the ability to invoke the stored procedure from within your application.

Solution

The following block of code shows the PL/SQL that is required to create the stored procedure that will be called by Java. The functionality of this stored procedure is very minor; it simply accepts a value and assigns that value to an OUT parameter so that the program can display it.

create or replace procedure dummy_proc (text IN VARCHAR2,
msg OUT VARCHAR2) as
begin
-- Do something, in this case the IN parameter value is assigned to the OUT parameter
msg :=text;
end;

The CallableStatement in the following code executes this stored procedure that is contained within the database, passing the necessary parameters. The results of the OUT parameter are then displayed to the user.

CallableStatement cs = null;
try {
cs = conn.prepareCall("{call DUMMY_PROC(?,?)}");
cs.setString(1, "This is a test");
cs.registerOutParameter(2, Types.VARCHAR);
cs.executeQuery();
System.out.println(cs.getString(2));
} catch (SQLException ex){
ex.printStackTrace();
}

Running the example class for this recipe will display the following output, which is the same as the input. This is because the DUMMY_PROC procedure simply assigns the contents of the IN parameter to the OUT parameter.

This is a test

How It Works

It is not uncommon for an application to use database stored procedures for logic that can be executed directly within the database. To call a database stored procedure from Java, you must create a CallableStatement object, rather than using a PreparedStatement. In the solution to this recipe, a CallableStatement is used to invoke a stored procedure named DUMMY_PROC. The syntax for instantiating the CallableStatement is similar to that of using a PreparedStatement. Use the Connection object’s prepareCall method, passing the call to the stored procedure. The solution to this recipe demonstrates one technique for making a stored procedure call, that is, enclosing it in curly braces: {}.

cs = conn.prepareCall("{call DUMMY_PROC(?,?)}");

Once the CallableStatement has been instantiated, it can be used just like a PreparedStatement for setting the values of parameters. However, if a parameter is registered within the database stored procedure as an OUT parameter, you must call a special method, registerOutParameter, passing the parameter position and database type of the OUT parameter that you want to register. In the solution to this recipe, the OUT parameter is in the second position, and it has a VARCHAR type.

cs.registerOutParameter(2, Types.VARCHAR);

To execute the stored procedure, call the executeQuery method on the CallableStatement. Once this has been done, you can see the value of the OUT parameter by making a call to the CallableStatement getXXX method that corresponds to the data type:

System.out.println(cs.getString(2));

cs = conn.prepareCall("{? = call DUMMY_FUNC(?)}");
cs.registerOutParameter(1, Types.VARCHAR);
cs.setString(2, "This is a test");
cs.execute();

Problem

The application you are developing requires the storage of Strings of text that can include an unlimited number of characters.

Solution

Because the size of the Strings that need to be stored is unlimited, it is best to use a character large object (CLOB) data type to store the data. The code in the following example demonstrates how to load a CLOB into the database and how to query it. The following excerpts are two methods from the org.javaeerecipes.chapter7.dao.ChapterDAO class.

Let’s take a look at how to read a CLOB column value from the database. The readClob method queries the database, reading the CHAPTER_NUMBER, TITLE, and DESCRIPTION columns from the CHAPTER database table. The length of the DESCRIPTION, which is the CLOB column, is printed to the command line along with the chapter number, title, and description.

public void readClob() {
    String qry = "select chapter_number, title, description from chapter";
    Clob theClob = null;
    CreateConnection.loadProperties();
    try (Connection conn = CreateConnection.getConnection();
            PreparedStatement stmt = conn.prepareStatement(qry)) {

    try (ResultSet rs = stmt.executeQuery();) {
        while (rs.next()) {
            int chapterNumber = rs.getInt(1);
            String title = rs.getString(2);
            theClob = rs.getClob(3);
            System.out.println("Clob length: " + theClob.length());
            System.out.println(chapterNumber + " - " + title + ": ");
                java.io.InputStream in =
                        theClob.getAsciiStream();
                int i;
                while ((i = in.read()) > -1) {
                    System.out.print((char) i);
                }
                System.out.println();
            }
        }
    } catch (IOException ex) {
        ex.printStackTrace();
    } catch (SQLException ex) {
        ex.printStackTrace();
    }
}

The resulting output from running the method would look similar to the following, depending upon which records are stored in the database:

Clob length: 19
1 - Getting Started with Java 7:
chapter description
Clob length: 19
2 - Strings:
chapter description
Clob length: 19
3 - Numbers and Dates:
chapter description
Clob length: 19
4 - Data Structures, Conditionals, and Iteration:
chapter description
Clob length: 19
5 - Input and Output:
chapter description
Clob length: 19
6 - Exceptions, Logging, and Debugging:
chapter description
Clob length: 19
7 - Object-Oriented Java:
chapter description
Clob length: 19
8 - Concurrency:
chapter description
Clob length: 19
9 - Debugging and Unit Testing:
chapter description
Clob length: 19
10 - Unicode, Internationalization, and Currency Codes:
chapter description

What about inserting CLOB values into the database? The next method accepts values for each field within a record of the CHAPTER table, and it constructs the CLOB contents and lastly performs the insert.

private void performCreate(int chapterNumber, int bookId, String title, String description) {
    String sql = "INSERT INTO CHAPTER VALUES("
            + "CHAPTER_S.NEXTVAL, ?, ?, ?, ?)";

    Clob textClob = null;
    CreateConnection.loadProperties();
    try (Connection conn = CreateConnection.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql)) {

        textClob = conn.createClob();
        textClob.setString(1, description);

        stmt.setInt(1, chapterNumber);
        stmt.setString(2, title.toUpperCase());
        stmt.setClob(3, textClob);
        stmt.setInt(4, bookId);
        // Returns row-count or 0 if not successful
        int result = stmt.executeUpdate();
        if (result > 0) {
            System.out.println("-- Record created --");
        } else {
            System.out.println("!! Record NOT Created !!");
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

How It Works

If your application requires the storage of String values, you need to know how large those Strings might possibly become. Most databases have an upper boundary when it comes to the storage size of VARCHAR fields. For instance, Oracle Database has an upper boundary of 2,000 characters, and anything exceeding that length will be cut off. If you have large amounts of text that need to be stored, use a CLOB field in the database.

A CLOB is handled a bit differently from a String within Java code. In fact, it is actually a bit odd to work with the first couple of times you use it because you have to create a CLOB from a Connection.

Let’s take a look at the performCreate method that is contained in the solution to this recipe. As you can see, a Clob object is created using the Connection object’s createClob method. Once the Clob has been created, you set its contents using the setString method by passing the position, which indicates where to place the String, and the String of text itself:

textClob = conn.createClob();
textClob.setString(1, "This will be the recipe text in clob format");

Once you have created and populated the Clob, you simply pass it to the database using the PreparedStatement setClob method. In the case of this example, the PreparedStatement performs a database insert into the CHAPTER table by calling the executeUpdate method as usual. Querying a Clob is fairly straightforward as well. As you can see in the readClob method that is contained within the solution to this recipe, a PreparedStatement query is set up, and the results are retrieved into a ResultSet. The only difference between using a Clob and a String is that you must load the Clob into a Clob type. Calling the Clob object’s getString method will pass you a funny-looking String of text that denotes a Clob object. Therefore, calling the Clob object’s getAsciiStream method will return the actual data that is stored in the Clob. This technique is used in the solution to this recipe.

Although Clobs are fairly easy to use, they take a couple of extra steps to prepare. It is best to plan your applications accordingly and try to estimate whether the database fields you are using might need to be CLOBs because of size restrictions. Proper planning will prevent you from going back and changing standard String-based code to work with Clobs later.

Problem

You want to work with data from a DBMS when you are in a disconnected state. That is, you are working on a device that is not connected to the database, and you still want to have the ability to work with a set of data as though you are connected. For instance, you are working with data on a small portable device, and you are away from the office without a connection. You want the ability to query, insert, update, and delete data, even though there is no connection available. Once a connection becomes available, you want to have your device synchronize any database changes that have been made while disconnected.

Solution

Use a CachedRowSet object to store the data that you want to work with while offline. This will afford your application the ability to work with data as though it were connected to a database. Once your connection is restored or you connect to the database, synchronize the data that has been changed within the CachedRowSet with the database repository. The following example class demonstrates the usage of a CachedRowSet. In this scenario, the main method executes the example. Suppose there was no main method, though, and that another application on a portable device invoked the methods of this class. Follow the code in the example and consider the possibility of working with the results that are stored within the CachedRowSet while not connected to the database. For instance, suppose you began some work in the office while connected to the network and are now outside of the office, where the network is spotty and you cannot maintain a constant connection to the database.

package org.javaeerecipes.chapter07.recipe07_14;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;
import javax.sql.rowset.spi.SyncProviderException;
import org.javaeerecipes.chapter07.CreateConnection;

public class CachedRowSetExample {

  
    public static CachedRowSet crs = null;

    public static void main(String[] args) {
        boolean successFlag = false;
        CreateConnection.loadProperties();
        try(Connection conn = CreateConnection.getConnection();) {
               
    // Perform Scrollable Query
            queryWithRowSet(conn);
            updateData();
            syncWithDatabase(conn);
        } catch (java.sql.SQLException ex) {
            System.out.println(ex);
        }
    }

    /**
     * Call this method to synchronize the data that has been used in the
     * CachedRowSet with the database
     */
    public static void syncWithDatabase(Connection conn) {
        try {
            crs.acceptChanges(conn);
        } catch (SyncProviderException ex) {
        // If there is a conflict while synchronizing, this exception
        // will be thrown.
            ex.printStackTrace();
        } finally {
        // Clean up resources by closing CachedRowSet
            if (crs != null) {
                try {
                    crs.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
    }

    public static void queryWithRowSet(Connection conn) {
        RowSetFactory factory;
        try {
        // Create a new RowSetFactory
            factory = RowSetProvider.newFactory();
        // Create a CachedRowSet object using the factory
            crs = factory.createCachedRowSet();
        // Alternatively populate the CachedRowSet connection settings
        // crs.setUsername(createConn.getUsername());
        // crs.setPassword(createConn.getPassword());
        // crs.setUrl(createConn.getJdbcUrl());
        // Populate a query that will obtain the data that will be used
            crs.setCommand("select id, chapter_number, title, description, book_id from chapter");
        // Set key columns
            int[] keys = {1};
            crs.setKeyColumns(keys);
        // Execute query
            crs.execute(conn);
        // You can now work with the object contents in a disconnected state
            while (crs.next()) {
                System.out.println(crs.getString(2) + ": " + crs.getString(3)
                        + " - " + crs.getString(4));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static boolean updateData() {
        boolean returnValue = false;
        try {

        // Move to the position before the first row in the result set
            crs.beforeFirst();
        // traverse result set
            while (crs.next()) {
        // If the chapter_number equals 1 then update
                if (crs.getInt("CHAPTER_NUMBER") == 1) {
                    System.out.println("updating Chapter 1");
                    crs.updateString("TITLE", "Subject to change");
                    crs.updateRow();
                }
            }
            returnValue = true;
        // Move to the position before the first row in the result set
            crs.beforeFirst();
        // traverse result set to see changes
            while (crs.next()) {
                System.out.println(crs.getString(2) + ": " + crs.getString(3));
            }
        } catch (SQLException ex) {
            returnValue = false;
            ex.printStackTrace();
        }
        return returnValue;
    }
}

Running this example code will display output that looks similar to the following code, although the text might vary depending upon the values in the database. Notice that the database record for Chapter 1 has a changed description after the update of the CachedRowSet.

Successfully connected
1:  Getting Started with Java 7 - javax.sql.rowset.serial.SerialClob@5e7afcba
2:  Strings - javax.sql.rowset.serial.SerialClob@5c6647cb
3:  Numbers and Dates - javax.sql.rowset.serial.SerialClob@3ef38fd1
4:  Data Structures, Conditionals, and Iteration - javax.sql.rowset.serial.SerialClob@686702a0
5:  Input and Output - javax.sql.rowset.serial.SerialClob@42dd8bec
6:  Exceptions, Logging, and Debugging - javax.sql.rowset.serial.SerialClob@5f0d553f
7:  Object-Oriented Java - javax.sql.rowset.serial.SerialClob@6457cbd9
8:  Concurrency - javax.sql.rowset.serial.SerialClob@40084706
9:  Debugging and Unit Testing - javax.sql.rowset.serial.SerialClob@5f6efbc1
10: Unicode, Internationalization, and Currency Codes - javax.sql.rowset.serial.SerialClob@6f526cd9
updating Chapter 1
1:  Subject to change
2:  Strings
3:  Numbers and Dates
4:  Data Structures, Conditionals, and Iteration
5:  Input and Output
6:  Exceptions, Logging, and Debugging
7:  Object-Oriented Java
8:  Concurrency
9:  Debugging and Unit Testing
10: Unicode, Internationalization, and Currency Codes11-3: Handling SQL Exceptions - Using SQLException

How It Works

It is not possible to remain connected to the Internet 100 percent of the time if you are working on a mobile device and traveling. Nowadays there are devices that allow you to perform substantial work while you are on the go, even when you are not connected directly to a database. In such cases, solutions like the CachedRowSet object can come into play. The CachedRowSet is the same as a regular ResultSet object, except it does not have to maintain a connection to a database in order to remain usable. You can query the database, obtain the results, and place them into a CachedRowSet object; then you work with them while not connected to the database. If changes are made to the data at any point, those changes can be synchronized with the database at a later time.

There are a couple of ways to create a CachedRowSet. The solution to this recipe uses a RowSetFactory to instantiate a CachedRowSet because this is new to Java SE 7. However, you can also use the CachedRowSet default constructor to create a new instance. Doing so would look like the following line of code:

CachedRowSet crs = new CachedRowSetImpl();

Once instantiated, you need to set up a connection to the database. There are also a couple of ways to do this. Properties could be set for the connection that will be used, and the solution to this recipe demonstrates this technique within comments. The following excerpt from the solution sets the connection properties using the CachedRowSet object’s setUsername, setPassword, and setUrl methods. Each of them accepts a String value, and in the example, that String is obtained from the CreateConnection class:

// Alternatively populate the CachedRowSet connection settings
// crs.setUsername(createConn.getUsername());
// crs.setPassword(createConn.getPassword());
// crs.setUrl(createConn.getJdbcUrl());

Another way to set up the connection is to wait until the query is executed and pass a Connection object to the executeQuery method. This is the technique that is used in the solution to this recipe. But before we can execute the query, it must be set using the setCommand method, which accepts a String value. In this case, the String is the SQL query you need to execute:

crs.setCommand("select id, chapter_number, title, description, book_id from chapter");

Next, if a CachedRowSet will be used for updates, the primary key values should be noted using the setKeys method. This method accepts an int array that includes the positional indices of the key columns. These keys are used to identify unique columns. In this case, the first column listed in the query, ID, is the primary key.

int[] keys = {1};
crs.setKeyColumns(keys);

Finally, execute the query and populate the CachedRowSet using the execute method. As mentioned previously, the execute method optionally accepts a Connection object, which allows the CachedRowSet to obtain a database connection.

crs.execute(conn);

Once the query has been executed and the CachedRowSet has been populated, it can be used just like any other ResultSet. You can use it to fetch records forward and backward or by specifying the absolute position of the row you’d like to retrieve. The solution to this recipe demonstrates only a couple of these fetching methods, but the most often used ones are listed in Table 7-2.

It is possible to insert and update rows within a CachedRowSet. To insert rows, use the moveToInsertRow method to move to a new row position. Then populate a row by using the various CachedRowSet methods (updateString, updateInt, and so on) that correspond to the data type of the column you are populating within the row. Once you have populated each of the required columns within the row, call the insertRow method, followed by the moveToCurrentRow method. The following lines of code demonstrate inserting a record for Chapter 11 into the CHAPTER table:

crs.moveToInsertRow();
crs.updateInt(1, sequenceValue); // obtain current sequence values with a prior query
crs.updateInt(2, 11);
crs.updateString(3, "Chapter 11 Title");
crs.updateSTring(4, "Description");
crs.updateInt(5, bookId);
crs.insertRow();
crs.moveToCurrentRow();

Updating rows is similar to using an updatable ResultSet. Simply update the values using the CachedRowSet object’s methods (updateString, updateInt, and so on) that correspond to the data type of the column that you are updating within the row. Once you have updated the column or columns within the row, call the updateRow method. This technique is demonstrated in the solution to this recipe.

crs.updateString("TITLE", "Subject to change");
crs.updateRow();

To make any updates or inserts propagate to the database, the acceptChanges method must be called. This method can accept an optional Connection argument in order to connect to the database. Once called, all changes are flushed to the database. Unfortunately, because time might have elapsed since the data was last retrieved for the CachedRowSet, there could be conflicts. If such a conflict arises, a SyncProviderException will be thrown. You can catch these exceptions and handle the conflicts manually using a SyncResolver object. However, resolving conflicts is out of the scope of this recipe, so for more information, please see the online documentation at

http://download.oracle.com/javase/tutorial/jdbc/basics/cachedrowset.html.

CachedRowSet objects provide great flexibility for working with data, especially when you are using a device that is not always connected to the database. However, they can also be overkill in situations where you can simply use a standard ResultSet or even a scrollable ResultSet.

Problem

You want to join two or more RowSets while not connected to a database. Perhaps your application is loaded on a mobile device that is not connected to the database 100 percent of the time. In such a case, you are looking for a solution that will allow you to join the results of two or more queries.

Solution

Use a JoinRowSet to take data from two relational database tables and join them. The data from each table that will be joined should be fetched into a RowSet, and then the JoinRowSet can be used to join each of those RowSet objects based upon related elements that are contained within them. For instance, suppose that there were two related tables contained within a database. One of the tables stores a list of authors, and the other table contains a list of chapters that are written by those authors. The two tables can be joined using SQL by the primary and foreign key relationship.

However, the application will not be connected to the database to make the JOIN query, so it must be done using a JoinRowSet. The following class demonstrates one strategy that can be used in this scenario:

package org.javaeerecipes.chapter07.recipe07_15;

import com.sun.rowset.JoinRowSetImpl;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.JoinRowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;
import org.javaeerecipes.chapter07.CreateConnection;

public class JoinRowSetExample {

    public static CreateConnection createConn;
    public static CachedRowSet bookAuthors = null;
    public static CachedRowSet authorWork = null;
    public static JoinRowSet jrs = null;

    public static void main(String[] args) {
        boolean successFlag = false;
        CreateConnection.loadProperties();
        try(Connection conn = CreateConnection.getConnection();) {

        // Perform Scrollable Query
            queryBookAuthor(conn);
            queryAuthorWork(conn);
            joinRowQuery();
        } catch (java.sql.SQLException ex) {
            System.out.println(ex);
        } finally {
             
            if (bookAuthors != null) {
                try {
                    bookAuthors.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (authorWork != null) {
                try {
                    authorWork.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (jrs != null) {

                try {
                    jrs.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
    }

    public static void queryBookAuthor(Connection conn) {
        RowSetFactory factory;
        try {
        // Create a new RowSetFactory
            factory = RowSetProvider.newFactory();
        // Create a CachedRowSet object using the factory
            bookAuthors = factory.createCachedRowSet();
        // Alternatively opulate the CachedRowSet connection settings
        // crs.setUsername(createConn.getUsername());
        // crs.setPassword(createConn.getPassword());
        // crs.setUrl(createConn.getJdbcUrl());
        // Populate a query that will obtain the data that will be used
            bookAuthors.setCommand("SELECT ID, LAST, FIRST FROM BOOK_AUTHOR");
            bookAuthors.execute(conn);
        // You can now work with the object contents in a disconnected state
            while (bookAuthors.next()) {
                System.out.println(bookAuthors.getString(1) + ": " + bookAuthors.getString(2)
                        + ", " + bookAuthors.getString(3));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static void queryAuthorWork(Connection conn) {
        RowSetFactory factory;
        try {

        // Create a new RowSetFactory
            factory = RowSetProvider.newFactory();
        // Create a CachedRowSet object using the factory
            authorWork = factory.createCachedRowSet();
        // Alternatively opulate the CachedRowSet connection settings
        // crs.setUsername(createConn.getUsername());
        // crs.setPassword(createConn.getPassword());
        // crs.setUrl(createConn.getJdbcUrl());
        // Populate a query that will obtain the data that will be used
            authorWork.setCommand(
            "SELECT ID, AUTHOR_ID, BOOK_ID FROM AUTHOR_WORK");
            authorWork.execute(conn);
        // You can now work with the object contents in a disconnected state
            while (authorWork.next()) {
                System.out.println(authorWork.getString(1) + ": " + authorWork.getInt(2)
                        + " - " + authorWork.getInt(3));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static void joinRowQuery() {
        try {
        // Create JoinRowSet
            jrs = new JoinRowSetImpl();
        // Add RowSet & Corresponding Keys
            jrs.addRowSet(bookAuthors, 1);
            jrs.addRowSet(authorWork, 2);
        // Traverse Results
            while (jrs.next()) {
                System.out.println(jrs.getString("BOOK_ID") + " - "
                        + jrs.getString("FIRST") + " "
                        + jrs.getString("LAST"));
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}

Running this class will result in output that resembles the following:

Successfully connected
21: JUNEAU, JOSH
22: DEA, CARL
23: BEATY, MARK
24: GUIME, FREDDY
25: JOHN, OCONNER
21: 21 - Java 7 Recipes
22: 23 - Java 7 Recipes
23: 22 - Java 7 Recipes
24: 24 - Java 7 Recipes
25: 21 - Java EE 7 Recipes
26: 22 - Java FX 2.0 - Introduction by Example
Java 7 Recipes - FREDDY GUIME
Java 7 Recipes - MARK BEATY
Java FX 2.0 - Introduction by Example - CARL DEA
Java 7 Recipes - CARL DEA
Java EE 7 Recipes - JOSH JUNEAU
Java 7 Recipes - JOSH JUNEAU

How It Works

A JoinRowSet is a combination of two or more populated RowSet objects. It can be used to join two RowSet objects based upon key-value relationships, just as if it were a SQL JOIN query. To create a JoinRowSet, you must first populate two or more RowSet objects with related data, and then they can each be added to the JoinRowSet to create the combined result.

In the solution to this recipe, the two tables that are queried are named BOOK_AUTHOR and AUTHOR_WORK. The BOOK_AUTHOR table contains a list of author names, while the AUTHOR_WORK table contains the list of chapters in a book along with the AUTHOR_ID for the author who wrote the chapter. Following along with the main method, first the BOOK_AUTHOR table is queried, and its results are fetched into a CachedRowSet using the queryBookAuthor method. For more details regarding the use of CachedRowSet objects, please see Recipe 7-13.

Next, another CachedRowSet is populated with the results of querying the AUTHOR_WORK table, when the queryAuthorWork method is called. At this point, there are two populated CacheRowSet objects, and they can now be combined using a JoinRowSet. To do so, each table must contain one or more columns that relate to the other table. In this case, the BOOK_AUTHOR.ID column relates to the AUTHOR_WORK.AUTHOR_ID column, so the RowSet objects must be joined on those column results.

The final method that is invoked within the main method is joinRowQuery. This method is where all the JoinRowSet work takes place. Note that the connection to the database can be null at this time. A new JoinRowSet is created by instantiating a JoinRowSetImpl object.

jrs = new JoinRowSetImpl();

Next, the two CachedRowSet objects are added to the newly created JoinRowSet by calling its addRowSet method. The addRowSet method accepts a couple of arguments. The first is the name of the RowSet object that you want to add to the JoinRowSet, and the second is an int value indicating the position within the CachedRowSet, which contains the key value that will be used to implement the join. In the solution to this recipe, the first call to addRowSet passes the bookAuthorsCachedRowSet, along with the number 1 because the element in the first position of the bookAuthorsCachedRowSet corresponds to the BOOK_AUTHOR.ID column. The second call to addRowSet passes the authorWorkCachedRowSet, along with the number 2 because the element in the second position of the authorWork CachedRowSet corresponds to the AUTHOR_WORK.AUTHOR_ID column.

// Add RowSet & Corresponding Keys
jrs.addRowSet(bookAuthors, 1);
jrs.addRowSet(authorWork, 2);

The JoinRowSet can now be used to fetch the results of the join, just as if it were a normal RowSet. When calling the corresponding methods (getString, getInt, and so on) of the JoinRowSet, pass the name of the database column corresponding to the data you want to store.

while(jrs.next()){
System.out.println(jrs.getInt("CHAPTER_NUMBER") + ": " +
jrs.getString("CHAPTER_TITLE") + " - " +
jrs.getString("FIRST") + " " +
jrs.getString("LAST"));
}

Although a JoinRowSet is not needed every day, it can be handy when performing work against two related sets of data. This especially holds true if the application is not connected to a database all the time or if you are trying to use as few Connection objects as possible.