Mybatis多对一和一对多
一、多对一
-
概述
即多个对象对应一个对象,例如多个学生对应一个老师
-
association — 联系 ,关联 多个人可以关联一个人。
-
collection — 集合 一个人有一个集合,包含多个人。
-
发现是多对一业务情况,我们需要使用association 标签进行关联
例如,现在有两张表,student表和teacher表,如下图,两张表之间用tid和id进行关联。现需要查询学生id,name,还有老师的name,因此需要连表查询
pojo实体类
package org.westos.pojo;
public class Teacher {
private int id;
private String name;
public Teacher() {
}
public Teacher(int id, String name) {
this.id = id;
this.name = name;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Teacher{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
package org.westos.pojo;
public class Student {
private int id;
private String name;
private Teacher teacher;
public Student() {
}
public Student(int id, String name, Teacher teacher) {
this.id = id;
this.name = name;
this.teacher = teacher;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Teacher getTeacher() {
return teacher;
}
public void setTeacher(Teacher teacher) {
this.teacher = teacher;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", teacher=" + teacher +
'}';
}
}
dao层接口:
import org.westos.pojo.Student;
import java.util.List;
public interface StudentDao {
//获得全部学生的信息以及对应的老师【方法一】
List<Student> getStudents();
//获得全部学生的信息以及对应的老师【方法二】
List<Student> getStudentsTwo();
}
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">
<!--namespace不能写别名-->
<mapper namespace="org.westos.dao.StudentDao">
<!--遇到问题:学生类中关联老师: 多个学生对应一个老师 -->
<!--<select id="getStudents" resultType="Student">-->
<!--select s.id,s.name,t.name from mybatis.student as s,mybatis.teacher as t-->
<!--where s.tid = t.id-->
<!--</select>-->
<!--解决问题方式一:按查询结果嵌套处理,模拟数据库思想;-->
<select id="getStudents" resultMap="StudentTeacher">
select * from mybatis.student
</select>
<resultMap id="StudentTeacher" type="Student">
<id column="id" property="id"/>
<result column="name" property="name"/>
<!--属性和字段对应 , 类和表对应 , 对象和记录
关联一个字段
需求:拿到老师这个类的属性
association : 关联,多对一
column : 数据库对应的列名
property : 对应属性名
javaType : 多对一字段对应的Java类型
select : 关联一个语句
-->
<association column="tid" property="teacher" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from mybatis.teacher where id = #{id}
</select>
<!-- 解决方式二:一个resultMap解决 , 模拟面向对象的思想-->
<select id="getStudentsTwo" resultMap="StudentTeacher2">
select s.id,s.name,t.id as tid,t.name as tname
from mybatis.student as s, mybatis.teacher as t
where s.tid = t.id
</select>
<!--设置结果集映射ResultMap -->
<resultMap id="StudentTeacher2" type="Student">
<id property="id" column="id"/>
<result property="name" column="name"/>
<!--直接关联一个老师-->
<association property="teacher" javaType="Teacher">
<id property="id" column="tid"/>
<result property="name" column="tname"/>
</association>
</resultMap>
</mapper>
测试类:
package org.westos.dao;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import org.westos.pojo.Student;
import org.westos.utils.MyBatisUtils;
import java.util.List;
public class StudentDaoTest {
@Test
public void getStudents(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
StudentDao mapper = sqlSession.getMapper(StudentDao.class);
List<Student> students = mapper.getStudents();
for (Student student : students) {
System.out.println("学生姓名:"+student.getName()+"\t老师姓名:"+student.getTeacher().getName());
}
}
@Test
public void getStudentsTwo(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
StudentDao mapper = sqlSession.getMapper(StudentDao.class);
List<Student> students = mapper.getStudentsTwo();
for (Student student : students) {
System.out.println("学生姓名:"+student.getName()+"\t老师姓名:"+student.getTeacher().getName()
+student.getTeacher().getId());
}
}
}
两种方式都能达到想要的效果,查询结果如下:
二、一对多
pojo类:
package org.westos.pojo;
import java.util.List;
public class Teacher {
private int id;
private String name;
private List<Student> students;
public Teacher() {
}
public Teacher(int id, String name, List<Student> students) {
this.id = id;
this.name = name;
this.students = students;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<Student> getStudents() {
return students;
}
public void setStudents(List<Student> students) {
this.students = students;
}
@Override
public String toString() {
return "Teacher{" +
"id=" + id +
", name='" + name + '\'' +"\n"+
", students=" + students +"\n"+
'}';
}
}
dao层接口:
package org.westos.dao;
import org.westos.pojo.Teacher;
public interface TeacherDao {
Teacher getTeacher(int id);
Teacher getTeacher2(int id);
}
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="org.westos.dao.TeacherDao">
<!--数据库思想-->
<select id="getTeacher" resultMap="TeacherStudent">
select * from mybatis.teacher where id = #{id}
</select>
<resultMap id="TeacherStudent" type="Teacher">
<collection property="students" column="id" javaType="ArrayList" ofType="Student" select="student"/>
</resultMap>
<select id="student" resultType="Student">
select * from mybatis.student s,mybatis.teacher t where s.tid=t.id
</select>
<!--面向对象思想-->
<select id="getTeacher2" resultMap="TeacherStudent2">
select s.name sname,s.id sid,t.id tid, t.name tname
from mybatis.student as s,mybatis.teacher as t
where s.tid = t.id and t.id = #{id}
</select>
<resultMap id="TeacherStudent2" type="Teacher">
<result property="name" column="tname"/>
<collection property="students" ofType="Student">
<id property="id" column="sid"/>
<result property="name" column="sname"/>
</collection>
</resultMap>
</mapper>
测试代码:
package org.westos.dao;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import org.westos.pojo.Teacher;
import org.westos.utils.MyBatisUtils;
public class TestTeacherMapper {
@Test
public void test(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
TeacherDao mapper = sqlSession.getMapper(TeacherDao.class);
Teacher teacher = mapper.getTeacher(1);
System.out.println(teacher);
}
@Test
public void test2(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
TeacherDao mapper = sqlSession.getMapper(TeacherDao.class);
Teacher teacher2 = mapper.getTeacher2(1);
System.out.println(teacher2);
}
}
运行结果:
三、动态SQL
动态SQL就是指根据不同查询条件,生成不同的SQL语句,在 Mybatis之前的版本中,有很多元素需要花时间了解。Mybatis3 大大精简了元素种类,现在只需学习原来一半的元素便可。Mybatis采用功能强大的基于 OGNL 的表达式来淘汰其它大部分元素。
if
参数test:里面的表达式如果为ture则执行,否则不执行,且test为必填属性
案例:通过多个条件(可能为一个,也可能为两个)查询获取值
<select id="getBillList" resultType="Bill">
select b.*,p.proName from smbms.smbms_bill b,smbms.smbms_provider p
where b.providerId=p.id
<if test="providerName!=null">and p.proName like CONCAT('%',#{providerName}'%')</if>
<if test="providerId!=null">and b.providerId=#{providerId}</if>
order by b.id asc limit #{currentPage},#{pageSize}
</select>
上面案例中两个第一个if表示如果providerName不为空则进行其后面的模糊查询,第二个if表示如果providerName不为空则执行其后的为providerId赋值操作