使用 DB2 触发器、Java UDF 和 JavaMail API 实施业务逻辑

 

通过使用示例应用程序，Kulvir Singh Bhogal 向您展示了在客户接近他们的信用限额时，如何自动地触发一封定制的e-mail。

简介

牛顿第三定律表明对于每一个作用力，都存在一个大小相等、方向相反的反作用力。但愿您能记住这一点。不过别担心，我不是要给您上一堂物理课。在本文中，我将指导您理解数据库应用程序中因果关系的实现方式，该数据库应用程序将 DB2 触发器、Java 用户定义函数（udf）以及 JavaMail API 这三个概念综合在一起。

下面高度概括了本文的内容：

1. DB2 数据库中的记录被更改。
2. 如果这个更改使数据记录满足某些基于布尔逻辑的条件，数据库触发器将调用一个 Java UDF。
3. Java UDF 使用 JavaMail API 向指定的收件人发送一封 e-mail。

一个现实中的例子

在开始体验之前，让我们通过一个现实中的例子来看看它的实际应用。假设我们是一家信用卡公司。客户要求当他们接近信用卡限额时，公司能通过 e-mail 通知他们。

图 1 描绘了我们希望能完成的工作。为了更加明确发送 e-mail 之前需要符合的条件，让我们假定在消息发出之前，用户必须在他们信用限额的 15% 之中。

图 1. 从数据库更新到 e-mail 确认的流程

创建数据库

首先，我们需要使用一个数据库。当然，一个真正的信用卡系统的数据库很可能比我们给出的这个简单模型要复杂得多。然而，出于学习目的，我们的例子将尽量简单。

1. 首先，用下面的 SQL 语句创建数据库：

   create db cardDB

   
   
2. 当然，我们必须连接数据库才能进行有用的操作：

   connect to cardDB

   
   
3. 接下来，我们需要一个表来存储所有的客户数据。 表 1 给出了表的列。

   表 1 客户概要信息

   列名	所存储的内容	数据库中的显示
   lastName	姓	varchar(30)
   firstName	名	varchar(30)
   emailAddress	E-mail 地址	varchar(30)
   currentBalance	信用卡余额	decimal(7,2)
   creditLimit	信用卡限额	decimal(7,2)

   下面的 SQL 语句将创建以上描述的表：

   create table cardHolderTable
   (lastName varchar(30) not null,
   firstName varchar(30) not null,
   emailaddress varchar(30) not null,
   currentBalance decimal(7,2) not null,
   creditLimit decimal (7,2) not null)

   
   

   然后，如果我们用语句 DESCRIBE TABLE 来显示表结构，将看到表已经被正确定义 （如 图 2 所示）。

   
   图 2 Cardholder 表结构显示结果
   
   
4. 现在我们需要向表填入一些虚拟的数据值。 表 2 中是一些尝试插入数据库中的样本数据。

表 2. Cardholder 表的样本数据

lastName	firstName	emailAddress	currentBalance	creditLimit
Bhogal	Kulvir	[e-mail]	$3753.00	$5000.00
Peters	Nancy	[e-mail]	$2722.43	$3500
Clancy	Jerry	[e-mail]	$650.23	$1500.00
Biggs	Gloria	[e-mail]	$718.78	$2500.00

重要： 当我们首先将样本数据填入表中时，我们需要使客户的当前余额低于他们的信用限额的 85%。因为在创建触发器之前，即使有客户的当前余额超出了 85%，也不会受到系统的详细检查。

使用下面的 SQL 语句插入 表 2 中显示的第一行数据：

insert into cardHolderTable values
('Bhogal','Kulvir','[e-mail]',3753.00,5000.00)

 

请执行类似的语句将 表2 中其余的虚拟数据记录插入您的样本数据库。

 

先有鸡还是先有蛋？

回到我们的“因果”主题，我们需要同时定义“原因”和“结果”。如果您是第一遍阅读和理解本文，请继续下去阅读 原因 小节。但是，如果您是第二遍阅读并且正在照此做实验，请先跳到 结果 小节。您应该在创建原因（触发器）之前先创建结果(UDF)，这样一来，当您的触发器预备调用 UDF 时，它就已经在那里了。

 

创建触发器（原因）

现在让我们来定义“原因”。我们通过创建 DB2 触发器来完成。

create trigger spawnEmailTrigger
      after update of currentBalance on cardHolderTable
        referencing old as oldrow new as newrow
        for each row
      mode db2sql
when
(newrow.currentBalance>(oldrow.creditLimit*.85))
(values(mailer(oldrow.emailAddress,oldrow.lastName,oldRow.firstName,newRow.
currentBalance,oldRow.creditLimit)))

 

让我们来看一看上面创建触发器的语句。如果您不熟悉触发器，可以这么认为：当在表中进行 delete、insert 或者 update 操作时，触发器是一组作为直接结果而被激活的操作。不过，这可不是另一个牛顿的定义。这就是我们正在谈论的 DB2！

对于数据库程序设计人员来说，触发器的可用功能是十分强大的。在本文中，我们仅仅展示触发器众多功能中的一种。

实际上语法是相当简单的。在我们这个例子中，触发器是由一个 update 操作激活的。对于客户增加信用卡消费额的事务，我们计划在系统中通过更新 currentBalance 列的方式来模拟。（我们的虚拟信用卡跟踪系统是十分粗略的。如果您的信用卡公司使用这样一个骨架系统，我会为您很担忧。然而，作为一个例子，它能运作就很好了。）

让我们回到触发器的详细说明：

• 我们将触发器命名为 spawnEmailTrigger 。
• 关键字 after 表明我们的 e-mail 是在操纵 DB2 表 之后 生成的（与我们更新它 之前 相反）。
• update of currentBalance on cardHolderTable 子句缩小了触发器关注的数据库操作的范围。总之，如果对 cardHolderTable 表中 currentBalance 列执行更新操作，我们就要激活触发器。
• 必须加上子句 mode db2sql 。
• 接下来，我们用一些布尔逻辑来限定触发器的触发条件。通过子句 when (newrow.currentBalance>(oldrow.creditLimit*.85)) ，我们指明了只有当 currentBalance 的值被更新而且超出了客户信用限额的 85% 时，触发器才被触发。执行更新操作时，特别要注意我们如何查阅操作执行前后数据库中的值。这个能力允许我们进行计算，并判断事实上触发器是否被触发了。
• 因此，当执行一个更新操作时，如果该操作将客户抛入了信用卡限额的 15% 以内的这个“红色区域”，我们就用下面的子句调用 UDF 来生成一封 e-mail：

  (values(mailer(oldrow.emailAddress,oldrow.lastName,oldRow.firstName,oldRow.
  currentBalance,oldRow.creditLimit)))

  
  

我们将在下一小节中讨论 UDF。这里要注意的一点就是我们将参数传递给 UDF 函数（名为“mailer”），由该函数来构造我们定制的 e-mail。

 

生成 e-mail （结果）

在触发器中，如果满足指定条件，就调用 Java UDF。 但是，为什么要引用 UDF 呢？下面将花些时间来回答这个问题。然而，我们首先介绍 UDF。

安装 JavaMail jar 文件

我们将要执行的 e-mailing 任务是以 JavaMail API 为基础的。当然，您不一定就有使用这个程序接口的文件。根据使用的 Java 版本，所需的 jar 文件也许您已经处理好了。这里我假设您还没有。实际上，如果您没有所需的 jar 文件，也不需要额外花钱。JavaMail API 是免费的并且可以 下载 。

这里假设您已十分熟悉 JavaMail API。如果不熟悉的话，请参阅 教程 。

假设您已经在硬盘驱动器上安装了两个与 JavaMail API 相关联的叫做 mail.jar 和 activation.jar 的 jar 文件，需要让 DB2 知道这两个文件在哪里。所以我们将 jar 安装到数据库服务器上的“Mail” 和“Activation”名称下。

可以使用以下语句来完成这件事：

call sqlj.install_jar('file:///c:/temp/mail.jar','MAIL')

 

和

call sqlj.install_jar('file:///c:/temp/activation.jar','ACTIVATION')

 

在上面的语句中，我们假设这些 jar 文件在 temp 目录中。您需要调整上面的语句使之指向 jar 文件的正确位置。

使用 Java UDF

现在，让我们转向 Java UDF 代码。您或许希望 下载 代码并花时间研究它。随代码一起提供的文档将指导您学会从 JavaMail API 的使用到创建和发送定制的 e-mail 信息。

凡事都有运行法则，这里关注的 mailer 方法是一个公共的静态方法，它返回一个 String。您的 UDF 方法必须有一个返回类型。我们使用传递给方法的参数来定制 e-mail，并通知目标接受者的信用卡余额正处于“红色”状态。

在可以使用 Java UDF 之前，必须执行一些准备步骤。

1. 编译好 JavaUDF.java 文件后，将结果类文件放到将使用该函数的 DB2 实例的 sqllib/function 目录下。如果您使用的是 Windows NT，并且安装选项是默认的，那么文件将被放入下面的位置：

   C:\\Program Files\\SQLLIB\\function

   
   
2. 接下来，必须注册新创建的 UDF，这样 DB2 才能知道它的存在。

   create function mailer(recipientEmail varchar(30),
   lastName varchar(30),  firstName varchar(30),
   currentBalance decimal(7,2),
   creditLimit decimal(7,2))
      returns varchar(70)
      fenced
      variant
      no sql
      external action
      language java
      parameter style java
      external name 'JavaUDF!mailer'

   
   

• 让我们花点时间来分析这条注册语句。在 CREATE FUNCTION 语句中，我们指明 DB2 中的函数名称。在这个例子中，我们使用 “mailer”。
• 我们还必须指明函数预期得到的 SQL 参数。值得注意的是，我们在这里使用 SQL 参数，然而在实际的 Java UDF 中，是使用 Java 变量类型。如果将 Java 代码与 UDF 注册语句相对照，您将发现，对于 varchar来说，对应的 Java 变量类型是一个 String。类似地，对于 SQL 类型的 decimal (7,2)，Java 中是使用 java.math.BigDecimal 类型。这种 SQL 与 Java 类型一对一的匹配关系，对于事情顺利进行是很必要的。下面是 mailer 方法的签名。

  public static String mailer(String input, String lastName,
  String firstName, BigDecimal currentBalance,
  BigDecimal creditLimit) 

  
  
• 回到我们的注册语句，我们指明函数将返回一个 varchar(70)。它用于存储 UDF 的进度描述。
• 我们使用 fenced 函数，因为它比 non-fenced 函数更加安全。这里不用太深究这个问题，fenced 函数独立运行于数据库管理程序的内部资源。因而即使发生错误，它也不会对管理程序构成太大威胁。
• 往下走，我们声明函数包含 no sql，意思是它不包含 SQL 语句。
• 子句 external action 表明函数执行一个影响数据库外部的操作。
• 通过子句 “language java parameter style java”，通知数据库 UDF 中使用的语言和参数样式为 Java。
• 最后，使用了子句 “external name 'JavaUDF!mailer'” ，通知数据库应查找的 Java 类文件为“JavaUDF”，以及正在注册的特定函数就是“mailer”方法。

局限性： 值得注意的事，我们的设置有一个固有的缺点。那就是我们的外部发送邮件操作无法获知没有成功的事务（例如，通过 Web 表单中止的余额更新操作）。因此，即使数据库事务没有完成，也可能生成一封 e-mail。

 

测试

如果您按照上述步骤至今没有出现任何问题，那您将很快就可以看到劳动成果了。

测试方法为：发出一条 update 语句，使得某个特定的信用卡客户处于信用限额的 15% 以内，触发器将通过创建的 Java UDF 发送一封 e-mail。

如果您使用本文开始建议的特定模拟数据，可以用下面的语句来测试：

 update cardHolderTable
set currentBalance = 4600.00
where lastName = 'Bhogal' and firstName = 'Kulvir'

 

通过发出这条 update 语句，我们已经有效地将姓为 Bhogal 名为 Kulvir 的客户置于信用限额的 15% 以内这个“红色区域”。

相应地，触发器将对这个 update 作出反应，因为它满足了调用 Java UDF 的条件。

通过将进入“红色区域”客户的 e-mail 地址改为您的 e-mail 地址，可以检查设置是否能够正常工作。有一个需要注意的陷阱就是，您的 SMTP 服务器也许不支持 e-mail 转发。这一点在 ISP 中是很普遍的，因为这样可以确保他们的 SMTP 服务器阻止垃圾邮件。因此，在 Java UDF 中要将“发送端”地址指定为 SMTP 服务器可识别的地址。如果您在邮箱中自动地接收到了一封 e-mail，我们的实验就通过了。

 

结束语

DB2 触发器对于数据库程序员来说是一项很强大的功能。在工具箱中拥有了这些触发器，就可以在数据库更新时立即执行操作。触发器允许您以一种将所执行的操作与应 用程序分离的方式来封装和定义业务逻辑。这样做实质上是将某些操作放到后台执行。这种做法在那些有几个不同程序（很可能由不同语言编写的）同时来更新数据 库的环境中特别有用。这些程序可能都有相同的基本功能（例如，发送一封 e-mail）。通过使用在本文中完成的 trigger/UDF 的联合，您可以毫不费力地用不同语言对同一功能进行重复编码。

此外，通过使用 Java UDF，您可以用触发器使 DB2 和 Java 协同工作。在本文中，当客户将要到达他的信用限额时，使用 DB2 触发器调用 Java 用户定义函数。这个 JavaUDF 又通过 JavaMail API 来生成一封定制的 e-mail。

评论

1 楼 xpipi 2008-11-20  

牛X
我这报错了
DB2 SQL error: SQLCODE: -723, SQLSTATE: 09000, SQLERRMC: ADMINISTRATOR.XJ;-4306;42724;ADMINISTRATOR.XJUFIDASETSMUSER|SQL0811201

XJUFIDASETSMUSER是我创建的UDF名称.

请问知道是什么原因么???

先谢过~```