搜档网
当前位置:搜档网 › mybatis调用存储过程

mybatis调用存储过程

1、创建存储过程:
1)查询:SHOW PROCEDURE STATUS
2)创建:
delimiter //
CREATE PROCEDURE proc1(out id int)
BEGIN
select count(*) from t_user;
END
//
2、java调用:
public int countALl() {
int i = 0;
SqlSession sqlSession = null;
Connection conn = null;
CallableStatement cs = null;
String sql = "{call proc_employee_getCount(?)}";
ResultSet rs = null;
try {
SqlSessionTemplate template = (SqlSessionTemplate) getSqlSession();

conn = SqlSessionUtils.getSqlSession(template
.getSqlSessionFactory(), template.getExecutorType(),
template.getPersistenceExceptionTranslator()).getConnection();
cs = conn.prepareCall(sql);
cs.setInt(1, 1);
rs = cs.executeQuery();
while (rs.next()) {
i = rs.getInt(1);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
MyBatisUtil.closeSession(sqlSession);
}
return i;
}

3、在mybatis中获取Connection:
SqlSessionTemplate template = (SqlSessionTemplate) getSqlSession();
conn = SqlSessionUtils.getSqlSession(template.getSqlSessionFactory(), template.getExecutorType(),
template.getPersistenceExceptionTranslator()).getConnection();
4、发生异常:
java.sql.SQLException: User does not have access to metadata required to determine stored procedure parameter types. If rights
can not be granted, configure connection with "noAccessToProcedureBodies=true" to have driver generate parameters that represent
INOUT strings irregardless of actual parameter types.

解决方法一:
jdbc:mysql://ipaddress:3306/test?noAccessToProcedureBodies=true
网上说设置noAccessToProcedureBodies=true会带来一些影响(未经考证):

1. 调用存储过程时,将没有类型检查,设为字符串类型,并且所有的参数设为in类型,但是在调用registerOutParameter时,不抛出异常。

2. 存储过程的查询结果无法使用getXXX(String parameterName)的形式获取,只能通过getXXX(int parameterIndex)的方式获取。
解决方法二:
给数据库用户赋权,赋执行mysql.proc表的select权限,示例如下:
Sql代码:
GRANT SELECT ON mysql.proc TO 'user'@'localhost';

相关主题