项目结构图
环境搭建
1.创建数据库
CREATE TABLE `teacher` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET=utf8
INSERT INTO teacher(`id`,`name`) VALUES (1,'李老师');
CREATE TABLE `student` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
`tid` INT(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fktid` (`tid`),
CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE= INNODB DEFAULT CHARSET=utf8
INSERT INTO student(`id`,`name`,`tid`) VALUES ('1','小明','1');
INSERT INTO student(`id`,`name`,`tid`) VALUES ('2','小张','1');
INSERT INTO student(`id`,`name`,`tid`) VALUES ('3','小李','1');
INSERT INTO student(`id`,`name`,`tid`) VALUES ('4','小红','1');
INSERT INTO student(`id`,`name`,`tid`) VALUES ('5','小王','1');
2.复制db.properties,mybatis-config.xml,MybatisUtils
3.创建实体类(使用Lombok插件,需要引入maven依赖)
Student
package com.codeyancy.pojo;
import lombok.Data;
@Data
public class Student {
private int id;
private String name;
//学生需要关联一个老师
private Teacher teacher;
}
Teacher
package com.codeyancy.pojo;
import lombok.Data;
@Data
public class Teacher {
private int id;
private String name;
}
4.编写实体类对应的Mapper接口 【两个】
StudentMapper
package com.codeyancy.dao;
import com.codeyancy.pojo.Student;
import java.util.List;
public interface StudentMapper {
}
TeacherMapper
package com.codeyancy.dao;
import com.codeyancy.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
public interface TeacherMapper {
}
5.编写Mapper接口对应的 mapper.xml配置文件 【两个】
StudentMapper.xml
<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.codeyancy.dao.StudentMapper">
</mapper>
TeacherMapper.xml
<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.codeyancy.dao.TeacherMapper">
</mapper>
6.修改mybatis-config.xml
<mappers>
<mapper resource="com/codeyancy/dao/TeacherMapper.xml "/>
<mapper resource="com/codeyancy/dao/StudentMapper.xml " />
</mappers>
环境搭建成功
多对一
多对一的理解:
- 多个学生对应一个老师
- 如果对于学生这边,就是一个多对一的现象,即从学生这边关联一个老师!
按查询嵌套处理 步骤(子查询)
1.编写StudentMapper
package com.codeyancy.dao;
import com.codeyancy.pojo.Student;
import java.util.List;
public interface StudentMapper {
//查询所有的学生信息,以及对应的老师的信息
public List<Student> getStudent();
}
2.编写StudentMapper.xml
<!--
思路:
1.查询所有学生的信息
2.根据查询出来的学生的tid,寻找对应的老师 子查询
-->
<select id="getStudent" resultMap="StudentTeacher">
select * from mybatis.student;
</select>
<resultMap id="StudentTeacher" type="Student">
<!--result只能针对单个属性-->
<result property="id" column="id" />
<result property="name" column="name" />
<!--复杂的属性需要单独处理 对象:association 集合:collection-->
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from mybatis.teacher where id = #{tid};
</select>
3.MyTest进行测试
@Test
public void testStudent(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentList = mapper.getStudent();
for (Student student : studentList) {
System.out.println(student);
}
sqlSession.close();
}
4.测试结果
按结果嵌套处理 步骤(连表查询)
1.编写StudentMapper
public List<Student> getStudent2();
2.编写StudentMapper.xml
<!--按照结果嵌套处理-->
<select id="getStudent2" resultMap="StudentTeacher2">
select s.id sid,s.name sname,t.name tname
from mybatis.student s,mybatis.teacher t
where s.tid = t.id;
</select>
<resultMap id="StudentTeacher2" type="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<association property="teacher" javaType="Teacher">
<result property="name" column="tname"/>
</association>
</resultMap>
3.MyTest进行测试
@Test
public void testStudent2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentList = mapper.getStudent2();
for (Student student : studentList) {
System.out.println(student);
}
sqlSession.close();
}
4.测试结果
一对多
一对多的理解:
- 一个老师拥有多个学生
- 如果对于老师这边,就是一个一对多的现象,即从一个老师下面拥有一群学生(集合)!
测试环境和以上相同,需要从新编写实体类
Student
package com.codeyancy.pojo;
import lombok.Data;
@Data
public class Student {
private int id;
private String name;
private int tid;
}
Teacher
package com.codeyancy.pojo;
import lombok.Data;
import java.util.List;
@Data
public class Teacher {
private int id;
private String name;
//一个老师要拥有多个学生
private List<Student> students;
}
按结果嵌套处理 (推荐使用)
- TeacherMapper接口编写方法
package com.codeyancy.dao;
import com.codeyancy.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface TeacherMapper {
//获取指定老师下的所有学生及老师的信息
Teacher getTeacher(@Param("tid") int id);
}
- 编写接口对应的Mapper配置文件(TeacherMapper.xml)
<!--按照结果来嵌套查询-->
<select id="getTeacher" resultMap="TeacherStudent">
select s.id sid, s.name sname, t.name tname, t.id tid
from student s,teacher t
where s.tid = t.id and t.id = #{tid}
</select>
<resultMap id="TeacherStudent" type="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname" />
<!--复杂的属性,我们需要单独处理 对象:association 集合:collection
javaType="" 指定属性的类型
集合中的泛型信息,用ofType获取
-->
<collection property="students" ofType="Student" >
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid" />
</collection>
</resultMap>
- 测试(MyTest)
@Test
public void test(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher(1);
System.out.println(teacher);
sqlSession.close();
}
4.测试结果
按查询嵌套处理
- TeacherMapper接口编写方法
Teacher getTeacher2(@Param("tid") int id);
- 编写接口对应的Mapper配置文件(TeacherMapper.xml)
<select id="getTeacher2" resultMap="TeacherStudent2">
select * from mybatis.teacher where id = #{tid}
</select>
<resultMap id="TeacherStudent2" type="Teacher">
<collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id"/>
</resultMap>
<select id="getStudentByTeacherId" resultType="Student">
select * from mybatis.student where tid = #{tid}
</select>
- 测试(MyTest)
@Test
public void test2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher2(1);
System.out.println(teacher);
sqlSession.close();
}
4.测试结果
小结
- 关联-association 【多对一】
- 集合-collection 【一对多】
- 所以association是用于一对一和多对一,而collection是用于一对多的关系
- JavaType和ofType都是用来指定对象类型的
- JavaType是用来指定pojo中属性的类型
- ofType指定的是映射到list集合属性中pojo的类型。
注意说明
- 保证SQL的可读性,尽量通俗易懂
- 根据实际要求,尽量编写性能更高的SQL语句
- 注意属性名和字段不一致的问题
- 注意一对多和多对一 中:字段和属性对应的问题
- 尽量使用Log4j,通过日志来查看自己的错误