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;
}
