该“使用Apache Derby进行Java数据库开发”系列的上一篇文章向您展示了如何使用Java Statement对象在Apache Derby数据库上执行SQL SELECT查询。 根据设计，查询将返回满足查询的行集。 结果，您使用了Statement对象的executeQuery方法来执行查询。 此方法将行集作为Java ResultSet对象返回。
但是许多SQL语句（例如SQL数据定义语言（DDL）命令）不会返回一组行。 相反，它们执行某种操作，例如创建表或插入，更新或删除行。 这些操作返回一个整数值，该值对操作的结果进行编码，例如插入或删除了多少行，或者发生错误的可能性。 对于SQL DDL操作，如清单1所示，对于成功的操作，返回计数为零。 有关将SQL DDL语句与Apache Derby数据库一起使用的更多信息，请阅读本系列的第三篇文章 。
...
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) ;
}
...像本文中介绍的其余Java代码一样，该示例基于前一篇文章的ThirdQuery示例。 结果，您在这里仅看到部分代码清单。 （完整的代码位于下载部分的压缩文件中。）在本示例中，您首先定义几个Java String对象，这些对象包含用于删除和创建在本系列中一直使用的bigdog.products表SQL代码。 。 然后，您定义一个新方法processStatement ，该方法处理通过使用Statement对象的executeUpdate方法给出的任何适当SQL语句。
此方法只能用于不返回数据SQL操作，例如SQL DDL或SQL INSERT ， UPDATE或DELETE操作。 此方法将SQL发送到Apache Derby数据库，在该数据库中对其进行处理，并返回一个整数值。 对于SQL DDL操作，返回计数为零，因此您可以在此入门示例的main方法中将其忽略。 在实践中，当您尝试使用更复杂的架构中的数据时，应验证该值以防止出现错误情况。
要运行本文介绍的Java程序，您需要一个干净的工作环境。 您可以按照清单2中显示的命令进行操作，以指导您完成该过程，或者重用上一篇文章中可能已有的现有测试数据库。
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
您可能会注意到清单2中显示的Apache数据库版本现在为10.2。 发布新版本的数据库时，数据库管理员必须小心。 遵循定义明确的迁移路径并进行足够的回归测试以验证关键业务应用程序的准确性，所有升级都应谨慎进行。 但是，出于本系列文章的目的，您可以谨慎对待。 这个新版本具有许多附加功能和一些错误修复，使升级成为一个轻松的决定。
这些步骤非常简单：
创建一个干净的工作目录，然后将示例代码扩展到该新目录中。
使用Apache Derby ij工具执行随附的Apache Derby脚本文件。
编译本文附带的所有Java代码，然后执行BuildSchema Java程序。
从示例输出中可以看到， BuildSchema类首先删除，然后重新创建bigdog.products表，从而提供一个可供您插入新数据的新鲜表。
如果在使用ij工具或编译或执行任何Java类时遇到错误，则最可能的罪魁祸首是Java CLASSPATH环境变量。 确保此变量包含必需的Apache Derby JAR文件，您可以通过使用echo $CLASSPATH命令显示此变量的值来完成； 该命令应产生类似于以下内容的输出（请注意，您的Apache Derby安装可能会稍微更改这些值）：
/opt/Apache/db-derby-10.2.1.6-bin/lib/derby.jar:/
/opt/Apache/db-derby-10.2.1.6-bin/lib/derbytools.jar:.数据修改语句
前面的示例通过使用CREATE和DROP类SQL DDL语句修改了bigdog模式。 您可以使用类似的过程通过SQL INSERT语句插入新行，如清单3所示。
清单3.处理SQL INSERT语句
...
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) ;
...
明确查询：最佳做法
在清单3SQL INSERT语句中，显式列出了列名（ itemNumber ， price等）。 尽管可以忽略它们，但不建议这样做：如果其他人通过例如重命名，添加或删除列意外修改了架构，它将使您的代码暴露于意外错误和潜在的静默错误。 通过显式列出列的名称及其顺序，可以最大程度地减少此类错误的风险。
在此FirstInsert Java程序中，将SQL DDL语句替换为SQL INSERT语句，通过在调用processStatement方法之前添加两个Java String对象，将其修改为包含适当的产品数据。 在这种情况下，每行数据都是分别插入的，您可以累加processStatement方法返回的行数，以确定向数据库中插入了多少行。
该操作将相同的10行插入到您在本系列的先前文章中添加的bigdog.products表中，但是在这种情况下，它一次bigdog.products插入一行。 相反，您可以编写一个大字符串，尝试一次插入所有数据-在这种情况下，将插入所有10行。 但是，这样做有两个不好的主意：
- 通过一次插入一行，您可以更好地控制数据库中的数据。 如果无法插入一行，那么当它是唯一要操纵的行时，查找问题会容易得多。
- 使用单个大的Java String插入大量行变得笨拙，并且导致难以维护的代码。 请注意，不建议以此方式将多个Java String对象一起添加； 相反，您应该使用StringBuffer 。 但是，出于本文的演示目的，您可以采用这种更简单的方法。
要运行此Java代码，请执行FirstInsert Java程序。 这样做将在bigdog.products表中填充10个新行，如清单4所示。
清单4.使用Java代码插入数据
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准备好的陈述
在上一节中 ，通过创建包含适当SQL INSERT语句的Java String ，将10行数据插入到Apache Derby数据库中。 这种方法虽然实用，但并不是最佳方法，因为它要求您每次要调用Java Statement对象的executeUpdate方法时都创建一个新的静态INSERT语句。
一种更有效的方法将基本INSERT语句发送到数据库，然后根据需要分别为每个新行传递相关数据。 这样，您可以让数据库以Java编译器处理Java函数的相同方式准备SQL语句。 按照此类推，然后可以将新参数传递到此准备好SQL语句中进行处理。 因为这种方法可以显着提高性能，所以JDBC规范提供了PreparedStatement类，使您可以使用不同的输入参数多次执行SQL操作。
动态SQL INSERT语句
鉴于其不同的功能，使用PreparedStatement与使用Statement是不同的。 首先，您需要通过使用问号字符（？）指示输入参数的提供位置来修改基本SQL INSERT语句。 例如， VALUES(?, ?, ?, ?)表示将提供四个输入参数来完成SQL INSERT语句的VALUES子句。 将此修改后的String作为输入传递给Connection对象的prepareStatement方法，该方法允许Apache Derby数据库预编译SQL以进行更快的处理。
其次，您必须为每个输入参数提供值。 您可以通过为每个输入参数调用set XXX方法来做到这一点。 关于此类方法的两个要点：
- XXX替换为要发送到数据库的参数的数据类型； 例如， setInt表示您要发送一个整数， setDate表示您要发送一个Date对象。
- 这些方法采用两个参数：输入参数的序号和要使用的实际值。 通过包含序数值（表示要设置的输入参数），您不必按特定顺序设置输入参数。
尽管使用PreparedStatement听起来可能令人困惑，但它确实很简单，如清单5所示。
清单5.对SQL INSERT操作使用准备好的语句
...
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) ;
...此示例代码从定义包含要插入的数据的Java数组开始。 尽管这是一种有用的演示策略，但是在生产环境中，您很可能会从文件中读取此数据，或者将其作为计算结果或从用户输入中检索出来。 其他修改基本上与将代码从使用Statement对象更改为使用PreparedStatement对象有关。 这包括：
- 创建PreparedStatement对象。
- 设置相关的输入参数，包括Java int ， BigDecimal ， Date和String 。
- 通过使用executeUpdate方法执行INSERT语句。
要了解这个概念的实际效果，请首先清除现有的bigdog.products表，您可以通过运行BuildSchema程序轻松地完成该表，然后运行SecondInsert程序，如清单6所示（请注意，此输出已压缩）代码清单以节省空间）。
清单6.执行Java准备的INSERT语句
rb$ 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动态更新和选择
与Statement对象一样， PreparedStatement对象可用于除SQL INSERT操作之外的其他SQL操作。 例如，通过使用适当设置的PreparedStatement对象，可以从Apache Derby数据库中选择性地UPDATE ， DELETE甚至SELECT数据。 在清单7中，使用PreparedStatement更新bigdog.products表中的行，然后通过使用其他PreparedStatement选择这些行。
清单7.对SQL UPDATE和DELETE操作使用准备好的语句
...
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) ;
...在FirstUpdate类中，首先定义将要传递的两个SQL语句，以创建两个PreparedStatement对象。 首先是SQL UPDATE语句，它更新价格已经超过某个阈值的任何行的价格。 第二个是SQL SELECT语句，该语句选择价格大于某个阈值的任何行。 您定义两个新方法来分别执行这两个操作，然后执行查询，修改数据并重做同一查询以验证SQL UPDATE操作的结果。
清单8.执行Java准备的UPDATE和SELECT语句
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批量操作
使用PreparedStatement可以提高Java代码的灵活性和性能特征，但是仍然不是最佳选择。 这是由于每个SQL INSERT （或其他SQL操作）在单独的事务中执行的事实。 正如您在本系列的第二篇文章中了解到的那样，事务是Apache Derby用来保证数据库一致性的逻辑工作单元。 单个事务中的所有操作都已成功完成，或者所有操作的效果都已撤消，或更正式地，已回滚到先前的数据库状态。
根据设计， executeUpdate调用executeUpdate方法（或executeQuery方法）时，该操作都被视为单个事务。 由于建立和完成多个事务的开销而导致性能下降。 更好的方法是将一批SQL语句发送到数据库并一起执行它们。 JDBC规范支持批处理功能以促进这种性能提升，如清单9所示。
清单9.使用批处理SQL INSERT语句
...
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) ;
}
...与前面两个SQL INSERT示例代码相比， ThirdInsert的主要更改是新的batchInsertData方法，该方法使用addBatch方法将每个完全定义的PreparedStatement添加到一批语句中。 您可以通过调用executeBatch方法在一个事务中一起执行所有这些语句。 对于大量SQL语句，此方法明显更快，因为Apache Derby数据库仅需要为每批语句设置一个事务。
executeBatch方法返回一个整数数组，该数组中的每个元素对应于批处理中相应语句插入，更新或删除的行数。 在此示例中，批处理中有10条语句，因此该数组包含10个整数。 您可以对它们进行迭代以获得批处理中已修改的总行数。 这一点很重要：它清楚地表明您只能在SQL批处理中包括SQL数据修改命令，例如CREATE ， DROP ， INSERT ， UPDATE和DELETE操作。 如果您试图在批处理中包含一个SELECT查询之类SQL命令，该命令将返回除单个更新计数以外的任何内容，那么executeBatch方法将引发异常。
调用batchInsertData方法还需要对main方法进行一些修改：
- 在设置批处理之前，请为当前数据库连接禁用autocommit模式。 这样做可以防止批处理中SQL操作自动应用于数据库，如果出现问题，这可能不是您想要的。
- 成功处理批处理后，添加显式提交操作。
- 为BatchUpdateException添加一个异常处理程序，如果在处理批处理时数据库遇到问题，则将引发该异常处理程序。
在这个简单的示例中，您回滚当前事务，该操作撤消了批处理中可能发生的所有数据库修改。 在生产环境中，可以在BatchUpdateException上调用getUpdateCount方法； 此方法返回一个数组，该数组的值指示批处理中的每个SQL操作是成功还是失败，从而使您可以更轻松地诊断和解决任何问题。
要测试批处理插入示例，请首先清理bigdog.products表，然后执行ThirdInsert程序，如清单10所示。
清单10.执行Java批处理插入语句
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摘要
在本文中，您学习了如何使用Java程序来修改Apache Derby数据库的内容。 这包括使用Statement对象执行SQL DDL和SQL INSERT操作。 然后，您学习了如何使用PreparedStatement执行在运行时在Apache Derby数据库中动态构建SQL数据修改命令。 最后，您了解了如何构造和执行一批SQL命令，以提高执行大量SQL数据修改命令的Java应用程序的性能。 以后的文章将基于这些基本技能，并演示如何从Java应用程序内部执行更复杂的数据库操作。
翻译自: https://www.ibm.com/developerworks/opensource/library/os-ad-trifecta11/index.html
