resultMap如何處理復(fù)雜映射問題
resultMap復(fù)雜映射問題
association
:關(guān)聯(lián)(多對一的情況)collection
: 集合(一對多的情況)javaType
: 用來指定實體類中屬性的類型。ofType
: 用來指定映射到List或集合中POJO的類型,泛型的約束類型。
Ⅰ 多對一查詢:學(xué)生——老師
數(shù)據(jù)庫表:
CREATE TABLE `teacher` ( `id` INT(10) NOT NULL, `name` VARCHAR(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; INSERT INTO teacher(`id`, `name`) VALUES (1, '王老師'); CREATE TABLE `student` ( `id` INT(10) NOT NULL, `name` VARCHAR(30) DEFAULT NULL, `tid` INT(10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `fktid` (`tid`), CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; 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'); INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1'); INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王', '1');
(1) 創(chuàng)建實體類POJO
@Data public class Student { private int id; private String name; private Teacher teacher; }
@Data public class Teacher { private int id; private String name; }
(2) 創(chuàng)建學(xué)生實體類對應(yīng)的接口
public interface StudentMapper { //查詢所有學(xué)生的信息 List<Student> getStudent(); List<Student> getStudent2(); }
(3) 編寫學(xué)生接口對應(yīng)的Mapper.xml
為了達(dá)到和接口在同一個包中的效果,在resource文件夾下新建包結(jié)構(gòu)com.glp.dao:
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.glp.dao.StudentMapper"> <!--按照結(jié)果查詢——聯(lián)表查詢--> <select id="getStudent2" resultMap="StudentMap2"> select s.id sid,s.name sname,t.name tname from student s, teacher t where s.tid=t.id; </select> <resultMap id="StudentMap2" type="Student"> <result property="id" column="sid"/> <result property="name" column="sname"/> <association property="teacher" javaType="Teacher"> <result property="name" column="tname"/> </association> </resultMap> <!--按照查詢嵌套處理——子查詢--> <select id="getStudent" resultMap="StudentMap" > select * from student; </select> <resultMap id="StudentMap" type="Student"> <result property="id" column="id"/> <result property="name" column="name"/> <!--復(fù)雜屬性:對象association, 集合collection--> <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/> </resultMap> <select id="getTeacher" resultType="Teacher"> select * from teacher where id = #{id}; </select> </mapper>
在多對一查詢中,需要用到teacher這個表,每個學(xué)生都對應(yīng)著一個老師。而property只能處理單個屬性,像teacher這種復(fù)雜屬性(內(nèi)含多個屬性)需要進(jìn)行處理。處理復(fù)雜對象要用association 。
方式一:
- 聯(lián)表查詢(直接查出所有信息,再對結(jié)果進(jìn)行處理)
<resultMap id="StudentMap2" type="Student"> <result property="id" column="sid"/> <result property="name" column="sname"/> <association property="teacher" javaType="Teacher"> <result property="name" column="tname"/> </association> </resultMap>
直接查詢出學(xué)生和老師,然后用association
去取老師里面的屬性property。
方式二:
- 子查詢(先查出學(xué)生信息,再拿著學(xué)生中的tid,去查詢老師的信息)
<resultMap id="StudentMap" type="Student"> <result property="id" column="id"/> <result property="name" column="name"/> <!--復(fù)雜屬性:對象association--> <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/> </resultMap> <select id="getTeacher" resultType="Teacher"> select * from teacher where id = #{id}; </select>
在resultMap中引入屬性association
,通過javaType指定property="teacher"的類型,javaType="Teacher"
。通過select引入子查詢
(嵌套查詢)。
這里是拿到學(xué)生中的tid,去查找對應(yīng)的老師。
(4)在核心配置類中引入Mapper
db.properties:數(shù)據(jù)庫連接參數(shù)配置文件
driver = com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&chracterEncoding=utf8 username =root password =mysql
mybatis.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"> <property name="username" value="root"/> <property name="password" value="mysql"/> </properties> <settings> <setting name="logImpl" value="STDOUT_LOGGING"/> </settings> <typeAliases> <typeAlias type="com.glp.POJO.Student" alias="Student"/> <typeAlias type="com.glp.POJO.Teacher" alias="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.glp.dao.StudentMapper"/> <mapper class="com.glp.dao.TeacherMapper"/> </mappers> </configuration>
注意:
要保證接口和Mapper.xml都在同一個包中。
(5) 測試
public class UserDaoTest { @Test public void getStudent(){ SqlSession sqlSession = MyUtils.getSqlSession(); StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); List<Student> list = mapper.getStudent(); for (Student stu:list ) { System.out.println(stu); } sqlSession.close(); } @Test public void getStudent2(){ SqlSession sqlSession = MyUtils.getSqlSession(); StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); List<Student> list = mapper.getStudent2(); for (Student stu:list ) { System.out.println(stu); } sqlSession.close(); } }
Ⅱ 一對多查詢:老師——學(xué)生
(1)實體類
@Data public class Student { private int id; private String name; private int tid; }
@Data public class Teacher { private int id; private String name; private List<Student> students; }
(2) 接口
package com.glp.dao; public interface TeacherMapper { Teacher getTeacher(@Param("tid") int id); Teacher getTeacher2(@Param("tid") int id); }
(3)接口對應(yīng)的Mapper.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.glp.dao.TeacherMapper"> <!--方式一 ======================= --> <select id="getTeacher" resultMap="TeacherStudent"> select s.id sid, s.name sname, t.name tname, t.id tid from student s ,teacher t where s.tid = t.id and t.id = #{tid}; </select> <resultMap id="TeacherStudent" type="Teacher"> <result property="id" column="tid"/> <result property="name" column="tname"/> <collection property="students" ofType="Student"> <result property="id" column="sid"/> <result property="name" column="sname"/> <result property="tid" column="tid"/> </collection> </resultMap> <!--方式二 ======================= --> <select id="getTeacher2" resultMap="TeacherStudent2"> select * from teacher where id = #{tid}; <!--這里的tid和接口中指定的屬性名相同--> </select> <resultMap id="TeacherStudent2" type="Teacher"> <result property="id" column="id"/> <result property="name" column="name"/> <!--上面的兩個可以省略--> <collection property="students" column="id" javaType="ArrayList" ofType="Student" select="getStuById"/> </resultMap> <select id="getStuById" resultType="Student"> select * from student where tid=#{tid}; <!--查詢老師對應(yīng)的學(xué)生,#{tid}--> </select> </mapper>
方式一:
- 聯(lián)表查詢,需要寫復(fù)雜SQL
- collection 用來處理集合,ofType用來指定集合中的約束類型
- 聯(lián)合查詢時,查詢出所以結(jié)果,然后再解析結(jié)果中的屬性,將屬性property賦予到collection中。
方式二:
- 子查詢,需要寫復(fù)雜映射關(guān)系
查詢學(xué)生時,需要拿著老師的id去查找,column用來給出老師的id。
(4)測試:
package com.glp.dao; public class UserDaoTest { @Test public void getTeacher(){ SqlSession sqlSession = MyUtils.getSqlSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); Teacher teacher = mapper.getTeacher(1); System.out.println(teacher); sqlSession.close(); } @Test public void getTeacher2(){ SqlSession sqlSession = MyUtils.getSqlSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); Teacher teacher = mapper.getTeacher2(1); System.out.println(teacher); sqlSession.close(); } }
總結(jié)
以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。
相關(guān)文章
SpringBoot在IDEA中實現(xiàn)熱部署(JRebel實用版)
這篇文章主要介紹了SpringBoot在IDEA中實現(xiàn)熱部署(JRebel實用版),文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-05-05spring boot 注入 property的三種方式(推薦)
這篇文章主要介紹了spring boot 注入 property的三種方式,需要的朋友可以參考下2017-07-07詳解MyBatis直接執(zhí)行SQL查詢及數(shù)據(jù)批量插入
這篇文章主要介紹了MyBatis直接執(zhí)行SQL查詢及數(shù)據(jù)批量插入的相關(guān)知識,需要的朋友一起學(xué)習(xí)吧2016-01-01