mybatis子查询

<mapper ******>
<select id="getListByTeacherId2" resultMap="StudentTeacher2">
    select *  from mybatis.student where tid =#{queryid}
</select>
<resultMap id="StudentTeacher2" type="Student">
    <result property="id" column="id"/>
    <result property="name" column="name"/>
    <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
    select id, name from mybatis.teacher where id = #{tid}
</select>
</mapper>

mybatis子查询 首先呢我们一定会有两个查询语句,两个表连接起来查询所以我们的pojo实体类 中主类中必须有包含子类的字段属性

比如我有Student 类和 teacher 类

Student:

package com.zjh.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data //这是除了构造参数创建了set get 覆盖了tostring等方法
@NoArgsConstructor//无参构造
@AllArgsConstructor//有参构造使用了lombok注解开发
public class Student {
    private int id;
    private String name;
    private Teacher teacher;

}

teacher类:

package com.zjh.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
    private int id;
    private String name;
}

我要连接来查询他们:

所以先在mapper.xml写一个查询语句因为没有StudentTeacher联合类所以要用映射类型resultMap

<select id="getListByTeacherId2" resultMap="StudentTeacher2">

select  *  from mybatis.student where tid =#{queryid}//这个#{里面是你要创建对象后你通过方法传进来的参数}//mybatis是我的数据库,student与teacher是数据库的对应表 tid,student里的id ,,student的name是student表里的字段teacher表里只有id 和name当然都是teacher的

</select>

然后映射结果设置

呐你们看resultMap="StudentTeacher2"设置在这里了,然后通过设置成功后返回结果类型是student类的,因为他是查询主表

<resultMap id="StudentTeacher2" type="Student">

<result property="id" column="id"/>//property="id" 当然这就是类中的属性column="id"数据库对应的字段列

<result property="name" column="name"/>//这边同理

//association是对象因为我们的student类中就是有teacher这个属性他也是Teacher类的么,就是可以赋值对象,他就是对象,对象就要用这个association,property="teacher"对应student类中的属性,javaType="Teacher"是数据库将子查询的数据包装成teacher类映射到student查询的属性里

<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>

//关键在这里因为 column="tid"是student的列字段,student类中并没有此属性所以他是子查询的关键,他这个列的数据,你可以理解为他就是子查询的参数去查询teacher数据库表

select="getTeacher"就是要填写的子查询的方法名对应下面的语句#{tid}就是tid字段的值会自动赋值

<select id="getTeacher" resultType="Teacher">

select id, name from mybatis.teacher where id = #{tid}

</select>

</resultMap>

@Test
public void test02(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
    List<Student> listByTeacherId = studentMapper.getListByTeacherId2(1);
    for ( Student c:listByTeacherId)
    {
        System.out.println(c);
    }
    sqlSession.close();
}

[org.apache.ibatis.datasource.pooled.PooledDataSource]-Created connection 87674905.
[org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@539d019]
[com.zjh.dao.StudentMapper.getListByTeacherId2]-==>  Preparing: select * from mybatis.student where tid =? 
[com.zjh.dao.StudentMapper.getListByTeacherId2]-==> Parameters: 1(Integer)
[com.zjh.dao.StudentMapper.getTeacher]-====>  Preparing: select id, name from mybatis.teacher where id = ? 
[com.zjh.dao.StudentMapper.getTeacher]-====> Parameters: 1(Integer)
[com.zjh.dao.StudentMapper.getTeacher]-<====      Total: 1
[com.zjh.dao.StudentMapper.getListByTeacherId2]-<==      Total: 4
Student(id=2, name=小红, teacher=Teacher(id=1, name=秦老师))
Student(id=3, name=小张, teacher=Teacher(id=1, name=秦老师))
Student(id=4, name=小李, teacher=Teacher(id=1, name=秦老师))
Student(id=5, name=小王, teacher=Teacher(id=1, name=秦老师))
[org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@539d019]
[org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@539d019]
[org.apache.ibatis.datasource.pooled.PooledDataSource]-Returned connection 87674905 to pool.