MyBatis多表查询(1)

两表联查（一对一）

1、创建一个丈夫（husband）表和妻子（wife）表

CREATE TABLE husband(
    husid INT PRIMARY KEY,
    husname VARCHAR(30)
)

INSERT INTO husband VALUES(1,"吴奇隆");
INSERT INTO husband VALUES(2,"邓超");
INSERT INTO husband VALUES(3,"胡歌");

CREATE TABLE wife(
   wifeid INT PRIMARY KEY,
   wifename VARCHAR(30),
   husid INT 
)

INSERT INTO wife VALUES(1,"孙俪",2);
INSERT INTO wife VALUES(2,"刘诗诗",1);
INSERT INTO wife VALUES(3,"阿娇",NULL);

2、创建Husband和Wife的实体类

package com.fan.entity;


public class Husband {
    private Integer husId;
    private String husName;

    private Wife wife;

    public Integer getHusId() {
        return husId;
    }

    public void setHusId(Integer husId) {
        this.husId = husId;
    }

    public String getHusName() {
        return husName;
    }

    public void setHusName(String husName) {
        this.husName = husName;
    }

    public Wife getWife() {
        return wife;
    }

    public void setWife(Wife wife) {
        this.wife = wife;
    }
}

package com.fan.entity;

public class Wife {
    private Integer wifeId;
    private String wifeName;
    private Integer husId;

    private Husband husband;

    public Integer getWifeId() {
        return wifeId;
    }

    public void setWifeId(Integer wifeId) {
        this.wifeId = wifeId;
    }

    public String getWifeName() {
        return wifeName;
    }

    public void setWifeName(String wifeName) {
        this.wifeName = wifeName;
    }

    public Integer getHusId() {
        return husId;
    }

    public void setHusId(Integer husId) {
        this.husId = husId;
    }

    public Husband getHusband() {
        return husband;
    }

    public void setHusband(Husband husband) {
        this.husband = husband;
    }
}

3、创建HusbandAndWifeDao接口

package com.fan.dao;

import com.fan.entity.Husband;

public interface HusbandAndWifeDao {
    //根据丈夫id查询丈夫信息并查出对应妻子的姓名
    public Husband findByHusId(int husId);
}

4、配置HusbandAndWifeDaoMapper.xml映射文件并将其配置到mybatis-config.xml

<mapper namespace="com.fan.dao.HusbandAndWifeDao">
    <!--type中对应Husband实体类（com.fan.entity.Husband），由于在mybatis-config.xml中起了别名这里可以简写-->
    <resultMap id="a1" type="Husband">
        <id property="husId" column="husid"></id>
        <result property="husName" column="husname"></result>
        <association property="wife" javaType="Wife">
            <id property="wifeId" column="wifeid"></id>
            <result property="wifeName" column="wifename"></result>
        </association>
    </resultMap>

    <select id="findByHusId" resultMap="a1">
       select * from husband,wife where husband.husid=wife.husid and husband.husid=#{husId}
    </select>
</mapper>

mybatis-config.xml获取对应的映射文件

 <mapper resource="Mapper/HusbandAndWifeDaoMapper.xml"></mapper>

5、添加测试类

import com.fan.dao.HusbandAndWifeDao;
import com.fan.entity.Husband;
import com.fan.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;

public class Test1 {
    public static void main(String[] args) {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        HusbandAndWifeDao husbandAndWifeDao = sqlSession.getMapper(HusbandAndWifeDao.class);
        Husband husband = husbandAndWifeDao.findByHusId(1);
        System.out.println(husband.getHusName()+"的妻子是"+husband.getWife().getWifeName());
        MyBatisUtil.closeSqlSession(sqlSession);
    }
}

测试结果如下：

测试结果