让DbUtils支持NamedParameter模式的sql

让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;
	}

}

sql处理工具类

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(){
			@Override
			public List<Map<String, Object>> handle(ResultSet rs)
					throws SQLException {
				return this.handle(rs);
			}}, obj);
		return result;
	}
	
}

让DbUtils支持NamedParameter模式的sql

相关推荐