In this section, we write a program that executes queries against the COREJAVA database. For this program to work, you must have a COREJAVA database populated with tables as described in the preceding section.
When querying the database, you can select the author and the publisher or leave either of them as Any.
You can also change the data in the database. Select a publisher and type an amount. All prices of that publisher are adjusted by the amount you entered, and the program displays how many rows were changed. After a price change, you might want to run a query to verify the new prices.
1. Prepared Statements
In this program, we use one new feature, prepared statements. Consider the SQL query for all books by a particular publisher, regardless of the author:
SELECT Books.Price, Books.Titte
FROM Books, Publishers
WHERE Books.Publisher_Id = Publishers.Publisher_Id
AND Publishers.Name = the name from the list box
Instead of building a separate query statement every time the user launches such a query, we can prepare a query with a host variable and use it many times, each time filling in a different string for the variable. That technique improves performance. Whenever the database executes a query, it first computes a strategy of how to do it efficiently. By preparing the query and reusing it, you ensure that the planning step is done only once.
Each host variable in a prepared query is indicated with a ?. If there is more than one variable, you must keep track of the positions of the ? when setting the values. For example, our prepared query becomes
String publisherQuery
= “SELECT Books.Price, Books.Title”
+ ” FROM Books, Publishers”
+ ” WHERE Books.Publisher_Id = Publishers.Publisher_Id AND Publishers.Name = ?”;
PreparedStatement stat = conn.prepareStatement(publisherQuery);
Before executing the prepared statement, you must bind the host variables to actual values with a set method. As with the get methods of the ResultSet interface, there are different set methods for the various types. Here, we want to set a string to a publisher name:
stat.setString(1, publisher);
The first argument is the position number of the host variable that we want to set. The position 1 denotes the first ?. The second argument is the value that we want to assign to the host variable.
If you reuse a prepared query that you have already executed, all host variables stay bound unless you change them with a set method or call the clearParameters method. That means you only need to call a setXxx method on those host variables that change from one query to the next.
Once all variables have been bound to values, you can execute the prepared statement:
ResultSet rs = stat.executeQuery();
The price update feature is implemented as an UPDATE statement. Note that we call executeUpdate, not executeQuery, because the UPDATE statement does not return a result set. The return value of executeUpdate is the count of changed rows.
int r = stat.executeUpdate();
System.out.println(r + ” rows updated”);
The following list briefly describes the structure of the example program:
- The author and publisher array lists are populated by running two queries that return all author and publisher names in the database.
- The queries involving authors are complex. A book can have multiple authors, so the BooksAuthors table stores the correspondence between authors and books. For example, the book with ISBN 0-201-96426-0 has two authors with codes DATE and DARW. The BooksAuthors table has the rows
0-201-96426-0, DATE, 1
0-201-96426-0, DARW, 2
to indicate this fact. The third column lists the order of the authors. (We can’t just use the position of the rows in the table. There is no fixed row ordering in a relational table.) Thus, the query has to join the Books, BooksAuthors, and Authors tables to compare the author name with the one selected by the user.
SELECT Books.Price, Books.Titte FROM Books, BooksAuthors, Authors, Publishers
WHERE Authors.Author_Id = BooksAuthors.Author_Id AND BooksAuthors.ISBN = Books.ISBN
AND Books.Publisher_Id = Publishers.Publisher_Id AND Authors.Name = ?
AND Publishers.Name = ?
- The changePrices method executes an UPDATE statement. Note that the WHERE clause of the UPDATE statement needs the publisher code and we only know the publisher name. This problem is solved with a nested subquery:
UPDATE Books
SET Price = Price + ?
WHERE Books.Publisher_Id = (SELECT Publisher_Id FROM Publishers WHERE Name = ?)
Listing 5.3 is the complete program code.
2. Reading and Writing LOBs
In addition to numbers, strings, and dates, many databases can store large objects (LOBs) such as images or other data. In SQL, binary large objects are called BLOBs, and character large objects are called CLOBs.
To read a LOB, execute a SELECT statement and call the getBlob or getClob method on the ResultSet. You will get an object of type Blob or Clob. To get the binary data from a Blob, call the getBytes or getBinaryStream. For example, if you have a table with book cover images, you can retrieve an image like this:
PreparedStatement stat = conn.prepareStatement(“SELECT Cover FROM BookCovers WHERE ISBN=?”);
…
stat.set(1, isbn);
try (ResultSet result = stat.executeQuery())
{
if (result.next())
{
Blob coverBlob = result.getBlob(1);
Image coverImage = ImageIO.read(coverBlob.getBinaryStream());
}
}
Similarly, if you retrieve a Clob object, you can get character data by calling the getSubString or getCharacterStream method.
To place a LOB into a database, call createBlob or createClob on your Connection object, get an output stream or writer to the LOB, write the data, and store the object in the database. For example, here is how you store an image:
Blob coverBlob = connection.createBlob();
int offset = 0;
OutputStream out = coverBlob.setBinaryStream(offset);
ImageIO.write(coverImage, “PNG”, out);
PreparedStatement stat = conn.prepareStatement(“INSERT INTO Cover VALUES (?, ?)”);
stat.set(1, isbn);
stat.set(2, coverBlob);
stat.executeUpdate();
3. SQL Escapes
The “escape” syntax features are commonly supported by databases but use database-specific syntax variations. It is the job of the JDBC driver to translate the escape syntax to the syntax of a particular database.
Escapes are provided for the following features:
- Date and time literals
- Calling scalar functions
- Calling stored procedures
- Outer joins
- The escape character in LIKE clauses
Date and time literals vary widely among databases. To embed a date or time literal, specify the value in the ISO 8601 format (www.cl.cam.ac.uk/~mgk25 /iso-time.htmt). The driver will then translate it into the native format. Use d, t, ts for DATE, TIME, or TIMESTAMP values:
{d ‘2008-01-24’}
{t ’23:59:59′}
{ts ‘2008-01-24 23:59:59.999’}
A scalar function is a function that returns a single value. Many functions are widely available in databases, but with varying names. The JDBC specification provides standard names and translates them into the database-specific names. To call a function, embed the standard function name and arguments like this:
{fn left(?, 20)}
{fn user()}
You can find a complete list of supported function names in the JDBC specification.
A stored procedure is a procedure that executes in the database, written in a database-specific language. To call a stored procedure, use the call escape. You need not supply parentheses if the procedure has no parameters. Use = to capture a return value:
{call PROC1(?, ?)}
{call PROC2}
{call ? = PROC3(?)}
An outer join of two tables does not require that the rows of each table match according to the join condition. For example, the query
SELECT * FROM {oj Books LEFT OUTER JOIN Publishers
ON Books.Publisher_Id = Publisher.Publisher_Id}
contains books for which Publisher_Id has no match in the Publishers table, with NULL values to indicate that no match exists. You would need a RIGHT OUTER JOIN to include publishers without matching books, or a FULL OUTER JOIN to return both. The escape syntax is needed because not all databases use a standard notation for these joins.
Finally, the _ and % characters have special meanings in a LIKE clause—to match a single character or a sequence of characters. There is no standard way to use them literally. If you want to match all strings containing a _, use this construct:
. . . WHERE ? LIKE %!_% {escape ‘I’}
Here we define I as the escape character. The combination !_ denotes a literal underscore.
4. Multiple Results
It is possible for a query to return multiple results. This can happen when executing a stored procedure, or with databases that also allow submission of multiple SELECT statements in a single query. Here is how you retrieve all result sets:
- Use the execute method to execute the SQL statement.
- Retrieve the first result or update count.
- Repeatedly call the getMoreResutts method to move on to the next result set.
- Finish when there are no more result sets or update counts.
The execute and getMoreResutts methods return true if the next item in the chain is a result set. The getUpdateCount method returns -1 if the next item in the chain is not an update count.
The following loop traverses all results:
boolean isResutt = stat.execute(command);
boolean done = false;
white (Idone)
{
if (isResutt)
{
ResuttSet result = stat.getResuttSet();
do something with resutt
}
etse
{
int updateCount = stat.getUpdateCount();
if (updateCount >= 0)
do something with updateCount
else
done = true;
}
if (Idone) isResutt = stat.getMoreResutts();
}
5. Retrieving Autogenerated Keys
Most databases support some mechanism for autonumbering rows in a database. Unfortunately, the mechanisms differ widely among vendors. These automatic numbers are often used as primary keys. Although JDBC doesn’t offer a vendor-independent solution for generating keys, it does provide an efficient way of retrieving them. When you insert a new row into a table and a key is automatically generated, you can retrieve it with the following code:
stat.executeUpdate(insertStatement, Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stat.getGeneratedKeys();
if (rs.next())
{
int key = rs.getInt(1);
}
Source: Horstmann Cay S. (2019), Core Java. Volume II – Advanced Features, Pearson; 11th edition.