mybatis入门笔记(中-1)

1、实现关联表查询

1.1一对一关联表查询

1.1.1创建表

教师表和班级表，班级表通过外键和教师表关联

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('Anhw');
INSERT INTO teacher(t_name) VALUES('Ang');
INSERT INTO class(c_name, teacher_id) VALUES('bj_a', 1);
INSERT INTO class(c_name, teacher_id) VALUES('bj_b', 2);

1.1.2定义实体类

public class Teacher{

private integer id;

private String name;

......

//getter和setter

//带参构造器

//无参构造器

//toString方法

}　

 

public class Classes {

private integer id;

private String name;

private Teacher teacher;

......

}　

1.1.3 创建sql映射文件ClassMapper.xml

<!--
方式一：嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集
封装联表查询的数据(去除重复的数据)
select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=1
-->
<select id="getClass" parameterType="int" resultMap="ClassResultMap">
select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=#{id}
</select>
<resultMap type="_Classes" id="ClassResultMap">
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<association property="teacher" column="teacher_id" javaType="_Teacher">
<id property="id" column="t_id"/>
<result property="name" column="t_name"/>
</association>
</resultMap>
<!--
方式二：嵌套查询：通过执行另外一个SQL 映射语句来返回预期的复杂类型
SELECT * FROM class WHERE c_id=1;
SELECT * FROM teacher WHERE t_id=1 //1 是上一个查询得到的teacher_id 的值
-->
<select id="getClass2" parameterType="int" resultMap="ClassResultMap2">
select * from class where c_id=#{id}
</select>
<resultMap type="_Classes" id="ClassResultMap2">
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<association property="teacher" column="teacher_id" javaType="Teacher"
select="getTeacher">
</association>
</resultMap>
<select id="getTeacher" parameterType="int" resultType="Teacher">
SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
</select>

//association用于一对一的关联查询

//property对象属性的名称

//javaType对象属性的类型 

//column所对应的外键字段名称

//select使用另一个查询封装的结果

1.1.4conf.xml中注册ClassMapper.xml文件

1.1.5测试

@Test
public void testO2O() {
SqlSession sqlSession = factory.openSession();
Classes c = sqlSession.selectOne("com.gsau.mybatis.test.OOMapper.getClass", 1);
System.out.println(c);
}
@Test
public void testO2O2() {
SqlSession sqlSession = factory.openSession();
Classes c = sqlSession.selectOne("com.gsau.mybatis.test.OOMapper.getClass2", 1);
System.out.println(c);
}

1.2一对多关联

1.2.1创建表

CREATE TABLE student(
s_id INT PRIMARY KEY AUTO_INCREMENT,
s_name VARCHAR(20),
class_id INT
);
INSERT INTO student(s_name, class_id) VALUES('xs_A', 1);
INSERT INTO student(s_name, class_id) VALUES('xs_B', 1);
INSERT INTO student(s_name, class_id) VALUES('xs_C', 1);
INSERT INTO student(s_name, class_id) VALUES('xs_D', 2);
INSERT INTO student(s_name, class_id) VALUES('xs_E', 2);
INSERT INTO student(s_name, class_id) VALUES('xs_F', 2);

1.2.2定义实体类

public class Student {

　　private Integer id;

　　private String name;

......

}

班级实体类增加学生集合，如下：

public class Classes {
private int id;
private String name;
private Teacher teacher;
private List<Student> students;
}

 

1.2.3定义sql映射文件

<!--
方式一: 嵌套结果: 使用嵌套结果映射来处理重复的联合结果的子集
SELECT * FROM class c, teacher t,student s WHERE c.teacher_id=t.t_id AND c.C_id=s.class_id AND c.c_id=1
-->
<select id="getClass3" parameterType="int" resultMap="ClassResultMap3">
select * from class c, teacher t,student s where c.teacher_id=t.t_id and c.C_id=s.class_id and
c.c_id=#{id}
</select>
<resultMap type="Classes" id="ClassResultMap3">
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<association property="teacher" column="teacher_id" javaType="Teacher">
<id property="id" column="t_id"/>
<result property="name" column="t_name"/>
</association>
<!-- ofType 指定students 集合中的对象类型 -->
<collection property="students" ofType="Student">
<id property="id" column="s_id"/>
<result property="name" column="s_name"/>
</collection>
</resultMap>
<!--
方式二：嵌套查询：通过执行另外一个SQL 映射语句来返回预期的复杂类型
SELECT * FROM class WHERE c_id=1;
SELECT * FROM teacher WHERE t_id=1 //1 是上一个查询得到的teacher_id 的值
SELECT * FROM student WHERE class_id=1 //1 是第一个查询得到的c_id 字段的值
-->
<select id="getClass4" parameterType="int" resultMap="ClassResultMap4">
select * from class where c_id=#{id}
</select>

<--!type：如果是包级别的别名映射就不用对pojo类取别名，直接指定其类名即可-->
<resultMap type="Classes" id="ClassResultMap4"

<id property="id" column="c_id"/>

<result property="name" column="c_name"/>
<association property="teacher" column="teacher_id" javaType="Teacher"
select="getTeacher2"></association>
<collection property="students" ofType="Student" column="c_id" select="getStudent"></collection>
</resultMap>
<select id="getTeacher2" parameterType="int" resultType="Teacher">
SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
</select>
<select id="getStudent" parameterType="int" resultType="Student">
SELECT s_id id, s_name name FROM student WHERE class_id=#{id}
</select>

1.2.4 在conf.xml中注册sql映射文件ClassMapper.xml

1.2.5测试

@Test
public void testOM() {
SqlSession sqlSession = factory.openSession();
Classes c = sqlSession.selectOne("com.gsau.mybatis.test.OOMapper.getClass3", 1);
System.out.println(c);
}
@Test
public void testOM2() {
SqlSession sqlSession = factory.openSession();
Classes c = sqlSession.selectOne("com.gsau.mybatis.test.OOMapper.getClass4", 1);
System.out.println(c);
}

//collection做一对多关联查询

//ofType指定集合中元素对象类型