本文通过一个例子简单介绍如何创建并使用Trafodion SPJ。主要步骤如下,
1 编写Java文件
import java.sql.*;
import java.math.*;
public class test
{
public static void adjCredit(String name, String[] newCredit) throws SQLException {
Connection conn = DriverManager.getConnection("jdbc:default:connection");
PreparedStatement updateCredit = conn.prepareStatement("update trafodion.sales.customer " +
"set credit = 'A2' " +
"where custname = ?");
PreparedStatement getCredit = conn.prepareStatement("select credit " +
"from trafodion.sales.customer " +
"where custname = ?");
updateCredit.setString(1, name);
updateCredit.executeUpdate();
getCredit.setString(1, name);
ResultSet rs = getCredit.executeQuery();
rs.next();
newCredit[0] = rs.getString(1);
rs.close();
conn.close();
}
}
2 编译1中的java文件生成class文件
[trafodion@cent-1 ~]$ javac test.java
[trafodion@cent-1 ~]$ ls -la test.*
-rw-rw-r--. 1 trafodion trafodion 1113 Oct 9 09:21 test.class
-rw-rw-r--. 1 trafodion trafodion 823 Oct 9 09:21 test.java
3 打包2中的class文件
[trafodion@cent-1 ~]$ jar cvf test.jar test.class
added manifest
adding: test.class(in = 1113) (out= 642)(deflated 42%)
[trafodion@cent-1 ~]$ ls -la test.*
-rw-rw-r--. 1 trafodion trafodion 1113 Oct 9 09:21 test.class
-rw-rw-r--. 1 trafodion trafodion 1095 Oct 9 09:21 test.jar
-rw-rw-r--. 1 trafodion trafodion 823 Oct 9 09:21 test.java
4 发布3中的jar文件
- 使用pdsh命令在每个节点上创建相同的目录
pdsh $MY_NODES -x $HOSTNAME mkdir $HOME/trafodion-spjs
- 使用pdcp把前面生成的jar复制到每个节点的相同目录下
pdcp $MY_NODES test.jar trafodion-spjs/
- 创建library指向上述jar路径
SQL>create library trafodion.sales.sales file '/home/trafodion/trafodion-spjs/test.jar';
--- SQL operation complete.
SQL>get libraries;
--- SQL operation complete.
SQL>get libraries in schema trafodion.sales;
SALES
--- SQL operation complete.
5 创建SPJ
SQL>create procedure trafodion.sales.adjCredit(in name varchar(10),out credit varchar(10))
+>external name 'test.adjCredit'
+>library trafodion.sales.sales
+>language java
+>parameter style java;
--- SQL operation complete.
SQL>get procedures;
Procedures in Schema TRAFODION.SALES
====================================
ADJCREDIT
6 执行SPJ
SQL>call adjcredit('A',?);
....
CREDIT
----------
A2
--- SQL operation complete.