MyBatis系列之关联查询(一对一)

灰太狼 2021-09-10 23:36 469阅读 0赞

我们以一个例子demo的形式，说明Mybatis的一对一关联查询

设计teachers表

![Center][]

classes表设置外键

![Center 1][]

classes表

![Center 2][]

classes表是课程表，teachers表是教师信息表

这里是一对一的关联关系，当然实际中一般不是这样的，一般可以是多对多，不过这里只是举例子

项目的结构，如图：

![Center 3][]

Classes.java:

package com.mybatis.entity;
    
    import java.util.List;
    
    public class Classes {
    
    	private int id;
    	
    	private String name;
    	
    	private Teacher teacher;
    
    	private List<Student> students;
    	
    	public List<Student> getStudents() {
    		return students;
    	}
    
    	public void setStudents(List<Student> students) {
    		this.students = students;
    	}
    
    	public int getId() {
    		return id;
    	}
    
    	public void setId(int id) {
    		this.id = id;
    	}
    
    	public String getName() {
    		return name;
    	}
    
    	public void setName(String name) {
    		this.name = name;
    	}
    
    	public Teacher getTeacher() {
    		return teacher;
    	}
    
    	public void setTeacher(Teacher teacher) {
    		this.teacher = teacher;
    	}
    	
    	@Override
    	public String toString() {
    		return "Classes [id=" + id + ", name=" + name + "]";
    	}
    	
    }

Teacher.java:

package com.mybatis.entity;
    
    public class Teacher {
    	
    	private int id;
    	
    	private String name;
    
    	public int getId() {
    		return id;
    	}
    
    	public void setId(int id) {
    		this.id = id;
    	}
    
    	public String getName() {
    		return name;
    	}
    
    	public void setName(String name) {
    		this.name = name;
    	}
    	
    
    }

这里以两种方式进行使用

classesMapper.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.mybatis.mapper.classesMapper">
    
    
     
    	<select id="findById" parameterType="int" resultMap="ClassesResultMap">
    	select * from classes c,teachers t where c.teacher_id=t.t_id and c.c_id=#{id}
    	</select>
    	<resultMap type="Classes" id="ClassesResultMap">
    		<id property="id" column="c_id" />
    		<result property="name" column="c_name" />
    		<association property="teacher" column="teacher_id" javaType="Teacher">
    			<id property="id" column="t_id" />
    			<result property="name" column="t_name" />
    		</association>
    	</resultMap>
    	
    	<select id="findById2" parameterType="int" resultMap="ClassesResultMap2">
    	select * from classes where c_id=#{id}
    	</select>
    	<resultMap type="Classes" id="ClassesResultMap2">
    		<id property="id" column="c_id" />
    		<result property="name" column="c_name" />
    		<association property="teacher" column="teacher_id" javaType="Teacher" select="getTeacherInfo">
    		</association>
    	</resultMap>
    	<select id="getTeacherInfo" parameterType="int" resultType="Teacher">
    	select t_id,t_name from teachers where t_id=#{id}
    	</select>
    </mapper>

然后对mybatis进行配置：

<?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>
    	
    	 
        <properties resource="db.properties" />  
         
       
    	<typeAliases>
    		<typeAlias alias="User" type="com.mybatis.entity.User" />
    		<typeAlias alias="Classes" type="com.mybatis.entity.Classes" />
    		<typeAlias alias="Student" type="com.mybatis.entity.Student" />
    		<typeAlias alias="Teacher" type="com.mybatis.entity.Teacher" />
    	</typeAliases>
    
    	
    	
    	<environments default="development">
    		<environment id="development">
    			<transactionManager type="JDBC" />
    			<dataSource type="POOLED">
    				<property name="driver" value="${driver}"/>
    	            <property name="url" value="${url}" />
    	            <property name="username" value="${username}"/>
    	            <property name="password" value="${password}"/>
    			</dataSource>
    		</environment>
    	</environments>
    	
    	
    	
    	<mappers>
    		<mapper class="com.mybatis.mapper.UserMapper"/>
    		<mapper resource="com/mybatis/mapper/classesMapper.xml"/>
    	</mappers>
    	
    </configuration>

db,properties:

driver=com.mysql.jdbc.Driver
    url=jdbc\:mysql\://localhost\:3306/db_mysql?useUnicode\=true&characterEncoding\=utf-8
    username=root
    password=111

编写测试类：

String resource = "mybatis-config.xml";
    		try{
    			//加载Mybatis的配置文件
    			Reader reader = Resources.getResourceAsReader(resource);
    			//构建sqlSession的工厂
    	        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
    			//创建可以执行sql的SqlSession
    	        SqlSession session = sessionFactory.openSession();
    			UserMapper mapper = session.getMapper(UserMapper.class);
    			User user = mapper.findById(1);
    	        System.out.println(user);
    	        
    	        Classes c = session.selectOne("com.mybatis.mapper.classesMapper.findById1",1);
    	        System.out.println(c);
    	      
    	        
    		}catch (Exception e) {
    			e.printStackTrace();
    		}

[Center]: /images/20210813/65e8a3ec01a24977adf34c35209fc552.png
[Center 1]: /images/20210813/c973e8414a064776b8b93a2319025560.png
[Center 2]: /images/20210813/f2d4350706e54a43a6a9a40dd6bf804b.png
[Center 3]: /images/20210813/b28c01d05f4c48cda6e487e7478fba82.png