One2One关联表查询

One2One关联表查询

 

<?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.hous.day5.classMapper">
	<!--
	方式一：嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集
	 封装联表查询的数据(去除重复的数据)
	SELECT * FROM class,teacher WHERE class.teacher_id=teacher.t_id AND c_id=2
	-->
	
	<select id="getClass" resultMap="teacherResultMap">
		SELECT * FROM class,teacher WHERE class.teacher_id=teacher.t_id AND c_id=#{id}
	</select>
	
	<resultMap type="com.hous.day5.Classes" id="teacherResultMap">
		<id property="id" column="c_id"/>
		<result property="name" column="c_name"/>
		<association property="teacher" column="teacher_id" javaType="com.hous.day5.Teacher">
			<id property="id" column="t_id"/>
			<result property="name" column="t_name"/>
		</association>
	</resultMap>
	
	<!--
	方式二：嵌套查询：通过执行另外一个 SQL 映射语句来返回预期的复杂类型
	SELECT * FROM class WHERE c_id=1;
	SELECT * FROM teacher WHERE t_id=1 //1 是上一个查询得到的 teacher_id 的值
	-->
	<select id="getClass2" parameterType="int" resultMap="classResultMap2">
		select * from class where c_id=#{id}
	</select>
	
	<select id="getTeacher" parameterType="int" resultType="com.hous.day5.Teacher">
		select t_id id, t_name name from teacher where t_id=#{id}
	</select>
	
	<resultMap type="com.hous.day5.Classes" id="classResultMap2">
		<id property="id" column="c_id"/>
		<result property="name" column="c_name"/>
		<association property="teacher" column="teacher_id" select="getTeacher">
			<id property="id" column="t_id"/>
			<result property="name" column="t_name"/>
		</association>
	</resultMap>
	
</mapper>

 

<?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://localhost:3306/mybatis"/>
				<property name="username" value="root" />
				<property name="password" value="root" />
			</dataSource>
		</environment>
	</environments>
	
	<mappers>
		<mapper resource="com/hous/day5/classMapper.xml" />
	</mappers>
</configuration>

 

package com.hous.day5;

import static org.junit.Assert.*;

import java.io.InputStream;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

public class MyTest {
	
	private SqlSessionFactory factory = null;
	
	@Before
	public void setUp() throws Exception {
		String resource = "day5/config.xml";
		InputStream config = MyTest.class.getClassLoader().getResourceAsStream(resource);
		factory = new SqlSessionFactoryBuilder().build(config);
	}

	@Test
	public void testGetClass() {
		SqlSession session = factory.openSession();
		String statement = "com.hous.day5.classMapper.getClass2";
		Classes classes = session.selectOne(statement, 1);
		session.commit();
		session.close();
		System.out.println("查询结果：" + classes);
	}

}

 

package com.hous.day5;

public class Classes {
	private int id;
	private String name;
	private Teacher teacher;

	public Classes() {
		super();
	}

	public Classes(int id, String name, Teacher teacher) {
		super();
		this.id = id;
		this.name = name;
		this.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 + "]";
	}
	
}

 

package com.hous.day5;

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;
	}

	@Override
	public String toString() {
		return "Teacher [id=" + id + ", name=" + name + "]";
	}

}

 

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
);

insert into teacher(t_name) values('ls1'),('ls2');
insert into class(c_name, teacher_id) values('bja', 1),('bjb', 2);