让DbUtils支持NamedParameter方式的sql
DbUtils代码很精悍，很多中小型项目都用它来编写持久层，但是不够强大，平时用习惯了spring jdbc的NamedParameter方式的sql（形如：select * from user where name=:name）,总觉得还缺点功能，干脆仿照spring jdbc写个类似的sql处理方法，造个小小小轮子，代码留念：

import java.util.ArrayList;import java.util.List;/** * 此类封装NamedParameterSql *  * @author zl * */public class ParsedSql {private String originalSql;//参数名private List<String> paramNames = new ArrayList<String>();//参数在sql中对应的位置private List<int[]> paramIndexs = new ArrayList<int[]>();//统计参数个数（不包含重复）private int namedParamCount;//统计sql中？的个数private int unnamedParamCount;private int totalParamCount;public ParsedSql(String originalSql){this.originalSql = originalSql;}public List<String> getParamNames() {return paramNames;}public void addParamNames(String paramName,int startIndex,int endIndex) {paramNames.add(paramName);paramIndexs.add(new int[]{startIndex,endIndex});}public int[] getParamIndexs(int position) {return paramIndexs.get(position);}public String getOriginalSql() {return originalSql;}public int getNamedParamCount() {return namedParamCount;}public void setNamedParamCount(int namedParamCount) {this.namedParamCount = namedParamCount;}public int getUnnamedParamCount() {return unnamedParamCount;}public void setUnnamedParamCount(int unnamedParamCount) {this.unnamedParamCount = unnamedParamCount;}public int getTotalParamCount() {return totalParamCount;}public void setTotalParamCount(int totalParamCount) {this.totalParamCount = totalParamCount;}public String toString(){return this.originalSql;}}

import java.util.HashSet;import java.util.List;import java.util.Map;import java.util.Set;/** * 带参数sql处理工具类 *  * @author zl * */public class NamedParameterUtils {//定义特殊字符（参考spring jdbc N多）private static final char[] PARAMETER_SEPARATORS =new char[] {'"', '\'', ':', '&', ',', ';', '(', ')', '|', '=', '+', '-', '*', '%', '/', '\\', '<', '>', '^'};/** * 对带参数sql的统计式封装，便于后续肢解拼装（恐怖啊。。。。。） * @param originalSql * @return */public static ParsedSql parserSqlStatement(String originalSql) {ParsedSql parsedSql = new ParsedSql(originalSql);Set<String> paramNames = new HashSet<String>();char[] sqlchars = originalSql.toCharArray();int namedParamCount = 0;int unNamedParamCount = 0;int totalParamCount = 0;int i = 0;while(i<sqlchars.length){char statement = sqlchars[i];if(statement==':'||statement=='&'){int j = i+1;while(j<sqlchars.length&&!isSeparatorsChar(sqlchars[j])){j++;}if(j-i>1){String paramName = originalSql.substring(i+1, j);if(!paramNames.contains(paramName)){paramNames.add(paramName);namedParamCount++;}parsedSql.addParamNames(paramName, i, j);totalParamCount++;}i=j-1;}else if(statement=='?'){unNamedParamCount++;totalParamCount++;}i++;}parsedSql.setNamedParamCount(namedParamCount);parsedSql.setUnnamedParamCount(unNamedParamCount);parsedSql.setTotalParamCount(totalParamCount);return parsedSql;}/** * 获得不带参数的sql，即替换参数为？ * @param parsedSql * @param params * @return */public static String substituteNamedParams(ParsedSql parsedSql,Map<String,Object> params){String original =parsedSql.getOriginalSql();StringBuffer actual = new StringBuffer("");int lastIndex = 0;List<String> paramNames = parsedSql.getParamNames();for(int i=0;i<paramNames.size();i++){int[] indexs = parsedSql.getParamIndexs(i);int startIndex = indexs[0];int endIndex = indexs[1];String paramName = paramNames.get(i);actual.append(original.substring(lastIndex, startIndex));if(params!=null&&params.containsKey(paramName)){//if(){}actual.append("?");}else{actual.append("?");}lastIndex = endIndex;}actual.append(original.subSequence(lastIndex, original.length()));return actual.toString();}/** * 获得sql所需参数 * @param parsedSql * @param params * @return */public static Object[] buildValueArray(ParsedSql parsedSql,Map<String,Object> params){List<String> paramNames = parsedSql.getParamNames();Object[] obj = new Object[parsedSql.getTotalParamCount()];if(parsedSql.getNamedParamCount()>0&&parsedSql.getUnnamedParamCount()>0){throw new RuntimeException("parameter方式与？方式不能混合！");}for(int i=0;i<paramNames.size();i++){String keyName = paramNames.get(i);if(params.containsKey(keyName)){obj[i]=params.get(keyName);}}return obj;}protected static boolean isSeparatorsChar(char statement){if(Character.isWhitespace(statement)){return true;}for(int i=0;i<PARAMETER_SEPARATORS.length;i++){if(statement==PARAMETER_SEPARATORS[i]){return true;}}return false;}}

import java.beans.PropertyDescriptor;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import java.util.Map;import javax.sql.DataSource;import org.apache.commons.dbutils.QueryRunner;import org.apache.commons.dbutils.handlers.MapListHandler;public class JDBCTemplement {protected QueryRunner runner;protected Connection conn;public JDBCTemplement(DataSource datasource) throws SQLException {runner = new QueryRunner(datasource);conn = datasource.getConnection();conn.setAutoCommit(false);}public List<Map<String,Object>> queryAsList(String sql) throws SQLException{List<Map<String,Object>> result = new ArrayList<Map<String,Object>>();result = runner.query(sql, new MapListHandler(){public List<Map<String, Object>> handle(ResultSet rs) throws SQLException {return this.handle(rs);}});return result;}public List<Map<String,Object>> queryAsList(String sql,Map<String,Object> param) throws SQLException{if(null==param){return this.queryAsList(sql);}ParsedSql parsedSql = NamedParameterUtils.parserSqlStatement(sql);String actualSql = NamedParameterUtils.substituteNamedParams(parsedSql, param);Object[] obj = NamedParameterUtils.buildValueArray(parsedSql, param);List<Map<String,Object>> result = new ArrayList<Map<String,Object>>(); result = runner.query(actualSql, new MapListHandler(){@Overridepublic List<Map<String, Object>> handle(ResultSet rs)throws SQLException {return this.handle(rs);}}, obj);return result;}}