Mapper.xml 常用配置、单表查询（paramterType、resultType）、多表关联查询（一对多、多对多）

本文链接：https://blog.youkuaiyun.com/weixin_44152682/article/details/124519880

本文详细介绍了MyBatis的Mapper.xml配置，包括全局环境配置和Mapper配置，重点讲解了单表查询中parameterType和resultType的使用，并探讨了一对多、多对多的关联查询。在多表查询部分，通过实例展示了collection和association的区别，以及如何在实体类和数据库表之间进行映射。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Mapper.xml 常用配置

MyBatis 配置文件有两种：
1、全局环境配置文件（数据源、事务管理、Mapper 注册、打印 SQL、惰性加载、二级缓存。。。）
2、Mapper 配置文件（定义自定义接口的具体实现方案：SQL、数据与 POJO 的映射）
多表关联查询包括一对一、一对多、多对多

单表查询

<select id="findById" parameterType="java.lang.Integer"
resultType="com.southwind.entity.People">
 select * from people where id = #{id}
</select>

业务：通过 id 查询 People 对象

目标表：test/people

实体类：com.southwind.entity.People

Mapper.xml 设置相关配置逻辑，由 MyBatis 自动完成查询，生成 POJO。

statement 标签主要属性有 id、parameterType、resultType，id 对应接口的方法名，parameterType 定义参数的数据类型、resultType 定义查询结果的数据类型（实体类的成员变量列表必须与目标表的字段列表一致）

paramterType

支持基本数据类型、包装类、String、多参数、POJO 等。

1、基本数据类型，通过 id 查询 POJO。

public People findById(int id);

<select id="findById" parameterType="int"
resultType="com.southwind.entity.People">
 select * from people where id = #{num}
</select>

2、包装类

public People findById(Integer id);

<select id="findById" parameterType="int"
resultType="com.southwind.entity.People">
 select * from people where id = #{num}
</select>

3、String 类型

public People findByName(String name);

<select id="findByName" parameterType="java.lang.String"
resultType="com.southwind.entity.People">
 select * from people where name = #{name}
</select>

4、多参数

public People findByIdAndName(Integer id,String name);

<select id="findByIdAndName" resultType="com.southwind.entity.People">
 select * from people where id = #{id} and name = #{name}
</select>

5、POJO

public int update(People people);

<update id="update" parameterType="com.southwind.entity.People">
 update people set name = #{name},money = #{money} where id = #{id}
</update>

resultType

resultType 与 parameterType 的使用基本⼀致。

1、基本数据类型

public int count();

<select id="count" resultType="int">
 select count(*) from people
</select>

2、包装类

public Integer count();

<select id="count" resultType="java.lang.Integer">
 select count(*) from people
</select>

3、String

public String findNameById(Integer id);

<select id="findNameById" parameterType="java.lang.Integer"
resultType="java.lang.String">
 select name from people where id = #{id}
</select>

4、POJO

public People findById(Integer id);

<select id="findById" parameterType="java.lang.Integer"
resultType="com.southwind.entity.People">
 select * from people where id = #{id}
</select>

多表关联查询

实际开发中最常用的是：⼀对多和多对多

一对多

1、建表

use test;
create table `t_classes`(
 `id` int(11) NOT NULL primary key auto_increment,
 `name` varchar(11) default null
);
create table `t_student`(
 `id` int(11) not null primary key auto_increment,
 `name` varchar(11) default null,
 `cid` int(11) default null,
 key `cid` (`cid`),
 constraint `t_student_ibfk_1` foreign key (`cid`) references
`t_classes`(`id`)
)

2、SQL

select s.id sid,s.name sname,c.id cid,c.name cname from t_student s,t_classes
c where s.id = 1 and s.cid = c.id

3、创建实体类

package com.southwind.entity;
import lombok.Data;
@Data
public class Student {
 private Integer id;
 private String name;
 private Classes classes; }

package com.southwind.entity;
import lombok.Data;
import java.util.List;
@Data
public class Classes {
 private Integer id;
 private String name;
 private List<Student> students; }

4、StudentRepository

package com.southwind.repository;
import com.southwind.entity.Student;
public interface StudentRepository {
 public Student findById(Integer id);
}

5、StudentRepository.xml

resultType 直接将结果集与实体类进行映射，结果集的字段名与实体类的成员变量名相等则映射。

resultMap 可以对结果集进行二次封装，根据需求来完成结果集数据到实体类的映射。

<?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.southwind.repository.StudentRepository">
 <resultMap id="studentMap" type="com.southwind.entity.Student">
 <id column="sid" property="id"></id>
 <result column="sname" property="name"></result>
 <association property="classes"
javaType="com.southwind.entity.Classes">
 <id property="id" column="cid"></id>
 <result property="name" column="cname"></result>
 </association>
 </resultMap>
 <select id="findById" parameterType="java.lang.Integer"
resultMap="studentMap">
 select s.id sid,s.name sname,c.id cid,c.name cname from t_student
s,t_classes c where s.id = 1 and s.cid = c.id
 </select>
 </mapper>

6、ClassesRepository

package com.southwind.repository;
import com.southwind.entity.Classes;
public interface ClassesRepository {
 public Classes findById(Integer id);
}

7、ClassesRepository.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.southwind.repository.ClassesRepository">
 <resultMap id="classesMap" type="com.southwind.entity.Classes">
 <id property="id" column="cid"></id>
 <result property="name" column="cname"></result>
 <collection property="students" ofType="com.southwind.entity.Student">
 <id property="id" column="sid"></id>
 <result property="name" column="sname"></result>
 </collection>
 </resultMap>
 <select id="findById" parameterType="java.lang.Integer"
resultMap="classesMap">
 select c.id cid,c.name cname,s.id sid,s.name sname from t_classes
c,t_student s where c.id = 1 and c.id = s.cid
 </select>
</mapper>

collection 和 association 的区别

collection 和 association 的区别：
collection 是将结果集封装成⼀个集合对象（多个目标对象）
association 是将结果集封装成⼀个实体类的对象（⼀个目标对象）
collection 是通过 ofType 设置数据类型，association 是通过 javaType 设置数据类型。

多对多

多对多是双向的⼀对多关系

1、建表

create table `t_account`(
 `id` int(11) not null primary key auto_increment,
 `name` varchar(11) default null
);
create table `t_course`(
 `id` int(11) not null primary key auto_increment,
 `name` varchar(11) default null
);
create table `account_course`(
 `id` int(11) not null primary key auto_increment,
 `aid` int(11) default null,
 `cid` int(11) default null,
 key `aid`(`aid`),
 key `cid`(`cid`),
 constraint `account_course_ibfk_1` foreign key (`aid`) references
`t_account`(`id`),
 constraint `account_course_ibfk_2` foreign key (`cid`) references
`t_course`(`id`)
);

2、创建实体类

package com.southwind.entity;
import lombok.Data;
import java.util.List;
@Data
public class Account {
 private Integer id;
 private String name;
 private List<Course> courses; }

package com.southwind.entity;
import lombok.Data;
import java.util.List;
@Data
public class Course {
 private Integer id;
 private String name;
 private List<Account> accounts; }

3、AccountRepository

package com.southwind.repository;
import com.southwind.entity.Account;
public interface AccountRepository {
 public Account findById(Integer id);
}

4、AccountRepository.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.southwind.repository.AccountRepository">
 <resultMap id="accoutMap" type="com.southwind.entity.Account">
 <id column="aid" property="id"></id>
 <result column="aname" property="name"></result>
 <collection property="courses" ofType="com.southwind.entity.Course">
 <id column="cid" property="id"/>
 <result column="cname" property="name"/>
 </collection>
 </resultMap>
 <select id="findById" parameterType="java.lang.Integer"
resultMap="accoutMap">
 select a.id aid,a.name aname,c.id cid,c.name cname from t_account
a,account_course ac,t_course c where a.id = #{id} and a.id = ac.aid and c.id =
ac.cid
 </select>
</mapper>

5、CourseRepository

package com.southwind.repository;
import com.southwind.entity.Course;
public interface CourseRepository {
 public Course findById(Integer id);
}

6、CourseRepository.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.southwind.repository.CourseRepository">
 <resultMap id="courseMap" type="com.southwind.entity.Course">
 <id column="cid" property="id"></id>
 <result column="cname" property="name"></result>
 <collection property="accounts" ofType="com.southwind.entity.Account">
 <id column="aid" property="id"/>
 <result column="aname" property="name"/>
 </collection>
 </resultMap>
 <select id="findById" parameterType="java.lang.Integer"
resultMap="courseMap">
 select a.id aid,a.name aname,c.id cid,c.name cname from t_account
a,account_course ac,t_course c where c.id = #{id} and a.id = ac.aid and c.id =
ac.cid
 </select>
</mapper>