使用Spring的JdbcTemplate簡單實現(xiàn)
支持入?yún)? 支持游標返回
調用方式
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的數(shù)據(jù)源(單體模式單一數(shù)據(jù)源不需要設置jdbcTemplate的數(shù)據(jù)源, 使用默認數(shù)據(jù)源即可)
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 以存儲過程方法聲明的參數(shù)進行返回,eg: @p_out_amt decimal(18,2) OutPut
*/
private static final String RESULT_TYPE_OUT_PUT = "output";
/**
* 調用存儲過程,無返回值 無入?yún)? *
* @param jdbcTemplate jdbcTemplate對象
* @param procName 存儲過程名稱
*/
public static void execute(JdbcTemplate jdbcTemplate, String procName) {
execute(jdbcTemplate, procName, null, null, null);
}
/**
* 調用存儲過程,有入?yún)?無返回值
*
* @param jdbcTemplate jdbcTemplate對象
* @param procName 存儲過程名
* @param params 入?yún)? */
public static void execute(JdbcTemplate jdbcTemplate, String procName, Map<String, Object> params) {
execute(jdbcTemplate, procName, params, null, null);
}
/**
* 調用存儲過程,無入?yún)?有游標返回值
*
* @param jdbcTemplate jdbcTemplate對象
* @param procName 存儲過程名
* @return 返回結果
*/
public static Object queryForList(JdbcTemplate jdbcTemplate, String procName) {
return execute(jdbcTemplate, procName, null, RESULT_TYPE_CURSOR, null);
}
/**
* 調用存儲過程,有入?yún)?有游標返回值
*
* @param jdbcTemplate jdbcTemplate對象
* @param procName 存儲過程名
* @param params 入?yún)? * @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數(shù)據(jù)源)
* eg: jdbcTemplate.setDataSource(routingDataSource.getDataSource("zy_data"));
* @param procName 存儲過程名
* @param params 入?yún)? * @param resultType 存儲過程返回類型(調用的存過無返回值時,傳null)
* @param outArgInfo 存儲過程返回值的參數(shù)類型(調用的存過無返回值時傳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) {
// 檢查輸出參數(shù)的合法性
checkOutArgs(outArgInfo);
// 構建存儲過程調用SQL語句
String execSql = buildCallProcSQL(procName, params, outArgInfo);
Object result = jdbcTemplate.execute((Connection con) -> {
CallableStatement cs = con.prepareCall(execSql);
// 設置入?yún)⒌膮?shù)值
if(MapUtils.isNotEmpty(params)){
for (String inArgName : params.keySet()) {
cs.setObject(inArgName, params.get(inArgName));
}
}
// 注冊輸出參數(shù)
if (MapUtils.isNotEmpty(outArgInfo)) {
// 注冊輸出參數(shù)
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;
// // 執(zhí)行存儲過程
// return jdbcTemplate.execute(execSql,
// (CallableStatementCallback<Object>) cs -> {
// // 設置入?yún)⒌膮?shù)值
// for (String inArgName : params.keySet()) {
// cs.setObject(inArgName, params.get(inArgName));
// }
//
// // 注冊輸出參數(shù)
// if (MapUtils.isNotEmpty(outArgInfo)) {
// // 注冊輸出參數(shù)
// 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 入?yún)⒘斜? * @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();
}
// 拼接存儲過程參數(shù)占位符
int placeholderLen = paramsSize + (outArgInfo != null ? outArgInfo.size() : 0);
String procPlaceHolder = genProcPlaceHolder(placeholderLen);
// 拼接要執(zhí)行的存儲過程
return String.format("exec %s %s", procName, procPlaceHolder);
}
/**
* 檢查輸出參數(shù)的合法性
*
* @param outArgInfo 輸出參數(shù)
*/
private static void checkOutArgs(Map<String, Object> outArgInfo) {
// 出參校驗
if (MapUtils.isEmpty(outArgInfo)) {
return;
}
// 校驗輸出參數(shù)類型必須為 SQLType
Collection<Object> values = outArgInfo.values();
values.forEach(p -> {
if (!(p instanceof Integer) || !(isIncludeTypes(Integer.parseInt(p.toString())))) {
throw new RuntimeException("類型代碼必須在【java.sql.Types】類中已定義");
}
});
}
/**
* 調用存儲過程后的參數(shù)返回
*
* @param cs CallableStatement對象
* @param outArgInfo 出參map
* @return 存過執(zhí)行返回的數(shù)據(jù)
*/
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()) {
// 根據(jù)key獲取值
Object object;
object = cs.getObject(key);
// 結果放入并返回
resultMap.put(key, object);
}
return resultMap;
}
/**
* 游標數(shù)據(jù)返回
* 注:此時在使用cs.execute(),后調用cs.getResultSet()會出現(xiàn)部分存儲過程返回的游標取不到的情況,具體原因未知
* 改為使用 cs.executeQuery()方法,存過里的更新也能正常執(zhí)行,結果集也能拿到
*
* @param cs CallableStatement對象
* @return 游標數(shù)據(jù)集返回:參數(shù)形式List<Map<String, Object>>
* @throws SQLException 異常
*/
private static List queryForList(CallableStatement cs) throws SQLException {
// 執(zhí)行存儲過程,獲得結果集
ResultSet rs = cs.executeQuery();
if (null != rs) {
// 組裝返回結果集
return convertResultSetToList(rs);
}
return null;
}
/**
* 按指定個數(shù)生成存儲過程占位符
*
* @param argCount 參數(shù)個數(shù)
* @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);
}
/**
* 組裝返回數(shù)據(jù)
*
* @param rs ResultSet對象
* @return 集合數(shù)據(jù)
* @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中定義的類型是否包含傳入的參數(shù)類型
for (Field declaredField : declaredFields) {
try {
typeCodeList.add(declaredField.getInt(Types.class));
} catch (IllegalAccessException e) {
throw new RuntimeException("類型檢查失敗!");
}
}
return typeCodeList.contains(key);
}
}
