mybatis一对一关联表查询

先创建一个表

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>
上一篇:Node & CLI


下一篇:Ubuntu 12.04下LAMP环境的搭建