mybatis关联查询(1—N)
http://blog.csdn.net/vpivot/article/details/11965965
1,数据表说明
Prodectorder_Imei_Conf (表) ——》ProdectorderImeiConf(实体类)
Prodectorder_Imei_Conf_Info(表) ——》ProdectorderImeiConfInfo(实体类)
Prodectorder_Imei_Conf 和Prodectorder_Imei_Conf_Info 是 一对多的关系
Prodectorder_Imei_Conf_Info中conf_id是外键,对应Prodectorder_Imei_Conf的id
2,实体类大概这样写了
1——N :在1的地方配置包含N的集合
[java] view plaincopyprint?
- public class ProdectorderImeiConf {
- private String id;
- private String productorderNo;
- private Integer onceImeiCount;
- private Integer imeiStrategy;
- private String printSn;
- private Date updatetime;
- private String username;
- private String userbh;
- private String isdel;
- private String mark;
- private List<ProdectorderImeiConfInfo> infoList; //这个才是重要的
- //省略get,set
- }
[java] view plaincopyprint?
- public class ProdectorderImeiConfInfo {
- private String id;
- private String imeiNo;
- private String confId;
- private String imeiStartNo;
- private String imeiEndNo;
- private String imeiLastNo;
- private String imeiIncrement;
- private String imeiTac;
- private String imeiFac;
- private String imeiSnr;
- private Integer imeiSp;
- private String state;
- //省略get,set
- }
3,重头戏在这边,映射文件的配置
首先呢,因为此处主要查1,关联带出n,所以,先要来这么一段(注意这个里面先只是基本数据映射,所以先没有infoList属性的映射关系)
[html] view plaincopyprint?
- <resultMap id="BaseResultMap" type="com.model.imei.ProdectorderImeiConf">
- <id column="id" jdbcType="VARCHAR" property="id" />
- <result column="productorder_no" jdbcType="VARCHAR" property="productorderNo" />
- <result column="once_imei_count" jdbcType="INTEGER" property="onceImeiCount" />
- <result column="imei_strategy" jdbcType="INTEGER" property="imeiStrategy" />
- <result column="print_sn" jdbcType="CHAR" property="printSn" />
- <result column="updatetime" jdbcType="DATE" property="updatetime" />
- <result column="username" jdbcType="VARCHAR" property="username" />
- <result column="userbh" jdbcType="VARCHAR" property="userbh" />
- <result column="isdel" jdbcType="CHAR" property="isdel" />
- <result column="mark" jdbcType="VARCHAR" property="mark" />
- </resultMap>
其次呢,关联查询n,所以要配置n的结果映射关系,再来下面这么一段
[html] view plaincopyprint?
- <resultMap id="relationResult" type="com.model.imei.ProdectorderImeiConfInfo">
- <id column="id" jdbcType="VARCHAR" property="id" />
- <result column="imei_no" jdbcType="CHAR" property="imeiNo" />
- <result column="conf_id" jdbcType="VARCHAR" property="confId" />
- <result column="imei_start_no" jdbcType="VARCHAR" property="imeiStartNo" />
- <result column="imei_end_no" jdbcType="VARCHAR" property="imeiEndNo" />
- <result column="imei_last_no" jdbcType="VARCHAR" property="imeiLastNo" />
- <result column="imei_increment" jdbcType="VARCHAR" property="imeiIncrement" />
- <result column="imei_tac" jdbcType="VARCHAR" property="imeiTac" />
- <result column="imei_fac" jdbcType="VARCHAR" property="imeiFac" />
- <result column="imei_snr" jdbcType="VARCHAR" property="imeiSnr" />
- <result column="imei_sp" jdbcType="INTEGER" property="imeiSp" />
- <result column="state" jdbcType="CHAR" property="state" />
- </resultMap>
再怎么样呢?把这两种结合起来呗,那就得来这么一段,揉啊搓啊,搞到一起(解释一下就是:搞了一个新数据类型,先继承1的数据类型,此时就有了1的功能了,但是这会儿你得想起来这1里面还有个包含了n的infoList属性吧。对了,这地方就给他扩展了,此属性用的是一个<collection>标签,property不用说了,column要注意,这个需要和后面的select看齐,既然是关联查询,那子查询肯定有一个条件是需要从主数据得到的。那这个column就是,我需要把那一列当做条件传给子查询当做条件。)
[html] view plaincopyprint?
- <resultMap id="myBaseResultMap" extends="BaseResultMap"
- type="com.model.imei.ProdectorderImeiConf">
- <collection property="infoList" column="id"
- ofType="com.model.imei.ProdectorderImeiConfInfo" select="getInfiList"/>
- </resultMap>
看看这个getInfiList查询配置就明白了(这个不用解释吧,在此处就是,根据主配置主键查找子配置信息,返回是子配置信息的集合)
[html] view plaincopyprint?
- <select id="getInfiList" resultMap="relationResult"
- parameterType="java.lang.String">
- select id, imei_no, conf_id, imei_start_no, imei_end_no,
- imei_last_no, imei_increment, imei_tac,
- imei_fac, imei_snr, imei_sp, state from prodectorder_imei_conf_info
- where conf_id=#{id}
- </select>
最最后,最终的查询配置就出来了
[html] view plaincopyprint?
- <select id="selectByPrimaryOrderNo" parameterType="java.lang.String"
- resultMap="myBaseResultMap">
- select
- <include refid="Base_Column_List" />
- from prodectorder_imei_conf
- where productorder_no = #{orderNo,jdbcType=VARCHAR}
- </select>