mybatis实战教程之四：实现关联数据的查询

mybatis主从表关联查询,返回对象带有集合属性解析⽬录主从表关联查询,返回对象带有集合属性VersionResult为接收返回数据对象UpdateRecordEntity为从表数据mapper.xml写法，这个是关键sql查询语句执⾏sql返回的数据页⾯调取接⼝mybatis关联查询（对象嵌套对象）⼀种是⽤关联另⼀个resultMap的形式⼀种联合查询(⼀对⼀)的实现主从表关联查询,返回对象带有集合属性昨天有同事让我帮着看⼀个问题，mybatis主从表联合查询，返回的对象封装集合属性。

我先将出现的问题记录⼀下，然后再讲处理⽅法也简单说明⼀下：VersionResult为接收返回数据对象get\set⽅法我这⾥就省略了。

public class VersionResult extends BaseResult implements Serializable{private Integer id;private String code;@JsonFormat(pattern = "yyyy-MM-dd HH:mm", timezone = "GMT+8")private Date createTimes;//记录内容表的集合对象private List<UpdateRecordEntity> UpdateRecordEntityList;}UpdateRecordEntity为从表数据同样get\set⽅法我这⾥就省略了。

@Table(name = "z_update_record")public class UpdateRecordEntity extends BaseEntity {@Idprivate Integer id;@Column(name = "version_id")private Integer versionId;@Column(name = "module_name")private String moduleName;@Column(name = "update_content")private String updateContent;@JsonFormat(pattern = "yyyy-MM-dd HH:mm", timezone = "GMT+8")@Column(name = "create_time")private Date createTime;@Column(name = "is_delete")private Integer isDelete;}mapper.xml写法，这个是关键<!--跟新记录表封装的对象--><resultMap id="BaseResultMap" type="com.wangtiansoft.wisdomedu.persistence.result.server.VersionResult"><id column="id" property="id" jdbcType="INTEGER"/><result column="code" property="code" /><result column="create_time" property="createTimes" /><collection property="UpdateRecordEntityList" ofType="com.wangtiansoft.wisdomedu.persistence.entity.UpdateRecordEntity"><id property="id" column="id"/><result property="versionId" column="version_id"/><result property="moduleName" column="module_name"/><result property="updateContent" column="update_content"/><result property="createTime" column="create_time"/><result property="isDelete" column="is_delete"/><result property="tenantId" column="tenant_id"/></collection></resultMap>sql查询语句<select id="selectVersionList" parameterType="map" resultMap="BaseResultMap">SELECTz.`code`,z.create_time createTimes,zur.module_name moduleName,zur.update_content updateContent,zur.create_time createTimeFROMz_version zLEFT JOIN z_update_record zur ON z.id = zur.version_idWHEREz.tenant_id = #{tenantId}AND z.is_delete = 0AND z.is_disabled = 0AND zur.tenant_id = #{tenantId}AND zur.is_delete = 0AND YEAR(z.create_time)=YEAR(#{date})ORDER by z.create_time desc</select>执⾏sql返回的数据页⾯调取接⼝下⾯我将接⼝数据粘贴下来：{"code": "0","msg": "","data": [{"id": null,"code": "1419","createTimes": null,"updateRecordEntityList": []}, {"id": null,"code": "开发修改1111","createTimes": null,"updateRecordEntityList": []}, {"id": null,"code": "开发修改1111","createTimes": null,"updateRecordEntityList": []}, {"id": null,"code": "开发修改1111","createTimes": null,"updateRecordEntityList": []}, {"id": null,"code": "开发修改1111","createTimes": null,"updateRecordEntityList": []}]}观察code、createTimes、updateRecordEntityList三个属性，会发现只有code字段有值其余的全部为null。

关于QueryWrapper,实现MybatisPlus多表关联查询⽅式⽬录QueryWrapper实现MybatisPlus多表关联查询1.dao层接⼝使⽤Select注解写SQL2.service层代码⽰例3.反射⼯具类4.判空⼯具类MybatisPlusQueryWrapper简单⽤法QueryWrapper实现MybatisPlus多表关联查询1.dao层接⼝使⽤Select注解写SQL重点：@Param("ew") Wrapper参数是必须，因为${ew.customSqlSegment} 底层其实就是where 条件，所以为了保证Wrapper不为空，service层代码中的Wrapper⾄少需要有⼀个条件：1 = 1@Override@Select("select a.code as code , as name , b.barcode as barcode , a.ware_code as wareCode , as wareName , a.qty as qty , a.oprice as oprice , a.total as total , " + " a.id as id , a.create_by as createBy , a.create_date as createDate , a.update_by as updateBy , a.update_date as updateDate , a.status as status , a.remarks as remarks " + "from sku_stock a , goods b , warehouse c " +"${ew.customSqlSegment} and a.code = b.code and a.ware_code = c.code")IPage<SkuStock> selectPage(IPage<SkuStock> page, @Param("ew")Wrapper<SkuStock> queryWrapper);2.service层代码⽰例service⽗类封装的findPage⽅法：/*** 封装findPage* @param entity* @param search Map中的key：";"为保留关键字，拆分数组，仅⽀持最⼤长度2的数组，* 下标0：QueryWrapper查询条件中的列名(⽀持多表关联查询的表别名 + 列名⽅式，需要dao层接⼝⽀持)* 下标1： QueryWrapper中不同的查询条件，eq：等于，ge：⼤于等..... todo：请⾃⾏完善Mybatis eq、ne、gt、lt、ge、le等* Map中的value：QueryWrapper需要查询的值* @param args QueryWrapper中order by 排序数组* @return*/public IPage<T> findPage(T entity , Map<String , Object> search , String... args){long current = 1L;long size = 10L;if (EmptyUtil.isNoEmpty(ReflexUtil.getFieldValue(entity , "page")) && (long) ReflexUtil.getFieldValue(entity , "page") != 0){current = (long) ReflexUtil.getFieldValue(entity , "page");}if (EmptyUtil.isNoEmpty(ReflexUtil.getFieldValue(entity , "limit")) && (long) ReflexUtil.getFieldValue(entity , "limit") != 0){size = (long) ReflexUtil.getFieldValue(entity , "limit");}QueryWrapper<T> queryWrapper;if (EmptyUtil.isNoEmpty(search)){queryWrapper = new QueryWrapper<>();for (Map.Entry<String , Object> entry:search.entrySet()) {String[] key = entry.getKey().split(";");if (key.length > 1){if (key[1].equals("eq")){queryWrapper.eq(key[0] , entry.getValue());}else if (key[1].equals("ge")){queryWrapper.ge(key[0] , entry.getValue());}else if (key[1].equals("lt")){queryWrapper.lt(key[0] , entry.getValue());}}else {queryWrapper.like(entry.getKey() , entry.getValue());}}}else {queryWrapper = new QueryWrapper<>(entity);}queryWrapper.orderByAsc(args);return super.page(new Page<T>(current , size) , queryWrapper);}service实现类⽅法：public IPage<SkuStock> findPage(SkuStock entity, String... args) {Map<String , Object> search = null;search = new HashedMap();search.put("1;eq" , "1");if (EmptyUtil.isNoEmpty(entity.getCode())|| EmptyUtil.isNoEmpty(entity.getWareCode())){if (EmptyUtil.isNoEmpty(entity.getCode())){search.put("code" , entity.getCode());if (EmptyUtil.isNoEmpty(entity.getWareCode())){search.put("ware_code" , entity.getWareCode());}}else {long limit = entity.getLimit();long page = entity.getPage();entity = new SkuStock();entity.setLimit(limit);entity.setPage(page);}return super.findPage(entity , search , args);}3.反射⼯具类package m.utils;import ng.reflect.Field;import ng.reflect.Method;import java.util.ArrayList;import java.util.Arrays;import java.util.List;/*** @ClassName ReflexUtil* @Description TODO* @Author foxsand* @Data 2021-06-09 15:17* @Version*/public class ReflexUtil {/*** 返回 entity 对象的所有属性，包含⽗类* @param obj* @return*/public static List<Field> getObjectFields(Object obj){Class clazz = obj.getClass();List<Field> fieldList = new ArrayList<>() ;while (clazz != null) {//当⽗类为null的时候说明到达了最上层的⽗类(Object类).fieldList.addAll(Arrays.asList(clazz .getDeclaredFields()));clazz = clazz.getSuperclass(); //得到⽗类,然后赋给⾃⼰}return fieldList;}public static List<Field> getObjectFields(Class<?> clazz){List<Field> fieldList = new ArrayList<>() ;while (clazz != null){fieldList.addAll(Arrays.asList(clazz .getDeclaredFields()));clazz = clazz.getSuperclass(); //得到⽗类,然后赋给⾃⼰}return fieldList;}/*** 判断 Class entity 是否存在名称为 fieldName 的属性* @param fieldName* @param entity* @return*/public static Boolean isField(String fieldName , Object entity){List<Field> fieldList = getObjectFields(entity);for (Field f1:fieldList) {if (fieldName.equals(f1.getName()))return true;}return false;}/*** 返回 entity 对象中的所有⽅法，包含⽗类* @param entity* @return*/public static List<Method> getObjectMethods(Object entity){Class<?> clazz = entity.getClass();List<Method> methods = new ArrayList<>();while (clazz != null && clazz != Object.class) {//当⽗类为null的时候说明到达了最上层的⽗类(Object类). methods.addAll(Arrays.asList(clazz .getDeclaredMethods()));clazz = clazz.getSuperclass(); //得到⽗类,然后赋给⾃⼰}return methods;}public static List<Method> getObjectMethods(Class<?> clazz){List<Method> methods = new ArrayList<>();while (clazz != null && clazz != Object.class) {//当⽗类为null的时候说明到达了最上层的⽗类(Object类). methods.addAll(Arrays.asList(clazz .getDeclaredMethods()));clazz = clazz.getSuperclass(); //得到⽗类,然后赋给⾃⼰return methods;}/*** 判断 Class entity 是否存在名称为 methodName 的⽅法* @param methodName* @param entity* @return*/public static Boolean isMethod(String methodName , Object entity){List<Method> methods = getObjectMethods(entity);for (Method m1:methods) {if (methodName.equals(m1.getName()))return true;}return false;}/*** 循环向上转型, 获取对象的 DeclaredMethod* @param obj* @param methodName* @param parameterTypes ⽅法参数类型* @return*/public static Method getDeclaredMethod(Object obj , String methodName , Class<?>...parameterTypes) {for (Class<?> clazz = obj.getClass(); clazz != Object.class && clazz != null; clazz = clazz.getSuperclass()) { try {return clazz.getDeclaredMethod(methodName, parameterTypes);} catch (Exception e) {// 这⾥甚么都不要做！并且这⾥的异常必须这样写，不能抛出去。

MyBatis关联查询，⼀对⼀关联查询数据库E-R关系实体类public class City {Long id;String name;Long countryId;Date lastUpdate;}public class Country {Long id;String name;Date lastUpdate;}public class CityPlus {Long id;String name;Long countryId;Date lastUpdate;Country country;}public class CountryPlus {Long id;String name;Date lastUpdate;List<City> cityList;}⼀对⼀关联查询 ⼀对⼀关联查询可采⽤的⽅式有：1. 单步查询，通过级联属性赋值result标签级联属性赋值association标签级联属性赋值2. 分步查询单步查询数据模型：⼀个实体Bean中包含另外⼀个实体BeanSQL查询：关联SQL 查询语句，如inner join、left join、right join具体实现⽅式：为级联属性赋值association标签采⽤相同的select标签<select id="selectCityPlusById" resultMap="cityPlusResultMap">select city_id,city,city.country_id as country_id,st_update as last_update,country.country_id as country_country_id,country,st_update as country_last_updatefrom city,countrywhere city.country_id = country.country_id and city_id=#{id}</select>result标签级联属性赋值<id column="city_id" property="id"/><result column="city" property="name"/><result column="country_id" property="countryId"/><result column="last_update" property="lastUpdate"/><result column="country_country_id" property="country.id"/><result column="country" property=""/><result column="country_last_update" property="stUpdate"/></resultMap>association标签级联属性赋值需要指定级联实体Bean在上级Bean中的属性名称，即association标签的property属性；需要指定级联实体Bean的类型，即association标签的javaType属性；association标签的内部和resultMap标签内部具有相同的结构；association标签也可以嵌套association标签；<resultMap id="cityPlusResultMap" type="canger.study.chapter04.bean.CityPlus"><id column="city_id" property="id"/><result column="city" property="name"/><result column="country_id" property="countryId"/><result column="last_update" property="lastUpdate"/><association property="country" javaType="canger.study.chapter04.bean.Country"><id column="country_country_id" property="id"/><result column="country" property="name"/><result column="country_last_update" property="lastUpdate"/></association></resultMap>分步查询 分步查询是指通过两次（或更多次）的查询，来为⼀个⼀对⼀关系的实体Bean赋值。

第1章关联关系查询1.1关联查询当查询内容涉及到具有关联关系的多个表时，就需要使用关联查询。

根据表与表间的关联关系的不同，关联查询分为四种：（1）一对一关联查询（2）一对多关联查询（3）多对一关联查询（4）多对多关联查询由于日常工作中最常见的关联关系是一对多、多对一与多对多，所以这里就不专门只讲解一对一关联查询了，其解决方案与多对一解决方案是相同的。

1.1.1一对多关联查询项目：one2many，在项目dynamicMapper基础上进行修改。

这里的一对多关联查询是指，在查询一方对象的时候，同时将其所关联的多方对象也都查询出来。

下面以国家Country与部长Minister间的一对多关系进行演示。

（1）定义实体在定义实体时，若定义的是双向关联，即双方的属性中均有对方对象作为域属性出现，那么它们在定义各自的toString()方法时需要注意，只让某一方可以输出另一方即可，不要让双方的toString()方法均可输出对方。

这样会形成递归调用，程序出错。

（2）定义数据库表（3）定义Dao层接口A、多表连接查询方式注意，此时即使字段名与属性名相同，在<resultMap/>中也要写出它们的映射关系。

因为框架是依据这人<resultMap/>封装对象的。

另外，在映射文件中使用<collection/>标签体现出两个实体对象间的关联关系。

其两个属性的意义为：➢property：指定关联属性，即Country类中的集合属性➢ofType：集合属性的泛型类型B、多表单独查询方式项目：one2many-2，在项目one2many基础上进行修改。

多表连接查询方式是将多张表进行连接，连为一张表后进行查询。

其查询的本质是一张表。

而多表单独查询方式是多张表各自查询各自的相关内容，需要多张表的联合数据了，则将主表的查询结果联合其它表的查询结果，封装为一个对象。

当然，这多个查询是可以跨越多个映射文件的，即是可以跨越多个namespace的。

mybatis实现关联数据的查询mybatis实战教程(mybatis in action)之四:实现关联数据的查询有了前面几章的基础，对一些简单的应用是可以处理的，但在实际项目中，经常是关联表的查询，比如最常见到的多对一，一对多等。

这些查询是如何处理的呢，这一讲就讲这个问题。

我们首先创建一个Article 这个表，并初始化数据. 程序代码Drop TABLE IF EXISTS `article`;Create TABLE`article` ( `id` int(11) NOT NULL auto_increment,`userid` int(11) NOT NULL, `title` varchar(100) NOT NULL, `content` text NOT NULL, PRIMARY KEY(`id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;-- ------------------------------ 添加几条测试数据-- ----------------------------Insert INTO `article` VALUES ('1', '1', 'test_title', 'test_content');Insert INTO `article` VALUES ('2', '1', 'test_title_2', 'test_content_2');Insert INTO `article` VALUES ('3', '1', 'test_title_3', 'test_content_3');Insert INTO `article` VALUES ('4', '1', 'test_title_4', 'test_content_4');你应该发现了，这几个文章对应的userid都是1，所以需要用户表user里面有id=1的数据。

iBatis/MyBatis 主子表关联查询主表：MASTER字段：ORDER_ID--主键RELATE_ID --申请单位STOCK_ADDRESS --仓库地址TERMINAL_VENDER --供应商PROVINCE_ID --省分IDSTATE --状态子表：DETAIL字段：ORDER_ID--与主表ORDER_ID关联PROPERTY_CODE --属性编码SALE_PRICE --价格TERMINAL_VENDER --供应商与主表TERMINAL_VENDER关联PROVINCE_ID --省分ID 与主表PROVINCE_ID关联主键为ORDER_ID + PROPERTY_CODE要求，取得主表：MASTER 中STATE为1的记录，并映射成易于操作的java对象。

并关联子表，ORDER_ID、TERMINAL_VENDER、PROVINCE_ID作为查询子表的条件。

将查询出的子表数据映射成List<Object> ，作为主表映射成对象的一个成员变量。

以便后续操作。

定义java对象Master，对应主表数据：1package com.test.ibatis.po;23import java.util.List;45/**6 * 主表对应数据7 */8public class Master implements java.io.Serializable {9private static final long serialVersionUID = 1L;10/** ID */11private String channelsId = null;12/** 地址 */13private String deliveryLoc = null;1415/** 对应子表数据 */16private List<Detail> details = null;1718public String getChannelsId() {19return channelsId;20 }2122public void setChannelsId(String channelsId) { 23this.channelsId = channelsId;24 }2526public String getDeliveryLoc() {27if (deliveryLoc == null)28return "";29return deliveryLoc;30 }3132public void setDeliveryLoc(String deliveryLoc) { 33this.deliveryLoc = deliveryLoc;34 }3536public List<Detail> getDetails() {37return details;38 }3940public void setDetails(List<Detail> details) {41this.details = details;42 }43}定义Detail类，对应子表数据:1package com.test.ibatis.po;23import java.text.DecimalFormat;45public class Detail implements java.io.Serializable {6private static final long serialVersionUID = 1L;78private static final DecimalFormat df = new DecimalFormat("### 0.00");9/** 产品编号 */10private String partNo = null;11/** 价格 */12private String price = null;1314public String getPartNo() {15return partNo;16 }1718public void setPartNo(String partNo) {19this.partNo = partNo;20 }2122public String getPrice() {23if (price == null)24return "0";25return df.format(Double.parseDouble(price) / 1000.0);26 }2728public void setPrice(String price) {29this.price = price;30 }31}sql如下配置：1<?xml version="1.0" encoding="gbk" ?>23<!DOCTYPE sqlMap4 PUBLIC "-////DTD SQL Map 2.0//EN"5 "/dtd/sql-map-2.dtd">6<sqlMap namespace="TEST_SQL">7<typeAlias alias="HashMap" type="java.util.HashMap"/>89<!-- Master对象定义 -->10<resultMap id="master" class="com.test.ibatis.po.Master">11<result property="channelsId" column="ORDER_CHANNEL"/>12<result property="deliveryLoc" column="DELIVER_ADDRESS"/>13<result property="details" column="{province=PROVINCE_CODE,id=O RDER_ID,VENDER=TERMINAL_VENDER}"14 select="select-dtl"/>15</resultMap>16<!-- Detail对象定义 -->17<resultMap id="detail" class="com.linkage.ess.ordercreate.po.OrderDetail ">18<result property="partNo" column="PROPERTY_CODE"/>19<result property="price" column="SALE_PRICE"/>20</resultMap>21<select id="selectData" resultMap="master">22<!--[CDATA[23 SELECT T.RELATE_ID ORDER_CHANNEL,24 T.STOCK_ADDRESS DELIVER_ADDRESS25 FROM MASTER T26 WHERE T.PROVINCE_ID = #PROVINCE_ID#27 AND T.STATE = '1'28 ]]>29 </select>30 <statement id="select-dtl" resultMap="detail">31 <![CDATA[32 SELECT D.PROPERTY_CODE,33 D.SALE_PRICE,34 FROM DETAIL D35 WHERE D.ORDER_ID = #id#36 AND D.TERMINAL_VENDER = #VENDER#37 AND D.PROVINCE_ID = #province#38 ]]-->39</statement>40</sqlMap>这样所有的工作都OK，执行List<Master> masters = (List<Master>) sqlMap.queryForList("selectData", param);// param 为HashMap，put("PROVINCE_ID", "BJ"); 作为查询条件用。

mybatis关联查询（⼀对多和多对⼀）⽬录mybatis关联查询DBUtil mybatis⼯具类public class DBUtil {private static SqlSessionFactory factory = null;static {try {String mybatis_config = "mybatis-config.xml";InputStream in = Resources.getResourceAsStream(mybatis_config);factory = new SqlSessionFactoryBuilder().build(in);} catch (Exception e) {e.printStackTrace();}}// 获取SqlSessionpublic static SqlSession getSqlSession() {return factory.openSession(true);}// 获取mapperpublic static <T> T getMapper(Class<T> mapper) {return getSqlSession().getMapper(mapper);}}mybatis⼀对多查询resultMap格式:cloumn属性可以是表字段名或者别名<resultMap id="唯⼀的标识" type="映射的pojo对象"><id column="表的主键字段" jdbcType="字段类型" property="映射pojo对象的主键属性" /><result column="表字段名或者别名" jdbcType="字段类型" property="映射到pojo对象的⼀个属性"/> <!--多个标签<result>...--><collection property="pojo的集合属性名" ofType="集合中的pojo对象"><id column="主键字段" jdbcType="字段类型" property="集合中pojo对象的主键属性" /><result column="表字段名或者别名" jdbcType="字段类型" property="集合中的pojo对象的属性" /> <!--多个标签<result>...--></collection></resultMap>案例⼀个⽼师对应多个学⽣, 查询⽼师的时候把⽼师学⽣类:@Data@AllArgsConstructor@NoArgsConstructorpublic class Student {private Integer id;private String name;}⽼师类:@Data@AllArgsConstructor@NoArgsConstructorpublic class Teacher {private Integer id;private String name;List<Student> students;}⽼师dao接⼝public interface TeacherMapper {List<Teacher> findAllTeacher();}⽅试⼀: 案结果嵌套处理（联表查询）TeacherMapper.xml<select id="findAllTeacher" resultMap="teacherStudent">selectt.id as t_id, as t_name,s.id as s_id, as s_namefrom teacher tleft join student s ons.t_id = t.id</select><resultMap id="teacherStudent" type="teacher"><id column="t_id" property="id"/><result column="t_name" property="name"/><collection property="students" ofType="student"><id column="s_id" property="id"/><result column="s_name" property="name"/></collection></resultMap>⽅式⼆: 按查询嵌套处理TeacherMapper.xml<select id="findAllTeacher" resultMap="teacherStudent">select * from teacher;</select><select id="findStudentById" resultType="student">select * from student where t_id = #{id};</select><resultMap id="teacherStudent" type="teacher"><id column="id" property="id"/><result column="name" property="name"/><collection property="students" javaType="ArrayList" ofType="student" select="findStudentById" column="id"/> </resultMap>⽅式⼀⽅式⼆测试均如下:@Testpublic void test1() {TeacherMapper mapper1 = DBUtil.getMapper(TeacherMapper.class);List<Teacher> teachers = mapper1.findAllTeacher();for (Teacher teacher : teachers) {System.out.println(teacher.getName());for (Student student : teacher.getStudents()) {System.out.println("\t" + student);}}}结果:张⽼师Student(id=1, name=张三)Student(id=2, name=李四)Student(id=3, name=王五)Student(id=4, name=赵六)王⽼师Student(id=5, name=李七)Student(id=6, name=冯⼋)mybatis多对⼀查询resultMap格式:<resultMap id="唯⼀的标识" type="映射的pojo对象"><id column="表的主键字段" jdbcType="字段类型" property="映射pojo对象的主键属性" /><result column="表字段名或者别名" jdbcType="字段类型" property="映射到pojo对象的⼀个属性"/> <!--多个标签<result>...--><association property="pojo的集合属性名" javaType="饮⽤配型"><id column="主键字段" jdbcType="字段类型" property="集合中pojo对象的主键属性" /><result column="表字段名或者别名" jdbcType="字段类型" property="集合中的pojo对象的属性" /> <!--多个标签<result>...--></association></resultMap>案例查询学⽣, 并查询学⽣的⽼师学⽣类:@Data@AllArgsConstructor@NoArgsConstructorpublic class Student {private Integer id;private String name;private Teacher teacher;}⽼师类:@Data@AllArgsConstructor@NoArgsConstructorpublic class Teacher {private Integer id;private String name;}学⽣dao层public interface StudentMapper {List<Student> findAllStudent();}⽅式⼀: 案结果嵌套处理（联表查询）StudentMapper.xml<select id="findAllStudent" resultMap="studentTeacher">selects.id as s_id, as s_name,t.id as t_id, as t_namefrom student s left join teacher t ons.t_id = t.id;</select><resultMap id="studentTeacher" type="student"><id column="s_id" property="id"/><result column="s_name" property="name"/><association property="teacher" javaType="teacher"><id column="t_id" property="id"/><result column="t_name" property="name"/></association></resultMap>⽅式⼆: 按查询结果嵌套StudentMapper.xml<select id="findAllStudent" resultMap="studentTeacher">select * from student;</select><select id="findTeacherById" resultType="teacher">select * from teacher where id = #{t_id};</select><resultMap id="studentTeacher" type="student"><id column="id" property="id"/><result column="name" property="name"/><association property="teacher" column="t_id" javaType="teacher" select="findTeacherById"/> </resultMap>⽅式⼀⽅式⼆测试均如下:@Testpublic void test1() {StudentMapper mapper = DBUtil.getMapper(StudentMapper.class);List<Student> students = mapper.findAllStudent();for (Student student : students) {System.out.println(student);}}结果:Student(id=1, name=张三, teacher=Teacher(id=1, name=张⽼师)) Student(id=2, name=李四, teacher=Teacher(id=1, name=张⽼师)) Student(id=3, name=王五, teacher=Teacher(id=1, name=张⽼师)) Student(id=4, name=赵六, teacher=Teacher(id=1, name=张⽼师)) Student(id=5, name=李七, teacher=Teacher(id=2, name=王⽼师)) Student(id=6, name=冯⼋, teacher=Teacher(id=2, name=王⽼师))。

Mybatis中连接查询和嵌套查询实例代码⽬录⼀、连接查询：1、多对⼀：2、⼀对多：3、多对多：⼆、嵌套查询：1、多对⼀：2、⼀对多：⾸先在mysql中确⽴表：#表⼀：地址国家表CREATE TABLE address(aid INT AUTO_INCREMENT PRIMARY KEY,aname VARCHAR(20));INSERT INTO address VALUES(NULL,"魏国");INSERT INTO address VALUES(NULL,"蜀国");INSERT INTO address VALUES(NULL,"吴国");#表⼆：出场⼈物表CREATE TABLE person(pid INT AUTO_INCREMENT PRIMARY KEY,pname VARCHAR(20),paid INT,CONSTRAINT pafk FOREIGN KEY person(paid) REFERENCES address(aid) ON UPDATE CASCADE ON DELETE CASCADE);INSERT INTO person VALUES(1,"曹操",1);INSERT INTO person VALUES(2,"荀彧",1);INSERT INTO person VALUES(3,"张辽",1);INSERT INTO person VALUES(4,"刘备",2);INSERT INTO person VALUES(5,"关⽻",2);INSERT INTO person VALUES(6,"张飞",2);INSERT INTO person VALUES(7,"诸葛亮",2);INSERT INTO person VALUES(8,"孙权",3);INSERT INTO person VALUES(9,"周瑜",3);INSERT INTO person VALUES(10,"陆逊",3);INSERT INTO person VALUES(11,"公孙瓒",NULL);#表三：交通⼯具表CREATE TABLE tool(tid INT AUTO_INCREMENT PRIMARY KEY,tname VARCHAR(20));INSERT INTO tool VALUES(1,"马");INSERT INTO tool VALUES(2,"船");#表四：地址国家——交通⼯具多对多关系表CREATE TABLE aandt(a_aid INT,a_tid INT,PRIMARY KEY(a_aid,a_tid),#联合主键，是指多个字段组成⼀个组合，该组合在数据表中唯⼀CONSTRAINT FOREIGN KEY aandt(a_aid) REFERENCES address(aid) ON UPDATE CASCADE ON DELETE CASCADE,CONSTRAINT FOREIGN KEY aandt(a_tid) REFERENCES tool(tid) ON UPDATE CASCADE ON DELETE CASCADE);INSERT INTO aandt VALUES(1,1);INSERT INTO aandt VALUES(2,1);INSERT INTO aandt VALUES(2,2);INSERT INTO aandt VALUES(3,2);查询a表的所有信息，如果a表的信息有对应的b表的信息，则查询b表的信息，如果没有，则不查询。

Mybatis关联查询（嵌套查询）表的存储sql文件：<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE configuration PUBLIC "-////DTD Config 3.0//EN" "/dtd/mybatis-3-config.dtd"><configuration><!--mybatis别名定义--><typeAliases><typeAlias alias="User" type="er"/><typeAlias alias="Article" type="com.mybatis.test.Article"/><typeAlias alias="Blog" type="com.mybatis.test.Blog"/></typeAliases><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://127.0.0.1:3306/mybatis" /> <property name="username" value="root"/><property name="password" value="admin"/></dataSource></environment></environments><!--mybatis的mapper文件，每个xml配置文件对应一个接口--> <mappers><mapper resource="com/mybatis/test/User.xml"/><mapper resource="com/mybatis/test/Article.xml"/><mapper resource="com/mybatis/test/Blog.xml"/></mappers></configuration>表的存储sql文件：<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN" "/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.mybatis.test.IArticleOperation"><resultMap type="User" id="userResultMap"><!--属性名和数据库列名映射--><id property="id" column="user_id" /><result property="userName" column="user_userName" /><result property="userAge" column="user_userAge" /><result property="userAddress" column="user_userAddress" /></resultMap><!-- User join Article进行联合查询(一对一)--><resultMap id="articleResultMap" type="Article"><id property="id" column="article_id" /><result property="title" column="article_title" /><result property="content" column="article_content" /><!--将article的user属性映射到userResultMap --><association property="user" javaType="User" resultMap="userResultMap"/></resultMap><!--使用别名来映射匹配--><select id="getUserArticles" parameterType="int" resultMap="articleResultMap"> select user.id user_id,erNameuser_userName,erAddressuser_userAddress, article.id article_id,article.titlearticle_title,article.contentarticle_content fromuser,articlewhere user.id=erid and user.id=#{id}</select><!--另一种联合查询(一对一)的实现，但是这种方式有“N+1”的问题--><!--<resultMap id="articleResultMap" type="Article"><id property="id" column="article_id" /><result property="title" column="article_title" /><result property="content" column="article_content" /><association property="user" javaType="User" column="userid" select="selectUser"/> </resultMap><select id="selectUser" parameterType="int" resultType="User">select * from user where id = #{id}</select> --></mapper>Blog.xml配置：<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN""/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.mybatis.test.IBlogOperation"><resultMap id="userResultMap" type="User"><id property="id" column="user_id" /><result property="userName" column="user_userName" /><result property="userAge" column="user_userAge" /><result property="userAddress" column="user_userAddress" /></resultMap><resultMap id="articleResultMap" type="Article"><id property="id" column="article_id" /><result property="title" column="article_title" /><result property="content" column="article_content" /><association property="user" javaType="User" resultMap="userResultMap"/></resultMap><resultMap id="blogResultMap" type="Blog"><id property="id" column="blog_id" /><result property="title" column="blog_title" /><!--将article list属性映射到collection --><collection property="articles" ofType="Article" resultMap="articleResultMap"/></resultMap><!-- select语句--><select id="getBlogByID" parameterType="int" resultMap="blogResultMap">select user.id user_id,erNameuser_userName,erAddressuser_userAddress, article.id article_id,article.titlearticle_title,article.contentarticle_content,blog.id blog_id, blog.titleblog_titlefromuser,article,blogwhere user.id=erid and blog.id=article.blogid and blog.id=#{id}</select></mapper>IArticleOperation定义：packagecom.mybatis.test;importjava.util.List;public interface IArticleOperation {public List<Article>getUserArticles(intuserID);}IBlogOperation定义：packagecom.mybatis.test;public interface IBlogOperation {Blog getBlogByID(int id);}Test类：packagecom.mybatis.test;importjava.io.Reader;importjava.util.List;importorg.apache.ibatis.io.Resources;importorg.apache.ibatis.session.SqlSession;importorg.apache.ibatis.session.SqlSessionFactory; importorg.apache.ibatis.session.SqlSessionFactoryBuilder;public class Test {private static SqlSessionFactorysqlSessionFactory;private static Reader reader;static {try {//通过配置文件初始化sqlSessionFactory reader = Resources.getResourceAsReader("Configuration.xml"); sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);} catch (Exception e) {e.printStackTrace();}}public static SqlSessionFactorygetSession() { returnsqlSessionFactory;}public void getUserByID(intuserID) {SqlSession session = sqlSessionFactory.openSession();try {IUserOperationuserOperation = session.getMapper(IUserOperation.class);User user = userOperation.selectUserByID(userID); if (user != null) {System.out.println(user.getId() + ":" + user.getUserName()+ ":" + user.getUserAddress());}} finally {session.close();}}public void getUserList(String userName) {SqlSession session = sqlSessionFactory.openSession();try {IUserOperationuserOperation = session.getMapper(IUserOperation.class);List<User> users = userOperation.selectUsersByName(userName); for (User user : users) {System.out.println(user.getId() + ":" + user.getUserName()+ ":" + user.getUserAddress());}} finally {session.close();}}/*** 增加后要commit*/public void addUser() {User user = new User();user.setUserAddress("place");user.setUserName("test_add");user.setUserAge(30);SqlSession session = sqlSessionFactory.openSession();try {IUserOperationuserOperation = session.getMapper(IUserOperation.class); userOperation.addUser(user);mit();System.out.println("新增用户ID：" + user.getId());} finally {session.close();}}/*** 修改后要commit*/public void updateUser() {SqlSession session = sqlSessionFactory.openSession();try {IUserOperationuserOperation = session.getMapper(IUserOperation.class);User user = userOperation.selectUserByID(1);if (user != null) {user.setUserAddress("A new place");userOperation.updateUser(user);mit();}} finally {session.close();}}/*** 删除后要commit.** @param id*/public void deleteUser(int id) {SqlSession session = sqlSessionFactory.openSession();try {IUserOperationuserOperation = session.getMapper(IUserOperation.class); userOperation.deleteUser(id);mit();} finally {session.close();}}public void getUserArticles(intuserid) {SqlSession session = sqlSessionFactory.openSession();try {IArticleOperationarticleOperation = session.getMapper(IArticleOperation.class);List<Article> articles = articleOperation.getUserArticles(userid); for (Article article : articles) {System.out.println(article.getTitle() + ":"+ article.getContent() + "用户名："+ article.getUser().getUserName() + "用户地址："+ article.getUser().getUserAddress());}} finally {session.close();}}public void getBlogArticles(intblogid) {SqlSession session = sqlSessionFactory.openSession();try {IBlogOperationblogOperation = session.getMapper(IBlogOperation.class);Blog blog = blogOperation.getBlogByID(blogid);System.out.println(blog.getTitle() + ":");List<Article> articles = blog.getArticles();for (Article article : articles) {System.out.println(article.getTitle() + ":"+ article.getContent() + "用户名："+ article.getUser().getUserName() + "用户地址："+ article.getUser().getUserAddress());/*System.out.println(article.getTitle() + ":"+ article.getContent());*/}} finally {session.close();}}public static void main(String[] args) {try {Test test = new Test();// test.getUserByID(1);// test.getUserList("test1");// test.addUser();// test.updateUser();// test.deleteUser(6);//test.getUserArticles(1);test.getBlogArticles(1);} catch (Exception e) {System.out.println(e.getMessage());}}}Mybatis的“N+1查询问题”<resultMap id=”blogResult” type=”Blog”><association property="author" column="blog_author_id"javaType="Author" select=”selectAuthor” /></resultMap><select id=”selectBlog” parameterType=”int” resultMap=”blogResult”>SELECT * FROM BLOG WHERE ID = #{id}</select><select id=”selectAuthor” parameterType=”int” resultType="Author">SELECT * FROM AUTHOR WHERE ID = #{id}</select>有两个查询语句：一个来加载博客，另外一个来加载作者，而且博客的结果映射描述了“selectAuthor”语句应该被用来加载它的author 属性。

mybatis如何使⽤注解实现⼀对多关联查询mybatis 注解实现⼀对多关联查询@Select("select id,mockexam_section as section,id as sectionId"+ " from t_p_qb_mockexam_section"+ " where mockexam_charpter_id = #{charpterId} and is_delete = 0"+ " order by mockexam_section_idx asc")@Results({@Result(property = "questionList",column = "sectionId",many = @Many(select = "com.zikaoshu.baseinfo.mapper.BaseinfoQuestionMapper.listQuestionResDto"))})List<SectionQuestionDto> listSectionQuestionDto(@Param("charpterId") Integer charpterId);@Select("select id,type,discuss_title as discussTitle,stem1,material,a,b,c,d,e,answer,analysis,mockeaxm_section_id as sectionId"+ " from t_p_qb_question_mockexam"+ " where mockeaxm_section_id = #{id} and is_delete = 0"+ " order by q_sequence,gmt_create asc")List<QuestionResDto> listQuestionResDto(@Param("id") Integer id);mybatis多对多查询(xml⽅式和注解⽅式）前⾯总结了⼀对⼀，多对⼀和⼀对多的多表查询，今天总结⼀下多对多的mybatis多表查询。

MyBatis⽰例-联合查询简介MyBatis 提供了两种联合查询的⽅式，⼀种是嵌套查询，⼀种是嵌套结果。

先说结论：在项⽬中不建议使⽤嵌套查询，会出现性能问题，可以使⽤嵌套结果。

测试类：com.yjw.demo.JointQueryTest，提供了对嵌套查询和嵌套结果的测试。

数据库表模型关系学⽣信息级联模型关系：学⽣信息级联模型关系是⼀个多种类型关联关系，包含了如下⼏种情况：其中学⽣表是我们关注的中⼼，学⽣证表和它是⼀对⼀的关联关系；⽽学⽣表和课程成绩表是⼀对多的关系，⼀个学⽣可能有多门课程；课程表和课程成绩表也是⼀对多的关系；学⽣有男有⼥，⽽健康项⽬也有所不⼀，所以⼥性学⽣和男性学⽣的健康表也会有所不同，这些是根据学⽣的性别来决定的，⽽鉴别学⽣性别的就是鉴别器。

关联关系在联合查询中存在如下⼏种对应关系：⼀对⼀的关系；⼀对多的关系；多对多的关系，实际使⽤过程中是把多对多的关系分解为两个⼀对多的关系，以降低关系的复杂度；还有⼀种是鉴别关系，⽐如我们去体检，男⼥有别，男性和⼥性的体检项⽬并不完全⼀样；所以在 MyBatis 中联合分为这么3种：association、collection 和 discriminator。

association：代表⼀对⼀关系；collection：代表⼀对多关系；discriminator：代表鉴别器，它可以根据实际选择采⽤哪种类作为实例，允许你根据特定的条件去关联不同的结果集；嵌套查询（不建议使⽤）⼀对⼀关系以学⽣表作为关注的中⼼，学⽣表和学⽣证表是⼀对⼀的关系。

POJO 对象和映射⽂件的实现如下：StudentDOpublic class StudentDO {private Long id;private String name;private Sex sex;private Long selfcardNo;private String note;private StudentSelfcardDO studentSelfcard;// get set ⽅法}StudentMapper.xml<!-- 联合查询：嵌套查询 --><resultMap id="studentMap1" type="studentDO"><id column="id" jdbcType="BIGINT" property="id" /><result column="name" jdbcType="VARCHAR" property="name" /><result column="sex" jdbcType="TINYINT" property="sex"typeHandler="mon.type.SexEnumTypeHandler"/><result column="selfcard_no" jdbcType="BIGINT" property="selfcardNo" /><result column="note" jdbcType="VARCHAR" property="note" /><!-- 嵌套查询：⼀对⼀级联 --><association property="studentSelfcard" column="{studentId=id}"select=".dao.StudentSelfcardDao.listByConditions" /></resultMap>⼀对⼀的关系建⽴通过 <association> 元素实现，该元素中的属性描述如下所⽰：property：JavaBean 中对应的属性字段；column：数据库的列名或者列标签别名。

Mybatis联合查询的实现⽅法⽬录1、级联属性封装结果集实现2、分步查询⽅法3、级联属性封装结果集4、分步查询数据库表结构departmentemployee要求⼀现在的要求是输⼊ id 把 employee 表的对应员⼯数据查询出来，并且查询出该员⼯的所处部门信息public class Employee {private Integer id;private String lastName;private String email;private String gender;private Department dept;setter和getter.......}public class Department {private Integer id;private String departmentName;setter和getter.......}1、级联属性封装结果集实现这个要求很明显就要⽤到两个表，想要把部门信息封装到Employee对象的dept字段需要⽤到resultMap属性⽅法⼀<!-- public Employee getEmployee(int id); --><select id="getEmployee" resultMap="emp1">select e.*, d.id did, d.department_namefrom employee e,department dwhere e.d_id = d.idand e.id = #{id}</select><resultMap id="emp1" type="employee"><id column="id" property="id"/><result column="last_name" property="lastName"/><result column="email" property="email"/><result column="gender" property="gender"/><result column="did" property="dept.id"/><result column="department_name" property="dept.departmentName"/></resultMap>⽅法⼆<!-- public Employee getEmployee(int id); --><select id="getEmployee" resultMap="emp2">select e.*, d.id did, d.department_namefrom employee e,department dwhere e.d_id = d.idand e.id = #{id}</select><resultMap id="emp2" type="employee"><id column="id" property="id"/><result column="last_name" property="lastName"/><result column="email" property="email"/><result column="gender" property="gender"/><association property="dept" javaType="department"><id column="did" property="id"/><result column="department_name" property="departmentName"/></association></resultMap>测试@Testpublic void test1() {SqlSession sqlSession = MyTest.getSqlSession();EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);System.out.println(mapper.getEmployee(1));}结果2、分步查询⽅法DepartmentMapper.xml<!-- public Department getDepartment2(int id); --><select id="getDepartment2" resultType="department">select * from department where id = #{id}</select>EmployeeMaper.xml<!-- public Employee getEmployee2(int id); --><!-- 分步查询 --><select id="getEmployee2" resultMap="emp3">select * from employee where id = #{id}</select><resultMap id="emp3" type="employee"><id column="id" property="id"/><result column="last_name" property="lastName"/><result column="email" property="email"/><result column="gender" property="gender"/><association property="dept" select="com.workhah.mapper.department.DepartmentMapper.getDepartment2" column="d_id"/> </resultMap>测试@Testpublic void test1() {SqlSession sqlSession = MyTest.getSqlSession();EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);System.out.println(mapper.getEmployee2(1));}结果要求⼆现在的要求是输⼊ id 把 department 表对应的部门信息查询出来，并且查询该部门下的所有员⼯信息public class Employee {private Integer id;private String lastName;private String email;private String gender;setter和getter.......}public class Department {private Integer id;private String departmentName;private List<Employee> employees;setter和getter.......}3、级联属性封装结果集⽅法<!-- public Department getDepartment(int id); --><select id="getDepartment" resultMap="dep1">select d.*, e.id eid, st_name, e.email, e.genderfrom department dleft join employee e on d.id = e.d_idwhere d.id = #{id}</select><resultMap id="dep1" type="department"><id column="id" property="id"/><result column="department_name" property="departmentName"/><collection property="employees" ofType="employee"><id column="eid" property="id"/><result column="last_name" property="lastName"/><result column="email" property="email"/><result column="gender" property="gender"/></collection></resultMap>测试@Testpublic void test2() {SqlSession sqlSession = MyTest.getSqlSession();DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);System.out.println(mapper.getDepartment(1));}结果4、分步查询EmployeeMaper.xml<!-- public List<Employee> getEmployeeByDid(int did); --><select id="getEmployeeByDid" resultType="employee">select *from employeewhere d_id = #{did}</select>DepartmentMapper.xml<!-- public Department getDepartment3(int id); --><select id="getDepartment3" resultMap="dep2">select *from departmentwhere id = #{id}</select><resultMap id="dep2" type="department"><id column="id" property="id"/><result column="depart_name" property="departName"/><collection property="employees" ofType="employee"select="com.workhah.mapper.employee.EmployeeMapper.getEmployeeByDid" column="id"/></resultMap>测试@Testpublic void test2() {SqlSession sqlSession = MyTest.getSqlSession();DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);System.out.println(mapper.getDepartment3(1));}结果到此这篇关于 Mybatis联合查询的实现⽅法的⽂章就介绍到这了,更多相关 Mybatis联合查询内容请搜索以前的⽂章或继续浏览下⾯的相关⽂章希望⼤家以后多多⽀持！。

Mybatis笔记--多表关联插⼊、更新、查询实例在DeviceModel添加需要关联的实体 DeviceModelAttrprivate List<DeviceModelAttr> deviceModelAttr;DeviceModelMapper.xml 中配置关联实体 DeviceModelAttr<collection property="deviceModelAttr" ofType="com.industry.txsp.entity.DeviceModelAttr"><id property="modelAttrId" column="model_attr_id"/><result property="dmAttrType" column="dm_attr_type"/><result property="attrValue" column="attr_value"/><result property="modelId" column="model_id"/></collection>在DeviceModelServiceImpl 中实现，关联插⼊，关联查询，关联更新@Servicepublic class DeviceModelServiceImpl implements DeviceModelService {private static final Logger logger = LoggerFactory.getLogger(DeviceModelService.class);@Autowiredprivate DeviceModelMapper deviceModelMapper;@Autowiredprivate DeviceModelAttrMapper deviceModelAttrMapper;@Overridepublic PageInfo<DeviceModel> query(DeviceModel deviceModel, Integer pageSize, Integer pageNum) {if (pageNum == null || pageNum.equals("")) {pageNum = 1;}if (pageSize == null || pageSize.equals("")) {pageSize = 10;}//开启分页PageHelper.startPage(pageNum, pageSize);List<DeviceModel> list = deviceModelMapper.query(deviceModel);("queryDeviceModel=> effect line count is " + list.size());return new PageInfo<>(list);}@Overridepublic DeviceModel findById(String modelId) {return deviceModelMapper.findById(modelId);}@Overridepublic int create(DeviceModel deviceModel) {IUser user = AdminSecurityUtils.getSecurityUser();String uuid = UuidUtil.getRandomUUID();deviceModel.setModelId(uuid);deviceModel.setCreator(user.getName());deviceModel.setCreateId(user.getId());deviceModel.setCreateTime(new Date());deviceModel.setUpdateTime(new Date());deviceModel.setIsDeleted(0);for (DeviceModelAttr deviceModelAttr : deviceModel.getDeviceModelAttr()) {deviceModelAttr.setModelId(uuid);deviceModelAttr.setModelAttrId(UuidUtil.getRandomUUID());deviceModelAttrMapper.create(deviceModelAttr);}return deviceModelMapper.create(deviceModel);}@Overridepublic int update(DeviceModel deviceModel) {for(DeviceModelAttr deviceModelAttr : deviceModel.getDeviceModelAttr()){deviceModelAttrMapper.update(deviceModelAttr);}return deviceModelMapper.update(deviceModel);}完整DeviceModelMapper.xml<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN" "/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.industry.txsp.mapper.DeviceModelMapper"><!-- 可根据⾃⼰的需求，是否要使⽤ --><resultMap type="com.industry.txsp.entity.DeviceModel" id="deviceModelMap"><result property="modelId" column="model_id"/><result property="modelName" column="model_name"/><result property="createId" column="create_id"/><result property="creator" column="creator"/><result property="createTime" column="create_time"/><result property="updateTime" column="update_time"/><result property="isDeleted" column="is_deleted"/><collection property="deviceModelAttr" ofType="com.industry.txsp.entity.DeviceModelAttr"><id property="modelAttrId" column="model_attr_id"/><result property="dmAttrType" column="dm_attr_type"/><result property="attrValue" column="attr_value"/><result property="modelId" column="model_id"/></collection></resultMap><select id="query" resultMap="deviceModelMap">SELECT*FROM device_model dm,device_model_attr dma<where><if test="deviceModel.modelId !=null and deviceModel.modelId !=''">AND model_id = #{deviceModel.modelId}</if><if test="deviceModel.modelName !=null and deviceModel.modelName !=''">AND model_name = #{deviceModel.modelName}</if><if test="deviceModel.createId !=null and deviceModel.createId !=''">AND create_id = #{deviceModel.createId}</if><if test="deviceModel.creator !=null and deviceModel.creator !=''">AND creator = #{deviceModel.creator}</if><if test="deviceModel.createTime !=null and deviceModel.createTime !=''">AND create_time = #{deviceModel.createTime}</if><if test="deviceModel.updateTime !=null and deviceModel.updateTime !=''">AND update_time = #{deviceModel.updateTime}</if><if test="deviceModel.isDeleted !=1">AND is_deleted = 0</if>AND dm.model_id = dma.model_id</where></select><select id="findById" resultMap="deviceModelMap">SELECT*FROM device_model dm,device_model_attr dmaWHERE dm.model_id = #{modelId} and dm.is_deleted =0 and dm.model_id = dma.model_id </select><!--增加--><insert id="create" parameterType="com.bonc.industry.txsp.entity.DeviceModel">INSERT INTO device_model(model_id,model_name,create_id,creator,create_time,update_time,is_deleted)VALUES(#{modelId},#{modelName},#{createId},#{creator},#{createTime},#{updateTime},#{isDeleted}) </insert><!--更新--><update id="update" parameterType="com.bonc.industry.txsp.entity.DeviceModel">UPDATE device_modelSET<if test="modelId !=null and modelId !=''">model_id = #{modelId}</if><if test="modelName !=null and modelName !=''">,model_name = #{modelName}</if><if test="createId !=null and createId !=''">,create_id = #{createId}</if><if test="creator !=null and creator !=''">,creator = #{creator}</if><if test="createTime !=null">,create_time = #{createTime,jdbcType=TIMESTAMP}</if><if test="updateTime !=null">,update_time = #{updateTime,jdbcType=TIMESTAMP} </if><if test="isDeleted !=null and isDeleted !=''">,is_deleted = #{isDeleted}</if>WHERE model_id = #{modelId} </update> </mapper>。

mybatis⼀对多和多对⼀关联查询⾸先数据库量表之间字段关系（没有主外键）studentmajor表的id字段对应student表⾥major字段两个实体类package com.model;import java.util.Date;public class Student {private Integer sno;private String sname;private String ssex;private Integer sclass;private StudentMajor studentmajor;public Student() {super();}public Student(Integer sno, String sname, String ssex, Integer sclass, StudentMajor studentmajor) {super();this.sno = sno;this.sname = sname;this.ssex = ssex;this.sclass = sclass;this.studentmajor = studentmajor;}public StudentMajor getStudentmajor() {return studentmajor;}public void setStudentmajor(StudentMajor studentmajor) {this.studentmajor = studentmajor;}public Integer getSno() {return sno;}public void setSno(Integer sno) {this.sno = sno;}public String getSname() {return sname;}public void setSname(String sname) {this.sname = sname;}public String getSsex() {return ssex;}public void setSsex(String ssex) {this.ssex = ssex;}@Overridepublic String toString() {return "Student [sno=" + sno + ", sname=" + sname + ", ssex=" + ssex + ", sclass=" + sclass + ", studentmajor="+ studentmajor + "]";}public Integer getSclass() {return sclass;}public void setSclass(Integer sclass) {this.sclass = sclass;}}package com.model;import java.util.List;public class StudentMajor {private Integer id;private String mcode;private String mname;private List<Student> students;public StudentMajor() {super();}public StudentMajor(Integer id, String mcode, String mname, List<Student> students) {super();this.id = id;this.mcode = mcode;this.mname = mname;this.students = students;}@Overridepublic String toString() {return "StudentMajor [id=" + id + ", mcode=" + mcode + ", mname=" + mname + ", students=" + students + "]"; }public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getMcode() {return mcode;}public void setMcode(String mcode) {this.mcode = mcode;}public String getMname() {return mname;}public void setMname(String mname) {this.mname = mname;}public List<Student> getStudents() {return students;}public void setStudents(List<Student> students) {this.students = students;}}定义两个接⼝package com.dao;import java.util.List;import java.util.Map;import com.model.Student;public interface StudentMapper {/*** 全表查询*/public List<Student> selectall(); /*** 根据专业查⼈员,给⼀对多⽤*/public List<Student> selectz(Integer major);}package com.dao;import java.util.List;import com.model.StudentMajor;public interface StudentMajorMapper {/*** 全表查询* @return*/public List<StudentMajor> selectAll();/*** 根据主键查数据,给多对⼀⽤* @param id* @return*/public StudentMajor select(Integer id);}定义两个实体类的映射⽅法<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapperPUBLIC "-////DTD Mapper 3.0//EN""/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.dao.StudentMapper"><!-- 多对⼀查询 --><resultMap type="Student" id="slist"><!-- 跟⼀对⼀⼀样⽤association标签,实体类定义的成员,要跟数据库字段名对应上 --><association property="studentmajor" column="major"select="com.dao.StudentMajorMapper.select"/><!-- ⽤接⼝⾥定义的⽅法，根据student表中的major字段查出对应数据 --> </resultMap><!-- 查全部 --><select id="selectall" resultMap="slist">select * from student</select><!-- 根据专业查⼈员 --><select id="selectz" parameterType="Integer" resultType="student">select * from student s where s.major=#{major}</select></mapper><?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapperPUBLIC "-////DTD Mapper 3.0//EN""/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.dao.StudentMajorMapper"><!-- ⼀对多查询关联 --><resultMap type="StudentMajor" id="slist"><!-- 实体类属性对应数据库的主键字段，不然主键会查不到 --><id property="id" column="id"/><!-- ⽤collection标签 ,也是实体类属性要对应数据库字段--><collection property="students" column="id"select="com.dao.StudentMapper.selectz"></collection></resultMap><!-- 全表查询 --><select id="selectAll" resultMap="slist">select * from studentmajor</select><!-- 根据主键查 --><select id="select" parameterType="Integer" resultType="StudentMajor">select * from studentmajor where id=#{id}</select></mapper>JUnit测试package com.util;import java.util.List;import org.apache.ibatis.session.SqlSession;import org.junit.After;import org.junit.Before;import org.junit.Test;import com.dao.StudentMajorMapper;import com.dao.StudentMapper;import com.model.Student;import com.model.StudentMajor;public class JJJtest {private SqlSession ss;private StudentMapper sm;private StudentMajorMapper smm;@Beforepublic void setUp() throws Exception {ss=SqlSessionUtil.getSqlSession();sm=ss.getMapper(StudentMapper.class);smm=ss.getMapper(StudentMajorMapper.class);}@Afterpublic void tearDown() throws Exception {mit();ss.close();}//⼀对多查询public void test() {List<StudentMajor> list=smm.selectAll();for(StudentMajor a:list){System.out.println(a);}}//根据专业查⼈员，给⼀对多⽤public void selectz(){List<Student> l=sm.selectz(3);for(Student a:l){System.out.println(a);}}//多对⼀查询@Testpublic void selectall() {List<Student> st=sm.selectall();for(Student tt:st){System.out.println(tt);}}//根据主键查询，给多对⼀⽤public void select(){StudentMajor a=smm.select(1);System.out.println(a);}}⼀对多查询结果多对⼀查询结果。

mybatis的关联查询以及count1.多表查询，1对1的时候，最简单的做法<resultMap id="postInfo" type="postInfoEntity"><id property="postId" column="post_id"/><result property="userName" column="user_name"/><result property="postTitle" column="post_title"/><result property="postContent" column="post_content"/><result property="topPost" column="top_post"/><result property="wonderfulPost" column="wonderful_post"/><result property="createTime" column="create_time"/><result property="count" column="num"/></resultMap><!--某个贴吧所有界⾯的帖⼦，通过置顶降序排序--><select id="listAllPostInfos" resultMap="postInfo">SELECT A.post_id, COUNT(*) num, post_title, post_content, top_post, wonderful_post, user_name, A.create_timeFROM post_info A, nf_user B, reply_post_info CWHERE post_status = 1 AND C.reply_status = 1AND post_bar_id = #{param1}AND er_id = er_idAND A.post_id = C.post_idGROUP BY post_idORDER BY top_post DESC, create_time</select>其中COUNT(*) 取了⼀个别名，⽬的是为了对应resultMap中的<result property="count" column="num"/>, 然⽽仅仅这样是不够的，因为虽然查得到，但是mybatis映射不出来，他底层的反射和动态代理还需要我们在实体类进⾏设置——我们加⼀个属性和property对应就可以了@Data@Document(indexName = "post_info")public class PostInfoEntity {@Idprivate Long postId;@Field(type = FieldType.Long)private Long postBarId;@Field(type = FieldType.Text, analyzer = "ik_max_word")private String postTitle;@Field(type = FieldType.Text, analyzer = "ik_max_word")private String postContent;@Field(type = FieldType.Long)private Long userId;@Field(type = FieldType.Long)private Long topPost;@Field(type = FieldType.Long)private Long wonderfulPost;@Field(type = FieldType.Integer)private Integer audit;@Field(type = FieldType.Long)private Long visitCount;@Field(type = FieldType.Integer)private Integer postStatus;@Field(type = FieldType.Date)private Date createTime;private Integer count;private String userName;}// 除了@Data注解其他都⽆视，其他的是elasticsearch的2.多表联合查询，可以像我们上⾯⼀样，和COUNT(*)同⼀种写法，直接写⼀个<result>标签然后实体类加⼀个属性对应。

mybatis之注解式开发之关联查询 1package com.bjsxt.mapper;23import org.apache.ibatis.annotations.Select;45import com.bjsxt.pojo.Clazz;67public interface ClazzMapper {89 @Select("select * from t_class where id=#{0}")10 Clazz selById(int id);11 }1package com.bjsxt.mapper;23import java.util.List;45import org.apache.ibatis.annotations.One;6import org.apache.ibatis.annotations.Result;7import org.apache.ibatis.annotations.Results;8import org.apache.ibatis.annotations.Select;910import com.bjsxt.pojo.Student;1112public interface StudentMapper {1314 @Select("select * from t_student")15 @Results(value = {16 @Result(column="id", property="id", id=true),17 @Result(column="name", property="name"),18 @Result(column="age", property="age"),19 @Result(column="gender", property="gender"),20 @Result(column="cid", property="cid"),21 @Result(property="clazz", one=@One(select="com.bjsxt.mapper.ClazzMapper.selById"), column="cid")22 })23 List<Student> sel();24 }1package com.bjsxt.pojo;23import java.io.Serializable;45public class Clazz implements Serializable {67private int id;8private String name;9private String room;1011public Clazz() {12super();13 }1415public int getId() {16return id;17 }1819public void setId(int id) {20this.id = id;2223public String getName() {24return name;25 }2627public void setName(String name) { = name;29 }3031public String getRoom() {32return room;33 }3435public void setRoom(String room) {36this.room = room;37 }3839 @Override40public int hashCode() {41final int prime = 31;42int result = 1;43 result = prime * result + id;44 result = prime * result + ((name == null) ? 0 : name.hashCode());45 result = prime * result + ((room == null) ? 0 : room.hashCode());46return result;47 }4849 @Override50public boolean equals(Object obj) {51if (this == obj)52return true;53if (obj == null)54return false;55if (getClass() != obj.getClass())56return false;57 Clazz other = (Clazz) obj;58if (id != other.id)59return false;60if (name == null) {61if ( != null)62return false;63 } else if (!name.equals())64return false;65if (room == null) {66if (other.room != null)67return false;68 } else if (!room.equals(other.room))69return false;70return true;71 }7273 @Override74public String toString() {75return "Clazz [id=" + id + ", name=" + name + ", room=" + room + "]";76 }77 }1package com.bjsxt.pojo;23import java.io.Serializable;45public class Student implements Serializable {7private int id;8private String name;9private int age;10private String gender;11private int cid;12private Clazz clazz;1314public Student() {15super();16 }1718public int getId() {19return id;20 }2122public void setId(int id) {23this.id = id;24 }2526public String getName() {27return name;28 }2930public void setName(String name) { = name;32 }3334public int getAge() {35return age;36 }3738public void setAge(int age) {39this.age = age;40 }4142public String getGender() {43return gender;44 }4546public void setGender(String gender) { 47this.gender = gender;48 }4950public int getCid() {51return cid;52 }5354public void setCid(int cid) {55this.cid = cid;56 }5758public Clazz getClazz() {59return clazz;60 }6162public void setClazz(Clazz clazz) {63this.clazz = clazz;64 }6566 @Override67public int hashCode() {68final int prime = 31;69int result = 1;70 result = prime * result + age;71 result = prime * result + cid;72 result = prime * result + ((clazz == null) ? 0 : clazz.hashCode());73 result = prime * result + ((gender == null) ? 0 : gender.hashCode());74 result = prime * result + id;75 result = prime * result + ((name == null) ? 0 : name.hashCode());76return result;77 }7879 @Override80public boolean equals(Object obj) {81if (this == obj)82return true;83if (obj == null)84return false;85if (getClass() != obj.getClass())86return false;87 Student other = (Student) obj;88if (age != other.age)89return false;90if (cid != other.cid)91return false;92if (clazz == null) {93if (other.clazz != null)94return false;95 } else if (!clazz.equals(other.clazz))96return false;97if (gender == null) {98if (other.gender != null)99return false;100 } else if (!gender.equals(other.gender))101return false;102if (id != other.id)103return false;104if (name == null) {105if ( != null)106return false;107 } else if (!name.equals())108return false;109return true;110 }111112 @Override113public String toString() {114return "Student [id=" + id + ", name=" + name + ", age=" + age + ", gender=" + gender + ", cid=" + cid 115 + ", clazz=" + clazz + "]";116 }117 }1package com.bjsxt.test;23import java.util.List;45import org.apache.ibatis.session.SqlSession;67import com.bjsxt.mapper.StudentMapper;8import com.bjsxt.pojo.Student;9import com.bjsxt.util.MyBatisUtil;1011public class TestStu {1213public static void main(String[] args) {14 SqlSession session = MyBatisUtil.getSession();1516 StudentMapper mapper = session.getMapper(StudentMapper.class);1718 List<Student> sel = mapper.sel();19for (Student student : sel) {20 System.out.println(student);21 }2223 session.close();24 }2526 }1package com.bjsxt.util;23import java.io.IOException;4import java.io.InputStream;56import org.apache.ibatis.io.Resources;7import org.apache.ibatis.session.SqlSession;8import org.apache.ibatis.session.SqlSessionFactory;9import org.apache.ibatis.session.SqlSessionFactoryBuilder;1011public class MyBatisUtil {1213private static SqlSessionFactory factory = null;1415static {16try {17 InputStream is = Resources.getResourceAsStream("mybatis-cfg.xml");18 factory = new SqlSessionFactoryBuilder().build(is);19 } catch (IOException e) {20 e.printStackTrace();21 }22 }2324public static SqlSession getSession() {25 SqlSession session = null;26if (factory != null) {27// true表⽰开启⾃动提交28// session = factory.openSession(true);29 session = factory.openSession();30 }31return session;32 }33 }1 jdbc.driver=com.mysql.jdbc.Driver2 jdbc.url=jdbc:mysql://localhost:3306/java5053 ername=root4 jdbc.password=root# Set root category priority to INFO and its only appender to CONSOLE.log4j.rootCategory=ERROR, CONSOLE# log4j.rootCategory=DEBUG, CONSOLE, LOGFILE# 单独设置SQL语句的输出级别为DEBUG级别.bjsxt.mapper=DEBUG# CONSOLE is set to be a ConsoleAppender using a PatternLayout.log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppenderyout=org.apache.log4j.PatternLayoutyout.ConversionPattern=- %m%n# LOGFILE is set to be a File appender using a PatternLayout.log4j.appender.LOGFILE=org.apache.log4j.FileAppenderlog4j.appender.LOGFILE.File=d:/test.loglog4j.appender.LOGFILE.Append=trueyout=org.apache.log4j.PatternLayoutyout.ConversionPattern=- %m %l%n1 <?xml version="1.0" encoding="UTF-8"?>2 <!DOCTYPE configuration3 PUBLIC "-////DTD Config 3.0//EN"4 "/dtd/mybatis-3-config.dtd">5 <configuration>6 <!-- properties加载外部⽂件 -->7 <properties resource="db.properties" />8 <!-- settings标签 -->9 <settings>10 <!-- 设置MyBatis使⽤log4j⽇志⽀持 -->11 <setting name="logImpl" value="LOG4J"/>12 </settings>13 <!-- typeAliases给类型起别名 -->14 <typeAliases>15 <package name="com.bjsxt.pojo" />16 </typeAliases>17 <environments default="dev">18 <environment id="dev">19 <transactionManager type="JDBC" />20 <dataSource type="POOLED">21 <property name="driver" value="${jdbc.driver}"/>22 <property name="url" value="${jdbc.url}"/>23 <property name="username" value="${ername}"/>24 <property name="password" value="${jdbc.password}"/>25 </dataSource>26 </environment>27 </environments>28 <mappers>29 <package name="com.bjsxt.mapper" />30 </mappers>31 </configuration>。

Mybatis多表关联查询的实现（DEMO）概要本节要实现的是多表关联查询的简单demo。

场景是根据id查询某商品分类信息，并展⽰该分类下的商品列表。

⼀、Mysql测试数据新建表Category(商品分类)和Product（商品），并插⼊⼏条测试数据。

create table Category (Id int not null auto_increment,Name varchar(80) null,constraint pk_category primary key (Id));INSERT INTO category(Name) VALUES ('⼥装');INSERT INTO category(Name) VALUES ('美妆');INSERT INTO category(Name) VALUES ('书籍');create table product (Id int not null auto_increment,categoryId int not null,Name varchar(80) null,constraint pk_product primary key (Id),constraint fk_product_2 foreign key (categoryId)references category (Id));create index productCat on product (categoryId);create index productName on product (Name);INSERT INTO product(CategoryId,Name) VALUES (1, '裂帛');INSERT INTO product(CategoryId,Name) VALUES (1, '雅⿅');INSERT INTO product(CategoryId,Name) VALUES (2,'膜法世家');INSERT INTO product(CategoryId,Name) VALUES (2,'御泥坊');INSERT INTO product(CategoryId,Name) VALUES (2, '雅诗兰黛');INSERT INTO product(CategoryId,Name) VALUES (2, '欧莱雅');INSERT INTO product(CategoryId,Name) VALUES (2, '韩后');INSERT INTO product(CategoryId,Name) VALUES (2, '相宜本草');INSERT INTO product(CategoryId,Name) VALUES (3,'疯狂JAVA');INSERT INTO product(CategoryId,Name) VALUES (3,'JAVA核⼼技术');⼆、配置mybatis-generator-config.xml配置mybatis-generator-config.xml的⽅法见 JAVA⼊门[7]-Mybatis generator(MBG)⾃动⽣成mybatis代码，这⾥主要改动的是table节点。

MyBatis联表查询MyBatis逆向⼯程主要⽤于单表操作，那么需要进⾏联表操作时，往往需要我们⾃⼰去写sql语句。

写sql语句之前，我们先修改⼀下实体类Course.java：1public class Course {2private Integer id;34private String cNum;56private String cName;78private String remark;910private Integer status;1112public Integer getId() {13return id;14 }1516public void setId(Integer id) {17this.id = id;18 }1920public String getcNum() {21return cNum;22 }2324public void setcNum(String cNum) {um = cNum == null ? null : cNum.trim();26 }2728public String getcName() {29return cName;30 }3132public void setcName(String cName) {ame = cName == null ? null : cName.trim();34 }3536public String getRemark() {37return remark;38 }3940public void setRemark(String remark) {41this.remark = remark == null ? null : remark.trim();42 }4344public Integer getStatus() {45return status;46 }4748public void setStatus(Integer status) {49this.status = status;50 }5152 @Override53public String toString() {54return "Course{" +55 "id=" + id +56 ", cNum='" + cNum + '\'' +57 ", cName='" + cName + '\'' +58 ", remark='" + remark + '\'' +59 ", status=" + status +60 '}';61 }62 }Task.java：1import java.util.Date;23public class Task {4private Integer id;56private String cid;78private Integer uid;910private String filename;1112private String fileUrl;1314private Date created;1516private Date updated;1718private String remark;1920private Integer status;2122//⾃定义23private Course course;//联表查询使⽤2425public Integer getId() {26return id;27 }2829public void setId(Integer id) {30this.id = id;31 }3233public String getCid() {34return cid;35 }3637public void setCid(String cid) {38this.cid = cid == null ? null : cid.trim();39 }4041public Integer getUid() {42return uid;43 }4445public void setUid(Integer uid) {46this.uid = uid;47 }4849public String getFilename() {50return filename;51 }5253public void setFilename(String filename) {54this.filename = filename == null ? null : filename.trim();55 }5657public String getFileUrl() {58return fileUrl;59 }6061public void setFileUrl(String fileUrl) {62this.fileUrl = fileUrl == null ? null : fileUrl.trim();63 }6465public Date getCreated() {66return created;67 }6869public void setCreated(Date created) {70this.created = created;71 }7273public Date getUpdated() {74return updated;75 }7677public void setUpdated(Date updated) {78this.updated = updated;79 }8081public String getRemark() {82return remark;83 }8485public void setRemark(String remark) {86this.remark = remark == null ? null : remark.trim();87 }8889public Integer getStatus() {90return status;91 }9293public void setStatus(Integer status) {94this.status = status;95 }9697//⾃定义98public Course getCourse() {99return course;100 }101102public void setCourse(Course course) {103this.course = course;104 }105106 @Override107public String toString() {108return "Task{" +109 "id=" + id +110 ", cid='" + cid + '\'' +111 ", uid=" + uid +112 ", filename='" + filename + '\'' +113 ", fileUrl='" + fileUrl + '\'' +114 ", created=" + created +115 ", updated=" + updated +116 ", remark='" + remark + '\'' +117 ", status=" + status +118 ", course=" + course +119 '}';120 }121 }TaskMapper.java：1import com.sun123.springboot.entity.Task;2import com.sun123.springboot.entity.TaskExample;3import org.apache.ibatis.annotations.Param;45import java.util.List;67public interface TaskMapper {8int countByExample(TaskExample example);910int deleteByExample(TaskExample example);1112int deleteByPrimaryKey(Integer id);1314int insert(Task record);1516int insertSelective(Task record);1718 List<Task> selectByExample(TaskExample example);1920 Task selectByPrimaryKey(Integer id);2122int updateByExampleSelective(@Param("record") Task record, @Param("example") TaskExample example);2324int updateByExample(@Param("record") Task record, @Param("example") TaskExample example);2526int updateByPrimaryKeySelective(Task record);2728int updateByPrimaryKey(Task record);2930 List<Task> taskList();//联表查询31 }TaskMapper.xml：（MyBatis逆向⼯程的基础上进⾏修改）1<?xml version="1.0" encoding="UTF-8" ?>2<!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN" "/dtd/mybatis-3-mapper.dtd" > 3<mapper namespace="com.sun123.springboot.mapper.TaskMapper">4<resultMap id="BaseResultMap" type="com.sun123.springboot.entity.Task">5<id column="id" property="id" jdbcType="INTEGER"/>6<result column="cid" property="cid" jdbcType="VARCHAR"/>7<result column="uid" property="uid" jdbcType="INTEGER"/>8<result column="filename" property="filename" jdbcType="VARCHAR"/>9<result column="file_url" property="fileUrl" jdbcType="VARCHAR"/>10<result column="created" property="created" jdbcType="TIMESTAMP"/>11<result column="updated" property="updated" jdbcType="TIMESTAMP"/>12<result column="remark" property="remark" jdbcType="VARCHAR"/>13<result column="status" property="status" jdbcType="INTEGER"/>14</resultMap>15<sql id="Example_Where_Clause">16<where >17<foreach collection="oredCriteria" item="criteria" separator="or">18<if test="criteria.valid">19<trim prefix="(" suffix=")" prefixOverrides="and">20<foreach collection="criteria.criteria" item="criterion">21<choose >22<when test="criterion.noValue">23 and ${criterion.condition}24</when>25<when test="criterion.singleValue">26 and ${criterion.condition} #{criterion.value}27</when>28<when test="criterion.betweenValue">29 and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}30</when>31<when test="criterion.listValue">32 and ${criterion.condition}33<foreach collection="criterion.value" item="listItem" open="(" close=")" separator=",">34 #{listItem}35</foreach>36</when>37</choose>38</foreach>39</trim>40</if>41</foreach>42</where>43</sql>44<sql id="Update_By_Example_Where_Clause">45<where >46<foreach collection="example.oredCriteria" item="criteria" separator="or">47<if test="criteria.valid">48<trim prefix="(" suffix=")" prefixOverrides="and">49<foreach collection="criteria.criteria" item="criterion">50<choose >51<when test="criterion.noValue">52 and ${criterion.condition}53</when>54<when test="criterion.singleValue">55 and ${criterion.condition} #{criterion.value}56</when>57<when test="criterion.betweenValue">58 and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}59</when>60<when test="criterion.listValue">61 and ${criterion.condition}62<foreach collection="criterion.value" item="listItem" open="(" close=")" separator=",">63 #{listItem}64</foreach>65</when>66</choose>67</foreach>68</trim>69</if>70</foreach>71</where>72</sql>73<sql id="Base_Column_List">74 id, cid, uid, filename, file_url, created, updated, remark, status75</sql>76<select id="selectByExample" resultMap="BaseResultMap" parameterType="com.sun123.springboot.entity.TaskExample"> 77 select78<if test="distinct">79 distinct80</if>81<include refid="Base_Column_List"/>82 from task83<if test="_parameter != null">84<include refid="Example_Where_Clause"/>85</if>86<if test="orderByClause != null">87 order by ${orderByClause}88</if>89</select>90<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="ng.Integer">91 select92<include refid="Base_Column_List"/>93 from task94 where id = #{id,jdbcType=INTEGER}95</select>96<delete id="deleteByPrimaryKey" parameterType="ng.Integer">97 delete from task98 where id = #{id,jdbcType=INTEGER}99</delete>100<delete id="deleteByExample" parameterType="com.sun123.springboot.entity.TaskExample">101 delete from task102<if test="_parameter != null">103<include refid="Example_Where_Clause"/>104</if>105</delete>106<insert id="insert" parameterType="com.sun123.springboot.entity.Task">107 insert into task (id, cid, uid,108 filename, file_url, created,109 updated, remark, status110 )111 values (#{id,jdbcType=INTEGER}, #{cid,jdbcType=VARCHAR}, #{uid,jdbcType=INTEGER},112 #{filename,jdbcType=VARCHAR}, #{fileUrl,jdbcType=VARCHAR}, #{created,jdbcType=TIMESTAMP},113 #{updated,jdbcType=TIMESTAMP}, #{remark,jdbcType=VARCHAR}, #{status,jdbcType=INTEGER}114 )115</insert>116<insert id="insertSelective" parameterType="com.sun123.springboot.entity.Task">117 insert into task118<trim prefix="(" suffix=")" suffixOverrides=",">119<if test="id != null">120 id,121</if>122<if test="cid != null">123 cid,124</if>125<if test="uid != null">126 uid,127</if>128<if test="filename != null">129 filename,130</if>131<if test="fileUrl != null">132 file_url,133</if>134<if test="created != null">135 created,136</if>137<if test="updated != null">138 updated,139</if>140<if test="remark != null">141 remark,142</if>143<if test="status != null">144 status,145</if>146</trim>147<trim prefix="values (" suffix=")" suffixOverrides=",">148<if test="id != null">149 #{id,jdbcType=INTEGER},150</if>151<if test="cid != null">152 #{cid,jdbcType=VARCHAR},153</if>154<if test="uid != null">155 #{uid,jdbcType=INTEGER},156</if>157<if test="filename != null">158 #{filename,jdbcType=VARCHAR},159</if>160<if test="fileUrl != null">161 #{fileUrl,jdbcType=VARCHAR},162</if>163<if test="created != null">164 #{created,jdbcType=TIMESTAMP},165</if>166<if test="updated != null">167 #{updated,jdbcType=TIMESTAMP},168</if>169<if test="remark != null">170 #{remark,jdbcType=VARCHAR},171</if>172<if test="status != null">173 #{status,jdbcType=INTEGER},174</if>175</trim>176</insert>177<select id="countByExample" parameterType="com.sun123.springboot.entity.TaskExample" resultType="ng.Integer"> 178 select count(*) from task179<if test="_parameter != null">180<include refid="Example_Where_Clause"/>181</if>182</select>183<update id="updateByExampleSelective" parameterType="map">184 update task185<set >186<if test="record.id != null">187 id = #{record.id,jdbcType=INTEGER},188</if>189<if test="record.cid != null">190 cid = #{record.cid,jdbcType=VARCHAR},191</if>192<if test="record.uid != null">193 uid = #{record.uid,jdbcType=INTEGER},194</if>195<if test="record.filename != null">196 filename = #{record.filename,jdbcType=VARCHAR},197</if>198<if test="record.fileUrl != null">199 file_url = #{record.fileUrl,jdbcType=VARCHAR},200</if>201<if test="record.created != null">202 created = #{record.created,jdbcType=TIMESTAMP},203</if>204<if test="record.updated != null">205 updated = #{record.updated,jdbcType=TIMESTAMP},206</if>207<if test="record.remark != null">208 remark = #{record.remark,jdbcType=VARCHAR},209</if>210<if test="record.status != null">211 status = #{record.status,jdbcType=INTEGER},212</if>213</set>214<if test="_parameter != null">215<include refid="Update_By_Example_Where_Clause"/>216</if>217</update>218<update id="updateByExample" parameterType="map">219 update task220 set id = #{record.id,jdbcType=INTEGER},221 cid = #{record.cid,jdbcType=VARCHAR},222 uid = #{record.uid,jdbcType=INTEGER},223 filename = #{record.filename,jdbcType=VARCHAR},224 file_url = #{record.fileUrl,jdbcType=VARCHAR},225 created = #{record.created,jdbcType=TIMESTAMP},226 updated = #{record.updated,jdbcType=TIMESTAMP},227 remark = #{record.remark,jdbcType=VARCHAR},228 status = #{record.status,jdbcType=INTEGER}229<if test="_parameter != null">230<include refid="Update_By_Example_Where_Clause"/>231</if>232</update>233<update id="updateByPrimaryKeySelective" parameterType="com.sun123.springboot.entity.Task">234 update task235<set >236<if test="cid != null">237 cid = #{cid,jdbcType=VARCHAR},238</if>239<if test="uid != null">240 uid = #{uid,jdbcType=INTEGER},241</if>242<if test="filename != null">243 filename = #{filename,jdbcType=VARCHAR},244</if>245<if test="fileUrl != null">246 file_url = #{fileUrl,jdbcType=VARCHAR},247</if>248<if test="created != null">249 created = #{created,jdbcType=TIMESTAMP},250</if>251<if test="updated != null">252 updated = #{updated,jdbcType=TIMESTAMP},253</if>254<if test="remark != null">255 remark = #{remark,jdbcType=VARCHAR},256</if>257<if test="status != null">258 status = #{status,jdbcType=INTEGER},259</if>260</set>261 where id = #{id,jdbcType=INTEGER}262</update>263<update id="updateByPrimaryKey" parameterType="com.sun123.springboot.entity.Task">264 update task265 set cid = #{cid,jdbcType=VARCHAR},266 uid = #{uid,jdbcType=INTEGER},267 filename = #{filename,jdbcType=VARCHAR},268 file_url = #{fileUrl,jdbcType=VARCHAR},269 created = #{created,jdbcType=TIMESTAMP},270 updated = #{updated,jdbcType=TIMESTAMP},271 remark = #{remark,jdbcType=VARCHAR},272 status = #{status,jdbcType=INTEGER}273 where id = #{id,jdbcType=INTEGER}274</update>275276277<resultMap id="TaskResultMap" type="com.sun123.springboot.entity.Task" extends="BaseResultMap">278<association property="course" resultMap="com.sun123.springboot.mapper.CourseMapper.BaseResultMap"></association>279</resultMap>280<select id="taskList" resultMap="TaskResultMap">281 SELECT t.*,c.* FROM task t LEFT JOIN course c ON t.cid=c.c_num282</select>283284</mapper><association property="course" resultMap="com.sun123.springboot.mapper.CourseMapper.BaseResultMap"></association>这种写法直接引⼊了CourseMApper.xml中的字段信息，不需要再次定义，⽐较简洁。