ibatis多表查询

我们先来看看这两张表

表名：tbl_Student

字段：ID,NAME,CLASS_ID

表名：tbl_Class

字段：ID，NAME

很简单的一个逻辑，班级下边有很多学生。我们先对学生表做简单的查询。

Model

public class Student

{

private String id;

private String name;

private Stirng birth;

private String class_id;

.......................

}

StudentMapper.xml

<typeAlias alias="StudentClass" type="model.Student"></typeAlias>

<sql id="select">

<![CDATA[

select ID,NAME,CLASS_ID

from tbl_Student

]]>

</sql>

<resultMap id="StudentMap" class="StudentClass">

<result property="id" column="ID" />

<result property="name" column="NAME" />

<result property="class_id" column="CLASS_ID" />

</resultMap>

<select id="selectAll" parameterClass="java.util.Map" resultMap="StudentMap">

<include refid="select" />

</select>

但是我想加入班级名称在里面呢？这个时候Student就不够用了，我们需要用dto了。

Dto

public class StudentDto

{

private String id;

private String name;

private Stirng birth;

private String class_id;

private String class_name;

......................

}

在原有的StudentMapper.xml文件中加入一下内容：

<typeAlias alias="StudentDtoClass" type="dto.StudentDto"></typeAlias>

<resultMap id="StudentDtoMap" class="StudentDtoClass">

<result property="id" column="ID" />

<result property="name" column="NAME" />

<result property="class_id" column="CLASS_ID" />

<result property="class_name" column="CLASS_NAME" />

</resultMap>

<sql id="select2">

<![CDATA[

select s.ID as id,s.NAME as name,s.CLASS_ID as class_id,c.name as class_name

from tbl_Student s,tbl_Class c

where s.class_id = c.id

]]>

</sql>

<select id="selectAll2" parameterClass="java.util.Map" resultMap="StudentDtoMap">

<include refid="select2" />

</select>

一定要注意写as后面的值，这样才能一一对应起来，否则会报错：未指定的列。