表格结构：

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映射进行多表查询，通过定义字段的映射关系：

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

实体类：

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 接口

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文件

<?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接口中已有的查询方法，避免重复定义子对象。

    <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

import lombok.Data;

@Data

public class PersonVO {

    String id;

    String name;

    Integer typeid;

    String typename;

}

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

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指定类型。

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

巴特西

Mybatis-Plus多表联查

使用Mapper文件

使用 VO

最新文章

热门文章