mybatis 一对一关系取值association

最新推荐文章于 2022-03-21 23:57:41 发布

archer.wu

最新推荐文章于 2022-03-21 23:57:41 发布

阅读量5.1k

点赞数

分类专栏： mybatis 文章标签： mybatis

本文链接：https://blog.csdn.net/wild46cat/article/details/53140298

版权

mybatis 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

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}
null
User2{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}
null
User2{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}
null
User2{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='122@qq.com', photo='', duty=null}
null
User2{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='8866@qq.com', 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='323@qq.com', photo='/ssmUpload/files/file20161106191234411.jpg', duty=null}
null