MyBatis 中两表关联查询MYSQL (14)
MyBatis 中两表关联查询MYSQL
1、创建数据库表语句
2、插入测试数据
3、pom文件内容
<?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> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatis01"/> <property name="username" value="root"/> <property name="password" value="root"/> </dataSource> </environment> </environments> <mappers> <!-- jack.zhao 加载映射文件 --> <mapper resource="com/mybatis03/mapper/personMapper.xml"/> <mapper resource="com/mybatis03/mapper/TeacherMapper.xml"/> </mappers> </configuration>
4、实体类
4.1教师表对应的实体类
package com.mybatis03.bean; /** * @author :jack.zhao * @description 教师实体类 * @date :2021-10-16 22:55 */ public class Teacher { /* 教师编号 */ private int teaNo; /* 课程编号 */ private int courseNo; /* 教师名称 */ private String teaName; public int getCourseNo() { return courseNo; } public void setCourseNo(int courseNo) { this.courseNo = courseNo; } public int getTeaNo() { return teaNo; } public void setTeaNo(int teaNo) { this.teaNo = teaNo; } public String getTeaName() { return teaName; } public void setTeaName(String teaName) { this.teaName = teaName; } }
4.2 教师课程实体类
package com.mybatis03.bean; /** * @author :jack.zhao * @Describe: 教师课程类 * @date :2021-10-16 22:55 */ public class TeacherCourse { /* 课程编号 */ private int courseNo; /* 课程名称 */ private String courseName; public int getCourseNo() { return courseNo; } public void setCourseNo(int courseNo) { this.courseNo = courseNo; } public String getCourseName() { return courseName; } public void setCourseName(String courseName) { this.courseName = courseName; } }
4.3 教师实体类与课程实体类总的集合类(用于关联数据库表查询结果集)
package com.mybatis03.bean; /** * @author :jack.zhao * @description 此类包含教师和课程属性 * @date :2021-10-16 22:55 */ public class TeacherBusiness extends Teacher { public TeacherBusiness(){ super(); } /* 课程编号 */ private int courseNo; /* 课程名称 */ private String courseName; public int getCourseNo() { return courseNo; } public void setCourseNo(int courseNo) { this.courseNo = courseNo; } public String getCourseName() { return courseName; } public void setCourseName(String courseName) { this.courseName = courseName; } @Override public String toString() { return "TeacherBusiness{" + "courseNo=" + courseNo + ", courseName='" + courseName + ", teacherName='" + this.getTeaName() + ", teacherNO='" + this.getTeaNo() + '\'' + '}'; } }
5、接口类
package com.mybatis03.mapper; import com.mybatis03.bean.TeacherBusiness; import java.util.List; /** * @author :jack.zhao * @Describe: 操作mybatis接口 * @date :2021-10-16 22:55 */ public interface TeacherMapper { List<TeacherBusiness> queryTeacherBusinessInfoWithCourseNo(int courseNo); }
6、mapper.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.mybatis03.mapper.TeacherMapper"> <select id="queryTeacherBusinessInfoWithCourseNo" parameterType="int" resultType="com.mybatis03.bean.TeacherBusiness"> select s.*,c.* FROM teacher s INNER JOIN teachercourse c ON s.courseno = c.courseno where s.teano = #{teano} </select> </mapper>
7、测试类
package com.mybatis03.test; import com.mybatis03.bean.TeacherBusiness; import com.mybatis03.mapper.TeacherMapper; 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.jupiter.api.Test; import java.io.Reader; import java.util.List; /** * @author :jack.zhao * @Describe: 测试 * @date :2021-10-16 22:55 */ public class testTeacher01 { @Test public void queryTeacherBusinessInfoWithCourseNo() throws Exception{ Reader reader = Resources.getResourceAsReader("mybatis-03.xml"); SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader); SqlSession session = sessionFactory.openSession(); int courseNo=1002; // (jack.zhao)动态代理 TeacherMapper personMapper = session.getMapper(TeacherMapper.class); List<TeacherBusiness> teacherBusinessList = personMapper.queryTeacherBusinessInfoWithCourseNo(courseNo); System.out.println("联合查询结果为:"+teacherBusinessList); } }
8、执行测试结果