java中调用存储过程(sqlserver)

java中调用存储过程(sqlserver)

sqlserver中定义存储过程，转账的例子

SELECT * FROM bank
GO
--创建存储过程
CREATE PROC myTRANSFER
	@inId int,
	@outId int,
	@money float,
	@inIdMoney float OUTPUT,--输出参数
	@outIdMoney float OUTPUT --输出参数
AS
UPDATE bank SET currentMoney = currentMoney + @money WHERE cid = @inId
UPDATE bank SET currentMoney = currentMoney - @money WHERE cid = @outId
SELECT @inIdMoney = currentMoney FROM bank WHERE cid = @inId
SELECT @outIdMoney = currentMoney FROM bank WHERE cid = @outId
PRINT '转进后的金额=' + CAST(@inIdMoney AS VARCHAR(10))
PRINT '转出后的金额=' + CAST(@outIdMoney  AS VARCHAR(10))
GO

--调用存储过程
DECLARE @a FLOAT
DECLARE @b FLOAT
--调用存储过程的时候，使用EXECUTE关键字，可以简写成EXEC
EXEC myTRANSFER @inId=10,@outId=11,@money=100,@inIdMoney=@a OUTPUT,@outIdMoney=@b OUTPUT
PRINT '(调用存储过程-输出参数)转进后的金额=' + CAST(@a AS VARCHAR(10))
PRINT '(调用存储过程-输出参数)转出后的金额=' + CAST(@b AS VARCHAR(10))
GO

SELECT * FROM bank
GO

java中调用存储过程

package com.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;

public class TestProc {
	
	final static String URL = "jdbc:sqlserver://localhost:1433;databaseName=testDB";
	final static String SQLSERVERDRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	final static String USERNAME = "sa";
	final static String PASSWORD = "123456";

	/** 
	*java中调用存储过程(该例子中使用的是sqlserver数据库，mysql和Oracle数据库同理)
	 * @throws Exception 
	*/
	public static void main(String[] args) throws Exception {
		Class.forName(SQLSERVERDRIVER);
		Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		String call = "{call myTRANSFER(?, ?, ?, ?, ?)}";
		CallableStatement callStatement = conn.prepareCall(call);
		callStatement.setInt(1, 10);
		callStatement.setInt(2, 11);
		callStatement.setFloat(3, 100);
		callStatement.registerOutParameter(4, java.sql.Types.FLOAT);
		callStatement.registerOutParameter(5, java.sql.Types.FLOAT);
		callStatement.execute();
		System.out.println(callStatement.getFloat(4));
		System.out.println(callStatement.getFloat(5));
		callStatement.close();
		conn.close();
	}

}