内容


回到基础:为 Apache Derby 的 ij 工具编写脚本

Comments

ij 工具的用途是什么?

Apache Derby 数据库(及对应的商用产品 IBM® Cloudscape™)最适合嵌入应用程序。它非常小,与 Java™ 数据库连通性(Java™ Database Connectivity,JDBC)和开放数据库连通性(Open Database Connectivity,ODBC)兼容,并构建在 Java 平台上,它可以在多数操作系统上使用。但这种通用性掩盖了它的一种最强大的工具。

ij 工具为简单的任务(如创建数据库和执行 SQL 语句)提供了一种轻松的处理方法。例如,您可以创建两个简单的数据库 —— 一个保持项目,一个保持开发人员(参见 清单 1)。

清单 1. 创建数据库和表
ij> connect 'jdbc:derby:projectDB;create=true;';
ij> create table projects (
   id integer,
   project_name varchar(75),
   status integer,
   comments varchar(255)
);
0 rows inserted/updated/deleted
ij> insert into projects values (1, 'Wheel O Fish', 0, 'Waiting for
 contract');
1 row inserted/updated/deleted
ij> insert into projects values (2, 'Bass O Matic', 1, 'In production');
1 row inserted/updated/deleted
ij> insert into projects values (3, 'Patty O Furniture', -1, 'Need
 clarification
.  Does anybody know what this is?');
1 row inserted/updated/deleted
ij> connect 'jdbc:derby:developerDB;create=true;';
ij(CONNECTION1)> create table developers (
   id integer,
   developer_name varchar(75),
   availability varchar(255)
);
0 rows inserted/updated/deleted
ij(CONNECTION1)> insert into developers values (1, 'Jim Bacon', 'Open');
1 row inserted/updated/deleted
ij(CONNECTION1)> insert into developers values (2, 'Brady James', 'Open');
1 row inserted/updated/deleted
ij(CONNECTION1)> insert into developers values (3, 'Michelle Rappaport',
 'Busy u
ntil October');
1 row inserted/updated/deleted
ij(CONNECTION1)> insert into developers values (4, 'Aaron Templeton', 'A
 joy every time we use him.  Hire him.');
1 row inserted/updated/deleted
ij(CONNECTION1)>

(是的,我们通常会将这些表放入同一个数据库中。但为了演示概念,请允许我采用上述做法。)

即使使用 ij 只是为了创建数据库、表和数据,这个工具依然很有用。但这并非关键所在。ij 使您能够执行许多您原本认为只有使用完备的 JDBC 应用程序才能完成的任务,如操纵游标和创建并执行准备好的语句。

但是,首先要注意,当您创建第二个数据库时 ij 如何创建第二个连接。到 projectDB 的第一个连接仍处于活动状态,但是您需要特别地告诉 ij 您要使用它。

让我们看一看其运作方式。

连接和隔离级别

ij 使您能够做的一件事情是控制整个数据库系统和与数据库的连接。例如,我们现在有两个数据库连接,我们可以使用 SET CONNECTION 命令在它们之间进行选择(参见 清单 2)。

清单 2. 在数据库连接之间进行选择
ij(CONNECTION1)> select * from projects;
ERROR 42X05: Table 'PROJECTS' does not exist.
ij(CONNECTION1)> show connections;
CONNECTION0 -   jdbc:derby:projectDB
CONNECTION1* -  jdbc:derby:developerDB
* = current connection
ij(CONNECTION1)> set connection connection0;
ij(CONNECTION0)> select * from projects;
ID         |PROJECT_NAME       |STATUS     |COMMENTS
-----------------------------------------------------------------
1          |Wheel O Fish       |0          |Waiting for contract

2          |Bass O Matic       |1          |In production

3          |Patty O Furniture  |-1         |Need clarification.  
Does anybody know what this is?


3 rows selected

注意,如果您先尝试从项目表进行选择,即使连接是打开的,仍会出现错误,因为您当前正在使用指向 developerDB 数据库的连接。您可以通过执行 show connections 命令查看此连接。要更改此连接,您可以使用 set connection 命令,后接 show connections 显示的数据库名称。

ij 还使您能够设置所有数据库属性。例如,您可以设置数据库的 derby.database.propertiesOnly 属性,如下所示:

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
'derby.database.propertiesOnly', 'true');

此命令可确保在此数据库上专门设置的属性(如身份验证要求)不被整个系统配置中的其他值否决。

Derby 可识别几十个系统属性,如:derby.database.defaultConnectionModederby.authentication.ldap.searchAuthDNderby.authentication.ldap.searchAuthPWderby.user.UserName

现在,让我们了解一下一些处理数据的方法。

使用游标

当您通过数据库的一个或多个表选择一组数据时,返回的数据也称为游标。(有一种流行的观点认为游标是 “行的当前集合” 的缩写。)隔离级别的文档总是使用游标,但是您不会听说直接通过 ij 操纵游标。事实是,看来需要 Java RecordSet 操纵的许多操作可以直接在 ij 中使用游标完成。

仅向前型(Forward-only)游标

游标的最简单类型是流水型(firehose) 或仅向前型游标。这种游标包括所有的数据,并使您能够遍历游标中的每条记录,但不能向后移动。例如,参见 清单 3

清单 3. 仅向前型游标
ij(CONNECTION0)> get cursor projectCursor as 'select * from projects';

ij(CONNECTION0)> next projectCursor;
ID         |PROJECT_NAME       |STATUS     |COMMENTS
---------------------------------------------------------------------
1          |Wheel O Fish       |0          |Waiting for contract

ij(CONNECTION0)> next projectCursor;
ID         |PROJECT_NAME       |STATUS     |COMMENTS
---------------------------------------------------------------------
2          |Bass O Matic       |1          |In production

ij(CONNECTION0)> next projectCursor;
ID         |PROJECT_NAME       |STATUS     |COMMENTS
---------------------------------------------------------------------
3          |Patty O Furniture  |-1         |Need clarification.  Does
anybody know what this is?

ij(CONNECTION0)> next projectCursor;
No current row

ij(CONNECTION0)> previous projectCursor;

IJ ERROR: PREVIOUS 不允许在仅向前型游标上使用。在此处您可以使用 get cursor 命令创建一个游标,并馈送它一个 SQL 语句,表示您要包含的数据。注意,在这里仅提取单个表中的数据,但是,您可以在这些 SQL 语句中使用与 Derby 提供的一样复杂的功能。

创建了游标后,您可以通知它向您提供下一个记录,在种情况下,它会按 SQL 语句指定的顺序向记录列表下方移动。到达底部时,ij 将通知您没有更多的记录。注意,向列表下方移动时,您不能向后移动;前文已经介绍过,标准的游标是仅向前型游标。但是,这并不意味着您必须按单一方向移动。

滚动不敏感型(Scroll-insensitive)游标

有时,仅向前型游标是不够的,因为您需要在记录之间来回移动。为此,您可以创建滚动不敏感型游标,如 清单 4 所示。

清单 4. 滚动不敏感型游标
ij(CONNECTION0)> get scroll insensitive cursor projectScroll as 
'select * from projects';
ij(CONNECTION0)> absolute 2 projectScroll;
ID         |PROJECT_NAME       |STATUS     |COMMENTS
--------------------------------------------------------------------
2          |Bass O Matic       |1          |In production

ij(CONNECTION0)> relative -1 projectScroll;
ID         |PROJECT_NAME       |STATUS     |COMMENTS
--------------------------------------------------------------------
1          |Wheel O Fish       |0          |Waiting for contract

ij(CONNECTION0)> relative 2 projectScroll;
ID         |PROJECT_NAME       |STATUS     |COMMENTS
--------------------------------------------------------------------
3          |Patty O Furniture  |-1         |Need clarification.  Does
anybody know what this is?

ij(CONNECTION0)> previous projectScroll;
ID         |PROJECT_NAME       |STATUS     |COMMENTS
--------------------------------------------------------------------
2          |Bass O Matic       |1          |In production

ij(CONNECTION0)> before first projectScroll;
No current row
ij(CONNECTION0)> next projectScroll;
ID         |PROJECT_NAME       |STATUS     |COMMENTS
--------------------------------------------------------------------
1          |Wheel O Fish       |0          |Waiting for contract

注意,要使用滚动不敏感型游标,必须关闭自动提交功能。

在这里您可以使用 get scroll insensitive 游标命令,并再一次为要包括的数据指定 SQL 语句。有了滚动不敏感型游标后,您可以更自由地移动。您可以使用 absolute 命令跳到第二行,使用 relative 命令向前和向后移动指定数量的行,移动到前一行或下一行,或使用 before firstafter last 命令将游标定位到数据的顶部或底部。

可更新型(Updateable)游标

上述这些游标都很有用,但是,直接操纵游标的实际价值在于在特定位置更新数据库的能力。例如,您可以遍历项目,并基于当前游标位置更新其状态(参见 清单 5)。

清单 5. 可更新型游标
ij(CONNECTION0)> autocommit off; 
ij(CONNECTION0)> get cursor updateProjects as 
                     'select * from projects for update';
ij(CONNECTION0)> next updateProjects;
ID         |PROJECT_NAME       |STATUS     |COMMENTS
--------------------------------------------------------------------
1          |Wheel O Fish       |0          |Waiting for contract

ij(CONNECTION0)> update projects set status = 1, comments = 
'Contract received, starting work.' where current of updateProjects;
1 row inserted/updated/deleted

ij(CONNECTION0)> next updateProjects;
ID         |PROJECT_NAME       |STATUS     |COMMENTS

--------------------------------------------------------------------
2          |Bass O Matic       |1          |In production

ij(CONNECTION0)> next updateProjects;
ID         |PROJECT_NAME       |STATUS     |COMMENTS
--------------------------------------------------------------------
3          |Patty O Furniture  |-1         |Need clarification.  Does
anybody know what this is?

ij(CONNECTION0)> delete from projects where current of updateProjects;
1 row inserted/updated/deleted
ij(CONNECTION0)> select * from projects;
ID         |PROJECT_NAME       |STATUS     |COMMENTS
--------------------------------------------------------------------
1          |Wheel O Fish       |1          |Contract received, 
starting work.
2          |Bass O Matic       |1          |In production

2 rows selected
ij(CONNECTION0)> commit;
ij(CONNECTION0)> close updateProjects;

首先,您必须绝对确保在创建可更新游标之前关闭了自动提交。其次,可更新型游标必须为仅向前型,select 语句中必须包含 for update 子句。

在此基础上,您可以和前面一样操纵游标,但也可以执行 updatedelete 语句,它们具有游标的当前位置(而不是任何特定数据)需要的 where 子句。

最后,完成时,关闭游标释放资源。

使用准备好的语句(prepared statements)

通常您不会联系到命令行的另外一项任务就是使用准备好的语句。准备好的语句可重新编译,从而获得更好的性能,在您反复执行同一操作时,它非常方便。例如,您可以创建一条语句,设置为可供当前所有开发人员使用,如 清单 6 所示。

清单 6. 准备一条语句
ij(CONNECTION0)> set connection connection1;
ij(CONNECTION1)> prepare allDevelopersOpen as 
                    'update developers set availability = ''open''';
ij(CONNECTION1)> execute allDevelopersOpen;
4 rows inserted/updated/deleted
ij(CONNECTION1)> select * from developers;
ID         |DEVELOPER_NAME       |AVAILABILITY
--------------------------------------------------------
1          |Jim Bacon            |open

2          |Brady James          |open

3          |Michelle Rappaport   |open

4          |Aaron Templeton      |open


4 rows selected
=

在这里,您可以切换回到另一个连接,并使用特定的、硬编码 SQL 语句创建一个准备好的语句,然后执行它。

但更有用的功能是创建可用于不同行和不同值的语句,如 清单 7 所示。

清单 7. 带有值的准备好的语句
ij(CONNECTION1)> prepare updateOneDeveloper as 
               'update developers set availability = ? where id = ?';
ij(CONNECTION1)> execute updateOneDeveloper using 
      'VALUES (''Im really serious here.  Hire this guy!'', 4)';
1 row inserted/updated/deleted
ij(CONNECTION1)> commit;
ij(CONNECTION1)> select * from developers;
ID         |DEVELOPER_NAME       |AVAILABILITY
--------------------------------------------------------
1          |Jim Bacon            |open

2          |Brady James          |open

3          |Michelle Rappaport   |open

4          |Aaron Templeton      |Im really serious here.  Hire this 
guy!


4 rows selected

与 JDBC 应用程序中的准备好的语句一样,值可以按在语句中出现的顺序被各占位符取代。

您甚至可以创建与多个数据集一起使用的准备好的语句(参见 清单 8)。

清单 8. 带有多个值集的准备好的语句
ij(CONNECTION1)> prepare addDeveloper as 
                     'insert into developers values (?, ?, ?)';
ij(CONNECTION1)> execute addDeveloper using 
      'VALUES (5, ''Corben Deeto'', ''Available this summer''), 
              (6, ''Seetha Pio'', ''Open'')';
1 row inserted/updated/deleted
1 row inserted/updated/deleted
ij(CONNECTION1)> select * from developers;
ID         |DEVELOPER_NAME       |AVAILABILITY
--------------------------------------------------------------------
1          |Jim Bacon            |open

2          |Brady James          |open

3          |Michelle Rappaport   |open

4          |Aaron Templeton      |Im really serious here.  HIRE this 
guy!

5          |Corben Deeto         |Available this summer

6          |Seetha Pio           |Open


6 rows selected

结合使用准备好的语句和游标

或许最有用的准备好的语句不仅可以获取输入值,而且还可以对游标的当前行进行操作。例如,您可以创建仅影响当前开发人员的语句(参见 清单 9)。

清单 9. 带有可更新型游标的准备好的语句
ij(CONNECTION1)> get cursor updateDevelopers as 
                    'select * from developers for update';
ij(CONNECTION1)> prepare busyDeveloper as 
                   'update developers set availability = ''Busy'' 
                               where current of updateDevelopers';
ij(CONNECTION1)> next updateDevelopers;
ID         |DEVELOPER_NAME       |AVAILABILITY
--------------------------------------------------------
1          |Jim Bacon            |open

ij(CONNECTION1)> next updateDevelopers;
ID         |DEVELOPER_NAME       |AVAILABILITY
--------------------------------------------------------
2          |Brady James          |open

ij(CONNECTION1)> execute busyDeveloper;
1 row inserted/updated/deleted
ij(CONNECTION1)> next updateDevelopers;
ID         |DEVELOPER_NAME       |AVAILABILITY
--------------------------------------------------------
3          |Michelle Rappaport   |open

ij(CONNECTION1)> next updateDevelopers;
ID         |DEVELOPER_NAME       |AVAILABILITY
--------------------------------------------------------
4          |Aaron Templeton      |Im really serious here.  HIRE 
this guy!

ij(CONNECTION1)> execute busyDeveloper;
1 row inserted/updated/deleted
ij(CONNECTION1)> select * from developers;
ID         |DEVELOPER_NAME       |AVAILABILITY
--------------------------------------------------------
1          |Jim Bacon            |open
2          |Brady James          |Busy
3          |Michelle Rappaport   |open
4          |Aaron Templeton      |Busy
5          |Corben Deeto         |Available this summer
6          |Seetha Pio           |Open

6 rows selected
ij(CONNECTION1)>

从技术角度来讲,在这里您不能执任何其他操作。您可以创建可更新型游标和准备好的语句。关键是准备好的语句中的 SQL 语句会引用游标的当前位置,所以您可以多次执行它,并更新不同的行。这里展示的任务可能是微不足道的,但它也能轻松处理跨多个表、执行触发器等的 SQL 语句。

运行 ij 脚本

如果一篇介绍为 ij 编写脚本的文章没有讨论 ij 脚本的运行,那么这篇文章就不完整。从本质上说,ij 脚本是将 ij 命令编译到一个文件中,然后将它反馈给 ij 应用程序。例如,您可以创建一个添加许多新开发人员和项目的脚本(参见 清单 10):

清单 10. 添加新开发人员和项目的脚本
connect 'jdbc:derby:projectDB;';

insert into projects values (4, 'Movie Addict', 0, 'Waiting for contract');
insert into projects values (5, 'Tree Huggers Anonymous', 0, 
'Waiting for contract');

select * from projects;

connect 'jdbc:derby:developerDB;';

insert into developers values (7, 'Frank Stein', 'Busy');
insert into developers values (8, 'Wolff Mann', 'Open');

select * from developers;

该脚本本身相当简单,仅执行与您通过命令行访问的相同类型的命令。

要运行脚本,您需有几个备选方案。第一个是从 ij 应用程序内部运行它(参见 清单 11)。

清单 11. 从 ij 应用程序内部运行 ij 脚本
ij> run 'C:\myScripts\derbyscript.sql';
ij> connect 'jdbc:derby:projectDB;';
ij> insert into projects values (4, 'Movie Addict', 0, 
'Waiting for contract');
1 row inserted/updated/deleted
ij> insert into projects values (5, 'Tree Huggers Anonymous', 0, 
'Waiting for contract');
1 row inserted/updated/deleted
ij> select * from projects;
ID         |PROJECT_NAME          |STATUS     |COMMENTS
--------------------------------------------------------------------
1          |Wheel O Fish          |1          |Contract received, 
starting work.

2          |Bass O Matic          |1          |In production

4          |Movie Addict          |0          |Waiting for contract

5          |Tree Huggers Anonymous|0          |Waiting for contract


4 rows selected
ij> connect 'jdbc:derby:developerDB;';
ij(CONNECTION1)> insert into developers values (7, 'Frank Stein', 
'Busy');
1 row inserted/updated/deleted

注意,ij 会给出每条命令的反馈。

另一个备选方案是在第一次启动 ij 应用程序时引用脚本,实际上就是使用该文件的内容替换 stdin(参见 清单 12):

清单 12. 将 ij 脚本反馈给 ij 应用程序
C:\SW\db-derby-10.1.2.1-bin\frameworks\embedded\bin>java 
-Djdbc.drivers=org.apache.derby.jdbc.EmbeddedDriver
 org.apache.derby.tools.ij derbyscript.sql
ij version 10.1
ij> connect 'jdbc:derby:projectDB;';
ij> insert into projects values (4, 'Movie Addict', 0, 
'Waiting for contract');
1 row inserted/updated/deleted
ij> insert into projects values (5, 'Tree Huggers Anonymous', 0, 
'Waiting for contract');
1 row inserted/updated/deleted
ij> select * from projects;
ID         |PROJECT_NAME       |STATUS     |COMMENTS
--------------------------------------------------------------------
1          |Wheel O Fish       |1          |Contract received, starting work.

2          |Bass O Matic       |1          |In production

当脚本完成时,ij 应用程序退出。在 ij 内部运行脚本是则不是这样。

在那种情况下,程序将继续正常运行,直到有人键入 exit 为止。

在某些情况下,您需要保存在 ij 运行的脚本输出。为此,仅将输出重新定向到文件,或使用 ij.outputfile 属性(参见 清单 13):

清单 13. 将 ij 脚本的输出保存到文件
java -Djdbc.drivers=org.apache.derby.jdbc.EmbeddedDriver 
-Dij.outfile=results.txt org.apache.derby.tools.ij 
derbyscript.sql

注意,如果您将输出重新定向到文件,则无法在命令行看到输出结果。

结束语

在本文中,您了解了为 Apache Derby 项目提供的 ij 管理工具的一些很少使用的功能。除创建数据库和执行屏幕捕获的普通功能外,ij 还使您能够创建和操纵仅向前型和滚动不敏感型游标,并更新它们。您可以创建并执行准备好的语句,或创建并运行外部脚本。可以将所有这些功能合并在一起,以便在 ij 中创建功能强大的、有用的应用程序,而无需构建任何外部基础设施。


相关主题


评论

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

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Open source, Information Management
ArticleID=168596
ArticleTitle=回到基础:为 Apache Derby 的 ij 工具编写脚本
publish-date=10162006