使用MyBatis的动态SQL注解实现实体的CRUD操作代码_Java

1. 引言

在使用mybatis进行数据库操作时，动态sql注解提供了一种优雅的方式来编写动态sql语句。mybatis 3.x 版本提供了以下四个crud的高级注解：

@selectprovider：用于构建动态查询sql。
@insertprovider：用于构建动态新增sql。
@updateprovider：用于构建动态更新sql。
@deleteprovider：用于构建动态删除sql。

这些注解可以帮助开发者在mapper接口中动态地构建sql语句，避免了在xml配置文件中编写大量的sql代码，使代码更加简洁和易于维护。本文将详细介绍如何使用这些动态sql注解来实现书籍信息的查询、新增、修改和删除操作。

此外，我们将与mybatis plus中的@select注解进行对比，展示mybatis动态sql注解的优势和灵活性。

2. 准备工作

2.1 创建数据表

首先，需要创建一个代表书籍信息的表 tb_book。

-- 判断数据表是否存在，存在则删除
drop table if exists tb_book;

-- 创建“书籍信息”数据表
create table if not exists tb_book
(
    book_id int auto_increment primary key comment '书籍编号',
    book_name varchar(50) not null comment '书名',
    author varchar(50) not null comment '作者',
    publisher varchar(50) comment '出版社',
    publish_date date comment '出版日期'
) comment = '书籍信息表';

-- 添加数据
insert into tb_book(book_name, author, publisher, publish_date) values ('书籍1', '作者1', '出版社1', '2023-01-01');

2.2 创建实体类 book

在 com.zhouquan.entity 包中创建 book 类，使用 @data 注解来自动生成getter和setter方法。

package com.zhouquan.entity;
import lombok.data;
import java.util.date;

/**
 * 书籍信息实体类
 */
@data
public class book {
    /**
     * 书籍编号
     */
    private int bookid;
    
    /**
     * 书名
     */
    private string bookname;
    
    /**
     * 作者
     */
    private string author;
    
    /**
     * 出版社
     */
    private string publisher;
    
    /**
     * 出版日期
     */
    private date publishdate;
}

2.3 修改mapper接口 bookmapper

在 com.zhouquan.mapper 包中创建 bookmapper 接口，并使用动态sql注解来实现crud操作。

package com.zhouquan.mapper;

import com.zhouquan.entity.book;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.sql;
import org.springframework.stereotype.repository;

@mapper
@repository
public interface bookmapper {

    @selectprovider(type = booksqlbuilder.class, method = "buildgetbookbyidsql")
    public book getbookbyid(@param("bookid") int bookid);

    @insertprovider(type = booksqlbuilder.class, method = "buildinsertbooksql")
    @options(usegeneratedkeys = true, keycolumn = "book_id", keyproperty = "bookid")
    public int insertbook(book book);

    @updateprovider(type = booksqlbuilder.class, method = "buildupdatebooksql")
    public int updatebook(book book);

    @deleteprovider(type = booksqlbuilder.class, method = "builddeletebooksql")
    public int deletebook(@param("bookid") int bookid);

    class booksqlbuilder {
        public string buildgetbookbyidsql(@param("bookid") int bookid) {
            return new sql() {{
                select("*");
                from("tb_book");
                where("book_id = #{bookid}");
            }}.tostring();
        }

        public string buildinsertbooksql(book book) {
            return new sql() {{
                insert_into("tb_book");
                values("book_name", "#{bookname}");
                values("author", "#{author}");
                values("publisher", "#{publisher}");
                values("publish_date", "#{publishdate}");
            }}.tostring();
        }

        public string buildupdatebooksql(book book) {
            return new sql() {{
                update("tb_book");
                set("book_name = #{bookname}", "author = #{author}", "publisher = #{publisher}", "publish_date = #{publishdate}");
                where("book_id = #{bookid}");
            }}.tostring();
        }

        public string builddeletebooksql(@param("bookid") int bookid) {
            return new sql() {{
                delete_from("tb_book");
                where("book_id = #{bookid}");
            }}.tostring();
        }
    }
}

3. 测试crud操作

为了测试crud操作，我们将使用junit进行单元测试，并通过日志记录操作的结果。

3.1 配置日志

在maven的pom.xml文件中引入slf4j和logback的依赖：

<dependencies>
    <!-- mybatis 依赖 -->
    <dependency>
        <groupid>org.mybatis.spring.boot</groupid>
        <artifactid>mybatis-spring-boot-starter</artifactid>
        <version>2.1.4</version>
    </dependency>
    
    <!-- slf4j 依赖 -->
    <dependency>
        <groupid>org.slf4j</groupid>
        <artifactid>slf4j-api</artifactid>
        <version>1.7.30</version>
    </dependency>
  
</dependencies>

3.2 查询操作

import com.zhouquan.entity.book;
import com.zhouquan.mapper.bookmapper;
import org.junit.jupiter.api.test;
import org.springframework.beans.factory.annotation.autowired;
import org.slf4j.logger;
import org.slf4j.loggerfactory;

public class bookmappertest {

    private static final logger logger = loggerfactory.getlogger(bookmappertest.class);

    @autowired
    private bookmapper bookmapper;

    /**
     * 根据书籍id，获取书籍信息
     */
    @test
    public void getbookbyid() {
        book book = bookmapper.getbookbyid(1);
        logger.info("书籍编号：{}", book.getbookid());
        logger.info("书名：{}", book.getbookname());
        logger.info("作者：{}", book.getauthor());
        logger.info("出版社：{}", book.getpublisher());
        logger.info("出版日期：{}", book.getpublishdate());
    }
}

3.3 新增操作

@autowired
private bookmapper bookmapper;

/**
 * 新增书籍，并获取自增主键
 */
@test
public void insertbook() {
    book book = new book();
    book.setbookname("新书");
    book.setauthor("新作者");
    book.setpublisher("新出版社");
    book.setpublishdate(new date());
    bookmapper.insertbook(book);
    logger.info("新增书籍id：{}", book.getbookid());
}

3.4 修改操作

@autowired
private bookmapper bookmapper;

/**
 * 更新书籍信息
 */
@test
public void updatebook() {
    book book = bookmapper.getbookbyid(1);
    book.setbookname("更新后的书名");
    bookmapper.updatebook(book);
    logger.info("更新后的书名：{}", book.getbookname());
}

3.5 删除操作

@autowired
private bookmapper bookmapper;

/**
 * 删除书籍
 */
@test
public void deletebook() {
    bookmapper.deletebook(1);
    logger.info("删除书籍id为1的记录");
}

4. 与mybatis plus的对比

mybatis plus提供了一种更加简洁的方式来编写sql语句，例如，使用@select注解可以直接在mapper接口中编写sql语句，而无需单独定义sql构建器类。

例如，使用mybatis plus可以这样定义bookmapper接口：

import com.baomidou.mybatisplus.core.mapper.basemapper;
import com.zhouquan.entity.book;
import org.apache.ibatis.annotations.select;

public interface bookmapper extends basemapper<book> {

    @select("select * from tb_book where book_id = #{bookid}")
    book getbookbyid(int bookid);
}

这种方式相对于mybatis动态sql注解而言，更加直接和易读。但是，mybatis动态sql注解在处理复杂sql语句时具有更高的灵活性和可维护性，特别是当需要根据不同条件动态生成sql时，mybatis动态sql注解显得更为强大和适用

5. 动态sql注解的适用场景

5.1 动态查询条件

在一些应用中，查询条件是动态的，可能根据不同的用户输入或业务逻辑生成不同的查询条件。使用动态sql注解可以在运行时根据这些条件动态构建查询语句，避免了硬编码查询条件的问题。

public string builddynamicquerysql(map<string, object> params) {
    return new sql() {{
        select("*");
        from("tb_book");
        if (params.get("author") != null) {
            where("author = #{author}");
        }
        if (params.get("publisher") != null) {
            where("publisher = #{publisher}");
        }
        if (params.get("publishdate") != null) {
            where("publish_date = #{publishdate}");
        }
    }}.tostring();
}

5.2 动态插入和更新

在一些情况下，插入或更新的数据字段可能不是固定的，而是根据业务需求动态变化的。使用动态sql注解可以根据传入的实体对象构建动态插入或更新语句。

public string builddynamicinsertsql(book book) {
    return new sql() {{
        insert_into("tb_book");
        if (book.getbookname() != null) {
            values("book_name", "#{bookname}");
        }
        if (book.getauthor() != null) {
            values("author", "#{author}");
        }
        if (book.getpublisher() != null) {
            values("publisher", "#{publisher}");
        }
        if (book.getpublishdate() != null) {
            values("publish_date", "#{publishdate}");
        }
    }}.tostring();
}

public string builddynamicupdatesql(book book) {
    return new sql() {{
        update("tb_book");
        if (book.getbookname() != null) {
            set("book_name = #{bookname}");
        }
        if (book.getauthor() != null) {
            set("author = #{author}");
        }
        if (book.getpublisher() != null) {
            set("publisher = #{publisher}");
        }
        if (book.getpublishdate() != null) {
            set("publish_date = #{publishdate}");
        }
        where("book_id = #{bookid}");
    }}.tostring();
}

5.3 复杂的业务逻辑

在一些复杂的业务场景中，sql语句的构建逻辑可能非常复杂，涉及多个表的联接、多种条件的判断等。使用动态sql注解可以在java代码中使用条件逻辑来构建复杂的sql语句，使代码更加清晰和易于维护。

public string buildcomplexquerysql(book book) {
    return new sql() {{
        select("b.*, a.author_name, p.publisher_name");
        from("tb_book b");
        join("tb_author a on b.author_id = a.author_id");
        join("tb_publisher p on b.publisher_id = p.publisher_id");
        if (book.getbookname() != null) {
            where("b.book_name like concat('%', #{bookname}, '%')");
        }
        if (book.getauthor() != null) {
            where("a.author_name like concat('%', #{author}, '%')");
        }
        if (book.getpublisher() != null) {
            where("p.publisher_name like concat('%', #{publisher}, '%')");
        }
    }}.tostring();
}

5.4 查询结果动态列的聚合

需求类似于下，根据专题聚合出不同的资料类型，select中的列需要动态的拼接，显然mp的mapper对于此种需求的支持并不友好

 /**
     * 资源量统计-根据专题
     * 之所以使用此种方式是因为统计的列是不固定的，所以需要动态拼接select
     *
     * @param resourcetypenamelist 资源名称列表
     * @param resourcetypesidlist  资源sid列表
     * @param start                加工开始时间
     * @param end                  加工结束时间
     * @return
     */
    public string grouptopicdynamicsql(final list<string> resourcetypenamelist, final list<string> resourcetypesidlist,
                                       final localdate start, final localdate end) {
        // 构建外部查询
        sql outerquery = new sql();
        outerquery.select("coalesce(topic_name, '总计') as '专题'");

        if (resourcetypenamelist != null && !resourcetypenamelist.isempty()) {
            for (string rt : resourcetypenamelist) {
                outerquery.select(string.format("sum(case when resource_type_name = '%s' then count else 0 end) as " +
                        "'%s'", rt, rt));
            }
        }

        // 构建子查询
        sql subquery = new sql();
        subquery.select("rt.name as resource_type_name, t.name as topic_name, count(*) as count")
                .from("works w")
                .join("works_topic wt on w.sid = wt.work_sid")
                .join("topic t on wt.topic_sid = t.sid")
                .join("resource_type rt on w.type_leaf_sid = rt.sid")
                .where(" w.deleted=0 ");

        if (resourcetypenamelist != null && !resourcetypenamelist.isempty()) {
            string joinedresourcetypes = resourcetypesidlist.stream()
                    .map(rt -> "'" + rt + "'")
                    .collect(collectors.joining(", "));
            subquery.where("w.type_leaf_sid in (" + joinedresourcetypes + ")");
        }
        if (start != null) {
            subquery.where("w.update_time >= #{start}");
        }
        if (end != null) {
            subquery.where("w.update_time <= #{end}");
        }
        subquery.group_by("rt.name, t.name");

        outerquery.select("sum(count) as '总计'")
                .from("(" + subquery + ") as subquery")
                .group_by("topic_name with rollup");

        return outerquery.tostring();
    }

/**
 * 统计分析-资源量统计
 *
 * @param resourcetypenamelist
 * @param resourcetypesidlist
 * @param start
 * @param end
 * @return
 */
@selectprovider(type = workssqlprovider.class, method = "grouptopicdynamicsql")
list<map<string, object>> staticbytopic(@param("resourcetypenamelist") list<string> resourcetypenamelist,
                                        @param("resourcetypesidlist") list<string> resourcetypesidlist,
                                        @param("start") localdate start,
                                        @param("end") localdate end);