mybatis puls学习笔记（一）

mybatis plus使用方式

xml映射
纯注解
构建器
wrapper

已经废弃的构建器写法

public String selectBlogsSql() {
  BEGIN(); // 重置 ThreadLocal 状态变量
  SELECT("*");
  FROM("BLOG");
  return SQL();
}
 
private String selectPersonSql() {
  BEGIN(); // 重置 ThreadLocal 状态变量
  SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME");
  SELECT("P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON");
  FROM("PERSON P");
  FROM("ACCOUNT A");
  INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID");
  INNER_JOIN("COMPANY C on D.COMPANY_ID = C.ID");
  WHERE("P.ID = A.ID");
  WHERE("P.FIRST_NAME like ?");
  OR();
  WHERE("P.LAST_NAME like ?");
  GROUP_BY("P.ID");
  HAVING("P.LAST_NAME like ?");
  OR();
  HAVING("P.FIRST_NAME like ?");
  ORDER_BY("P.ID");
  ORDER_BY("P.FULL_NAME");
  return SQL();
}

构建器

package com.ychen.ddd.infrastructure.jpa.mapper;
 
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ychen.ddd.domain.aggregate1.model.entity.User;
import com.ychen.ddd.infrastructure.jpa.po.UserBookPo;
import com.ychen.ddd.interfaces.vo.UserBookReqDTO;
import com.ychen.ddd.interfaces.vo.UserBookRespDTO;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;
import java.util.Date;
import java.util.List;
 
/**
 * @Author: chenyuanqing
 * @Description:
 * @Date: Created in 10:36 2022/2/8
 */
@Mapper
public interface UserMapper extends BaseMapper<User> {
 
    /**
     * 查询所有用户信息
     * @return
     */
    @Select("select * from user")
    List<User> getAll();
 
    /**
     * 更新用户信息
     * @param user
     * @param id
     * @return
     */
    @UpdateProvider(type = UserProvider.class, method = "updateUser")
    int updateUser(@Param("user") User user, @Param("id") int id);
 
    class UserProvider {
        public String updateUser(@Param("user") User user, @Param("id") int id) {
            SQL sql = new SQL();
            sql.UPDATE("user");
            if(user.getUsername() != null) {
                sql.SET("username=#{user.username}");
            }
            if(user.getPassword() != null) {
                sql.SET("password = #{user.password}");
            }
            if(user.getSex() != null) {
                sql.SET("sex = #{user.sex}");
            }
            if(user.getBirthday() != null) {
                sql.SET("birthday = #{user.birthday}");
            }
            sql.WHERE("id = #{id}");
            return sql.toString();
        }
    }
 
    /**
     * 添加用户
     */
    @InsertProvider(type = addUserProvider.class, method = "addUser")
    Integer addUser(@Param("username") String username, @Param("password") String password, @Param("birthday") Date birthday);
 
    class addUserProvider {
        public String addUser(@Param("username") String username, @Param("password") String password, @Param("birthday") Date birthday) {
            String sql = new SQL()
                    .INSERT_INTO("user")
                    .VALUES("username, password", "#{username}, #{password}")
                    .VALUES("birthday", "#{birthday}")
                    .toString();
            return sql;
        }
    }
 
    /**
     * 删除用户
     * @param id
     * @return
     */
    @DeleteProvider(type = delUserProvider.class, method = "deleteUser")
    int deleteUser(int id);
 
    class delUserProvider {
        public String deleteUser() {
            return new SQL() {
                {
                    DELETE_FROM("user");
                    WHERE("id= #{id}");
                }
            }.toString();
        }
    }
 
    /**
     * 连接查询
     * select u.id, u.username, u.password, u.sex, u.birthday,
     * b.id, b.bookname, b.author, b.price
     * from user u inner join book b on u.username = b.author;
     * @return
     */
    @SelectProvider(type = findUserProvider.class, method = "findUser")
    List<UserBookPo> findUser();
 
    class findUserProvider {
        public String findUser() {
            String sql = new SQL()
                    .SELECT("u.username", "u.password", "b.bookname", "b.author")
                    .FROM("user u")
                    .INNER_JOIN("book b")
                    .WHERE("u.username = b.author")
                    .toString();
            return sql;
        }
    }
 
    /**
     * 分页查询
     * SELECT * FROM user LIMIT 2,3;
     */
    @SelectProvider(type = findUsersProvider.class, method = "findUsers")
    List<User> findUsers();
 
    class findUsersProvider {
        public String findUsers() {
            String sql = new SQL()
                    .SELECT("id", "username", "password", "sex", "birthday")
                    .FROM("user")
                    .LIMIT("2, 3")
                    .toString();
            return sql;
        }
    }
 
    /**
     * 分页连接查询
     * @param
     * @return
     * select u.id, u.username, u.password, u.sex, u.birthday, b.id, b.bookname, b.author, b.price
     * from user u inner join book b on u.username = b.author
     * where
     * u.username = #{username}
     * or u.sex = #{sex}
     * or b.bookname = #{bookname}
     * or b.author = #{author}
     * or b.price = #{price}
     * limit #{pageNum}, #{pageSize}
     */
    @SelectProvider(type = getUserBookProvider.class, method = "getUserBook")
    List<UserBookRespDTO> getUserBook(UserBookReqDTO user);
 
    class getUserBookProvider {
        public String getUserBook(UserBookReqDTO user) {
            SQL sql = new SQL();
            sql.SELECT("u.id", "u.username", "u.password", "u.sex", "u.birthday", "b.id", "b.bookname", "b.author", "b.price")
            .FROM("user u").INNER_JOIN("book b").WHERE("u.username = b.author");
            if(user.getUsername() != null) {
                sql.SET("username=#{user.username}");
            }
            if(user.getSex() != null) {
                sql.SET("sex = #{user.sex}");
            }
            if(user.getBookname() != null) {
                sql.SET("bookname = #{user.bookname}");
            }
            if(user.getAuthor() != null) {
                sql.SET("author = #{user.author}");
            }
            if(user.getPrice() != null) {
                sql.SET("price = #{user.price}");
            }
            if(user.getPageSize() != null && user.getPageNum() != null) {
                sql.LIMIT("#{user.pageNum}, #{user.pageSize}");
            }
            return sql.toString();
        }
    }
 
    /**
     * 批量添加用户
     */
    @InsertProvider(type = addUserListProvider.class, method = "addUserList")
    Integer addUserList(@Param("username") String username, @Param("password") String password, @Param("birthday") Date birthday);
 
    class addUserListProvider {
        public String addUserList(@Param("username") String username, @Param("password") String password, @Param("birthday") Date birthday) {
            String sql = new SQL()
                    .INSERT_INTO("user")
                    .VALUES("username, password", "#{username}, #{password}")
                    .VALUES("birthday", "#{birthday}")
                    .toString();
            return sql;
        }
    }
 
}
 

SQL类

// 匿名内部类风格
public String deletePersonSql() {
  return new SQL() {{
    DELETE_FROM("PERSON");
    WHERE("ID = #{id}");
  }}.toString();
}
 
// Builder / Fluent 风格
public String insertPersonSql() {
  String sql = new SQL()
    .INSERT_INTO("PERSON")
    .VALUES("ID, FIRST_NAME", "#{id}, #{firstName}")
    .VALUES("LAST_NAME", "#{lastName}")
    .toString();
  return sql;
}