MybatisPlus注解多种方式实现一对一、一对多查询、增删改

环境搭建

创建SpringBoot项目，引入依赖

  <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

完成配置

server:
  port: 8089
spring:
  datasource:   #数据源
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/jdbc?useSSL=false&characterEncoding=utf8&serverTimezone=UTC
    username: root
    password: root

# mybatis-plus配置
mybatis-plus:
  configuration:
    map-underscore-to-camel-case: true  # 开启自动驼峰映射
  type-aliases-package: demo.domain  # 别名扫描包
  global-config:
    banner: off

创建数据库及表

Emp表

company表

项目结构图

实体类

员工实体

/**
 * 员工实体
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@TableName(value = "emp",autoResultMap = true)
public class Emp {

    private int id;

    private String username;

    private int age;


    private List<Company> companies;

    @Override
    public String toString() {
        return "Emp{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", age=" + age +
                ", companies=" + companies +
                '}';
    }

}

公司实体

/**
 * 公司实体
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@TableName(value = "company",autoResultMap = true)
public class Company {

    private int id;

    private String name;

    private Emp emp;


}

查询

EmpMapper

@Mapper
public interface EmpMapper {

    /**
     * 一般查询
     * @param id
     * @return
     */
    @Select("select * from emp where id=#{id}")
    public Emp getById(int id);

    /**
     * 多属性查询方式一
     * @param username
     * @param age
     * @return
     */
    @Select("select * from emp where username=#{username} and age=#{age}")
    public Emp getByNameAndAge(String username, int age);

    /**
     * 多属性查询方式二
     * 对象方式
     * @param emp
     * @return
     */
    @Select("select * from emp where username=#{username} and age=#{age}")
    public Emp getByNameAndAge(Emp emp);

	/**
	 根据emp id查询所属公司，配合一对多查询
	*/
	@Select("select * from company where eid=#{id}")
    public List<Company> findByEid(int id);
    
    /**
     * 一对多查询
     * @return
     */
    @Select("select * from emp")
    @Results({
            @Result(column = "id", property = "id"),
            @Result(column = "username", property = "username"),
            @Result(column = "age", property = "age"),
            @Result(
                    property = "companies",
                    column = "id",
                    javaType = List.class,
                    many = @Many(select = "demo.mapper.EmpMapper.findByEid")
            )
    })
    public List<Emp> getEmpAndCompany();


	/**
     * 一对一查询，方式一
     * @return
     */
    @Select("select *,c.id cid from emp e,company c where e.id=c.eid")
    @Results({
            @Result(column = "cid",property = "id"),
            @Result(column = "name",property = "name"),
            @Result(column = "eid",property = "emp.id"),
            @Result(column = "username",property = "emp.username"),
            @Result(column = "age",property = "emp.age")
    })
    public List<Company> findAll();

	/**
     * 一对一查询，方式二
     * @return
     */
    @Select("select* from company")
    @Results({
            @Result(column = "id", property = "id"),
            @Result(column = "name", property = "name"),
            @Result(
                    property = "emp",
                    column = "eid",
                    javaType = demo.domain.Emp.class,
                    one = @One(select = "demo.mapper.EmpMapper.getById")
            )
    })
    public List<Company> findAll2();
}

增删改

EmpMapper

@Mapper
public interface EmpMapper{

    /**
     * 插入一条数据
     * @param emp
     */
    @Insert("insert into emp values(#{username},#{age})")
    public void add(Emp emp);

    /**
     * 修改数据
     * @param emp
     */
    @Update("update emp set age=#{age} where username=#{username}")
    public void update(Emp emp);

    /**
     * 删除数据
     * @param username
     */
    @Delete("delete from emp where username=#{username}")
    public void delete(String username);
}

测试类

@SpringBootTest
class DemoApplicationTests {

    @Resource
    private EmpMapper empMapper;

    @Test
    void testGetById(){
        Emp emp = empMapper.getById(1);
        System.out.println(emp);
    }
    
    @Test
    void testGetByNameAndAge(){
        Emp emp=new Emp();
        emp.setUsername("张三");
        emp.setAge(20);
        //Emp res = empMapper.getByNameAndAge("张三", 21);
        Emp res = empMapper.getByNameAndAge(emp);
        System.out.println(res);
    }
    
    @Test
    void testGetAll(){
        List<Company> companyList = empMapper.findByEid(1);
        System.out.println(companyList);
        
//        List<Emp> empList = empMapper.getEmpAndCompany();
//        System.out.println(empList);
        
//        List<Company> list = empMapper.findAll2();
//        System.out.println(list);

    }

}