package com.java110.service.dao.impl;
|
|
import com.java110.common.util.StringUtil;
|
import com.java110.core.base.dao.BaseServiceDao;
|
import com.java110.entity.service.ServiceSql;
|
import com.java110.service.dao.IQueryServiceDAO;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Transactional;
|
|
import java.sql.*;
|
import java.util.*;
|
|
/**
|
* Created by wuxw on 2018/4/20.
|
*/
|
@Service("queryServiceDAOImpl")
|
@Transactional
|
public class QueryServiceDAOImpl extends BaseServiceDao implements IQueryServiceDAO {
|
|
/**
|
* 防止sql注入 改造成直接用prepareStatement 预处理sql
|
* @param sql
|
* @param params
|
* @return
|
*/
|
@Override
|
public List<Map<String,Object>> executeSql(String sql,Object[] params) {
|
logger.debug("----【queryServiceDAOImpl.executeSql】入参 : "+sql+" params= "+params);
|
Connection conn = null;
|
ResultSet rs = null;
|
PreparedStatement ps = null;
|
List<Map<String,Object>> mapList = new ArrayList<Map<String,Object>>();
|
try {
|
conn = sqlSessionTemplate.getConnection();
|
ps = conn.prepareStatement(sql);
|
if(params != null){
|
for(int i = 0 ; i < params.length ; i++){
|
ps.setObject(i+1, params[i]);
|
}
|
}
|
rs = ps.executeQuery();
|
//精髓的地方就在这里，类ResultSet有getMetaData()会返回数据的列和对应的值的信息，然后我们将列名和对应的值作为map的键值存入map对象之中...
|
ResultSetMetaData rsmd = rs.getMetaData();
|
while(rs.next()){
|
Map<String,Object> map = new HashMap<String,Object>();
|
for(int i = 0 ; i < rsmd.getColumnCount() ; i++){
|
String col_name = rsmd.getColumnName(i+1);
|
Object col_value = rs.getObject(col_name);
|
if(col_value == null){
|
col_value = "";
|
}
|
map.put(col_name, col_value);
|
}
|
mapList.add(map);
|
}
|
return mapList;
|
} catch (SQLException e) {
|
logger.error("执行sql异常：" + sql +params,e);
|
return null;
|
}finally{
|
try {
|
//conn.close();
|
ps.close();
|
rs.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
//return sqlSessionTemplate.selectList("queryServiceDAOImpl.executeSql",sql);
|
}
|
|
/**
|
* 防止sql注入 改造成直接用prepareStatement 预处理sql
|
* @param sql
|
* @param params
|
* @return
|
*/
|
public int updateSql(String sql,Object[] params){
|
logger.debug("----【queryServiceDAOImpl.updateSql】入参 : "+sql+" params= "+params);
|
Connection conn = null;
|
PreparedStatement ps = null;
|
try {
|
conn = sqlSessionTemplate.getConnection();
|
ps = conn.prepareStatement(sql);
|
if(params != null){
|
for(int i = 0 ; i < params.length ; i++){
|
ps.setObject(i+1, params[i]);
|
}
|
}
|
return ps.executeUpdate();
|
//精髓的地方就在这里，类ResultSet有getMetaData()会返回数据的列和对应的值的信息，然后我们将列名和对应的值作为map的键值存入map对象之中...
|
} catch (SQLException e) {
|
logger.error("执行sql异常：" + sql +params,e);
|
return 0;
|
}finally{
|
try {
|
//conn.close();
|
ps.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
//return sqlSessionTemplate.update("queryServiceDAOImpl.updateSql",sql);
|
}
|
|
@Override
|
public String executeProc(Map<String,Object> paramsInfo) {
|
String paramsInfoStr = "";
|
for (String key : paramsInfo.keySet()){
|
if("procName".equals(key)){
|
paramsInfoStr += (paramsInfo.get("procName") + "(");
|
}else{
|
if(StringUtil.isNullOrNone(paramsInfo.get(key))){
|
paramsInfoStr += "'',";
|
}else{
|
paramsInfoStr += "'"+paramsInfo.get(key)+"',";
|
}
|
}
|
}
|
|
paramsInfo.put("paramsInfo",paramsInfoStr);
|
|
sqlSessionTemplate.selectOne("queryServiceDAOImpl.executeProc",paramsInfo);
|
|
return paramsInfo.get("resMsg") ==null ?"" :paramsInfo.get("resMsg").toString();
|
}
|
|
@Override
|
public String updateProc(Map<String,Object> paramsInfo) {
|
String paramsInfoStr = "";
|
for (String key : paramsInfo.keySet()){
|
if("procName".equals(key)){
|
paramsInfoStr += (paramsInfo.get("procName") + "(");
|
}else{
|
if(StringUtil.isNullOrNone(paramsInfo.get(key))){
|
paramsInfoStr += "'',";
|
}else{
|
paramsInfoStr += "'"+paramsInfo.get(key)+"',";
|
}
|
}
|
}
|
|
paramsInfo.put("paramsInfo",paramsInfoStr);
|
|
sqlSessionTemplate.update("queryServiceDAOImpl.updateProc",paramsInfo);
|
|
return paramsInfo.get("resMsg") ==null ?"" :paramsInfo.get("resMsg").toString();
|
}
|
|
/**
|
* 查询 ServiceSql
|
* @return
|
*/
|
@Override
|
public List<ServiceSql> qureyServiceSqlAll() {
|
return sqlSessionTemplate.selectList("queryServiceDAOImpl.qureyServiceSqlAll");
|
}
|
}
|
