MyBatis 入门(三)--链接表查询

一、只包含一个对象的情况

1. 先看看数据库的设计
   
   一个用户表，和用户的身份证id记录表

2. mapper文件

<resultMap id="BaseResultMap" type="com.elements.user.model.User">
        <id column="userId" property="userid" jdbcType="INTEGER" />
        <result column="UserName" property="username" jdbcType="VARCHAR" />
        <result column="UserEmail" property="useremail" jdbcType="VARCHAR" />
        <association property="useridentification" column="userId"
            javaType="com.elements.user.model.Useridentification">
            <id property="userid" column="ui_id" />
            <result property="code" column="code" />
        </association>
    </resultMap>

    <select id="selectByPrimaryKey" resultMap="BaseResultMap"
        parameterType="java.lang.Integer">

        SELECT
        u.userId as userId, u.UserName as UserName,
        u.UserEmail as UserEmail, ui.userId as ui_id, ui.code as code
        FROM
        user
        u,
        useridentification ui
        WHERE
        u.userId = ui.userId
        and u.userId = #{userid,jdbcType=INTEGER}
    </select>

二、一个对象内包括一个list的情况 （1对多）

1. 先看数据库的设计
   

我们先有一个主表 acpz,他有kjq和pzh两个主键，又设计了他的一个从表axpzmx的他的明细表.
我们希望读取主表的时候就加载从表的明细数据，那么在acpz内就有另一个明细的list ：private List mxlist;
2.类设计

public class Acpz extends AcpzKey {

    private Long acctAmt;

    private List<Axpzmx> mxlist;

    public List<Axpzmx> getMxlist() {
        return mxlist;
    }

    public void setMxlist(List<Axpzmx> mxlist) {
        this.mxlist = mxlist;
    }  
    public Long getAcctAmt() {
        return acctAmt;
    }
        public void setAcctAmt(Long acctAmt) {
        this.acctAmt = acctAmt;
    }
    @Override
    public String toString() {
        return "Acpz [acctAmt=" + acctAmt + ", " + super.toString() + ", mxlist=" + mxlist+"]";
    }

}

3.mapper 文件

   <resultMap type="com.elements.acpz.model.Acpz" id="AcpzResult">
        <id property="kjq" column="kjq" />
        <id property="pzh" column="pzh" />
        <result property="acctAmt" column="acct_amt" />
        <collection property="mxlist" ofType="com.elements.acpz.model.Axpzmx">
            <id property="kjq" column="kjq" />
            <id property="pzh" column="pzh" />
            <id property="id" column="id" />
            <result property="acctCode" column="acct_code" />
            <result property="accAmt" column="mx_acct_amt" />
        </collection>
    </resultMap>

    <select id="selectByPrimaryKey" parameterType="com.elements.acpz.model.AcpzKey"
        resultMap="AcpzResult">
        SELECT
        pz.kjq as kjq,
        pz.pzh as pzh,
        pz.acct_amt
        as acct_amt,
        mx.id as id,
        mx.acct_code as acct_code,
        mx.acc_amt as
        mx_acct_amt
        FROM
        mybatis.acpz pz,
        axpzmx mx
        WHERE
        pz.kjq = mx.kjq AND
        pz.pzh = mx.pzh
        and pz.kjq=#{kjq,jdbcType=VARCHAR}
        and pz.pzh=#{pzh,jdbcType=VARCHAR}

    </select>


    <select id="selectAll" parameterType="com.elements.acpz.model.AcpzKey"
        resultMap="AcpzResult">
        SELECT
        pz.kjq as kjq,
        pz.pzh as pzh,
        pz.acct_amt
        as acct_amt,
        mx.id as id,
        mx.acct_code as acct_code,
        mx.acc_amt as
        mx_acct_amt
        FROM
        mybatis.acpz pz,
        axpzmx mx
        WHERE
        pz.kjq = mx.kjq AND
        pz.pzh = mx.pzh


    </select>

4.数据库主键和mapper配置文件的关系

 项目代码：http://pan.baidu.com/s/1hrN5pNQ