MyBatis实现多表查询方式二

上次我们的SQL映射全部写在了一个文件中，不利于复用和维护。

sql语句除了上次的表连接的写法，也可以写成子查询的方式

select 
stuid,stuname,stupwd,stuage,ggid,student.gid,
(select gender from gender where gid=student.ggid),
(select gname from grade where gid=student.gid)
from student

下面就是这种方式的体现。

修改映射文件未多个，分开来写：
gender表的映射

<?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.xyj.dao.GenderDao">
	
	<select id="findById" resultMap="gendermap">
		select * from gender where gid=#{gid}
	</select>
	
	<resultMap type="gender" id="gendermap">
		<id column="gid" property="gid"/>
		<result column="gender" property="gender"/>
	</resultMap>
	
</mapper>

grade表的映射

<?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.xyj.dao.GradeDao">
	
	<select id="findById" resultMap="grademap">
		select * from grade where gid=#{gid}
	</select>
	
	<resultMap type="grade" id="grademap">
		<id column="gid" property="gid"/>
		<result column="gname" property="gname"/>
	</resultMap>
	
</mapper>

由于student表中引用了gender表和grade表，所以分开映射文件之后还想调用的话要使用以下方式：

<association property="gender" select="com.xyj.dao.GenderDao.findById" column="ggid"></association>

student表的映射

<?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.xyj.dao.StudentDao">
	
	<select id="findThreeTables" resultMap="stumap">
		select * from student
	</select>
	
	<resultMap type="student" id="stumap">
		<id column="stuid" property="stuid"/>
		<result column="stuname" property="stuname"/>
		<result column="stupwd" property="stupwd"/>
		<result column="stuage" property="stuage"/>
		<association property="gender" select="com.xyj.dao.GenderDao.findById" column="ggid"></association>
		<association property="grade" select="com.xyj.dao.GradeDao.findById" column="gid"></association>
	</resultMap>
	
	
</mapper>

因为有三个映射文件，所以配置文件中的mappers元素需要加载三个映射文件，如下：

	<mappers>
		<mapper resource="mapper/StudentMapper.xml"/>
		<mapper resource="mapper/GradeMapper.xml"/>
		<mapper resource="mapper/GenderMapper.xml"/>
	</mappers>

三个映射文件对应的Dao层如下：

public interface GenderDao {
	Gender findById(@Param("gid")int gid);
}

public interface GradeDao {
	Grade findById(@Param("gid")int gid);
}

public interface StudentDao {
	 List<Student> findThreeTables();
}

以上就修改完成啦，测试是否可以成功：

	@org.junit.Test
	public void testThreeTables() {
		SqlSession session = MyBatisUtils.getSqlSession();
		StudentDao sd = session.getMapper(StudentDao.class);
		List<Student> list = sd.findThreeTables();
		list.forEach(System.out::println);
	}