首先,准备好MyBatis需要的jar包。下面以对教师信息,学生信息的增删改查作为例子。
创建教师信息表与学生信息表
create table teacher(
id NUMBER(8,2) not null,
name varchar(30),/*名称*/
salary number(8,2),/*薪资*/
title varchar(20),/*职称*/
gender char(1)/*性别*/
);
create table student(
id NUMBER(8,2) not null,
tid number(8,2),/*对应teacher表id*/
studentId number(8,2),/*学号*/
name varchar(30),/*名称*/
gender char(1)/*性别*/
);
Teacher表数据
Student表数据
创建教师信息与学生信息JavaBean
package com.qh.test.pojo;
import java.util.List;
/**
*@author Michael Ng
*@version 创建时间:2014-4-9 下午02:52:03
*/
public class Teacher {
private int id;
private int age; //年龄
private String name;//姓名
private String title;//职称
private String gender;//性别
private double salary;//薪资
private List<Student> students;
public List<Student> getStudents() {
return students;
}
public void setStudents(List<Student> students) {
this.students = students;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public double getSalary() {
return salary;
}
public void setSalary(double salary) {
this.salary = salary;
}
}
package com.qh.test.pojo;
/**
*@author Michael Ng
*@version 创建时间:2014-4-9 下午02:52:03
*/
public class Student {
private int id;
private int age; // 年龄
private int tid; // 对应teacher表id
private int studentId; // 学号
private String gender;// 性别
private String name;//姓名
private Teacher teacher;
public Teacher getTeacher() {
return teacher;
}
public void setTeacher(Teacher teacher) {
this.teacher = teacher;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public int getTid() {
return tid;
}
public void setTid(int tid) {
this.tid = tid;
}
public int getStudentId() {
return studentId;
}
public void setStudentId(int studentId) {
this.studentId = studentId;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
}
定义Mapper接口
package com.qh.test.mapper;
import java.util.List;
import com.qh.test.pojo.Student;
import com.qh.test.pojo.Teacher;
/**
*@author Michael Ng
*@version 创建时间:2014-4-9 下午02:59:31
*/
public interface DemoMapper {
/**
* 添加教师信息
*/
public int addTeacher(Teacher t);
/**
* 查询教师信息
* @return
*/
public Teacher getSingleTacher(Teacher t);
/**
* 修改教师信息
*/
public int updateTeacher(Teacher t);
/**
* 删除教师信息
*/
public int deleteTeacher(Teacher t);
/**
* 获取教师信息集合
*/
public List<Teacher> getTeachers();
/**
* 获取全部教师信息
* @return
*/
public List<Teacher> getAllTeacher();
/***
* 获取全部学生信息
*/
public List<Student> getAllStudent();
/**
* 获取学生信息
*/
public List<Student> getStudents();
/**
* 根据id获取学生信息
* @param id
* @return
*/
public Student getStudentById(int id);
/**
* 根据id获取教师信息
* @param id
* @return
*/
public Student getTeacherById(int id);
/**
* 获取教师信息
* @return
*/
public List<Teacher> getStMsg();
}
定义service接口与其实现类
package com.qh.test.service;
import java.util.List;
import com.qh.test.pojo.Student;
import com.qh.test.pojo.Teacher;
/**
*@author Michael Ng
*@version 创建时间:2014-4-9 下午03:00:42
*/
public interface DemoService {
/**
* 添加教师信息
*/
public Teacher addTeacher(Teacher t);
/**
* 查询教师信息
* @return
*/
public Teacher getSingleTacher(Teacher t);
/**
* 修改教师信息
*/
public int updateTeacher(Teacher t);
/**
* 删除教师信息
*/
public int deleteTeacher(Teacher t);
/**
* 获取全部教师信息
* @return
*/
public List<Teacher> getAllTeacher();
/***
* 获取全部学生信息
*/
public List<Student> getAllStudent();
/**
* 获取教师信息集合
*/
public List<Teacher> getTeachers();
/**
* 获取学生信息
*/
public List<Student> getStudents();
/**
* 根据id获取学生信息
* @param id
* @return
*/
public Student getStudentById(int id);
/**
* 根据id获取教师信息
* @param id
* @return
*/
public Student getTeacherById(int id);
/**
* 获取教师信息
* @return
*/
public List<Teacher> getStMsg();
}
package com.qh.test.service.impl;
import java.util.List;
import com.qh.test.mapper.DemoMapper;
import com.qh.test.pojo.Student;
import com.qh.test.pojo.Teacher;
import com.qh.test.service.DemoService;
/**
*@author Michael Ng
*@version 创建时间:2014-4-9 下午03:01:23
*/
public class DemoServiceImpl implements DemoService {
private DemoMapper mapper;
public DemoMapper getMapper() {
return mapper;
}
public void setMapper(DemoMapper mapper) {
this.mapper = mapper;
}
/**
* 添加教师信息
*/
@Override
public Teacher addTeacher(Teacher t) {
if (mapper.addTeacher(t) > 0) {
return t;
} else {
return null;
}
}
/**
* 查询教师信息
*/
@Override
public Teacher getSingleTacher(Teacher t) {
return mapper.getSingleTacher(t);
}
/**
* 修改教师信息
*/
@Override
public int updateTeacher(Teacher t) {
return mapper.updateTeacher(t);
}
/**
* 删除教师信息
*/
@Override
public int deleteTeacher(Teacher t) {
return mapper.deleteTeacher(t);
}
/**
* 获取教师信息集合
*/
@Override
public List<Teacher> getTeachers() {
return mapper.getTeachers();
}
/**
* 获取全部教师信息
* @return
*/
@Override
public List<Teacher> getAllTeacher() {
return mapper.getAllTeacher();
}
/**
* 获取全部学生信息
*/
@Override
public List<Student> getAllStudent() {
return mapper.getAllStudent();
}
/**
* 获取学生信息
*/
@Override
public List<Student> getStudents() {
return mapper.getStudents();
}
/**
* 根据id获取学生信息
* @param id
* @return
*/
@Override
public Student getStudentById(int id) {
return mapper.getStudentById(id);
}
/**
* 根据id获取教师信息
* @param id
* @return
*/
public Student getTeacherById(int id){
return mapper.getTeacherById(id);
}
/**
* 获取教师信息
* @return
*/
public List<Teacher> getStMsg(){
return mapper.getStMsg();
}
}
DemoMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qh.test.mapper.DemoMapper">
<!-- 集合的嵌套结果 (单向一对多) start -->
<resultMap type="teacher" id="teacherList">
<id property="id" column="id"/>
<result property="name" column="t_name"/>
<result property="age" column="age"/>
<result property="title" column="title"/>
<result property="gender" column="gender"/>
<result property="salary" column="salary"/>
<collection property="students" resultMap="studentList"></collection>
</resultMap>
<resultMap type="student" id="studentList">
<id property="id" column="s_id"/>
<result property="name" column="s_name"/>
<result property="tid" column="s_tid"/>
<result property="studentId" column="s_sid"/>
<result property="gender" column="s_gender"/>
</resultMap>
<!-- 查询教师信息集合 -->
<select id="getTeachers" resultMap="teacherList">
select t.id,t.t_name,t.SALARY,t.TITLE,t.GENDER ,
s.id s_id,s.NAME s_name,s.STUDENTID s_sid ,
s.TID s_tid ,s.GENDER s_gender
from teacher t
left join student s on s.tid = t.id
</select>
<!-- 集合的嵌套结果 end -->
<!-- 集合的嵌套查询 (单向一对多) start -->
<resultMap type="teacher" id="teachers">
<collection property="students" column="id" ofType="Student" select="getStudentById"></collection>
<!--这里column=id 指定的是teacher的id -->
</resultMap>
<!-- 获取教师信息 -->
<select id="getAllTeacher" resultMap="teachers">
select * from teacher t
</select>
<!-- 获取学生信息 -->
<select id="getStudentById" resultType="student">
select * from student s where s.tid = #{id}
</select>
<!-- 集合的嵌套查询 end -->
<!-- 关联的嵌套查询 (单向多对一) start -->
<resultMap type="student" id="students">
<association property="teacher" column="tid" javaType="Teacher" select="getTeacherById"></association>
</resultMap>
<!--获取学生信息 -->
<select id="getAllStudent" resultMap="students">
select * from student
</select>
<!-- 获取教师信息 -->
<select id="getTeacherById" resultType="teacher">
select ID,T_NAME name,SALARY,TITLE,GENDER from teacher t where t.id = #{id}
</select>
<!-- 关联的嵌套查询 end -->
<!-- 关联的嵌套结果查询 (单向多对一)start -->
<resultMap type="teacher" id="singleTeacher">
<id property="id" column="id"/>
<result property="name" column="t_name"/>
<result property="age" column="age"/>
<result property="title" column="title"/>
<result property="gender" column="gender"/>
<result property="salary" column="salary"/>
</resultMap>
<resultMap type="student" id="assoStudent">
<id property="id" column="s_id"/>
<result property="name" column="s_name"/>
<result property="tid" column="s_tid"/>
<result property="studentId" column="s_sid"/>
<result property="gender" column="s_gender"/>
<association property="teacher" resultMap="singleTeacher"></association>
</resultMap>
<select id="getStudents" resultMap="assoStudent">
select t.id,t.t_name,t.SALARY,t.TITLE,t.GENDER ,
s.id s_id,s.NAME s_name,s.STUDENTID s_sid ,
s.TID s_tid ,s.GENDER s_gender
from teacher t
left join student s on s.tid = t.id
</select>
<!-- 关联的嵌套结果查询 end -->
<!-- 添加教师信息 -->
<insert id="addTeacher" parameterType="teacher" >
insert into teacher(ID,NAME,SALARY,TITLE,GENDER) values(#{id},#{name},#{salary},#{title},#{gender})
</insert>
<!-- 查询教师信息 -->
<select id="getSingleTacher" parameterType="teacher" resultType="teacher">
select * from teacher t
<where>
t.id = #{id}
</where>
</select>
<!-- 修改教师信息 -->
<update id="updateTeacher">
<!--第一种写法 -->
<!-- update teacher t set t.NAME = #{name,jdbcType=VARCHAR},t.SALARY = #{salary,jdbcType=NUMERIC} WHERE t.id = #{id}-->
<!-- 第二种写法 -->
update teacher t
<set>
t.NAME = #{name,jdbcType=VARCHAR}
,t.SALARY = #{salary,jdbcType=NUMERIC}
</set>
<where>
t.id = #{id}
</where>
</update>
<!-- 删除教师信息 -->
<delete id="deleteTeacher">
delete from teacher t where t.id= 1
</delete>
<!-- 双向的一对多 start-->
<resultMap type="teacher" id="twoSideTeacher">
<id property="id" column="id"/>
<result property="name" column="t_name"/>
<result property="age" column="age"/>
<result property="title" column="title"/>
<result property="gender" column="gender"/>
<result property="salary" column="salary"/>
<collection property="students" resultMap="twoSideStudent"></collection>
</resultMap>
<resultMap type="student" id="twoSideStudent">
<id property="id" column="s_id"/>
<result property="name" column="s_name"/>
<result property="tid" column="s_tid"/>
<result property="studentId" column="s_sid"/>
<result property="gender" column="s_gender"/>
<association property="teacher" resultMap="twoSideTeacher"></association>
</resultMap>
<select id="getStMsg" resultMap="twoSideTeacher">
select t.id,t.t_name,t.SALARY,t.TITLE,t.GENDER ,
s.id s_id,s.NAME s_name,s.STUDENTID s_sid ,
s.TID s_tid ,s.GENDER s_gender
from teacher t
left join student s on s.tid = t.id
</select>
<!-- 双向的一对多 end -->
</mapper>
定义configuration.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>
<!--懒加载-->
<settings>
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
<typeAliases>
<typeAlias type="com.qh.test.pojo.Teacher" alias="teacher"/>
<typeAlias type="com.qh.test.pojo.Student" alias="student"/>
</typeAliases>
<mappers>
<mapper resource="com/qh/test/mapper/DemoMapper.xml" />
</mappers>
</configuration>
在configuration.xml中定义了教师信息的别名,所以在demoMapper.xml文件中就可以使用该别名了,不用输入类的全路径
Junit测试
package com.qh.test;
import java.util.List;
import org.junit.Test;
import test.MyBatisUtil;
import com.qh.test.pojo.Student;
import com.qh.test.pojo.Teacher;
import com.qh.test.service.DemoService;
/**
*@author Michael Ng
*@version 创建时间:2014-3-27 下午04:01:13
*/
public class MyBatisTest {
/**
* 添加教师信息
*/
@Test
public void addTeacher(){
DemoService service = (DemoService) MyBatisUtil.ac.getBean("demoService");
Teacher t = new Teacher();
t.setId(2);
t.setGender("1");
t.setAge(28);
t.setName("Michael");
t.setSalary(6000.0);
t.setTitle("高级叫兽");
System.out.println(service.addTeacher(t));
}
/**
* 查询教师信息
*/
@Test
public void selectTeacher(){
DemoService service = (DemoService) MyBatisUtil.ac.getBean("demoService");
Teacher t = new Teacher();
t.setId(1);
Teacher teacher = service.getSingleTacher(t);
System.out.println(teacher.getName());
}
/**
* 修改教师信息
*/
@Test
public void updateTeacher(){
DemoService service = (DemoService) MyBatisUtil.ac.getBean("demoService");
Teacher t = new Teacher();
t.setId(2);
t.setName("jason");
t.setSalary(200.0);
System.out.println(service.updateTeacher(t));
}
/**
* 删除教师信息
*/
@Test
public void deleteTeacher(){
DemoService service = (DemoService) MyBatisUtil.ac.getBean("demoService");
Teacher t = new Teacher();
t.setId(1);
System.out.println(service.deleteTeacher(t));
}
/**
* 获取教师信息集合(使用集合的嵌套结果查询学生信息,没有N+1问题)
*/
@Test
public void getTeachers(){
DemoService service = (DemoService) MyBatisUtil.ac.getBean("demoService");
List<Teacher> list = service.getTeachers();
if(list != null){
for(Teacher t : list){
System.out.println(t.getName()+"教师的学生名单");
for(Student s : t.getStudents()){
System.out.println("学生名称:"+s.getName());
}
}
}
}
/**
* 获取教师信息集合(使用集合的嵌套查询学生信息,有N+1问题)
*/
@Test
public void getAllTeacher(){
DemoService service = (DemoService) MyBatisUtil.ac.getBean("demoService");
List<Teacher> list = service.getAllTeacher();
if(list != null){
for(Teacher t : list){
System.out.println(t.getStudents());
}
}
}
/**
* 获取学生信息集合(使用关联的嵌套查询教师信息 ,有n+1问题)
*/
@Test
public void getAllStudent(){
DemoService service = (DemoService) MyBatisUtil.ac.getBean("demoService");
List<Student> list = service.getAllStudent();
if(list != null){
for(Student s : list){
System.out.println(s.getTeacher().getName());
}
}
}
/**
* 获取学生信息集合(使用关联的嵌套结果查询教师信息,没有N+1问题)
*/
@Test
public void getStudents(){
DemoService service = (DemoService) MyBatisUtil.ac.getBean("demoService");
List<Student> list = service.getStudents();
if(list != null){
for(Student s : list){
System.out.println(s.getTeacher().getName());
}
}
}
/**
* 获取教师信息(双向多对一)
*/
@Test
public void getStMsg(){
DemoService service = (DemoService) MyBatisUtil.ac.getBean("demoService");
List<Teacher> list = service.getStMsg();
if(list != null){
for(Teacher t : list){
System.out.println(t.getName()+"教师一共有"+t.getStudents().size()+"个学生");
for(Student s :t.getStudents()){
System.out.println(s.getTeacher()==null?"null":"学生"+s.getName()+"的教师名称为"+s.getTeacher().getName());
}
}
}
}
}
在DemoMapper.xml中获取教师信息集合,
<select id="getTeachers" resultType="teacher">
select NAME,SALARY, TITLE, GENDER from teacher t
</select>
我用了resultType来接受返回的数据。这里用resultType的原因主要是teacher这个pojo属性名与数据库表teacher中的字段名是相同,MyBatis会把teacher表字段值都映射到与之相对应pojo属性中。
当pojo的属性名与数据库表teacher中的字段名不相同时(比如:Teacher表name字段名改成t_Name),该怎么办??
1.这情况可以在sql语句中使用别名,使其与pojo属性相同,这样映射也是没有问题的。 例如:
<select id="getTeachers" resultType="teacher">
select T_NAME name,SALARY, TITLE, GENDER from teacher t
</select>
运行junit测试文件HotelOrderTest中的getTeachers()方法,后台会打印出owen(前提是你的数据库中得有这条数据),如果你不使用别名,后台打印应出null.
2.也可以配置一个<resultMap>
<resultMap type="teacher" id="teacherList">
<id property="id" column="id"/>
<result property="name" column="t_name"/>
<result property="age" column="age"/>
<result property="title" column="title"/>
<result property="gender" column="gender"/>
<result property="salary" column="salary"/>
</resultMap>
把DemoMapper.xml中的获取教师信息改成:
<select id="getTeachers" resultMap="teacherList">
select T_NAME ,SALARY, TITLE, GENDER from teacher t
</select>
运行junit测试文件HotelOrderTest中的getTeachers()方法,后台也会打印出owen
比较这两种方法,你可能觉得为何要配置<resultMap>这么麻烦,用个别名使用resultType不就搞定了么。这里用<resultMap>是有点杀鸡用牛刀了,其实<resultMap>还有更大的作用。
MyBatis 一对多(单向)
运行junit测试文件MyBatisTest.java中的getAllTeacher()方法 结果如下:
从上图看出,获取出教师信息之后,再根据教师id去数据库获取出其下面的学生信息。这样就形成N+1问题了。
怎样去避免N+1问题呢? 那就是用集合的嵌套结果,在DemoMapper文件中已经标明了注释那块是集合的嵌套结果。
运行测试文件MyBatisTest.java中的 getTeachers()方法,结果如下图:
从上图中可以看出,只发出一条sql语句,就拿到全部的数据了,从而避免的N+1问题。
MyBatis 多对一(单向)
MyBatis的多对一和一对多一样,可以使用关联的嵌套查询(有N+1问题),也可以使用关联的嵌套结果(没有N+1问题)。
首先来看采用关联的嵌套查询(这个已经在DemoMapper.xml中实现了,详细请看DemoMapper.xml中的注释)来实现多对一
运行测试文件 MyBatisTest.java中的 getAllStudent()方法,结果如下图:
从上图中可以看出,先获取出学生信息,根据学生信息中的教师id ,再去数据库获取其相对应的教师信息。每一个学生都会再发出一条sql语句去获取
其教师信息,所以造成的N+1问题。
再来看使用关联的嵌套结果(这个方式已经在DemoMapper.xml中实现了,详细请看DemoMapper.xml中的注释)实现MyBatis多对一
运行测试文件MyBatisTest.java中的getStudents()方法,结果如下图:
从上图可以看出只发出一条sql语句。
MyBatis的一对多(双向)
已经在DemoMapper.xml中实现了,详细请看DemoMapper.xml中的注释
运行测试文件MyBatisTest.java中的getStMsg()方法,结果如下图:
分析上图的结果,从教师信息中可以获取到学生信息,反过来,从学生信息中也可以获取出教师的信息。
我的博客只是用来记录在开发过程用到的技术,以便日后学习!
欢迎各位评论和给出你们的建议,谢谢!