jdbcTemplate封装一个CRUD框架

很久没更新博文了，今天抽了1个小时空，给大家分享一个基于jdbcTemplate封装的CRUD工具API。

pom.xml依赖坐标

该框架用到的maven坐标如下，由于这是框架，所有的maven依赖均指定optional为true，这个代表依赖版本号以使用者项目为准，同时使用该框架的客户端项目必须重新强制依赖的方式引入这批依赖，否则编译时将会报错：

<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-api</artifactId>
    <optional>true</optional>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <optional>true</optional>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
    <optional>true</optional>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot</artifactId>
    <optional>true</optional>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
    <optional>true</optional>
</dependency>

‍

定义BaseModel

为什么一定要定义BaseModel？定义BaseModel有如下好处：

1、定义通用字段，例如主键、创建人、创建时间。

2、在CRUD DAO接口入参时通过泛型<M extends BaseModel>指定入参的父类型范围，然后可以在父类定义接口方式在DAO获取到主键进行操作。

BaseModel代码如下：

@Data
public abstract class BaseModel implements Serializable {


    static final long serialVersionUID = 25456241672452L;


    private Long id;
    private String createBy;
    private String updateBy;
    private LocalDateTime createDate;
    private LocalDateTime updateDate;


    /**
     * 表名
     * @return
     */
    public abstract String tableName();


}

定义BaseDao接口

BaseDao接口是CRUD的关键API定义，代码如下：

public interface BaseDao<M extends BaseModel> {


    M findByPk(Long pk);


    void create(M model);


    void update(M model);


    void delete(M model);


    PageResponse<M> query(PageRequest request);


}

实现BaseDao接口

BaseDao接口实现类是框架的核心，关键代码如下：

@Slf4j
@SuppressWarnings("all")
public class BaseDefaultDao<M extends BaseModel> implements BaseDao<M> {


    @Autowired
    protected JdbcTemplate jdbcTemplate;


    @Override
    public M findByPk(Long pk) {
        try {
            if (pk == null) {
                throw new ExamRuntimeException("pk cant not be null");
            }
            M model = (M) ReflectionUtil.getSuperGenericityInstance(this.getClass(), 1);
            StringBuilder sql = new StringBuilder("select * from ").append(model.tableName()).append(" where 1=1 and id = ?;");


            this.infoLog("findByPk sql: {}, vals:{}", sql, pk);
            Map<String, Object> resultMap = jdbcTemplate.queryForMap(sql.toString(), pk);
            resultMap.forEach((field, val) -> {
                Method setMethod = ReflectionUtil.getSetMethodByField(ReflectionUtil.getFieldByName(StringUtil.toCame(field), model.getClass()), model.getClass());
                ReflectionUtil.invokeMethod(setMethod, model, val);
            });
            return model;
        } catch (EmptyResultDataAccessException emptyResultDataAccessException) {
            throw new ExamRuntimeException("查无数据");
        }
    }


    @Override
    public void create(M model) {


        if (model.getId() == null){
            model.setId(IdUtil.DEFAULT_SNOWFLAKE.nextId());
        }
        if (model.getCreateDate() == null){
            model.setCreateDate(LocalDateTime.now());
        }
        if (model.getUpdateDate() == null){
            model.setUpdateDate(LocalDateTime.now());
        }
        model.setCreateBy(SpringUtil.getBean(LoginProvide.class).takeCreateBy());
        model.setUpdateBy(model.getCreateBy());
        List<String> fields = ReflectionUtil.allFields(model.getClass(), "serialVersionUID");
        List<Object> fieldVals = new ArrayList<>();
        for (String field : fields) {
            fieldVals.add(ReflectionUtil.getValueByFieldName(field, model.getClass(), model));
        }


        StringBuilder valSql = new StringBuilder();
        StringBuilder sql = new StringBuilder("insert into ").append(model.tableName()).append("(");
        for (String field : fields) {
            sql.append(StringUtil.toUnderline(field)).append(",");
            valSql.append("?").append(",");
        }


        sql.delete(sql.length() - 1, sql.length());
        valSql.delete(valSql.length() - 1, sql.length());
        sql.append(")values(").append(valSql).append(");");


        this.infoLog("insert sql: {}, vals:{}", sql, fieldVals);
        jdbcTemplate.update(sql.toString(), fieldVals.toArray());
    }


    protected void infoLog(String logStr, Object... args) {
        if (ProjectProperty.SELF.isPrintSql()) {
            log.info(logStr, args);
        }
    }


    @Override
    public void update(M model) {
        if (model.getId() == null) {
            throw new ExamRuntimeException("id cant not be null");
        }
        model.setUpdateDate(LocalDateTime.now());
        model.setUpdateBy(SpringUtil.getBean(LoginProvide.class).takeUpdateBy());
        List<String> fields = ReflectionUtil.allFields(model.getClass(), "serialVersionUID");
        List<Object> fieldVals = new ArrayList<>();
        for (String field : fields) {
            fieldVals.add(ReflectionUtil.getValueByFieldName(field, model.getClass(), model));
        }


        StringBuilder sql = new StringBuilder("update ").append(model.tableName()).append(" set ");
        for (String field : fields) {
            sql.append(StringUtil.toUnderline(field)).append("=?,");
        }


        sql.delete(sql.length() - 1, sql.length());
        sql.append(" where id=?;");


        fieldVals.add(model.getId());
        this.infoLog("update sql: {}, vals:{}", sql, fieldVals);
        jdbcTemplate.update(sql.toString(), fieldVals.toArray());
    }


    @Override
    public void delete(M model) {
        if (model.getId() == null) {
            throw new ExamRuntimeException("id cant not be null");
        }
        StringBuilder sql = new StringBuilder("delete from ").append(model.tableName()).append(" where id =?; ");
        this.infoLog("delete sql: {}, vals:{}", sql, model.getId());
        jdbcTemplate.update(sql.toString(), model.getId());
    }


    @Override
    public PageResponse<M> query(PageRequest request) {


        BaseModel model = null;
        try {
            model = (BaseModel) request.getCls().newInstance();
        } catch (Exception e) {
            throw new ExamRuntimeException(e);
        }
        StringBuilder sql = new StringBuilder("select * from ").append(model.tableName()).append(" where 1=1 ");
        List<Object> params = new ArrayList<>();


        for (Map.Entry<String, ConditionRuleEnum> entry : request.getConditionsRuleMap().entrySet()) {
            ConditionRuleEnum conditionRuleEnum = entry.getValue();
            switch (conditionRuleEnum) {
                case IN:
                    sql.append(" and ").append(StringUtil.toUnderline(entry.getKey())).append(" in( ");
                    List inVals = (List) request.getConditionsValMap().get(entry.getKey());
                    for (Object val : inVals) {
                        sql.append("?,");
                        params.add(val);
                    }
                    sql.delete(sql.length() - 1, sql.length()).append(") ");
                    break;
                case NOT_IN:
                    sql.append(" and ").append(StringUtil.toUnderline(entry.getKey())).append(" not in( ");
                    List notInVals = (List) request.getConditionsValMap().get(entry.getKey());
                    for (Object val : notInVals) {
                        sql.append("?,");
                        params.add(val);
                    }
                    sql.delete(sql.length() - 1, sql.length()).append(") ");
                    break;
                case BETWEEN:
                    sql.append(" and ").append(StringUtil.toUnderline(entry.getKey())).append(" between ? and ? ");
                    String betweenVals = (String) request.getConditionsValMap().get(entry.getKey());
                    String[] betweenValToArray = betweenVals.split(",");
                    params.add(betweenValToArray[0]);
                    params.add(betweenValToArray[1]);
                    break;
                case LIKE:
                    String likeVal = (String) request.getConditionsValMap().get(entry.getKey());
                    sql.append(" and ").append(StringUtil.toUnderline(entry.getKey())).append(conditionRuleEnum.getValue()).append(" ?");
                    params.add("%" + likeVal + "%");
                    break;
                case IS_NULL:
                case NOT_NULL:
                    sql.append(" and ").append(StringUtil.toUnderline(entry.getKey())).append(conditionRuleEnum.getValue());
                    break;
                default:
                    sql.append(" and ").append(StringUtil.toUnderline(entry.getKey())).append(conditionRuleEnum.getValue()).append(" ?");
                    params.add(request.getConditionsValMap().get(entry.getKey()));
                    break;
            }
        }


        //逻辑删除
        if (!request.getIncludeLogicDelete()){
            sql.append(" and delete_flag = ? ");
            params.add(ExamConst.ZERO);
        }


        //响应结果集
        PageResponse<M> response = new PageResponse<>();
        //计算起始行数
        response.setStartIndex();
        //计算结束行数
        response.setLastIndex();
        //设置每页显示记录数
        response.setPageSize(request.getPageSize());
        //设置要显示的页数
        response.setCurrentPage(request.getCurrentPage());
        //计算总记录数
        StringBuilder totalSQL = new StringBuilder(" SELECT count(*) FROM ( ");
        totalSQL.append(sql);
        totalSQL.append(" ) totalTable ");
        this.infoLog("select resultSet sql ：{}", sql);
        this.infoLog("select count sql：{}", totalSQL);
        //总记录数
        if (request.getCount()){
            response.setTotalRows(jdbcTemplate.queryForObject(totalSQL.toString(), params.toArray(), Integer.class));
        }
        //排序
        if (request.getSort()) {
            sql.append(" order by ").append(StringUtil.toUnderline(request.getSortField())).append(" ").append(request.getSortType());
        }
        //分页
        if (request.getPaging()) {
            sql.append(" limit ").append(response.getStartIndex()).append(",").append(response.getPageSize()).toString();
        }
        //计算总页数
        response.setTotalPages();
        //执行结果集查询
        List<Map<String, Object>> rowSet = jdbcTemplate.queryForList(sql.toString(),  params.toArray());
        List<M>  transOfBeanList = new ArrayList<>();
        for (Map<String, Object> resultMap : rowSet) {
            M newModel = (M) BeanUtil.newInstance(request.getCls());
            resultMap.forEach((field, val) -> {
                Method setMethod = ReflectionUtil.getSetMethodByField(ReflectionUtil.getFieldByName(StringUtil.toCame(field), newModel.getClass()), newModel.getClass());
                ReflectionUtil.invokeMethod(setMethod, newModel, val);
            });
            transOfBeanList.add(newModel);
        }
        response.setResultList(transOfBeanList);


        return response;
    }
}

PageRequest类源码

public class PageRequest {


    /**
     * 从当前页
     */
    private Integer currentPage = 1;
    /**
     * 取多少条数据
     */
    private Integer pageSize = 10;
    /**
     * 是否查总数，默认是
     */
    private Boolean count = true;
    /**
     * 排序字段
     */
    private String sortField = "updateDate";
    /**
     * 默认从大到小
     */
    private String sortType = "desc";
    /**
     * Class
     */
    private Class cls;
    /**
     * 是否排序，默认是
     */
    private Boolean sort = true;
    /**
     * 是否分页
     */
    private Boolean paging = true;
    /**
     * 包含详情
     */
    private Boolean includeDetail = false;
    /**
     * 是否包含逻辑删除数据
     */
    private Boolean includeLogicDelete = false;
    /**
     * 条件规则 Map
     */
    private Map<String, ConditionRuleEnum> conditionsRuleMap = new HashMap<>();
    /**
     * 条件值 Map
     */
    private Map<String, Object> conditionsValMap = new HashMap<>();




    public PageRequest() {


    }


    public Boolean getIncludeLogicDelete() {
        return includeLogicDelete;
    }


    public PageRequest setIncludeLogicDelete(Boolean includeLogicDelete) {
        this.includeLogicDelete = includeLogicDelete;
        return this;
    }


    public Boolean getIncludeDetail() {
        return includeDetail;
    }


    public PageRequest setIncludeDetail(Boolean includeDetail) {
        this.includeDetail = includeDetail;
        return this;
    }


    public PageRequest setCurrentPage(Integer currentPage) {
        this.currentPage = currentPage;
        return this;
    }


    public PageRequest setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
        return this;
    }


    public PageRequest setCount(Boolean count) {
        this.count = count;
        return this;
    }


    public PageRequest setSortField(String sortField) {
        this.sortField = sortField;
        return this;
    }


    public PageRequest setSortType(String sortType) {
        this.sortType = sortType;
        return this;
    }


    public PageRequest setSort(Boolean sort) {
        this.sort = sort;
        return this;
    }


    public PageRequest setPaging(Boolean paging) {
        this.paging = paging;
        return this;
    }


    public PageRequest queryAll(){
        this.paging = false;
        this.count = false;
        this.sort = false;
        return this;
    }


    public Map<String, Object> getConditionsValMap() {
        return conditionsValMap;
    }




    public Class getCls() {
        return cls;
    }


    public PageRequest setCls(Class cls) {
        this.cls = cls;
        return this;
    }




    public PageRequest eq(String field, Comparable val) {
        this.conditionsRuleMap.put(field, ConditionRuleEnum.EQ);
        this.conditionsValMap.put(field, val);
        return this;
    }


    public PageRequest like(String field, String val) {
        if (StringUtil.hasLength(val)){
            this.conditionsRuleMap.put(field, ConditionRuleEnum.LIKE);
            this.conditionsValMap.put(field, val);
        }
        return this;
    }


    public PageRequest between(String field, String val) {
        if (StringUtil.hasLength(val) && val.contains(",")){
            this.conditionsRuleMap.put(field, ConditionRuleEnum.BETWEEN);
            this.conditionsValMap.put(field, val);
        }
        return this;
    }


    public PageRequest ne(String field, Comparable val) {
        this.conditionsRuleMap.put(field, ConditionRuleEnum.NOT_EQ);
        this.conditionsValMap.put(field, val);
        return this;
    }


    public PageRequest in(String field, List val) {
        if (CollectionUtils.isEmpty(val)){
            return this;
        }
        this.conditionsRuleMap.put(field, ConditionRuleEnum.IN);
        this.conditionsValMap.put(field, val);
        return this;
    }


    public PageRequest noIn(String field, List val) {
        if (CollectionUtils.isEmpty(val)){
            return this;
        }
        this.conditionsRuleMap.put(field, ConditionRuleEnum.NOT_IN);
        this.conditionsValMap.put(field, val);
        return this;
    }


    public PageRequest noNull(String field) {
        this.conditionsRuleMap.put(field, ConditionRuleEnum.NOT_NULL);
        return this;
    }


    public PageRequest isNull(String field) {
        this.conditionsRuleMap.put(field, ConditionRuleEnum.IS_NULL);
        return this;
    }


    public PageRequest gt(String field, Comparable val) {
        this.conditionsRuleMap.put(field, ConditionRuleEnum.GT);
        this.conditionsValMap.put(field, val);
        return this;
    }


    public PageRequest lt(String field, Comparable val) {
        this.conditionsRuleMap.put(field, ConditionRuleEnum.LT);
        this.conditionsValMap.put(field, val);
        return this;
    }


    public PageRequest ge(String field, Comparable val) {
        this.conditionsRuleMap.put(field, ConditionRuleEnum.GE);
        this.conditionsValMap.put(field, val);
        return this;
    }


    public PageRequest le(String field, Comparable val) {
        this.conditionsRuleMap.put(field, ConditionRuleEnum.LE);
        this.conditionsValMap.put(field, val);
        return this;
    }


    public PageRequest eq(boolean condition, String field, Comparable val) {
        if (condition) {
            return this.eq(field, val);
        }
        return this;
    }


    public PageRequest like(boolean condition, String field, String val) {
        if (condition) {
            return this.like(field, val);
        }
        return this;
    }


    public PageRequest between(boolean condition, String field, String val) {
        if (condition) {
            return this.between(field, val);
        }
        return this;
    }


    public PageRequest ne(boolean condition, String field, Comparable val) {
        if (condition) {
            return this.ne(field, val);
        }
        return this;
    }


    public PageRequest in(boolean condition, String field, List val) {
        if (condition) {
            return this.in(field, val);
        }
        return this;
    }


    public PageRequest noIn(boolean condition, String field, List val) {
        if (condition) {
            return this.noIn(field, val);
        }
        return this;
    }


    public PageRequest noNull(boolean condition, String field) {
        if (condition) {
            return this.noNull(field);
        }
        return this;
    }


    public PageRequest isNull(boolean condition, String field) {
        if (condition) {
            return this.isNull(field);
        }
        return this;
    }


    public PageRequest gt(boolean condition, String field, Comparable val) {
        if (condition) {
            return this.gt(field, val);
        }
        return this;
    }


    public PageRequest lt(boolean condition, String field, Comparable val) {
        if (condition) {
            return this.lt(field, val);
        }
        return this;
    }


    public PageRequest ge(boolean condition, String field, Comparable val) {
        if (condition) {
            return this.ge(field, val);
        }
        return this;
    }


    public PageRequest le(boolean condition, String field, Comparable val) {
        if (condition) {
            return this.le(field, val);
        }
        return this;
    }


    public Boolean getPaging() {
        return paging;
    }


    public Integer getBeginRow() {
        if (currentPage <= 1) {
            return 0;
        } else {
            return this.currentPage = (currentPage - 1) * getPageSize();
        }
    }




    public Integer getCurrentPage() {
        return currentPage;
    }


    public Integer getPageSize() {
        return pageSize;
    }


    public Boolean getCount() {
        return count;
    }


    public String getSortField() {
        return sortField;
    }


    public String getSortType() {
        return sortType;
    }


    public Boolean getSort() {
        return sort;
    }


    public Map<String, ConditionRuleEnum> getConditionsRuleMap() {
        return conditionsRuleMap;
    }

PageResponse类源码

@Slf4j
@Data
public class PageResponse<T> {


    //一页显示的记录数
    private int pageSize;
    //记录总数
    private int totalRows;
    //总页数
    private int totalPages;
    //当前页码
    private int currentPage;
    //起始行数
    private int startIndex;
    //结束行数
    private int lastIndex;
    //结果集存放List
    private List<T> resultList = new ArrayList<>();




    public PageResponse() {
    }


    public PageResponse(int pageSize, int currentPage) {
        this.pageSize = pageSize;
        this.currentPage = currentPage;
    }


    public int getCurrentPage() {
        return currentPage;
    }


    public void setCurrentPage(int currentPage) {
        //如果当前页码<1，则默认加载第一页数据
        if (currentPage < 0) {
            this.currentPage = 1;
        } else {
            this.currentPage = currentPage;
        }
    }




    public List<T> getResultList() {
        return resultList;
    }


    public void setResultList(List<T> resultList) {
        this.resultList = resultList;
    }


    public int getTotalPages() {
        return totalPages;
    }


    public T singleElement(boolean requiredSingle) {
        AssertUtil.isTrue((requiredSingle && this.getResultList().size() == 1), "查无数据或多条" + this.getResultList().size());
        if (CollectionUtils.isEmpty(this.getResultList())) {
            return null;
        }
        return this.getResultList().get(0);
    }


    //计算总页数
    public void setTotalPages() {
        if (pageSize == 0) {
            totalPages = 0;
        } else {
            if (totalRows % pageSize == 0) {
                this.totalPages = totalRows / pageSize;
            } else {
                this.totalPages = (totalRows / pageSize) + 1;
            }
        }
    }


    public int getTotalRows() {
        return totalRows;
    }


    public void setTotalRows(int totalRows) {
        this.totalRows = totalRows;
    }


    public int getStartIndex() {
        return startIndex;
    }


    public void setStartIndex() {
        if (currentPage <= 1) {
            startIndex = 0;
        } else {
            startIndex = (currentPage - 1) * getPageSize();
        }


    }


    public int getLastIndex() {
        return lastIndex;
    }


    public int getPageSize() {
        return pageSize;
    }


    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }


    //计算结束时候的索引
    public void setLastIndex() {


        this.lastIndex = pageSize;
    }


}

ConditionRuleEnum类源码

public enum ConditionRuleEnum {


    LE(" <= "), GE(" >= "), EQ(" = "), LT(" < "), GT(" > "), IN(" in "), NOT_NULL(" is not null"),
    LIKE(" like"), IS_NULL(" is null "), NOT_IN(" not in "), NOT_EQ(" != "), BETWEEN(" between ");


    private String value;


    ConditionRuleEnum(String value) {
        this.value = value;
    }


    public void setValue(String value) {
        this.value = value;
    }


    public String getValue() {
        return value;
    }


    public static ConditionRuleEnum searchByRule(String rule) {
        for (ConditionRuleEnum ruleEnum : ConditionRuleEnum.values()) {
            if (ruleEnum.getValue().equalsIgnoreCase(rule)) {
                return ruleEnum;
            }
        }
        System.out.println("Not Found Match " + rule + " in RuleEnum.values");
        return ConditionRuleEnum.EQ;
    }
}

以上是框架所有的关键源码，下面通过一个例字看看客户端是怎么使用这个框架API的。

@SpringBootTest
public class CrudTest {


    @Autowired
    ExamPaperDao examPaperDao;




    //查询
    @Test
    public void testQuery() {


        List<Long> inIds = new ArrayList<>();
        inIds.add(1080129702851887104L);
        inIds.add(1L);
        List<Long> notInIds = new ArrayList<>();
        notInIds.add(11L);
        notInIds.add(22L);


        List<String> inStatuss = new ArrayList<>();
        inStatuss.add("ok");
        inStatuss.add("no");
        PageRequest pageRequest = new PageRequest();
        pageRequest
//                .eq("id", 1080129702851887104L)
//                .lt("itemCount", 20)
//                .eq("status", "ok")
                .like("title", "a")
                .le("deleteFlag", 0)
//                .in("id", inIds)
                .noIn("id", notInIds)
//                .in("status", inStatuss)
//                .noIn("status", inStatuss)
                .between("itemCount", "10,20")
                .noNull("expand1")
                .setPaging(true)
                .setSort(true)
                .setCount(true)
                .setCls(ExamPaperModel.class)
                .setSortType("desc")
                .setSortField("updateDate")
                .setPageSize(10)
                .setCurrentPage(1);
        System.out.println(JSON.toJSONString(examPaperDao.query(pageRequest)));
        ;
    }


    @Test
    public void testFindByPk() {
        System.out.println(JSON.toJSONString(examPaperDao.findByPk(1080091764821430272L)));
    }


    //删除
    @Test
    public void testDelete() {
        ExamPaperModel examPaperModel = new ExamPaperModel();
        examPaperModel.setId(1080130392751403008L);
        examPaperDao.delete(examPaperModel);
    }


    //修改
    @Test
    public void testUpdate() {
        ExamPaperModel examPaperModel = new ExamPaperModel();
        examPaperModel.setDomain("111");
        examPaperModel.setDuration("222");
        examPaperModel.setScore(new BigDecimal(80));
        examPaperModel.setTitle("ab");
        examPaperModel.setStatus("ok");
        examPaperModel.setDeleteFlag("0");
        examPaperModel.setItemCount(12);
        examPaperModel.setGrade("二年级");
        examPaperModel.setCreateBy("zhangsan");
        examPaperModel.setUpdateBy("lisi");
        examPaperModel.setUpdateDate(LocalDateTime.now());
        examPaperModel.setCreateDate(LocalDateTime.now());
        examPaperModel.setId(1080130392751403008L);
        examPaperDao.update(examPaperModel);
    }


    //新增
    @Test
    public void testInsert() {
        ExamPaperModel examPaperModel = new ExamPaperModel();
        examPaperModel.setDomain("111");
        examPaperModel.setDuration("222");
        examPaperModel.setScore(new BigDecimal(1));
        examPaperModel.setTitle("ab");
        examPaperModel.setStatus("ok");
        examPaperModel.setDeleteFlag("0");
        examPaperModel.setItemCount(12);
        examPaperModel.setGrade("一年级");
        examPaperModel.setCreateBy("zhangsan");
        examPaperModel.setUpdateBy("lisi");
        examPaperModel.setUpdateDate(LocalDateTime.now());
        examPaperModel.setCreateDate(LocalDateTime.now());
        examPaperModel.setId(IdUtil.DEFAULT_SNOWFLAKE.nextId());
        examPaperDao.create(examPaperModel);
    }


}

----- 正文结束 -----
长按扫码关注微信公众号

Java软件编程之家