使用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.Driverc3p0.user=sac3p0.password=sasac3p0.minPoolSize=1c3p0.maxPoolSize=10c3p0.initialPoolSize=1c3p0.acquireIncrement=1c3p0.maxIdleTime=60c3p0.idleConnectionTestPeriod=60c3p0.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 .@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 OUTPUTAS
页:
[1]