In some of the examples I show below I will be making use of a sample database. This database is used to record sales for a store, and stores information about the customers, the items for sale, and purchases the customers have made.
The customers table contains basic information about the customers.
|
The products table contains information about the products for sale.
 |
The invoices table has an entry for each purchase invoice written.
 |
Individual items purchased on a given invoice are listed as line items in a line_items table.
 |
To provide summary information about sales, we also construct a view named purchases. This view is constructed by the command
CREATE VIEW purchases AS
select customer, line_items.invoice, product, quantity
from invoices join line_items
on invoices.idinvoices = line_items.invoice
 |
Sorting query results
Query results can be sorted by adding an 'order by' clause to a select statement. For example, to sort the entries in the purchases view by invoice number we issue the query
select * from purchases order by invoice
 |
We can sort by multiple criteria. For example, to sort by invoice number first and then by quantity sold we issue the query
select * from purchases order by invoice, quantity
 |
We can also ask to sort by a certain column in descending order instead of the default ascending order by attaching the keyword 'desc' after the column name.
select * from purchases order by invoice, quantity desc
 |
A common situation that occurs in many tables is that we will have multiple rows sharing the same field value. For example, in our example database the line_items table will have many rows with the same invoice number.
Sometimes it is useful to construct summary information that combines all the rows with a common field value. The first step needed to do this is to group rows by adding a 'group by' clause to a select statement.
select * from purchases group by invoice
 |
This by itself is not very useful, because what we end up getting is one representative from each group (usually just the first row in that group). The real purpose of grouping is to provide summary information. Summary information is most often generated by applying the SUM() or COUNT() functions to particular columns.
Here is an example of a more useful summary query:
select customer, invoice, count(product), sum(quantity) from purchases group by invoice
 |
This query shows us a summary of all the invoices, with a count of the number of distinct products sold in that invoice and the total number of individual items for that invoice. You can verify that this information is correct by examing the original data sorted by invoice:
 |
On occasion JDBC applications will need to execute a series of updates in response to a single event. An example of how this might happen occurs in the Programming Competition assignment. In that assignment whenever we create a new team we have to put nine new rows in the problems table. That requires nine separate insert statements. Normally, we would use a prepared statement and a loop that issues that prepared insert statement nine times.
As an alternative, you can use the Statement class's addBatch and executeBatch methods. The addBatch method takes as its parameter a String containing some SQL code you would like to send to the database. Instead of executing that code right away, addBatch puts that command in a cache. Subsequently calling the executeBatch method sends all of the cached SQL statements to the database at once, which is more efficient than sending the statements separately one by one. This optimization is most useful in scenarios in which we need to do either mass inserts or mass updates.
The code below demonstrates how the addBatch and executeBatch methods are typically used.
String problemSQL = "insert into problems(team,problem_number) values("+teamID+",";
for(int problem = 1;problem <= 9;problem++) {
statement.addBatch(problemSQL+problem+")");
}
statement.executeBatch();
A constraint we have had to contend with in our use of ResultSet objects is that we can only interate through those result sets in the forward direction. That constraint was present in version 1 of JDBC, but starting in version 2 it became possible to create scrollable result sets.
Here is some code that demonstrates how to tell JDBC that you want a statment object that you can move both forwards and backwards in:
statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
Any result sets generated from that statement object will be scrollable, read-only result sets. If your result set is scrollable, you can continue moving forward through it by calling next(), but now you can also move backwards by calling previous(). To avoid running past the ends of the result set you should also call the methods isAfterLast() and isBeforeFirst() as needed.
Here is a project that demonstrates the use of a scrollable cursor. This project shows some simple information taken from a database of contacts.
 |
Users can scoll back and forth through the list of contacts by clicking the navigation buttons.
Another even nicer capability that result sets now have is the ability to both read and write through result sets. To create a result set that allows you to both read and write values, you need to construct a statement this way:
statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
Any result sets generated from that statement will then be both scrollable and updatable.
To update a value in a row that the ResultSet currently points to, use an updateXXX() method to make changes to specific fields and then use the updateRow() method to commit the updates to the database. For example,
results.updateString("name",nameField.getText());
results.updateRow();
Here is a project that demonstrates the use of a scrollable, updatable result set. This project shows some simple information taken from a database of contacts.
 |
Users can edit the information shown in the name and phone fields and save the changes back to the database by clicking the Save Changes button.