Mybatis上路_03-简单关联数据查询

1．示例数据库准备：

1）增加创建示例表单：

CREATE TABLE TB_JOB (    id INT PRIMARY KEY AUTO_INCREMENT,    id_person INT NOT NULL,    job VARCHAR(60) NOT NULL,    company VARCHAR(60) NOT NULL);

2）插入示例条目：

INSERT INTO    TB_JOB (id_person, job, company)VALUES    (3, "Singer", "BadBoy");INSERT INTO    TB_JOB (id_person, job, company)VALUES    (3, "writer", "BadBoy2");INSERT INTO    TB_JOB (id_person, job, company)VALUES    (3, "Producer", "BadBoy3");

3）增加创建JavaBean类BeanJob：

package cn.cvu.domain;public class BeanJob {    private Integer id;    private BeanPerson person;    private String job;    private String company;     public Integer getId() {...}    public void setId(Integer id) {...}    public BeanPerson getPerson() {...}    public void setPerson(BeanPerson person) {...}    public String getJob() {...}    public void setJob(String job) {...}    public String getCompany() {...}    public void setCompany(String company) {...}    public BeanJob(Integer id, BeanPerson person, String job, String company) {        this.id = id;        this.person = person;        this.job = job;        this.company = company;    }    public BeanJob() {    }    @Override    public String toString() {        return "BeanJob [id=" + id + ", person=" + person + ", job=" + job                + ", company=" + company + "]";    }}

2．修改接口：

package cn.cvu.service;import java.util.List;import cn.cvu.domain.BeanJob;//接口，定义操作public interface IOperationBean {    //根据Person的id得到他全部的工作    public List<BeanJob> getPersonJobs(int id);}

3．修改BeanPerson.xml文件：

1）方式一：

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"        "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><!-- 命名空间 namespace=“接口的全路径” --><mapper namespace="cn.cvu.service.IOperationBean"><!--          连接查询            -->    <!-- 查询 Person -->    <resultMap id="resultListPerson" type="cn.cvu.domain.BeanPerson">        <id column="id" property="id" />        <result column="name" property="name" />        <result column="country" property="country" />        <result column="address" property="address" />    </resultMap>    <!-- Job联合Person 进行查询  -->    <resultMap id="resultPersonJobs" type="cn.cvu.domain.BeanJob">        <id property="id" column="id" />        <result property="job" column="job" />        <result property="company" column="company" />        <association property="person"                     javaType="cn.cvu.domain.BeanPerson"                     resultMap="resultListPerson" />     </resultMap>    <select id="getPersonJobs" parameterType="int" resultMap="resultPersonJobs">                   SELECT                      <!-- 这里必须是tb_job在前 -->                       tb_job.id,    tb_job.job,     tb_job.company,   tb_job.id_person,                      <!-- 必须是tb_person在后 -->                      tb_person.id, tb_person.name, tb_person.country, tb_person.address                      <!-- 否则只能查到第一条记录 -->                   FROM                      tb_person, tb_job                   WHERE                       tb_person.id=tb_job.id_person                   AND                      tb_person.id=#{id}    </select></mapper>

2）方式二：

view source

print?

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"          "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><!-- 命名空间 namespace=“接口的全路径” --><mapper namespace="cn.cvu.service.IOperationBean">    <!--          连接查询                       -->    <resultMap id="resultPersonJobList" type="cn.cvu.domain.BeanJob">        <id property="id" column="id" />        <result property="job" column="job" />        <result property="company" column="company" />        <association property="person" javaType="cn.cvu.domain.BeanPerson">            <id property="id" column="id" />            <result property="name" column="name" />            <result property="country" column="country" />            <result property="address" column="address" />         </association>    </resultMap>    <select id="getPersonJobs" parameterType="int" resultMap="resultPersonJobList">                    SELECT                         tb_job.id,    tb_job.job,     tb_job.company, tb_job.id_person,                        tb_person.id, tb_person.name, tb_person.country, tb_person.address                    FROM                        tb_person, tb_job                    WHERE                        tb_person.id=tb_job.id_person                    AND                        tb_person.id=#{id}     </select></mapper>

4． 测试： 

/**     * 查询连接数据     * @throws Exception     */    @Test    public void testGetPersonJob() throws Exception {        Reader reader = Resources.getResourceAsReader("Configuration.xml");        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);        SqlSession session = sqlSessionFactory.openSession();        IOperationBean operationBean = session.getMapper(IOperationBean.class);        List<BeanJob> jobs = operationBean.getPersonJobs(3);        for(BeanJob job:jobs){             System.out.println("人员ID："+job.getPerson().getId());             System.out.println("姓名： "+job.getPerson().getName());             System.out.println("国家： "+job.getPerson().getCountry());             System.out.println("地址： "+job.getPerson().getAddress());             System.out.println("工作ID： "+job.getId());             System.out.println("工作： "+job.getJob());             System.out.println("公司： "+job.getCompany());        }        session.close();    }

 - end