项目中持久层用的是SpringJDBC,在调用存储过程时,网上大致搜了些,自己总结下。
public class StoredService extends BaseService {
private Map<String, Object> param = new HashMap<String, Object>();
{
param.put("c_id", "234");
param.put("c_name", "dddd");
}
public void test1() {
PersonPostResult result = getQuickSupportDao().getQuickJdbcTemplate().execute(
new CallableStatementCreator() {
public CallableStatement createCallableStatement(Connection con) throws SQLException {
String storedProc = "{call proc_test(?,?,?,?)}";// 调用的存储过程
CallableStatement cs = con.prepareCall(storedProc);
cs.setString(1, "111");
cs.setString(2, "mingzi");
cs.registerOutParameter(3, java.sql.Types.VARCHAR);
cs.registerOutParameter(4, java.sql.Types.VARCHAR);
return cs;
}
}, new CallableStatementCallback<PersonPostResult>() {
public PersonPostResult doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
cs.execute();
String status = cs.getString(5);
String msg = cs.getString(6);
PersonPostResult tempResult = new PersonPostResult(status, msg);
return tempResult;
}
});
System.out.println(result);
}
public void test() {
StoredProcedureTemplate t = new StoredProcedureTemplate(
getQuickSupportDao().getQuickJdbcTemplate(), "proc_test");
t.setValue("c_id", "111");
t.setValue("c_name", "mingZi");
t.setVarcharParam("c_id");
t.setVarcharParam("c_name");
t.setVarcharOutParam("status");
t.setVarcharOutParam("msg");
Map map = t.execute();
System.out.println(map);
}
public void test2() {
SimpleJdbcCall call = new SimpleJdbcCall(getQuickSupportDao().getQuickJdbcTemplate());
call.withProcedureName("proc_test").declareParameters(new SqlParameter[]{
new SqlParameter("c_id", Types.VARCHAR),
new SqlParameter("c_name", Types.VARCHAR),
new SqlOutParameter("status", Types.VARCHAR),
new SqlOutParameter("msg", Types.VARCHAR)
}).returningResultSet("message", ParameterizedBeanPropertyRowMapper.newInstance(Message.class));
Map<String, Object> ming = call.execute(param);
System.out.println(ming);
}
public void test3() {
SimpleJdbcCall call = new SimpleJdbcCall(getQuickSupportDao().getQuickJdbcTemplate());
call.withProcedureName("proc_test").declareParameters(new SqlParameter[]{
new SqlParameter("c_id", Types.VARCHAR),
new SqlParameter("c_name", Types.VARCHAR),
new SqlOutParameter("status", Types.VARCHAR),
new SqlOutParameter("msg", Types.VARCHAR)
});
String ming = call.executeObject(String.class, param);
//Message message = call.executeObject(Message.class, param);
System.out.println(ming);
}
class Message{
private String status;
private String msg;
public void setStatus(String status) {
this.status = status;
}
public void setMsg(String msg) {
this.msg = msg;
}
public String getStatus() {
return status;
}
public String getMsg() {
return msg;
}
@Override
public String toString() {
return "Message{" +
"status='" + status + '\'' +
", msg='" + msg + '\'' +
'}';
}
}
}
test1用的是jdbcTemple的execute方法,test采用的是继承抽象类storedProcedure归根到底用的还是jdbcTemple的call方法,
test2用的是SimpleJdbcCall的execute,
test3的executeObject,本来以为能直接返回对象给我,出现错误信息,String不能转成***对象,意思是只取第一个返回值。
10:11:03,267 WARN CallMetaDataContext:251 - Accessing single output value when procedure has more than one output parameter
public String getScalarOutParameterName() {
if (isFunction()) {
return getFunctionReturnName();
}
else {
if (this.outParameterNames.size() > 1) {
logger.warn("Accessing single output value when procedure has more than one output parameter");
}
return (this.outParameterNames.size() > 0 ? this.outParameterNames.get(0) : null);
}
}