先创建一个表
CREATE TABLE teacher(
t_id INT PRIMARY KEY AUTO_INCREMENT,
t_name VARCHAR(20)
);
CREATE TABLE class(
c_id INT PRIMARY KEY AUTO_INCREMENT,
c_name VARCHAR(20),
teacher_id INT
);
ALTER TABLE class ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES
teacher(t_id);
INSERT INTO teacher(t_name) VALUES('LS1');
INSERT INTO teacher(t_name) VALUES('LS2');
INSERT INTO class(c_name, teacher_id) VALUES('bj_a', 1);
INSERT INTO class(c_name, teacher_id) VALUES('bj_b', 2);
定义实体类：
public class Teacher {
private int id;
private String name;
}
public class Classes {
private int id;
private String name;
private Teacher teacher;
}
想要获得两个表显示的内容SQL有两种查询方式：
select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=#{id} //联表查询
SELECT * FROM class WHERE c_id=1;
SELECT * FROM teacher WHERE t_id=1 //1 是上一个查询得到的 teacher_id 的值 //多表查询
针对第一种方式的映射文件：
<select id="getClass" parameterType="int" resultMap="getClassMap">
select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=#{id}
</select>
<resultMap type="Classes" id="getClassMap">
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<association property="teacher" javaType="Teacher">
<id property="id" column="t_id"/>
<result property="name" column="t_name"/>
</association>
</resultMap>
针对第二种方式的映射文件
<select id="getClass2" resultMap="getClass2Map">
select * from class where c_id=#{id}
</select>
<select id="getTeacher" resultType="Teacher">
select t_id id, t_name name from teacher where t_id=#{id}
</select>
<resultMap type="Classes" id="getClass2Map">
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<association property="teacher" column="teacher_id" select="getTeacher">
</association>
</resultMap>