在数据库的查询中,我们经常会遇到一对一的连接查询,比如一条商品购买明细能唯一对应一条商品信息。如下面两个类:
商品类(均省略get和set函数):
public class Seckill {
private long seckillId;
private String name;
private int number;
private Date startTime;
private Date endTime;
private Date createTime;
商品购买明细类:
public class SuccessKilled {
private long seckillId;
private long userPhone;
private short state;
private Date createTime;
//多对一
private Seckill seckill;
在使用Mybatis时,我们可以很轻易的获得商品信息的一条记录,对应xml文件如下:
<mapper namespace="edu.seckill.dao.SeckillDao">
<resultMap id="seckill" type="edu.seckill.entity.Seckill">
<id column="seckill_id" jdbcType="INTEGER" property="seckillId"></id>
<result column="name" jdbcType="VARCHAR" property="name"></result>
<result column="number" jdbcType="INTEGER" property="number"></result>
<result column="start_time" jdbcType="TIMESTAMP" property="startTime"></result>
<result column="end_time" jdbcType="TIMESTAMP" property="endTime"></result>
<result column="create_time" jdbcType="TIMESTAMP" property="createTime"></result>
</resultMap>
<select id="queryById" parameterType="edu.seckill.entity.Seckill" resultMap="seckill">
SELECT seckill_id, name, number, start_time, end_time, create_time
FROM seckill
WHERE seckill_id = #{seckillId};
</select>
但是在读取商品明细类时,seckill却不止一个字段,这时就可以使用association来进行读取操作。
第一种方式:直接在association标签里写对应的列名
<resultMap id="successKilled" type="edu.seckill.entity.SuccessKilled">
<id column="seckill_id" jdbcType="INTEGER" property="seckillId"></id>
<id column="user_phone" jdbcType="INTEGER" property="userPhone"></id>
<result column="state" jdbcType="INTEGER" property="state"></result>
<result column="create_time" jdbcType="TIMESTAMP" property="createTime"></result>
<association property="seckill" javaType="edu.seckill.entity.Seckill">
<id column="seckill.seckill_id" jdbcType="INTEGER" property="seckillId"></id>
<result column="seckill.name" jdbcType="VARCHAR" property="name"></result>
<result column="seckill.number" jdbcType="INTEGER" property="number"></result>
<result column="seckill.start_time" jdbcType="TIMESTAMP" property="startTime"></result>
<result column="seckill.end_time" jdbcType="TIMESTAMP" property="endTime"></result>
<result column="seckill.create_time" jdbcType="TIMESTAMP" property="createTime"></result>
</association>-->
</resultMap>
对应的sql语句应写全,要注意sql语句列名与ResultMap里column的值的对应。
注:Mybatis会忽略表名或者表的别名。
<select id="queryByIdWithSeckill" resultMap="successKilled">
SELECT
sk.seckill_id,
sk.user_phone,
sk.create_time,
sk.state,
s.seckill_id "seckill.seckill_id",
s.name "seckill.name",
s.number "seckill.number",
s.start_time "seckill.start_time",
s.end_time "seckill.end_time",
s.create_time "seckill.create_time"
FROM success_killed sk INNER JOIN seckill s
ON sk.seckill_id = s.seckill_id
WHERE sk.seckill_id = #{seckillId}
AND user_phone = #{userPhone}
</select>
第二种方式:给association传入一个ResultMap
<resultMap id="successKilled" type="edu.seckill.entity.SuccessKilled">
<id column="seckill_id" jdbcType="INTEGER" property="seckillId"></id>
<id column="user_phone" jdbcType="INTEGER" property="userPhone"></id>
<result column="state" jdbcType="INTEGER" property="state"></result>
<result column="create_time" jdbcType="TIMESTAMP" property="createTime"></result>
<association property="seckill" javaType="edu.seckill.entity.Seckill" resultMap="edu.seckill.dao.SeckillDao.seckill"></association>
</resultMap>
这种方式显得代码简洁,但是要注意的是association里的ResultMap与自己所处的ResultMap里的column列名有没有重复,如果重复的列名对应的值是相同的就无所谓,但如果取值不同的话则不建议使用。
这里的sql语句与第一种大致相同,需要注意的是列别名。
第三种方式:给association传入一个select
<resultMap id="successKilled" type="edu.seckill.entity.SuccessKilled">
<id column="seckill_id" jdbcType="INTEGER" property="seckillId"></id>
<id column="user_phone" jdbcType="INTEGER" property="userPhone"></id>
<result column="state" jdbcType="INTEGER" property="state"></result>
<result column="create_time" jdbcType="TIMESTAMP" property="createTime"></result>
<association column="seckill_id" property="seckill" javaType="edu.seckill.entity.Seckill" select="edu.seckill.dao.SeckillDao.queryById"></association>
</resultMap>
这种方式本质上相当于两次select,但是使用得当显得代码简洁,sql语句也不用去进行连接查询。
此处的sql语句:
<select id="queryByIdWithSeckill" resultMap="successKilled">
SELECT * FROM success_killed
WHERE seckill_id = #{seckillId}
AND user_phone = #{userPhone}
</select>
注:此处的association标签传入的column参数只有一个,如果需要多个的话可以使用column= “{prop1=col1,prop2=col2}”这样的语法,设置多个列名传入到嵌套查询语句。