Mapper.xml 详解
MyBatis 主要有两个的配置文件:config.xml 和 Mapper.xml,这两个配置文件可以自定义文件名。
- config.xml 是全局配置文件,主要配置 MyBatis 的数据源(DataSource),事务管理(TransactionManager)以及打印 SQL 语句,开启二级缓存,注册 Mapper.xml 等。
- Mapper.xml 的作用是什么?因为 MyBatis 是“半自动”的ORM框架,即SQL语句需要开发者自定义,MyBatis 的关注点在 POJO 与 SQL 之间的映射关系,所以 Mapper.xml 主要配置接口方法对应的 SQL 语句,即接口方法的具体实现。
准备测试环境如下:
实体类 User:
package com.training.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
//使用这个插件后,会默认生成属性的Getter与Setter方法
@AllArgsConstructor
@NoArgsConstructor
@Data
public class User {
private Integer id;
private String name;
private Double score;
}
自定义 UserMapper 接口
package com.training.mapper;
public interface UserMapper {
//定义相关的业务方法
//...
}
创建接口对应的 UserMapper.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.training.mapper.UserMapper">
<!--定义接口方法对应的 SQL 语句-->
</mapper>
测试类 Test
package com.training.test;
import com.training.entity.User;
import com.training.mapper.UserMapper;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
import java.util.List;
public class Test {
public static void main(String[] args) {
//加载MyBatis配置信息
InputStream inputStream = Test.class.getClassLoader().getResourceAsStream("config.xml");
//构建SQLSessionFactoryBuilder
SqlSessionFactoryBuilder builder = new
SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(inputStream);
//获取SqlSession
SqlSession sqlSession = factory.openSession();
//获取实现接口的代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//...
}
}
1. parameterType
parameterType 设置参数的数据类型,支持基本数据类型、包装类、String、多个参数以及 Java 对象。
1.1 基本数据类型
public interface UserMapper {
//通过id查询User
public User findById(int id);
}
<mapper namespace="com.training.mapper.UserMapper">
<select id="findById" parameterType="int" resultType="com.training.entity.User">
select * from user where id=#{id};
</select>
</mapper>
注:#{id}
可以替换任意名称,因为只有一个参数,所以无论映射名是什么,都可以完成映射。
1.2 包装类
public interface UserMapper {
//通过score查询User
public User findByScore(Double score);
}
<mapper namespace="com.training.mapper.UserMapper">
<select id="findByScore" parameterType="java.lang.Double" resultType="com.training.entity.User">
select * from user where score=#{score}
</select>
</mapper>
1.3 字符串 String
public interface UserMapper {
//通过name查询User
public User findByName(String name);
}
<mapper namespace="com.training.mapper.UserMapper">
<select id="findByName" parameterType="String" resultType="com.training.entity.User">
select * from user where name=#{name}
</select>
</mapper>
1.4 多个参数
多个参数的情况下,无法通过映射名来进行映射,而应该采用下标进行映射,即:[arg0, arg1, arg2, ……]
或者 [param1, param2, param3, ……]
。
public interface UserMapper {
//通过id和name查询User
public User findByIdAndName(Integer id, String name);
}
<mapper namespace="com.training.mapper.UserMapper">
<select id="findByIdAndName" resultType="com.training.entity.User">
<!--select * from user where id=#{arg0} and name=#{arg1};-->
select * from user where id=#{param1} and name=#{param2};
</select>
</mapper>
1.5 JavaBean
参数列表中的映射名就是实体类的属性名。
public interface UserMapper {
//根据User封装对象查找User
public User findByUser(User user);
}
<mapper namespace="com.training.mapper.UserMapper">
<select id="findByUser" parameterType="com.training.entity.User" resultType="com.training.entity.User">
select * from user where name=#{name} and score=#{score};
</select>
</mapper>
2. resultType
resultType 设置返回值的数据类型,支持基本数据类型、包装类、String、多个参数以及 Java 对象。
2.1 基本数据类型
public interface UserMapper {
//查询User的总数
public int getCount();
}
<mapper namespace="com.training.mapper.UserMapper">
<select id="getCount" resultType="int">
select count(*) from user;
</select>
</mapper>
2.2 包装类
public interface UserMapper {
//查询User的总数
public Integer getCount();
}
<mapper namespace="com.training.mapper.UserMapper">
<select id="getCount" resultType="java.lang.Integer">
select count(*) from user;
</select>
</mapper>
2.3 字符串 String
public interface UserMapper {
//根据id查询name
public String findNameById(Integer id);
}
<mapper namespace="com.training.mapper.UserMapper">
<select id="findNameById" parameterType="int" resultType="java.lang.String">
select name from user where id=#{id};
</select>
</mapper>
2.4 JavaBean
public interface UserMapper {
//通过id查询User
public User findById(Integer id);
}
<mapper namespace="com.training.mapper.UserMapper">
<select id="findById" parameterType="java.lang.Integer" resultType="com.training.entity.User">
select * from user where id=#{id};
</select>
</mapper>
3. 级联查询
上面介绍的查询都是基于 User 的单表查询,如果是多表关联查询,比如:Student 与 Class 一对多的关联查询、Student 与 Course 多对多的级联查询,应该如何处理呢?显然仅仅使用 resultType 无法完成了。
3.1 一对多
创建数据表
create table class
(
id int auto_increment primary key,
name varchar(22) null
);
create table student
(
id int auto_increment primary key,
name varchar(25) not null comment '姓名',
GPA double default 0 null comment '分数',
cid int null
);
数据表对应的实体类
package com.training.entity;
import lombok.Data;
@Data
public class Student {
private Integer id;
private String name;
private Double GPA;
private Integer cid;
private Class aClass;
}
package com.training.entity;
import lombok.Data;
@Data
public class Class {
private Integer id;
private String name;
private List<Student> students;
}
例1:根据学生 id 查询学生所在的班级信息
自定义接口 StudentMapper.java
package com.training.mapper;
import com.training.entity.Student;
public interface StudentMapper {
//查询学生的班级信息
public Student findById(Integer id);
}
SQL配置文件 StudentMapper.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.training.mapper.StudentMapper">
<resultMap id="studentMap" type="com.training.entity.Student">
<id column="sid" property="id"></id>
<result column="sname" property="name"></result>
<result column="GPA" property="GPA"></result>
<association property="aClass" javaType="com.training.entity.Class">
<id column="cid" property="id"></id>
<result column="cname" property="name"></result>
</association>
</resultMap>
<select id="findById" parameterType="java.lang.Integer" resultMap="studentMap">
select s.id sid, s.name sname, GPA, c.id cid, c.name cname
from student s,class c
where s.cid = c.id and s.id = 1;
</select>
</mapper>
例2:根据班级 id 查询所在班级的学生
自定义接口 ClassMapper.java
package com.training.mapper;
import com.training.entity.Class;
public interface ClassMapper {
public Class findById(Integer id);
}
SQL配置文件 StudentMapper.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.training.mapper.StudentMapper">
<resultMap id="studentMap" type="com.training.entity.Student">
<id column="sid" property="id"></id>
<result column="sname" property="name"></result>
<result column="GPA" property="GPA"></result>
<association property="aClass" javaType="com.training.entity.Class">
<id column="cid" property="id"></id>
<result column="cname" property="name"></result>
</association>
</resultMap>
<select id="findById" parameterType="java.lang.Integer" resultMap="studentMap">
select s.id sid, s.name sname, GPA, c.id cid, c.name cname
from student s,class c
where s.cid = c.id and s.id = 1;
</select>
</mapper>
3.2 多对多
创建数据表
create table student
(
id int auto_increment primary key,
name varchar(25) not null comment '姓名',
GPA double default 0 null comment '分数'
);
create table stu_course
(
id int auto_increment primary key,
sid int null,
cid int null
);
create table course
(
id int auto_increment primary key,
name varchar(225) null
);
数据表对应的实体类
package com.training.entity;
import lombok.Data;
import java.util.List;
@Data
public class Student {
private Integer id;
private String name;
private Double GPA;
private List<Course> courses;
}
package com.training.entity;
import lombok.Data;
import java.util.List;
@Data
public class Course {
private Integer id;
private String name;
private List<Student> students;
}
例1:通过学生 id 查询该学生的选课信息
自定义接口 StudentMapper.java
public interface StudentMapper {
public Student findById(Integer id);
}
SQL配置文件 StudentMapper.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.training.mapper.StudentMapper">
<resultMap id="studentMap" type="com.training.entity.Student">
<id column="sid" property="id"></id>
<result column="sname" property="name"></result>
<result column="GPA" property="GPA"></result>
<collection property="courses" ofType="com.training.entity.Course">
<id column="cid" property="id"></id>
<result column="cname" property="name"></result>
</collection>
</resultMap>
<select id="findById" parameterType="java.lang.Integer" resultMap="studentMap">
select s.id sid, s.name sname, GPA, c.id cid, c.name cname
from student s,stu_course sc,course c
where s.id = sc.sid and c.id = sc.cid and s.id = 1;
</select>
</mapper>
控制台打印结果如下:
例2:通过课程 id 查询选过该课的学生信息
自定义接口 CourseMapper.java
public interface ClassMapper {
public Class findById(Integer id);
}
SQL配置文件 CourseMapper.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.training.mapper.CourseMapper">
<resultMap id="courseMap" type="com.training.entity.Course">
<id column="cid" property="id"></id>
<result column="cname" property="name"></result>
<collection property="students" ofType="com.training.entity.Student">
<id column="sid" property="id"></id>
<result column="sname" property="name"></result>
<result column="GPA" property="GPA"></result>
</collection>
</resultMap>
<select id="findById" parameterType="java.lang.Integer" resultMap="courseMap">
select c.id cid, c.name cname, s.id sid, s.name sname, GPA
from student s,stu_course sc,course c
where s.id = sc.sid and c.id = sc.cid and c.id = 1;
</select>
</mapper>
控制台打印结果如下: