使用JTDS调用MS SQL Server 2005的存储过程

JTDS适合在Linux下使用，无需jdbc-odbc桥。调用存储过程时候可以用@ttc_id的方式传入参数。

在实际的项目中，我们鼓励使用存储过程而非jee的持久化方式。存储过程性能更高，大大减少网络上传输的数据量。

1 打开SQL Server Configuration Manager。

将SQL Server 2005网络配置中的MSSQLSERVER协议中的TCP/IP打开。确定netstat -ano中有tcp 1433端口。

默认安装该端口并没有打开

2 使用C3P0做连接池

编辑c3p0.properties

c3p0.jdbcUrl=jdbc:jtds:sqlserver://localhost:1433/ttcard; c3p0.driverClass=net.sourceforge.jtds.jdbc.Driver c3p0.user=sa c3p0.password=sasa c3p0.minPoolSize=1 c3p0.maxPoolSize=10 c3p0.initialPoolSize=1 c3p0.acquireIncrement=1 c3p0.maxIdleTime=60 c3p0.idleConnectionTestPeriod=60 c3p0.maxStatements=0

3 初始化连接池

public static ComboPooledDataSource cpds;

cpds = new ComboPooledDataSource();

4 调用的代码

conn = TransactionServer.cpds.getConnection();

cstmt = conn
.prepareCall("{call dbo.ttcard_transaction(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");

cstmt.setString("@ttc_id", ttc_id);
cstmt.setString("@ttc_passwd", password);
cstmt.setString("@use_way", use_way);
cstmt.setString("@use_type", use_type);
cstmt.setDouble("@use_amount", use_amount);
cstmt.setString("@pos_sno", pos_sno);
cstmt.setString("@pos_num", pos_num);
cstmt.setString("@shop_id", shop_id);
cstmt.setString("@transaction_message", transaction_message);
cstmt.setString("@origin_transaction_message", origin_transaction_message);

cstmt.registerOutParameter("@re_flag", java.sql.Types.INTEGER); // re_flag
cstmt.registerOutParameter("@re_msg", java.sql.Types.VARCHAR); // re_msg
cstmt.registerOutParameter("@rest_amount", java.sql.Types.DOUBLE); // rest_amount
cstmt.registerOutParameter("@valid_date", java.sql.Types.DATE); // valid_date
cstmt.registerOutParameter("@use_id", java.sql.Types.BIGINT); // use_id

cstmt.execute();

re_flag = cstmt.getInt("@re_flag");
re_msg = cstmt.getString("@re_msg");
rest_amount = cstmt.getDouble("@rest_amount");
valid_date = cstmt.getDate("@valid_date");

cstmt.close();
cstmt = null;

conn.close(); //一定要关闭连接，把连接还给连接池

conn = null;

5 存储过程的定义部分如下：

ALTER PROCEDURE [dbo].[ttcard_transaction] @ttc_id varchar(16), @ttc_passwd varchar(32), @use_way char(2), @use_type char(2), @use_amount money, @pos_sno varchar(12), @pos_num varchar(8), @shop_id varchar(15), @transaction_message varchar(42), @origin_transaction_message varchar(42), @re_flag int OUTPUT, @re_msg varchar(255) OUTPUT, @rest_amount money OUTPUT, @valid_date datetime OUTPUT, @use_id bigint OUTPUT AS