Day37——MyBatis之动态sql语句

MyBatis part2

---

---

在认识多对一与一对多之前，个人学完后的感觉是像在学习MySQL语句中的联表查询（先有两张表，通过他们之间存在的相同键值，使两者联系起来），将那些语句写在了操作数据库的Java程序中。

学习前的准备工作：有两张表，一张为Student表，一张为Teacher表。

首先，两个表之间进行联系，Student表中的tid列对应Teacher表中的id列，这样两个表可以联系起来，方便后面的增删改查。接着认识多对一是用在什么环境下

多对一处理

当有多个学生，对应着一个老师，我们想查询所有的学生信息，并且还想知道他们对应的老师是谁？

进行测试：

1、导入所需依赖

<!-- 导入依赖 -->
<dependencies>
    <!-- 单元测试的包 Junit -->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.10</version>
    </dependency>
    <!-- Mybatis -->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.4</version>
    </dependency>
    <!-- 操作数据库 -->
    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
    </dependency>
    <!-- lombok -->
    <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.12</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/log4j/log4j -->
    <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>1.2.17</version>
    </dependency>
</dependencies>

2、建立与两张表对应的实体类Teacher，Student

@Data
public class Teacher {
    private int id;
    private String name;
}

@Data
public class Student {
    private int id;
    private String name;
    private int tid;
    
    private Teacher teacher;//因为要显示出学生对应的老师是谁
}

3、建立对应的Mapper接口

public interface StudentMapper {

    List<Student> getStudents();
}


public interface TeacherMapper {


}

4、建立对应的Mapper.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.feng.mapper.StudentMapper">

    <resultMap id="StudentTeacher" type="Student">
        <id 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>

    <select id="getStudents" resultMap="StudentTeacher">
        select s.id sid,s.name sname,t.name tname,t.id tid
        from student s,teacher t
        where s.tid=t.id
    </select>
</mapper>

5、在Mybatis-config.xml中要绑定注册所需的Mapper接口或（直接扫描package）

<?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>
    <!--xml 中元素的标签上下位置是有要求的-->
    <properties resource="db.properties"/>

    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <setting name="logImpl" value="LOG4J"/>
    </settings>
    <!--包的别名配置-->
    <typeAliases>
        <package name="com.feng.pojo"/>
        <!--<typeAlias type="com.kuang.pojo.User" alias="User"/>-->
    </typeAliases>

    <!-- 一个environments 标签元素可以有多套配置 -->
    <environments default="development">
        <!-- 里面的每一个environment代表一个具体的环境 -->
        <environment id="development">
            <!--transactionManager 事务管理器 -->
            <transactionManager type="JDBC"/>
            <!-- dataSource 数据源配置 -->
            <dataSource type="POOLED">
                <!-- 连接数据库的配置i-->
                <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 resource="com/feng/mapper/StudentMapper.xml"/>

    </mappers>

</configuration>

6、建立测试类，查看是否成功

    @Test
    public void testGetStudent(){
        SqlSession session = MyBatisUtils.getSession();
        StudentMapper mapper = session.getMapper(StudentMapper.class);
        System.out.println(mapper.getStudents());
    }

控制台打印：

一对多处理

现在要查询老师，并且要知道老师对应的学生有那些

1、环境与上例一致

2、建立与两张表对应的实体类Teacher，Student

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

   private List<Student> students; //要查看到老师对应的学生
}

@Data
public class Student {
    private int id;
    private String name;
    private int tid;

}

3、建立对应的Mapper接口

public interface TeacherMapper {

    public Teacher getTeacher(int id);

}
public interface StudentMapper {

}

4、建立对应的Mapper.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.feng.mapper.TeacherMapper">

<!--一对多，一个学生对应多个老师-->
    <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>

    <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=#{id}
    </select>

</mapper>

5、在Mybatis-config.xml中要绑定注册所需的Mapper接口

6、建立测试类，查看是否成功

    @Test
    public void testGetTeacher(){
        SqlSession session = MyBatisUtils.getSession();
        TeacherMapper mapper = session.getMapper(TeacherMapper.class);
        System.out.println(mapper.getTeacher(1));
    }

打印输出：

• 对于学生这边而言， 关联 ， 多个学生，关联一个老师 【多对一】
• 对于老师而言， 集合 ， 一个老师，有很多学生 【一对多】

注意下：

1. 关联 - association 【多对一】
2. 集合 - collection 【一对多】
3. javaType & ofType
   1. JavaType 用来指定实体类中属性的类型
   2. ofType 用来指定映射到List或者集合中的 pojo类型，泛型中的约束类型！

动态SQL

MyBatis 的强大特性之一便是它的动态 SQL。如果有使用 JDBC 或其他类似框架的经验，就能体会到根据不同条件拼接 SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格，还要注意省掉列名列表最后的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。

准备工作：

1、建立一个数据库

CREATE TABLE `blog` (
  `id` VARCHAR(50) NOT NULL COMMENT '博客id',
  `title` VARCHAR(100) NOT NULL COMMENT '博客标题',
  `author` VARCHAR(30) NOT NULL COMMENT '博客作者',
  `create_time` DATETIME NOT NULL COMMENT '创建时间',
  `views` INT(30) NOT NULL COMMENT '浏览量'
) ENGINE=INNODB DEFAULT CHARSET=utf8

2、在项目中建立对应的实体类

@Data
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createDate;
    //在数据库中我们一般使用下划线分割命名，
    private int views;

}

3、建立对应的Mapper接口与xml配置文件

public interface BolgMapper {
    int addBlog(Blog blog);  //写方法来插入数据
}

<?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.feng.mapper.BolgMapper">

    <insert id="addBlog" parameterType="Blog">
        insert into mybatis.blog(id,title,author,create_time,views)
        values (#{id},#{title},#{author},#{createDate},#{views})
    </insert>
</mapper>

4、在Mybatis-config文件中进行对应绑定注册

5、建立测试类，写入数据

@Test
public void testAddBlog() {

    SqlSession session = MyBatisUtils.getSession(true);//开启自动提交，在MyBatisUtils中再构建一个参数为Boolean值的getSession方法即可
    BolgMapper mapper = session.getMapper(BolgMapper.class);

    Blog blog1 = new Blog();
    blog1.setId(IDUtils.getId());
    blog1.setTitle("mybatis so easy");
    blog1.setAuthor("AA");
    blog1.setCreateDate(new Date());
    blog1.setViews(99);
    mapper.addBlog(blog1);


    Blog blog2 = new Blog();
    blog2.setId(IDUtils.getId());
    blog2.setTitle("Java so easy");
    blog2.setAuthor("BB");
    blog2.setCreateDate(new Date());
    blog2.setViews(99);
    mapper.addBlog(blog2);

    Blog blog3 = new Blog();
    blog3.setId(IDUtils.getId());
    blog3.setTitle("data so easy");
    blog3.setAuthor("CC");
    blog3.setCreateDate(new Date());
    blog3.setViews(99);
    mapper.addBlog(blog3);

    Blog blog4 = new Blog();
    blog4.setId(IDUtils.getId());
    blog4.setTitle("dou so easy");
    blog4.setAuthor("DD");
    blog4.setCreateDate(new Date());
    blog4.setViews(99);
    mapper.addBlog(blog4);

    Blog blog5 = new Blog();
    blog5.setId(IDUtils.getId());
    blog5.setTitle("nan so easy");
    blog5.setAuthor("EE");
    blog5.setCreateDate(new Date());
    blog5.setViews(99);
    mapper.addBlog(blog5);

    Blog blog6 = new Blog();
    blog6.setId(IDUtils.getId());
    blog6.setTitle("yiyi so easy");
    blog6.setAuthor("FF");
    blog6.setCreateDate(new Date());
    blog6.setViews(99);
    mapper.addBlog(blog6);
}

//其中，IDUtils是写在Utils下的一个工具类
public class IDUtils {
    public static String getId(){
        return UUID.randomUUID().toString().replaceAll("-","");
    }
}

进行写入数据，这里查看数据库，发现已经写入成功

IF

动态 SQL 通常要做的事情是有条件地包含 where 子句的一部分。比如:

在上述栗子中BlogMapper的接口里写个方法：

public interface BolgMapper {

    List<Blog> getBlogByIf(Map map);

}

对上面栗子中对应的BlogMapper.xml，进行第一个动态Sql语句的书写，我们想可选地通过"title"和"author"两个条件搜索：

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

编写测试类进行测试，此时用Log4j日志记录

@Test
    public void testgetBlogByIf() {

        SqlSession session = MyBatisUtils.getSession(true);
        BolgMapper mapper = session.getMapper(BolgMapper.class);

        HashMap<String, String> map = new HashMap<String, String>();
        map.put("title","dou so easy");
        map.put("author","DD");
        List<Blog> list = mapper.getBlogByIf(map);
        for (Blog blog : list) {
            System.out.println(blog);
        }
    }

会查询到：跟上述两个条件完全一致的数据

当其中有一个条件不满足，就 查询不到。或者当每次只满足其中一个条件时，会返回对应的数据

chooose(when,otherwise)

有些时候，我们不想用到所有的条件语句，而只想从中择其一二。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。

还是上面的例子，但是这次变为提供了"title"就按"title"查找，提供了"author"就按"author"查找，若两者都没有提供，就返回所有符合条件(栗子中两者都不满足会走otherwise，相当于Java-switch中的default)的Blog.

首先在接口中写入方法

 List<Blog> queryBlogChoose(Map map);

再继续在xml中写sql语句

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

编写测试类进行测试

//只想查询作者为EE的那条数据
@Test
    public void testqueryBlogChoose() {

        SqlSession session = MyBatisUtils.getSession(true);
        BolgMapper mapper = session.getMapper(BolgMapper.class);

        HashMap<String, String> map = new HashMap<String, String>();
       
        map.put("author","EE");
        List<Blog> list = mapper.queryBlogChoose(map);
        for (Blog blog : list) {
            System.out.println(blog);
        }
    }

当我将题目加上，但加上此题目不对的作者，返回的数据如下，为只满足题目的数据

当上面两个条件不满足时，将views=99，会打印

trim(where,set)

where 元素知道只有在一个以上的if条件有值的情况下才去插入"WHERE"子句。而且，若最后的内容是"AND"或"OR"开头的，where 元素也知道如何将他们去除。

select * from mybatis.blog
<where>
    <if test="title != null">
        title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</where>

如果 where 元素没有按正常套路出牌，我们还是可以通过自定义 trim 元素来定制我们想要的功能。比如，和 where 元素等价的自定义 trim 元素为：

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ... 
</trim>

prefixOverrides 属性会忽略通过管道分隔的文本序列（注意此例中的空格也是必要的）。它带来的结果就是所有在 prefixOverrides 属性中指定的内容将被移除，并且插入 prefix 属性中指定的内容。

类似的用于动态更新语句的解决方案叫做 set。set 元素可以被用于动态包含需要更新的列，而舍去其他的。比如：

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

这里，set 元素会动态前置 SET 关键字，同时也会消除无关的逗号，因为用了条件语句之后很可能就会在生成的赋值语句的后面留下这些逗号。set真面目：

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

对上面的updateBlog进行测试

编写测试类

@Test
public void testupdateBlog(){
    SqlSession session = MyBatisUtils.getSession(true);
    BolgMapper mapper = session.getMapper(BolgMapper.class);

    HashMap<String, String> map = new HashMap<String, String>();

    map.put("id","680b9f77c78c4429be1a9b7992da54a9");
    map.put("author","QQQ");
    mapper.updateBlog(map);
}

更新成功

Foreach

动态 SQL 的另外一个常用的操作是需要对一个集合进行遍历，通常是在构建 IN 条件语句的时候。比如：

<select id="queryBlogForeach" parameterType="map" resultType="blog">
    select * from mybatis.blog

    <where>
        <foreach collection="ids" item="id" open="and (" close=")" separator="or">
            id = #{id}
        </foreach>
    </where>

</select>
<!--查询当id=?时所有数据-->
<!--collection:集合类型；item：集合的变量名；open循环以什么开始；close循环以什么结束,separator:以什么分割 -->

进行测试：

@Test
    public void testqueryBlogForeach() {
        SqlSession session = MyBatisUtils.getSession(true);
        BolgMapper mapper = session.getMapper(BolgMapper.class);

        HashMap<String,Object> map = new HashMap<String,Object>();
        ArrayList<String> list = new ArrayList<String>();
        list.add("6ff5e719ea004feab2f08d35ac6dd2fe");
        list.add("a79fe6f2fe96403e9a5349b2aaabded6");
        list.add("680b9f77c78c4429be1a9b7992da54a9");
        map.put("ids",list);
        List<Blog> blogs = mapper.queryBlogForeach(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

    }

结果就会将遍历出来的数据打印：