Mybatis-Plus多表联查

表格结构：

Copy
CREATE TABLE `ssmpdemo`.`person_test`  (
  `id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `type` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `type`(`type`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

CREATE TABLE `ssmpdemo`.`type_test`  (
  `id` int(4) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

使用Mapper文件#

使用Mapper映射进行多表查询，通过定义字段的映射关系：

1. 定义resultMap 定义当前实体的属性以及子对象的属性。
2. 和数据库字段名称相同的属性也要定义。
3. 外键可用 <association> 或者 <collection>。指定对应的类用 javaType="com.example.ssmpdemo.entity.MyType"字段。

实体类：

Copy
package com.example.ssmpdemo.entity;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

@Data
@TableName("person_test")
public class Person {
    @TableId
    @TableField("id")
    private String id;

    @TableField("name")
    private String name;

    @TableField(value = "type", exist = false)
    private MyType myType;
}

Mapper 接口

Copy
package com.example.ssmpdemo.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.ssmpdemo.entity.Person;
import java.util.List;
public interface PersonMapper extends BaseMapper<Person> {

    public List<Person> getFullData();
}

Mapper.xml文件

Copy
<?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="com.example.ssmpdemo.mapper.PersonMapper">

    <!--定义结果类型-->
    <resultMap id="BaseResultMap" type="com.example.ssmpdemo.entity.Person">
            <id property="id" column="id" jdbcType="VARCHAR"/>
            <result property="name" column="NAME" jdbcType="VARCHAR"/>
            <!--写成 association 和 collection 都可-->
            <association property="myType" javaType="com.example.ssmpdemo.entity.MyType">
                <id property="id" column="typeid" jdbcType="INTEGER" />
                <result property="name" column="typename" jdbcType="VARCHAR" />
            </association>
    </resultMap>
    
    <!--定义查询语句，注意字段名不要相同，不然在结果类型中有同名字段无法匹配-->
    <select id="getFullData" resultMap="BaseResultMap">
        select person_test.*, type_test.id as typeid, type_test.name as typename  from person_test, type_test where person_test.type=type_test.id
    </select>
</mapper>

Mapper.xml 文件也可以简化。<collection> 中可以使用Mapper接口中已有的查询方法，避免重复定义子对象。

Copy
    <resultMap id="BaseResultMap" type="com.example.ssmpdemo.entity.Person">
            <id property="id" column="id" jdbcType="VARCHAR"/>
            <result property="name" column="NAME" jdbcType="VARCHAR"/>
            <!--typeid表示外键字段-->
        <collection property="myType" column="typeid"
                    select="com.example.ssmpdemo.mapper.TypeMapper.selectById" />
    </resultMap>

使用 VO#

建立VO

Copy
import lombok.Data;
@Data
public class PersonVO {
    String id;
    String name;
    Integer typeid;
    String typename;
}

使用@Select指定查询sql，查询的字段需要一一对应。

Copy
import java.util.List;

public interface PersonMapper extends BaseMapper<Person> {
    public List<Person> getFullData();

    @Select("select person_test.id, person_test.name ,type_test.id as typeid, type_test.name as typename \n" +
            "from person_test, type_test \n" +
            "where person_test.type=type_test.id\n")
    public List<PersonVO> getFullData2();
}

也可使用Mapper.xml进行匹配。使用resultType指定类型。

Copy
    <select id="getFullData2" resultType="com.example.ssmpdemo.entity.vo.PersonVO">
        select person_test.*, type_test.id as typeid, type_test.name as typename  from person_test, type_test where person_test.type=type_test.id
    </select>