程序员社区
当前位置：IDEA激活码 > 未分类 > 正文

3.Mapper.xml 详解

2022-02-05 分类：未分类 阅读(904)

文章目录

    • Mapper.xml 详解
      • 1. parameterType
      • 2. resultType
      • 3. 级联查询
        • 3.1 一对多
        • 3.2 多对多

Mapper.xml 详解

MyBatis 主要有两个的配置文件：config.xml 和 Mapper.xml，这两个配置文件可以自定义文件名。

  1. config.xml 是全局配置文件，主要配置 MyBatis 的数据源(DataSource)，事务管理(TransactionManager)以及打印 SQL 语句，开启二级缓存，注册 Mapper.xml 等。
  2. 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 一对多

创建数据表

3.Mapper.xml 详解插图

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

创建数据表

3.Mapper.xml 详解插图1

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>

控制台打印结果如下：

在这里插入图片描述

赞(0) 打赏
未经允许不得转载：IDEA激活码 » 3.Mapper.xml 详解

相关推荐

IDEA永久激活破解教程,IDEA激活码

pycharm激活破解教程,pycharm激活码

Java视频资源

Java电子书

DataGrip激活教程 Docker Git教程 IntellIJ idea IntelliJ IDEA激活码 Java Java IO Java基础 Java基础面试题 Java集合面试题 Java面试题 JVM JVM面试题 Linux MySQL MySQL面试题 Nginx PyCharm PyCharm激活 redis Redis SpringBoot SpringBoot SpringBoot面试题 Web开发 剑指Offer系列 多线程 多线程并发面试题 字符串面试题 数据库 数据库 数据结构与算法 未分类 爬虫 电子书 软件下载

一个分享Java & Python知识的社区