package com.code2roc.fastface.db;
|
|
|
import org.slf4j.Logger;
|
import org.slf4j.LoggerFactory;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.dao.DataAccessException;
|
import org.springframework.dao.EmptyResultDataAccessException;
|
import org.springframework.jdbc.core.JdbcTemplate;
|
import org.springframework.jdbc.core.ResultSetExtractor;
|
import org.springframework.stereotype.Component;
|
|
import java.sql.ResultSet;
|
import java.sql.ResultSetMetaData;
|
import java.sql.SQLException;
|
import java.util.ArrayList;
|
import java.util.LinkedHashMap;
|
import java.util.List;
|
import java.util.Map;
|
import java.util.regex.Matcher;
|
import java.util.regex.Pattern;
|
|
@Component
|
public class JDBCDTO {
|
private Logger logger = LoggerFactory.getLogger(JDBCDTO.class);
|
@Autowired
|
private MasterJdbcTemplate jdbcTemplate;
|
|
public List<LinkedHashMap<String, Object>> findListByParam(String sqlText, Map<String, Object> map) {
|
|
List<LinkedHashMap<String, Object>> result = new ArrayList<>();
|
List<Object> paramList = new ArrayList<>();
|
//解析sqlText中的占位符#{xxxx}
|
String regex = "\\#\\{(?<RegxName>[\\w.]*)\\}";
|
String sqlTextCopy = sqlText;
|
Pattern pattern = Pattern.compile(regex);
|
|
Matcher matcher = pattern.matcher(sqlTextCopy);
|
while (matcher.find()) {
|
String paramNameSymbol = matcher.group(0);
|
sqlText = sqlText.replace(paramNameSymbol, " ? ");
|
}
|
logger.debug("【sqlText】：" + sqlText);
|
|
//参数赋值
|
matcher = pattern.matcher(sqlTextCopy);
|
while (matcher.find()) {
|
String paramNameSymbol = matcher.group(0);
|
String paramName = paramNameSymbol.replace("#", "").replace("{", "").replace("}", "");
|
Object paramValue = map.get(paramName);
|
logger.debug("【paramName】：" + paramName);
|
logger.debug("【paramValue】：" + paramValue);
|
paramList.add(paramValue);
|
}
|
|
jdbcTemplate.query(sqlText, paramList.toArray(), new ResultSetExtractor<Object>() {
|
@Override
|
public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
|
try {
|
ResultSetMetaData rsMetaData = rs.getMetaData();
|
while (rs.next()) {
|
LinkedHashMap<String, Object> resultitem = new LinkedHashMap<>();
|
for (int i = 1; i <= rsMetaData.getColumnCount(); i++) {
|
String columnName = "";
|
columnName = rsMetaData.getColumnName(i);
|
Object columnValue = rs.getObject(columnName);
|
resultitem.put(columnName, columnValue);
|
}
|
result.add(resultitem);
|
}
|
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
return null;
|
}
|
}
|
});
|
return result;
|
}
|
|
public int executeSQL(String sqlText, Map<String, Object> map){
|
List<Object> paramList = new ArrayList<>();
|
//解析sqlText中的占位符#{xxxx}
|
String regex = "\\#\\{(?<RegxName>[\\w.]*)\\}";
|
String sqlTextCopy = sqlText;
|
Pattern pattern = Pattern.compile(regex);
|
|
Matcher matcher = pattern.matcher(sqlTextCopy);
|
while (matcher.find()) {
|
String paramNameSymbol = matcher.group(0);
|
sqlText = sqlText.replace(paramNameSymbol, " ? ");
|
}
|
|
logger.debug("【sqlText】：" + sqlText);
|
|
//参数赋值
|
matcher = pattern.matcher(sqlTextCopy);
|
while (matcher.find()) {
|
String paramNameSymbol = matcher.group(0);
|
String paramName = paramNameSymbol.replace("#", "").replace("{", "").replace("}", "");
|
Object paramValue = map.get(paramName);
|
logger.debug("【paramName】：" + paramName);
|
logger.debug("【paramValue】：" + paramValue);
|
paramList.add(paramValue);
|
}
|
|
return jdbcTemplate.update(sqlText,paramList.toArray());
|
}
|
|
public Object executeScar(String sqlText, Map<String, Object> map){
|
List<Object> paramList = new ArrayList<>();
|
//解析sqlText中的占位符#{xxxx}
|
String regex = "\\#\\{(?<RegxName>[\\w.]*)\\}";
|
String sqlTextCopy = sqlText;
|
Pattern pattern = Pattern.compile(regex);
|
|
Matcher matcher = pattern.matcher(sqlTextCopy);
|
while (matcher.find()) {
|
String paramNameSymbol = matcher.group(0);
|
sqlText = sqlText.replace(paramNameSymbol, " ? ");
|
}
|
|
logger.debug("【sqlText】：" + sqlText);
|
|
//参数赋值
|
matcher = pattern.matcher(sqlTextCopy);
|
while (matcher.find()) {
|
String paramNameSymbol = matcher.group(0);
|
String paramName = paramNameSymbol.replace("#", "").replace("{", "").replace("}", "");
|
Object paramValue = map.get(paramName);
|
logger.debug("【paramName】：" + paramName);
|
logger.debug("【paramValue】：" + paramValue);
|
paramList.add(paramValue);
|
}
|
|
Object result;
|
try {
|
result = jdbcTemplate.queryForObject(sqlText, Object.class, paramList.toArray());
|
} catch (EmptyResultDataAccessException e) {
|
result = null;
|
}
|
return result;
|
}
|
|
public void batchExecuteSQL(String sqlText, List<Map<String, Object>> mapList){
|
|
List<Object[]> paramList = new ArrayList<>();
|
//解析sqlText中的占位符#{xxxx}
|
String regex = "\\#\\{(?<RegxName>[\\w.]*)\\}";
|
String sqlTextCopy = sqlText;
|
Pattern pattern = Pattern.compile(regex);
|
|
Matcher matcher = pattern.matcher(sqlTextCopy);
|
while (matcher.find()) {
|
String paramNameSymbol = matcher.group(0);
|
sqlText = sqlText.replace(paramNameSymbol, " ? ");
|
}
|
|
logger.debug("【sqlText】：" + sqlText);
|
|
//参数赋值
|
for (Map<String, Object> map:mapList) {
|
matcher = pattern.matcher(sqlTextCopy);
|
List<Object> singleParamList = new ArrayList<>();
|
while (matcher.find()) {
|
String paramNameSymbol = matcher.group(0);
|
String paramName = paramNameSymbol.replace("#", "").replace("{", "").replace("}", "");
|
Object paramValue = map.get(paramName);
|
logger.debug("【paramName】：" + paramName);
|
logger.debug("【paramValue】：" + paramValue);
|
singleParamList.add(paramValue);
|
}
|
paramList.add(singleParamList.toArray());
|
}
|
jdbcTemplate.batchUpdate(sqlText,paramList);
|
}
|
}
|
