mybatis-04多表查询

mybatis查询

针对一对一、多对多查询实现

一对一

一个account表对应一个user用户
一个user用户可能存在多个account账户信息

对账户account表来说，一对一的话，类Account中就需要存在一个user信息。通过getUser()/setUser(User user)来实现对user的获取/赋值

package com.example.domain;

public class Account {

    public int id;

    public String accountName;

    public double money;

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    User user;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getAccountName() {
        return accountName;
    }

    public void setAccountName(String accountName) {
        this.accountName = accountName;
    }

    public double getMoney() {
        return money;
    }

    public void setMoney(double money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", accountName='" + accountName + '\'' +
                ", money=" + money +
                ", user=" + user +
                '}';
    }
}

那么对user类来说，account应该是一个集合 List<Account> accounts;

package com.example.domain;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

/*

*create table User(
id INT PRIMARY KEY AUTO_INCREMENT,
userName VARCHAR(100),
birthDay DATE,
sex VARCHAR(1),
address varchar(512))




delete from user

SELECT * FROM USER

insert into user(userName,birthDay,sex,address)values('张三','1990-02-01','男','北京')
insert into user(userName,birthDay,sex,address)values('李四','1990-02-01','男','北京');
insert into user(userName,birthDay,sex,address)values('王五','1990-02-01','男','北京');
*  开发流程
* 1、搭建工程
* 2、创建user 和IUser
* 3、创建主配置文件
* 4、创建配置映射文件
*
* 环境搭建的注意事项：
* 1、创建IUserDao.xml和IUserDao.java时是为了和我们之前的知识保持一致
* 2、idea创建目录和创建包是不一样的
* 3、IUserDao.xml映射配置文件的目录必须与java对应起来
* 4、映射配置文件的mapper标签 namespce必须是全限定名
* 5、映射配置文件的mapper标签里的Id必须是IUserDao中的方法名，应该是mybatis根据id来查找。
* */
public class User implements Serializable {
    private Integer userId;
    private String userName;
    private Date userBirthDay;
    private String userSex;
    private String userAddress;

    List<Account> accounts;

    public List<Account> getAccounts() {
        return accounts;
    }

    public void setAccounts(List<Account> accounts) {
        this.accounts = accounts;
    }

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public Date getUserBirthDay() {
        return userBirthDay;
    }

    public void setUserBirthDay(Date userBirthDay) {
        this.userBirthDay = userBirthDay;
    }

    public String getUserSex() {
        return userSex;
    }

    public void setUserSex(String userSex) {
        this.userSex = userSex;
    }

    public String getUserAddress() {
        return userAddress;
    }

    public void setUserAddress(String userAddress) {
        this.userAddress = userAddress;
    }

    @Override
    public String toString() {
        return "User{" +
                "userId=" + userId +
                ", userName='" + userName + '\'' +
                ", userBirthDay=" + userBirthDay +
                ", userSex='" + userSex + '\'' +
                ", userAddress='" + userAddress + '\'' +
                ", accounts=" + accounts +
                '}';
    }
}

访问账户中关联人信息

设置IUserDao.xml

<resultMap id="useraccount" type="user">
    <id column="id" property="userId"></id>
    <result column="name" property="userName"></result>
    <result column="sex" property="userSex"></result>
    <result column="address" property="userAddress"></result>
    <result column="birthday" property="userBirthDay"></result>
    <collection property="accounts" ofType="account">
        <id column="aid" property="id"></id>
        <result column="accountname" property="accountName"></result>
        <result column="money" property="money"></result>
    </collection>
</resultMap>
    <select id="findUserAccountInfo" resultMap="useraccount">
        select user.*,account.id aid,account.accountname,account.money from user left outer  join account on account.uid=user.id;
    </select>

设置IAcountDao.xml

<mapper namespace="com.example.dao.IAccountDao">

    <resultMap id="accountuser" type="account">
        <id property="id" column="aid"></id>
        <result property="accountName" column="accountname"></result>
        <result property="money" column="money"></result>
        <association property="user" column="id" javaType="user">
            <id column="id" property="userId"></id>
            <result property="userName" column="username"></result>
            <result column="sex" property="userSex"></result>
            <result column="address" property="userAddress"></result>
            <result column="birthday" property="userBirthDay"></result>
        </association>
    </resultMap>
    <select id="findAll" resultMap="accountuser">
        select user.*,account.id aid,account.accountname,account.money from user,account where user.id=account.uid;
    </select>
</mapper>

这两个的区别一个是collection 一个是association

测试

    /*
    * 查找所有账户信息
    * */
    @Test
    public void TestFindAll(){
        List<Account> accounts=accountDao.findAll();
        for(Account a : accounts){
            System.out.println(a);
        }
    }

可以看出能够关联出账户人的信息

Account{id=1, accountName='中国银行', money=1000.0, user=User{userId=1, userName='张三', userBirthDay=Thu Feb 01 00:00:00 CST 1990, userSex='男', userAddress='北京', accounts=null}}
Account{id=2, accountName='工商银行', money=1000.0, user=User{userId=2, userName='李四', userBirthDay=Thu Feb 01 00:00:00 CST 1990, userSex='男', userAddress='北京', accounts=null}}
Account{id=3, accountName='建设银行', money=1000.0, user=User{userId=3, userName='王五', userBirthDay=Thu Feb 01 00:00:00 CST 1990, userSex='男', userAddress='北京', accounts=null}}
Account{id=4, accountName='中信银行', money=2000.0, user=User{userId=2, userName='李四', userBirthDay=Thu Feb 01 00:00:00 CST 1990, userSex='男', userAddress='北京', accounts=null}}

测试用户的所有账户信息

    @Test
    public void TestUserAccount(){
        List<User> users=userDao.findUserAccountInfo();
        for(User u:users){
            System.out.println(u);
        }
    }

输出

User{userId=1, userName='null', userBirthDay=Thu Feb 01 00:00:00 CST 1990, userSex='男', userAddress='北京', accounts=[Account{id=1, accountName='中国银行', money=1000.0, user=null}]}
User{userId=2, userName='null', userBirthDay=Thu Feb 01 00:00:00 CST 1990, userSex='男', userAddress='北京', accounts=[Account{id=2, accountName='工商银行', money=1000.0, user=null}, Account{id=4, accountName='中信银行', money=2000.0, user=null}]}
User{userId=3, userName='null', userBirthDay=Thu Feb 01 00:00:00 CST 1990, userSex='男', userAddress='北京', accounts=[Account{id=3, accountName='建设银行', money=1000.0, user=null}]}
User{userId=5, userName='null', userBirthDay=Thu Apr 23 00:00:00 CST 2020, userSex='?', userAddress='???', accounts=[]}
User{userId=6, userName='null', userBirthDay=Thu Apr 23 00:00:00 CST 2020, userSex='男', userAddress='济南市历下区', accounts=[]}
User{userId=7, userName='null', userBirthDay=Thu Apr 23 00:00:00 CST 2020, userSex='男', userAddress='济南市历221下区', accounts=[]}

这里用户2的账户信息都归集到一起了

测试多对多

一个学生可以选择多节课，一节课可以被多个学生选择。所以这两个是多对多的关系。
创建实体类student/course
Student类

package com.example.domain;

import java.util.List;

/*
*
				create table student(id INTEGER PRIMARY KEY AUTO_INCREMENT,
				studentcode varchar(100),
				studentname varchar(200))
				*
				insert into student(studentcode,studentname)values('01','小张');
				insert into student(studentcode,studentname)values('02','小王');
				insert into student(studentcode,studentname)values('03','小李');
				insert into student(studentcode,studentname)values('04','小于');
				insert into student(studentcode,studentname)values('05','小曹');
				insert into student(studentcode,studentname)values('06','小白');

* */
public class Student {

    private int id;
    private String studentCode;
    private String studentName;
    private List<Course> coursesList;


    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getStudentCode() {
        return studentCode;
    }

    public void setStudentCode(String studentCode) {
        this.studentCode = studentCode;
    }

    public String getStudentName() {
        return studentName;
    }

    public void setStudentName(String studentName) {
        this.studentName = studentName;
    }

    public List<Course> getCoursesList() {
        return coursesList;
    }

    public void setCoursesList(List<Course> coursesList) {
        this.coursesList = coursesList;
    }

    @Override
    public String toString() {
        return "Students{" +
                "id=" + id +
                ", studentCode='" + studentCode + '\'' +
                ", studentName='" + studentName + '\'' +
                ", coursesList=" + coursesList +
                '}';
    }
}

Course类

package com.example.domain;

import java.util.List;

/*
*
				create table course(id INTEGER PRIMARY key AUTO_INCREMENT,
														coursename varchar(2))
														*
														*
				insert into course(coursename)values('语文')


				insert into course(coursename)values('数学')


				insert into course(coursename)values('英语')

*
*
* 中间表信息
* 				create table cour_stu(id INTEGER PRIMARY KEY AUTO_INCREMENT,
				courseid int,
				studentid int)

*
				insert into cour_stu(courseid,studentid)values(1,1);
				insert into cour_stu(courseid,studentid)values(1,2);
				insert into cour_stu(courseid,studentid)values(1,3);
				insert into cour_stu(courseid,studentid)values(2,4);
				insert into cour_stu(courseid,studentid)values(2,1);
				insert into cour_stu(courseid,studentid)values(3,1);
				insert into cour_stu(courseid,studentid)values(3,2);

				insert into cour_stu(courseid,studentid)values(3,3);
				insert into cour_stu(courseid,studentid)values(3,4);

				insert into cour_stu(courseid,studentid)values(3,5);
				insert into cour_stu(courseid,studentid)values(3,6);

* */
public class Course {
    private int id;
    private String courseName;

    private List<Student> students;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getCourseName() {
        return courseName;
    }

    public void setCourseName(String courseName) {
        this.courseName = courseName;
    }

    public List<Student> getStudents() {
        return students;
    }

    public void setStudents(List<Student> students) {
        this.students = students;
    }

    @Override
    public String toString() {
        return "Course{" +
                "id=" + id +
                ", courseName='" + courseName + '\'' +
                ", students=" + students +
                '}';
    }
}

两个表的关联关系通过cour_stu来处理

增加mapper结果映射

IStudentDao.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.example.dao.IStudentDao">
    <resultMap id="studentcourse" type="student">
        <id property="id" column="id"></id>
        <result column="studentcode" property="studentCode"></result>
        <result column="studentname" property="studentName"></result>
        <collection property="coursesList" ofType="course">
            <id property="id" column="cid"></id>
            <result column="coursename" property="courseName"></result>
        </collection>
    </resultMap>
    <select id="findAll" resultMap="studentcourse">
        select a.*,c.id cid,c.coursename from student a left outer join cour_stu b on a.id=b.studentid left outer join course c on c.id=b.courseid;
    </select>
</mapper>

ICourseDao.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.example.dao.ICourseDao">
    <resultMap id="coursestudent" type="course">
        <id property="id" column="id"></id>
        <result column="coursename" property="courseName"></result>
        <collection property="students" ofType="student">
            <id property="id" column="sid"></id>
            <result column="studentcode" property="studentCode"></result>
            <result column="studentname" property="studentName"></result>
        </collection>
    </resultMap>
    <select id="findAll" resultMap="coursestudent">
select  c.*,a.id sid,a.studentcode,a.studentname from course c left outer join cour_stu b on c.id=b.courseid left outer join student a on a.id=b.studentid;
    </select>
</mapper>

测试

测试学生

package com.example.test;
import com.example.dao.IStudentDao;
import com.example.domain.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

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

public class StudentTest {

    private InputStream in;
    private SqlSession session;
    private IStudentDao studentDao;

    @Before
    public void Init() throws Exception{
        in= Resources.getResourceAsStream("SqlMapConfigure.xml");
        //创建sqlSessionFactory工厂
        SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory= builder.build(in);
        //使用工厂创建一个SqlSession
        session=sqlSessionFactory.openSession(true);
        //使用sqlSession创建dao代理对象（动态代理）
        studentDao=session.getMapper(IStudentDao.class);
    }

    @After
    public void destory() throws Exception{
        session.close();
        in.close();
    }

    @Test
    public void TestStudentCoure(){
        List<Student> students=studentDao.findAll();
        for(Student student: students){
            System.out.println(student);
        }
    }
}

输出

Students{id=1, studentCode='01', studentName='小张', coursesList=[Course{id=1, courseName='语文', students=null}, Course{id=2, courseName='英语', students=null}, Course{id=3, courseName='数学', students=null}]}
Students{id=2, studentCode='02', studentName='小王', coursesList=[Course{id=1, courseName='语文', students=null}, Course{id=3, courseName='数学', students=null}]}
Students{id=3, studentCode='03', studentName='小李', coursesList=[Course{id=1, courseName='语文', students=null}, Course{id=3, courseName='数学', students=null}]}
Students{id=4, studentCode='04', studentName='小于', coursesList=[Course{id=2, courseName='英语', students=null}, Course{id=3, courseName='数学', students=null}]}
Students{id=5, studentCode='05', studentName='小曹', coursesList=[Course{id=3, courseName='数学', students=null}]}
Students{id=6, studentCode='06', studentName='小白', coursesList=[Course{id=3, courseName='数学', students=null}]}

测试课程

package com.example.test;

import com.example.dao.ICourseDao;
import com.example.domain.Course;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import javax.swing.plaf.IconUIResource;
import java.io.InputStream;
import java.util.List;

public class CourseTest {

    private InputStream in;
    private SqlSession session;
    private ICourseDao courseDao;

    @Before
    public void Init() throws Exception{
        in= Resources.getResourceAsStream("SqlMapConfigure.xml");
        //创建sqlSessionFactory工厂
        SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory= builder.build(in);
        //使用工厂创建一个SqlSession
        session=sqlSessionFactory.openSession(true);
        //使用sqlSession创建dao代理对象（动态代理）
        courseDao=session.getMapper(ICourseDao.class);
    }

    @After
    public void destory() throws Exception{
        session.close();
        in.close();
    }

    @Test
    public void TestCourseAll(){
        List<Course> courses= courseDao.findAll();
       for(Course course:courses ){
           System.out.println(course);
       }
    }
}

输出

Course{id=1, courseName='语文', students=[Students{id=1, studentCode='01', studentName='小张', coursesList=null}, Students{id=2, studentCode='02', studentName='小王', coursesList=null}, Students{id=3, studentCode='03', studentName='小李', coursesList=null}]}
Course{id=2, courseName='英语', students=[Students{id=4, studentCode='04', studentName='小于', coursesList=null}, Students{id=1, studentCode='01', studentName='小张', coursesList=null}]}
Course{id=3, courseName='数学', students=[Students{id=1, studentCode='01', studentName='小张', coursesList=null}, Students{id=2, studentCode='02', studentName='小王', coursesList=null}, Students{id=3, studentCode='03', studentName='小李', coursesList=null}, Students{id=4, studentCode='04', studentName='小于', coursesList=null}, Students{id=5, studentCode='05', studentName='小曹', coursesList=null}, Students{id=6, studentCode='06', studentName='小白', coursesList=null}]}

能够看出多对多都可以完全实现。

总结

Mybatis实现多表查询还是比较方便的，主要还是考验写sql的能力，但是对比netcore的EFCore我感觉还是EFCore更方便，不要写这么多sql和配置信息。改天写个对比实践。