Mybatis多表查询多对一与一对多的简单实现

Leave a reply

1、数据库

1、tab_student

id name tid
1 张三 1
2 李四 1
3 王五 2
4 赵六 2

2、tab_teacher

id name
1 孙老师
2 秦老师

2.1、多对一数据查询

2.1.1、实体类

Student.java 

package com.yusian.domain;

import lombok.Data;

@Data
public class Student {
    private Integer id;
    private String name;
    private Teacher teacher;
}

Teacher.java 

package com.yusian.domain;

import lombok.Data;

@Data
public class Teacher {
    private Integer id;
    private String name;
}

2.1.2、Mapper

StudentMapper.java 

package com.yusian.dao;

import com.yusian.domain.Student;
import java.util.List;

public interface StudentMapper {
    /**
     * 获取所有学生列表
     * @return  结果集列表
     */
    List<Student> getStudentList();
}

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.yusian.dao.StudentMapper">

  <resultMap id="studentMap" type="com.yusian.domain.Student">
      <id property="id" column="id"/>
      <result property="name" column="name" />
      <association property="teacher" column="tid" select="getTeacherById"/>
  </resultMap>
  <select id="getStudentList" resultMap="studentMap">
      select * from tab_student
  </select>

  <select id="getTeacherById" resultType="com.yusian.domain.Teacher">
      select * from tab_teacher where id = #{id}
  </select>
</mapper>
  • 关联查询 
    <?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.yusian.dao.StudentMapper">
  <resultMap id="studentMap" type="com.yusian.domain.Student">
      <id property="id" column="id" />
      <result property="name" column="s_name"/>
      <association property="teacher">
          <id property="id" column="tid"/>
          <result property="name" column="t_name"/>
      </association>
  </resultMap>
  <select id="getStudentList" resultMap="studentMap">
      select s.id as id, s.name as s_name, s.tid, t.name as t_name from tab_student s, tab_teacher t where s.tid = t.id
  </select>
</mapper>

2.1.3、测试代码

package com.yusian.test;

import com.yusian.dao.StudentMapper;
import com.yusian.domain.Student;
import com.yusian.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class StudentMapperTest {

    @Test
    public void getStudentListTest() {
        try(SqlSession sqlSession = MybatisUtils.getSqlSession()) {
            StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
            List<Student> students = mapper.getStudentList();
            System.out.println(students);
        }
    }
}

2.1.4、执行结果

  • 子查询 
    Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1991313236.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@76b10754]
==>  Preparing: select * from tab_student
==> Parameters: 
<==    Columns: id, name, tid
<==        Row: 1, 张三, 1
====>  Preparing: select * from tab_teacher where id = ?
====> Parameters: 1(Integer)
<====    Columns: id, name
<====        Row: 1, 孔老师
<====      Total: 1
<==        Row: 2, 李四, 1
<==        Row: 3, 王五, 2
====>  Preparing: select * from tab_teacher where id = ?
====> Parameters: 2(Integer)
<====    Columns: id, name
<====        Row: 2, 秦老师
<====      Total: 1
<==        Row: 4, 赵六, 2
<==      Total: 4
[Student(id=1, name=张三, teacher=Teacher(id=1, name=孔老师)), Student(id=2, name=李四, teacher=Teacher(id=1, name=孔老师)), Student(id=3, name=王五, teacher=Teacher(id=2, name=秦老师)), Student(id=4, name=赵六, teacher=Teacher(id=2, name=秦老师))]
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@76b10754]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@76b10754]
Returned connection 1991313236 to pool.

Process finished with exit code 0
  • 关联查询 
    Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1991313236.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@76b10754]
==>  Preparing: select s.id as id, s.name as s_name, s.tid, t.name as t_name from tab_student s, tab_teacher t where s.tid = t.id
==> Parameters: 
<==    Columns: id, s_name, tid, t_name
<==        Row: 1, 张三, 1, 孔老师
<==        Row: 2, 李四, 1, 孔老师
<==        Row: 3, 王五, 2, 秦老师
<==        Row: 4, 赵六, 2, 秦老师
<==      Total: 4
[Student(id=1, name=张三, teacher=Teacher(id=1, name=孔老师)), Student(id=2, name=李四, teacher=Teacher(id=1, name=孔老师)), Student(id=3, name=王五, teacher=Teacher(id=2, name=秦老师)), Student(id=4, name=赵六, teacher=Teacher(id=2, name=秦老师))]
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@76b10754]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@76b10754]
Returned connection 1991313236 to pool.

Process finished with exit code 0

2.2、一对多查询

2.2.1、实体类

Student.java 

package com.yusian.domain;

import lombok.Data;

@Data
public class Student {
    private Integer id;
    private String name;
}

Teacher.java 

package com.yusian.domain;

import lombok.Data;

import java.util.List;

@Data
public class Teacher {
    private Integer id;
    private String name;
    List<Student> students;
}

2.2.2、Mapper

TeacherMapper.java 

package com.yusian.dao;

import com.yusian.domain.Teacher;

import java.util.List;

public interface TeacherMapper {
    /**
     * 获取所有教师列表
     * @return
     */
    List<Teacher> getTeacherList();
}

TeacherMapper.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.yusian.dao.TeacherMapper">
<!--    子查询-->
  <resultMap id="teacherMap" type="com.yusian.domain.Teacher">
      <id property="id" column="id"/>
      <result property="name" column="name"/>
      <collection property="students" column="id" ofType="com.yusian.domain.Student" select="getStudentList">
          <id property="id" column="id"/>
          <result property="name" column="name"/>
      </collection>
  </resultMap>

  <select id="getTeacherList" resultMap="teacherMap">
      select * from tab_teacher
  </select>

  <select id="getStudentList" resultType="com.yusian.domain.Student">
      select * from tab_student where tid = #{tid}
  </select>
</mapper>
  • 关联查询 
    <?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.yusian.dao.TeacherMapper">
<!--    关联查询-->
  <resultMap id="teacherMap" type="com.yusian.domain.Teacher">
      <id property="id" column="t_id"/>
      <result property="name" column="t_name"/>
      <collection property="students" ofType="com.yusian.domain.Student">
          <id property="id" column="s_id"/>
          <result property="name" column="s_name"/>
      </collection>
  </resultMap>
  <select id="getTeacherList" resultMap="teacherMap">
      select s.id as s_id, s.name as s_name, s.tid as s_tid, t.id as t_id, t.name as t_name
      from tab_student s, tab_teacher t
      where s.tid = t.id
  </select>
</mapper>

2.2.3、测试代码

package com.yusian.test;

import com.yusian.dao.TeacherMapper;
import com.yusian.domain.Teacher;
import com.yusian.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class TeacherMapperTest {
    @Test
    public void getTeacherListTest() {
        try (SqlSession sqlSession = MybatisUtils.getSqlSession()) {
            TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
            List<Teacher> teachers = mapper.getTeacherList();
            System.out.println(teachers);
        }
    }
}

2.2.4、执行结果

  • 子查询 
    Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1991313236.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@76b10754]
==>  Preparing: select * from tab_teacher
==> Parameters: 
<==    Columns: id, name
<==        Row: 1, 孔老师
====>  Preparing: select * from tab_student where tid = ?
====> Parameters: 1(Integer)
<====    Columns: id, name, tid
<====        Row: 1, 张三, 1
<====        Row: 2, 李四, 1
<====      Total: 2
<==        Row: 2, 秦老师
====>  Preparing: select * from tab_student where tid = ?
====> Parameters: 2(Integer)
<====    Columns: id, name, tid
<====        Row: 3, 王五, 2
<====        Row: 4, 赵六, 2
<====      Total: 2
<==      Total: 2
[Teacher(id=1, name=孔老师, students=[Student(id=1, name=张三), Student(id=2, name=李四)]), Teacher(id=2, name=秦老师, students=[Student(id=3, name=王五), Student(id=4, name=赵六)])]
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@76b10754]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@76b10754]
Returned connection 1991313236 to pool.

Process finished with exit code 0
  • 关联查询 
    Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1991313236.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@76b10754]
==>  Preparing: select s.id as s_id, s.name as s_name, s.tid as s_tid, t.id as t_id, t.name as t_name from tab_student s, tab_teacher t where s.tid = t.id
==> Parameters: 
<==    Columns: s_id, s_name, s_tid, t_id, t_name
<==        Row: 1, 张三, 1, 1, 孔老师
<==        Row: 2, 李四, 1, 1, 孔老师
<==        Row: 3, 王五, 2, 2, 秦老师
<==        Row: 4, 赵六, 2, 2, 秦老师
<==      Total: 4
[Teacher(id=1, name=孔老师, students=[Student(id=1, name=张三), Student(id=2, name=李四)]), Teacher(id=2, name=秦老师, students=[Student(id=3, name=王五), Student(id=4, name=赵六)])]
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@76b10754]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@76b10754]
Returned connection 1991313236 to pool.

Process finished with exit code 0

Leave a Reply