反射自动返回自定义对象QueryCountDate

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * object: QueryCountDate
 * author: 程晓鹏
 * description: 查询日统计数据
 * date: 2018-04-17 09:31
 */
public class QueryCountDate<MODEL extends ICountDate> implements IQueryCountDate<MODEL> {

    /**
     * 默认构造函数
     */
    public QueryCountDate(){

    }

    @Override
    public List<MODEL> queryCountDate(Class<? extends ICountDate> cls, String startDate, String endDate) {
        List<MODEL> result = new ArrayList<MODEL>();
        BatchData sqlData = this.getQuerySql(cls,startDate,endDate);
        DBHelper db = new DbcpHelper("bd");
        ResultSet rs = db.executeQuery(sqlData.sql,sqlData.param); //执行查询
        try {
            while (rs.next()) {
                MODEL model = this.getInstance(cls); //创建一个对象
                for(Map.Entry<String, String> entry: model.getColumn2Filed().entrySet()) {
                    String column = entry.getKey(); //bd_count_day表中count_attribute中的值
                    String fieldName = entry.getValue(); //类的成员变量名
                    String dbvalue = rs.getString(column); //数据库中的值
                    try {
                        Field field = model.getClass().getDeclaredField(fieldName);
                        field.setAccessible(true);
                        field.set(model, dbvalue); //给成员变量，进行赋值
                    } catch (NoSuchFieldException e) {
                        e.printStackTrace();
                    }catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                }
                String countDate = rs.getString("count_date"); //获取统计日期数据
                try {
                    Method method = model.getClass().getMethod("setCountDate",String.class);
                    method.invoke(model, countDate); //调用设置统计日期方法
                } catch (NoSuchMethodException e) {
                    e.printStackTrace();
                }catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                }
                result.add(model);
            }
            rs.close();
        }catch (SQLException e) {
            e.printStackTrace();
        }finally{
            db.Close();
        }


        return result;
    }

    public List<MODEL> queryCountDate2(Class<? extends ICountDate> cls, String startDate, String endDate) {
        String[] columnArray = this.getCloumnArray(cls);
        Map<String,Map<String,String>> data = this.queryCountDate(columnArray,startDate,endDate);
        return this.formatQueryResult(cls,data);
    }

    public Map<String,Map<String,String>> queryCountDate(String[] columns, String startDate, String endDate) {
        Map<String,Map<String,String>> result = new HashMap<String, Map<String, String>>();
        BatchData sqlData = this.getQuerySql(columns,startDate,endDate);
        DBHelper db = new DbcpHelper("bd");
        ResultSet rs = db.executeQuery(sqlData.sql,sqlData.param); //执行查询
        try {
            while (rs.next()) {
                Map<String,String> item = new HashMap<String, String>(); //创建一个子map
                for(int i=0;i<columns.length;i++) {
                    String column = columns[i]; //bd_count_day表中count_attribute中的值
                    String dbvalue = rs.getString(column); //数据库中的值
                    item.put(column,dbvalue);
                }
                String countDate = rs.getString("count_date"); //获取统计日期数据
                result.put(countDate,item);
            }
            rs.close();
        }catch (SQLException e) {
            e.printStackTrace();
        }finally{
            db.Close();
        }


        return result;
    }

    private BatchData getQuerySql(Class<? extends ICountDate> cls, String startDate, String endDate){
        String[] array = this.getCloumnArray(cls);
        return this.getQuerySql(array, startDate,endDate);
    }

    public String[] getCloumnArray(Class<? extends ICountDate> cls){
        String[] result = null;
        MODEL model = this.getInstance(cls);
        result = new String[model.getColumn2Filed().size()];
        int i=0;
        for(Map.Entry<String, String> entry: model.getColumn2Filed().entrySet()) {
            result[i++] = entry.getKey();
        }

        return result;
    }

    private BatchData getQuerySql(String[] columns, String startDate, String endDate){
        BatchData batchData = new BatchData();
        StringBuilder sb = new StringBuilder();
        String[] param = new String[columns.length+2];
        sb.append("select distinct ");
        int i=0;
        for(; i<columns.length;i++) {
            String column = columns[i]; //bd_count_day表中count_attribute中的值
            sb.append(" IFNULL((select tb"+String.valueOf(i+1)+".attribute_value from bd_count_day tb"+String.valueOf(i+1)+" ");
            sb.append(" where tb"+String.valueOf(i+1)+".count_date = tb0.count_date");
            sb.append(" and tb"+String.valueOf(i+1)+".customer_id = tb0.customer_id ");
            sb.append(" and tb"+String.valueOf(i+1)+".count_attribute = ?), '') "+ column +", ");
            param[i] = column;
        }
        sb.append("tb0.count_date count_date ");
        sb.append("from bd_count_day tb0 ");
        sb.append("where tb0.count_date >= ? and tb0.count_date <= ?");
        param[i++] = startDate;
        param[i++] = endDate;
        batchData.sql = sb.toString();
        batchData.param = param;
        return batchData;
    }

    /**
     * 获取类的对象实例
     * @param cls
     * @return
     */
    private MODEL getInstance(Class<? extends ICountDate> cls){
        MODEL result = null;
        try {
            result = (MODEL)cls.newInstance();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 格式化查询结果
     * @param cls　实现ICountDate接口的类名
     * @param data　要格式化的数据
     * @return MODEL泛型对象集合
     */
    public List<MODEL> formatQueryResult(Class<? extends ICountDate> cls, Map<String,Map<String,String>> data){
        List<MODEL> result = null;
        if(data != null && data.size()>0){
            result = new ArrayList<MODEL>();
            for(Map.Entry<String, Map<String,String>> entry: data.entrySet()) {
                MODEL model = this.getInstance(cls);
                this.setCountDate(model,entry.getKey()); //设置日期
                for(Map.Entry<String,String> mapItem: entry.getValue().entrySet()){
                    this.setAttribute(model,mapItem.getKey(),mapItem.getValue());
                }
                result.add(model);
            }
        }

        return result;
    }

    private void setAttribute(MODEL model, String attrCode, String attrValue){
        try {
            String fieldName = model.getColumn2Filed().get(attrCode);
            Field field = model.getClass().getDeclaredField(fieldName);
            field.setAccessible(true);
            field.set(model, attrValue); //给成员变量，进行赋值
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        }catch (IllegalAccessException e) {
            e.printStackTrace();
        }
    }

    private void setCountDate(MODEL model, String countDate){
        try {
            Method method = model.getClass().getMethod("setCountDate",String.class);
            method.invoke(model, countDate); //调用设置统计日期方法
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        }catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
    }
}