使用Spring的JdbcTemplate简单实现
支持入参, 支持游标返回
调用方式
import xxx.RoutingDataSource;
import xxx.BaseService;
import xxx.DbStoreProcUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
/**
* xxx
*
* @author zhangbs
* @version 2021-01-08
*/
@Service
@Transactional(readOnly = true)
public class UpdateGoodsPricingService extends BaseService {
@Autowired
private RoutingDataSource routingDataSource;
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* xxxxx
*/
@Transactional(readOnly = false)
public void execute() {
// 设置jdbcTemplate的数据源(单体模式单一数据源不需要设置jdbcTemplate的数据源, 使用默认数据源即可)
jdbcTemplate.setDataSource(routingDataSource.getDataSource("xxx"));
// 调用存储过程xxxxxx
DbStoreProcUtils.execute(jdbcTemplate, "xxxxxx");
}
}
import xxx.ListUtils;
import xxx.MapUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;
/**
* EIP调用存储过程工具类
*
* @author zhangbs
* @version 2020-05-11
*/
public class DbStoreProcUtils {
/**
* 存储过程返回类型 cursor游标
*/
private static final String RESULT_TYPE_CURSOR = "cursor";
/**
* 返回类型 output 以存储过程方法声明的参数进行返回,eg: @p_out_amt decimal(18,2) OutPut
*/
private static final String RESULT_TYPE_OUT_PUT = "output";
/**
* 调用存储过程,无返回值 无入参
*
* @param jdbcTemplate jdbcTemplate对象
* @param procName 存储过程名称
*/
public static void execute(JdbcTemplate jdbcTemplate, String procName) {
execute(jdbcTemplate, procName, null, null, null);
}
/**
* 调用存储过程,有入参 无返回值
*
* @param jdbcTemplate jdbcTemplate对象
* @param procName 存储过程名
* @param params 入参
*/
public static void execute(JdbcTemplate jdbcTemplate, String procName, Map<String, Object> params) {
execute(jdbcTemplate, procName, params, null, null);
}
/**
* 调用存储过程,无入参,有游标返回值
*
* @param jdbcTemplate jdbcTemplate对象
* @param procName 存储过程名
* @return 返回结果
*/
public static Object queryForList(JdbcTemplate jdbcTemplate, String procName) {
return execute(jdbcTemplate, procName, null, RESULT_TYPE_CURSOR, null);
}
/**
* 调用存储过程,有入参 有游标返回值
*
* @param jdbcTemplate jdbcTemplate对象
* @param procName 存储过程名
* @param params 入参
* @return 返回结果
*/
public static Object queryForList(JdbcTemplate jdbcTemplate, String procName, Map<String, Object> params) {
return execute(jdbcTemplate, procName, params, RESULT_TYPE_CURSOR, null);
}
/**
* 调用存储过程
* 支持无参,有参,无返回值,有返回值等的调用
*
* @param jdbcTemplate jdbcTemplate对象(需要在调用处实例化,并修改其DataSource数据源)
* eg: jdbcTemplate.setDataSource(routingDataSource.getDataSource("zy_data"));
* @param procName 存储过程名
* @param params 入参
* @param resultType 存储过程返回类型(调用的存过无返回值时,传null)
* @param outArgInfo 存储过程返回值的参数类型(调用的存过无返回值时传null,此类型非返回游标里的字段类型!!)
* eg: Map<String, Object> outArgInfo = MapUtils.newHashMap();
* outArgInfo.put("p_out_amt", Types.DECIMAL);
*/
private static Object execute(JdbcTemplate jdbcTemplate, String procName, Map<String, Object> params,
String resultType, Map<String, Object> outArgInfo) {
// 检查输出参数的合法性
checkOutArgs(outArgInfo);
// 构建存储过程调用SQL语句
String execSql = buildCallProcSQL(procName, params, outArgInfo);
Object result = jdbcTemplate.execute((Connection con) -> {
CallableStatement cs = con.prepareCall(execSql);
// 设置入参的参数值
if(MapUtils.isNotEmpty(params)){
for (String inArgName : params.keySet()) {
cs.setObject(inArgName, params.get(inArgName));
}
}
// 注册输出参数
if (MapUtils.isNotEmpty(outArgInfo)) {
// 注册输出参数
for (String outArgName : outArgInfo.keySet()) {
cs.registerOutParameter(outArgName, (Integer) outArgInfo.get(outArgName));
}
}
return cs;
}, (CallableStatement cs) -> {
// 如果是游标返回
if (RESULT_TYPE_CURSOR.equals(resultType)) {
return convertResultSetToList(cs.executeQuery());
} else { //如果是output返回
cs.execute();
if (RESULT_TYPE_OUT_PUT.equals(resultType)) {
return buildOutParams(cs, outArgInfo);
} else {
return null;
}
}
});
return result;
// // 执行存储过程
// return jdbcTemplate.execute(execSql,
// (CallableStatementCallback<Object>) cs -> {
// // 设置入参的参数值
// for (String inArgName : params.keySet()) {
// cs.setObject(inArgName, params.get(inArgName));
// }
//
// // 注册输出参数
// if (MapUtils.isNotEmpty(outArgInfo)) {
// // 注册输出参数
// for (String outArgName : outArgInfo.keySet()) {
// cs.registerOutParameter(outArgName, (Integer) outArgInfo.get(outArgName));
// }
// }
//
// // 判断是否有返回值
// if (StringUtils.isNotEmpty(resultType)) {
// // 如果是游标返回
// if (RESULT_TYPE_CURSOR.equals(resultType)) {
// return queryForList(cs);
// } else if (RESULT_TYPE_OUT_PUT.equals(resultType)) { //如果是output返回
// return buildOutParams(cs, outArgInfo);
// }
// } else {
// cs.execute();
// }
// return null;
// });
}
/**
* 构建调用存储过程SQL语句
*
* @param procName 存储过程名
* @param params 入参列表
* @param outArgInfo 出参列表
* @return 存储过程调用SQL语句
*/
private static String buildCallProcSQL(String procName, Map<String, Object> params, Map<String, Object> outArgInfo) {
int paramsSize;
if (MapUtils.isEmpty(params)) {
paramsSize = 0;
} else {
paramsSize = params.size();
}
// 拼接存储过程参数占位符
int placeholderLen = paramsSize + (outArgInfo != null ? outArgInfo.size() : 0);
String procPlaceHolder = genProcPlaceHolder(placeholderLen);
// 拼接要执行的存储过程
return String.format("exec %s %s", procName, procPlaceHolder);
}
/**
* 检查输出参数的合法性
*
* @param outArgInfo 输出参数
*/
private static void checkOutArgs(Map<String, Object> outArgInfo) {
// 出参校验
if (MapUtils.isEmpty(outArgInfo)) {
return;
}
// 校验输出参数类型必须为 SQLType
Collection<Object> values = outArgInfo.values();
values.forEach(p -> {
if (!(p instanceof Integer) || !(isIncludeTypes(Integer.parseInt(p.toString())))) {
throw new RuntimeException("类型代码必须在【java.sql.Types】类中已定义");
}
});
}
/**
* 调用存储过程后的参数返回
*
* @param cs CallableStatement对象
* @param outArgInfo 出参map
* @return 存过执行返回的数据
*/
private static Map<String, Object> buildOutParams(CallableStatement cs, Map<String, Object> outArgInfo) throws SQLException {
Map<String, Object> resultMap = MapUtils.newHashMap();
cs.execute();
for (String key : outArgInfo.keySet()) {
// 根据key获取值
Object object;
object = cs.getObject(key);
// 结果放入并返回
resultMap.put(key, object);
}
return resultMap;
}
/**
* 游标数据返回
* 注:此时在使用cs.execute(),后调用cs.getResultSet()会出现部分存储过程返回的游标取不到的情况,具体原因未知
* 改为使用 cs.executeQuery()方法,存过里的更新也能正常执行,结果集也能拿到
*
* @param cs CallableStatement对象
* @return 游标数据集返回:参数形式List<Map<String, Object>>
* @throws SQLException 异常
*/
private static List queryForList(CallableStatement cs) throws SQLException {
// 执行存储过程,获得结果集
ResultSet rs = cs.executeQuery();
if (null != rs) {
// 组装返回结果集
return convertResultSetToList(rs);
}
return null;
}
/**
* 按指定个数生成存储过程占位符
*
* @param argCount 参数个数
* @return 占位符字符串, eg: ?,?,?,...
*/
private static String genProcPlaceHolder(int argCount) {
List<String> placeHolderList = ListUtils.newArrayList();
for (int i = 0; i < argCount; i++) {
placeHolderList.add("?");
}
return String.join(",", placeHolderList);
}
/**
* 组装返回数据
*
* @param rs ResultSet对象
* @return 集合数据
* @throws SQLException 异常
*/
private static List<Map<String, Object>> convertResultSetToList(ResultSet rs) throws SQLException {
if (rs == null) {
return null;
}
// 封装到 List
List<Map<String, Object>> resultList = ListUtils.newArrayList();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
while (rs.next()) {
// 转换每行的返回值到Map中
Map<String, Object> rowMap = MapUtils.newHashMap();
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getColumnName(i);
rowMap.put(columnName, rs.getString(columnName));
}
resultList.add(rowMap);
}
// 关闭
rs.close();
return resultList;
}
/**
* 检查传入类型代码是否合法
*
* @param key 类型代码
* @return 如果合法则返回 true,否则返回 false
*/
private static boolean isIncludeTypes(int key) {
List<Integer> typeCodeList = ListUtils.newArrayList();
Field[] declaredFields = Types.class.getDeclaredFields();
// 判断Types中定义的类型是否包含传入的参数类型
for (Field declaredField : declaredFields) {
try {
typeCodeList.add(declaredField.getInt(Types.class));
} catch (IllegalAccessException e) {
throw new RuntimeException("类型检查失败!");
}
}
return typeCodeList.contains(key);
}
}
