Mybatis复杂对象resultMap
jhx_84 人气:0Mybatis 复杂对象resultMap
数据对象含有普通属性,一对一对象,一对多对象(2种情况:单一主键和复合主键)
下面是resultMap的定义
<resultMap id="GoodsResultMap" type="GoodsModelCustomize"> <id property="goods_cd" column="goods_cd" /> <result property="production_company_cd" column="production_company_cd" /> <result property="production_place_cd" column="production_place_cd" /> <result property="goods_nm_jp" column="goods_nm_jp" /> <result property="goods_nm_en" column="goods_nm_en" /> <result property="goods_nm_zh" column="goods_nm_zh" /> <result property="goods_type" column="goods_type" /> <result property="goods_package" column="goods_package" /> <result property="upd_dttm" column="upd_dttm" /> <association property="productioncompany" column="production_company_cd" javaType="trade.db.model.Productioncompany"> <id property="production_company_cd" column="production_company_cd"/> <result property="company_nm_jp" column="company_nm_jp"/> <result property="company_rnm_jp" column="company_rnm_jp"/> </association> <collection property="goodsnutrientList" column="goods_cd" ofType="trade.db.model.Goodsnutrient" select="getGoodsnutrient" /> <collection property="liquorgoodsccicList" column="goods_cd" ofType="trade.db.model.Liquorgoodsccic" select="getLiquorgoodsccic"> <id property="goods_cd" column="goods_cd" /> <id property="ccic_item_cd" column="ccic_item_cd"/> <result property="ccic_item_val1" column="ccic_item_val1"/> </collection> <collection property="projectgoodsList" column="goods_cd" ofType="trade.db.model.Projectgoods" select="getProjectgoods"> <id property="project_goods_cd" column="project_goods_cd"/> <result property="project_cd" column="project_cd"/> <result property="goods_cd" column="goods_cd"/> </collection> </resultMap> <resultMap id="GoodsnutrientResultMap" type="Goodsnutrient"> <id property="goods_cd" column="goods_cd" /> <id property="nutrient_item_cd" column="nutrient_item_cd" /> <result property="sort_no" column="sort_no" /> <result property="nutrient_value" column="nutrient_value" /> <result property="nutrient_nrv" column="nutrient_nrv" /> <result property="upd_dttm" column="upd_dttm" /> </resultMap>
普通属性省略说明
- 一对一属性productioncompany
- 一对多属性goodsnutrientList(复合主键,返回的复杂对象内有数据)
- 一对多属性liquorgoodsccicList(复合主键,返回的复杂对象内没有数据)
- 一对多属性projectgoodsList(单一主键,返回的复杂对象内有数据)
select相关配置
<select id="getGoodsnutrient" parameterType="String" resultMap="GoodsnutrientResultMap"> SELECT m_goods_nutrient.* FROM m_goods_nutrient <where> m_goods_nutrient.goods_cd = #{goods_cd} AND m_goods_nutrient.del_flg = '0' </where> </select> <select id="getLiquorgoodsccic" parameterType="String" resultType="trade.db.model.Liquorgoodsccic"> SELECT m_liquorgoods_ccic.* FROM m_liquorgoods_ccic <where> m_liquorgoods_ccic.goods_cd = #{goods_cd} AND m_liquorgoods_ccic.del_flg = '0' </where> </select> <select id="getProjectgoods" parameterType="String" resultType="trade.db.model.Projectgoods"> SELECT t_project_goods.* FROM t_project_goods <where> t_project_goods.goods_cd = #{goods_cd} AND t_project_goods.del_flg = '0' </where> </select> <select id="findByPrimaryKey" parameterType="String" resultMap="GoodsResultMap"> SELECT m_goods.*, m_production_company.*, now() AS SELECT_TIME FROM m_goods LEFT JOIN m_production_company ON m_goods.production_company_cd = m_production_company.production_company_cd AND m_production_company.del_flg = '0' WHERE m_goods.goods_cd = #{primary_key} AND m_goods.del_flg = '0' </select>
- 通过findByPrimaryKey方法获取普通属性和1对1对象属性
- 通过getGoodsnutrient方法获取1对多复合主键的属性,注意返回类型的配置为resultMap=“GoodsnutrientResultMap”(返回的对象的List属性内有数据)
- 通过getLiquorgoodsccic方法获取1对多复合主键的属性,此处的返回类型为 resultType=“trade.db.model.Liquorgoodsccic”(返回的对象的List属性内没有数据)
- 通过getProjectgoods方法获取1对多单一主键的属性,此处的返回类型为resultType=“trade.db.model.Projectgoods”(返回的对象的List属性内有数据)
Model代码
public class GoodsModelCustomize extends Goods { /** * 製造会社 */ private Productioncompany productioncompany; public Productioncompany getProductioncompany() { return productioncompany; } public void setProductioncompany(Productioncompany productioncompany) { this.productioncompany = productioncompany; } /** * 商品栄養成分 */ private List<Goodsnutrient> goodsnutrientList; public List<Goodsnutrient> getGoodsnutrientList() { return goodsnutrientList; } public void setGoodsnutrientList(List<Goodsnutrient> goodsnutrientList) { this.goodsnutrientList = goodsnutrientList; } /** * 酒類商品CCIC */ private List<Liquorgoodsccic> liquorgoodsccicList; public List<Liquorgoodsccic> getLiquorgoodsccicList() { return liquorgoodsccicList; } public void setLiquorgoodsccicList(List<Liquorgoodsccic> liquorgoodsccicList) { this.liquorgoodsccicList = liquorgoodsccicList; } /** * 项目商品列表 */ private List<Projectgoods> projectgoodsList; public List<Projectgoods> getProjectgoodsList() { return projectgoodsList; } public void setProjectgoodsList(List<Projectgoods> projectgoodsList) { this.projectgoodsList = projectgoodsList; } }
普通属性继承与Goods代码省略,上述属性productioncompany,goodsnutrientList和projectgoodsList有数据,但是liquorgoodsccicList没有数据
因此,当返回对象内有1对多的List属性,同时此list为复合主键的话,推荐使用resultMap来对返回数据映射。
resultMap处理复杂映射问题
association
:关联(多对一的情况)collection
: 集合(一对多的情况)javaType
: 用来指定实体类中属性的类型。ofType
: 用来指定映射到List或集合中POJO的类型,泛型的约束类型。
Ⅰ 多对一查询:学生——老师
数据库表:
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) 创建实体类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) 创建学生实体类对应的接口
public interface StudentMapper { //查询所有学生的信息 List<Student> getStudent(); List<Student> getStudent2(); }
(3) 编写学生接口对应的Mapper.xml
为了达到和接口在同一个包中的效果,在resource文件夹下新建包结构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"> <!--按照结果查询——联表查询--> <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"/> <!--复杂属性:对象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这个表,每个学生都对应着一个老师。而property只能处理单个属性,像teacher这种复杂属性(内含多个属性)需要进行处理。处理复杂对象要用association 。
方式一:联表查询(直接查出所有信息,再对结果进行处理)
<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>
直接查询出学生和老师,然后用association去取老师里面的属性property。
方式二:子查询(先查出学生信息,再拿着学生中的tid,去查询老师的信息)
<resultMap id="StudentMap" type="Student"> <result property="id" column="id"/> <result property="name" column="name"/> <!--复杂属性:对象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引入子查询(嵌套查询)。
这里是拿到学生中的tid,去查找对应的老师。
(4)在核心配置类中引入Mapper
db.properties:数据库连接参数配置文件
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(); } }
Ⅱ 一对多查询:老师——学生
(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)接口对应的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}; <!--查询老师对应的学生,#{tid}--> </select> </mapper>
方式一:联表查询,需要写复杂SQL
collection 用来处理集合,ofType用来指定集合中的约束类型
联合查询时,查询出所以结果,然后再解析结果中的属性,将属性property赋予到collection中。
方式二:子查询,需要写复杂映射关系
查询学生时,需要拿着老师的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(); } }
以上为个人经验,希望能给大家一个参考,也希望大家多多支持。
加载全部内容