The previous
article in this "Java
database development with Apache Derby" series showed you how to use the Java
Statement object to execute an SQL
SELECT query on an Apache Derby database. A
query, by design, returns the set of rows that satisfy the query. As a
result, you use the Statement object's
executeQuery method to perform the query; this
method returns the set of rows as a Java
ResultSet object.
But many SQL statements, such as SQL Data Definition Language (DDL) commands, don't return a set of rows. Instead, they perform an action, such as creating a table or inserting, updating, or deleting rows. These operations return an integer value that encodes the result of the action, such as how many rows were inserted or deleted, or the possibility of an error. For SQL DDL operations, as shown in Listing 1, the return count is zero for a successful operation. For more information on using SQL DDL statements with the Apache Derby database, read the third article in this series.
Listing 1. Processing SQL DDL statements
...
public class BuildSchema {
...
private static final String dropProductsSQL = "DROP TABLE bigdog.products" ;
private static final String createProductsSQL =
"CREATE TABLE bigdog.products (" +
"itemNumber INT NOT NULL," +
"price DECIMAL(5, 2)," +
"stockDate DATE," +
"description VARCHAR(40))" ;
private static final String productsQuerySQL =
"SELECT itemNumber, price, stockDate, description FROM bigdog.products" ;
static int processStatement(String sql) throws SQLException {
Statement stmt = con.createStatement() ;
int count = stmt.executeUpdate(sql) ;
stmt.close() ;
return(count) ;
}
...
public static void main(String[] args) {
try {
Class.forName(driver) ;
con = DriverManager.getConnection(url);
...
processStatement(dropProductsSQL) ;
processStatement(createProductsSQL) ;
doProductsQuery(productsQuerySQL) ;
} catch (SQLException se) {
printSQLException(se) ;
}
... |
Like the rest of the Java code presented in this article, this example
builds on the ThirdQuery example from the
previous article. As a result, you see only partial code listings here.
(The full code is available in the compressed file available in the Download section.) In this example, you first define several Java
String objects that contain the SQL code for
dropping and creating the bigdog.products table
you've been using throughout this series. You then define a new method,
processStatement, which processes any
appropriate SQL statement it's given by using the
Statement object's
executeUpdate method.
This method can be used only for SQL operations that don't return data,
such as SQL DDL or SQL INSERT,
UPDATE, or DELETE
operations. This method sends the SQL to the Apache Derby database, where
it's processed, and an integer value is returned. In the case of SQL DDL
operations, the return count is zero, so you can ignore it in the
main method of this introductory example. In
practice, you should verify the value to prevent error conditions when you
try to work with data in more complex schemas.
To run the Java programs presented in this article, you need a clean working environment. You can either follow along with the commands displayed in Listing 2, which guide you through the process, or reuse an existing test database you may have from a previous article.
Listing 2. Modifying database schema from Java
rb$ mkdir derbyWork rb$ cd derbyWork rb$ unzip ../derby11.zip Archive: ../derby11.zip inflating: BuildSchema.java inflating: derby.build.sql inflating: FirstInsert.java inflating: FirstUpdate.java inflating: SecondInsert.java inflating: ThirdInsert.java rb$ java org.apache.derby.tools.ij < derby.build.sql ij version 10.2 ... ij> rb$ javac *.java rb$ java BuildSchema ITEMNUMBER |PRICE |STOCKDATE |DESCRIPTION ------------------------------------------------------------------------ 0 rows selected |
These steps are fairly simple:
- Create a clean working directory, then expand the example code into this new directory.
- Use the Apache Derby ij tool to execute the included Apache Derby script file.
- Compile all the Java code included with this article, and then execute
the
BuildSchemaJava program.
As you can see in the example output, the
BuildSchema class first drops and then
re-creates the bigdog.products table, providing
a fresh table that is ready for you to insert new data into.
If you encounter an error in either using the ij tool or compiling or
executing any of the Java classes, the most likely culprit is the Java
CLASSPATH environmental variable. Be sure this variable contains the
requisite Apache Derby JAR files, which you can do by displaying the value
of this variable using the echo $CLASSPATH
command; the command should produce output similar to the following (note
that your Apache Derby installation may change these values slightly):
/opt/Apache/db-derby-10.2.1.6-bin/lib/derby.jar:/ /opt/Apache/db-derby-10.2.1.6-bin/lib/derbytools.jar:. |
The previous example modified the bigdog schema
by using SQL DDL statements like CREATE and
DROP. You can use a similar process to insert
new rows using an SQL INSERT statement, as
shown in Listing 3.
Listing 3. Processing SQL
INSERT statements
...
public class FirstInsert {
...
private static final String insertProductsSQL =
"INSERT INTO bigdog.products(itemNumber, price, stockDate, description) VALUES" ;
private static final String[] productsData =
{"(1, 19.95, '2006-03-31', 'Hooded sweatshirt')",
"(2, 99.99, '2006-03-29', 'Beach umbrella')",
"(3, 0.99, '2006-02-28', '')",
"(4, 29.95, '2006-02-10', 'Male bathing suit, blue')",
"(5, 49.95, '2006-02-20', 'Female bathing suit, one piece, aqua')",
"(6, 9.95, '2006-01-15', 'Child sand toy set')",
"(7, 24.95, '2005-12-20', 'White beach towel')",
"(8, 32.95, '2005-12-22', 'Blue-stripe beach towel')",
"(9, 12.95, '2006-03-12', 'Flip-flop')",
"(10, 34.95, '2006-01-24', 'Open-toed sandal')"} ;
private static final String productsQuerySQL =
"SELECT itemNumber, price, stockDate, description FROM bigdog.products" ;
...
public static void main(String[] args) {
...
int numRows = 0 ;
for(String product: productsData){
numRows += processStatement(insertProductsSQL + product) ;
}
System.out.println("\n" + numRows +
" rows inserted into bigdog.products table.") ;
doProductsQuery(productsQuerySQL) ;
... |
In this FirstInsert Java program, you replace
the SQL DDL statements with an SQL INSERT
statement, which is modified to contain the appropriate product data by
adding the two Java String objects prior to
calling the processStatement method. In this
case, each row of data is inserted separately, and you accumulate the row
count returned by the processStatement method
to determine how many total rows were inserted into the database.
This operation inserts the same 10 rows into the
bigdog.products table that you added in earlier
articles in this
series, but in this case, it does so one row at a time. You could, instead, write
one large string that attempts to insert all the data at once — in this
case, all 10 rows. However, doing so is a bad idea for two reasons:
- By inserting one row at a time, you gain a finer level of control over the data in your database. If one row fails to be inserted, it's much easier to track down the problem when it's the only row being manipulated.
- Using a single large Java
Stringto insert a large number of rows becomes unwieldy and results in code that's harder to maintain. Note that adding multiple JavaStringobjects together in this fashion is not a recommended practice; instead, you should use aStringBuffer. For the demonstration purposes of this article, however, you can follow this simpler approach.
To run this Java code, execute the FirstInsert
Java program. Doing so populates your
bigdog.products table with 10 new rows, which
are displayed as shown in Listing 4.
Listing 4. Using Java code to insert data
rb$ java FirstInsert 10 rows inserted into bigdog.products table. ITEMNUMBER |PRICE |STOCKDATE |DESCRIPTION ------------------------------------------------------------------------ 1 |19.95 |2006-03-31|Hooded sweatshirt 2 |99.99 |2006-03-29|Beach umbrella 3 |0.99 |2006-02-28| 4 |29.95 |2006-02-10|Male bathing suit, blue 5 |49.95 |2006-02-20|Female bathing suit, one piece, aqua 6 |9.95 |2006-01-15|Child sand toy set 7 |24.95 |2005-12-20|White beach towel 8 |32.95 |2005-12-22|Blue-stripe beach towel 9 |12.95 |2006-03-12|Flip-flop 10 |34.95 |2006-01-24|Open-toed sandal 10 rows selected |
In the previous section, you inserted 10 rows of
data into an Apache Derby database by creating a
Java String that contained the appropriate SQL
INSERT statement. This approach, although
functional, isn't optimal, because it required that you create a new
static INSERT statement every time you want to
call the Java Statement object's
executeUpdate method.
A more-efficient approach sends the basic
INSERT statement to the database and then
passes the relevant data for each new row separately as required. This way
you can let the database prepare the SQL statement in the same manner in
which the Java compiler handles a Java function. Following this analogy,
you can then pass new parameters into this prepared SQL statement for
processing. Because this approach can lead to a significant performance
boost, the JDBC specification provides the
PreparedStatement class to enable you to
execute an SQL operation multiple times with different input parameters.
Given its different capabilities, using a
PreparedStatement is different than using a
Statement. First, you need to modify the basic
SQL INSERT statement by using question mark
characters (?) to indicate where an input parameter will be supplied. For
example, VALUES(?, ?, ?, ?) indicates that four
input parameters will be supplied to complete the
VALUES clause of your SQL
INSERT statement. You pass this modified
String as input to the
Connection object's
prepareStatement method, which allows the
Apache Derby database to precompile the SQL for faster processing.
Second, you must supply values for each of the input parameters. You do
this by calling a setXXX method for each
input parameter. Two important points about this class of methods:
XXXis replaced by the data type for the parameter you're sending to the database; for example,setIntindicates you're sending an integer, andsetDateindicates you're sending aDateobject.- These methods take two parameters: the ordinal number of the input parameter and the actual value to be used. By including an ordinal value, which indicates the input parameter that's being set, you don't have to set the input parameters in a particular order.
Although using a PreparedStatement may sound
confusing, it's really simple, as shown in Listing 5.
Listing 5. Using prepared statements for SQL
INSERT operations
...
public class SecondInsert {
...
private static final String insertProductsSQL =
"INSERT INTO bigdog.products(itemNumber, price, stockDate, description) " +
"VALUES(?, ?, ?, ?)" ;
private static final int[] itemNumbers = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10} ;
private static final BigDecimal[] prices =
{new BigDecimal(19.95), new BigDecimal(99.99), new BigDecimal(0.99),
new BigDecimal(29.95), new BigDecimal(49.95), new BigDecimal(9.95),
new BigDecimal(24.95), new BigDecimal(32.95),
new BigDecimal(12.95), new BigDecimal(34.95)} ;
private static final Date[] dates =
{Date.valueOf("2006-03-31"), Date.valueOf("2006-03-29"),
Date.valueOf("2006-02-28"), Date.valueOf("2006-02-10"),
Date.valueOf("2006-02-20"), Date.valueOf("2006-01-15"),
Date.valueOf("2005-12-20"), Date.valueOf("2005-12-22"),
Date.valueOf("2006-03-12"), Date.valueOf("2006-01-24")} ;
private static final String[] descriptions =
{"Hooded sweatshirt", "Beach umbrella", "", "Male bathing suit, blue",
"Female bathing suit, one piece, aqua", "Child sand toy set",
"White beach towel", "Blue-stripe beach towel", "Flip-flop",
"Open-toed sandal"} ;
private static final String productsQuerySQL =
"SELECT itemNumber, price, stockDate, description FROM bigdog.products" ;
...
static void insertData(String sql) throws SQLException {
int numRows = 0 ;
PreparedStatement stmt = con.prepareStatement(sql) ;
for(int itemNumber: itemNumbers){
stmt.setInt(1, itemNumbers[itemNumber - 1]) ;
stmt.setBigDecimal(2, prices[itemNumber - 1]) ;
stmt.setDate(3, dates[itemNumber - 1]) ;
stmt.setString(4, descriptions[itemNumber - 1]) ;
numRows += stmt.executeUpdate() ;
}
System.out.println("\n" + numRows +
" rows inserted into bigdog.products table.") ;
stmt.close() ;
}
public static void main(String[] args) {
...
insertData(insertProductsSQL) ;
doProductsQuery(productsQuerySQL) ;
... |
This example code starts by defining Java arrays that contain the data to
be inserted. Although this is a useful demonstration tactic, in a
production environment you will most likely read this data in from a file
or retrieve it as the result of a calculation or from a user's input. The
other modifications basically relate to changing the code from using a
Statement object to using a
PreparedStatement object. This includes:
- Creating the
PreparedStatementobject. - Setting the relevant input parameters, which include a Java
int,BigDecimal,Date, andString. - Executing the
INSERTstatement by using theexecuteUpdatemethod.
To see this concept in action, first clear out the existing
bigdog.products table, which you can easily do
by running the BuildSchema program, and then
run the SecondInsert program, as shown in
Listing 6 (note that the output has been compressed in this code listing
to save space).
Listing 6. Executing Java prepared
INSERT statementsrb$ java BuildSchema ITEMNUMBER |PRICE |STOCKDATE |DESCRIPTION ------------------------------------------------------------------------ 0 rows selected rb$ java SecondInsert 10 rows inserted into bigdog.products table. ITEMNUMBER |PRICE |STOCKDATE |DESCRIPTION ------------------------------------------------------------------------ 1 |19.94 |2006-03-31|Hooded sweatshirt ... 10 |34.95 |2006-01-24|Open-toed sandal 10 rows selected |
As was the case with the Statement object, the
PreparedStatement object can be used for other
SQL operations in addition to SQL INSERT
operations. For example, you can selectively
UPDATE, DELETE, and
even SELECT data from an Apache Derby database
by using an appropriately set PreparedStatement
object. In Listing 7, you use a
PreparedStatement to update rows in the
bigdog.products table, and then you select
these rows by using a different
PreparedStatement.
Listing 7. Using prepared statements for SQL
UPDATE and DELETE operations
...
public class FirstUpdate {
...
private static final String updateProductsSQL =
"UPDATE bigdog.products SET price = price * 1.25, " + "" +
"stockDate = CURRENT_DATE WHERE price > ?" ;
private static final String productsQuerySQL =
"SELECT itemNumber, price, stockDate, description " +
"FROM bigdog.products WHERE price > ?" ;
...
static void doProductsQuery(String sql) throws SQLException {
...
PreparedStatement stmt = con.prepareStatement(sql) ;
BigDecimal threshold = new BigDecimal(40.00) ;
stmt.setBigDecimal(1, threshold) ;
ResultSet rs = stmt.executeQuery() ;
...
}
static void updateData(String sql) throws SQLException {
PreparedStatement stmt = con.prepareStatement(sql) ;
BigDecimal threshold = new BigDecimal(40.00) ;
stmt.setBigDecimal(1, threshold) ;
int numRows = stmt.executeUpdate() ;
System.out.println("\n" + numRows + " rows updated in bigdog.products table.") ;
stmt.close() ;
}
public static void main(String[] args) {
...
doProductsQuery(productsQuerySQL) ;
updateData(updateProductsSQL) ;
doProductsQuery(productsQuerySQL) ;
... |
In the FirstUpdate class, you first define the
two SQL statements that will be passed in to create the two
PreparedStatement objects. First is the SQL
UPDATE statement, which updates the price of
any row that already has a price greater than some threshold value. Second
is the SQL SELECT statement, which selects any
row that has a price greater than some threshold value. You define two new
methods to separately perform these two operations, and then you perform
the query, modify the data, and redo the same query to verify the results
of the SQL UPDATE operation.
Listing 8. Executing Java prepared UPDATE and SELECT statements
rb$ java FirstUpdate ITEMNUMBER |PRICE |STOCKDATE |DESCRIPTION ------------------------------------------------------------------------ 2 |99.98 |2006-03-29|Beach umbrella 5 |49.95 |2006-02-20|Female bathing suit, one piece, aqua 2 rows selected 2 rows updated in bigdog.products table. ITEMNUMBER |PRICE |STOCKDATE |DESCRIPTION ------------------------------------------------------------------------ 2 |124.97 |2006-11-03|Beach umbrella 5 |62.43 |2006-11-03|Female bathing suit, one piece, aqua 2 rows selected |
Using a PreparedStatement improves the
flexibility and performance characteristics of your Java code, but it
still isn't optimal. This is due to the fact that each SQL
INSERT (or other SQL operation) is performed in
a separate transaction. As you learned in the second article in this series, a transaction is a logical unit of work that Apache Derby uses to
guarantee the consistency of your database. Either all operations in an
individual transaction are successfully completed or the effects of all
the operations are undone or, more formally, rolled back to the previous
database state.
By design, any time you call the executeUpdate
method (or the executeQuery method), the
operation is treated as a single transaction. This results in a
performance degradation due to the overhead of setting up and finalizing
multiple transactions. A better approach is to send a batch of SQL
statements to the database and execute them all together. The JDBC
specification supports a batch capability to facilitate this performance
boost, as demonstrated in Listing 9.
Listing 9. Using batch SQL
INSERT statements
...
public class ThirdInsert {
...
private static final String insertProductsSQL =
"INSERT INTO bigdog.products(itemNumber, price, stockDate, description) " +
"VALUES(?, ?, ?, ?)" ;
...
static void batchInsertData(String sql) throws SQLException {
PreparedStatement stmt = con.prepareStatement(sql) ;
for(int itemNumber: itemNumbers){
stmt.setInt(1, itemNumbers[itemNumber - 1]) ;
stmt.setBigDecimal(2, prices[itemNumber - 1]) ;
stmt.setDate(3, dates[itemNumber - 1]) ;
stmt.setString(4, descriptions[itemNumber - 1]) ;
stmt.addBatch() ;
}
int numRows = 0 ;
int[] counts = stmt.executeBatch() ;
for(int count: counts){
numRows += count ;
}
System.out.println("\n" + numRows +
" rows inserted into bigdog.products table.") ;
stmt.close() ;
}
public static void main(String[] args) {
...
con.setAutoCommit(false) ;
batchInsertData(insertProductsSQL) ;
doProductsQuery(productsQuerySQL) ;
con.commit() ;
}catch(BatchUpdateException bue) {
try{
con.rollback() ;
System.err.println("Batch Update Exception: Transaction Rolled Back") ;
printSQLException((SQLException)bue) ;
}catch(SQLException se){
printSQLException(se) ;
}
... |
The main change in ThirdInsert from the
previous two SQL INSERT example codes is the
new batchInsertData method, which uses the
addBatch method to add each fully defined
PreparedStatement to a batch of statements. You
execute all these statements together within a single transaction by
calling the executeBatch method. For a large
number of SQL statements, this approach is significantly faster, because
the Apache Derby database needs to set up only one transaction for every
batch of statements.
The executeBatch method returns an array of
integers, where each element in the array corresponds to the number of
rows that were inserted, updated, or deleted by the corresponding
statement in the batch. In this example, you have 10 statements in your
batch, so the array contains 10 integers; you iterate over them to get the
total number of rows that were modified in the batch. This point is
important: It clearly indicates that you can include only SQL
data-modification commands in your SQL batch, such as
CREATE, DROP,
INSERT, UPDATE, and
DELETE operations. If you attempt to include in
a batch an SQL command like a SELECT query,
which returns anything other than a single update count, the
executeBatch method will throw an exception.
Calling the batchInsertData method also
requires several modifications to your main
method:
- Disable
autocommitmode for the current database connection before setting up the batch. Doing this prevents the SQL operations in your batch from being automatically applied to the database, which may not be what you intended if something goes wrong. - Add an explicit commit operation after the batch has been successfully processed.
- Add an exception handler for a
BatchUpdateException, which will be thrown if the database runs into a problem while processing your batch.
In this simple example, you roll back the current transaction, which
undoes all database modifications that may have occurred within the batch.
In a production environment, you can call the
getUpdateCount method on a
BatchUpdateException; this method returns an
array whose values indicate whether every SQL operation in your batch
succeeded or failed, thereby allowing you to more easily diagnose and fix
any problems.
To test the batch-insert example, first clean out the
bigdog.products table, and then execute the
ThirdInsert program, as shown in Listing 10.
Listing 10. Executing Java batch-insert statements
rb$ java BuildSchema ITEMNUMBER |PRICE |STOCKDATE |DESCRIPTION ------------------------------------------------------------------------ 0 rows selected rb$ java ThirdInsert 10 rows inserted into bigdog.products table. ITEMNUMBER |PRICE |STOCKDATE |DESCRIPTION ------------------------------------------------------------------------ 1 |19.94 |2006-03-31|Hooded sweatshirt ... 10 |34.95 |2006-01-24|Open-toed sandal 10 rows selected |
In this article, you learned how to use a Java program to modify the
contents of an Apache Derby database. This included using the
Statement object to execute SQL DDL and SQL
INSERT operations. Then you learned how to use
a PreparedStatement to execute SQL
data-modification commands that are dynamically constructed within your
Apache Derby database at run time. Finally, you saw how to construct and
execute a batch of SQL commands to improve the performance of Java
applications that perform a large number of SQL data-modification
commands. Future articles will build on these basic skills and demonstrate
how to execute more complex database operations from within your Java
application.
| Description | Name | Size | Download method |
|---|---|---|---|
| Derby SQL script and Java code for this article | derby11.zip | 8KB | HTTP |
Information about download methods
Learn
- Check out some of other relevant articles
in this series:
- The first article in this series, "Developing with Apache Derby -- Hitting the Trifecta: Introduction to Apache Derby," introduces the Apache Derby database and provides the foundation for many topics in this series.
- The second article in this series, "Developing with Apache Derby -- Hitting the Trifecta: Database development with Apache Derby, Part 1," introduces the ij tool and demonstrates how to use it to connect to an Apache Derby database.
- The fourth article in this series,
"Developing with Apache Derby -- Hitting the Trifecta: Database development with Apache Derby, Part
3," introduces the concept of executing
SQL scripts with Apache Derby and demonstrates how to insert data
into tables in a Derby database using the SQL
INSERTstatement. - The fifth article in this series,
"Developing with Apache Derby -- Hitting the Trifecta: Database development with Apache Derby, Part
4," introduces the concept of an SQL
query and demonstrates how to extract data from a Derby database
using the SQL
SELECTstatement. - The ninth article in this series, "Developing with Apache Derby -- Hitting the Trifecta: Java Database development with Apache Derby, Part 1," discusses how to establish a connection to a Derby database from a Java program.
- The tenth article in this series, "Developing with Apache Derby -- Hitting the Trifecta: Java Database development with Apache Derby, Part 2," discusses how to execute queries on a Derby database from a Java program.
- Access a number of online Apache Derby
project
manuals for
more detailed information on how to use the Derby database.
- Learn how to
download and install
Apache Derby in this Derby Project tutorial.
- Refer to the
Apache Derby developer's reference
manual
for a wealth of useful information, including the type matching that
indicates the allowed conversions to go between SQL data types and their
corresponding Java data types.
- Learn how to properly use the JDBC API on
the
official Web site for JDBC.
- Check out the developerWorks
Apache Derby project area
for articles, tutorials, and other resources to help you get started with
Derby today.
- Stay current with
developerWorks technical events and webcasts.
- Browse all the
Apache articles
and
free Apache tutorials
available in the developerWorks Open source zone.
- Browse for books on these and other
technical topics at the
Safari bookstore.
- Visit the
developerWorks Open source zone
for extensive how-to information, tools, and project updates to help you
develop with open source technologies and use them with IBM's
products.
- Get an
RSS feed for this series.
(Find out more about
RSS.)
Get products and technologies
-
Download Apache Derby.
- Innovate your next
open source development project with
IBM trial software,
available for download or on DVD.
Discuss
- Get involved in the developerWorks
community by participating in
developerWorks blogs.
Comments (Undergoing maintenance)
Table of contents
Next steps from IBM
Access IBM software products in the Amazon Elastic Compute Cloud (EC2) virtual environment.
My developerWorks community
Tags
Use the slider bar to see more or fewer tags.
Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).
My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
