car-wash-java/jdbc.md

Comm-JDBC 使用指引

1. 说明

a.本框架基于springboot jdbcTemplate二次封装，支持事务，分页，批量、函数、存储过程操作等，支持MySQL、PostgreSQL等多方言关系数据的读写操作。
b.数据核心pojo类一一对应数据库表，使用自定义的注解解析器动态生成SQL，相较于mybatis支持pojo类配置封装类型如List、Set、Map、自定义类的持久化和序列化反序列化操作，无须配置类型处理器，无须配置mapper.xml。
c.除了pojo类注解方式，也兼容自定义SQL读写操作，此时封装类需自行处理（适用于复杂SQL）。
d.代码生成类 /wash/car-wash-admin/src/main/java/com/kym/admin/utils/CommJdbcCodeGenerator.java

2. 基类

DbHandler：所有DAO类继承该类，提供基础CRUD操作，支持事务，分页，批量操作等。
BasicQuery：查询封装基类（第一种，适用于暴露前端接口参数定义，默认分页30），预封装查询参数，动态生成关联查询条件、排序SQL语句。
OBuilder：查询封装类（第二种，适用于后端业务查询），按需查询，类似mybatisplus语法。

3. 使用

以知识库t_org_doc表为例，介绍如何使用本框架进行CRUD操作。

3.1 定义实体类(OrgDoc)

package com.png.entity.po.org;

import com.png.entity.bo.AccessStrategy;
import com.png.entity.po.user.User;
import com.png.entity.vo.SimpleVo;
import com.png.entity.vo.SimpleVo.SimpleAttachVo;
import com.png.jdbc.BasicQuery;
import com.png.jdbc.annotations.DBF;
import com.png.jdbc.annotations.Entity;
import com.png.jdbc.annotations.FK;
import com.png.jdbc.annotations.OP;
import com.png.jdbc.annotations.One;
import com.png.jdbc.annotations.QE;
import com.png.jdbc.annotations.QF;
import jakarta.validation.constraints.NotEmpty;
import lombok.Data;
import lombok.EqualsAndHashCode;

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


/**
 * 知识库
 *
 * @author yaop
 * @date 2024-02-03T10:44:52.780
 */
@Data
@EqualsAndHashCode(callSuper = false)
@Entity(clz = OrgDoc.class, comment = "知识库")
public class OrgDoc implements Serializable {

    public static final int STATUS_拟稿中= 1;
    public static final int STATUS_已发布= 2;

    @DBF(comment = "ID")
    private long id;
    @DBF(comment = "组织")
    private long oid;
    @DBF(comment = "应用")
    private long appletId;

    @NotEmpty(message = "名称不能为空")
    @DBF(comment = "名称", max = 128)
    private String name;

    @DBF(comment = "摘要", max = 512)
    private String digest;
    @DBF(comment = "文章目录")
    private List<OrgDocOutline> outlineList;
    @DBF(comment = "是否目录")
    private boolean directory;
    @DBF(comment = "父级目录")
    private long pid;
    @DBF(comment = "所在目录结构")
    private List<String> pathList;
    @DBF(comment = "状态")
    private int status;
    @DBF(comment = "排序权重")
    private int weight;

    @DBF(comment = "附件清单")
    private List<Long> attachIdList;
    @DBF(comment = "附件清单")
    private List<SimpleAttachVo> attachList;

    @DBF(comment = "是否删除")
    private boolean deleted;


    @DBF(comment = "是否开启访问控制")
    private boolean accessAvailable;

    @DBF(comment = "访问策略")
    private AccessStrategy accessStrategy;


    @DBF(comment = "创建时间")
    private Date createAt;
    @FK(clz = User.class)
    @DBF(comment = "创建人")
    private long createBy;
    @DBF(comment = "更新时间")
    private Date updateAt;

    @FK(clz = User.class)
    @DBF(comment = "更新人")
    private long updateBy;
    @DBF(comment = "源文章")
    private long sid;
    @DBF(comment = "是否最新版本")
    private boolean latest;
    @DBF(comment = "版本号")
    private int version;
    //region 版本相关

    //endregion

    @Data
    @EqualsAndHashCode(callSuper = true)
    public static class OrgDocInfo extends OrgDoc {
        @One(mkf = "createBy", tf = "name", comment = "创建人")
        private String createName;

        //扩展类字段，查询Info扩展类时自动生成SQL
        @One(mkf = "updateBy", tf = "name", comment = "更新人")
        private String updateName;

        //扩展类字段，无注解不会持久化
        private String content;
    }

    /**
     * 富文本目录结构
     */
    @Data
    public static class OrgDocOutline {
        private String id;
        /**
         * 目录名称（或内容）
         */
        private String text;
        /**
         * level: 目录级别，值是 1 ~ 6，对应的 html 标签是 h1 ~ h6
         */
        private int level;
        /**
         * 目录节点所在文档位置
         */
        private int pos;
        /**
         * 目录节点的内容大小
         */
        private int size;
    }

    /**
     *查询实体类，字段为封装类型，框架根据非空值生成SQL
     */
    @Data
    @EqualsAndHashCode(callSuper = true)
    @QE(clz = OrgDocInfo.class)
    public static class OrgDocBasicQuery extends BasicQuery {
        private Long id;
        private Long orgId;
        private Long appletId;
        private String name;
        private String digest;
        private Long pid;
        private Integer status;
        private String attachIdList;
        private String attachList;
        private String userIdList;
        private String userList;
        private String roleList;
        private String roleIdList;
        private Long createBy;
        private Long updateBy;
        private Long sid;
        private Boolean latest;
        private Integer version;

        //region date query
        @QF(op = OP.GTE, tf = "createAt")
        private Date createAtStart;
        @QF(op = OP.LTE, tf = "createAt")
        private Date createAtEnd;
        @QF(op = OP.GTE, tf = "updateAt")
        private Date updateAtStart;
        @QF(op = OP.LTE, tf = "updateAt")
        private Date updateAtEnd;
        //endregion
        
        //json数组查询（第一种，自定义sql片段）
        @QF(sql="json_contains(l.attach_id_list,?) ")
        private Long attachId;

        //json数据查询（第二种）
        @QF(op = OP.JIN, tf = "attachIdList")
        private Long attachId;

        //关联查询
        @QF(tf = "name",pkf = "createBy")
        private String createName;


        //region sort query  1-升序  2-降序
        private Integer idSort;
        private Integer orgIdSort;
        private Integer appletIdSort;
        private Integer pidSort;
        private Integer statusSort;
        private Integer createAtSort;
        private Integer createBySort;
        private Integer updateAtSort;
        private Integer updateBySort;
        //endregion
    }
}

3.2 定义持久类(OrgDocDAO)

package com.png.dao.org;

import com.png.JacksonUtil;
import com.png.dao.DbHandler;
import org.springframework.stereotype.Component;

import java.util.List;


@Component("orgDocDAO")
public class OrgDocDAO extends DbHandler {

    /**
     * 更新旧版本标识
     * @param sid
     * @param latest
     */
    public void updateVersionLatest(long sid, boolean latest) {
        update("update t_org_doc set latest = ? where sid = ?", latest, sid);
    }


    /**
     * 更新旧版本的文档路径
     * @param sid
     * @param pathList
     */
    public void updateOldVersionPath(long sid, List<String> pathList) {
        update("update t_org_doc set path_list=? where sid =?", JacksonUtil.toJSONString(pathList),sid);

    }
}

3.3 编写业务类（OrgDocService）

package com.png.service.org;


/**
 * 知识库业务类
 *
 * @author yaop
 * @date 2024-02-03T10:44:52.780
 */
@Service("orgDocService")
public class OrgDocService extends IService {

    @Resource
    private OrgDocDAO orgDocDAO;

}

3.3.1 插入-insert

    OrgDoc orgDoc = new OrgDoc();
    orgDoc.setSid(orgDoc.getId());
        orgDoc.setLatest(true);
    orgDoc.setVersion(1);
    if (orgDoc.getPid() == 0) {
        orgDoc.setPathList(List.of("/"));
        } else {
    OrgDoc parent = orgDocDAO.selectOneExist(OrgDoc.class, orgDoc.getPid());
        parent.getPathList().add(parent.getName());
        //封装类型字段，除业务处理外不做其他操作
        orgDoc.setPathList(parent.getPathList());
        }
//插入数据库，有ID返回
long id = orgDocDAO.insert(orgDoc);
//插入数据库，无ID返回
    orgDocDAO.insertOnly(orgDoc);
//插入数据库，有ID返回，跳过status、digest字段
    orgDocDAO.insertSelective(orgDoc,"status","digest");
//插入数据库，无ID返回，跳过status、digest字段
    orgDocDAO.insertSelectiveOnly(orgDoc,"status","digest");
//批量插入
    orgDocDAO.insertBatch(orgDocs);
//批量插入 ,仅插入id name字段 
    orgDocDAO.insertBatch(List.of("name","id"),orgDocs);
        //批量插入,自定义sql方式
    orgDocDAO.insertBatch("insert into t_org_doc (id,name) values (?,?)",orgDocs);

3.3.2 删除-delete

    //删除ID为1的知识库记录
    orgDocDAO.delete(OrgDoc.class,1);
//删除状态有效且创建时间大于‘2024-09-01’的知识库记录，builder方式条件构造
    orgDocDAO. delete(User.class,OBuilder.build().eq(OrgDoc::getStatus,Consts.STATUS_有效).gt(OrgDoc::getCreateAt,date));

3.3.3 修改-update

    //更新ID为1的知识库记录的名称为wiki1111
    OrgDoc orgDoc = new OrgDoc();
    orgDoc.setId(1);
    orgDoc.setName("wiki11111");
    orgDocDAO.update(orgDoc);
//更新ID为1的知识库记录
    orgDocDAO. update(orgDoc,OBuilder.build().eq(OrgDoc::getID,1));
  //更新ID为1的知识库记录
    orgDocDAO. update("UPDATE t_org_doc set name = ?  where id =? ","wiki111",1)); 
//批量更新,默认where条件为id
    orgDocDAO. updateBatch(List.of("name"),orgDocs);
    //批量更新,sql方式
    orgDocDAO. updateBatch("UPDATE t_org_doc set name = ?  where id =?  and status =? ",orgDocs);

3.3.4 查询-select

//查询ID为1的知识库记录，无关联扩展字段
OrgDoc orgDoc = orgDocDAO.selectOne(OrgDoc.class, OBuilder.build().eq(OrgDoc::getID, 1));
//查询ID为1的知识库记录,自动关联外键字段
OrgDocInfo orgDoc = orgDocDAO.selectOne(OrgDocInfo.class, OBuilder.build().eq(OrgDoc::getID, 1));
//查询ID为1的知识库记录,自动关联外键字段并校验记录是否存在，不存在时抛出异常IllegalArgumentException
OrgDocInfo orgDoc = orgDocDAO.selectOneExist(OrgDocInfo.class, OBuilder.build().eq(OrgDoc::getID, 1));
//查询ID为1的知识库记录, 无关联外键字段并校验记录是否存在，不存在时抛出异常IllegalArgumentException
OrgDoc orgDoc = orgDocDAO.selectOneExist(OrgDoc.class, 1);
//查询ID为1的知识库记录,自动关联外键字段,且仅查询id name attachList字段(封装类自动处理)
OrgDocInfo orgDoc = orgDocDAO.selectOne(OrgDocInfo.class, OBuilder.build().eq(OrgDoc::getID, 1), List.of("id","name","attachList"));
//查询ID为1的知识库记录,自动关联外键字段,且查询i排除d name attachList字段
OrgDocQuery query = new OrgDocQuery();
query.setId(1);
OrgDocInfo orgDoc = orgDocDAO.selectOneExclude(query, List.of("id","name","attachList"));

//查询I状态有效的的知识库记录列表,自动关联外键字段（第一种，builder构造查询条件）
List<OrgDocInfo> orgDocs = orgDocDAO.selectList(OrgDocInfo.class, OBuilder.build().eq(OrgDoc::getStatus, Consts.STATUS_有效));
//查询I状态有效的的知识库记录列表,自动关联外键字段且查询i排除d name attachList字段（第二种，query实体类查询）
OrgDocQuery query = new OrgDocQuery();
query.setStatus(Consts.STATUS_有效);
OrgDocInfo orgDoc = orgDocDAO.selectListExclude(query, List.of("id","name","attachList"));
//查询I状态有效的的知识库记录列表 (第三种，自定义sql语句)
List<OrgDocInfo> orgDocs = orgDocDAO.selectList("select id,name from t_org_doc",data->{
    OrgDocInfo info = new OrgDocInfo();
    info.setId(data.get("id"));
    return info;
},  Consts.STATUS_有效));