连接查询

• 参考

• 参考

• pom.xml

<dependency>
    <groupId>com.github.yulichang</groupId>
    <artifactId>mybatis-plus-join</artifactId>
    <version>1.2.4</version>
</dependency>

• yml

server:
  port: 80
 
spring:
  application:
    name: demo06
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/dbtest?useUnicode=true&characterEncoding=UTF-8
    username: root
    password: 123456
  main:
    allow-circular-references: true
  devtools:
    restart:
      enabled: true
 
mybatis:
  configuration:
    map-underscore-to-camel-case: true
mybatis-plus:
  mapper-locations: classpath*:/mapper/*.xml
  global-config:
    db-config:
      property-format: "\"%s\""
logging:
  level:
    org:
      example:
        demo06: debug

• 实体类

@TableName("user")
@Data
public class User {
 
    @TableId
    private int id;
 
    @TableField("username")
    private String username;
 
    @TableField("password")
    private String password;
 
    @TableField("age")
    private Integer age;
 
    @TableField(exist = false)
    private String phone;
}
 
@TableName("book")
@Data
public class Book {
 
    @TableId
    private int id;
 
    @TableField("bookname")
    private String bookname;
 
    @TableField("price")
    private String price;
 
    @TableField("author")
    private String author;
 
}
 
@Data
public class BookDTO {
 
    private int id;
 
    private String username;
 
    private String password;
 
    private Integer age;
 
    private String bookname;
 
    private String price;
 
    private String author;
 
}

• mapper

@Component
public interface BookMapper extends MPJBaseMapper<Book> {
    
}
 
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.example.demo06.mapper.BookMapper">
 
</mapper>

• service

public interface BookService extends MPJBaseService<Book> {
 
}
 
@Service
public class BookServiceImpl extends MPJBaseServiceImpl<BookMapper, Book> implements BookService {
 
}

• controller

@RestController
@RequestMapping("book")
public class BookController {
 
    @Autowired
    private BookMapper bookMapper;
 
    /**
     * 连接查询
     * SELECT t1.id,t1.username,t1.password,t1.age,t.bookname,t.price,t.author FROM book t LEFT JOIN user t1 ON (t1.username = t.author) WHERE (t1.id = ?)
     * 1(Integer)
     * @return
     */
    @RequestMapping("/test1")
    @ResponseBody
    public String test1(){
        List<BookDTO> list = bookMapper.selectJoinList(BookDTO.class,
                new MPJLambdaWrapper<User>()
                        .selectAll(User.class)
                        .select(Book::getBookname, Book::getPrice, Book::getAuthor)
                        .leftJoin(User.class, User::getUsername, Book::getAuthor)
                        .eq(User::getId, 1));
        return "success";
    }
 
    /**
     * SELECT u.id,u.username,u.password,u.age,t.bookname,t.price,t.author FROM book t LEFT JOIN user u on u.username = t.author WHERE (u.id = ?)
     * 1(Integer)
     * @return
     */
    @RequestMapping("/test2")
    @ResponseBody
    public String test2(){
        List<BookDTO> list = bookMapper.selectJoinList(BookDTO.class,
                new MPJQueryWrapper<User>()
                        .select("u.id", "u.username", "u.password", "u.age")
                        .select("t.bookname", "t.price", "t.author")
                        .leftJoin("user u on u.username = t.author")
                        .eq("u.id", 1));
        return "success";
    }
 
}

• 简介

BookDTO.class 查询结果返回类(resultType)
selectAll() 查询指定实体类的全部字段
select() 查询指定的字段,支持可变参数,同一个select只能查询相同表的字段
selectAs() 字段别名查询,用于数据库字段与业务实体类属性名不一致时使用
leftJoin() 参数说明 第一个参数: 参与连表的实体类class 第二个参数: 连表的ON字段,这个属性必须是第一个参数实体类的属性 第三个参数: 参与连表的ON的另一个实体类属性
默认主表别名是t,其他的表别名以先后调用的顺序使用t1,t2,t3....
条件查询,可以查询主表以及参与连接的所有表的字段,全部调用mp原生的方法,正常使用没有sql注入风险

• 分页查询需要添加分页拦截器

@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor(){
    MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
    interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.H2));
    return interceptor;
}