Mybatis查询

Mybatis中关联查询之子查询

Mybatis中通过关联查询，基础的有两种方式，第一种是直接根据条件查询，第二种是在第一个查询下进行子查询。

第一种方式:嵌套结果

<!-- 多表查询 嵌套结果的方式处理 -->
    <select id="getClasses" parameterType="int" resultMap="Classes">
        select *
        from class c,teacher t where c.teacher_id=t.t_id and c.c_id=#{id}
    </select>
    <!-- 下面是对结果集在字段和数据库中对应的指定形式，可以去除重复的结果 -->
    <resultMap type="com.mybatis.bean.Classes" id="Classes">
        <id property="id" column="c_id" />
        <result property="name" column="c_name" />
        <association property="teacher" column="teacher_id"
            javaType="com.mybatis.bean.Teacher">
            <id property="id" column="t_id" />
            <result property="name" column="t_name" />
        </association>
    </resultMap>

通过这种方式查询实际上就是简单的条件查询，下面是测试方法和测试结果：

```
@Test
    public void testClasses(){
        SqlSession session = MybatisUtils.getSession();
        String statement = "com.mybatis.bean.orderMapper.getClasses";
        Classes classes = session.selectOne(statement,2);
        System.out.println(classes);
        session.close();
    }

2016-12-10 19:11:41,626 [main] DEBUG [com.mybatis.bean.orderMapper.getClasses] - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@49393f9]
2016-12-10 19:11:41,627 [main] DEBUG [com.mybatis.bean.orderMapper.getClasses] - ==>  Preparing: select * from class c,teacher t where c.teacher_id=t.t_id and c.c_id=? 
2016-12-10 19:11:41,721 [main] DEBUG [com.mybatis.bean.orderMapper.getClasses] - ==> Parameters: 2(Integer)
Classes [id=2, name=bj_b, teacher=Teacher [id=2, name=LS2]]

可以看出能够得到正确的结果

第二种方式：嵌套查询

    <!-- 第二种查询方式:子查询 下面查询方式没有错，但是运行可能出错，这里只是一段查询方式示例代码，读者只需要知道这种方式十二中查询即可，重点看后面多表连接查询-->
<select id="getClass" parameterType="int" resultMap="getClasses">
        select c_id id,c_name name,teacher_id t_id from class where c_id=#{id}
    </select>
    <resultMap type="com.mybatis.bean.Classes" id="getClasses">
        <id property="id" column="c_id"/>
        <result property="name" column="c_name"/>
        <association property="teacher" column="teacher_id" javaType="com.mybatis.bean.Teacher" select="getTeacher">
        </association>
    </resultMap>
    <select id="getTeacher" parameterType="int" resultType="com.mybatis.bean.Teacher" >
        select t_id id,t_name name from teacher where t_id=#{id}
    </select>

通过这种方式运行可能会报下面类似的错误

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.lang.IllegalArgumentException: Mapped Statements collection does not contain value for com.mybatis.test.orderMapper.getClass4
### Cause: java.lang.IllegalArgumentException: Mapped Statements collection does not contain value for com.mybatis.test.orderMapper.getClass4
    at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:95)
    at com.mybatis.test.Test6.test(Test6.java:17)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
Caused by: java.lang.IllegalArgumentException: Mapped Statements collection does not contain value for com.mybatis.test.orderMapper.getClass4
    at org.apache.ibatis.session.Configuration$StrictMap.get(Configuration.java:672)
    at org.apache.ibatis.session.Configuration.getMappedStatement(Configuration.java:507)
    at org.apache.ibatis.session.Configuration.getMappedStatement(Configuration.java:500)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:100)
    ... 25 more

---

**上面之所以会报错，首先做以下检查：
1、Xxxmapper.xml中你写的namespace可能不正确 或者 Xxxmapper.xml文件名和所写的mapper名称不相同(一般是这种错误)
2、Xxxmapper.xml没有在你写的配置文件中注册(当你是一个一个Mapper注册而不是扫描包)**

---

下面给出一个查询三个表的一个子查询例子(当然也可以通过方式一实现，而且更加简单，只有一条sql语句，此处不再赘述，懂原理的朋友相信能够举一反三，不懂得可以交流探讨^_^)：

<?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.mybatis.test.test6Mapper">

    <select id="getClass4" parameterType="int" resultMap="ClassResultMap4">
        select *
        from class where c_id=#{id}
    </select>
    <resultMap type="com.mybatis.bean.Classes" id="ClassResultMap4">
        <id property="id" column="c_id" />
        <result property="name" column="c_name" />
        <association property="teacher" column="teacher_id"
            javaType="com.mybatis.bean.Teacher" select="getTeacher2"></association>
        <collection property="students" ofType="com.mybatis.bean.Student"
            column="c_id" select="getStudent"></collection>
    </resultMap>
    <select id="getTeacher2" parameterType="int"
        resultType="com.mybatis.bean.Teacher">
        SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
    </select>
    <select id="getStudent" parameterType="int" resultType="com.mybatis.bean.Student">
        SELECT
        s_id id, s_name name FROM student WHERE class_id=#{id}
    </select>

</mapper>

下面是测试及结果

package com.mybatis.test;

import java.util.List;

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

import com.mybatis.bean.Classes;
import com.mybatis.utils.MybatisUtils;

public class Test6 {

    @Test
    public void test() {
        SqlSession session = MybatisUtils.getSession();
        String statement = "com.mybatis.test.test6Mapper.getClass4";
        List<Classes> list = session.selectList(statement, 1);
        for (Object arg : list) {
            System.out.println(arg);
        }
    }

}

2016-12-11 13:11:04,385 [main] DEBUG [com.mybatis.test.test6Mapper.getClass4] - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@2eae85ab]
2016-12-11 13:11:04,386 [main] DEBUG [com.mybatis.test.test6Mapper.getClass4] - ==>  Preparing: select * from class where c_id=? 
2016-12-11 13:11:04,471 [main] DEBUG [com.mybatis.test.test6Mapper.getClass4] - ==> Parameters: 1(Integer)
2016-12-11 13:11:04,512 [main] DEBUG [com.mybatis.test.test6Mapper.getTeacher2] - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@2eae85ab]
2016-12-11 13:11:04,513 [main] DEBUG [com.mybatis.test.test6Mapper.getTeacher2] - ==>  Preparing: SELECT t_id id, t_name name FROM teacher WHERE t_id=? 
2016-12-11 13:11:04,513 [main] DEBUG [com.mybatis.test.test6Mapper.getTeacher2] - ==> Parameters: 1(Integer)
2016-12-11 13:11:04,515 [main] DEBUG [com.mybatis.test.test6Mapper.getStudent] - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@2eae85ab]
2016-12-11 13:11:04,515 [main] DEBUG [com.mybatis.test.test6Mapper.getStudent] - ==>  Preparing: SELECT s_id id, s_name name FROM student WHERE class_id=? 
2016-12-11 13:11:04,516 [main] DEBUG [com.mybatis.test.test6Mapper.getStudent] - ==> Parameters: 1(Integer)
Classes [id=1, name=bj_a, teacher=Teacher [id=1, name=LS1], students=[Student [id=1, name=xs_A], Student [id=2, name=xs_B], Student [id=3, name=xs_C]]]