Mybatis复杂查询(一对一,一对多)

一对一(一个学生对应一个老师),一对多(一个学生对应多个课程)

1.实体类
学生实体 getter、setter方法,tostring方法,有参、无参构造方法

package com.test.entity;

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

/**
 * @className: com.test.entity.Student
 * @description: TODO
 * @author: Administrator
 * @create: 2021-04-08 9:14
 */
public class Student implements Serializable {
    private int stuId;//学生编号
    private String stuName;//姓名
    private int stuAge;//年龄
    private String stuGender;//性别
    private int teaId;//教师编号
    private Date stuCreateDate;//日期
    private Teacher teacher;//教师属性
    private List<Subject> subjects;//课程属性

    public Student() {
    }

    public Student(String stuName, int stuAge, String stuGender, int teaId, Date stuCreateDate) {
        this.stuName = stuName;
        this.stuAge = stuAge;
        this.stuGender = stuGender;
        this.teaId = teaId;
        this.stuCreateDate = stuCreateDate;
    }

    public Student(int stuId, String stuName, int stuAge, String stuGender, int teaId, Date stuCreateDate) {
        this.stuId = stuId;
        this.stuName = stuName;
        this.stuAge = stuAge;
        this.stuGender = stuGender;
        this.teaId = teaId;
        this.stuCreateDate = stuCreateDate;
    }

    public Student(int stuId, String stuName, int stuAge, String stuGender, int teaId, Date stuCreateDate, Teacher teacher) {
        this.stuId = stuId;
        this.stuName = stuName;
        this.stuAge = stuAge;
        this.stuGender = stuGender;
        this.teaId = teaId;
        this.stuCreateDate = stuCreateDate;
        this.teacher = teacher;
    }

    public Student(int stuId, String stuName, int stuAge, String stuGender, int teaId, Date stuCreateDate, Subject subject) {
        this.stuId = stuId;
        this.stuName = stuName;
        this.stuAge = stuAge;
        this.stuGender = stuGender;
        this.teaId = teaId;
        this.stuCreateDate = stuCreateDate;
        this.subjects = subjects;
    }

    public Student(int stuId, String stuName, int stuAge, String stuGender, int teaId, Date stuCreateDate, Teacher teacher, Subject subject) {
        this.stuId = stuId;
        this.stuName = stuName;
        this.stuAge = stuAge;
        this.stuGender = stuGender;
        this.teaId = teaId;
        this.stuCreateDate = stuCreateDate;
        this.teacher = teacher;
        this.subjects = subjects;
    }

    public int getStuId() {
        return stuId;
    }

    public void setStuId(int stuId) {
        this.stuId = stuId;
    }

    public String getStuName() {
        return stuName;
    }

    public void setStuName(String stuName) {
        this.stuName = stuName;
    }

    public int getStuAge() {
        return stuAge;
    }

    public void setStuAge(int stuAge) {
        this.stuAge = stuAge;
    }

    public String getStuGender() {
        return stuGender;
    }

    public void setStuGender(String stuGender) {
        this.stuGender = stuGender;
    }

    public int getTeaId() {
        return teaId;
    }

    public void setTeaId(int teaId) {
        this.teaId = teaId;
    }

    public Date getStuCreateDate() {
        return stuCreateDate;
    }

    public void setStuCreateDate(Date stuCreateDate) {
        this.stuCreateDate = stuCreateDate;
    }

    public Teacher getTeacher() {
        return teacher;
    }

    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }

    public List<Subject> getSubjects() {
        return subjects;
    }

    public void setSubjects(List<Subject> subjects) {
        this.subjects = subjects;
    }

    @Override
    public String toString() {
        return "Student{" +
                "stuId=" + stuId +
                ", stuName='" + stuName + '\'' +
                ", stuAge=" + stuAge +
                ", stuGender='" + stuGender + '\'' +
                ", teaId=" + teaId +
                ", stuCreateDate=" + stuCreateDate +
                ", teacher=" + teacher +
                ", subjects=" + subjects +
                '}';
    }
}

教师实体 getter、setter方法,tostring方法,有参、无参构造方法

package com.test.entity;

import java.io.Serializable;

/**
 * @className: com.test.entity.Teacher
 * @description: TODO
 * @author: Administrator
 * @create: 2021-04-08 9:18
 */
public class Teacher implements Serializable {
    private int tId;//教师编号
    private String tName;//姓名
    private String tProfession;//专业

    public Teacher() {
    }

    public Teacher(String tName, String tProfession) {
        this.tName = tName;
        this.tProfession = tProfession;
    }

    public Teacher(int tId, String tName, String tProfession) {
        this.tId = tId;
        this.tName = tName;
        this.tProfession = tProfession;
    }

    public int gettId() {
        return tId;
    }

    public void settId(int tId) {
        this.tId = tId;
    }

    public String gettName() {
        return tName;
    }

    public void settName(String tName) {
        this.tName = tName;
    }

    public String gettProfession() {
        return tProfession;
    }

    public void settProfession(String tProfession) {
        this.tProfession = tProfession;
    }

    @Override
    public String toString() {
        return "Teacher{" +
                "tId=" + tId +
                ", tName='" + tName + '\'' +
                ", tProfession='" + tProfession + '\'' +
                '}';
    }
}

课程实体 getter、setter方法,tostring方法,有参、无参构造方法

package com.test.entity;

import java.io.Serializable;

/**
 * @className: com.test.entity.Subject
 * @description: TODO
 * @author: Administrator
 * @create: 2021-04-08 9:22
 */
public class Subject implements Serializable {
    private int subId;//课程编号
    private String subName;//课程名称
    private int stuId;//学生编号

    public Subject() {
    }

    public Subject(String subName, int stuId) {
        this.subName = subName;
        this.stuId = stuId;
    }

    public Subject(int subId, String subName, int stuId) {
        this.subId = subId;
        this.subName = subName;
        this.stuId = stuId;
    }

    public int getSubId() {
        return subId;
    }

    public void setSubId(int subId) {
        this.subId = subId;
    }

    public String getSubName() {
        return subName;
    }

    public void setSubName(String subName) {
        this.subName = subName;
    }

    public int getStuId() {
        return stuId;
    }

    public void setStuId(int stuId) {
        this.stuId = stuId;
    }

    @Override
    public String toString() {
        return "Subject{" +
                "subId=" + subId +
                ", subName='" + subName + '\'' +
                ", stuId=" + stuId +
                '}';
    }
}

2.mybatis-config.xml总配置文件 配置数据库连接参数；添加mappers映射关系

<?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>
    <!--开启二级缓存-->
    <settings>
        <setting name="cacheEnabled" value="true"/>
    </settings>
    <!--配置数据库连接信息-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="oracle.jdbc.OracleDriver"/>
                <property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:orcl"/>
                <property name="username" value="scott"/>
                <property name="password" value="tiger"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="com/test/dao/StudentMapper.xml"/>
        <mapper resource="com/test/dao/SubjectMapper.xml"/>
        <mapper resource="com/test/dao/TeacherMapper.xml"/>
    </mappers>
</configuration>

3.mapper.xml配置文件 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.test.dao.IStudentDao">
    <!--开启二级缓存-->
    <cache></cache>
    <!--通用结果集映射-->
    <resultMap id="BaseResultMap1" type="com.test.entity.Student">
        <id column="stu_id" property="stuId"></id>
        <result column="stu_name" property="stuName"></result>
        <result column="stu_age" property="stuAge"></result>
        <result column="stu_gender" property="stuGender"></result>
        <result column="tea_id" property="teaId"></result>
        <result column="stu_create_date" property="stuCreateDate"></result>
        <!--添加教师映射关系  一对一-->
        <association property="teacher" javaType="com.test.entity.Teacher">
            <id column="t_id" property="tId"></id>
            <result column="t_name" property="tName"></result>
            <result column="t_profession" property="tProfession"></result>
        </association>
    </resultMap>
    <!--一对一  学生 教师-->
    <select id="selectStudentList" resultMap="BaseResultMap1">
        select s.*, t.* from student s,teacher t where s.tea_id = t.t_id
    </select>

    <!--通用结果集映射-->
    <resultMap id="BaseResultMap2" type="com.test.entity.Student">
        <id column="stu_id" property="stuId"></id>
        <result column="stu_name" property="stuName"></result>
        <result column="stu_age" property="stuAge"></result>
        <result column="stu_gender" property="stuGender"></result>
        <result column="tea_id" property="teaId"></result>
        <result column="stu_create_date" property="stuCreateDate"></result>
        <!--添加课程映射关系   一对多-->
        <collection property="subjects" ofType="com.test.entity.Subject">
            <id column="sub_id" property="subId"></id>
            <result column="sub_name" property="subName"></result>
            <result column="stu_id" property="stuId"></result>
        </collection>
    </resultMap>
    <!--一对多  学生  课程-->
    <select id="selectStudentList2" resultMap="BaseResultMap2">
        select st.*,s.* from student st left join subject s on st.stu_id = s.stu_id
    </select>
</mapper>

4.dao层

package com.test.dao;

import com.test.entity.Student;

import java.util.List;

/**
 * @className: com.test.dao.IStudentDao
 * @description: TODO
 * @author: Administrator
 * @create: 2021-04-08 9:36
 */
public interface IStudentDao {
    //查询 学生 教师
    public List<Student> selectStudentList1();

    //查询 学生 课程
    public List<Student> selectStudentList2();
}

5.service层

package com.test.service;

import com.test.entity.Student;

import java.util.List;

/**
 * @className: com.test.service.IStudentService
 * @description: TODO
 * @author: Administrator
 * @create: 2021-04-08 9:39
 */
public interface IStudentService {
    //查询 学生 教师
    public List<Student> selectStudentList1();

    //查询 学生 课程
    public List<Student> selectStudentList2();
}

service.impl层 实现service方法,通过工具类获取连接,创建dao层对象,调用dao层方法

package com.test.service.impl;

import com.test.dao.IStudentDao;
import com.test.entity.Student;
import com.test.service.IStudentService;
import com.test.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;

import java.util.List;

/**
 * @className: com.test.service.impl.StudentServiceImpl
 * @description: TODO
 * @author: Administrator
 * @create: 2021-04-08 9:40
 */
public class StudentServiceImpl implements IStudentService {
    @Override
    public List<Student> selectStudentList1() {
        //获取连接
        SqlSession session = MyBatisUtil.getSqlSession();
        //创建dao层对象    代理模式(反射)
        IStudentDao dao = session.getMapper(IStudentDao.class);
        //调用dao层方法
        List<Student> list = dao.selectStudentList1();
        //提交事务
        session.commit();
        //关闭session
        MyBatisUtil.close();
        //返回值
        return list;
    }

    @Override
    public List<Student> selectStudentList2() {
        //获取连接
        SqlSession session = MyBatisUtil.getSqlSession();
        //创建dao层对象    代理模式(反射)
        IStudentDao dao = session.getMapper(IStudentDao.class);
        //调用dao层方法
        List<Student> list = dao.selectStudentList2();
        //提交事务
        session.commit();
        //关闭session
        MyBatisUtil.close();
        //返回值
        return list;
    }
}

工具类

package com.test.util;

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 java.io.InputStream;

/**
 * 工具类
 * Created by mp on 2021-03-30.- 9:38
 */
public class MyBatisUtil {
    private static SqlSessionFactory factory;

    //保证service层 进行多次dao操作,公用一个SqlSession对象
    private static ThreadLocal<SqlSession> tl = new ThreadLocal<SqlSession>();

    static {
        try {
            String resouce = "mybatis-config.xml";  //代表总配置文件的路径
            InputStream is = Resources.getResourceAsStream(resouce);//使用输入流读取配置文件
            factory = new SqlSessionFactoryBuilder().build(is);//facotry只需要创建一个
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession() {
        SqlSession session = tl.get();
        if (session == null) {
            session = factory.openSession();
            tl.set(session);
        }
        return session;
    }


    public static void close() {
        SqlSession session = tl.get();
        if (session != null) {
            session.close();
            tl.set(null);
        }
    }
}

6.测试

	//多态  创建service层对象
 	IStudentService serviceStudent = new StudentServiceImpl();
    //调用service方法    学生 - 教师
    List<Student> listStudent1 = serviceStudent.selectStudentList1();
    //调用service方法    学生 - 课程
    List<Student> listStudent2 = serviceStudent.selectStudentList2();
    //输出
    System.out.println(listStudent1);
    System.out.println(listStudent2);

总结:一对一，一对多，和多对多关系的查询语句，经常用到的是association标签和collection标签。需要注意的是,mapper映射关系,SQL语句的编写,通用结果集映射,这些都是我在写代码过程中遇到的错误,一个不小心就会出错.萌新一枚,请多多关照.