先定义枚举:
public enum ProcParamType {
IN,OUT
}
调用存储过程的描述
public class ProcCallableDescriptor {
private List<ProcParam> procParams = new ArrayList<ProcParam>();
public List<ProcParam> getProcParams() {
return procParams;
}
public void addProPram(ProcParam param) {
procParams.add(param);
}
public void addInProParam(int index, Object paramValue) {
addProPram(new ProcParam(index, paramValue));
}
public void addOutProParam(int index, int sqlType) {
addProPram(new ProcParam(index, sqlType));
}
public static class ProcParam {
private int index;
private ProcParamType paramType;
private Object paramValue;
private int sqlType; //出参数时指定sqlType
/**
* 入参数构造函数
* @param index
* @param paramValue
*/
public ProcParam(int index, Object paramValue) {
super();
this.index = index;
this.paramType = ProcParamType.IN;
this.paramValue = paramValue;
}
/**
* 出参数构造函数
* @param index
* @param sqlType
*/
public ProcParam(int index, int sqlType) {
super();
this.index = index;
this.paramType = ProcParamType.OUT;
this.sqlType = sqlType;
}
public int getIndex() {
return index;
}
public void setIndex(int index) {
this.index = index;
}
public ProcParamType getParamType() {
return paramType;
}
public void setParamType(ProcParamType paramType) {
this.paramType = paramType;
}
public Object getParamValue() {
return paramValue;
}
public void setParamValue(Object paramValue) {
this.paramValue = paramValue;
}
public int getSqlType() {
return sqlType;
}
public void setSqlType(int sqlType) {
this.sqlType = sqlType;
}
}
}
服务类:
@Service
public class ProcedureService {
@Autowired
protected SessionFactory sessionFactory;
public Object call(String sqlStatement, ProcCallableDescriptor descriptor) throws Exception {
List<Object> outParams = new ArrayList<Object>();
Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
Connection con = SessionFactoryUtils.getDataSource(sessionFactory).getConnection();
try {
CallableStatement cstmt = con.prepareCall(sqlStatement);
if (ObjectUtils.isNotEmpty(descriptor.getProcParams())) {
for (ProcCallableDescriptor.ProcParam param : descriptor.getProcParams()) {
if (ProcParamType.IN.equals(param.getParamType())) {
cstmt.setObject(param.getIndex(), param.getParamValue());
} else if (ProcParamType.OUT.equals(param.getParamType())) {
cstmt.registerOutParameter(param.getIndex(), param.getSqlType());
}
}
}
cstmt.execute();
if (ObjectUtils.isNotEmpty(descriptor.getProcParams())) {
for (ProcCallableDescriptor.ProcParam param : descriptor.getProcParams()) {
if (ProcParamType.OUT.equals(param.getParamType())) {
Object outObj = cstmt.getObject(param.getIndex());
//如果出参是ResultSet类型直接转换成ListOfMap结构
if (outObj instanceof ResultSet) {
List<Map<String, Object>> listOfMaps = new ArrayList<Map<String, Object>>();
ResultSet rs = (ResultSet) outObj;
while (rs.next()) {
Map<String, Object> row = new LinkedHashMap<String, Object>();
for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
row.put(rs.getMetaData().getColumnLabel(i).toLowerCase(), rs.getObject(i));
}
listOfMaps.add(row);
}
rs.close();
outParams.add(listOfMaps);
} else {
outParams.add(outObj);
}
}
}
}
cstmt.close();
tx.commit();
} catch (Exception e) {
tx.rollback();
throw e;
} finally {
if(con!=null){
con.close();
}
session.close();
}
int outPramCount = 0;
if (ObjectUtils.isNotEmpty(descriptor.getProcParams())) {
for (ProcCallableDescriptor.ProcParam param : descriptor.getProcParams()) {
if (ProcParamType.OUT.equals(param.getParamType())) {
outPramCount++;
}
}
}
if (outPramCount == 1 && ObjectUtils.isNotEmpty(outParams)) {
return outParams.get(0);
} else {
return outParams;
}
}
}
test测试类:
public class ProcedureServiceTest extends AbstractTestCase{
@Autowired
private ProcedureService service;
@Test
public void test() throws Exception{
ProcCallableDescriptor desc = new ProcCallableDescriptor();
desc.addInProParam(1, DateUtils.toSqlDate("2012-06-08", "yyyy-MM-dd"));
desc.addInProParam(2, DateUtils.toSqlDate("2012-06-09", "yyyy-MM-dd"));
desc.addOutProParam(3, -10);
Object obj = service.call("{call rp1(?,?,?)} ", desc);
System.out.println(obj);
}
}
转载于:https://blog.51cto.com/sherwin28/1080479