Mybatis-Plus多表联查
表格结构:
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>
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步