Working with JDBC Statements

In the following sections, you will see how to use the JDBC Statement to execute SQL statements, obtain results, and deal with errors. Then we show you a simple program for populating a database.

1. Executing SQL Statements

To execute a SQL statement, you first create a Statement object. To create statement objects, use the Connection object that you obtained from the call to DriverManager,getConnection.

Statement stat = conn,createStatement();

Next, place the statement that you want to execute into a string, for example:

String command = “UPDATE Books”

+ ” SET Price = Price – 5,00″

+ ” WHERE Title NOT LIKE ‘%Introduction%'”;

Then, call the executeUpdate method of the Statement interface:

stat.executeUpdate(command);

The executeUpdate method returns a count of the rows that were affected by the SQL statement, or zero for statements that do not return a row count. For example, the call to executeUpdate in the preceding example returns the number of rows where the price was lowered by $5.00.

The executeUpdate method can execute actions such as INSERT, UPDATE, and DELETE, as well as data definition statements such as CREATE TABLE and DROP TABLE. However, you need to use the executeQuery method to execute SELECT queries. There is also a catch-all execute statement to execute arbitrary SQL statements. It’s commonly used only for queries that a user supplies interactively.

When you execute a query, you are interested in the result. The executeQuery object returns an object of type ResultSet that you can use to walk through the result one row at a time.

ResuttSet rs = stat.executeQuery(“SELECT * FROM Books”);

The basic loop for analyzing a result set looks like this:

white (rs.next())

{

look at a row of the result set

}

The order of the rows in a result set is completely arbitrary. Unless you specifically ordered the result with an ORDER BY clause, you should not attach any significance to the row order.

When inspecting an individual row, you will want to know the contents of the fields. A large number of accessor methods give you this information.

String isbn = rs.getString(l);

double price = rs.getDoubte(“Price”);

There are accessors for various types, such as getString and getDoubte. Each acces­sor has two forms, one taking a numeric argument, and the other, a string argument. When you supply a numeric argument, you refer to the column with that number. For example, rs.getString(l) returns the value of the first column in the current row.

When you supply a string argument, you refer to the column in the result set with that name. For example, rs.getDouble(“Price”) returns the value of the column with label Price. Using the numeric argument is a bit more efficient, but string arguments make the code easier to read and maintain.

Each get method makes reasonable type conversions when the type of the method doesn’t match the type of the column. For example, the call rs.getString(“Price”) converts the floating-point value of the Price column to a string.

2. Managing Connections, Statements, and Result Sets

Every Connection object can create one or more Statement objects. You can use the same Statement object for multiple unrelated commands and queries. However, a statement has at most one open result set. If you issue multiple queries whose results you analyze concurrently, you need multiple Statement objects.

Be forewarned, though, that there is a limit to the number of statements per connection. Use the getMaxStatements method of the DatabaseMetaData interface to find out the number of concurrently open statements that your JDBC driver supports.

In practice, you should probably not fuss with multiple concurrent result sets. If the result sets are related, you should be able to issue a combined query and analyze a single result. It is much more efficient to let the database combine queries than it is for a Java program to iterate through multiple result sets.

Be sure to finish processing of any result set before you issue a new query or update on a Statement object. The result sets of prior queries are automatically closed.

When you are done using a ResultSet, Statement, or Connection, it is a good idea to call the close method immediately. These objects use large data structures that draw on the finite resources of the database server.

The close method of a Statement object closes any associated result sets. Similarly, the close method of the Connection class closes all statements of the connection.

Conversely, you can call the closeOnCompletion method on a Statement, and it will close automatically as soon as all its result sets have closed.

If your connections are short-lived, don’t worry about closing statements and result sets. To make sure a connection object cannot possibly remain open, use a try-with-resources statement:

try (Connection conn = . . .)

{

Statement stat = conn.createStatement();

ResuttSet result = stat.executeQuery(queryString);

process query result

}

3. Analyzing SQL Exceptions

Each SQLException has a chain of SQLException objects that are retrieved with the getNextException method. This exception chain is in addition to the “cause” chain of Throwable objects that every exception has. (See Volume I, Chapter 7 for de­tails about Java exceptions.) One would need two nested loops to fully enu­merate all these exceptions. Fortunately, the SQLException class has been enhanced to implement the Iterable<Throwable> interface. The iterator() method yields an Iterator<Throwable> that iterates through both chains: It starts by going through the cause chain of the first SQLException, then moves on to the next SQLException, and so on. You can simply use an enhanced for loop:

for (Throwable t : sqlException)

{

do something with t

}

You can call getSQLState and getErrorCode on a SQLException to analyze it further. The first method yields a string that is standardized by either X/Open or SQL:2003. (Call the getSQLStateType method of the DatabaseMetaData interface to find out which standard is used by your driver.) The error code is vendor-specific.

The SQL exceptions are organized into an inheritance tree (shown in Figure 5.5). This allows you to catch specific error types in a vendor- independent way.

In addition, the database driver can report nonfatal conditions as warnings. You can retrieve warnings from connections, statements, and result sets. The SQLWarning class is a subclass of SQLException (even though a SQLWarning is not thrown as an exception). Call getSQLState and getErrorCode to get further information about the warnings. Similar to SQL exceptions, warnings are chained. To retrieve all warnings, use this loop:

SQLWarning w = stat.getWarning();
while (w != null)
{

do something with w
w = w.nextWarning();

}

The DataTruncation subclass of SQLWarning is used when data being read from the database are unexpectedly truncated. If data truncation happens in an update statement, a DataTruncation is thrown as an exception

4. Populating a Database

We are now ready to write our first real JDBC program. Sure, it would be nice to try some of the fancy queries discussed earlier, but we have a problem: Right now, there are no data in the database. We need to populate the database, and a simple way of doing that is with a set of SQL instructions to create tables and insert data into them. Most database programs can process a set of SQL instructions from a text file, but there are pesky differences about statement terminators and other syntactical issues.

For that reason, we will use JDBC in a simple program that reads a file with SQL instructions, one instruction per line, and executes them.

Specifically, the program reads data from a text file in a format such as

CREATE TABLE Publishers (Publisher_Id CHAR(6), Name CHAR(30), URL CHAR(80));

INSERT INTO Publishers VALUES (‘0201’, ‘Addison-Wesley’, ‘www.aw-bc.com’);

INSERT INTO Publishers VALUES (‘0471’, ‘John Wiley & Sons’, ‘www.wiley.com’);

Listing 5.2 contains the code for the program that reads a file with SQL statements and executes them. You don’t have to read through the code; simply use the program so you can populate your database and run the examples in the remainder of this chapter.

Make sure that your database server is running, and run the program as follows:

java .classpath driverPath:. exec.ExecSQL Books.sql

java .classpath driverPath:. exec.ExecSQL Authors.sql

java .classpath driverPath:. exec.ExecSQL Publishers.sql

java .classpath driverPath:. exec.ExecSQL BooksAuthors.sql

Before running the program, check that the file database.properties is set up properly for your environment (see Section 5.3.5, “Connecting to the Database,” on p. 296).

Source: Horstmann Cay S. (2019), Core Java. Volume II – Advanced Features, Pearson; 11th edition.

Leave a Reply

Your email address will not be published. Required fields are marked *