加入CODE CHINA

· 不限速    · 不限空间    · 不限人数    · 私仓免费

免费加入
README.md

paoding-rose-jade DAO层使用手册

jdk 需要 1.8以上版本

功能

1. 支持jade 原生的语法 具体用法请参考 1.0 语法支持
2. 支持 条件语句封装 具体用法参考 2.0 ,[想象MyBatis 的xml一样,自动的添加 关键字]
3. 支持Option返回值 参考3.0

特别说明

文档中还存在 1=1 的情况也在 2.0.1 version 中解决,具体请参考文档中的2.0

语法

  • 1.0 语法支持
    • 1.1 变量赋值
      • 1.1.1 冒号[:] 表示这是一个变量,例如例子中 :limit
       @SQL("SELECT user_id, device_token FROM test_table LIMIT :limit")
       public List<Test> getTests(@SQLParam("limit") int limit);
      • 1.1.2 :1 :2 ....的语法使用
      @SQL("SELECT user_id, device_token FROM test_table LIMIT :1")
      public List<Test> getTests(int limit);
      
      @SQL("SELECT user_id, device_token FROM test_table where user_name = :1.userName")
      public List<Test> getTests(User user);
    • 1.2 字符串拼接
      • 1.2.1 双# 的用法[##]
      @SQL("SELECT user_id, device_token FROM test_##(:tableName) where user_name = :user.userName")
      public List<Test> getTests(@SQLParam("tableName") String tableName, @SQLParam("user")User user);
    • 1.3 条件表达式 #if(){} #if(){}#else{}
    // #if(){}
    @SQL("SELECT user_id, device_token FROM test_user #if(:user!=null){ where user_name = :user.userName}")
    public List<Test> getTests(@SQLParam("user")User user);
    // #if(){}#else{}
    @SQL("SELECT user_id, device_token FROM test_user #if(:user!=null){ where #if(:user.userName!=null){ user_name = :user.userName }#else{ id=:user.id }}")
    public List<Test> getTests(@SQLParam("user")User user);
    • 1.4 for循环的使用,请参考下方demo中的用例
    • 1.5 for循环中index的使用,请参考下方demo中的用例
    • 1.6 in 的使用
     @SQL("SELECT user_id, device_token FROM test_##(:partition) where user_id in(:ids)")
     public List<Test> getTestsByIds(@SQLParam("partition") int partition, @SQLParam("ids") List<Integer> ids);
    • 1.7 like 的使用
     @SQL("SELECT user_id, device_token FROM test_user where user_name like CONCAT('%',:userName,'%')")
     public List<Test> getTestsByIds(@SQLParam("userName") String userName);

baseDao 公共方法的抽取

package cn.zhangfusheng.base.server.dao;

import cn.zhangfusheng.base.page.PageRequest;
import cn.zhangfusheng.base.page.SortBy;
import net.paoding.rose.jade.annotation.ReturnGeneratedKeys;
import net.paoding.rose.jade.annotation.SQL;
import net.paoding.rose.jade.annotation.SQLParam;
import net.paoding.rose.jade.annotation.SQLType;

import java.util.*;

/**
 * @ClassName: BaseDao
 * @Author: ZFS
 * @Date: 2018/11/26 16:12
 */
public interface BaseDao<T> {

    public static final String SELECT_SQL = "select $SELECT_COLUMN from `$TABLE_NAME` ";
    public static final String COUNT_SQL = "select count(1) from `$TABLE_NAME` ";
    public static final String DELETE_SQL = "delete from `$TABLE_NAME` ";

    /**
     * 根据id查询
     * @param id 主键id
     * @return T
     */
    @SQL(SELECT_SQL + "where id = :id")
    T queryById(@SQLParam("id") Integer id);

    /**
     * 根据条件查询
     * @param queryMap 查询条件
     * @return
     */
    @SQL(SELECT_SQL + "#if(:m != null){#for(key in :m.keySet()){#if(index==0){where}#else{#if(index!=0){and}} `##(:key)`=#(:m[:key]) }}")
    List<T> queryByAll(@SQLParam("m") Map<String, Object> queryMap);

    /**
     * 查询并排序
     * @param queryMap 查询条件
     * @param sortBy   排序方式
     * @return
     */
    @SQL(SELECT_SQL + "#if(:m != null){#for(key in :m.keySet()){#if(index==0){where}#else{#if(index!=0){and}} `##(:key)`=#(:m[:key]) }}" +
            " #if(:s != null && :s.columnName!=null && :s.sortOrder!=null){order by ##(:s.columnName) ##(:s.sortOrder)}")
    List<T> queryForSort(@SQLParam("m") Map<String, Object> queryMap, @SQLParam("s") SortBy sortBy);

    /**
     * 分页查询
     * @param queryMap    查询条件
     * @param pageRequest 分页数据
     * @param sortBy      排序方式
     * @return
     */
    @SQL(SELECT_SQL + "#if(:m != null){#for(key in :m.keySet()){#if(index==0){where}#else{#if(index!=0){and}} `##(:key)`=#(:m[:key]) }}" +
            " #if(:s != null && :s.columnName!=null && :s.sortOrder!=null){order by ##(:s.columnName) ##(:s.sortOrder)}" +
            " #if(:pageRequest != null){LIMIT :pageRequest.startNum,:pageRequest.pageSize}")
    List<T> queryForPage(
            @SQLParam("m") Map<String, Object> queryMap, @SQLParam("pageRequest") PageRequest pageRequest, @SQLParam("s") SortBy sortBy);

    /**
     * 根据条件只能查询出一条结果
     * @param queryMap
     * @return
     */
    @SQL(SELECT_SQL + "#if(:m != null){#for(key in :m.keySet()){#if(index==0){where}#else{#if(index!=0){and}} `##(:key)`=#(:m[:key]) }}")
    T queryOne(@SQLParam("m") Map<String, Object> queryMap);

    /**
     * 统计个数
     * @param queryMap
     * @return int
     */
    @SQL(COUNT_SQL + "#if(:m != null){#for(key in :m.keySet()){#if(index==0){where}#else{#if(index!=0){and}} `##(:key)`=#(:m[:key]) }}")
    int baseCount(@SQLParam("m") Map<String, Object> queryMap);

    /**
     * 根据id 更新
     * @param keyValue 可以调用cn.slhz.base.bean.BaseBean.beanTOMap(Object object) 方法,将对象转换成map
     * @param id       主键id
     * @return 更新的个数 == 1
     */
    @SQL("UPDATE $TABLE_NAME SET #for(key in :m.keySet()){#if(index!=0){,} `##(:key)` = #(:m[:key]) } WHERE id = :id")
    Integer updateById(@SQLParam("m") Map<String, Object> keyValue, @SQLParam("id") int id);

    /**
     * 插入一条数据 该表必须包含id字段
     * @param keyValue 可以调用cn.slhz.base.bean.BaseBean.beanTOMap(Object object) 方法,将对象转换成map
     * @return 主键id
     */
    @ReturnGeneratedKeys
    @SQL("INSERT INTO $TABLE_NAME" +
            " (#for(key in :keyValue.keySet()){#if(index!=0){,} `##(:key)`})" +
            " VALUES" +
            " (#for(key in :keyValue.keySet()){#if(index!=0){,}'##(:keyValue[:key])'})")
    int insert(@SQLParam("keyValue") Map<String, Object> keyValue);

    /**
     * 批量保存
     * @param keyValue
     * @return
     */
    @SQL("INSERT INTO $TABLE_NAME ( ##(:keyValue.filedName) ) VALUES ##(:keyValue.filedValue) ")
    int insertAll(@SQLParam("keyValue") Map<String, Object> keyValue);

    /**
     * 批量删除
     * @param ids
     * @return
     */
    @SQL("DELETE FROM $TABLE_NAME WHERE id in (:ids)")
    int delete(@SQLParam("ids") List<Integer> ids);

    /**
     * 根据id单个删除
     * @param id
     * @return
     */
    @SQL("DELETE FROM $TABLE_NAME WHERE id = :id")
    int deleteById(@SQLParam("id") int id);

    /**
     * 根据条件删除
     * @param queryMap
     * @return
     */
    @SQL("DELETE FROM $TABLE_NAME #if(:m != null){#for(key in :m.keySet()){#if(index==0){where}#else{#if(index!=0){and}} `##(:key)`=#(:m[:key]) }}")
    int delete(@SQLParam("m") Map<String, Object> queryMap);

    /**
     * 先查询 如果不存在 则 插入
     * @param keyValue
     * @return
     */
    @ReturnGeneratedKeys
    @SQL(type = SQLType.WRITE,
            value = "INSERT INTO $TABLE_NAME (#if(index!=0){,}#for(key in :m.keySet()){#if(index!=0){,}`##(:key)`}) select #for(key in :m.keySet()){#if(index!=0){,}'##(:m[:key])'} from dual where not exists (select id from $TABLE_NAME #if(:m != null){#for(key in :m.keySet()){#if(index==0){where}#else{#if(index!=0){and}} `##(:key)`=#(:m[:key]) }}})")
    Integer selectInsert(@SQLParam("m") Map<String, Object> keyValue);


}

PageRequest 分页相关参数封装

package cn.zhangfusheng.base.page;

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.experimental.Accessors;

import java.util.List;

/**
 * @author fusheng.zhang
 * @Description
 * @create 2019-11-05 11:36:00
 */
@Data
@Accessors(chain = true)
public class PageRequest {

    /**
     * 每页的大小
     */
    @ApiModelProperty("分页大小")
    private int pageSize;

    /**
     * 开始页数
     */
    @ApiModelProperty("第几页")
    private int pageNumber;
    /**
     * 开始条数
     */
    @ApiModelProperty(hidden = true)
    private int startNum;
    /**
     * 结束页数
     */
    @ApiModelProperty(hidden = true)
    private int endPage;
    /**
     * 总条数
     */
    @ApiModelProperty(hidden = true)
    private int count;
    /**
     * 总条数
     */
    @ApiModelProperty(hidden = true)
    private int total;
    /**
     * 总页数
     */
    @ApiModelProperty(hidden = true)
    private int totalPage;

    public synchronized <T> PageResponse<T> pageResponse() {
        return new PageResponse<>();
    }

    public synchronized <T> PageResponse<T> pageResponse(List<T> data, Integer count) {
        PageResponse<T> tPageResponse = new PageResponse<>();
        tPageResponse.setData(data).setCount(count);
        return tPageResponse;
    }

    public int getPageNumber() {
        return Math.max(pageNumber, 1);
    }

    public int getStartNum() {
        return startNum = pageNumber <= 1 ? 0 : (pageNumber - 1) * getPageSize();
    }

    public int getPageSize() {
        return pageSize = pageSize == 0 ? 10 : pageSize;
    }

    public int getEndPage() {
        return endPage = count % getPageSize() == 0 ? count / getPageSize() : count / getPageSize() + 1;
    }

    public int getCount() {
        return count;
    }

    public int getTotalPage() {
        return count % pageSize == 0 ? count / pageSize : count / pageSize + 1;
    }

    public int getTotal() {
        total = count == 0 ? 1 : count;
        return total;
    }
}

sortBy 字段排序配置

package cn.zhangfusheng.base.page;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @author fusheng.zhang
 * @Description
 * @create 2020-04-13 16:29:00
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@ApiModel("排序")
public class SortBy {

    public static final String DESC = "desc";
    public static final String ASC = "asc";

    @ApiModelProperty("排序字段,多个字段采用逗号拼接")
    private String columnName;

    @ApiModelProperty(value = "排序方式 asc desc", allowableValues = "desc,asc")
    private String sortOrder = DESC;
}
  • 2.0 调整@SQL注解,添加SQLCondition注解,具体用法如下
        // SELECT_SQL 为BaseDAO中提取的公共的sql模板
        final static String TABLE_NAME = "tb_user";
        final static String SELECT_COLUMN = "`id`,`user_name`,`phone`,`password`,`head_img`,`province_id`,`province_name`,`city_id`,`city_name`,`area_id`,`area_name`,`address`,`status`,`create_time`,`update_time`";
    
        /**
         * 产生sql:
         * select `id`,`user_name`,`phone`,`password`,`head_img`,`province_id`,`province_name`,`city_id`,`city_name`,`area_id`,`area_name`,`address`,`status`,`create_time`,`update_time`
         * from `tb_user`
         * where user_name = '123' and phone = '456' and id in (1001) or province_id=1 or (province_id=1 or id in (1001))
         * @param tbUser
         * @return
         */
        @SQL(
                value = SELECT_SQL,
                condition = {
                        @SQLCondition("#if(:vo.userName!=null){user_name = :vo.userName}"),
                        @SQLCondition("#if(:vo.phone!=null){phone = :vo.phone}"),
                        @SQLCondition("#if(:vo.id!=null){id in (:vo.id)}"),
                        @SQLCondition(value = "#if(:vo.provinceId!=null){province_id=:vo.provinceId}", condition = "or"),
                        @SQLCondition(value = "#if(:vo.provinceId!=null){(province_id=:vo.provinceId or id in (:vo.id))}", condition = "or"),
                }
        )
        List<TbUser> find(@SQLParam("vo") TbUser tbUser);
    
    
        /**
         * 产生sql
         * select
         * `id`,`user_name`,`phone`,`password`,`head_img`,`province_id`,`province_name`,`city_id`,`city_name`,`area_id`,`area_name`,`address`,`status`,`create_time`,`update_time`
         * from `tb_user`
         * where id in (1,2,3,4,5)
         * @param ids
         * @return
         */
        @SQL(
                value = SELECT_SQL, condition = {
                @SQLCondition("#if(:1!=null){id in (:1)}"),
        })
        List<TbUser> findByIds(List<Integer> ids);
    
        /**
         * 产生sql:
         * select
         * `id`,`user_name`,`phone`,`password`,`head_img`,`province_id`,`province_name`,`city_id`,`city_name`,`area_id`,`area_name`,`address`,`status`,`create_time`,`update_time`
         * from `tb_user`
         * where
         * user_name in ('1','2','3','4','5')
         * @param names
         * @return
         */
        @SQL(
                value = SELECT_SQL, condition = {
                @SQLCondition("#if(:1!=null){user_name in (:1)}"),
        })
        List<TbUser> findByNames(List<String> names);
    
        /**
         * 产生sql:
         * select
         * `id`,`user_name`,`phone`,`password`,`head_img`,`province_id`,`province_name`,`city_id`,`city_name`,`area_id`,`area_name`,`address`,`status`,`create_time`,`update_time`
         * from `tb_user`
         * where
         * id in (1,2,3,4,5) and id in (1,2,3,4,5) and user_name in ('1','2','3','4','5')
         * @param names
         * @param ids
         * @return
         */
        @SQL(
                value = SELECT_SQL,
                condition = {
                        @SQLCondition(value = "#if(:2!=null){id in (:2)}", values = {"#if(:2!=null){id in (:2)}", "#if(:1!=null){user_name in (:1)}"})
                }
        )
        List<TbUser> findByNamesAndId(List<String> names, List<Integer> ids);
        
        /**
         * 分页 或者排序
         * @param departmentVo
         * @param pageRequest
         * @return
         */
        @SQL(value = SELECT_SQL,
                condition = {
                        @SQLCondition("#if(:vo.companyId!=null){company_id=:vo.companyId}"),
                        @SQLCondition("#if(:vo.name!=null){department_name like CONCAT('%',:vo.name,'%')}"),
                        @SQLCondition(value = "order by id", condition = ""),
                        @SQLCondition(value = "#if(:pageRequest != null){LIMIT :pageRequest.startNum,:pageRequest.pageSize}", condition = ""),
                }
        )
        List<TbDepartment> find(@SQLParam("vo") QueryDepartmentVo departmentVo, @SQLParam("pageRequest") PageRequest pageRequest);
  • 3.0 调整返回值支持Optional返回值
@SQL(SELECT_SQL + "where id = :id")
Optional<T> queryById(@SQLParam("id") Integer id);

项目简介

当前项目暂无项目简介

发行版本

当前项目没有发行版本

贡献者 1

菜鸟阿达 @qq_33547169

开发语言

  • Java 100.0 %