Mybatis中联合查询

实体类有Employee和Project，这次要通过联合查询，通过雇员的id查找雇员所做的项目，对应的数据库的字段为：

新建一个实体类project，属性并没和数据库对应：

package micro.model;
/**
@author:micro_hz
2015年8月20日
 */
public class Project {
    int id;
    //这里属性有Employee类型
    Employee employee;
    String name;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Employee getEmployee() {
        return employee;
    }
    public void setEmployee(Employee employee) {
        this.employee = employee;
    }
}

Employee:

package micro.model;
/**
@author:micro_hz
2015年8月19日
 */
public class Employee {
    int id;
    String name;
    int age;
    String address;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }

}

Configuration.xml:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <typeAliases>
        <typeAlias alias="User" type="micro.model.User" />
        <typeAlias alias="Student" type="micro.model.Student" />
        <typeAlias alias="Employee" type="micro.model.Employee" />
        <typeAlias alias="Article" type="micro.model.Article"/>
        <typeAlias alias="Project" type="micro.model.Project"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://localhost:3306/micro" />
                <property name="username" value="root" />
                <property name="password" value="root" />
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="micro/mapper/User.xml" />
        <mapper resource="micro/mapper/Student.xml" />
        <mapper resource="micro/mapper/Employee.xml" />


    </mappers>
</configuration>

Employee.xml:

<?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">
<mapper namespace="micro.inter.EmployeeDao">
    <resultMap type="Employee" id="EmployeeList">
        <result column="id" property="id" />
        <result column="name" property="name" />
        <result column="age" property="age" />
        <result column="address" property="address" />
    </resultMap>

    <select id="selectEmployeeById" parameterType="int" resultType="Employee">
        select * from employee where #{id}
    </select>

    <select id="selectEmployees" resultMap="EmployeeList">
        select * from employee
    </select>

    <insert id="addEmployee" parameterType="Employee">
        insert into employee
        value(#{id},#{name},#{age},#{address})
    </insert>

    <update id="updateEmployee" parameterType="Employee">
        update employee set
        name = #{name},age = #{age},address = #{address} where id = #{id}
    </update>

    <delete id="deleteEmployee" parameterType="int">
        delete from employee
        where id = #{id}
    </delete>

    <!-- 第一种配置方法 -->
    <resultMap type="Project" id="EmloyeeProList">
        <result property="id" column="id" />
        <result property="name" column="name" />
        <!-- 
        <association property="employee" javaType="Employee">
            <result property="id" column="emid" />
            <result property="name" column="name" />
            <result property="address" column="address" />
        </association>  -->
        <!-- 第二种配置方法 -->
        <association property="employee" resultMap="EmployeeList"></association>
    </resultMap>
    <select id="selectEmloyeeProList" parameterType="int" resultMap="EmloyeeProList">
        select project.id,project.name,employee.id,employee.name,employee.address,employee.age from employee,project where employee.id = project.emid and employee.id = #{id}
    </select>
</mapper>

接口EmployeeDao:

package micro.inter;

import java.util.List;

import micro.model.Employee;
import micro.model.Project;

/**
@author:micro_hz
2015年8月19日
 */
public interface EmployeeDao {
    //定义 CUID
    //通过id查找
    public void selectEmployeeById(int id);
    //把所有对象放进列表
    public List<Employee> selectEmployees();
    //增加employee
    public void addEmployee(Employee employee);
    //删除employee
    public void deleteEmployee(int id);
    //改对应id的employee
    public void updateEmployee(Employee employee);
    //查找对应雇员的项目
    public List<Project> selectEmloyeeProList(int id);
}

测试：

//查到对应雇员id的所有项目
        List<Project> list = Service.getProjectsByEmid(2);
        for(Project project : list)
        {
            System.out.println("项目名字叫"+project.getName()+"项目负责的雇员为"+project.getEmployee().getId()+"项目的id为"+project.getId());
        }