060_Mybatis结果映射(多对一和一对多)
目录
结果映射
搭建环境
创建数据库
CREATE TABLE `teacher` (
`id` int(10) NOT NULL COMMENT '老师主键' ,
`name` varchar(100) NULL COMMENT '老师姓名' ,
PRIMARY KEY (`id`)
);
CREATE TABLE `student` (
`id` int(10) NOT NULL COMMENT '学生主键' ,
`name` varchar(100) NULL COMMENT '学生姓名' ,
`tid` int(10) NULL COMMENT '老师主键' ,
PRIMARY KEY (`id`)
);
INSERT INTO `teacher` (`id`, `name`) VALUES (1, '鸿钧');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (1, '太清', 1);
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (2, '玉清', 1);
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (3, '上清', 1);
添加实体类
package com.qing.pojo;
import lombok.Data;
import java.io.Serializable;
@Data
public class Teacher implements Serializable {
private static final long serialVersionUID = 1L;
private int id;
private String name;
}
package com.qing.pojo;
import lombok.Data;
import java.io.Serializable;
@Data
public class Student implements Serializable {
private static final long serialVersionUID = 1L;
private int id;
private String name;
private Teacher teacher;
}
添加Mapper接口
package com.qing.dao;
import com.qing.pojo.Teacher;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface TeacherMapper {
@Select("select * from mybatis.teacher")
List<Teacher> listTeacher();
}
package com.qing.dao;
public interface StudentMapper {
}
添加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.qing.dao.TeacherMapper">
</mapper>
<?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.qing.dao.StudentMapper">
</mapper>
注册Mapper接口到mybatis-config.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="logImpl" value="STDOUT_LOGGING"/>
</settings>
<!--别名-->
<typeAliases>
<package name="com.qing.pojo"/>
</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>
<!--注册Mapper接口-->
<mappers>
<package name="com.qing.dao"/>
</mappers>
</configuration>
测试
package com.qing.dao;
import com.qing.pojo.Teacher;
import com.qing.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class MyTest {
/**
* 查询全部老师
*/
@Test
public void listTeacher() {
SqlSession sqlSession = null;
try {
// 获取sqlSession
sqlSession = MybatisUtils.getSqlSession();
// 执行SQL
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
List<Teacher> list = mapper.listTeacher();
for (Teacher teacher : list) {
System.out.println(teacher);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭sqlSession
sqlSession.close();
}
}
}
多对一
添加接口
package com.qing.dao;
import com.qing.pojo.Student;
import java.util.List;
public interface StudentMapper {
/**
* 查询学生List
* @return
*/
List<Student> listStudent();
}
添加Mapper
复杂的属性，需要单独处理 对象用：association 集合用：collection
方式一：按照查询嵌套处理
<?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.qing.dao.StudentMapper">
<resultMap id="student" type="student">
<id property="id" column="id"/>
<result property="name" column="name"/>
<!--复杂的属性，需要单独处理 对象用：association 集合用：collection-->
<association property="teacher" column="tid" javaType="com.qing.pojo.Teacher" select="getTeacher"/>
</resultMap>
<!--查询学生List-->
<select id="listStudent" resultMap="student">
select * from mybatis.student
</select>
<select id="getTeacher" resultType="teacher">
select * from mybatis.teacher where id=#{id}
</select>
</mapper>
方式二：按照结果嵌套处理
<?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.qing.dao.StudentMapper">
<resultMap id="student" type="student">
<association property="teacher" javaType="com.qing.pojo.Teacher">
<id property="id" column="tid"/>
<result property="name" column="tname"/>
</association>
</resultMap>
<!--查询学生List-->
<select id="listStudent" resultMap="student">
select s.id, s.name, s.tid, t.name tname from mybatis.student s, mybatis.teacher t where s.tid=t.id
</select>
</mapper>
一对多
修改实体类
package com.qing.pojo;
import lombok.Data;
import java.io.Serializable;
import java.util.List;
@Data
public class Teacher implements Serializable {
private static final long serialVersionUID = 1L;
private int id;
private String name;
private List<Student> studentList;
}
添加接口
package com.qing.dao;
import com.qing.pojo.Student;
import com.qing.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface TeacherMapper {
/**
* 查询指定老师及学生
* @return
*/
Teacher getTeacher(@Param("id") int id);
}
添加Mapper
方式一：按照查询嵌套处理
<?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.qing.dao.TeacherMapper">
<resultMap id="teacher" type="teacher">
<id property="id" column="id"/>
<result property="name" column="name"/>
<collection property="studentList" ofType="com.qing.pojo.Student" select="listStudentByTid" column="id"/>
</resultMap>
<select id="getTeacher" resultMap="teacher">
select * from mybatis.teacher where id=#{id}
</select>
<select id="listStudentByTid" resultType="student">
select * from mybatis.student where tid=#{tid}
</select>
</mapper>
方式二：按照结果嵌套处理
<?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.qing.dao.TeacherMapper">
<resultMap id="teacher" type="teacher">
<id property="id" column="id"/>
<result property="name" column="name"/>
<collection property="studentList" ofType="com.qing.pojo.Student">
<id property="id" column="sid"/>
<result property="name" column="sname"/>
</collection>
</resultMap>
<select id="getTeacher" resultMap="teacher">
select t.id, t.name, s.id sid, s.name sname from mybatis.teacher t, mybatis.student s where t.id=s.tid and t.id=#{id}
</select>
</mapper>
