基于Spring JDBC 手写定制自己的ORM框架

mybatis源码分析 专栏收录该内容
10 篇文章 0 订阅

实现思路概述

ResultSet

说到ResultSet,对于有java开发经验的小伙伴自然是熟悉不过了。从ResultSet的取值大家都会,比如:

private static List<Member> select(String sql) {
       List<Member> result = new ArrayList<>();
       Connection con = null;          //连接对象
       PreparedStatement pstm = null;  //语句集
       ResultSet rs = null;            //结果集
       try {
           //1、加载驱动类
           Class.forName("com.mysql.jdbc.Driver");
           //2、建立连接
           con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/spring-db-demo","root","123456");
            //3、创建语句集
            pstm =  con.prepareStatement(sql);
            //4、执行语句集
            rs = pstm.executeQuery();
            while (rs.next()){
                //纯粹的硬编码
                Member instance = new Member();
                instance.setId(rs.getLong("id"));
                instance.setName(rs.getString("name"));
                instance.setAge(rs.getInt("age"));
                instance.setAddr(rs.getString("addr"));
                result.add(instance);
            }
            //5、获取结果集
        }catch (Exception e){
            e.printStackTrace();
        }
        //6、关闭结果集、关闭语句集、关闭连接
        finally {
            try {
                rs.close();
                pstm.close();
                con.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return result;
    }

这是我们之前没用框架的常规操作。但是随着业务和开发量的增加,我们可以发现在数据持久层这样的重复代码出现频次非常高。因此我们第一步想到的都是将非功能性代码和业务代码进行分离。大家首先想到的应该都是将封装数据的代码部分进行分离,类似如下代码:

private static List<Member> select(String sql) {
        List<Member> result = new ArrayList<>();
        Connection con = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            //1、加载驱动类
            Class.forName("com.mysql.jdbc.Driver");
            //2、建立连接
            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/spring-db-demo","root","123456");
            //3、创建语句集
            pstm =  con.prepareStatement(sql);
            //4、执行语句集
            rs = pstm.executeQuery();
            while (rs.next()){
                Member instance = mapperRow(rs,rs.getRow());
                result.add(instance);
            }
            //5、获取结果集
        }catch (Exception e){
            e.printStackTrace();
        }
        //6、关闭结果集、关闭语句集、关闭连接
        finally {
            try {
                rs.close();
                pstm.close();
                con.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return result;
    }

    private static Member mapperRow(ResultSet rs, int i) throws Exception {
        Member instance = new Member();
        instance.setId(rs.getLong("id"));
        instance.setName(rs.getString("name"));
        instance.setAge(rs.getInt("age"));
        instance.setAddr(rs.getString("addr"));
        return instance;
    }

但是在日常开发中,这样的代码逻辑重复率还是太高,上面的改造只能应用Member这个类,换一个类有需要重新封装,所以太过麻烦,这个时候我们不妨做出如下改进:

首先创建Member类:

@Entity
@Table(name = "t_member")
public class Member implements Serializable {
    @Id
    private Long id;
    private String name;
    private String addr;
    private Integer age;

    public Member() {
    }

    public Member(String name, String addr, Integer age) {
        this.name = name;
        this.addr = addr;
        this.age = age;
    }

    @Override
    public String toString() {
        return "Member{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", addr='" + addr + '\'' +
                ", age=" + age +
                '}';
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAddr() {
        return addr;
    }

    public void setAddr(String addr) {
        this.addr = addr;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }
}

对JDBC操作优化:

   public static void main(String[] args) {

        Member condition = new Member();
        condition.setName("TomCat");
        List<?> result = select(condition);
        System.out.println(JSON.toJSONString(result,true));
    }

    public static List<?> select(Object condition) {

        List<Object> result = new ArrayList<>();

        Class<?> entityClass = condition.getClass();

        Connection con = null;          //连接对象
        PreparedStatement pstm = null;  //语句集
        ResultSet rs = null;            //结果集

        try {
            //1、加载驱动类
            Class.forName("com.mysql.jdbc.Driver");
            //2、建立连接
            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/spring-db-demo","root","123456");

            //用到反射
            Map<String,String> getFieldNameByColumn = new HashMap<String,String>();
            Map<String,String> getColumnByFieldName = new HashMap<String,String>();
            Field[] fields = entityClass.getDeclaredFields();
            for (Field field : fields) {
                field.setAccessible(true);
                String fieldName = field.getName();
                if(field.isAnnotationPresent(Column.class)){
                    Column column = field.getAnnotation(Column.class);
                    //别名优先
                    String columnName = column.name();
                    getFieldNameByColumn.put(columnName,fieldName);
                    getColumnByFieldName.put(fieldName,columnName);
                }else{
                    //默认属性名就是列名
                    getFieldNameByColumn.put(fieldName,fieldName);
                    getColumnByFieldName.put(fieldName,fieldName);
                }
            }

            StringBuffer sql = new StringBuffer();
            //3、创建语句集
            Table table = entityClass.getAnnotation(Table.class);
            sql.append("select * from " + table.name() + " where 1=1 ");
            for (Field field : fields) {

                Object value = field.get(condition);
                if(null != value){
                    if(String.class == field.getType()){
                        sql.append(" and " + getColumnByFieldName.get(field.getName()) + " = '" + value + "'");
                    }else{
                        sql.append(" and " + getColumnByFieldName.get(field.getName()) + " = " + value);
                    }
                    //其他依次类推
                }
            }

           pstm = con.prepareStatement(sql.toString());

            //4、执行,获取结果集
           rs = pstm.executeQuery();

           //MetaDate 元信息
           int columnCounts = rs.getMetaData().getColumnCount();
           while (rs.next()){
               //一行一行往下读数据
               Object instance = entityClass.newInstance();  //反射

               for (int i = 1; i <= columnCounts; i++) {
                   String columnName = rs.getMetaData().getColumnName(i); //拿到列名

                   Field field = entityClass.getDeclaredField(getFieldNameByColumn.get(columnName));
                   field.setAccessible(true);

                   field.set(instance,rs.getObject(columnName));

               }
               result.add(instance);
           }
        }catch (Exception e){
            e.printStackTrace();
        }
        //6、关闭结果集、关闭语句集、关闭连接
        finally {
            try {
                rs.close();
                pstm.close();
                con.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return result;
    }

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lmWSjHwe-1635688157372)(F:\notes\images\image-20211031120859589.png)]

巧妙地利用反射机制,读取class信息和Annotation信息,将数据库表中的列和类中的字段进行关联映射并赋值,以减少重复代码。

数据库设计

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3wP3UvBO-1635688157374)(F:\notes\images\image-20211031120019153.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bOeQyujs-1635688157375)(F:\notes\images\image-20211031120033163.png)]

为什么需要ORM框架

通过上面的操作,其实我们已经了解ORM框架的基本实现原理。ORM是指对象关系映射,映射的不仅仅只是对象值,还有对象和对象之间的关系。例如一对多、多对多、一对一这样的表关系。

目前市面上有众多ORM框架,比较主流的有hibernate,jpa,mybatis等框架。

名称特征描述
Hibernate全自动框架不需要写一句SQL
MyBatis半自动框架支持简单的映射,复杂关系需要自己写SQL
Spring JDBC纯手动框架所有的SQL都需要自己写,它提供了一套标准设计流程

为了更好的了解ORM框架的实现原理,所以直接手写一个我们自己的ORM框架。

ORM顶层设计

顶层接口设计

ORM框架,我们首先需要约定好顶层接口设计。只做单表操作;多表操作通过传SQL语句;

  • List<?> select(QueryRule rule);
  • int delete(T entity)
  • int update(T entity)
  • int insert(T entity)
    • delete/update entity ID不能为空
    • insert entity ID不能为空
    • select entity非空值都作为查询条件拼接
    • queryRlue :类似JPA 构建查询条件
public interface BaseDao<T, PK> {
    /**
     * 获取列表
     *
     * @param queryRule 查询条件
     * @return
     */
    List<T> select(QueryRule queryRule) throws Exception;

    /**
     * 获取分页结果
     *
     * @param queryRule 查询条件
     * @param pageNo    页码
     * @param pageSize  每页条数
     * @return
     */
    Page<?> select(QueryRule queryRule, int pageNo, int pageSize) throws Exception;

    /**
     * 根据SQL获取列表
     *
     * @param sql  SQL语句
     * @param args 参数
     * @return
     */
    List<Map<String, Object>> selectBySql(String sql, Object... args) throws Exception;

    /**
     * 根据SQL获取分页
     *
     * @param sql      SQL语句
     * @param pageNo   页码
     * @param pageSize 每页条数
     * @return
     */
    Page<Map<String, Object>> selectBySqlToPage(String sql, Object[] param, int pageNo, int pageSize) throws Exception;


    /**
     * 删除一条记录
     *
     * @param entity entity中的ID不能为空,如果ID为空,其他条件不能为空,都为空不予执行
     * @return
     */
    boolean delete(T entity) throws Exception;

    /**
     * 批量删除
     *
     * @param list
     * @return 返回受影响的行数
     * @throws Exception
     */
    int deleteAll(List<T> list) throws Exception;

    /**
     * 插入一条记录并返回插入后的ID
     *
     * @param entity 只要entity不等于null,就执行插入
     * @return
     */
    PK insertAndReturnId(T entity) throws Exception;

    /**
     * 插入一条记录自增ID
     *
     * @param entity
     * @return
     * @throws Exception
     */
    boolean insert(T entity) throws Exception;

    /**
     * 批量插入
     *
     * @param list
     * @return 返回受影响的行数
     * @throws Exception
     */
    int insertAll(List<T> list) throws Exception;

    /**
     * 修改一条记录
     *
     * @param entity entity中的ID不能为空,如果ID为空,其他条件不能为空,都为空不予执行
     * @return
     * @throws Exception
     */
    boolean update(T entity) throws Exception;
}

搭建基础架构

/**
 * 分页对象. 包含当前页数据及分页信息如总记录数.
 * 能够支持JQuery EasyUI直接对接,能够支持和BootStrap Table直接对接
 */
public class Page<T> implements Serializable {

    private static final long serialVersionUID = 1L;

    private static final int DEFAULT_PAGE_SIZE = 20;

    private int pageSize = DEFAULT_PAGE_SIZE; // 每页的记录数

    private long start; // 当前页第一条数据在List中的位置,从0开始

    private List<T> rows; // 当前页中存放的记录,类型一般为List

    private long total; // 总记录数

    /**
     * 构造方法,只构造空页.
     */
    public Page() {
        this(0, 0, DEFAULT_PAGE_SIZE, new ArrayList<T>());
    }

    /**
     * 默认构造方法.
     *
     * @param start     本页数据在数据库中的起始位置
     * @param totalSize 数据库中总记录条数
     * @param pageSize  本页容量
     * @param rows      本页包含的数据
     */
    public Page(long start, long totalSize, int pageSize, List<T> rows) {
        this.pageSize = pageSize;
        this.start = start;
        this.total = totalSize;
        this.rows = rows;
    }

    /**
     * 取总记录数.
     */
    public long getTotal() {
        return this.total;
    }

    public void setTotal(long total) {
        this.total = total;
    }

    /**
     * 取总页数.
     */
    public long getTotalPageCount() {
        if (total % pageSize == 0) {
            return total / pageSize;
        } else {
            return total / pageSize + 1;
        }
    }

    /**
     * 取每页数据容量.
     */
    public int getPageSize() {
        return pageSize;
    }

    /**
     * 取当前页中的记录.
     */
    public List<T> getRows() {
        return rows;
    }

    public void setRows(List<T> rows) {
        this.rows = rows;
    }

    /**
     * 取该页当前页码,页码从1开始.
     */
    public long getPageNo() {
        return start / pageSize + 1;
    }

    /**
     * 该页是否有下一页.
     */
    public boolean hasNextPage() {
        return this.getPageNo() < this.getTotalPageCount() - 1;
    }

    /**
     * 该页是否有上一页.
     */
    public boolean hasPreviousPage() {
        return this.getPageNo() > 1;
    }

    /**
     * 获取任一页第一条数据在数据集的位置,每页条数使用默认值.
     *
     * @see #getStartOfPage(int, int)
     */
    protected static int getStartOfPage(int pageNo) {
        return getStartOfPage(pageNo, DEFAULT_PAGE_SIZE);
    }

    /**
     * 获取任一页第一条数据在数据集的位置.
     *
     * @param pageNo   从1开始的页号
     * @param pageSize 每页记录条数
     * @return 该页第一条数据
     */
    public static int getStartOfPage(int pageNo, int pageSize) {
        return (pageNo - 1) * pageSize;
    }

}
/**
*定义统一返回结果
*/
public class ResultMsg<T> implements Serializable {

    private static final long serialVersionUID = 2635002588308355785L;

    private int status; //状态码,系统的返回码
    private String msg;  //状态码的解释
    private T data;  //放任意结果

    public ResultMsg() {
    }

    public ResultMsg(int status) {
        this.status = status;
    }

    public ResultMsg(int status, String msg) {
        this.status = status;
        this.msg = msg;
    }

    public ResultMsg(int status, T data) {
        this.status = status;
        this.data = data;
    }

    public ResultMsg(int status, String msg, T data) {
        this.status = status;
        this.msg = msg;
        this.data = data;
    }

    public int getStatus() {
        return status;
    }

    public void setStatus(int status) {
        this.status = status;
    }

    public String getMsg() {
        return msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }

    public T getData() {
        return data;
    }

    public void setData(T data) {
        this.data = data;
    }

}
/**
 * sql排序组件
 */
public class Order {
    private boolean ascending; //升序还是降序
    private String propertyName; //哪个字段升序,哪个字段降序

    public String toString() {
        return propertyName + ' ' + (ascending ? "asc" : "desc");
    }

    protected Order(String propertyName, boolean ascending) {
        this.propertyName = propertyName;
        this.ascending = ascending;
    }

    public static Order asc(String propertyName) {
        return new Order(propertyName, true);
    }

    public static Order desc(String propertyName) {
        return new Order(propertyName, false);
    }

}
/**
 * 查询规则构造器,实现多条件复杂查询的条件拼接
 * Role 角色,Rule 尺子,规则
 */
public final class QueryRule implements Serializable {
    private static final long serialVersionUID = 1L;
    public static final int ASC_ORDER = 101;
    public static final int DESC_ORDER = 102;
    public static final int LIKE = 1;
    public static final int IN = 2;
    public static final int NOTIN = 3;
    public static final int BETWEEN = 4;
    public static final int EQ = 5;
    public static final int NOTEQ = 6;
    public static final int GT = 7;
    public static final int GE = 8;
    public static final int LT = 9;
    public static final int LE = 10;
    public static final int ISNULL = 11;
    public static final int ISNOTNULL = 12;
    public static final int ISEMPTY = 13;
    public static final int ISNOTEMPTY = 14;
    public static final int AND = 201;
    public static final int OR = 202;
    private List<Rule> ruleList = new ArrayList<Rule>();
    private List<QueryRule> queryRuleList = new ArrayList<QueryRule>();
    private String propertyName;

    private QueryRule() {
    }

    private QueryRule(String propertyName) {
        this.propertyName = propertyName;
    }

    public static QueryRule getInstance() {
        return new QueryRule();
    }

    /**
     * 添加升序规则
     *
     * @param propertyName
     * @return
     */
    public QueryRule addAscOrder(String propertyName) {
        this.ruleList.add(new Rule(ASC_ORDER, propertyName));
        return this;
    }

    /**
     * 添加降序规则
     *
     * @param propertyName
     * @return
     */
    public QueryRule addDescOrder(String propertyName) {
        this.ruleList.add(new Rule(DESC_ORDER, propertyName));
        return this;
    }

    public QueryRule andIsNull(String propertyName) {
        this.ruleList.add(new Rule(ISNULL, propertyName).setAndOr(AND));
        return this;
    }

    public QueryRule andIsNotNull(String propertyName) {
        this.ruleList.add(new Rule(ISNOTNULL, propertyName).setAndOr(AND));
        return this;
    }

    public QueryRule andIsEmpty(String propertyName) {
        this.ruleList.add(new Rule(ISEMPTY, propertyName).setAndOr(AND));
        return this;
    }

    public QueryRule andIsNotEmpty(String propertyName) {
        this.ruleList.add(new Rule(ISNOTEMPTY, propertyName).setAndOr(AND));
        return this;
    }

    public QueryRule andLike(String propertyName, Object value) {
        this.ruleList.add(new Rule(LIKE, propertyName, new Object[]{value}).setAndOr(AND));
        return this;
    }

    public QueryRule andEqual(String propertyName, Object value) {
        this.ruleList.add(new Rule(EQ, propertyName, new Object[]{value}).setAndOr(AND));
        return this;
    }

    public QueryRule andBetween(String propertyName, Object... values) {
        this.ruleList.add(new Rule(BETWEEN, propertyName, values).setAndOr(AND));
        return this;
    }

    public QueryRule andIn(String propertyName, List<Object> values) {
        this.ruleList.add(new Rule(IN, propertyName, new Object[]{values}).setAndOr(AND));
        return this;
    }

    public QueryRule andIn(String propertyName, Object... values) {
        this.ruleList.add(new Rule(IN, propertyName, values).setAndOr(AND));
        return this;
    }

    public QueryRule andNotIn(String propertyName, List<Object> values) {
        this.ruleList.add(new Rule(NOTIN, propertyName, new Object[]{values}).setAndOr(AND));
        return this;
    }

    public QueryRule orNotIn(String propertyName, Object... values) {
        this.ruleList.add(new Rule(NOTIN, propertyName, values).setAndOr(OR));
        return this;
    }


    public QueryRule andNotEqual(String propertyName, Object value) {
        this.ruleList.add(new Rule(NOTEQ, propertyName, new Object[]{value}).setAndOr(AND));
        return this;
    }

    public QueryRule andGreaterThan(String propertyName, Object value) {
        this.ruleList.add(new Rule(GT, propertyName, new Object[]{value}).setAndOr(AND));
        return this;
    }

    public QueryRule andGreaterEqual(String propertyName, Object value) {
        this.ruleList.add(new Rule(GE, propertyName, new Object[]{value}).setAndOr(AND));
        return this;
    }

    public QueryRule andLessThan(String propertyName, Object value) {
        this.ruleList.add(new Rule(LT, propertyName, new Object[]{value}).setAndOr(AND));
        return this;
    }

    public QueryRule andLessEqual(String propertyName, Object value) {
        this.ruleList.add(new Rule(LE, propertyName, new Object[]{value}).setAndOr(AND));
        return this;
    }


    public QueryRule orIsNull(String propertyName) {
        this.ruleList.add(new Rule(ISNULL, propertyName).setAndOr(OR));
        return this;
    }

    public QueryRule orIsNotNull(String propertyName) {
        this.ruleList.add(new Rule(ISNOTNULL, propertyName).setAndOr(OR));
        return this;
    }

    public QueryRule orIsEmpty(String propertyName) {
        this.ruleList.add(new Rule(ISEMPTY, propertyName).setAndOr(OR));
        return this;
    }

    public QueryRule orIsNotEmpty(String propertyName) {
        this.ruleList.add(new Rule(ISNOTEMPTY, propertyName).setAndOr(OR));
        return this;
    }

    public QueryRule orLike(String propertyName, Object value) {
        this.ruleList.add(new Rule(LIKE, propertyName, new Object[]{value}).setAndOr(OR));
        return this;
    }

    public QueryRule orEqual(String propertyName, Object value) {
        this.ruleList.add(new Rule(EQ, propertyName, new Object[]{value}).setAndOr(OR));
        return this;
    }

    public QueryRule orBetween(String propertyName, Object... values) {
        this.ruleList.add(new Rule(BETWEEN, propertyName, values).setAndOr(OR));
        return this;
    }

    public QueryRule orIn(String propertyName, List<Object> values) {
        this.ruleList.add(new Rule(IN, propertyName, new Object[]{values}).setAndOr(OR));
        return this;
    }

    public QueryRule orIn(String propertyName, Object... values) {
        this.ruleList.add(new Rule(IN, propertyName, values).setAndOr(OR));
        return this;
    }

    public QueryRule orNotEqual(String propertyName, Object value) {
        this.ruleList.add(new Rule(NOTEQ, propertyName, new Object[]{value}).setAndOr(OR));
        return this;
    }

    public QueryRule orGreaterThan(String propertyName, Object value) {
        this.ruleList.add(new Rule(GT, propertyName, new Object[]{value}).setAndOr(OR));
        return this;
    }

    public QueryRule orGreaterEqual(String propertyName, Object value) {
        this.ruleList.add(new Rule(GE, propertyName, new Object[]{value}).setAndOr(OR));
        return this;
    }

    public QueryRule orLessThan(String propertyName, Object value) {
        this.ruleList.add(new Rule(LT, propertyName, new Object[]{value}).setAndOr(OR));
        return this;
    }

    public QueryRule orLessEqual(String propertyName, Object value) {
        this.ruleList.add(new Rule(LE, propertyName, new Object[]{value}).setAndOr(OR));
        return this;
    }


    public List<Rule> getRuleList() {
        return this.ruleList;
    }

    public List<QueryRule> getQueryRuleList() {
        return this.queryRuleList;
    }

    public String getPropertyName() {
        return this.propertyName;
    }

    protected class Rule implements Serializable {
        private static final long serialVersionUID = 1L;
        private int type;    //规则的类型
        private String property_name;
        private Object[] values;
        private int andOr = AND;

        public Rule(int paramInt, String paramString) {
            this.property_name = paramString;
            this.type = paramInt;
        }

        public Rule(int paramInt, String paramString,
                    Object[] paramArrayOfObject) {
            this.property_name = paramString;
            this.values = paramArrayOfObject;
            this.type = paramInt;
        }

        public Rule setAndOr(int andOr) {
            this.andOr = andOr;
            return this;
        }

        public int getAndOr() {
            return this.andOr;
        }

        public Object[] getValues() {
            return this.values;
        }

        public int getType() {
            return this.type;
        }

        public String getPropertyName() {
            return this.property_name;
        }
    }
}

基于SpringJDBC实现关键功能

ClassMappings

/**
 * 关系映射 获取相关方法 字段等
 */
public class ClassMappings {

    private ClassMappings() {
    }

    static final Set<Class<?>> SUPPORTED_SQL_OBJECTS = new HashSet<Class<?>>();

    static {
        //只要这里写了的,默认支持自动类型转换
        Class<?>[] classes = {
                boolean.class, Boolean.class,
                short.class, Short.class,
                int.class, Integer.class,
                long.class, Long.class,
                float.class, Float.class,
                double.class, Double.class,
                String.class,
                Date.class,
                Timestamp.class,
                BigDecimal.class
        };
        SUPPORTED_SQL_OBJECTS.addAll(Arrays.asList(classes));
    }

    static boolean isSupportedSQLObject(Class<?> clazz) {
        return clazz.isEnum() || SUPPORTED_SQL_OBJECTS.contains(clazz);
    }

    public static Map<String, Method> findPublicGetters(Class<?> clazz) {
        Map<String, Method> map = new HashMap<String, Method>();
        Method[] methods = clazz.getMethods();
        for (Method method : methods) {
            if (Modifier.isStatic(method.getModifiers()))
                continue;
            if (method.getParameterTypes().length != 0)
                continue;
            if (method.getName().equals("getClass"))
                continue;
            Class<?> returnType = method.getReturnType();
            if (void.class.equals(returnType))
                continue;
            if (!isSupportedSQLObject(returnType)) {
                continue;
            }
            if ((returnType.equals(boolean.class)
                    || returnType.equals(Boolean.class))
                    && method.getName().startsWith("is")
                    && method.getName().length() > 2) {
                map.put(getGetterName(method), method);
                continue;
            }
            if (!method.getName().startsWith("get"))
                continue;
            if (method.getName().length() < 4)
                continue;
            map.put(getGetterName(method), method);
        }
        return map;
    }

    public static Field[] findFields(Class<?> clazz) {
        return clazz.getDeclaredFields();
    }

    public static Map<String, Method> findPublicSetters(Class<?> clazz) {
        Map<String, Method> map = new HashMap<String, Method>();
        Method[] methods = clazz.getMethods();
        for (Method method : methods) {
            if (Modifier.isStatic(method.getModifiers()))
                continue;
            if (!void.class.equals(method.getReturnType()))
                continue;
            if (method.getParameterTypes().length != 1)
                continue;
            if (!method.getName().startsWith("set"))
                continue;
            if (method.getName().length() < 4)
                continue;
            if (!isSupportedSQLObject(method.getParameterTypes()[0])) {
                continue;
            }
            map.put(getSetterName(method), method);
        }
        return map;
    }

    public static String getGetterName(Method getter) {
        String name = getter.getName();
        if (name.startsWith("is"))
            name = name.substring(2);
        else
            name = name.substring(3);
        return Character.toLowerCase(name.charAt(0)) + name.substring(1);
    }

    private static String getSetterName(Method setter) {
        String name = setter.getName().substring(3);
        return Character.toLowerCase(name.charAt(0)) + name.substring(1);
    }
}

QueryRuleSqlBuilder

/**
 * 根据QueryRule自动构建sql语句
 */
public class QueryRuleSqlBuilder {
    private int CURR_INDEX = 0; //记录参数所在的位置
    private List<String> properties; //保存列名列表
    private List<Object> values; //保存参数值列表
    private List<Order> orders; //保存排序规则列表

    private String whereSql = "";
    private String orderSql = "";
    private Object[] valueArr = new Object[]{};
    private Map<Object, Object> valueMap = new HashMap<Object, Object>();

    /**
     * 或得查询条件
     *
     * @return
     */
    public String getWhereSql() {
        return this.whereSql;
    }

    /**
     * 获得排序条件
     *
     * @return
     */
    public String getOrderSql() {
        return this.orderSql;
    }

    /**
     * 获得参数值列表
     *
     * @return
     */
    public Object[] getValues() {
        return this.valueArr;
    }

    /**
     * 获取参数列表
     *
     * @return
     */
    public Map<Object, Object> getValueMap() {
        return this.valueMap;
    }

    /**
     * 创建SQL构造器
     *
     * @param queryRule
     */
    public QueryRuleSqlBuilder(QueryRule queryRule) {
        CURR_INDEX = 0;
        properties = new ArrayList<String>();
        values = new ArrayList<Object>();
        orders = new ArrayList<Order>();
        for (QueryRule.Rule rule : queryRule.getRuleList()) {
            switch (rule.getType()) {
                case QueryRule.BETWEEN:
                    processBetween(rule);
                    break;
                case QueryRule.EQ:
                    processEqual(rule);
                    break;
                case QueryRule.LIKE:
                    processLike(rule);
                    break;
                case QueryRule.NOTEQ:
                    processNotEqual(rule);
                    break;
                case QueryRule.GT:
                    processGreaterThen(rule);
                    break;
                case QueryRule.GE:
                    processGreaterEqual(rule);
                    break;
                case QueryRule.LT:
                    processLessThen(rule);
                    break;
                case QueryRule.LE:
                    processLessEqual(rule);
                    break;
                case QueryRule.IN:
                    processIN(rule);
                    break;
                case QueryRule.NOTIN:
                    processNotIN(rule);
                    break;
                case QueryRule.ISNULL:
                    processIsNull(rule);
                    break;
                case QueryRule.ISNOTNULL:
                    processIsNotNull(rule);
                    break;
                case QueryRule.ISEMPTY:
                    processIsEmpty(rule);
                    break;
                case QueryRule.ISNOTEMPTY:
                    processIsNotEmpty(rule);
                    break;
                case QueryRule.ASC_ORDER:
                    processOrder(rule);
                    break;
                case QueryRule.DESC_ORDER:
                    processOrder(rule);
                    break;
                default:
                    throw new IllegalArgumentException("type " + rule.getType() + " not supported.");
            }
        }
        //拼装where语句
        appendWhereSql();
        //拼装排序语句
        appendOrderSql();
        //拼装参数值
        appendValues();
    }

    /**
     * 去掉order
     *
     * @param sql
     * @return
     */
    protected 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();
    }

    /**
     * 去掉select
     *
     * @param sql
     * @return
     */
    protected String removeSelect(String sql) {
        if (sql.toLowerCase().matches("from\\s+")) {
            int beginPos = sql.toLowerCase().indexOf("from");
            return sql.substring(beginPos);
        } else {
            return sql;
        }
    }

    /**
     * 处理like
     *
     * @param rule
     */
    private void processLike(QueryRule.Rule rule) {
        if (ArrayUtils.isEmpty(rule.getValues())) {
            return;
        }
        Object obj = rule.getValues()[0];

        if (obj != null) {
            String value = obj.toString();
            if (!StringUtils.isEmpty(value)) {
                value = value.replace('*', '%');
                obj = value;
            }
        }
        add(rule.getAndOr(), rule.getPropertyName(), "like", "%" + rule.getValues()[0] + "%");
    }

    /**
     * 处理between
     *
     * @param rule
     */
    private void processBetween(QueryRule.Rule rule) {
        if ((ArrayUtils.isEmpty(rule.getValues()))
                || (rule.getValues().length < 2)) {
            return;
        }
        add(rule.getAndOr(), rule.getPropertyName(), "", "between", rule.getValues()[0], "and");
        add(0, "", "", "", rule.getValues()[1], "");
    }

    /**
     * 处理 =
     *
     * @param rule
     */
    private void processEqual(QueryRule.Rule rule) {
        if (ArrayUtils.isEmpty(rule.getValues())) {
            return;
        }
        add(rule.getAndOr(), rule.getPropertyName(), "=", rule.getValues()[0]);
    }

    /**
     * 处理 <>
     *
     * @param rule
     */
    private void processNotEqual(QueryRule.Rule rule) {
        if (ArrayUtils.isEmpty(rule.getValues())) {
            return;
        }
        add(rule.getAndOr(), rule.getPropertyName(), "<>", rule.getValues()[0]);
    }

    /**
     * 处理 >
     *
     * @param rule
     */
    private void processGreaterThen(
            QueryRule.Rule rule) {
        if (ArrayUtils.isEmpty(rule.getValues())) {
            return;
        }
        add(rule.getAndOr(), rule.getPropertyName(), ">", rule.getValues()[0]);
    }

    /**
     * 处理>=
     *
     * @param rule
     */
    private void processGreaterEqual(
            QueryRule.Rule rule) {
        if (ArrayUtils.isEmpty(rule.getValues())) {
            return;
        }
        add(rule.getAndOr(), rule.getPropertyName(), ">=", rule.getValues()[0]);
    }

    /**
     * 处理<
     *
     * @param rule
     */
    private void processLessThen(QueryRule.Rule rule) {
        if (ArrayUtils.isEmpty(rule.getValues())) {
            return;
        }
        add(rule.getAndOr(), rule.getPropertyName(), "<", rule.getValues()[0]);
    }

    /**
     * 处理<=
     *
     * @param rule
     */
    private void processLessEqual(
            QueryRule.Rule rule) {
        if (ArrayUtils.isEmpty(rule.getValues())) {
            return;
        }
        add(rule.getAndOr(), rule.getPropertyName(), "<=", rule.getValues()[0]);
    }

    /**
     * 处理  is null
     *
     * @param rule
     */
    private void processIsNull(QueryRule.Rule rule) {
        add(rule.getAndOr(), rule.getPropertyName(), "is null", null);
    }

    /**
     * 处理 is not null
     *
     * @param rule
     */
    private void processIsNotNull(QueryRule.Rule rule) {
        add(rule.getAndOr(), rule.getPropertyName(), "is not null", null);
    }

    /**
     * 处理  <>''
     *
     * @param rule
     */
    private void processIsNotEmpty(QueryRule.Rule rule) {
        add(rule.getAndOr(), rule.getPropertyName(), "<>", "''");
    }

    /**
     * 处理 =''
     *
     * @param rule
     */
    private void processIsEmpty(QueryRule.Rule rule) {
        add(rule.getAndOr(), rule.getPropertyName(), "=", "''");
    }


    /**
     * 处理in和not in
     *
     * @param rule
     * @param name
     */
    private void inAndNotIn(QueryRule.Rule rule, String name) {
        if (ArrayUtils.isEmpty(rule.getValues())) {
            return;
        }
        if ((rule.getValues().length == 1) && (rule.getValues()[0] != null)
                && (rule.getValues()[0] instanceof List)) {
            List<Object> list = (List) rule.getValues()[0];

            if ((list != null) && (list.size() > 0)) {
                for (int i = 0; i < list.size(); i++) {
                    if (i == 0 && i == list.size() - 1) {
                        add(rule.getAndOr(), rule.getPropertyName(), "", name + " (", list.get(i), ")");
                    } else if (i == 0 && i < list.size() - 1) {
                        add(rule.getAndOr(), rule.getPropertyName(), "", name + " (", list.get(i), "");
                    }
                    if (i > 0 && i < list.size() - 1) {
                        add(0, "", ",", "", list.get(i), "");
                    }
                    if (i == list.size() - 1 && i != 0) {
                        add(0, "", ",", "", list.get(i), ")");
                    }
                }
            }
        } else {
            Object[] list = rule.getValues();
            for (int i = 0; i < list.length; i++) {
                if (i == 0 && i == list.length - 1) {
                    add(rule.getAndOr(), rule.getPropertyName(), "", name + " (", list[i], ")");
                } else if (i == 0 && i < list.length - 1) {
                    add(rule.getAndOr(), rule.getPropertyName(), "", name + " (", list[i], "");
                }
                if (i > 0 && i < list.length - 1) {
                    add(0, "", ",", "", list[i], "");
                }
                if (i == list.length - 1 && i != 0) {
                    add(0, "", ",", "", list[i], ")");
                }
            }
        }
    }

    /**
     * 处理 not in
     *
     * @param rule
     */
    private void processNotIN(QueryRule.Rule rule) {
        inAndNotIn(rule, "not in");
    }

    /**
     * 处理 in
     *
     * @param rule
     */
    private void processIN(QueryRule.Rule rule) {
        inAndNotIn(rule, "in");
    }

    /**
     * 处理 order by
     *
     * @param rule 查询规则
     */
    private void processOrder(QueryRule.Rule rule) {
        switch (rule.getType()) {
            case QueryRule.ASC_ORDER:
                // propertyName非空
                if (!StringUtils.isEmpty(rule.getPropertyName())) {
                    orders.add(Order.asc(rule.getPropertyName()));
                }
                break;
            case QueryRule.DESC_ORDER:
                // propertyName非空
                if (!StringUtils.isEmpty(rule.getPropertyName())) {
                    orders.add(Order.desc(rule.getPropertyName()));
                }
                break;
            default:
                break;
        }
    }


    /**
     * 加入到sql查询规则队列
     *
     * @param andOr and 或者 or
     * @param key   列名
     * @param split 列名与值之间的间隔
     * @param value 值
     */
    private void add(int andOr, String key, String split, Object value) {
        add(andOr, key, split, "", value, "");
    }

    /**
     * 加入到sql查询规则队列
     *
     * @param andOr  and 或则 or
     * @param key    列名
     * @param split  列名与值之间的间隔
     * @param prefix 值前缀
     * @param value  值
     * @param suffix 值后缀
     */
    private void add(int andOr, String key, String split, String prefix, Object value, String suffix) {
        String andOrStr = (0 == andOr ? "" : (QueryRule.AND == andOr ? " and " : " or "));
        properties.add(CURR_INDEX, andOrStr + key + " " + split + prefix + (null != value ? " ? " : " ") + suffix);
        if (null != value) {
            values.add(CURR_INDEX, value);
            CURR_INDEX++;
        }
    }


    /**
     * 拼装 where 语句
     */
    private void appendWhereSql() {
        StringBuffer whereSql = new StringBuffer();
        for (String p : properties) {
            whereSql.append(p);
        }
        this.whereSql = removeSelect(removeOrders(whereSql.toString()));
    }

    /**
     * 拼装排序语句
     */
    private void appendOrderSql() {
        StringBuffer orderSql = new StringBuffer();
        for (int i = 0; i < orders.size(); i++) {
            if (i > 0 && i < orders.size()) {
                orderSql.append(",");
            }
            orderSql.append(orders.get(i).toString());
        }
        this.orderSql = removeSelect(removeOrders(orderSql.toString()));
    }

    /**
     * 拼装参数值
     */
    private void appendValues() {
        Object[] val = new Object[values.size()];
        for (int i = 0; i < values.size(); i++) {
            val[i] = values.get(i);
            valueMap.put(i, values.get(i));
        }
        this.valueArr = val;
    }

}

BaseDaoSupport

/**
 * BaseDao 扩展类,主要功能是支持自动拼装sql语句,必须继承方可使用
 * 需要重写和实现以下三个方法
 * //设定主键列
 * private String getPKColumn() {return "id";}
 * //重写对象反转为Map的方法
 * protected Map<String, Object> parse(Object entity) {return utils.parse((Entity)entity);}
 * //重写结果反转为对象的方法
 * protected Entity mapRow(ResultSet rs, int rowNum) throws SQLException {return utils.parse(rs);}
 */
public abstract class BaseDaoSupport<T extends Serializable, PK extends Serializable> implements BaseDao<T, PK> {
    private Logger log = Logger.getLogger(BaseDaoSupport.class);

    private String tableName = "";

    private JdbcTemplate jdbcTemplateWrite;
    private JdbcTemplate jdbcTemplateReadOnly;

    //实现读写分离 操作两个数据源
    private DataSource dataSourceReadOnly;
    private DataSource dataSourceWrite;

    private EntityOperation<T> op;

    @SuppressWarnings("unchecked")
    protected BaseDaoSupport() {
        try {
            //    Class<T> entityClass = (Class<T>)((ParameterizedType) getClass().getGenericSuperclass()).getActualTypeArguments()[0];
            Class<T> entityClass = GenericsUtils.getSuperClassGenricType(getClass(), 0);
            op = new EntityOperation<T>(entityClass, this.getPKColumn());
            this.setTableName(op.tableName);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    protected String getTableName() {
        return tableName;
    }

    protected DataSource getDataSourceReadOnly() {
        return dataSourceReadOnly;
    }

    protected DataSource getDataSourceWrite() {
        return dataSourceWrite;
    }

    /**
     * 动态切换表名
     */
    protected void setTableName(String tableName) {
        if (StringUtils.isEmpty(tableName)) {
            this.tableName = op.tableName;
        } else {
            this.tableName = tableName;
        }
    }

    protected void setDataSourceWrite(DataSource dataSourceWrite) {
        this.dataSourceWrite = dataSourceWrite;
        jdbcTemplateWrite = new JdbcTemplate(dataSourceWrite);
    }

    protected void setDataSourceReadOnly(DataSource dataSourceReadOnly) {
        this.dataSourceReadOnly = dataSourceReadOnly;
        jdbcTemplateReadOnly = new JdbcTemplate(dataSourceReadOnly);
    }

    private JdbcTemplate jdbcTemplateReadOnly() {
        return this.jdbcTemplateReadOnly;
    }

    private JdbcTemplate jdbcTemplateWrite() {
        return this.jdbcTemplateWrite;
    }


    /**
     * 还原默认表名
     */
    protected void restoreTableName() {
        this.setTableName(op.tableName);
    }

    /**
     * 将对象解析为Map
     *
     * @param entity
     * @return
     */
    protected Map<String, Object> parse(T entity) {
        return op.parse(entity);
    }


    /**
     * 根据ID获取对象. 如果对象不存在,返回null.<br>
     */
    protected T get(PK id) throws Exception {
        return (T) this.doLoad(id, this.op.rowMapper);
    }

    /**
     * 获取全部对象. <br>
     *
     * @return 全部对象
     */
    protected List<T> getAll() throws Exception {
        String sql = "select " + op.allColumn + " from " + getTableName();
        return this.jdbcTemplateReadOnly().query(sql, this.op.rowMapper, new HashMap<String, Object>());
    }

    /**
     * 插入并返回id
     *
     * @param entity
     * @return
     */
    public PK insertAndReturnId(T entity) throws Exception {
        return (PK) this.doInsertRuturnKey(parse(entity));
    }

    /**
     * 插入一条记录
     *
     * @param entity
     * @return
     */
    public boolean insert(T entity) throws Exception {
        return this.doInsert(parse(entity));
    }


    /**
     * 保存对象,如果对象存在则更新,否则插入.
     *
     * @throws IllegalAccessException
     * @throws IllegalArgumentException
     */
    protected boolean save(T entity) throws Exception {
        PK pkValue = (PK) op.pkField.get(entity);
        if (this.exists(pkValue)) {
            return this.doUpdate(pkValue, parse(entity)) > 0;
        } else {
            return this.doInsert(parse(entity));
        }
    }

    /**
     * 保存并返回新的id,如果对象存在则更新,否则插入
     *
     * @param entity
     * @return
     * @throws IllegalAccessException
     * @throws IllegalArgumentException
     */
    protected PK saveAndReturnId(T entity) throws Exception {
        Object o = op.pkField.get(entity);
        if (null == o) {
            return (PK) this.doInsertRuturnKey(parse(entity));
            //return (PK)id;
        }
        PK pkValue = (PK) o;
        if (this.exists(pkValue)) {
            this.doUpdate(pkValue, parse(entity));
            return pkValue;
        } else {
            return (PK) this.doInsertRuturnKey(parse(entity));
        }
    }

    /**
     * 更新对象.<br>
     * 例如:以下代码将对象更新到数据库
     * <pre>
     *        <code>
     * User entity = service.get(1);
     * entity.setName(&quot;zzz&quot;);
     * // 更新对象
     * service.update(entity);
     * </code>
     * </pre>
     *
     * @param entity 待更新对对象
     * @throws IllegalAccessException
     * @throws IllegalArgumentException
     */
    public boolean update(T entity) throws Exception {
        return this.doUpdate(op.pkField.get(entity), parse(entity)) > 0;
    }

    /**
     * 使用SQL语句更新对象.<br>
     * 例如:以下代码将更新id="0002"的name值更新为“张三”到数据库
     * <pre>
     *        <code>
     * String name = "张三";
     * String id = "0002";
     * String sql = "UPDATE SET name = ? WHERE id = ?";
     * // 更新对象
     * service.update(sql,name,id)
     * </code>
     * </pre>
     *
     * @param sql  更新sql语句
     * @param args 参数对象
     * @return 更新记录数
     */
    protected int update(String sql, Object... args) throws Exception {
        return jdbcTemplateWrite().update(sql, args);
    }

    /**
     * 使用SQL语句更新对象.<br>
     * 例如:以下代码将更新id="0002"的name值更新为“张三”到数据库
     * <pre>
     *        <code>
     * Map<String,Object> map = new HashMap();
     * map.put("name","张三");
     * map.put("id","0002");
     * String sql = "UPDATE SET name = :name WHERE id = :id";
     * // 更新对象
     * service.update(sql,map)
     * </code>
     * </pre>
     *
     * @param sql      更新sql语句
     * @param paramMap 参数对象
     * @return 更新记录数
     */
    protected int update(String sql, Map<String, ?> paramMap) throws Exception {
        return jdbcTemplateWrite().update(sql, paramMap);
    }

    /**
     * 批量保存对象.<br>
     * 例如:以下代码将对象保存到数据库
     * <pre>
     *        <code>
     * List&lt;Role&gt; list = new ArrayList&lt;Role&gt;();
     * for (int i = 1; i &lt; 8; i++) {
     *     Role role = new Role();
     *     role.setId(i);
     *     role.setRolename(&quot;管理quot; + i);
     *     role.setPrivilegesFlag(&quot;1,2,3&quot;);
     *     list.add(role);
     * }
     * service.insertAll(list);
     * </code>
     * </pre>
     *
     * @param list 待保存的对象List
     * @throws InvocationTargetException
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     */
    public int insertAll(List<T> list) throws Exception {
        int count = 0, len = list.size(), step = 50000;
        Map<String, PropertyMapping> pm = op.mappings;
        int maxPage = (len % step == 0) ? (len / step) : (len / step + 1);
        for (int i = 1; i <= maxPage; i++) {
            Page<T> page = pagination(list, i, step);
            String sql = "insert into " + getTableName() + "(" + op.allColumn + ") values ";// (" + valstr.toString() + ")";
            StringBuffer valstr = new StringBuffer();
            Object[] values = new Object[pm.size() * page.getRows().size()];
            for (int j = 0; j < page.getRows().size(); j++) {
                if (j > 0 && j < page.getRows().size()) {
                    valstr.append(",");
                }
                valstr.append("(");
                int k = 0;
                for (PropertyMapping p : pm.values()) {
                    values[(j * pm.size()) + k] = p.getter.invoke(page.getRows().get(j));
                    if (k > 0 && k < pm.size()) {
                        valstr.append(",");
                    }
                    valstr.append("?");
                    k++;
                }
                valstr.append(")");
            }
            int result = jdbcTemplateWrite().update(sql + valstr.toString(), values);
            count += result;
        }

        return count;
    }


    protected boolean replaceOne(T entity) throws Exception {
        return this.doReplace(parse(entity));
    }


    protected int replaceAll(List<T> list) throws Exception {
        int count = 0, len = list.size(), step = 50000;
        Map<String, PropertyMapping> pm = op.mappings;
        int maxPage = (len % step == 0) ? (len / step) : (len / step + 1);
        for (int i = 1; i <= maxPage; i++) {
            Page<T> page = pagination(list, i, step);
            String sql = "replace into " + getTableName() + "(" + op.allColumn + ") values ";// (" + valstr.toString() + ")";
            StringBuffer valstr = new StringBuffer();
            Object[] values = new Object[pm.size() * page.getRows().size()];
            for (int j = 0; j < page.getRows().size(); j++) {
                if (j > 0 && j < page.getRows().size()) {
                    valstr.append(",");
                }
                valstr.append("(");
                int k = 0;
                for (PropertyMapping p : pm.values()) {
                    values[(j * pm.size()) + k] = p.getter.invoke(page.getRows().get(j));
                    if (k > 0 && k < pm.size()) {
                        valstr.append(",");
                    }
                    valstr.append("?");
                    k++;
                }
                valstr.append(")");
            }
            int result = jdbcTemplateWrite().update(sql + valstr.toString(), values);
            count += result;
        }
        return count;
    }


    /**
     * 删除对象.<br>
     * 例如:以下删除entity对应的记录
     * <pre>
     *        <code>
     * service.delete(entity);
     * </code>
     * </pre>
     *
     * @param entity 待删除的实体对象
     */
    public boolean delete(T entity) throws Exception {
        return this.doDelete(op.pkField.get(entity)) > 0;
    }

    /**
     * 删除对象.<br>
     * 例如:以下删除entity对应的记录
     * <pre>
     *        <code>
     * service.deleteAll(entityList);
     * </code>
     * </pre>
     *
     * @param list 待删除的实体对象列表
     * @throws InvocationTargetException
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     */
    public int deleteAll(List<T> list) throws Exception {
        String pkName = op.pkField.getName();
        int count = 0, len = list.size(), step = 1000;
        Map<String, PropertyMapping> pm = op.mappings;
        int maxPage = (len % step == 0) ? (len / step) : (len / step + 1);
        for (int i = 1; i <= maxPage; i++) {
            StringBuffer valstr = new StringBuffer();
            Page<T> page = pagination(list, i, step);
            Object[] values = new Object[page.getRows().size()];

            for (int j = 0; j < page.getRows().size(); j++) {
                if (j > 0 && j < page.getRows().size()) {
                    valstr.append(",");
                }
                values[j] = pm.get(pkName).getter.invoke(page.getRows().get(j));
                valstr.append("?");
            }

            String sql = "delete from " + getTableName() + " where " + pkName + " in (" + valstr.toString() + ")";
            int result = jdbcTemplateWrite().update(sql, values);
            count += result;
        }
        return count;
    }

    /**
     * 根据ID删除对象.如果有记录则删之,没有记录也不报异常<br>
     * 例如:以下删除主键唯一的记录
     * <pre>
     *        <code>
     * service.deleteByPK(1);
     * </code>
     * </pre>
     *
     * @param id 序列化对id
     */
    protected void deleteByPK(PK id) throws Exception {
        this.doDelete(id);
    }

    /**
     * 根据ID删除对象.如果有记录则删之,没有记录也不报异常<br>
     * 例如:以下删除主键唯一的记录
     * <pre>
     *        <code>
     * service.delete(1);
     * </code>
     * </pre>
     *
     * @param id 序列化对id
     *
     * @return 删除是否成功
     */
// protected boolean delete(PK id)  throws Exception {
//    return this.doDelete(id) > 0;
// }

    /**
     * 根据属性名查询出内容等于属性值的唯一对象,没符合条件的记录返回null.<br>
     * 例如,如下语句查找id=5的唯一记录:
     *
     * <pre>
     *     <code>
     * User user = service.selectUnique(User.class, &quot;id&quot;, 5);
     * </code>
     * </pre>
     *
     * @param propertyName 属性名
     * @param value        属性值
     * @return 符合条件的唯一对象 or null if not found.
     */
    protected T selectUnique(String propertyName, Object value) throws Exception {
        QueryRule queryRule = QueryRule.getInstance();
        queryRule.andEqual(propertyName, value);
        return this.selectUnique(queryRule);
    }

    /**
     * 根据主键判断对象是否存在. 例如:以下代码判断id=2的User记录是否存在
     *
     * <pre>
     *        <code>
     * boolean user2Exist = service.exists(User.class, 2);
     * </code>
     * </pre>
     *
     * @param id 序列化对象id
     * @return 存在返回true,否则返回false
     */
    protected boolean exists(PK id) throws Exception {
        return null != this.doLoad(id, this.op.rowMapper);
    }

    /**
     * 查询满足条件的记录数,使用hql.<br>
     * 例如:查询User里满足条件?name like "%ca%" 的记录数
     *
     * <pre>
     *        <code>
     * long count = service.getCount(&quot;from User where name like ?&quot;, &quot;%ca%&quot;);
     * </code>
     * </pre>
     *
     * @param queryRule
     * @return 满足条件的记录数
     */
    protected long getCount(QueryRule queryRule) throws Exception {
        QueryRuleSqlBuilder bulider = new QueryRuleSqlBuilder(queryRule);
        Object[] values = bulider.getValues();
        String ws = removeFirstAnd(bulider.getWhereSql());
        String whereSql = ("".equals(ws) ? ws : (" where " + ws));
        String countSql = "select count(1) from " + getTableName() + whereSql;
        return (Long) this.jdbcTemplateReadOnly().queryForMap(countSql, values).get("count(1)");
    }

    /**
     * 根据某个属性值倒序获得第一个最大值
     *
     * @param propertyName
     * @return
     */
    protected T getMax(String propertyName) throws Exception {
        QueryRule queryRule = QueryRule.getInstance();
        queryRule.addDescOrder(propertyName);
        Page<T> result = this.select(queryRule, 1, 1);
        if (null == result.getRows() || 0 == result.getRows().size()) {
            return null;
        } else {
            return result.getRows().get(0);
        }
    }

    /**
     * 查询函数,使用查询规
     * 例如以下代码查询条件为匹配的数据
     *
     * <pre>
     *        <code>
     * QueryRule queryRule = QueryRule.getInstance();
     * queryRule.addLike(&quot;username&quot;, user.getUsername());
     * queryRule.addLike(&quot;monicker&quot;, user.getMonicker());
     * queryRule.addBetween(&quot;id&quot;, lowerId, upperId);
     * queryRule.addDescOrder(&quot;id&quot;);
     * queryRule.addAscOrder(&quot;username&quot;);
     * list = userService.select(User.class, queryRule);
     * </code>
     * </pre>
     *
     * @param queryRule 查询规则
     * @return 查询出的结果List
     */
    public List<T> select(QueryRule queryRule) throws Exception {
        QueryRuleSqlBuilder bulider = new QueryRuleSqlBuilder(queryRule);
        String ws = removeFirstAnd(bulider.getWhereSql());
        String whereSql = ("".equals(ws) ? ws : (" where " + ws));
        String sql = "select " + op.allColumn + " from " + getTableName() + whereSql;
        Object[] values = bulider.getValues();
        String orderSql = bulider.getOrderSql();
        orderSql = (StringUtils.isEmpty(orderSql) ? " " : (" order by " + orderSql));
        sql += orderSql;
        log.debug(sql);
        return (List<T>) this.jdbcTemplateReadOnly().query(sql, this.op.rowMapper, values);
    }

    /**
     * 根据SQL语句执行查询,参数为Map
     *
     * @param sql   语句
     * @param pamam 为Map,key为属性名,value为属性值
     * @return 符合条件的所有对象
     */
    protected List<Map<String, Object>> selectBySql(String sql, Map<String, ?> pamam) throws Exception {
        return this.jdbcTemplateReadOnly().queryForList(sql, pamam);
    }

    /**
     * 根据SQL语句查询符合条件的唯一对象,没符合条件的记录返回null.<br>
     *
     * @param sql   语句
     * @param pamam 为Map,key为属性名,value为属性值
     * @return 符合条件的唯一对象,没符合条件的记录返回null.
     */
    protected Map<String, Object> selectUniqueBySql(String sql, Map<String, ?> pamam) throws Exception {
        List<Map<String, Object>> list = selectBySql(sql, pamam);
        if (list.size() == 0) {
            return null;
        } else if (list.size() == 1) {
            return list.get(0);
        } else {
            throw new IllegalStateException("findUnique return " + list.size() + " record(s).");
        }
    }

    /**
     * 根据SQL语句执行查询,参数为Object数组对象
     *
     * @param sql  查询语句
     * @param args 为Object数组
     * @return 符合条件的所有对象
     */
    public List<Map<String, Object>> selectBySql(String sql, Object... args) throws Exception {
        return this.jdbcTemplateReadOnly().queryForList(sql, args);
    }

    /**
     * 根据SQL语句查询符合条件的唯一对象,没符合条件的记录返回null.<br>
     *
     * @param sql  查询语句
     * @param args 为Object数组
     * @return 符合条件的唯一对象,没符合条件的记录返回null.
     */
    protected Map<String, Object> selectUniqueBySql(String sql, Object... args) throws Exception {
        List<Map<String, Object>> list = selectBySql(sql, args);
        if (list.size() == 0) {
            return null;
        } else if (list.size() == 1) {
            return list.get(0);
        } else {
            throw new IllegalStateException("findUnique return " + list.size() + " record(s).");
        }
    }

    /**
     * 根据SQL语句执行查询,参数为List对象
     *
     * @param sql            查询语句
     * @param list<Object>对象
     * @return 符合条件的所有对象
     */
    protected List<Map<String, Object>> selectBySql(String sql, List<Object> list) throws Exception {
        return this.jdbcTemplateReadOnly().queryForList(sql, list.toArray());
    }

    /**
     * 根据SQL语句查询符合条件的唯一对象,没符合条件的记录返回null.<br>
     *
     * @param sql       查询语句
     * @param listParam 属性值List
     * @return 符合条件的唯一对象,没符合条件的记录返回null.
     */
    protected Map<String, Object> selectUniqueBySql(String sql, List<Object> listParam) throws Exception {
        List<Map<String, Object>> listMap = selectBySql(sql, listParam);
        if (listMap.size() == 0) {
            return null;
        } else if (listMap.size() == 1) {
            return listMap.get(0);
        } else {
            throw new IllegalStateException("findUnique return " + listMap.size() + " record(s).");
        }
    }

    /**
     * 分页查询函数,使用查询规则<br>
     * 例如以下代码查询条件为匹配的数据
     *
     * <pre>
     *        <code>
     * QueryRule queryRule = QueryRule.getInstance();
     * queryRule.addLike(&quot;username&quot;, user.getUsername());
     * queryRule.addLike(&quot;monicker&quot;, user.getMonicker());
     * queryRule.addBetween(&quot;id&quot;, lowerId, upperId);
     * queryRule.addDescOrder(&quot;id&quot;);
     * queryRule.addAscOrder(&quot;username&quot;);
     * page = userService.select(queryRule, pageNo, pageSize);
     * </code>
     * </pre>
     *
     * @param queryRule 查询规则
     * @param pageNo    页号,从1开始
     * @param pageSize  每页的记录条数
     * @return 查询出的结果Page
     */
    public Page<T> select(QueryRule queryRule, final int pageNo, final int pageSize) throws Exception {
        QueryRuleSqlBuilder bulider = new QueryRuleSqlBuilder(queryRule);
        Object[] values = bulider.getValues();
        String ws = removeFirstAnd(bulider.getWhereSql());
        String whereSql = ("".equals(ws) ? ws : (" where " + ws));
        String countSql = "select count(1) from " + getTableName() + whereSql;
        long count = (Long) this.jdbcTemplateReadOnly().queryForMap(countSql, values).get("count(1)");
        if (count == 0) {
            return new Page<T>();
        }
        long start = (pageNo - 1) * pageSize;
        // 有数据的情况下,继续查询
        String orderSql = bulider.getOrderSql();
        orderSql = (StringUtils.isEmpty(orderSql) ? " " : (" order by " + orderSql));
        String sql = "select " + op.allColumn + " from " + getTableName() + whereSql + orderSql + " limit " + start + "," + pageSize;
        List<T> list = (List<T>) this.jdbcTemplateReadOnly().query(sql, this.op.rowMapper, values);
        log.debug(sql);
        return new Page<T>(start, count, pageSize, list);
    }


    /**
     * 分页查询特殊SQL语句
     *
     * @param sql      语句
     * @param param    查询条件
     * @param pageNo   页码
     * @param pageSize 每页内容
     * @return
     */
    protected Page<Map<String, Object>> selectBySqlToPage(String sql, Map<String, ?> param, final int pageNo, final int pageSize) throws Exception {
        String countSql = "select count(1) from (" + sql + ") a";
        long count = (Long) this.jdbcTemplateReadOnly().queryForMap(countSql, param).get("count(1)");

//    long count = this.jdbcTemplateReadOnly().queryForMap(countSql, param);
        if (count == 0) {
            return new Page<Map<String, Object>>();
        }
        long start = (pageNo - 1) * pageSize;
        // 有数据的情况下,继续查询
        sql = sql + " limit " + start + "," + pageSize;
        List<Map<String, Object>> list = (List<Map<String, Object>>) this.jdbcTemplateReadOnly().queryForList(sql, param);
        log.debug(sql);
        return new Page<Map<String, Object>>(start, count, pageSize, list);
    }


    /**
     * 分页查询特殊SQL语句
     *
     * @param sql      语句
     * @param param    查询条件
     * @param pageNo   页码
     * @param pageSize 每页内容
     * @return
     */
    public Page<Map<String, Object>> selectBySqlToPage(String sql, Object[] param, final int pageNo, final int pageSize) throws Exception {
        String countSql = "select count(1) from (" + sql + ") a";

        long count = (Long) this.jdbcTemplateReadOnly().queryForMap(countSql, param).get("count(1)");
//    long count = this.jdbcTemplateReadOnly().queryForLong(countSql, param);
        if (count == 0) {
            return new Page<Map<String, Object>>();
        }
        long start = (pageNo - 1) * pageSize;
        sql = sql + " limit " + start + "," + pageSize;
        List<Map<String, Object>> list = (List<Map<String, Object>>) this.jdbcTemplateReadOnly().queryForList(sql, param);
        log.debug(sql);
        return new Page<Map<String, Object>>(start, count, pageSize, list);
    }

    /**
     * 根据<属性名和属属性值Map查询符合条件的唯一对象,没符合条件的记录返回null.<br>
     * 例如,如下语句查找sex=1,age=18的所有记录:
     *
     * <pre>
     *     <code>
     * Map properties = new HashMap();
     * properties.put(&quot;sex&quot;, &quot;1&quot;);
     * properties.put(&quot;age&quot;, 18);
     * User user = service.selectUnique(properties);
     * </code>
     * </pre>
     *
     * @param properties 属性值Map,key为属性名,value为属性值
     * @return 符合条件的唯一对象,没符合条件的记录返回null.
     */
    protected T selectUnique(Map<String, Object> properties) throws Exception {
        QueryRule queryRule = QueryRule.getInstance();
        for (String key : properties.keySet()) {
            queryRule.andEqual(key, properties.get(key));
        }
        return selectUnique(queryRule);
    }

    /**
     * 根据查询规则查询符合条件的唯一象,没符合条件的记录返回null.<br>
     * <pre>
     *     <code>
     * QueryRule queryRule = QueryRule.getInstance();
     * queryRule.addLike(&quot;username&quot;, user.getUsername());
     * queryRule.addLike(&quot;monicker&quot;, user.getMonicker());
     * queryRule.addBetween(&quot;id&quot;, lowerId, upperId);
     * User user = service.selectUnique(queryRule);
     * </code>
     * </pre>
     *
     * @param queryRule 查询规则
     * @return 符合条件的唯一对象,没符合条件的记录返回null.
     */
    protected T selectUnique(QueryRule queryRule) throws Exception {
        List<T> list = select(queryRule);
        if (list.size() == 0) {
            return null;
        } else if (list.size() == 1) {
            return list.get(0);
        } else {
            throw new IllegalStateException("findUnique return " + list.size() + " record(s).");
        }
    }


    /**
     * 根据当前list进行相应的分页返回
     *
     * @param objList
     * @param pageNo
     * @param pageSize
     * @return Page
     */
    protected Page<T> pagination(List<T> objList, int pageNo, int pageSize) throws Exception {
        List<T> objectArray = new ArrayList<T>(0);
        int startIndex = (pageNo - 1) * pageSize;
        int endIndex = pageNo * pageSize;
        if (endIndex >= objList.size()) {
            endIndex = objList.size();
        }
        for (int i = startIndex; i < endIndex; i++) {
            objectArray.add(objList.get(i));
        }
        return new Page<T>(startIndex, objList.size(), pageSize, objectArray);
    }

    /**
     * 合并PO List对象.(如果POJO中的值为null,则继续使用PO中的值)
     *
     * @param pojoList 传入的POJO的List
     * @param poList   传入的PO的List
     * @param idName   ID字段名称
     */
    protected void mergeList(List<T> pojoList, List<T> poList, String idName) throws Exception {
        mergeList(pojoList, poList, idName, false);
    }

    /**
     * 合并PO List对象.
     *
     * @param pojoList   传入的POJO的List
     * @param poList     传入的PO的List
     * @param idName     ID字段名称
     * @param isCopyNull 是否拷贝null(当POJO中的值为null时,如果isCopyNull=ture,则用null,否则继续使用PO中的值)
     */
    protected void mergeList(List<T> pojoList, List<T> poList, String idName, boolean isCopyNull) throws Exception {
        Map<Object, Object> map = new HashMap<Object, Object>();
        Map<String, PropertyMapping> pm = op.mappings;
        for (Object element : pojoList) {
            Object key;
            try {
                key = pm.get(idName).getter.invoke(element);
                map.put(key, element);
            } catch (Exception e) {
                throw new IllegalArgumentException(e);
            }
        }
        for (Iterator<T> it = poList.iterator(); it.hasNext(); ) {
            T element = it.next();
            try {
                Object key = pm.get(idName).getter.invoke(element);
                if (!map.containsKey(key)) {
                    delete(element);
                    it.remove();
                } else {
                    DataUtils.copySimpleObject(map.get(key), element, isCopyNull);
                }
            } catch (Exception e) {
                throw new IllegalArgumentException(e);
            }
        }
        T[] pojoArray = (T[]) pojoList.toArray();
        for (int i = 0; i < pojoArray.length; i++) {
            T element = pojoArray[i];
            try {
                Object key = pm.get(idName).getter.invoke(element);
                if (key == null) {
                    poList.add(element);
                }
            } catch (Exception e) {
                throw new IllegalArgumentException(e);
            }
        }
    }

    private String removeFirstAnd(String sql) {
        if (StringUtils.isEmpty(sql)) {
            return sql;
        }
        return sql.trim().toLowerCase().replaceAll("^\\s*and", "") + " ";
    }

    private EntityOperation<T> getOp() {
        return this.op;
    }


    /**
     * ResultSet -> Object
     *
     * @param <T>
     * @param rs
     * @param obj
     */
    private <T> T populate(ResultSet rs, T obj) {
        try {
            ResultSetMetaData metaData = rs.getMetaData(); // 取得结果集的元元素
            int colCount = metaData.getColumnCount(); // 取得所有列的个数
            Field[] fields = obj.getClass().getDeclaredFields();
            for (int i = 0; i < fields.length; i++) {
                Field f = fields[i];
                // rs的游标从1开始,需要注意
                for (int j = 1; j <= colCount; j++) {
                    Object value = rs.getObject(j);
                    String colName = metaData.getColumnName(j);
                    if (!f.getName().equalsIgnoreCase(colName)) {
                        continue;
                    }

                    // 如果列名中有和字段名一样的,则设置值
                    try {
                        BeanUtils.copyProperty(obj, f.getName(), value);
                    } catch (Exception e) {
                        log.warn("BeanUtils.copyProperty error, field name: "
                                + f.getName() + ", error: " + e);
                    }

                }
            }
        } catch (Exception e) {
            log.warn("populate error...." + e);
        }
        return obj;
    }

    /**
     * 封装一下JdbcTemplate的queryForObject(默认查不到会抛异常)方法,
     *
     * @param sql
     * @param mapper
     * @param args
     * @return 如查询不到,返回null,不抛异常;查询到多个,也抛出异常
     */
    private <T> T selectForObject(String sql, RowMapper<T> mapper,
                                  Object... args) {
        List<T> results = this.jdbcTemplateReadOnly().query(sql, mapper, args);
        return DataAccessUtils.singleResult(results);
    }

    protected byte[] getBlobColumn(ResultSet rs, int columnIndex)
            throws SQLException {
        try {
            Blob blob = rs.getBlob(columnIndex);
            if (blob == null) {
                return null;
            }

            InputStream is = blob.getBinaryStream();
            ByteArrayOutputStream bos = new ByteArrayOutputStream();

            if (is == null) {
                return null;
            } else {
                byte buffer[] = new byte[64];
                int c = is.read(buffer);
                while (c > 0) {
                    bos.write(buffer, 0, c);
                    c = is.read(buffer);
                }
                return bos.toByteArray();
            }
        } catch (IOException e) {
            throw new SQLException(
                    "Failed to read BLOB column due to IOException: "
                            + e.getMessage());
        }
    }

    protected void setBlobColumn(PreparedStatement stmt, int parameterIndex,
                                 byte[] value) throws SQLException {
        if (value == null) {
            stmt.setNull(parameterIndex, Types.BLOB);
        } else {
            stmt.setBinaryStream(parameterIndex,
                    new ByteArrayInputStream(value), value.length);
        }
    }

    protected String getClobColumn(ResultSet rs, int columnIndex)
            throws SQLException {
        try {
            Clob clob = rs.getClob(columnIndex);
            if (clob == null) {
                return null;
            }

            StringBuffer ret = new StringBuffer();
            InputStream is = clob.getAsciiStream();

            if (is == null) {
                return null;
            } else {
                byte buffer[] = new byte[64];
                int c = is.read(buffer);
                while (c > 0) {
                    ret.append(new String(buffer, 0, c));
                    c = is.read(buffer);
                }
                return ret.toString();
            }
        } catch (IOException e) {
            throw new SQLException(
                    "Failed to read CLOB column due to IOException: "
                            + e.getMessage());
        }
    }

    protected void setClobColumn(PreparedStatement stmt, int parameterIndex,
                                 String value) throws SQLException {
        if (value == null) {
            stmt.setNull(parameterIndex, Types.CLOB);
        } else {
            stmt.setAsciiStream(parameterIndex,
                    new ByteArrayInputStream(value.getBytes()), value.length());
        }
    }

    /**
     * 分页查询支持,支持简单的sql查询分页(复杂的查询,请自行编写对应的方法)
     *
     * @param <T>
     * @param sql
     * @param rowMapper
     * @param args
     * @param pageNo
     * @param pageSize
     * @return
     */
    private <T> Page simplePageQuery(String sql, RowMapper<T> rowMapper, Map<String, ?> args, long pageNo, long pageSize) {
        long start = (pageNo - 1) * pageSize;
        return simplePageQueryByStart(sql, rowMapper, args, start, pageSize);
    }

    /**
     * @param sql
     * @param rowMapper
     * @param args
     * @param start
     * @param pageSize
     * @return
     */
    private <T> Page simplePageQueryByStart(String sql, RowMapper<T> rowMapper, Map<String, ?> args, long start, long pageSize) {
        // 首先查询总数
        String countSql = "select count(*) " + removeSelect(removeOrders(sql));

        long count = (Long) this.jdbcTemplateReadOnly().queryForMap(countSql, args).get("count(1)");
//    long count = this.jdbcTemplateReadOnly().queryForLong(countSql, args);
        if (count == 0) {
            log.debug("no result..");
            return new Page();
        }
        // 有数据的情况下,继续查询
        sql = sql + " limit " + start + "," + pageSize;
        log.debug(StringUtils.format("[Execute SQL]sql:{0},params:{1}", sql, args));
        List<T> list = this.jdbcTemplateReadOnly().query(sql, rowMapper, args);
        return new Page(start, count, (int) pageSize, list);
    }

    protected long queryCount(String sql, Map<String, ?> args) {
        String countSql = "select count(1) " + removeSelect(removeOrders(sql));

        return (Long) this.jdbcTemplateReadOnly().queryForMap(countSql, args).get("count(1)");
    }

    protected <T> List<T> simpleListQueryByStart(String sql, RowMapper<T> rowMapper,
                                                 Map<String, ?> args, long start, long pageSize) {

        sql = sql + " limit " + start + "," + pageSize;
        log.debug(StringUtils.format("[Execute SQL]sql:{0},params:{1}", sql, args));
        List<T> list = this.jdbcTemplateReadOnly().query(sql, rowMapper, args);
        if (list == null) {
            return new ArrayList<T>();
        }
        return list;
    }

    /**
     * 分页查询支持,支持简单的sql查询分页(复杂的查询,请自行编写对应的方法)
     *
     * @param sql
     * @param rm
     * @param args
     * @param pageNo
     * @param pageSize
     * @return
     */
    private Page simplePageQueryNotT(String sql, RowMapper rm, Map<String, ?> args, long pageNo, long pageSize) {
        // 首先查询总数
        String countSql = "select count(*) " + removeSelect(removeOrders(sql));
        long count = (Long) this.jdbcTemplateReadOnly().queryForMap(countSql, args).get("count(1)");
        if (count == 0) {
            log.debug("no result..");
            return new Page();
        }
        // 有数据的情况下,继续查询
        long start = (pageNo - 1) * pageSize;
        sql = sql + " limit " + start + "," + pageSize;
        log.debug(StringUtils.format("[Execute SQL]sql:{0},params:{1}", sql, args));
        List list = this.jdbcTemplateReadOnly().query(sql, rm, args);
        return new Page(start, count, (int) pageSize, list);
    }

    /**
     * 去掉order
     *
     * @param sql
     * @return
     */
    private 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();
    }

    /**
     * 去掉select
     *
     * @param sql
     * @return
     */
    private String removeSelect(String sql) {
        int beginPos = sql.toLowerCase().indexOf("from");
        return sql.substring(beginPos);
    }


    private long getMaxId(String table, String column) {
        String sql = "SELECT max(" + column + ") FROM " + table + " ";
        long maxId = (Long) this.jdbcTemplateReadOnly().queryForMap(sql).get("max(" + column + ")");
        return maxId;
    }

    /**
     * 生成简单对象UPDATE语句,简化sql拼接
     *
     * @param tableName
     * @param pkName
     * @param pkValue
     * @param params
     * @return
     */
    private String makeSimpleUpdateSql(String tableName, String pkName, Object pkValue, Map<String, Object> params) {
        if (StringUtils.isEmpty(tableName) || params == null || params.isEmpty()) {
            return "";
        }

        StringBuffer sb = new StringBuffer();
        sb.append("update ").append(tableName).append(" set ");
        //添加参数
        Set<String> set = params.keySet();
        int index = 0;
        for (String key : set) {
//        sb.append(key).append(" = :").append(key);
            sb.append(key).append(" = ?");
            if (index != set.size() - 1) {
                sb.append(",");
            }
            index++;
        }
//    sb.append(" where ").append(pkName).append(" = :").append(pkName) ;
        sb.append(" where ").append(pkName).append(" = ?");
        params.put("where_" + pkName, params.get(pkName));

        return sb.toString();
    }


    /**
     * 生成简单对象UPDATE语句,简化sql拼接
     *
     * @param pkName
     * @param pkValue
     * @param params
     * @return
     */
    private String makeSimpleUpdateSql(String pkName, Object pkValue, Map<String, Object> params) {
        if (StringUtils.isEmpty(getTableName()) || params == null || params.isEmpty()) {
            return "";
        }

        StringBuffer sb = new StringBuffer();
        sb.append("update ").append(getTableName()).append(" set ");
        //添加参数
        Set<String> set = params.keySet();
        int index = 0;
        for (String key : set) {
            sb.append(key).append(" = :").append(key);
            if (index != set.size() - 1) {
                sb.append(",");
            }
            index++;
        }
        sb.append(" where ").append(pkName).append(" = :").append(pkName);

        return sb.toString();
    }


    /**
     * 生成对象INSERT语句,简化sql拼接
     *
     * @param tableName
     * @param params
     * @return
     */
    private String makeSimpleReplaceSql(String tableName, Map<String, Object> params) {
        if (StringUtils.isEmpty(tableName) || params == null || params.isEmpty()) {
            return "";
        }
        StringBuffer sb = new StringBuffer();
        sb.append("replace into ").append(tableName);

        StringBuffer sbKey = new StringBuffer();
        StringBuffer sbValue = new StringBuffer();

        sbKey.append("(");
        sbValue.append("(");
        //添加参数
        Set<String> set = params.keySet();
        int index = 0;
        for (String key : set) {
            sbKey.append(key);
            sbValue.append(" :").append(key);
            if (index != set.size() - 1) {
                sbKey.append(",");
                sbValue.append(",");
            }
            index++;
        }
        sbKey.append(")");
        sbValue.append(")");

        sb.append(sbKey).append("VALUES").append(sbValue);

        return sb.toString();
    }

    /**
     * 生成对象INSERT语句,简化sql拼接
     *
     * @param tableName
     * @param params
     * @return
     */
    private String makeSimpleReplaceSql(String tableName, Map<String, Object> params, List<Object> values) {
        if (StringUtils.isEmpty(tableName) || params == null || params.isEmpty()) {
            return "";
        }
        StringBuffer sb = new StringBuffer();
        sb.append("replace into ").append(tableName);

        StringBuffer sbKey = new StringBuffer();
        StringBuffer sbValue = new StringBuffer();

        sbKey.append("(");
        sbValue.append("(");
        //添加参数
        Set<String> set = params.keySet();
        int index = 0;
        for (String key : set) {
            sbKey.append(key);
            sbValue.append(" ?");
            if (index != set.size() - 1) {
                sbKey.append(",");
                sbValue.append(",");
            }
            index++;
            values.add(params.get(key));
        }
        sbKey.append(")");
        sbValue.append(")");

        sb.append(sbKey).append("VALUES").append(sbValue);

        return sb.toString();
    }


    /**
     * 生成对象INSERT语句,简化sql拼接
     *
     * @param tableName
     * @param params
     * @return
     */
    private String makeSimpleInsertSql(String tableName, Map<String, Object> params) {
        if (StringUtils.isEmpty(tableName) || params == null || params.isEmpty()) {
            return "";
        }
        StringBuffer sb = new StringBuffer();
        sb.append("insert into ").append(tableName);

        StringBuffer sbKey = new StringBuffer();
        StringBuffer sbValue = new StringBuffer();

        sbKey.append("(");
        sbValue.append("(");
        //添加参数
        Set<String> set = params.keySet();
        int index = 0;
        for (String key : set) {
            sbKey.append(key);
//       sbValue.append(" :").append(key);
            sbValue.append(" ?");
            if (index != set.size() - 1) {
                sbKey.append(",");
                sbValue.append(",");
            }
            index++;
        }
        sbKey.append(")");
        sbValue.append(")");

        sb.append(sbKey).append("VALUES").append(sbValue);

        return sb.toString();
    }

    /**
     * 生成对象INSERT语句,简化sql拼接
     *
     * @param tableName
     * @param params
     * @return
     */
    private String makeSimpleInsertSql(String tableName, Map<String, Object> params, List<Object> values) {
        if (StringUtils.isEmpty(tableName) || params == null || params.isEmpty()) {
            return "";
        }
        StringBuffer sb = new StringBuffer();
        sb.append("insert into ").append(tableName);

        StringBuffer sbKey = new StringBuffer();
        StringBuffer sbValue = new StringBuffer();

        sbKey.append("(");
        sbValue.append("(");
        //添加参数
        Set<String> set = params.keySet();
        int index = 0;
        for (String key : set) {
            sbKey.append(key);
            sbValue.append(" ?");
            if (index != set.size() - 1) {
                sbKey.append(",");
                sbValue.append(",");
            }
            index++;
            values.add(params.get(key));
        }
        sbKey.append(")");
        sbValue.append(")");

        sb.append(sbKey).append("VALUES").append(sbValue);

        return sb.toString();
    }


    private Serializable doInsertRuturnKey(Map<String, Object> params) {
        final List<Object> values = new ArrayList<Object>();
        final String sql = makeSimpleInsertSql(getTableName(), params, values);
        KeyHolder keyHolder = new GeneratedKeyHolder();
        final JdbcTemplate jdbcTemplate = new JdbcTemplate(getDataSourceWrite());
        try {

            jdbcTemplate.update(new PreparedStatementCreator() {
                public PreparedStatement createPreparedStatement(

                        Connection con) throws SQLException {
                    PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

                    for (int i = 0; i < values.size(); i++) {
                        ps.setObject(i + 1, values.get(i) == null ? null : values.get(i));

                    }
                    return ps;
                }

            }, keyHolder);
        } catch (DataAccessException e) {
            log.error("error", e);
        }


        if (keyHolder == null) {
            return "";
        }


        Map<String, Object> keys = keyHolder.getKeys();
        if (keys == null || keys.size() == 0 || keys.values().size() == 0) {
            return "";
        }
        Object key = keys.values().toArray()[0];
        if (key == null || !(key instanceof Serializable)) {
            return "";
        }
        if (key instanceof Number) {
            //Long k = (Long) key;
            Class clazz = key.getClass();
//       return clazz.cast(key);
            return (clazz == int.class || clazz == Integer.class) ? ((Number) key).intValue() : ((Number) key).longValue();


        } else if (key instanceof String) {
            return (String) key;
        } else {
            return (Serializable) key;
        }


    }


    /**
     * 生成默认的对象UPDATE语句,简化sql拼接
     *
     * @param pkValue
     * @param params
     * @return
     */
    private String makeDefaultSimpleUpdateSql(Object pkValue, Map<String, Object> params) {
        return this.makeSimpleUpdateSql(getTableName(), getPKColumn(), pkValue, params);
    }

    /**
     * 生成默认的对象INSERT语句,简化sql拼接
     *
     * @param params
     * @return
     */
    private String makeDefaultSimpleInsertSql(Map<String, Object> params) {
        return this.makeSimpleInsertSql(this.getTableName(), params);
    }

    /**
     * 获取一个实例对象
     *
     * @param tableName
     * @param pkName
     * @param pkValue
     * @param rm
     * @return
     */
    private Object doLoad(String tableName, String pkName, Object pkValue, RowMapper rm) {
        StringBuffer sb = new StringBuffer();
        sb.append("select * from ").append(tableName).append(" where ").append(pkName).append(" = ?");
        List<Object> list = this.jdbcTemplateReadOnly().query(sb.toString(), rm, pkValue);
        if (list == null || list.isEmpty()) {
            return null;
        }
        return list.get(0);
    }

    /**
     * 获取默认的实例对象
     *
     * @param <T>
     * @param pkValue
     * @param rowMapper
     * @return
     */
    private <T> T doLoad(Object pkValue, RowMapper<T> rowMapper) {
        Object obj = this.doLoad(getTableName(), getPKColumn(), pkValue, rowMapper);
        if (obj != null) {
            return (T) obj;
        }
        return null;
    }


    /**
     * 删除实例对象,返回删除记录数
     *
     * @param tableName
     * @param pkName
     * @param pkValue
     * @return
     */
    private int doDelete(String tableName, String pkName, Object pkValue) {
        StringBuffer sb = new StringBuffer();
        sb.append("delete from ").append(tableName).append(" where ").append(pkName).append(" = ?");
        int ret = this.jdbcTemplateWrite().update(sb.toString(), pkValue);
        return ret;
    }

    /**
     * 删除默认实例对象,返回删除记录数
     *
     * @param pkValue
     * @return
     */
    private int doDelete(Object pkValue) {
        return this.doDelete(getTableName(), getPKColumn(), pkValue);
    }

    /**
     * 更新实例对象,返回删除记录数
     *
     * @param tableName
     * @param pkName
     * @param pkValue
     * @param params
     * @return
     */
    private int doUpdate(String tableName, String pkName, Object pkValue, Map<String, Object> params) {
        params.put(pkName, pkValue);
        String sql = this.makeSimpleUpdateSql(tableName, pkName, pkValue, params);
        int ret = this.jdbcTemplateWrite().update(sql, params.values().toArray());
        return ret;
    }

    /**
     * 更新实例对象,返回删除记录数
     *
     * @param pkName
     * @param pkValue
     * @param params
     * @return
     */
    private int doUpdate(String pkName, Object pkValue, Map<String, Object> params) {
        params.put(pkName, pkValue);
        String sql = this.makeSimpleUpdateSql(pkName, pkValue, params);
        int ret = this.jdbcTemplateWrite().update(sql, params.values().toArray());
        return ret;
    }

    /**
     * 更新实例对象,返回删除记录数
     *
     * @param pkValue
     * @param params
     * @return
     */
    private int doUpdate(Object pkValue, Map<String, Object> params) {
        //
        String sql = this.makeDefaultSimpleUpdateSql(pkValue, params);
        params.put(this.getPKColumn(), pkValue);
        int ret = this.jdbcTemplateWrite().update(sql, params.values().toArray());
        return ret;
    }


    private boolean doReplace(Map<String, Object> params) {
        String sql = this.makeSimpleReplaceSql(this.getTableName(), params);
        int ret = this.jdbcTemplateWrite().update(sql, params.values().toArray());
        return ret > 0;
    }

    private boolean doReplace(String tableName, Map<String, Object> params) {
        String sql = this.makeSimpleReplaceSql(tableName, params);
        int ret = this.jdbcTemplateWrite().update(sql, params.values().toArray());
        return ret > 0;
    }


    /**
     * 插入
     *
     * @param tableName
     * @param params
     * @return
     */
    private boolean doInsert(String tableName, Map<String, Object> params) {
        String sql = this.makeSimpleInsertSql(tableName, params);
        int ret = this.jdbcTemplateWrite().update(sql, params.values().toArray());
        return ret > 0;
    }

    /**
     * 插入
     *
     * @param params
     * @return
     */
    private boolean doInsert(Map<String, Object> params) {
        String sql = this.makeSimpleInsertSql(this.getTableName(), params);
        int ret = this.jdbcTemplateWrite().update(sql, params.values().toArray());
        return ret > 0;
    }

    /**
     * 获取主键列名称 建议子类重写
     *
     * @return
     */
    protected abstract String getPKColumn();

    protected abstract void setDataSource(DataSource dataSource);

    private Map<String, Object> convertMap(Object obj) {
        Map<String, Object> map = new HashMap<String, Object>();

        List<FieldInfo> getters = TypeUtils.computeGetters(obj.getClass(), null);
        for (int i = 0, len = getters.size(); i < len; i++) {
            FieldInfo fieldInfo = getters.get(i);
            String name = fieldInfo.getName();
            try {
                Object value = fieldInfo.get(obj);
                map.put(name, value);
            } catch (Exception e) {
                log.error(String.format("convertMap error object:%s  field: %s", obj.toString(), name));
            }
        }

        return map;
    }

}

动态数据源切换的底层原理

可以使用动态数据源实现分库分表的相关操作。

DynamicDataSourceEntry

public class DynamicDataSourceEntry {

    // 默认数据源
    public final static String DEFAULT_SOURCE = null;

    private final static ThreadLocal<String> local = new ThreadLocal<String>();

    /**
     * 清空数据源
     */
    public void clear() {
        local.remove();
    }

    /**
     * 获取当前正在使用的数据源名字
     *
     * @return String
     */
    public String get() {
        return local.get();
    }

    /**
     * 还原指定切面的数据源
     *
     * @param joinPoint
     */
    public void restore(JoinPoint join) {
        local.set(DEFAULT_SOURCE);
    }

    /**
     * 还原当前切面的数据源
     */
    public void restore() {
        local.set(DEFAULT_SOURCE);
    }

    /**
     * 设置已知名字的数据源
     *
     * @param dataSource
     */
    public void set(String source) {
        local.set(source);
    }

    /**
     * 根据年份动态设置数据源
     *
     * @param year
     */
    public void set(int year) {
        local.set("DB_" + year);
    }
}

DynamicDataSource

public class DynamicDataSource extends AbstractRoutingDataSource {

    //entry的目的,主要是用来给每个数据源打个标记
    private DynamicDataSourceEntry dataSourceEntry;

    @Override
    protected Object determineCurrentLookupKey() {
        return this.dataSourceEntry.get();
    }

    public void setDataSourceEntry(DynamicDataSourceEntry dataSourceEntry) {
        this.dataSourceEntry = dataSourceEntry;
    }

    public DynamicDataSourceEntry getDataSourceEntry() {
        return this.dataSourceEntry;
    }

}

业务代码

实体类

member类创建过,在创建一个order类。

@Entity
@Table(name = "t_order")
public class Order implements Serializable {
    private Long id;
    @Column(name = "mid")
    private Long memberId;
    private String detail;
    private Long createTime;
    private String createTimeFmt;

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", memberId=" + memberId +
                ", detail='" + detail + '\'' +
                ", createTime=" + createTime +
                ", createTimeFmt='" + createTimeFmt + '\'' +
                '}';
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public Long getMemberId() {
        return memberId;
    }

    public void setMemberId(Long memberId) {
        this.memberId = memberId;
    }

    public String getDetail() {
        return detail;
    }

    public void setDetail(String detail) {
        this.detail = detail;
    }

    public Long getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Long createTime) {
        this.createTime = createTime;
    }

    public String getCreateTimeFmt() {
        return createTimeFmt;
    }

    public void setCreateTimeFmt(String createTimeFmt) {
        this.createTimeFmt = createTimeFmt;
    }
}

Dao类

@Repository
public class MemberDao extends BaseDaoSupport<Member, Long> {

    @Override
    protected String getPKColumn() {
        return "id";
    }


    @Resource(name = "dataSource")
    public void setDataSource(DataSource dataSource) {
        super.setDataSourceReadOnly(dataSource);
        super.setDataSourceWrite(dataSource);
    }


    public List<Member> selectAll() throws Exception {
        QueryRule queryRule = QueryRule.getInstance();
//        queryRule.andLike("name","cc%");
        return super.select(queryRule);
    }


    public Page<Member> selectForPage(int pageNo, int pageSize) throws Exception {
        QueryRule queryRule = QueryRule.getInstance();
        queryRule.andLike("name", "cc%");
        Page<Member> page = super.select(queryRule, pageNo, pageSize);
        return page;
    }

    public void select() throws Exception {
        String sql = "";
        List<Map<String, Object>> result = super.selectBySql(sql);
//        System.out.println(JSON.parseObject(JSON.toJSONString(result)),Member.class);
    }

    public boolean insert(Member entity) throws Exception {
//        super.setTableName("t_mmmmm");
        return super.insert(entity);
    }
}
@Repository
public class OrderDao extends BaseDaoSupport<Order, Long> {

    private SimpleDateFormat yearFormat = new SimpleDateFormat("yyyy");
    private SimpleDateFormat monthFormat = new SimpleDateFormat("MM");
    private SimpleDateFormat fullDataFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    private DynamicDataSource dataSource;

    @Override
    protected String getPKColumn() {
        return "id";
    }

    /**
     * 动态数据源
     *
     * @param dataSource
     */
    @Resource(name = "dynamicDataSource")
    public void setDataSource(DataSource dataSource) {
        this.dataSource = (DynamicDataSource) dataSource;
        this.setDataSourceReadOnly(dataSource);
        this.setDataSourceWrite(dataSource);
    }

    public boolean insertOne(Order order) throws Exception {
        Date date = null;
        if (order.getCreateTime() == null) {
            date = new Date();
            order.setCreateTime(date.getTime());
        } else {
            date = new Date(order.getCreateTime());
        }
        //分库 : 根据年份将数据插入到不同的库中
        Integer dbRouter = Integer.valueOf(yearFormat.format(date));
        System.out.println("自动分配到【DB_" + dbRouter + "】数据源");
        this.dataSource.getDataSourceEntry().set(dbRouter);

        order.setCreateTimeFmt(fullDataFormat.format(date));

        //分表 : 根据月份将输入插入到不同的表中
        String month = monthFormat.format(date);

        super.setTableName(super.getTableName() + "_" + String.valueOf(Integer.parseInt(month) - 1));

        Long orderId = super.insertAndReturnId(order);

        //还原默认表名
        super.restoreTableName();

        order.setId(orderId);
        return orderId > 0;
    }

}

相关配置文件

直接复制项目中的即可,但是需要修改db.propertise和application-db.xml文件中的相关信息。

需要注意的是如果采用分库分表,需要提前创建相关库表。

如spring-db-2021库t_order_4表。

项目地址

orm-mini

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

参与评论 您还未登录,请先 登录 后发表或查看评论
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

、楽.

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值