JdbcTemplateDao

package com.hoss.core.dao;

import com.hoss.core.exception.HossSqlException;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;

import java.util.List;
import java.util.Map;

public interface SqlQueryDao {

    <T> Page<T> queryPageByConditionModel(Class<T>  resultClass,Pageable pageable,String sql,Object conditionModel) throws HossSqlException;

    <T> Page<T> queryPage(Class<T> resultClass,Pageable pageable,String sql,Object ... params);

    <T> Page<Map<String,Object>> queryPageByConditionModel(Pageable pageable,String sql,Object conditionMode) throws HossSqlException;

    <T> Page<Map<String,Object>> queryPage(Pageable pageable,String sql,Object ... params);

    <T> List<T> queryListByConditionModel(Class<T>  resultClass,String sql,Object conditionModel) throws HossSqlException;

    <T> List<T> queryList(Class<T> resultClass,String sql,Object ... params);

    <T> List<Map<String,Object>> queryListByConditionModel(String sql,Object conditionModel) throws HossSqlException;

    <T> List<Map<String,Object>> queryList(String sql,Object ... params);

    <T> T queryOneByConditionModel(Class<T>  resultClass,String sql,Object conditionModel) throws HossSqlException;

    <T> T queryOne(Class<T>  resultClass,String sql,Object ... params);

    Map<String,Object> queryOneByConditionModel(String sql,Object conditionModel) throws HossSqlException;

    Map<String,Object> queryOne(String sql,Object ... params);

    Number queryNumberByConditionMode(String sql,String numberKey,Object conditionModel) throws HossSqlException;

    Number queryNumber(String sql,String numberKey,Object ... params);

    Number queryCountByConditionMode(String sql,Object conditionModel) throws HossSqlException;

    Number queryCount(String sql,Object ... params);

    Integer execute(String sql,Object ...params);

    Integer executeByConditionMode(String sql,Object conditionModel) throws HossSqlException;

}

 

 

 

 

package com.hoss.core.dao;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Iterator;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.ConcurrentHashMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;

import com.hoss.core.util.StringUtil;
import org.apache.commons.beanutils.ConvertUtils;
import org.apache.commons.beanutils.PropertyUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;


import com.hoss.core.model.Broker;


@Repository
public class SqlQueryDaoImpl implements SqlQueryDao {

	private static final String SQL_KEY="#SQL#";
	private static final String COUNT_KEY="RESULT_COUNT";
	private static final String QUERY_COUNT_SQL=" SELECT COUNT(1) AS "+COUNT_KEY+" FROM ( "+SQL_KEY+" ) QUEYR_COUNT";
	private static final String DEFAULT_WEHRE=" 1=1 ";
	private static final Pattern REGEX= Pattern.compile(":{1}\\w+",Pattern.CASE_INSENSITIVE);
	private static final Pattern PATTERN=Pattern.compile("\\{[^{]*\\}");

	@Autowired
	private DataSource dataSource;

	private JdbcTemplate jdbcTemplate;
	@PostConstruct
	public void init(){
		jdbcTemplate=new JdbcTemplate(dataSource);
	}


	@Override
	public <T> Page<T> queryPageByConditionModel(Class<T> resultClass, Pageable pageable, String sql, Object conditionModel){
		SqlAndParams sp=generateSqlAndParams(sql, conditionModel);
		return queryPage(resultClass, pageable, sp.getSql(),sp.getParams().toArray());
	}

	@Override
	public <T> Page<T> queryPage(Class<T> resultClass, Pageable pageable, String sql, Object... params) {
		String newSql=new String ( sql+ getSortSql(pageable.getSort()) );
		Long total=queryCount(newSql,params).longValue();
		List<T> data=queryList(resultClass, genPageSql(newSql,pageable), params);
		return new PageImpl<T>(data,pageable,total);
	}

	@Override
	public Page<Map<String, Object>> queryPageByConditionModel(Pageable pageable, String sql, Object conditionMode) throws HossSqlException {
		SqlAndParams sp=generateSqlAndParams(sql,conditionMode);
		return queryPage(pageable, sp.getSql(),sp.getParams().toArray());
	}



	@Override
	public Page<Map<String, Object>> queryPage(Pageable pageable, String sql, Object... params) {
		String newSql=new String ( sql+ getSortSql(pageable.getSort()) );
		Long total=queryCount(newSql,params).longValue();
		List<Map<String,Object>> data=queryList(genPageSql(newSql,pageable), params);
		return new PageImpl<Map<String,Object>>(data,pageable,total);
	}

	@Override
	public <T> List<T> queryListByConditionModel(Class<T> resultClass, String sql, Object conditionModel) throws HossSqlException {
		SqlAndParams sp=generateSqlAndParams(sql,conditionModel);
		return queryList(resultClass, sp.getSql(), sp.getParams().toArray());
	}


	@Override
	public <T> List<T> queryList(Class<T> resultClass, String sql, Object... params) {
		return jdbcTemplate.query(sql,rowMapper(resultClass), params );
	}

	@Override
	public List<Map<String, Object>> queryListByConditionModel(String sql, Object conditionModel) throws HossSqlException {
		SqlAndParams sp=generateSqlAndParams(sql,conditionModel);
		return queryList(sp.getSql(), sp.getParams().toArray());
	}

	@Override
	public List<Map<String, Object>> queryList(String sql, Object... params) {
		System.out.println(sql);
		return jdbcTemplate.queryForList(sql, params);
	}

	@Override
	public <T> T queryOneByConditionModel(Class<T> resultClass, String sql, Object conditionModel) throws HossSqlException {
		SqlAndParams sp=generateSqlAndParams(sql,conditionModel);
		return queryOne(resultClass,sp.getSql(),sp.getParams().toArray());
	}


	@Override
	public <T>T queryOne(Class<T> resultClass, String sql, Object... params) {
        List<T> result=queryList(resultClass, sql, params);
        if( result==null || result.isEmpty() )
            return null;
		return result.get(0);
	}

	@Override
	public Map<String, Object> queryOneByConditionModel(String sql, Object conditionModel) throws HossSqlException {
		SqlAndParams sp=generateSqlAndParams(sql,conditionModel);
		return queryOne(sp.getSql(),sp.getParams().toArray());
	}



	@Override
	public Map<String, Object> queryOne(String sql, Object... params) {
		return jdbcTemplate.queryForMap(sql, params);
	}

	@Override
	public Number queryNumberByConditionMode(String sql, String numberKey, Object conditionModel) throws HossSqlException {
		SqlAndParams sp=generateSqlAndParams(sql, conditionModel);
		return queryNumber(sp.getSql(),numberKey,sp.getParams().toArray());
	}


	@Override
	public Number queryNumber(String sql, String numberKey, Object... params) {
		Map<String,Object> map=jdbcTemplate.queryForMap(sql, params);
        if(map==null)
            return null;
		return (Number) map.get(numberKey);
	}

	@Override
	public Number queryCountByConditionMode(String sql, Object conditionModel) throws HossSqlException {
		SqlAndParams sp=generateSqlAndParams(sql,conditionModel);
		return queryCount(sp.getSql(),sp.getParams().toArray());
	}


	@Override
	public Number queryCount(String sql, Object... params) {
		String query_count=QUERY_COUNT_SQL.replace(SQL_KEY,sql);
		return queryNumber(query_count,COUNT_KEY,params);
	}

    @Override
    public Integer execute(String sql, Object... params) {
        return jdbcTemplate.update(sql,params);
    }

    @Override
    public Integer executeByConditionMode(String sql, Object conditionModel) throws HossSqlException {
        SqlAndParams sp=generateSqlAndParams(sql,conditionModel);
        return execute(sp.getSql(),sp.getParams().toArray());
    }

    public String genPageSql(String sql,Pageable pageable){
		 StringBuffer pageSql=new StringBuffer(sql).append(" ");
		 pageSql.append("limit ");
		 pageSql.append( pageable.getOffset()).append(",").append(pageable.getPageSize());
		 return pageSql.toString();
	}


	/**获取排序SQl
	 * @param sort
	 * @return
	 */
	private String getSortSql(Sort sort){
		if(sort == null)
			return "";
		Iterator<Sort.Order> iterator= sort.iterator();
		StringBuffer sb=new StringBuffer();
		while (iterator.hasNext()){
			Sort.Order order=iterator.next();
			sb.append(" ").append(order.getProperty()).append(" ").append(order.getDirection());
			if( iterator.hasNext() ){
				sb.append(",");
			}
		}
        if( sb.length()>0 )
            return sb.insert(0," ORDER BY ").toString();
		return sb.toString();
	}


	/**生成  预编译 SQL 与参数
	 * @param sql 原始SQL
	 * @param conditionModel 查询条件模型
	 * @return
	 * @throws HossSqlException
	 */
	private SqlAndParams generateSqlAndParams(String sql,Object conditionModel) throws HossSqlException {
		String newSql=cleanNullAttributes(sql,conditionModel);
		Matcher matcher= REGEX.matcher(newSql);
		StringBuffer sb=new StringBuffer();
		SqlAndParams result=new SqlAndParams();
		while(matcher.find()){
			String key=matcher.group();
			String pk=key.replaceFirst(":","");
			Object value=getProperty(conditionModel,pk);
			if(value==null)
				throw new HossSqlException("param "+key+" is null by SQL :"+sql);
			else{
				matcher.appendReplacement(sb, paramAdd(result.getParams(),value));
			}
		}
		matcher.appendTail(sb);
		result.setSql(sb.toString());
		return result;

	}

	/**清除条件模型中 为NULL 的查询条件
	 * @param sql
	 * @param conditionModel
	 * @return
	 */
	private String cleanNullAttributes(String sql,Object conditionModel){
		Matcher sqlMatcher=PATTERN.matcher(sql);
		StringBuffer sb = new StringBuffer();

		while(sqlMatcher.find()){
			StringBuffer temp=new StringBuffer(sqlMatcher.group());
			temp.deleteCharAt(temp.length()-1).deleteCharAt(0);
			Matcher conditionMatcher= REGEX.matcher(temp);

			while(conditionMatcher.find()){
				String mapKey=conditionMatcher.group().replaceFirst(":","");
				Object value=getProperty(conditionModel,mapKey);
				if( value == null || (value instanceof String && ((String)value).trim().length()==0) ){
					temp=new StringBuffer(DEFAULT_WEHRE);
					break;
				}
			}
			sqlMatcher.appendReplacement(sb,temp.toString());
		}
		sqlMatcher.appendTail(sb);
		return sb.toString();
	}


	/**生成 SQL 查询所用到的参数 并生成相应的 预编译所用到的? (主要解决数组和集合参数 转化 SQL in 的问题)
	 * @param params
	 * @param value
	 * @return
	 */
	private String paramAdd(List<Object> params,Object value){
		int length=0;
		if( value instanceof  Collection ){
			Collection<?> collection=(Collection<?>)value;
			length=collection.size();
			params.addAll(collection);
		}else if( value.getClass().isArray() ){
			Object[] array=(Object[])value;
			length=array.length;
			Collections.addAll(params,array);
		}else {
			length=1;
			params.add(value);
		}
		StringBuffer sb=new StringBuffer();
		for(int i=0;i<length;i++){
			sb.append("?");
			if( i+1<length )
				sb.append(",");
		}
		return sb.toString();
	}


	/**通过反射获取属性
	 * @param obj
	 * @param key
	 * @return
	 */
	private  Object getProperty(Object obj,String key){
		if(obj == null)
			return null;
		try {
			return PropertyUtils.getProperty(obj, key);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**行记录转换pojo
	 * @param cls
	 * @return
	 */
	private <T> RowMapper<T> rowMapper(final Class<T> cls){
		return new RowMapper<T>(){

			private ConcurrentHashMap<String,Method> successCache;//不需要类型转换的
			private ConcurrentHashMap<String,PropertyDescriptor> converCache;//需要类型转换的

			@Override
			public T mapRow(ResultSet rs, int rowNum) throws SQLException {
				if(rowNum==0){  //初始化mapping 需要的缓存
					initCache(rs.getMetaData());
				}

				T object= null;
				try {
					object = cls.newInstance();
				} catch (Exception e) {

				}
				if(object==null){
					return null;
				}

				for(Map.Entry<String, Method> entry : successCache.entrySet()){ //不需要转换的字段

					setValue(entry.getValue(),object, getValue(rs,entry.getKey()));

				}

				for(PropertyDescriptor propertyDescriptor : converCache.values()){ //需要用到类型转换的字段
					String name=propertyDescriptor.getName();
					Object value=getValue(rs,name);
					if( value!=null ){
						Class<?> trgetClass=propertyDescriptor.getPropertyType();
						Method method=propertyDescriptor.getWriteMethod();
						if(value.getClass() == trgetClass || value.getClass().getSuperclass()==trgetClass){
							setValue(method,object,value);
							converCache.remove(name);
							successCache.put(name,method);
						}else{
							setValue(method,object,value,trgetClass);
						}
					}
				}
				return object;
			}

			//从结果集中获取value
			private Object getValue(ResultSet rs,String key){
				if(key != null){
					try{
						return rs.getObject(key);
					}catch (Exception e){
						
					}
				}
				return null;
			}

			//需要转换设置value
			private void setValue(Method method,Object trget,Object value,Class<?> trgetClass){
				if(value != null){
					try{
						method.invoke(trget, ConvertUtils.convert(value,trgetClass));
					}catch (Exception e){

					}
				}
			}

			//需要转换设置value
			private void setValue(Method method,Object trget,Object value){
				if(value != null){
					try{
						method.invoke(trget,value);
					}catch (Exception e){
						
					}
				}
			}


			//初始化mapping缓存
			private void initCache(ResultSetMetaData rmd){ //缓存mapping 以加快转换的速度

				converCache=new ConcurrentHashMap<String, PropertyDescriptor>();
				successCache=new ConcurrentHashMap<String,Method>();

				PropertyDescriptor [] propertyDescriptors = PropertyUtils.getPropertyDescriptors(cls);
				for(PropertyDescriptor propertyDescriptor : propertyDescriptors){

                    if( !propertyDescriptor.getName().equalsIgnoreCase("class") )
					    converCache.put(propertyDescriptor.getName(), propertyDescriptor);
				}
				try {
					for(int i=1;i<rmd.getColumnCount();i++){
						String name=rmd.getColumnLabel(i);
						if( !converCache.containsKey(name) ){
							converCache.remove(name);
						}
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		};
	}

	class SqlAndParams{
		private String sql;
		private List<Object> params;

		public SqlAndParams(){
			params=new ArrayList<Object>();
		}

		public SqlAndParams(List<Object> params){
			this.params=params;
		}

		public String getSql() {
			return sql;
		}

		public void setSql(String sql) {
			this.sql = sql;
		}

		public List<Object> getParams() {
			return params;
		}

		public void setParams(List<Object> params) {
			this.params = params;
		}


	}

	public static void main(String ... args) throws HossSqlException {
		PropertyDescriptor [] propertyDescriptors = PropertyUtils.getPropertyDescriptors(Broker.class);
		for(PropertyDescriptor propertyDescriptor : propertyDescriptors){

			//      	  converCache.put(key, value)
			System.out.println(propertyDescriptor.getName());
			//      	  propertyDescriptor.getWriteMethod()
			//      	  propertyDescriptor.getName()
			//      	  propertyDescriptor.getPropertyType()
		}
	}

}