spring JdbcTemplate 在项目中的浅层（5个使用场景）封装 ...-白红宇

导读：

主要从4个方面来阐述，1：背景；2：思路；3：代码实现；4：使用

一：封装背景，

在做项目的时候，用的JPA ，有些复杂查询，比如报表用原生的JdbcTemplate ，很不方便，传参也不方便，如果参数多；需要增加分页功能，以及结果集自动转对像等5个常用功能，见第4节

下面两个图是开源测试管理软件 itest 的统计功能，因为SQL复杂，有些有200行，所以才有后面的 JdbcTemplateWrapper；可以在这体验这些报表（点测试，然后选择一个项目，然后点度量分析），速度还不错，https://itest.work/rsf/site/itest/product/index.html

上面这图，SQL 就有200行

二：封装实现思路

（1）实现spring RowMapper 接口，直接把jdbc 结果集转 JAVA 对像(用的反射，访问量大的业务不推荐用这方式)

（2）不通过 RowMapper 接口, 把JdbcTemplate 返回的List<Map<String,Object>> 原生JDBC结果集中，字段名，也就是MAP中的KEY，转换为驼峰规则的JAVA对属性名,方便前端组件使用，不再中间转为VO 或实体类对像后，再返前端;比传统查询（多了一个LIST的遍历，基本对性能影响不大）

（3）封装分页

（4）在 JdbcTemplateWrapper 中包装 NamedParameterJdbcTemplate 解决友好传参的问题,再封几个常用的查询方法，可变长参数，或是以MAP形式传参数 key 为SQL中的参数名占位符

三：代码实现

两个类，ObjectRowMapper 和 JdbcTemplateWrapper

ObjectRowMapper 代码如下：

package cn.com.mypm.framework.jdbc;import java.lang.reflect.Field;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.util.HashMap;import java.util.Map;import java.util.concurrent.ConcurrentHashMap;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;import org.springframework.jdbc.core.RowMapper;import org.springframework.util.StringUtils;import cn.com.mypm.common.util.CalendaUtilities;import cn.com.mypm.framework.common.config.PropertiesBean;/** ** 标题: RowMapper 接口实现
* 业务描述:完成原生结果集到JAVA对像的转换
* 公司:itest.work
* 版权:itest 2018
* @author itest andy* @date 2018年6月8日* @version V1.0 */@SuppressWarnings("rawtypes")public class ObjectRowMapper implements RowMapper {        private Class
     objectClass;    private String[] columnNames = null;    private Field[] fields ;    ///缓存 当前结果集字对段和当前class的 Field的对应关系，    private Map
    
      currQueryFieldMap ;    //缓存当前结果集，字段和 JAVA属性名的对应关系 ，按脱峰规则做的转换    private Map
     
       fieldClassMap ;    private Boolean isConvertSwitch  = null;    // MYSQL 是否区分大小写的标记，要是区转，要把结果集中，字段名，大写转为小写    private String mysqlLowerCaseTableNames = null;    private static Log logger = LogFactory.getLog(ObjectRowMapper.class);    //缓存某个class 已处理过的字段映射到属性名的关系，避免同一个类每次重新处理，第一个KEY 为类名    private static Map
      
       > dbColumnClassFieldMap = new ConcurrentHashMap
       
        >();        public ObjectRowMapper(Class
         objectClass) {        this.objectClass = objectClass;        fields = objectClass.getDeclaredFields();    }    public void clean(){        if(currQueryFieldMap!=null){            currQueryFieldMap.clear();            currQueryFieldMap = null;        }        if(fieldClassMap!=null){            fieldClassMap.clear();            fieldClassMap = null;        }        if(fields!=null){            fields = null;        }        if(columnNames!=null){            columnNames = null;        }    }        /**     * 该方法自动将数据库字段对应到Object中相应字段 要求：     * 字段名严格为驼峰形式 == 数据库字段名去掉下划线转为驼峰形式     * 如user_name 转为userName ，如数据库字段名，无下划线     * 就只能把首字母变为大小写后的 set ,get     */    @Override    public Object mapRow(ResultSet rs, int rowNum) throws SQLException {        Object targetObject = null;        try {            targetObject = objectClass.newInstance();        } catch (InstantiationException | IllegalAccessException e) {            logger.error(e.getMessage(), e);        }        if (columnNames == null) {            ResultSetMetaData rsmd = rs.getMetaData();            int columnCount = rsmd.getColumnCount();            columnNames = new String[columnCount];            for (int i = 0; i < columnCount; i++) {                columnNames[i] = rsmd.getColumnLabel(i + 1);            }        }        isConvertSwitch = true;        if(mysqlLowerCaseTableNames == null){            String lowerCaseNames = PropertiesBean.getInstance().getProperty("conf.mysql.lowerCaseNames");            if(lowerCaseNames==null){                mysqlLowerCaseTableNames = "yes";            }else{                mysqlLowerCaseTableNames = "no";            }        }        if(currQueryFieldMap==null){            currQueryFieldMap = new HashMap
        
         (columnNames.length);            for (String columnName : columnNames) {                for (Field field : fields) {                    if(isConvertSwitch==null){                        if (field.getName().equals(                                convertColumnNameToFieldName(columnName))) {                            currQueryFieldMap.put(columnName, field);                            break;                        }                    }else{                        if(isConvertSwitch){                            if(targetObject instanceof  CustomRowMapper&&(!((CustomRowMapper)targetObject).isConvert())){                                if (field.getName().equals(columnName)) {                                    currQueryFieldMap.put(columnName, field);                                    break;                                }                            }else{                                if (field.getName().equals(                                        convertColumnNameToFieldName(columnName))) {                                    currQueryFieldMap.put(columnName, field);                                    break;                                }                            }                        }                    }                }            }        }        for (String columnName : columnNames) {            Field field = currQueryFieldMap.get(columnName);            if(field==null){                if(logger.isDebugEnabled()){                    logger.debug(objectClass.getName() +"is  not property match  db columnName:"+columnName );                }                continue;            }            Object value = rs.getObject(columnName);            if (value == null) {                continue;            }            boolean accessFlag = field.isAccessible();            if (!accessFlag) {                field.setAccessible(true);            }            if(fieldClassMap==null){                fieldClassMap = new HashMap
         
          (columnNames.length); } if(fieldClassMap.get(columnName)==null){ fieldClassMap.put(columnName, getFieldClaszName(field)); } setFieldValue(targetObject, field, rs, columnName,fieldClassMap.get(columnName)); // 恢复相应field的权限 if (!accessFlag) { field.setAccessible(accessFlag); } } return targetObject; } public String convertColumnNameToFieldName(String columnName) { Map
          
            fieldMap = dbColumnClassFieldMap.get(objectClass .getName()); boolean emptyFlg = false; if (fieldMap == null) { fieldMap = new HashMap
           
            (); emptyFlg = true; } String classFieldName = fieldMap.get(columnName); if (classFieldName != null) { return classFieldName; } String columnNameKey = columnName; //if ("oracle".equals(dbType)||("mysql".equals(dbType)&&"no".equals(mysqlLowerCaseTableNames))) { columnName = columnName.toLowerCase(); //} StringBuffer buf = new StringBuffer(); int i = 0; while ((i = columnName.indexOf('_')) > 0) { buf.append(columnName.substring(0, i)); columnName = StringUtils.capitalize(columnName.substring(i + 1)); } buf.append(columnName); fieldMap.put(columnNameKey, buf.toString()); if (emptyFlg) { dbColumnClassFieldMap.put(objectClass.getName(), fieldMap); } return fieldMap.get(columnNameKey); } /** * 根据类型对具体对象属性赋值 */ public static void setFieldValue(Object targetObj, Field field, ResultSet rs, String columnLabel,String fieldClass) { try { if ("String".equals(fieldClass)) { field.set(targetObj, rs.getString(columnLabel)); } else if ("Double".equals(fieldClass)) { field.set(targetObj, rs.getDouble(columnLabel)); } else if ("Float".equals(fieldClass)) { field.set(targetObj, rs.getFloat(columnLabel)); } else if ("Integer".equals(fieldClass)) { field.set(targetObj, rs.getInt(columnLabel)); } else if ("Long".equals(fieldClass)) { field.set(targetObj, rs.getLong(columnLabel)); } else if ("BigDecimal".equals(fieldClass)) { field.set(targetObj, rs.getBigDecimal(columnLabel)); } else if ("Date".equals(fieldClass)) { field.set(targetObj, rs.getDate(columnLabel)); } else if ("Short".equals(fieldClass)) { field.set(targetObj, rs.getShort(columnLabel)); } else if ("Boolean".equals(fieldClass)) { field.set(targetObj, rs.getBoolean(columnLabel)); } else if ("Byte".equals(fieldClass)) { field.set(targetObj, rs.getByte(columnLabel)); } else if ("Timestamp".equals(fieldClass)) { field.set(targetObj, rs.getTimestamp(columnLabel)); } else if("BigDecimal".equals(fieldClass)) { field.set(targetObj, rs.getBigDecimal(columnLabel)); }else { //这里没有实现，如有特殊需要处理的在这里实现 } } catch (IllegalArgumentException e) { logger.error(e.getMessage(), e); } catch (IllegalAccessException e) { logger.error(e.getMessage(), e); } catch (SQLException e) { logger.error(e.getMessage(), e); } } private static String getFieldClaszName(Field field) { String elemType = field.getType().toString(); if ("class java.lang.String".equals(elemType) || elemType.indexOf("char") != -1 || elemType.indexOf("Character") != -1) { return "String"; } else if (elemType.indexOf("double") != -1 || elemType.indexOf("Double") != -1) { return "Double"; } else if (elemType.indexOf("float") != -1 || elemType.indexOf("Float") != -1) { return "Float"; } else if (elemType.indexOf("int") != -1 || elemType.indexOf("Integer") != -1||elemType.indexOf("BigInteger") != -1) { return "Integer"; } else if (elemType.indexOf("long") != -1 || elemType.indexOf("Long") != -1) { return "Long"; } else if (elemType.indexOf("BigDecimal") != -1) { return "BigDecimal"; } else if (elemType.indexOf("Date") != -1) { return "Date"; } else if (elemType.indexOf("short") != -1 || elemType.indexOf("Short") != -1) { return "Short"; } else if (elemType.indexOf("boolean") != -1 || elemType.indexOf("Boolean") != -1) { return "Boolean"; } else if (elemType.indexOf("byte") != -1 || elemType.indexOf("Byte") != -1) { return "Byte"; } else if (elemType.indexOf("Timestamp") != -1) { return "Timestamp"; } return "String"; }

JdbcTemplateWrapper

package cn.com.mypm.framework.jdbc;import java.util.ArrayList;import java.util.HashMap;import java.util.Iterator;import java.util.List;import java.util.Map;import java.util.Map.Entry;import java.util.Set;import java.util.regex.Matcher;import java.util.regex.Pattern;import org.apache.commons.lang3.StringUtils;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.RowMapper;import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;import org.springframework.util.Assert;import cn.com.mypm.common.dto.PageModel;import cn.com.mypm.framework.exception.DataBaseException;/** * * 标题:  JdbcTemplate 包装器
* 业务描述:实现分页，原生结果集转对像，原生结果集字段名转脱峰java 属性名
* 公司:itest.work
* 版权:itest 2018
* @author itest andy * @date 2018年6月8日* @version V1.0 */public class JdbcTemplateWrapper  {    private static Log logger = LogFactory.getLog(JdbcTemplateWrapper.class);        private JdbcTemplate jdbcTemplate;        private String dbType = "mysql";    private String showSql = "false";    protected NamedParameterJdbcTemplate namedParameterJdbcTemplate;        private String mysqlLowerCaseTableNames = null;    public JdbcTemplateWrapper() {        super();    }    public JdbcTemplateWrapper(JdbcTemplate jdbcTemplate) {        super();        this.jdbcTemplate = jdbcTemplate;        this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);    }    public JdbcTemplateWrapper(JdbcTemplate jdbcTemplate, String dbType, String showSql) {        super();        this.jdbcTemplate = jdbcTemplate;        this.dbType = dbType;        this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);        this.showSql = showSql;    }    public JdbcTemplate getJdbcTemplate() {        return jdbcTemplate;    }    /**     * 查询所有匹配的列表     *      * @param sql     *            查询sql     * @param className     *            对象类型     * @param args     *            查询参数     * @return     * @author itest andy      */    @SuppressWarnings({ "rawtypes", "unchecked" })    public List queryAllMatchList(String sql, Class clasz, Object[] args) {        Assert.notNull(clasz, "clasz must not be null");        List dataList = null;        RowMapper rowMapper = new ObjectRowMapper(clasz);        dataList = getJdbcTemplate().query(sql, args, rowMapper);        ((ObjectRowMapper) rowMapper).clean();        rowMapper = null;        return dataList;    }    /**     * 查询所有匹配的列表     *      * @param sql     *            查询sql     * @param className     *            对象类型     * @param args     *            查询参数     * @return     * @author itest andy      */    @SuppressWarnings({ "rawtypes", "unchecked" })    public List queryAllMatchListWithFreePra(String sql, Class clasz, Object... args) {        Assert.notNull(clasz, "clasz must not be null");        List dataList = null;        RowMapper rowMapper = new ObjectRowMapper(clasz);        dataList = getJdbcTemplate().query(sql, args, rowMapper);        ((ObjectRowMapper) rowMapper).clean();        rowMapper = null;        return dataList;    }    /**     * 把list map 原生JDBC结果集中，字段名，也就是MAP中的KEY，转换为驼峰规则的JAVA对属性名     *      * @param resultList ：JDBC 结果集     * @return    把MAP中的KEY转换为转换为驼峰规则的JAVA对属性名的LIST
       <驼峰规则的java对属性名形式的key,object>
        >     * @author itest andy      */    public void converDbColumnName2ObjectPropName(List
        
         
          > resultList) {                if(resultList!=null&&!resultList.isEmpty()) {            List
          
           
            > convertList= new ArrayList
            
             
              >(resultList.size()); //用于缓存字段名到属性名的映射，第二条记录时就不再处理字段名到属性名的转换，提升性能 Map
              
                ColumnNamePropNameMap = null; if(resultList.size()>1) { ColumnNamePropNameMap = new HashMap
               
                (); } for(Map
                
                  currMap :resultList) { if(currMap!=null&&!currMap.isEmpty()) { Iterator
                 
                  
                   > currentIt = currMap.entrySet().iterator(); Map tempMap = new HashMap
                   
                    (); convertList.add(tempMap); while(currentIt.hasNext()) { Map.Entry
                    
                      me= currentIt.next(); String dbColumnName = me.getKey(); Object value = me.getValue(); if(resultList.size()>1) { if(ColumnNamePropNameMap.get(dbColumnName)==null) { String currProName = convertColumnName2OFieldName(dbColumnName); tempMap.put(currProName, value); //缓存起来，第二条记录时就不再处理字段名到属性名的转换，提升性能 ColumnNamePropNameMap.put(dbColumnName, currProName); }else { tempMap.put(ColumnNamePropNameMap.get(dbColumnName), value); } }else { tempMap.put(convertColumnName2OFieldName(dbColumnName), value); } } } } resultList.clear(); for(Map
                     
                       currMap:convertList) { resultList.add(currMap); } convertList.clear(); convertList = null; } } public String convertColumnName2OFieldName(String columnName ) { if(mysqlLowerCaseTableNames == null){ String lowerCaseNames = PropertiesBean.getInstance().getProperty("conf.mysql.lowerCaseNames"); if(lowerCaseNames==null){ mysqlLowerCaseTableNames = "yes"; }else{ mysqlLowerCaseTableNames = "no"; } } if ("oracle".equals(dbType)||("mysql".equals(dbType)&&"no".equals(mysqlLowerCaseTableNames))) { columnName = columnName.toLowerCase(); } if("true".equals(mysqlLowerCaseTableNames)) { columnName = columnName.toLowerCase(); } StringBuffer buf = new StringBuffer(); int i = 0; while ((i = columnName.indexOf('_')) > 0) { buf.append(columnName.substring(0, i)); columnName = StringUtils.capitalize(columnName.substring(i + 1)); } buf.append(columnName); return buf.toString(); } /** * 查询所有匹配的列表 * * @param sql * 查询sql * @param className * 对象类型 * @param paramMap * 查询参数 * @return * @author itest andy */ @SuppressWarnings({ "rawtypes", "unchecked" }) public List queryAllMatchListWithParaMap(String sql, Class clasz, Map paramMap) { if (paramMap != null && paramMap.isEmpty()) { paramMap = null; } if ("true".equals(showSql)) { try { logger.info(getSqlFromQueryData(sql, paramMap)); } catch (Exception e) { logger.error(e.getMessage(), e); } } List resultList = null; if (clasz == null) { resultList = namedParameterJdbcTemplate.queryForList(sql, paramMap); } else { RowMapper rowMapper = new ObjectRowMapper(clasz); resultList = namedParameterJdbcTemplate.query(sql, paramMap, rowMapper); ((ObjectRowMapper) rowMapper).clean(); rowMapper = null; } return resultList; } /** * * @param pageModel: * @param className * : 从查询结果集中构建出的类，如为null则pageModel的PageData为List
                      ， * 不为null则pageModel的PageData为List
                       
                         * @param columnNameForCount:查询记录数时的字段名，一般用主键 */ @SuppressWarnings({ "rawtypes", "unchecked" }) public void fillPageModelData(PageModel pageModel, Class className, String columnNameForCount) { if (pageModel.getHqlParamMap() != null && pageModel.getHqlParamMap().isEmpty()) { pageModel.setHqlParamMap(null); } if (pageModel.getTotal() == 0) { int totalRows = this.getResultCountWithValuesMap(pageModel.getQueryHql(), columnNameForCount, pageModel.getHqlParamMap()); pageModel.setTotal(totalRows); } if (pageModel.getTotal() == 0) { pageModel.setRows(new ArrayList()); return; } if (pageModel.getPageNo() > 1) { int pageCount = this.getValidPage(pageModel.getPageNo(), pageModel.getTotal(), pageModel.getPageSize()); if (pageCount < pageModel.getPageNo()) { pageModel.setPageNo(pageCount); } } int startRow = getStartOfPage(pageModel.getPageNo(), pageModel.getPageSize()); String sql = this.buildPageSql(pageModel.getQueryHql(), startRow, pageModel.getPageSize()); if ("true".equals(showSql)) { try { logger.info(getSqlFromQueryData(sql, pageModel.getHqlParamMap())); } catch (Exception e) { logger.error(e.getMessage(), e); } } List dataList = null; if (className == null) { dataList = namedParameterJdbcTemplate.queryForList(sql, pageModel.getHqlParamMap()); } else { RowMapper rowMapper = new ObjectRowMapper(className); dataList = namedParameterJdbcTemplate.query(sql, pageModel.getHqlParamMap(), rowMapper); ((ObjectRowMapper) rowMapper).clean(); rowMapper = null; } pageModel.setRows(dataList); } /** * 多表sql 分页查询,多表连查时，才用这个方法，其他请用commonDao的 SQL分页查询 * * @param sql * @param className * @param paramMap * @param pageNo * @param PageSize * @param columnNameForCount * @return */ @SuppressWarnings({ "rawtypes", "unchecked" }) public List queryAllMatchListWithParaMap(String sql, Class clasz, Map paramMap, int pageNo, int pageSize, String columnNameForCount) { if (paramMap != null && paramMap.isEmpty()) { paramMap = null; } if ("true".equals(showSql)) { try { logger.info(getSqlFromQueryData(sql, paramMap)); } catch (Exception e) { logger.error(e.getMessage(), e); } }// int totalRows = this.getResultCountWithValuesMap(sql,// columnNameForCount, paramMap);// if(pageNo>1){// int pageCount = this.getValidPage(pageNo, totalRows, pageSize);// } List resultList = null; int startRow = getStartOfPage(pageNo, pageSize); if (clasz == null) { resultList = namedParameterJdbcTemplate.queryForList(this.buildPageSql(sql, startRow, pageSize), paramMap); } else { RowMapper rowMapper = new ObjectRowMapper(clasz); resultList = namedParameterJdbcTemplate.query(this.buildPageSql(sql, startRow, pageSize), paramMap, rowMapper); rowMapper = null; } return resultList; } private String buildPageSql(String sql, int startRow, int pageSize) { if ("oracle".equals(this.getDbType())) { return this.buildOraclePageSql(sql, startRow, pageSize); } else if ("mysql".equals(this.getDbType())) { return this.buildMysqlPageSql(sql, startRow, pageSize); } else if ("informix".equals(this.getDbType())) { return this.buildInformixPageSql(sql, startRow, pageSize); } throw new DataBaseException("don't support db type,please confirm db is oracle or mysql or informix"); } private String buildOraclePageSql(String sql, int startRow, int pageSize) { StringBuilder pageSql = new StringBuilder("SELECT * FROM "); pageSql.append(" ( "); pageSql.append(" SELECT pageDataTable.*, ROWNUM RNV "); pageSql.append(" FROM (" + sql + " ) pageDataTable "); pageSql.append(" WHERE ROWNUM <= " + (startRow + pageSize)); pageSql.append(" ) WHERE RNV >= " + (startRow + 1)); return pageSql.toString(); } private String buildMysqlPageSql(String sql, int startRow, int pageSize) { sql = sql + " limit " + startRow + ", " + pageSize; return sql; } private String buildInformixPageSql(String sql, int startRow, int pageSize) { sql = sql.trim(); if (sql.startsWith("select")) { sql = sql.replaceFirst("select", " select skip " + startRow + " first " + pageSize + " "); } else { sql = sql.replaceFirst("SELECT", " select skip " + startRow + " first " + pageSize + " "); } return sql; } private Integer getValidPage(Integer pageNo, int totalRows, Integer pageSize) { if (!isValidPage(pageNo, totalRows, pageSize)) { return getValidPage(--pageNo, totalRows, pageSize); } int pageCount = (totalRows + (pageSize - (totalRows % pageSize == 0 ? pageSize : totalRows % pageSize))) / pageSize; return pageCount; } private static int getStartOfPage(int pageNo, int pageSize) { if(pageNo==0){ pageNo=1; } return (pageNo - 1) * pageSize; } private boolean isValidPage(Integer pageNo, Integer totalRows, Integer pageSize) { if (pageNo == 1) { return true; } int rowStart = (pageNo - 1) * pageSize; int rowEnd = rowStart + pageSize; if (rowEnd > totalRows) { rowEnd = totalRows; } return rowEnd > rowStart; } /** * 查询记录数 * * @param sql * @param columnNameForCount * @param praValuesMap * @return */ // @SuppressWarnings("deprecation") public int getResultCountWithValuesMap(String sql, String columnNameForCount, Map
                        
                          praValuesMap) { if (praValuesMap != null && praValuesMap.isEmpty()) { praValuesMap = null; } String countQuerySql = null; countQuerySql = " select count(*) from ( " + sql + " ) V_TABLE"; return namedParameterJdbcTemplate.queryForInt(countQuerySql, praValuesMap); } public int queryForIntWithpraValuesMap(String countQuerySql, Map
                         
                           praValuesMap) { if (praValuesMap != null && praValuesMap.isEmpty()) { praValuesMap = null; } return namedParameterJdbcTemplate.queryForInt(countQuerySql, praValuesMap); } public int queryForInt(String countQuerySql, Object... args) { return getJdbcTemplate().queryForInt(countQuerySql, args); } public static String getSqlFromQueryData(String sql, Map
                          
                            paramMap) { if (StringUtils.isEmpty(sql)) { return null; } if (paramMap == null) { return sql; } StringBuffer sqlExp = new StringBuffer(sql); Set
                           
                            
                             > set = paramMap.entrySet(); for (Entry
                             
                               entry : set) { int start = sqlExp.indexOf(":" + entry.getKey() + " "); if (start < 0) { continue; } int last = sqlExp.lastIndexOf(":" + entry.getKey() + " "); if (start >= 0 && start == last) { if (entry.getValue() != null) { sqlExp.replace(start, start + entry.getKey().length(), "'" + entry.getValue().toString() + "'"); // sqlExp.replace(start-1, start+entry.getKey().length(), // "'"+entry.getValue().toString()+"'"); } } else { // 处理同一参数多处出现 sqlExp.replace(start, start + entry.getKey().length(), "'" + entry.getValue().toString() + "'"); start = sqlExp.indexOf(":" + entry.getKey()); while (start > 0) { sqlExp.replace(start, start + entry.getKey().length(), "'" + entry.getValue().toString() + "'"); start = sqlExp.indexOf(":" + entry.getKey()); } } } return sqlExp.toString(); } private static String removeSelect(String sql) { int beginPos = sql.indexOf(" from "); if (beginPos < 0) { beginPos = sql.indexOf("from "); } if (beginPos < 0) { beginPos = sql.toLowerCase().indexOf(" from "); } if (beginPos < 0) { beginPos = sql.toLowerCase().indexOf("from "); } return sql.substring(beginPos); } /** * 去除sql的orderby 用于页查果询 * * @param sql * @return */ private static String removeOrders(String sql) { Pattern p = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*", Pattern.CASE_INSENSITIVE); Matcher m = p.matcher(sql); StringBuffer sb = new StringBuffer(); while (m.find()) { m.appendReplacement(sb, ""); } m.appendTail(sb); return sb.toString(); } public String getDbType() { return dbType; } public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() { return namedParameterJdbcTemplate; } public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate); } public String getMysqlLowerCaseTableNames() { return mysqlLowerCaseTableNames; } public void setMysqlLowerCaseTableNames(String mysqlLowerCaseTableNames) { this.mysqlLowerCaseTableNames = mysqlLowerCaseTableNames; } public String isShowSql() { return showSql; } public void setShowSql(String showSql) { this.showSql = showSql; }}

三：使用

数据源和JPA是同一数据源，在同一事务中，用的连接和JPA使用的是同一个连接，在CommonDao 额外注入一个 JdbcTemplateWrapper,当作处理复杂SQL的帮手。

JdbcTemplateWrapper 常用的方法同如下几个：

clasz 把查询结果要转为的类对像，内部再构造 ObjectRowMapper

1：public List queryAllMatchListWithFreePra(String sql, Class clasz, Object... args) // SQL 参数为问号占位符，使用参数个数<=3 个时的情况,可以用要不参数多了，可变长参数多，阅读性差

2：public List queryAllMatchListWithParaMap(String sql, Class clasz, Map paramMap) // SQL 参数，为参数名占位符 ,如 name=:name, paramMap中 key 为参数名，value 为值数值(用于参数个数多于3时，用参数名占位符的方式，然后用Map 传参) ; 如clasz 传为为空，实际返为 List<Map<String,Object>>,不为空侧为 List<Map<String,clasz>>

3：以pageModel为载体实现分页

/**

* @param pageModel: 设置了查询SQL ，及查询参数 Map paramMap 的分页对像

* @param className

* : 从查询结果集中构建出的类，如为null则pageModel的PageData为List<Map>，

* 不为null则pageModel的PageData为List<className>

* @param columnNameForCount:查询记录数时的字段名，一般用主键

@SuppressWarnings({ "rawtypes", "unchecked" })

public void fillPageModelData(PageModel pageModel, Class className)

4 ：不用 pageModel 分页查询

/**

* 多表sql 分页查询,多表连查时，才用这个方法，其他请用commonDao的 SQL分页查询

* @param sql

* @param className

* @param paramMap

* @param pageNo

* @param PageSize

* @param columnNameForCount

* @return

@SuppressWarnings({ "rawtypes", "unchecked" })

public List queryAllMatchListWithParaMap(String sql, Class clasz, Map paramMap, int pageNo, int pageSize)

5 ：把list map 原生JDBC结果集中，字段名，也就是MAP中的KEY，转换为驼峰规则的JAVA对属性名

/**

* 把list map 原生JDBC结果集中，字段名，也就是MAP中的KEY，转换为驼峰规则的JAVA对属性名

* 方便前端组件使用，不再中间转为VO 或实体类对像后，再返前端;比传统查询（多了一个LIST的遍历，基本对性能影响不大）

* @param resultList ：JDBC 结果集

* @return 把MAP中的KEY转换为转换为驼峰规则的JAVA对属性名的LIST<map<驼峰规则的JAVA对属性名形式的KEY,Object>>

* @author itest andy

public void converDbColumnName2ObjectPropName(List<Map<String,Object>> resultList)

转自本人在博客园的原创，在这里还算不算原创?