背景

最近使用Mybatis时候遇到一个场景需要操作两类表：
1、一套系列表，表结构和表个数完全相同，只有表名不通；
2、单个表，表名和表结构均不同；
需要满足以后再添加一套表不能修改代码，表名用${tableName}方式可能存在安全问题，所以比较适合用代码组装SQL方式执行，结合实践和网文总结两种方式实现；

扩展：
最好搞更高一层的抽象，可以设计一个代理层来做根据业务代理具体场景系列表的访问代理；

环境准备

SpringBoot 工程，添加 com.baomidou的mybatis-plus-boot-starter即可，最好使用h2内存数据库方便测试；

方式一

ibatis的annotations方式中直接使用CURD注解注入SQL实现，简单粗暴，注意应用层代码需要注意安全方面的设计，主要代码示例：

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface UserMapper extends BaseMapper<User> {

    @Select("${selSql}")
    List<User> queryBySql(@Param("selSql") String selSql);

    @Insert("${intSql}")
    int addBySql(@Param("intSql") String intSql);

    @Update("${updSql}")
    int updBySql(@Param("updSql") String updSql);

    @Delete("${delSql}")
    int delBySql(@Param("delSql") String delSql);


}

测试调用示例：

@Test
    public void testUserMapper() {
        //select
        String selSql = " select id, name, age, email from user ";
        List<User> list = userMapper.queryBySql(selSql);
        list.forEach(System.out::println);
        System.out.println(("----- insert a user test ------"));

        //insert
        String intSql = " insert into user( id, name, age, email) values(6, 'zhou', 90, 'zhou@163.com') ";
        int intCnt = userMapper.addBySql(intSql);
        System.out.println("intCnt:"+intCnt);
        System.out.println(("----- selectAll method test ------"));
        list = userMapper.queryBySql(selSql);
        list.forEach(System.out::println);

        //update
        String updSql = " update user set age = 100 where id = 6 ";
        int updCnt = userMapper.updBySql(updSql);
        System.out.println("updCnt:"+updCnt);
        System.out.println(("----- selectAll method test ------"));
        list = userMapper.queryBySql(selSql);
        list.forEach(System.out::println);

        //delete
        String delSql = " delete from user where id = 3 ";
        int delCnt = userMapper.delBySql(delSql);
        System.out.println("delCnt:"+delCnt);
        System.out.println(("----- selectAll method test ------"));
        list = userMapper.queryBySql(selSql);
        list.forEach(System.out::println);
    }

方式二

使用注解中的@CURDProvider方式实现，结合SQL语句构建器类，主要示例：

 @SelectProvider(type = BlogSqlProvider.class, method = "getSql")
    @Results(value = {
            @Result(id = true, property = "id", column = "id", javaType = Integer.class, jdbcType = JdbcType.INTEGER),
            @Result(property = "title", column = "title", javaType = String.class, jdbcType = JdbcType.VARCHAR),
            @Result(property = "date", column = "date", javaType = String.class, jdbcType = JdbcType.VARCHAR),
            @Result(property = "content", column = "content", javaType = String.class, jdbcType = JdbcType.VARCHAR),
    })
    Blog getBlog(@Param("id") int id);

    @SelectProvider(type = BlogSqlProvider.class, method = "getAllSql")
    @Results(value = {
            @Result(id = true, property = "id", column = "id", javaType = Integer.class, jdbcType = JdbcType.INTEGER),
            @Result(property = "title", column = "title", javaType = String.class, jdbcType = JdbcType.VARCHAR),
            @Result(property = "date", column = "date", javaType = String.class, jdbcType = JdbcType.VARCHAR),
            @Result(property = "content", column = "content", javaType = String.class, jdbcType = JdbcType.VARCHAR),
    })
    List<Blog> getAllBlog();

    @SelectProvider(type = BlogSqlProvider.class, method = "getSqlByTitle")
    @Results(value = {
            @Result(id = true, property = "id", column = "id", javaType = Integer.class, jdbcType = JdbcType.INTEGER),
            @Result(property = "title", column = "title", javaType = String.class, jdbcType = JdbcType.VARCHAR),
            @Result(property = "date", column = "date", javaType = String.class, jdbcType = JdbcType.VARCHAR),
            @Result(property = "content", column = "content", javaType = String.class, jdbcType = JdbcType.VARCHAR),
    })
    List<Blog> getBlogByTitle(@Param("title") String title);

    @InsertProvider(type = BlogSqlProvider.class, method = "insertSql")
    void insertBlog(Blog blog);

    @UpdateProvider(type = BlogSqlProvider.class, method = "updateSql")
     void updateBlog(Blog blog);

    @DeleteProvider(type = BlogSqlProvider.class, method = "deleteSql")
    void deleteBlog(int ids);

测试代码

 @Test
    public void testBlogMapper() {
        Blog blog = Blog.builder().id(5).title("title5").date("2019-08-10").content("content5").build();
        blogMapper.insertBlog(blog);

        System.out.println(("----- selectAll method test ------"));
        List<Blog> list = blogMapper.getAllBlog();
        Assert.assertEquals(5, list.size());
        list.forEach(System.out::println);
    }

码云工程

https://gitee.com/danni505/mp-sqlself

参考资料

http://www.mybatis.org/mybatis-3/zh/statement-builders.html