mybatis 一对一关系取值association

mybatis支持一对一取值和一对多取值。简单的来讲就是mybatis实现的如下的功能。

我有一个user表，还有一个duty表，其中每一个人只能属于一个部门，这样每个人和部门就是一对一的关系。这个时候表是这样设计的：

user表中的字段DUTYID对应的是duty表中的id字段。那么如果正常是用关联进行查询的话是这样的。

select a.* ,b.* from user a inner join duty b on a.DUTYID=b.ID

现在，问题来了。我取出来的这个是个什么类型的？map吗，如果是map的话，那么就不用面向对象（换句话说，只是返回map的话对于业务上会有些不清晰，不过也可以）。

这样的话我们需要返回duty的bean和user的bean。明显的是user中有一个duty属性。

下面是对应的类：

Duty.java

package com.xueyou.ssm.model;/** * Created by wuxueyou on 2016/11/12. */public class Duty {    private String id;    private String dutyname;    private String memo;    private String depid;    private String ordernumber;    public Duty(String id, String dutyname, String memo, String depid, String ordernumber) {        this.id = id;        this.dutyname = dutyname;        this.memo = memo;        this.depid = depid;        this.ordernumber = ordernumber;    }    @Override    public String toString() {        return "Duty{" +                "id='" + id + '\'' +                ", dutyname='" + dutyname + '\'' +                ", memo='" + memo + '\'' +                ", depid='" + depid + '\'' +                ", ordernumber='" + ordernumber + '\'' +                '}';    }    public String getId() {        return id;    }    public void setId(String id) {        this.id = id;    }    public String getDutyname() {        return dutyname;    }    public void setDutyname(String dutyname) {        this.dutyname = dutyname;    }    public String getMemo() {        return memo;    }    public void setMemo(String memo) {        this.memo = memo;    }    public String getDepid() {        return depid;    }    public void setDepid(String depid) {        this.depid = depid;    }    public String getOrdernumber() {        return ordernumber;    }    public void setOrdernumber(String ordernumber) {        this.ordernumber = ordernumber;    }}

User2.java

package com.xueyou.ssm.model;import com.fasterxml.jackson.annotation.JsonFormat;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.Date;/** * Created by wuxueyou on 16/10/31. */public class User2 {    private String id;    private String loginId;    private String password;    private String name;    private String sex;    private Date createTime;    private Date birthday;    private String status;    private String dutyid;    private String phone;    private String email;    private String photo;    private Duty duty;    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public String getSex() {//        if (sex == null || sex.equals("")) {//            return "";//        }//        if (sex.equals("1")) {//            return "男";//        } else {//            return "女";//        }        return sex;    }    public void setSex(String sex) {        this.sex = sex;    }    public String getId() {        return id;    }    public void setId(String id) {        this.id = id;    }    public String getLoginId() {        return loginId;    }    public void setLoginId(String loginId) {        this.loginId = loginId;    }    public String getPassword() {        return password;    }    public void setPassword(String password) {        this.password = password;    }    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")    public Date getCreateTime() {        return createTime;    }    public void setCreateTime(String createTime) {        try {            this.createTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(createTime);        } catch (ParseException e) {            e.printStackTrace();        }    }    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")    public Date getBirthday() {        return birthday;    }    public void setBirthday(String birthday) {        try {            this.birthday = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(birthday);        } catch (ParseException e) {            e.printStackTrace();        }    }    public String getStatus() {        return status;        /*if (status == null || status.equals("")) {            return "";        }        if (status.equals("1")) {            return "启用";        } else {            return "禁用";        }*/    }    public void setStatus(String status) {        this.status = status;    }    public String getEmail() {        return email;    }    public void setEmail(String email) {        this.email = email;    }    public String getPhone() {        return phone;    }    public void setPhone(String phone) {        this.phone = phone;    }    public String getDutyid() {        return dutyid;    }    public void setDutyid(String dutyid) {        this.dutyid = dutyid;    }    public String getPhoto() {        return photo;    }    public void setPhoto(String photo) {        this.photo = photo;    }    public Duty getDuty() {        return duty;    }    public void setDuty(Duty duty) {        this.duty = duty;    }    @Override    public String toString() {        return "User2{" +                "id='" + id + '\'' +                ", loginId='" + loginId + '\'' +                ", password='" + password + '\'' +                ", name='" + name + '\'' +                ", sex='" + sex + '\'' +                ", createTime=" + createTime +                ", birthday=" + birthday +                ", status='" + status + '\'' +                ", dutyid='" + dutyid + '\'' +                ", phone='" + phone + '\'' +                ", email='" + email + '\'' +                ", photo='" + photo + '\'' +                ", duty=" + duty +                '}';    }}

好了，现在需要对两个Bean进行持久化了。

首先是duty

public Duty getDutyById(String dutyid);

对应的dutymapper.xml中

<select id="getDutyById" parameterType="String" resultType="com.xueyou.ssm.model.Duty">        SELECT ID as id,DUTYNAME as dutyname, MEMO as memo,DEPID as depid,ORDERNUMBER as ordernumber from duty WHERE ID = #{dutyid}    </select>

然后是user，在user持久化的时候需要考虑到对应的duty是如何被关联的。

public List<User2> user2List(Map<String,Object> params);

对应的usermaper.xml中

<resultMap id="user2mapper" type="com.xueyou.ssm.model.User2">        <id property="id" column="ID"/>        <result property="loginId" column="LOGINID"/>        <result property="password" column="PASSWORD"/>        <result property="name" column="NAME"/>        <result property="createTime" column="CREATETIME"/>        <result property="birthday" column="BIRTHDAY"/>        <result property="sex" column="SEX"/>        <result property="status" column="STATUS"/>        <result property="dutyid" column="DUTYID"/>        <result property="phone" column="PHONE"/>        <result property="email" column="EMAIL"/>        <result property="photo" column="PHOTO"/>        <association property="duty" column="DUTYID" select="com.xueyou.ssm.dao.DutyDao.getDutyById"/>    </resultMap>    <select id="user2List" statementType="STATEMENT" parameterType="Map" resultMap="user2mapper">    SELECT id, loginId,password,name,createTime,birthday,    sex,status,dutyid,phone,email,photo FROM user    ORDER BY createTime DESC limit ${page},${rows}    </select>

这样就实现了一对一的持久化。

需要注意的地方是这里，在association 中的column是两张表在数据库中关联的字段。在本例中是DUTYID这个字段。

下面是一个单元测试：

@Test    public void getUser(){        Map<String,Object> params = new HashMap<>();        params.put("page","1");        params.put("rows","10");        List<User2> userlist = userDao.user2List(params);        for (User2 user : userlist) {            System.out.println(user);            System.out.println(user.getDuty());        }        Assert.assertEquals(7,userlist.size());    }

测试结果：

Duty{id='b68560a6-96c5-11e6-b79e-83fe6f33afd5', dutyname='项目助理', memo='负责辅助项目执行', depid='869069b6-937f-11e6-979c-bd06e7738959', ordernumber='1'}User2{id='c6a5f6b4-c668-4a4a-a2f0-e6c3320fcfba', loginId='yyuu', password='cc', name='aba', sex='1', createTime=Sun Nov 06 21:07:01 CST 2016, birthday=Sun Nov 06 21:07:07 CST 2016, status='1', dutyid='12', phone='1', email='1', photo='', duty=null}nullUser2{id='6bf4dc62-c80b-44c9-ae38-a61740c3a363', loginId='cc', password='1', name='1', sex='2', createTime=Sun Nov 06 20:56:20 CST 2016, birthday=Tue Nov 15 20:56:23 CST 2016, status='1', dutyid='1', phone='1', email='1', photo='', duty=null}nullUser2{id='30e95aa7-1984-4a1d-bdf4-fa16c3dfd7f5', loginId='43', password='12', name='1', sex='1', createTime=Sat Nov 05 23:29:00 CST 2016, birthday=Sat Nov 05 23:29:12 CST 2016, status='1', dutyid='1', phone='1', email='1', photo='/ssmUpload/files/file20161106191126337.jpg', duty=null}nullUser2{id='9999', loginId='132啊啊吧啊', password='faree', name='1', sex='2', createTime=Fri Nov 04 00:50:05 CST 2016, birthday=Sun Nov 06 00:09:57 CST 2016, status='1', dutyid='b68560a6-96c5-11e6-b79e-83fe6f33afd5', phone='123', email='123', photo='/ssmUpload/files/file20161106191506568.jpg', duty=Duty{id='b68560a6-96c5-11e6-b79e-83fe6f33afd5', dutyname='项目助理', memo='负责辅助项目执行', depid='869069b6-937f-11e6-979c-bd06e7738959', ordernumber='1'}}Duty{id='b68560a6-96c5-11e6-b79e-83fe6f33afd5', dutyname='项目助理', memo='负责辅助项目执行', depid='869069b6-937f-11e6-979c-bd06e7738959', ordernumber='1'}User2{id='714c9921-8b60-4213-bbaa-0cf91574e188', loginId='uuii', password='000', name='小明', sex='1', createTime=Thu Nov 03 19:23:11 CST 2016, birthday=Wed Nov 02 19:23:17 CST 2016, status='1', dutyid='12', phone='12341', email='[e-mail]', photo='', duty=null}nullUser2{id='47a226a2-9b8d-11e6-b79e-83fe6f33afd5', loginId='user1', password='1', name='aaa', sex='2', createTime=Wed Oct 26 23:02:57 CST 2016, birthday=Sun Nov 06 00:09:43 CST 2016, status='1', dutyid='77a7225e-960f-11e6-979c-bd06e7738959', phone='18322223333', email='[e-mail]', photo='/ssmUpload/files/file20161106191205189.png', duty=Duty{id='77a7225e-960f-11e6-979c-bd06e7738959', dutyname='项目主管', memo='管理项目经理', depid='869069b6-937f-11e6-979c-bd06e7738959', ordernumber='1'}}Duty{id='77a7225e-960f-11e6-979c-bd06e7738959', dutyname='项目主管', memo='管理项目经理', depid='869069b6-937f-11e6-979c-bd06e7738959', ordernumber='1'}User2{id='1083caa8-9143-11e6-945d-708e78b00b24', loginId='a', password='1', name='aa', sex='1', createTime=Mon Oct 24 20:39:56 CST 2016, birthday=Sat Oct 15 17:48:59 CST 2016, status='1', dutyid='111', phone='12344445555', email='[e-mail]', photo='/ssmUpload/files/file20161106191234411.jpg', duty=null}null