Mybatis学习二 实现关联表查询

字段名与属性名冲突

在介绍 Mybatis 的关联表查询实现之前，我们先来看一个小问题：如何解决字段名与实体类属性名不相同的冲突

首先准备表和数据

CREATE TABLE orders(
        order_id INT PRIMARY KEY AUTO_INCREMENT,
        order_no VARCHAR(20),
        order_price FLOAT
);
INSERT INTO orders(order_no, order_price) VALUES('aaa', 11);
INSERT INTO orders(order_no, order_price) VALUES('bbb', 22);
INSERT INTO orders(order_no, order_price) VALUES('ccc', 33);

工程结构如图

定义实体类

package com.bupt.mybatis.beans;

public class Order
{
    private int id;
    private String orderNo;
    private float price;
    //生成 setter 和 get 方法、带参和不带参的构造方法以及重写 toString() 方法
}

编写 Mybatis 配置文件 mybatis-conf.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    
    <typeAliases>
　　　　 <!-- 用类名来代替全类名 --!>
        <package name="com.bupt.mybatis.beans"/>
    </typeAliases>
    
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql:///test"/>
                <property name="username" value="root"/>
                <property name="password" value="000"/>
            </dataSource>
        </environment>
    </environments>
    
    <mappers>
　　　　 <!-- 将 SQL 映射文件注册到 Mybatis 配置文件中 --!>
        <mapper resource="com/bupt/mybatis/beanMappers/orderMapper.xml"/>
    </mappers>
</configuration>

如上代码可以看到，数据库中的字段名与我们在实体类中定义的属性名不一致，所以在查询数据库记录时与正常情况下不同

有两种方式来解决这种不一致

1. 通过在 SQL 语句中定义别名

SQL 映射文件 orderMapper.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.bupt.mybatis.beanMappers.orderMapper">
    <select id="getOrder" parameterType="int" resultType="Order">
        select order_id id, order_no orderNo, order_price price from orders where order_id=#{id}
    </select>
    
</mapper>

2. 通过 <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.bupt.mybatis.beanMappers.orderMapper">
    
    <select id="selectOrder" parameterType="int" resultMap="orderResultMap">
        select * from orders where order_id=#{id}
    </select>
    
　　<!-- resultMap 标签说明字段和属性之间的联系，column值为字段名，property值为属性名 --!>
　　<!-- type指定返回值的类型，id值需与上面定义的resultMap值一致 --!>
    <resultMap type="Order" id="orderResultMap">
        <id property="id" column="order_id"/>
        <result property="orderNo" column="order_no"/>
        <result property="price" column="order_price"/>
    </resultMap>
    
</mapper>

如此就解决了字段名与属性名不匹配的情况。

一对一关联

现在假设一种情况，两张表，一张班级表，包含老师 id 属性；另一张表为老师表，包含老师 id 和名字属性。现在根据班级 id 来查询班级信息，包括老师的信息。

首先新建表和数据

CREATE TABLE teacher(
        t_id INT PRIMARY KEY AUTO_INCREMENT,
        t_name VARCHAR(20),
);
CREATE TABLE class(
        c_id INT PRIMARY KEY AUTO_INCREMENT,
        c_name VARCHAR(20),
        teacher_id INT
);
ALTER TABLE class ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES teacher(t_id);

INSERT INTO teacher(t_name) VALUES('LS1');
INSERT INTO teacher(t_name) VALUES('LS2');

INSERT INTO class(c_name, teacher_id) VALUES('bj_a', 1);
INSERT INTO class(c_name, teacher_id) VALUES('bj_a', 2);

定义实体类

package com.bupt.mybatis.beans;

public class Teacher
{
    private int id;
    private String name;
    //生成 setter 和 get 方法、带参和不带参的构造方法以及重写 toString() 方法
}

package com.bupt.mybatis.beans;

import java.util.List;

public class Classes
{
    private int id;
    private String name;
    private Teacher teacher;
    //生成 setter 和 get 方法、带参和不带参的构造方法以及重写 toString() 方法
}

对于这种一对一的关联表查询，也有两种方式

1. 使用嵌套结果映射来处理重复的联合结果的子集封装链表查询的数据（去除重复数据）

相应的 SQL 映射文件 classMapper.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.bupt.mybatis.beanMappers.classMapper">
     
    <select id="getClass" parameterType="int" resultMap="classResultMap">
        select * from class c, teacher t where c.teacher_id=t.t_id AND c.c_id=#{id}
    </select>
    
    <resultMap type="Classes" id="classResultMap">
        <id column="c_id" property="id"/>
        <result column="c_name" property="name"/>
　　　　 <!-- 通过 association 标签来关联其他数据表，javaType指定对象属性类型 --!>
        <association column="teacher_id" property="teacher" javaType="Teacher">
            <id column="t_id" property="id"/>
            <result column="t_name" property="name"/>
        </association>
    </resultMap>

</mapper>

2.  通过执行另一个 SQL 映射语句来返回预期的复杂类型

可以分解为两步：

SELECT * FROM class WHERE c_id=1;

SELECT * FROM teacher WHERE t_id=1; //1是上一个查询得到的 teacher_id 的值

<?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.bupt.mybatis.beanMappers.classMapper">
    
     <select id="selectClass" parameterType="int" resultMap="classResultMap">
         select * from class where c_id=#{id}
     </select>
     
     <resultMap type="Classes" id="classResultMap">
         <id column="c_id" property="id"/>
         <result column="c_name" property="name"/>
　　　　　
　　　　　<!-- 通过 select 标签来指定执行下一条查询语句 --!>
         <association column="teacher_id" property="teacher" javaType="Teacher" select="getTeacher"/>
     </resultMap>
     
     <select id="getTeacher" parameterType="int" resultType="Teacher">
         select t_id id, t_name name from teacher where t_id=#{id}
     </select>
</mapper>

将 SQL 映射文件注册到 mybatis-conf.xml 配置文件中去

    <mappers>
        <mapper resource="com/bupt/mybatis/beanMappers/classMapper.xml"/>
    </mappers>

接下来，来看以下测试结果

package com.bupt.mybatis.test;

import java.io.InputStream;
import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import com.bupt.mybatis.beans.Classes;
import com.bupt.mybatis.beans.Order;

public class TestAlias
{
    InputStream is = TestAlias.class.getClassLoader().getResourceAsStream("mybatis-conf.xml");
    SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
    
    @Test
    public void testGetClass()
    {
        SqlSession session = sessionFactory.openSession();
        String statement = "com.bupt.mybatis.beanMappers.classMapper.getClass";
        
        try
        {
            Classes classes = session.selectOne(statement, 1);
            session.commit();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            session.close();
        }
       
        System.out.println(classes);
    }
    
    //这里这么写是为了偷懒，最好把session操作放到try-catch中
    public void testSelectClass()
    {
        SqlSession session = sessionFactory.openSession();
        String statement = "com.bupt.mybatis.beanMappers.classMapper.selectClass";
        Classes classes = session.selectOne(statement, 2);
        session.commit();
        System.out.println(classes);
    }
}

执行 getClass 的结果（第一种方法）

 执行 selectClass 的结果（第二种方法）

一对多关联

除了一对一关联之外，还会存在一对多的关联

首先新建一张表

CREATE TABLE student(
        s_id INT PRIMARY KEY AUTO_INCREMENT,
        s_name VARCHAR(20),
        class_id INT
);
INSERT INTO student(s_name, class_id) VALUES('xs_A', 1);
INSERT INTO student(s_name, class_id) VALUES('xs_B', 1);
INSERT INTO student(s_name, class_id) VALUES('xs_C', 1);
INSERT INTO student(s_name, class_id) VALUES('xs_D', 2);
INSERT INTO student(s_name, class_id) VALUES('xs_E', 2);
INSERT INTO student(s_name, class_id) VALUES('xs_F', 2);

要求根据 Class 的 id 查询对应的班级信息，包括学生、老师

创建 Student 实体类

package com.bupt.mybatis.beans;

public class Student
{
    private int id;
    private String name;
    //生成 setter 和 get 方法、带参和不带参的构造方法以及重写 toString() 方法
}

另外需要修改 Classes 类

package com.bupt.mybatis.beans;

import java.util.List;

public class Classes
{
    private int id;
    private String name;
    private Teacher teacher;
    private List<Student> student;
　　//生成 setter 和 get 方法、带参和不带参的构造方法以及重写 toString() 方法
}

跟一对一情况一样，这里也介绍两种 SQL 映射文件的写法，而且两者方法类似

1. 1. 使用嵌套结果映射来处理重复的联合结果子集

<?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.bupt.mybatis.beanMappers.classesMapper">

    <select id="getClass" parameterType="int" resultMap="classResultMap">
        select * from class c, teacher t, student s where c.teacher_id=t.t_id AND c.c_id=s.class_id AND c.c_id=#{id}
    </select>
    
    <resultMap type="Classes" id="classResultMap">
        <id column="c_id" property="id"/>
        <result column="c_name" property="name"/>
        
　　　　 <association column="teacher_id" property="teacher" javaType="Teacher">
            <id column="t_id" property="id"/>
            <result column="t_name" property="name"/>
        </association>
　　　　
　　　　 <!-- collection 用来做一对多关联查询，ofType 指定集合中元素对象的类型 --!>
        <collection property="student" ofType="Student">
            <id column="s_id" property="id"/>
            <result column="s_name" property="name"/>
        </collection>
    </resultMap>
   
</mapper>

 2.2. 通过执行另外一个 SQL 映射语句来返回预期的复杂类型

分解为三步：

SELECT * FROM class WHERE c_id=1;

SELECT * FROM teacher WHERE t_id=1; //1是上一个查询得到的 teacher_id 的值

SELECT * FROM student WHERE class_id=1; //1是上一个查询得到的 c_id 字段的值

<?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.bupt.mybatis.beanMappers.classesMapper">
     
     <select id="selectClass" parameterType="int" resultMap="classResultMap">
         select * from class where c_id=#{id}
     </select>
     
     <resultMap type="Classes" id="classResultMap">
         <id column="c_id" property="id"/>
         <result column="c_name" property="name"/>
         <association column="teacher_id" property="teacher" javaType="Teacher" select="getTeacher"/>
         <collection column="c_id" property="student" ofType="Teacher" select="getStudent"/>
     </resultMap>

    <select id="getTeacher" parameterType="int" resultType="Teacher">
        select t_id id, t_name name from teacher where t_id=#{id} 
    </select>
    
    <select id="getStudent" parameterType="int" resultType="Student">
        select s_id id, s_name name from student where class_id=#{id}
    </select>     
</mapper>

在 mybatis-conf.xml中 注册 SQL 映射文件

    <mappers>
        <mapper resource="com/bupt/mybatis/beanMappers/classesMapper.xml"/>
    </mappers>

测试类中编写测试方法

    @Test
    public void testOne2More1()
    {
        String statement = "com.bupt.mybatis.beanMappers.classesMapper.getClass";
        Sqlsession session = sessionFactory.openSession(true);
　　　　 Classes classes = session.selectOne(statement, 1);
        System.out.println(classes);
    }
    
    @Test
    public void testOne2More2()
    {
        String statement = "com.bupt.mybatis.beanMappers.classesMapper.selectClass";
        SqlSession session = sessionFactory.openSession(true);
　　　　 Classes classes = session.selectOne(statement, 2);
        System.out.println(classes);
    }

测试结果分别如下图

动态 SQL 与模糊查询

所谓SQL的动态和静态，是指SQL语句在何时被编译和执行。

在某种高级语言中，如果嵌入了SQL语句，而这个SQL语句的主体结构已经明确，例如在Java的一段代码中有一个待执行的SQL "select * from t1 where c1>5"，在Java编译阶段，就可以将这段SQL交给数据库管理系统去分析，数据库软件可以对这段SQL进行语法解析，生成数据库方面的可执行代码，这样的SQL称为静态SQL，即在编译阶段就可以确定数据库要做什么事情。

而如果嵌入的SQL没有明确给出，如在Java中定义了一个字符串类型的变量sql：String sql;，然后采用 preparedStatement 对象的 execute() 方法去执行这个sql，该sql的值可能等于从文本框中读取的一个SQL或者从键盘输入的SQL，但具体是什么，在编译时无法确定，只有等到程序运行起来，在执行的过程中才能确定，这种SQL叫做动态SQL。例如每一种数据库软件都有能够执行SQL语句的界面，那个界面接收的SQL就是动态SQL，因为数据库厂商在做这个界面时，并不知道用户会输入哪些SQL，只有在该界面执行后，接收了用户的实际输入，才知道SQL是什么。另外还要注意一点，在SQL中如果某些参数没有确定，如 "select * from t1 where c1>? and c2<?"，这种语句是静态SQL，不是动态SQL，虽然个别参数的值不知道，但整个SQL的结构已经确定，数据库是可以将它编译的，在执行阶段只需将个别参数的值补充进来即可。

Mybatis 中可用的动态 SQL 标签包括

a. if

b. choose(when, otherwise)

c. trim(where, set)

d. foreach

这里用 if 标签作为例子，其他标签可以去前一篇 mybatis 的博文最后下载 mybatis 的中文官方文档，里面有较详细的介绍

下面我们直接看例子

先创建一张表和数据

CREATE TABLE d_user(
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(10),
        age INT(3)
);

INSERT INTO d_user(name, age) VALUES('Tom', 12);
INSERT INTO d_user(name, age) VALUES('Bob', 13);
INSERT INTO d_user(name, age) VALUES('Jack', 18);

创建实体类

package com.bupt.mybatis.beans;

public class User
{
    private int id;
    private String name;
    private int age;
    //生成 setter 和 get 方法、带参和不带参的构造方法以及重写 toString() 方法
}

创建查询条件实体类

package com.bupt.mybatis.beans;

public class ConditionUser
{
    private String name;
    private int maxAge;
    private int minAge;
    public ConditionUser(String name, int minAge, int maxAge)
    {
        super();
        this.name = name;
        this.maxAge = maxAge;
        this.minAge = minAge;
    }
    public ConditionUser()
    {
        super();
    }
}

编写 SQL 映射文件 userMapper.xml ，同时记得在配置文件中注册此文件

<if test="条件判断语句"></if>

<?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.bupt.mybatis.beanMappers.userMapper">
    
    <select id="selectUser" parameterType="ConditionUser" resultType="User">
        select * from d_user where age>=#{minAge} and age&lt;=#{maxAge}
        
　　　　 <!-- 满足 test 中定义的语句才会执行if体内的语句 -->
　　　　 <if test="name!='%null%'">and name like #{name}</if>
    </select>
    
</mapper>

    <mappers>
        <mapper resource="com/bupt/mybatis/beanMappers/userMapper.xml"/>
    </mappers>

编写测试程序

    @Test
    public void testSelectUser()
    {
        String statement = "com.bupt.mybatis.beanMappers.userMapper.selectUser";
　　　　 SqlSession session = sessionFactory.openSession(true);　　　　
　　
　　　　　//模糊查询，查询出名字中间带有字母 o 的记录
        List<User> user = session.selectList(statement, new ConditionUser("%o%", 1, 14));
        System.out.println(user);
    }

执行 Junit 测试，结果如下

调用存储过程

需求：查询得到男性或女性的数量，如果传入的是0就女性否则男性

准备数据库表和存储过程

CREATE TABLE p_user(
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(10),
        sex CHAR(2)
);

INSERT INTO p_user(name, sex) VALUES('A', "man");
INSERT INTO p_user(name, sex) VALUES('B', "female");
INSERT INTO p_user(name, sex) VALUES('C', "man");

#创建存储过程
DELIMITER $
CREATE PROCEDURE test.ges_user_count(IN sex_id INT, OUT user_count INT)
BEGIN
IF sex_id=0 THEN
SELECT COUNT(*) FROM test.p_user WHERE p_user.sex="female" INTO user_count;
ELSE
SELECT COUNT(*) FROM test.p_user WHERE p_user.sex="man" INTO user_count;
END IF;
END
$

#调用存储过程q
SET @user_count=0;
CALL test.ges_user_count(1, @user_count);
SELECT @user_count;

当我们在 Mysql Workbench 中执行调用存储过程那部分代码时，因为调用时（CALL）传入的参数为1非0，所以去数据库查找男性记录统计相加，可以得到如下结果

这里我们来看一下如何使用 mybatis 来实现上述调用存储过程

新建配置文件 puserMapper.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.bupt.mybatis.beanMappers.puserMapper">

    <select id="getUserCount" parameterMap="getUserCountMap" statementType="CALLABLE">
        CALL test.ges_user_count(?,?);
    </select>

    <parameterMap type="java.util.Map" id="getUserCountMap">
        <parameter property="sexid" mode="IN" jdbcType="INTEGER"/>
        <parameter property="usercount" mode="OUT" jdbcType="INTEGER"/>
    </parameterMap>
</mapper>

测试方法

    @Test
    public void testCall()
    {
        String statement = "com.bupt.mybatis.beanMappers.puserMapper.getUserCount";
　　　　 SqlSession session = sessionFactory.openSession(true);  

        Map<String, Integer> parameterMap = new HashMap<String, Integer>();
        parameterMap.put("sexid", 1);
        parameterMap.put("usercount", -1);
        
        session.selectOne(statement, parameterMap);
        Integer result = parameterMap.get("usercount");
        
        System.out.println(result);
    }

需要注意的是：我所创建的 p_user 表，以及存储过程都是在名为 test 的数据库中进行的，我们需要根据自己的数据库名称来更改前缀名。