通常我们在开发Java企业级应用的时候使用的技术大部分是Spring、Hibernate、mybatis、Struts2等。尤其是Spring，相信这个庞大而优雅的技术体系你已经离不开了，在我们项目代码中基本是骨干力量的存在。

而我们使用的ORM框架大多数也是Hibernate、mybatis或者Spring提供的jdbc简单封装的JdbcTemplate。如果我们的项目组开发人员对所选型的ORM框架很熟悉并能熟练使用是再好不过了，但是大部分情况下项目组成员的技术水平是参差不齐的，有时会踩到所选型ORM框架的坑。比如Hibernate很容易引起性能问题，mybatis在重构时很麻烦等等增加了项目的交付风险。

本文希望能在Dao层的开发上做的简单，健壮，提高开发效率：

1 对JdbcTemplate做一层简单的封装，能够对单表的CRUD做到无需写SQL语句

2 在重构SQL代码时对SQL层做少量修改或者不修改

我们开始吧！

我们先考虑一下：如果做到CRUD的不需要写SQL，这也意味着SQL是要自动生成的。

我们先看SQL语句的组成：

1 添加语句： insert into tableName (id, name, ...) values (1, 'name', ...);

2 修改语句：update tableName set name = 'name', age = 'age' where id = 'id';

3 删除语句：delete from tableName where id = 'id';

4 查询语句：select id, name from tableName where age > 18;

一般我们做JAVA企业级开发都会有领域模型的概念，这个领域模型会对应一个数据库表并包括这个数据库表的所有字段。那么这时我们可以利用领域模型的字段生成对应的SQL语句。这里我们先借用JPA的注解完成领域模型属性与数据库表的映射，熟悉hibernate的朋友一定不会陌生，当然你也可以自己定义注解。

如下领域模型：

package com.applet.model;

import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import com.fasterxml.jackson.databind.ser.std.ToStringSerializer;
import com.applet.base.BaseModel;
import com.applet.enumeration.YesNoEnum;
import com.applet.utils.DateUtils;

import javax.persistence.*;
import java.io.Serializable;
import java.util.Date;

@Entity
@Table(name = "TS_ROLE")
public class Role implements Serializable {

    /**
     * <p>
     * Field serialVersionUID: 序列号
     * </p>
     */
    private static final long serialVersionUID = 1L;

    //主键
    @Id
    @Column
    @JsonSerialize(using = ToStringSerializer.class)
    protected Long id;

    // 名称
    @Column
    private String name;

    // 状态（1启用，2停用）
    @Column
    private Integer state;

    // 创建人id
    @Column
    @JsonSerialize(using = ToStringSerializer.class)
    private Long createId;

    // 创建日期
    @Temporal(TemporalType.TIMESTAMP)
    @Column
    private Date createTime;

    // 是否系统角色（1是，2否）
    @Column
    private Integer isSys;

    // 描述
    @Column
    private String remark;

    public Long getId() {
        return id;
    }

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

    public String getStateStr() {
        return YesNoEnum.valueOfValidateLabel(state);
    }

    public String getIsSysStr() {
        return YesNoEnum.valueOf(isSys);
    }

    public String getCreateTimeStr() {
        if (createTime != null) {
            return DateUtils.dateTimeToString(createTime);
        }
        return null;
    }

    public String getName() {
        return name;
    }

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

    public Integer getState() {
        return state;
    }

    public void setState(Integer state) {
        this.state = state;
    }

    public Long getCreateId() {
        return createId;
    }

    public void setCreateId(Long createId) {
        this.createId = createId;
    }

    public Date getCreateTime() {
        return createTime;
    }

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

    public Integer getIsSys() {
        return isSys;
    }

    public void setIsSys(Integer isSys) {
        this.isSys = isSys;
    }

    public String getRemark() {
        return remark;
    }

    public void setRemark(String remark) {
        this.remark = remark;
    }
}

我们的基础的dao接口如下：

package com.applet.base;

import java.io.Serializable;
import java.util.List;

public interface BaseDao<T extends BaseModel, PK extends Serializable>{
    
    /**
     * 判断某一字段是否重复
     * @param id 实体id
     * @param filedValue 字段值
     * @param fieldName 字段名称
     * @return
     */
    //public boolean isDuplicateField(PK id, Object filedValue, String fieldName);
    
    /**
     * <p>Description: 添加实体</p>
     * @param t 实体对象
     */
    public int insert(T t);

    /**
     * <p>Description: 批量添加实体</p>
     * @param list 实体对象列表
     */
    public int batchInsert(final List<T> list);

    /**
     * <p>Description: 更新实体，字段值为null的不更新</p>
     * @param t 实体对象
     */
    public int update(T t);

    /**
     * <p>Description: 更新实体</p>
     * @param t 实体对象
     */
    public int updateForce(T t);

    /**
     * <p>Description: 根据id删除实体</p>
     * @param id 实体id值
     */
    public int delete(PK id);

    /**
     * <p>Description: 批量删除实体</p>
     * @param ids 实体id值数组
     */
    public int delete(PK[] ids);

    /**
     * <p>Description: 按条件查询实体列表</p>
     * @param wb QueryCondition对象
     * @return 实体列表
     */
    //public List<T> query(QueryCondition wb);

    /**
     * <p>Description: 按条件查询实体数量</p>
     * @param wb QueryCondition对象
     * @return 实体数量
     */
    //public int count(QueryCondition wb);

    /**
     * <p>Description: 根据id查询实体</p>
     * @param id 实体id值
     * @return 实体对象
     */
    public T load(PK id);

    /**
     * <p>Description: 按条件删除实体</p>
     * @param wb QueryCondition对象
     */
    //public int deleteByCondition(QueryCondition wb);

    /**
     * <p>Description: 分页查询</p>
     * @param wb QueryCondition对象
     * @return
     */
    //public Page<T> queryPage(QueryCondition wb);
}

我们基础的dao接口实现如下：

package com.applet.base;

import com.applet.sql.builder.SelectBuilder;
import com.applet.sql.builder.WhereBuilder;
import com.applet.sql.mapper.DefaultRowMapper;
import com.applet.sql.page.PageSql;
import com.applet.sql.record.DomainModelAnalysis;
import com.applet.sql.record.DomainModelContext;
import com.applet.sql.record.ExtendType;
import com.applet.sql.record.TableColumn;
import com.applet.sql.type.JdbcType;
import com.applet.sql.type.TypeHandler;
import com.applet.sql.type.TypeHandlerRegistry;
import com.applet.utils.KeyUtils;
import com.applet.utils.Page;
import com.applet.utils.SpringContextHelper;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.ReflectionUtils;

import java.io.Serializable;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class BaseDaoImpl<T extends BaseModel, PK extends Serializable> implements BaseDao<T, PK>, InitializingBean {

    protected static final Logger log = Logger.getLogger(BaseDaoImpl.class);

    @Autowired
    protected JdbcTemplate jdbcTemplate;
    @Autowired
    protected PageSql pageSql;

    protected Class<T> modelClass;
    protected DomainModelAnalysis domainModelAnalysis;

    public BaseDaoImpl() {
    }

    @SuppressWarnings("unchecked")
    protected Class<T> autoGetDomainClass() {
        if (modelClass == null) {
            Type type = this.getClass().getGenericSuperclass();
            if (type instanceof ParameterizedType) {
                modelClass = (Class<T>) ((ParameterizedType) type).getActualTypeArguments()[0];
            } else {
                throw new RuntimeException("SubClass must give the ActualTypeArguments");
            }
        }
        return modelClass;
    }

    /**
     * 获取添加实体的SQL语句
     *
     * @return
     */
    protected String getInsertSql() {
        String[] array = domainModelAnalysis.joinColumnWithPlaceholder(", ");
        String sql = String.format("INSERT INTO %s (%s) VALUES (%s)", domainModelAnalysis.getTableName(), array[0], array[1]);
        return sql;
    }

    /**
     * 将完整的SQL转换为统计SQL
     * 如：select a, b, c, t.d from table t
     * 转换后为：select count(1) from table t
     *
     * @param sql
     * @return
     */
    protected String toCountSql(String sql) {
        if (StringUtils.isEmpty(sql)) {
            return null;
        }
        return sql.replaceFirst("(?<=(?i)SELECT).*?(?=(?i)FROM)", " COUNT\\(1\\) ").replaceAll("(?=(?i)order).*", "");
    }

    /**
     * 添加实体
     *
     * @param t 实体对象
     * @return
     */
    @Override
    public int insert(T t) {
        List<TableColumn> tableColumnList = domainModelAnalysis.getTableColumnList();
        List<Object> list = new ArrayList<Object>();
        for (int i = 0, size = tableColumnList.size(); i < size; i++) {
            TableColumn tableColumn = tableColumnList.get(i);
            if (tableColumn.isTransient()) {
                continue;
            }
            Object value = ReflectionUtils.invokeMethod(tableColumn.getFieldGetMethod(), t);
            ExtendType extendType = tableColumn.getExtendType();
            if (extendType != null && extendType.getCode() != ExtendType.DEFAULT.getCode()) {
                value = value.toString();
            }
            list.add(value);
        }
        return jdbcTemplate.update(getInsertSql(), list.toArray(new Object[0]));
    }

    /**
     * 批量添加实体
     *
     * @param list 实体对象列表
     * @return
     */
    @Override
    public int batchInsert(final List<T> list) {
        final List<TableColumn> tableColumnList = domainModelAnalysis.getTableColumnList();
        final TypeHandlerRegistry typeHandlerRegistry = DomainModelContext.getTypeHandlerRegistry();
        return jdbcTemplate.batchUpdate(getInsertSql(), new BatchPreparedStatementSetter() {
            @SuppressWarnings({"rawtypes", "unchecked"})
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                T t = list.get(i);
                int index = 1;
                for (int k = 0, size = tableColumnList.size(); k < size; k++) {
                    TableColumn tableColumn = tableColumnList.get(k);
                    if (tableColumn.isTransient()) {
                        continue;
                    }
                    Object value = ReflectionUtils.invokeMethod(tableColumn.getFieldGetMethod(), t);
                    TypeHandler typeHandler = typeHandlerRegistry.getTypeHandler(tableColumn.getJavaType());
                    typeHandler.setParameter(ps, index, value, JdbcType.NULL);
                    index++;
                }
            }

            @Override
            public int getBatchSize() {
                return list.size();
            }
        }).length;
    }

    /**
     * 更新实体中的非空(不含null, "")字段
     *
     * @param t 实体对象
     * @return
     */
    @Override
    public int update(T t) {
        WhereBuilder wb = new WhereBuilder();
        wb.andEquals(domainModelAnalysis.getPrimaryKey(), t.getId());
        return updateByCondition(t, wb);
    }

    protected int updateByCondition(T t, WhereBuilder whereBuilder) {
        StringBuilder sqlBuilder = new StringBuilder(String.format("UPDATE %s SET ", domainModelAnalysis.getTableName()));
        List<TableColumn> tableColumnList = domainModelAnalysis.getTableColumnList();

        Method primaryKeyMethod = null;
        List<Object> values = new ArrayList<Object>();
        for (int i = 0, size = tableColumnList.size(); i < size; i++) {
            final TableColumn tableColumn = tableColumnList.get(i);
            if (tableColumn.isTransient()) {
                continue;
            }
            if (tableColumn.isPrimaryKey()) {
                primaryKeyMethod = tableColumn.getFieldGetMethod();
                continue;
            }
            Object value = ReflectionUtils.invokeMethod(tableColumn.getFieldGetMethod(), t);
            ExtendType extendType = tableColumn.getExtendType();
            if (extendType != null && extendType.getCode() != ExtendType.DEFAULT.getCode()) {
                value = value.toString();
            }
            if (value == null || (value instanceof String && StringUtils.isEmpty((String) value))) {
                continue;
            }

            values.add(value);
            sqlBuilder.append(tableColumn.getColumnName())
                    .append(" = ")
                    .append(tableColumn.getPlaceholder())
                    .append(", ");
        }

        if (values.size() > 0) {
            int length = sqlBuilder.length();
            sqlBuilder.delete(length - 2, length);
            String sql = sqlBuilder.toString();
            sql = whereBuilder.getSql(sql);
            values.addAll(whereBuilder.getParameterList());
            return jdbcTemplate.update(sql, values.toArray(new Object[0]));
        }
        return 0;
    }

    /**
     * 更新实体所有字段
     *
     * @param t 实体对象
     * @return
     */
    @Override
    public int updateForce(T t) {
        StringBuilder sqlBuilder = new StringBuilder(String.format("UPDATE %s SET ", domainModelAnalysis.getTableName()));
        List<TableColumn> tableColumnList = domainModelAnalysis.getTableColumnList();

        Method primaryKeyMethod = null;
        List<Object> values = new ArrayList<Object>();
        for (int i = 0, size = tableColumnList.size(); i < size; i++) {
            final TableColumn tableColumn = tableColumnList.get(i);
            if (tableColumn.isTransient()) {
                continue;
            }
            if (tableColumn.isPrimaryKey()) {
                primaryKeyMethod = tableColumn.getFieldGetMethod();
                continue;
            }
            Object value = ReflectionUtils.invokeMethod(tableColumn.getFieldGetMethod(), t);
            values.add(value);
            sqlBuilder.append(tableColumn.getColumnName())
                    .append(" = ")
                    .append(tableColumn.getPlaceholder())
                    .append(", ");
        }

        int length = sqlBuilder.length();
        sqlBuilder.delete(length - 2, length);
        sqlBuilder.append(" WHERE ").append(domainModelAnalysis.getPrimaryKey()).append(" = ?");
        values.add(ReflectionUtils.invokeMethod(primaryKeyMethod, t));
        return jdbcTemplate.update(sqlBuilder.toString(), values.toArray(new Object[0]));
    }

    /**
     * 根据主键删除实体
     *
     * @param id 实体主键值
     * @return
     */
    @Override
    public int delete(PK id) {
        String sql = String.format("DELETE FROM %s WHERE %s = ?", domainModelAnalysis.getTableName(), domainModelAnalysis.getPrimaryKey());
        return jdbcTemplate.update(sql, new Object[]{id});
    }

    /**
     * 根据主键删除实体
     *
     * @param ids 实体主键值数组
     * @return
     */
    @Override
    public int delete(final PK[] ids) {
        String sql = String.format("DELETE FROM %s WHERE %s = ?", domainModelAnalysis.getTableName(), domainModelAnalysis.getPrimaryKey());
        int[] batchUpdate = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setObject(1, ids[i]);
            }

            @Override
            public int getBatchSize() {
                return ids.length;
            }
        });
        return batchUpdate.length;
    }

    /**
     * 查询实体列表
     *
     * @param wb where语句拼接实例
     * @return
     */
    protected List<T> query(WhereBuilder wb) {
        String sql = String.format("SELECT %s FROM %s", domainModelAnalysis.getDefaultColumnArrayStr(), domainModelAnalysis.getTableName());
        Object args[] = null;
        if (wb != null) {
            sql = wb.getSql(sql);
            args = wb.getParameters();
        }
        return jdbcTemplate.query(sql, args, new DefaultRowMapper<T>(domainModelAnalysis));
    }

    /**
     * 查询实体列表
     *
     * @param sql select语句
     * @param wb  where语句拼接实例
     * @return
     */
    protected List<T> query(String sql, WhereBuilder wb) {
        Object args[] = null;
        if (wb != null) {
            sql = wb.getSql(sql);
            args = wb.getParameters();
        }
        return jdbcTemplate.query(sql, args, new DefaultRowMapper<T>(domainModelAnalysis));
    }

    /**
     * 限制返回查询记录数量
     *
     * @param wb where语句拼接实例
     * @return
     */
    protected List<T> queryLimit(WhereBuilder wb) {
        String sql = String.format("SELECT %s FROM %s", domainModelAnalysis.getDefaultColumnArrayStr(), domainModelAnalysis.getTableName());
        return queryLimit(sql, wb);
    }

    /**
     * 限制返回查询记录数量
     *
     * @param querySql select语句
     * @param wb       where语句拼接实例
     * @return
     */
    protected List<T> queryLimit(String querySql, WhereBuilder wb) {
        Object args[] = null;
        int pageNum = 0, pageSize = 0;
        if (wb != null) {
            pageNum = wb.getPageNum();
            pageSize = wb.getPageSize();
            querySql = wb.getSql(querySql);
            args = wb.getParameters();
        }
        String qsql = pageSql.getSql(querySql, pageNum, pageSize);
        List<T> list = jdbcTemplate.query(qsql, args, new DefaultRowMapper<T>(domainModelAnalysis));
        return list;
    }

    /**
     * 按条件统计实体数量
     *
     * @param wb where语句拼接实例
     * @return
     */
    protected int count(WhereBuilder wb) {
        String sql = String.format("SELECT COUNT(1) FROM %s", domainModelAnalysis.getTableName());
        Object args[] = null;
        if (wb != null) {
            sql = wb.getSql(sql);
            args = wb.getParameters();
        }
        return jdbcTemplate.queryForObject(sql, args, Integer.class);
    }

    /**
     * 根据主键查询实体
     *
     * @param id 实体主键值
     * @return
     */
    @Override
    public T load(PK id) {
        String sql = String.format("SELECT %s FROM %s WHERE %s = ?", domainModelAnalysis.getDefaultColumnArrayStr(), domainModelAnalysis.getTableName(), domainModelAnalysis.getPrimaryKey());
        List<T> list = jdbcTemplate.query(sql, new Object[]{id}, new DefaultRowMapper<T>(domainModelAnalysis));
        return DataAccessUtils.singleResult(list);
    }

    /**
     * 按条件删除实体
     *
     * @param wb where语句拼接实例
     * @return
     */
    protected int deleteByCondition(WhereBuilder wb) {
        String sql = String.format("DELETE FROM %s", domainModelAnalysis.getTableName());
        Object args[] = null;
        if (wb != null) {
            sql = wb.getSql(sql);
            args = wb.getParameters();
        }
        return jdbcTemplate.update(sql, args);
    }

    /**
     * 分页查询
     *
     * @param wb where语句拼接实例
     * @return
     */
    protected Page<T> queryPage(WhereBuilder wb) {
        String sql = String.format("SELECT %s FROM %s ", domainModelAnalysis.getDefaultColumnArrayStr(), domainModelAnalysis.getTableName());
        return queryPage(sql, wb);
    }

    /**
     * 分页查询
     *
     * @param sql select语句
     * @param wb  where语句拼接实例
     * @return
     */
    protected Page<T> queryPage(String sql, WhereBuilder wb) {
        String countSql = toCountSql(sql);
        return queryPage(sql, countSql, wb);
    }

    /**
     * 分页查询
     *
     * @param querySql select语句
     * @param countSql count语句
     * @param wb       where语句拼接实例
     * @return
     */
    protected Page<T> queryPage(String querySql, String countSql, WhereBuilder wb) {
        Object args[] = null;
        int pageNum = 0, pageSize = 0;
        if (wb != null) {
            querySql = wb.getSql(querySql);
            args = wb.getParameters();
            pageSize = wb.getPageSize();
            pageNum = wb.getPageNum();
        }
        String qsql = pageSql.getSql(querySql, pageNum, pageSize);
        List<T> list = jdbcTemplate.query(qsql, args, new DefaultRowMapper<T>(domainModelAnalysis));

        Page<T> page = new Page<T>();
        page.setData(list);

        if (StringUtils.isNotEmpty(countSql)) {
            String csql = wb.getCountSql(countSql);
            long count = jdbcTemplate.queryForObject(csql, args, Long.class);
            page.setTotal(count);
        }
        return page;
    }

    @Override
    public void afterPropertiesSet() throws Exception {
        DomainModelContext domainModelContext = SpringContextHelper.getBean(DomainModelContext.class);
        domainModelAnalysis = domainModelContext.registerBean(autoGetDomainClass());
    }
}

这里最关键的是如何解析领域模型的属性信息，我们可以在运行时通过反射把领域模型的属性信息解析出来并全局缓存起来。这步操作是在BaseDaoImpl.java的如下代码完成的---如果你不熟悉InitializingBean接口，可以搜索一下它的意义：

@Override
public void afterPropertiesSet() throws Exception {
        DomainModelContext domainModelContext = SpringContextHelper.getBean(DomainModelContext.class);
        domainModelAnalysis = domainModelContext.registerBean(autoGetDomainClass());
}

下一节为大家介绍如何使用Java反射把领域模型的属性信息解析出来并全局缓存起来。