package com.java110.db.dao.impl;
|
|
import com.java110.utils.util.StringUtil;
|
import com.java110.core.base.dao.BaseServiceDao;
|
import com.java110.entity.order.ServiceBusiness;
|
import com.java110.entity.service.ServiceSql;
|
import com.java110.db.dao.IQueryServiceDAO;
|
import org.slf4j.Logger;
|
import org.slf4j.LoggerFactory;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Transactional;
|
|
import java.sql.Connection;
|
import java.sql.PreparedStatement;
|
import java.sql.ResultSet;
|
import java.sql.ResultSetMetaData;
|
import java.sql.SQLException;
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
|
/**
|
* Created by wuxw on 2018/4/20.
|
*/
|
@Service("queryServiceDAOImpl")
|
@Transactional
|
public class QueryServiceDAOImpl extends BaseServiceDao implements IQueryServiceDAO {
|
|
private final static Logger logger = LoggerFactory.getLogger(QueryServiceDAOImpl.class);
|
|
/**
|
* 防止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.getColumnLabel(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();
|
if (ps != null) {
|
ps.close();
|
}
|
if (rs != null) {
|
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");
|
}
|
|
|
/**
|
* 查询服务业务信息
|
*
|
* @return
|
*/
|
public List<ServiceBusiness> qureyServiceBusiness() {
|
return sqlSessionTemplate.selectList("queryServiceDAOImpl.queryServiceBusiness");
|
}
|
}
|
