Java框架--Mybaits（二）

3. 高级映射

前期准备：

数据库表

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `sid` int(0) NOT NULL AUTO_INCREMENT,
  `sname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `tid` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '小红', 1);
INSERT INTO `student` VALUES (2, '小明', 1);
INSERT INTO `student` VALUES (3, '小王', 1);
INSERT INTO `student` VALUES (4, '小赵', 1);

SET FOREIGN_KEY_CHECKS = 1;

====================================================>
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `tid` int(0) NOT NULL,
  `tname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`tid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, 'A老师');

SET FOREIGN_KEY_CHECKS = 1;

数据库结构图

image.png

image.png

3.1 多对一

多对一的意思是指多个学生对应一个老师，有可能是一个实体类A对象里面有另外一个实体类B对象，但是数据库查询的时候并没有B对象的属性的情况使用resultMap进行高级映射

示例：查询学生和老师姓名的对应关系

3.1.1 创建实体类

学生对象包含老师对象

package pojo;

public class Student {

    private int id;
    private String name;
    //老师对象存在
    private Teacher teacher;
    //getter&setter省路
}

教师对象

package pojo;

import java.util.List;

public class Teacher {
    private int id;
    private String name;

3.1.2 创建查询SQL语句

可以看到查询语句中对应的Java实体类Student对象中并没有tname的字段，只有teacher对象，这种情况下就需要用resultMap来进行映射

SELECT s.sid,s.sname,t.tname FROM student s INNER JOIN teacher t ON s.tid = t.tid

3.1.3 创建StudentMapper.xml

property：在Java程序中的字段
column：在数据库中的字段
type：返回的类型

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="dao.StudentMapper">
    <select id="getStudent" resultMap="studentTeacher">
        SELECT s.sid,s.sname,t.tname FROM student s INNER JOIN teacher t ON s.tid = t.tid
    </select>

    <resultMap id="studentTeacher" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="id" column="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>
</mapper>

3.1.4 创建StudentMapper接口

package dao;

import pojo.Student;
import pojo.Teacher;

import java.util.List;

public interface StudentMapper {
    List<Student> getStudent();
}

3.1.5 创建测试类

public class MybatisTest {
    public static void main(String[] args) throws Exception{

        SqlSession sqlSession = MybatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        List<Student> students = mapper.getStudent();

       for (Student student:students) {
           System.out.println(student);
        }
    }
}

3.2 多对一

和一对多情况类似，就是一个老师对应多个学生，创建教师实体类时包含了一个集合对象

3.2.1 创建实体类对象

包含学生集合的情况

package pojo;

import java.util.List;

public class Teacher {

    private int id;
    private String name;
    private List<Student> students;
   // getter&setter
}

学生实体类

package pojo;

public class Student {

    private int id;
    private String name;
    private int tid;
 // getter&setter
}

3.2.2 创建查询语句

老师的实体类中并没有学生对象的属性只有集合，所以需要resultMap对集合进行映射

SELECT t.tid,t.tname,s.sid,s.sname FROM student s INNER JOIN teacher t ON s.tid = t.tid

3.2.3 创建TeacherMapper.xml

ofType：要查出集合元素的类型

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="dao.TeacherMapper">

    <select id="getTeacher" resultMap="teacherStudent">
        SELECT t.tid,t.tname,s.sid,s.sname FROM student s INNER JOIN teacher t ON s.tid = t.tid
    </select>

    <resultMap id="teacherStudent" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>

        <collection property="students" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>

</mapper>

3.2.4 创建TeacherMapper接口

package dao;

import pojo.Student;
import pojo.Teacher;

import java.util.List;

public interface UserMapper {
   List<Teacher> getTeacher();
}

3.2.5 创建测试类

public class MybatisTest {
    public static void main(String[] args) throws Exception{

        SqlSession sqlSession = MybatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<Teacher> teachers = mapper.getTeacher();
        for (Teacher teacher:teachers) {
            System.out.println(teacher);
        }
  }
}

4. 动态SQL

动态SQL就是根据不同的条件进行SQL的拼接，实现动态SQL需要使用标签跟JSTL类似

if
choose (when, otherwise)
trim (where, set)
foreach

4.1 数据库环境准备

image.png

4.2 if标签

test=""内部写判断条件即可

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="dao.BlogMapper">
  <select id="queryBlog1" parameterType="map" resultType="pojo.Blog">
    select * from blog where 1=1
    <if test="title != null">
      and title = #{title}
    </if>
    <if test="author != null">
      and author = #{author}
    </if>
  </select>
</mapper>

代码示例

DAO层

public interface BlogMapper {
    List<Blog> queryBlog1(Map map);
}

测试类

public class MybatisTest {
    public static void main(String[] args) throws Exception{
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
       
        Map<String,String> map1 = new HashMap<>();
        map1.put("title","Java如此简单");

        List<Blog> blogs = mapper.queryBlog1(map1);

        for (Blog blog:blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
 }
}

效果展示

image.png

补充

可以把1=1 换成<where>标签下面代码仅修改了BlogMapper.xml代码

  <select id="queryBlog2" parameterType="map" resultType="pojo.Blog">
    select * from blog
    <where>
      <if test="title != null">
        and title = #{title}
      </if>
      <if test="author != null">
        and author = #{author}
      </if>
    </where>
  </select>

4.2 choose标签

<when>：相当于case
<otherwise>：相当于default

<select id="queryBlog3" parameterType="map" resultType="pojo.Blog">
    select * from blog
    <where>
      <choose>
        <when test="title != null">and title = #{title}</when>
        <when test="author != null">and author = #{author}</when>
        <otherwise>views = 5000</otherwise>
      </choose>
    </where>
  </select>

4.3 set标签

<update id="updateBlog" parameterType="map">
    update blog
    <set>
      <if test="title != null">title = #{title}</if>
      <if test="author != null">author = #{author}</if>
    </set>
    where id = #{id}
  </update>

4.4 foreach标签

TODO有用到再更新