MyBatis中一对一关联查询

水深无声 2022-07-13 01:40 344阅读 0赞

1、一对一关联查询的案例

1.1  需求

根据班级id查询班级信息（带老师信息）

1.2  创建表和数据

创建一张教师表和班级表，这里我们假设一个老师只负责教一个班，那么老师和班级之间的关系就是一种一对一的关系。

CREATE TABLE teacher(
        t_id INT PRIMARY KEY AUTO_INCREMENT, 
        t_name VARCHAR(20)
    );
    CREATE TABLE class(
        c_id INT PRIMARY KEY AUTO_INCREMENT, 
        c_name VARCHAR(20), 
        teacher_id INT
    );
    ALTER TABLE class ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES teacher(t_id);    
    
    INSERT INTO teacher(t_name) VALUES('teacher1');
    INSERT INTO teacher(t_name) VALUES('teacher2');
    
    INSERT INTO class(c_name, teacher_id) VALUES('class_a', 1);
    INSERT INTO class(c_name, teacher_id) VALUES('class_b', 2);

2、定义实体类

2.1  Teacher类，Teacher类是teacher表对应的实体类

public class Teacher {
    
    	// 定义实体类的属性，与teacher表中的字段对应
    	private int id; // id===>t_id
    	private String name; // name===>t_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;
    	}
    
    	@Override
    	public String toString() {
    		return "Teacher [id=" + id + ", name=" + name + "]";
    	}
    }

2.2 Classes类，Classes类是class表对应的实体类

public class Classes {
    
    	// 定义实体类的属性，与class表中的字段对应
    	private int id; // id===>c_id
    	private String name; // name===>c_name
    
    	/**
    	 * class表中有一个teacher_id字段，所以在Classes类中定义一个teacher属性，
    	 * 用于维护teacher和class之间的一对一关系，通过这个teacher属性就可以知道这个班级是由哪个老师负责的
    	 */
    	private Teacher teacher;
    
    	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=" + teacher
    				+ "]";
    	}
    }

3、定义Mapper对象ClassMapper

public interface ClassMapper {
    	/**
    	 * 用于测试，非懒加载的one2one的实现
    	 * 
    	 * @param id
    	 * @return
    	 */
    	public Classes getClass1(int id);
    
    	/**
    	 * 用于测试，懒加载的one2one的实现
    	 * 
    	 * @param id
    	 * @return
    	 */
    	public Classes getClass2(int id);
    }

4、定义sql映射文件classMapper.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.baowei.mapper.ClassMapper">
    
    	
    
    	 
    	<select id="getClass1" parameterType="int" resultMap="ClassResultMap">
    		select *
    		from class c, teacher t where c.teacher_id=t.t_id and c.c_id=#{id}
    	</select>
    	
    	<resultMap type="com.baowei.entity.Classes" id="ClassResultMap">
    		<id property="id" column="c_id" />
    		<result property="name" column="c_name" />
    		<association property="teacher" javaType="com.baowei.entity.Teacher">
    			<id property="id" column="t_id" />
    			<result property="name" column="t_name" />
    		</association>
    	</resultMap>
    
    	
    	<select id="getClass2" parameterType="int" resultMap="ClassResultMap2">
    		select *
    		from class where c_id=#{id}
    	</select>
    	
    	<resultMap type="com.baowei.entity.Classes" id="ClassResultMap2">
    		<id property="id" column="c_id" />
    		<result property="name" column="c_name" />
    		<association property="teacher" column="teacher_id"
    			select="getTeacher" />
    	</resultMap>
    	<select id="getTeacher" parameterType="int"
    		resultType="com.baowei.entity.Teacher">
    		SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
    	</select>
    
    </mapper>

5、Mybatis核心配置文件SqlMapConfig.xml的配置

5.1  打开Mybatis的懒加载功能

<settings>
    		
    		<setting name="lazyLoadingEnabled" value="true" />
    		
    		<setting name="aggressiveLazyLoading" value="false" />
    	</settings>

5.2  完整的SqlMapConfig.xml配置

<?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" />
    	
    	<settings>
    		
    		<setting name="lazyLoadingEnabled" value="true" />
    		
    		<setting name="aggressiveLazyLoading" value="false" />
    	</settings>
    	
    	<environments default="development">
    		<environment id="development">
    			
    			<transactionManager type="JDBC" />
    			
    			<dataSource type="POOLED">
    				<property name="driver" value="${jdbc.driver}" />
    				<property name="url" value="${jdbc.url}" />
    				<property name="username" value="${jdbc.username}" />
    				<property name="password" value="${jdbc.password}" />
    			</dataSource>
    		</environment>
    	</environments>
    	
    	<mappers>
    		<package name="com.baowei.mapper" />
    	</mappers>
    
    </configuration>

6、MyBatisUtil.java工具的使用

import java.io.IOException;
    import java.io.InputStream;
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    
    public class MyBatisUtil {
    
    	/**
    	 * 获取SqlSessionFactory
    	 * 
    	 * @return SqlSessionFactory
    	 */
    	public static SqlSessionFactory getSqlSessionFactory() {
    		String resource = "SqlMapConfig.xml";
    		InputStream inputStream = null;
    		SqlSessionFactory sqlSessionFactory = null;
    		try {
    			inputStream = Resources.getResourceAsStream(resource);
    			sqlSessionFactory = new SqlSessionFactoryBuilder()
    					.build(inputStream);
    
    		} catch (IOException e) {
    			e.printStackTrace();
    		}
    		return sqlSessionFactory;
    	}
    
    	/**
    	 * 获取SqlSession
    	 * 
    	 * @return SqlSession
    	 */
    	public static SqlSession getSqlSession() {
    		return getSqlSessionFactory().openSession();
    	}
    
    	/**
    	 * 获取SqlSession
    	 * 
    	 * @param isAutoCommit
    	 *            true 表示创建的SqlSession对象在执行完SQL之后会自动提交事务 false
    	 *            表示创建的SqlSession对象在执行完SQL之后不会自动提交事务
    	 *            ，这时就需要我们手动调用sqlSession.commit()提交事务
    	 * @return SqlSession
    	 */
    	public static SqlSession getSqlSession(boolean isAutoCommit) {
    		return getSqlSessionFactory().openSession(isAutoCommit);
    	}
    
    	public static void main(String[] args) {
    		System.out.println(getSqlSessionFactory());
    	}
    }

7、one2one关联查询的测试代码

import org.apache.ibatis.session.SqlSession;
    import org.junit.Test;
    
    import com.baowei.entity.Classes;
    import com.baowei.mapper.ClassMapper;
    import com.baowei.utils.MyBatisUtil;
    
    public class TestOne2One {
    
    	/**
    	 * 非懒加载的one2one的测试
    	 */
    	@Test
    	public void testGetClass() {
    		SqlSession sqlSession = MyBatisUtil.getSqlSession();
    
    		ClassMapper mapper = sqlSession.getMapper(ClassMapper.class);
    		Classes clazz = mapper.getClass1(1);
    
    		// 使用SqlSession执行完SQL之后需要关闭SqlSession
    		sqlSession.close();
    		System.out.println(clazz.getId());
    	}
    
    	/**
    	 * 懒加载的one2one的测试
    	 */
    	@Test
    	public void testGetClass2() {
    		SqlSession sqlSession = MyBatisUtil.getSqlSession();
    
    		ClassMapper mapper = sqlSession.getMapper(ClassMapper.class);
    		Classes clazz = mapper.getClass2(1);
    
    		// 使用SqlSession执行完SQL之后需要关闭SqlSession
    		sqlSession.close();
    		System.out.println(clazz.getId());
    		// 可用于测试懒加载(查看日志的sql输出结果，就可以发现使用了懒加载)
    		//System.out.println(clazz.getTeacher());
    
    	}
    }

8、Mybatis一对一关联查询的总结

MyBatis中使用association标签来解决一对一的关联查询，association标签可用的属性如下：

*  property:对象属性的名称
 *  javaType:对象属性的类型
 *  column:所对应的外键字段名称
 *  select:使用另一个查询封装的结果

9、参考博文地址

http://www.cnblogs.com/xdp-gacl/p/4264440.html

10、[源码下载][Link 1]

[Link 1]: http://pan.baidu.com/s/1o8Esubo