内容


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

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

动态数据修改

Comments

系列内容:

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

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

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

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

SQL 游标

在本系列的 上一篇文章 中,您了解了如何通过使用 next() 方法在 Java ResultSet 的各行之间移动。这项技术将反映在 Apache Derby 数据库附带的 ij 工具内执行 SQL 查询时发生的操作。在两种情况下,此功能在 Derby 数据库(或者任何其他兼容 SQL 的数据库)中都是通过游标来实现的。游标 是一种数据库结构,它将允许您循环访问 SQL 查询的结果。

您可以通过显式使用 ij 工具发出 SQL 命令直接与 Derby 中的 SQL 游标进行交互,也可以通过隐式使用 JDBC 应用程序编程接口 (API) 中的相关方法直接与 Derby 中的 SQL 游标进行交互。本文将从在 ij 工具内显式使用 SQL 游标开始讨论这两种方法。

开始时,启动 ij 工具,连接到数据库,如清单 1 中所示。(注:为了节省空间,本文中所示的所有基于 Java 的示例都是经过简化的。)归档文件中提供了每个示例的完整源代码,您可以从本文末尾的 下载 部分中获得该归档文件。还有一个可以执行的 Derby 脚本文件 derby.build.sql(要了解如何创建一个工作的 Derby 数据库以与这些示例结合使用,请参阅本系列的 第四篇文章)。

清单 1. 通过 Apache Derby ij 工具使用 SQL 游标
rb$ java org.apache.derby.tools.ij < derby.build.sql
...
rb$ java org.apache.derby.tools.ij
ij version 10.2
ij> CONNECT 'jdbc:derby:test' ;
ij> AUTOCOMMIT OFF ;
ij> GET CURSOR productsCursor AS 
'SELECT * FROM bigdog.products FOR UPDATE OF price' ;
ij> NEXT productsCursor ;
ITEMNUMBER |PRICE  |STOCKDATE |DESCRIPTION                             
-----------------------------------------------------------------------
1          |19.95  |2006-03-31|Hooded sweatshirt                       
ij> UPDATE bigdog.products SET price = price * 1.10 WHERE CURRENT OF productsCursor ;
1 row inserted/updated/deleted
ij> CLOSE productsCursor ;
ij> SELECT * FROM bigdog.products WHERE itemNumber = 1 ;
ITEMNUMBER |PRICE  |STOCKDATE |DESCRIPTION                             
-----------------------------------------------------------------------
1          |21.94  |2006-03-31|Hooded sweatshirt                       

1 row selected
ij> ROLLBACK ;
ij> SELECT * FROM bigdog.products WHERE itemNumber = 1 ;
ITEMNUMBER |PRICE  |STOCKDATE |DESCRIPTION                             
-----------------------------------------------------------------------
1          |19.95  |2006-03-31|Hooded sweatshirt                       

1 row selected
ij> EXIT ;

如清单 1 中的示例所示,要使用 SQL 游标,您必须有一个适当的数据库。本示例将首先执行样例代码中提供的 Derby 脚本(可在 下载 部分中获得)。接下来,启动 ij 工具,并建立与适当数据库的连接。下一步是禁用 AUTOCOMMIT 模式。默认情况下,AUTOCOMMIT 处于启用状态,并且所有数据修改过程都将导致执行一次提交操作 —— 该操作在默认情况下将关闭所有打开的游标。因而,要执行定点的更新或删除操作,您必须禁用 AUTOCOMMIT 模式。注:如果仅通过游标进行循环访问,则可以把 AUTOCOMMIT 模式保留为启用状态。

下一步是创建指定的游标,方法为使用 GET CURSOR 命令,以及使用必要的 SQL 查询为游标选择行。在 Derby 中,标准游标是只向前的游标,它将从第一行开始,允许您使用 NEXT 命令按顺序遍历行。当您在游标内找到了所需行后,您可以执行相应的 UPDATE 操作。

在此示例中,下一步是关闭游标并执行一次 SELECT 查询以显示修改后的结果。接下来,将执行 ROLLBACK 命令,它将删除更改。这是可能的,因为您永远不执行 COMMIT 命令,并且禁用了 AUTOCOMMIT 模式。为了演示 UPDATE 操作并未被提交到数据库,将重新执行查询,显示最初的结果。

第二版的 JDBC API 引入了对两类新游标的支持:滚动敏感游标滚动不敏感游标。两类游标都将启用滚动,滚动将使您可以在游标内相对和绝对地向前移动和向后移动。这两种类型的不同之处是:它们在游标打开时对底层数据的变化是否敏感。敏感游标将提供底层数据的一个动态视图,而不敏感游标通常对游标打开时数据库的变化不敏感。

从 10.2 版开始,Apache Derby 只对滚动不敏感游标提供支持。此类游标可以用很多有趣的方法来操作,如清单 2 所示。

清单 2. 通过 Apache Derby ij 工具使用滚动不敏感游标
rb$ java org.apache.derby.tools.ij
ij version 10.2
ij> CONNECT 'jdbc:derby:test' ;
ij> AUTOCOMMIT OFF ;
ij> GET SCROLL INSENSITIVE CURSOR productsCursor AS 
'SELECT * FROM bigdog.products FOR UPDATE OF price' ;
ij> ABSOLUTE 5 productsCursor ;
ITEMNUMBER |PRICE  |STOCKDATE |DESCRIPTION                             
-----------------------------------------------------------------------
5          |49.95  |2006-02-20|Female bathing suit, one piece, aqua    
ij> relative -1 productsCursor ;
ITEMNUMBER |PRICE  |STOCKDATE |DESCRIPTION                             
-----------------------------------------------------------------------
4          |29.95  |2006-02-10|Male bathing suit, blue                 
ij> BEFORE FIRST productsCursor ;
No current row
ij> NEXT productsCursor ;
ITEMNUMBER |PRICE  |STOCKDATE |DESCRIPTION                             
-----------------------------------------------------------------------
1          |19.95  |2006-03-31|Hooded sweatshirt                       
ij> UPDATE bigdog.products SET price = price * 1.10 WHERE CURRENT OF productsCursor ;
1 row inserted/updated/deleted
ij> NEXT productsCursor ; 
ITEMNUMBER |PRICE  |STOCKDATE |DESCRIPTION                             
-----------------------------------------------------------------------
2          |99.99  |2006-03-29|Beach umbrella                          
ij> PREVIOUS productsCursor ;
ITEMNUMBER |PRICE  |STOCKDATE |DESCRIPTION                             
-----------------------------------------------------------------------
ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
ij> CLOSE productsCursor ;
ij> SELECT * FROM bigdog.products WHERE itemNumber = 1 ;
ITEMNUMBER |PRICE  |STOCKDATE |DESCRIPTION                             
-----------------------------------------------------------------------
1          |21.94  |2006-03-31|Hooded sweatshirt                       

1 row selected
ij> ROLLBACK ; 
ij> SELECT * FROM bigdog.products WHERE itemNumber = 1 ;
ITEMNUMBER |PRICE  |STOCKDATE |DESCRIPTION                             
-----------------------------------------------------------------------
1          |19.95  |2006-03-31|Hooded sweatshirt                       

1 row selected
ij> EXIT ;

此示例完全不同于第一个示例,使用了滚动不敏感游标。在从 ij 工具内建立了数据库连接后,禁用 AUTOCOMMIT 模式,并创建滚动不敏感游标,您将了解如何使用清单 1 中列出的若干个基本的 SQL 游标操作命令。首先,使用 ABSOLUTE 命令移至游标中的第五行;然后,使用 RELATIVE 命令回移一行(因此游标现在位于第四行)。接下来,使用 BEFORE FIRST 命令把游标定位到第一行之前,这样才可以调用 NEXT 命令来访问游标中的第一行。

此时,执行 SQL UPDATE 操作来修改当前行并移到第二行。最后,尝试回移至第一行以显示已更新的内容。但是将会收到 NullPointerException,因为当前行已不存在 —— 它已被修改,并且更改在当前的游标中不可见。要查看已更新的行,必须关闭该游标并发出一个查询来显示新结果。ROLLBACK 命令将删除更改并退出至操作系统。

表 1. Apache Derby 中的基本 SQL 游标操作命令
ij 命令示例说明
NEXT nameNEXT productsCursor检索游标所在位置的下一行
FIRST nameFIRST productsCursor检索游标中的第一行
LAST nameLAST productsCursor检索游标中的最后一行
PREVIOUS namePREVIOUS productsCursor检索游标中的上一行
ABSOLUTE int nameABSOLUTE 1 productsCursor把游标定位到指定的行数(负整数用于指示从最后一行倒数)
RELATIVE int nameRELATIVE 1 productsCursor把游标定位到一个新行,该行与当前行有若干行之隔(负数表示在游标中回退)
AFTER LAST nameAFTER LAST productsCursor把游标定位到最后一行之后
BEFORE FIRST nameBEFORE FIRST productsCursor把游标定位到第一行之前
GETCURRENTROWNUMBER nameGETCURRENTROWNUMBER productsCursor返回游标中的当前行的行数
CLOSE nameCLOSE productsCursor关闭游标

可更新的 ResultSet

由于整个 Apache Derby 数据库套件是用 Java 语言编写的,因此您可以轻松地通过创建可更新的 ResultSet 并调用适当的 JDBC 方法将 ij 可更新的游标功能复制到您自己的 Java 应用程序中。JDBC API 包括可以用于在可滚动 ResultSet 内移动的许多方法(要在只能向前移动的 ResultSet 中移动,可使用 next() 方法)。表 1 中列出了匹配 ij 命令的一些最重要的方法,包括 next()previous()absolute()relative()beforeFirst()

接下来的几个部分将演示如何使用 JDBC API 有选择性地更新、删除数据或把新数据插入 ResultSet 中。为了简化示例,它们都使用了单向游标;但是您可以轻松地把它们修改为使用可滚动游标,可滚动游标将允许您通过编程的方法在 ResultSet 中来回移动。

更新行

正如您想象的那样,常见的业务需求是在循环访问 ResultSet 中的行时修改特定的列值。例如,您可能需要在销售产品后或更新产品的价格后,根据可用性更新存货清单中的项目数量。使用可更新的 ResultSet,这个过程是相对简单的,如清单 3 所示。

清单 3. 使用 Java 代码更新 Apache Derby 数据库中的行
BigDecimal itemPrice ;
BigDecimal multiplier = new BigDecimal("0.90") ;

Date itemDate ;
Date updateDate = Date.valueOf("2006-1-01") ;

String sql = "SELECT itemNumber, price, stockDate, description FROM bigdog.products" ;

Statement stmt = con.createStatement(
    ResultSet.TYPE_FORWARD_ONLY, 
    ResultSet.CONCUR_UPDATABLE) ;

ResultSet uprs = stmt.executeQuery(sql) ;

while(uprs.next()){
    itemDate = uprs.getDate("stockDate") ;
    if (itemDate.before(updateDate)){
        itemPrice = uprs.getBigDecimal("price") ;
        itemPrice = itemPrice.multiply(multiplier) ;
        uprs.updateBigDecimal("price", itemPrice) ;
        uprs.updateRow() ;
    }
}

uprs.close() ;
stmt.close() ;

这段示例代码将把 bigdog.products 表中的所有旧项目的价格减少为 90%,在实际业务环境中这样做可以移动未售出的商品。要在数据库中完成此过程,您需要首先定义适当的变量以确定测试条件和更新过程,此过程要求使用 DateBigDecimal 值。

下一步是创建可更新的 ResultSet,方法为在 ij 工具内使用 GET CURSOR 命令。当编写 Java 代码来创建可更新的 ResultSet 时,您需要做出的惟一更改是把两个参数传递到 createStatement() 方法中。第一个参数用于指示游标应当是单向的(TYPE_FORWARD_ONLY),还是双向的(TYPE_SCROLL_INSENSITIVE)。第二个参数用于指示游标是只读的(CONCUR_READ_ONLY),还是可更新的(CONCUR_UPDATABLE)。如果不给 createStatement() 方法提供任何参数,默认值为单向只读的 ResultSet。因此,只有在以下情形时才需要为 createStatement() 方法提供参数:由于需要可滚动游标或可更新的 ResultSet 而编写 Java 代码来操作 Derby 数据库。

当您有了一个可更新的 ResultSet 之后,可循环访问各行直至找到需要修改的行。在本例中,查找的是 stockDate 属性为 2006 年 1 月 1 日之前的行。给定一行,调用相应的 updateXXX() 方法,其中 XXX 将被替换为相应的 Java 数据类型 —— 例如,BigDecimal。这些更新方法将获得两个参数:应当修改的列号或列名以及该列的新值。在本例中,将更新 price 列并调用 updateRow() 方法来指示行更新的完成。此时,行更新不是被永久写入到数据库中;直到提交当前事务才永久写入。但是,在当前事务中,更新通常都是可见的。

删除行

您可以调用 deleteRow() 方法来从可更新的 ResultSet 中删除行,如清单 4 所示。

清单 4. 使用 Java 代码删除 Apache Derby 数据库中的行
Date itemDate ;
Date updateDate = Date.valueOf("2006-1-01") ;

String sql = "SELECT itemNumber, price, stockDate, description FROM bigdog.products" ;

Statement stmt = con.createStatement(
    ResultSet.TYPE_FORWARD_ONLY, 
    ResultSet.CONCUR_UPDATABLE) ;

ResultSet uprs = stmt.executeQuery(sql) ;
        
while(uprs.next()){
    itemDate = uprs.getDate("stockDate") ;
    if (itemDate.before(updateDate)){
        uprs.deleteRow() ;
    }
}

uprs.close() ;
stmt.close() ;

在本例中,您将了解如何通过有选择性地从数据库中删除行把旧项目从产品存货清单中删除。在这里,您将专门删除 bigdog.products 表的 stockDate 列值早于 2006 年 1 月 1 日的所有行。为此,您将创建可更新的 ResultSet,在各行之间迭代游标并适当地调用 deleteRow() 方法。虽然在当前事务中立即做出了更改,但是直至提交当前事务才会从数据库中删除行。

插入行

在所有的 SQL 数据修改操作中,插入数据是最复杂的,因为必须为新数据获取新存储空间,并且必须为新数据正确编码。JDBC 标准严格按照清单 5 所示的方法将数据插入到可更新的 ResultSet 中。主要思想是把一个特殊的新行引入到 ResultSet 中,称为insert row。此行用于在准备向数据库中插入行时保存新数据。

清单 5. 使用 Java 代码向 Apache Derby 数据库中插入行
int itemNumber = 11 ;
BigDecimal price = new BigDecimal(99.99) ;
Date stockDate = Date.valueOf("2006-12-07") ;
String description = "Board carrying bag" ;

String sql = 
    "SELECT itemNumber, price, stockDate, description FROM bigdog.products" ; 

Statement stmt = con.createStatement(
    ResultSet.TYPE_FORWARD_ONLY, 
    ResultSet.CONCUR_UPDATABLE) ;

ResultSet uprs = stmt.executeQuery(sql) ;

uprs.moveToInsertRow();
uprs.updateInt("itemNumber", itemNumber);
uprs.updateBigDecimal("price", price) ;
uprs.updateDate("stockDate", stockDate) ;
uprs.updateString("description", description) ;
uprs.insertRow();

uprs.close() ;
stmt.close() ;

在本例中,首先创建需要添加到数据库中的基本数据。在实践中,您可能通过 Web 表单从用户那里获得此数据,也可能通过从供应商的购买订单或发货单中获得此数据。接下来,您将创建可更新的 ResultSet。现在,您已经准备好插入新行,这要求通过调用 moveToInsertRow() 方法把游标移动到特定插入行。当您把游标定位到插入行之后,更新行的各个列以保存新数据值并调用 insertRow() 方法以指示应当保存新值。

如果在此事务中发出一个新查询,将显示新行,因为事务持有新行的锁。其他用户直到提交事务后才能看到新行。由于是在 AUTOCOMMIT 处于禁用状态的情况下工作,因此新行并未永久地保存到 bigdog.products 表中。另一个重要的要点是在插入新行之后可以继续使用可更新的 ResultSet。为此,需要指示底层的数据库游标移回至 ResultSet 中的当前行,方法为调用 moveToCurrentRow() 方法,该方法将把游标移回至移至插入行之前在 ResultSet 中所在的行。在本例中,那是在第一行之前,因此调用 next() 方法将检索到 ResultSet 中的第一行。

定位更新

JDBC API 的灵活性还允许您创建一个可以通过编程的方法来使用的游标,用于通过发出相应的 SQL 命令执行定位更新和删除。要利用此项功能,您需要按名称引用数据库游标,如清单 6 所示。

清单 6. 使用 Java 代码在 Apache Derby 数据库中进行定位更新
con.setAutoCommit(false) ;

Statement stmt = con.createStatement() ;

stmt.setCursorName("PRODUCTSCURSOR") ;

String usql = "SELECT itemNumber, price, stockDate, description " + "" +
    "FROM bigdog.products FOR UPDATE of price, stockDate" ;

ResultSet uprs = stmt.executeQuery(usql) ;

String updateProductsSQL = 
    "UPDATE bigdog.products SET price = ?, stockDate = CURRENT_DATE " +
    "WHERE CURRENT OF " + uprs.getCursorName() ;

PreparedStatement pstmt = con.prepareStatement(updateProductsSQL) ;

本例看上去与先前的 Java 示例很不一样,因为您将嵌入用于执行大多数工作的 SQL 命令。首先,禁用 AUTOCOMMIT 以便更改不会被自动地应用到数据库中。这是十分重要的,因为您将在特定事务中执行多个查询(对于所需的每个 SQL 命令都要执行一次查询);如果 AUTOCOMMIT 处于启用状态(默认),则将在单个事务中执行所有的查询。

下一步是执行适当的 SQL 查询来构造可更新的游标,方法为根据先前的描述使用 FOR UPDATE 子句。接下来,构造 SQL 命令来更新目标行,方法为使用带有 WHERE CURRENT OF 子句的 SQL UPDATE 语句。通过调用 getCursorName() 方法显式检索游标的名称来结束这个子句。您可以调用 setCursorName() 方法显式设定此名称,如本例中所示;也可以使用数据库定义的名称;不论使用哪一种方法,调用正确的 ResultSet 时,getCursorName() 方法都将检索正确的名称。

您可能已经注意到清单 6 从未执行您为 SQL UPDATE 命令构造的 PreparedStatement。源代码中提供的完整清单将向您展示如何循环访问只读的 ResultSet 并更新相应的行,方法为先设定 PreparedStatement 中的值,然后执行 SQL UPDATE 命令。

结束语

您了解了几种可用于动态修改 Apache Derby 数据库中的数据的不同概念。首先了解了通过 ij 工具显式使用 SQL 游标来创建单向的和可滚动的 SQL 游标,然后了解了如何在游标内移动以及如何有选择性地执行数据修改操作。接下来,学习了通过创建可更新的 ResultSet 在 Java 程序中实现类似的功能。最后,学习了结合使用两种方法来执行定位更新和删除。

至此,在本系列中,您已经进入了一个重要的阶段:可以发出查询,处理结果,并从 Java 程序中动态更新 Apache Derby 数据库。下一步是学习几种高级 Java 查询概念,之后您可以学习如何创建和部署包含嵌入式 Apache Derby 数据库的 Java 应用程序。请继续关注本系列!


下载资源


相关主题


评论

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

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