首页 > 数据库技术 > 详细

如何使用JDBC调用存储在数据库中的函数或存储过程 */

时间:2016-02-29 01:56:18      阅读:311      评论:0      收藏:0      [点我收藏+]

//创建存储过程

alter proc [dbo].[proc_get_customer1] (
@sid varchar(10),
@name varchar(20) out,
@birth datetime out,
@email varchar(30) out,
@id char(10) out
)with recompile
as
begin
select id,name,birth,email from customers where id<@sid
select @id=id,@name=name,@birth=birth,@email=email from customers where id<@sid
end

/**
*
* @param sql
* @param outCount
* @param params:为IN参数即输入参数,需要注意的是存储过程或函数的输入参数在前,输出参数在后,若有output即输入输出参数的话将output属性的参数放到中间
*/
public void callableProc(String sql, int outCount, Object... params) {
Connection connection = null;
CallableStatement callableStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCTools.getConnection();
callableStatement = connection.prepareCall(sql);
if (!sql.contains("=")) {
for (int i = 0; i < params.length; i++) {
callableStatement.setObject(i + 1, params[i]);
}
callableStatement.registerOutParameter(2,Types.VARCHAR);
callableStatement.registerOutParameter(3, Types.DATE);
callableStatement.registerOutParameter(4, Types.VARCHAR);
callableStatement.registerOutParameter(5,Types.CHAR);
}else{
for (int i = 0; i < params.length; i++) {
callableStatement.setObject(i + 2, params[i]);
}
}
resultSet=callableStatement.executeQuery();
//获得输出参数的cstmt.getInt(3)必须在处理完结果集的所有内容后再执行否则会抛出异常:java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Object has been closed.
while(resultSet.next()){
for(int i=0;i<4;i++){
System.out.print(resultSet.getObject(i+1)+" a ");
}
System.out.println();
}
System.out.println(callableStatement.getString(2));
System.out.println(callableStatement.getDate(3));
System.out.println(callableStatement.getString(4));
System.out.println(callableStatement.getString(5));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCTools.release(resultSet, callableStatement, connection);
}

}

 

//下面是测试

@Test
public void testCustomerProc() {
String sql = "{call proc_get_customer1(?,?,?,?,?)}";
String[] params = { "100090" };
customerDao.callableProc(sql, 5, params);
}

如何使用JDBC调用存储在数据库中的函数或存储过程 */

原文:http://www.cnblogs.com/xiaona19841010/p/5226181.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!