一、环境搭建
1.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, '乔丹');
INSERT INTO teacher(`id`, `name`) VALUES (2, '科比');
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');
1.2 导入依赖
<dependencies>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.16</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
<scope>provided</scope>
</dependency>
</dependencies>
1.3 创建实体类
1.3.1 Student 实体类
@Data
@NoArgsConstructor
public class Student {
private int id;
private String name;
//学生要关联一个老师
private Teacher teacher;
}
1.3.2 Teacher 实体类
@Data
public class Teacher {
private int id;
private String name;
}
1.4 创建Mapper接口
1.4.1 StudentMapper 接口
public interface StudentMapper {
}
1.4.2 TeacherMapper 接口
public interface TeacherMapper {
@Select("select *from teacher where id=#{tid}")
Teacher getTeacher(@Param("tid") int id);
}
1.5 创建Mapper.xml文件
1.5.1 namespace 绑定StudentMapper接口
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kuang.dao.StudentMapper">
</mapper>
1.5.2 namespace 绑定TeacherMapper接口
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.kuang.dao.TeacherMapper"> </mapper>
1.6 核心配置文件中注册Mapper.xml文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<properties resource="db.properties"/>
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<typeAliases>
<package name="com.kuang.pojo"/>
<!-- <typeAlias type="com.kuang.pojo.User" alias="User"/>-->
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper class="com.kuang.dao.TeacherMapper"/>
<mapper class="com.kuang.dao.StudentMapper"/>
</mappers>
</configuration>
1.7 测试
@Test
public void getTeacher(){
SqlSession sqlSession = MybatisUtils.getSqlSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher(1);
System.out.println(teacher);
sqlSession.close();
}
测试结果
二、多对一复杂查询
2.1 按照查询嵌套处理
2.1.1 编写接口方法
public interface StudentMapper {
//查询所有的学生信息,以及对应的老师信息
List<Student> getStudent();
}
2.1.2 编写sql标签
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kuang.dao.StudentMapper">
<select id="getStudent" resultMap="StudentTeacher">
select *from student;
</select>
<resultMap id="StudentTeacher" type="Student">
<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 teacher where id=#{id}
</select>
</mapper>
2.1.3 测试
@Test
public void testStudent(){
//第一步获取sqlsession对象
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();
}
2.1.4 测试结果
2.2 按照结果嵌套处理
2.2.1 编写接口方法
public interface StudentMapper {
//查询所有的学生信息,以及对应的老师信息
List<Student> getStudent2();
}
2.2.2 编写sql标签
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kuang.dao.StudentMapper">
<!--按照结果嵌套查询-->
<select id="getStudent2" resultMap="StudentTeacher2">
select s.id sid,s.name sname,t.name tname
from student s,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>
</mapper>
2.2.3 测试
@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();
}
2.2.4 测试结果
三、一对多复杂查询
3.1 按照结果嵌套查询
3.1.1 学生实体类
@Data
public class Student1 {
private int id;
private String name;
private int tid;
}
3.1.2 教师实体类
@Data
public class Teacher1 {
private int id;
private String name;
//一个老师对应多个学生
private List<Student1> students;
}
3.1.3 编写接口
public interface TeacherMapper1 {
//获取指定老师下的所有学生及老师的信息
Teacher1 getTeacher2(@Param("tid") int id);
}
3.1.4 编写TeacherMapper1.xml文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kuang.dao.TeacherMapper1">
<select id="getTeacher1" resultType="Teacher1">
select *from mybatis.teacher;
</select>
<!--按照结果嵌套查询-->
<select id="getTeacher2" resultMap="TeacherStudent1">
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="TeacherStudent1" type="Teacher1">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<!--复杂的属性:我们要单独处理 对象:association 集合:collection -->
<!--javaType="" 指定属性的类型 集合中的泛型信息我们使用ofType获取-->
<collection property="student1s" ofType="Student1" javaType="java.util.List" >
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
</mapper>
3.1.5 测试
@Test
public void test2() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper1 mapper = sqlSession.getMapper(TeacherMapper1.class);
Teacher1 teacher = mapper.getTeacher2(1);
System.out.println(teacher);
}
3.1.6 测试结果
3.2 根据查询嵌套处理
3.2.1 编写接口
public interface TeacherMapper1 {
//获取指定老师下的所有学生及老师的信息
Teacher1 getTeacher3(@Param("tid") int id);
}
3.2.2 编写TeacherMapper1.xml文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kuang.dao.TeacherMapper1">
<select id="getTeacher3" resultType="com.kuang.pojo.Teacher1" resultMap="TeacherStudent2">
select *from mybatis.teacher where id = #{tid};
</select>
<resultMap id="TeacherStudent2" type="Teacher1">
<collection property="student1s" javaType="ArrayList" ofType="Student1" select="getStudentByTeacherId" column="id"/>
</resultMap>
<select id="getStudentByTeacherId" resultType="Student1">
select *from mybatis.student where id = #{tid}
</select>
</mapper>
3.2.3 测试
@Test
public void test2() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper1 mapper = sqlSession.getMapper(TeacherMapper1.class);
Teacher1 teacher = mapper.getTeacher3(1);
System.out.println(teacher);
}