内容


用 Apache Derby 进行开发 —— 取得节节胜利

用 Apache Derby 进行 Java 数据库开发,第 3 部分

修改数据

Comments

系列内容:

此内容是该系列 # 部分中的第 # 部分: 用 Apache Derby 进行开发 —— 取得节节胜利

敬请期待该系列的后续内容。

此内容是该系列的一部分:用 Apache Derby 进行开发 —— 取得节节胜利

敬请期待该系列的后续内容。

简介

本系列的上一篇文章 “用 Apache Derby 进行 Java 数据库开发,第 2 部分”(developerWorks,2007 年 1 月),向您展示了如何使用 Java Statement 对象在 Apache Derby 数据库中执行 SQL SELECT 查询。按照设计,查询将返回满足查询条件的行集。因此,使用 Statement 对象的 executeQuery 方法来执行查询;此方法将把行集作为 Java ResultSet 对象返回。

但是很多 SQL 语句,例如 SQL 数据定义语言(Data Definition Language,DDL)命令,都不返回行集。相反,它们执行操作,例如创建表或插入、更新或删除行。这些操作将返回一个整型值,该值将把操作的结果编码,例如插入或删除了多少行或者错误的可能性。对于 SQL DDL 操作(如清单 1 所示),操作成功则返回数为零。有关将 Apache Derby 数据库与 SQL DDL 语句结合使用的更多信息,请阅读 本系列的第三篇文章。 .

清单 1. 处理 SQL DDL 语句
...
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 INSERTUPDATEDELETE 操作。此方法将把 SQL 发送给 Apache Derby 数据库,将在其中处理 SQL 并返回整型值。就 SQL DDL 操作来说,返回数为零,因此可以在这个介绍性示例的 main 方法中忽略它。在实践中,应当检验该值以防止在尝试在更复杂的模式中使用数据时遇到错误情况。

要运行本文中提供的 Java 程序,需要具有一个干净的工作环境。您可以遵循清单 2 中显示的指引完成该过程,也可以重用在上一篇文章中配好的现有的测试数据库。

清单 2. 通过 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

以下步骤十分简单:

  1. 创建一个干净的工作目录,然后将示例代码展开到这个新目录中。
  2. 使用 Apache Derby ij 工具执行附带的 Apache Derby 脚本文件。
  3. 编译本文附带的所有 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:. 。

数据修改语句

以上示例使用 CREATEDROP 之类的 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) ;
...

在这个 FirstInsert Java 程序中,SQL DDL 语句被替换成了 SQL INSERT 语句,后者被修改为在调用 processStatement 方法之前先添加两个 Java String 对象来包含适当的产品数据。在这种情况下,每一行数据都是单独插入的,并且需要累计 processStatement 方法返回的行数才能确定在数据库中总共插入了多少行。

此操作将把相同的 10 行插入在 本系列 的前面几篇文章中添加的 bigdog.products 表中,但是在这种情况下,它每次只能插入一行。您可以转而编写一个大型字符串尝试一次插入所有数据 —— 在这种情况下为全部的 10 行。但是,这样做不是个好主意,原因有两个:

  • 通过一次插入一行,可对数据库中的数据进行更高级别的控制。如果一行插入失败,则在只处理一行的情况下更易于跟踪问题。
  • 使用单个大型 Java String 插入大量行难于处理并且导致代码更加难以维护。注:不建议在实践中按照这种方式集中添加多个 Java String 对象;相反,应当使用 StringBuffer。但是,出于本文的演示目的,可以遵循这种更简单的方法。

要运行这段 Java 代码,请执行 FirstInsert Java 程序。这样做将用 10 个新行填充 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 进行更快速的处理。

其次,必须为每个输入参数提供值。通过对每个输入参数调用 setXXX 方法可完成此操作。关于此类方法,有两个要点十分重要:

  • 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 intBigDecimalDateString
  • 使用 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 数据库中的数据执行 UPDATEDELETE 甚至 SELECT 操作。在清单 7 中,使用 PreparedStatement 更新 bigdog.products 表中的行,然后使用另一个 PreparedStatement 选择这些行。

清单 7. 使用准备好的语句用于 SQL UPDATEDELETE 操作
...
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 类中,首先定义要传入以创建两个 PreparedStatement 对象的两条 SQL 语句。第一条是 SQL UPDATE 语句,该语句将更新包含大于某个阈值的价格的所在行。第二条是 SQL SELECT 语句,该语句将选择包含大于某个阈值的价格的所在行。您定义了两个新方法分别执行这两个操作,然后执行查询,修改数据,并重新执行同一个查询以检验 SQL UPDATE 操作的结果。清单 8 中显示了执行这个 Java 程序的结果。

清单 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 方法(或者 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 数据修改命令,例如 CREATEDROPINSERTUPDATEDELETE 操作。如果尝试在批处理中包括一条类似 SELECT 查询的 SQL 命令,该查询将返回除了单个更新数以外的所有内容,executeBatch 方法将抛出异常。

调用 batchInsertData 方法还要求对 main 方法进行一些修改:

  1. 设置批处理之前先禁用当前数据库连接的 autocommit 模式。这样做将防止批处理中的 SQL 操作被自动应用到数据库中,如果其中有错误,则这样做可能不是您想要的结果。
  2. 批处理成功完成后,添加一个显式的提交操作。
  3. 添加一个异常处理程序用于处理 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 应用程序中执行更复杂的数据库操作。


下载资源


相关主题


评论

添加或订阅评论,请先登录注册

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Open source, Java technology, Information Management
ArticleID=263719
ArticleTitle=用 Apache Derby 进行开发 —— 取得节节胜利: 用 Apache Derby 进行 Java 数据库开发,第 3 部分
publish-date=06072007