hooyantsing's Blog

第104次课程_Mybatis优化及高级特性

字数统计: 2.4k阅读时长: 13 min
2020/02/13

源辰76班

第104次课程

2020.02.13

内容

Mybatis优化及高级特性

1.insert/selectkey

MovieMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
<?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.yc.mybatis.dao.MovieMapper">
  <select id="selectAll"  resultType="com.yc.mybatis.bean.Movie">
    select * from movie;
  </select>
  
  <!-- if else -->
   <select id="selectByMovie"  resultType="com.yc.mybatis.bean.Movie">
    select * from movie
    <where>
        <if test="name != null">and name  like #{name}</if>
        <if test="region != null">and region  = #{region}</if>
        <if test="language != null">and  language = #{language}</if>
    </where>
   </select>
   
   <!--  switch==choose/case==when/default==otherwise  -->
   <select id="selectByFlag"  resultType="com.yc.mybatis.bean.Movie">
    select * from movie
    <where>
        <choose>
            <when test="flag == 1">name =  #{name}</when>
            <when test="flag == 2">name like  #{name}</when>
            <when test="flag == 3">name is  null</when>
            <otherwise>name is not  null</otherwise>
        </choose>
    </where>
   </select>
   
   <!-- update -->
   <select id="selectById"  resultType="com.yc.mybatis.bean.Movie">
    select * from movie where id = #{id}
   </select>
   <update id="update">
    update movie
        <set>
           <if test="name != null">name =  #{name},</if>
            <if test="duration !=  null">duration = #{duration},</if>
            <if test="region != null">region  = #{region},</if>
        </set>
        where id = #{id}
   </update>
   
   <!-- foreach:循环 -->
    <select id="selectInLanguage"  resultType="com.yc.mybatis.bean.Movie">
        select * from movie where
        <foreach collection="languages"  item="l" open="language in (" close=")"  separator=",">
        #{l}
        </foreach>
    </select>
    
    <!-- 两种方式在插入新记录时,自动生成主键值  -->

    <!-- useGeneratedKeys属性:打开返回值 -->
    <insert id="insert"  useGeneratedKeys="true" keyColumn="id"  keyProperty="id">
        insert into movie(name,duration)  values (#{name},#{duration})
    </insert>
    
    <!-- selectKey -->
    <insert id="insert">
    <!-- order表示该语句执行的时机 -->
        <selectKey keyColumn="id"  keyProperty="id" order="AFTER"  resultType="int">
        select last_insert_id()
        </selectKey>
        insert into movie(name,duration)  values (#{name},#{duration})
    </insert>
  
</mapper>

MovieMapper.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
package com.yc.mybatis.dao;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.yc.mybatis.bean.Movie;

public interface MovieMapper {
    
    List<Movie> selectAll();
    
    List<Movie> selectByMovie(Movie m);
    
    
    /**
        Mybatis默认参数[param1,param2]
        可以用@Param("name") 将形参名 param1 -> name
     */
    List<Movie> selectByFlag(@Param("name") String n,@Param("flag") String f);
    
    Movie selectById(int id);
    void update(Movie m);
    
    List<Movie> selectInLanguage(@Param("languages") String[] l);
    
    void insert(Movie m);
}

MovieMapperTest.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
package com.yc.mybatis.dao;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import com.yc.mybatis.bean.Movie;

import org.junit.Assert;

public class MovieMapperTest {
    private SqlSession session;
    
    @Before
    public void before() throws IOException {
        // 定义mybatis配置文件路径,默认从classpath开始
        String resource = "mybatis.xml";
        // 读入配置文件
        InputStream inputStream = Resources.getResourceAsStream(resource);
        // 创建会话工厂bean
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        // MyBatis的会话底层包装一个JDBC连接
        session = sqlSessionFactory.openSession();
    
    }
    
    @Test
    public void text() {
        MovieMapper mm = session.getMapper(MovieMapper.class);
        
        List<Movie> list = mm.selectAll();
        
        //Assert.assertEquals(23, list.size());        
    }
    
    @Test
    public void testSelectByMovie() {
        MovieMapper mm = session.getMapper(MovieMapper.class);
        
        mm.selectByMovie(null);
        
        Movie m = new Movie();
        mm.selectByMovie(m);
        
        m.setName("少年派");
        mm.selectByMovie(m);
        
        m.setRegion("美国");
        mm.selectByMovie(m);
        
        m.setLanguage("英语");
        mm.selectByMovie(m);
    }
    
    @Test
    public void testSelectByFlag() {
        MovieMapper mm = session.getMapper(MovieMapper.class);
        
        mm.selectByFlag("少年", "1");
        mm.selectByFlag("少年", "2");
        mm.selectByFlag("少年", "3");
        mm.selectByFlag("少年", "4");
    }
    
    @Test
    public void testUpdate() {
        MovieMapper mm = session.getMapper(MovieMapper.class);
        Movie m = mm.selectById(1);
        mm.update(m);
        m.setName(null);
        mm.update(m);
        m.setRegion(null);
        mm.update(m);
        m.setDuration(null);
        //mm.update(m);
    }
    
    @Test
    public void testSelectInLanguage() {
        MovieMapper mm = session.getMapper(MovieMapper.class);
        String[] languages = {"英语","国语","粤语","法语"};
        mm.selectInLanguage(languages);
    }
    
    @Test
    public void testInsert() {
        MovieMapper mm = session.getMapper(MovieMapper.class);
        Movie m = new Movie();
        m.setName("终结者6");
        m.setDuration(1000);
        mm.insert(m);
        //提交
        session.commit();
        /*
         * 在insert完成后,直接获取到movie记录的id值
         * 需要进行表要的配置
         */
        Assert.assertEquals(true, m.getId()>0);
    }
    
}

2.include/sql 和 cache 标签

include/sql:统一替换变量。

cache:二级缓存。所有session共享二级缓存块内容。

MovieMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
<?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.yc.mybatis.dao.MovieMapper">
  <select id="selectAll"   resultType="com.yc.mybatis.bean.Movie">
    select * from movie;
  </select>
  
  <!-- if else -->
   <select id="selectByMovie"   resultType="com.yc.mybatis.bean.Movie">
    select * from movie
    <where>
        <if test="name != null">and name   like #{name}</if>
        <if test="region != null">and region   = #{region}</if>
        <if test="language != null">and   language = #{language}</if>
    </where>
   </select>
   
   <!--   switch==choose/case==when/default==otherwise   -->
   <select id="selectByFlag"   resultType="com.yc.mybatis.bean.Movie">
    select * from movie
    <where>
        <choose>
            <when test="flag == 1">name =   #{name}</when>
            <when test="flag == 2">name like   #{name}</when>
            <when test="flag == 3">name is   null</when>
            <otherwise>name is not   null</otherwise>
        </choose>
    </where>
   </select>
   
   <!-- update -->
   <select id="selectById"   resultType="com.yc.mybatis.bean.Movie">
    select * from movie where id = #{id}
   </select>
   <update id="update">
    update movie
        <set>
           <if test="name != null">name =   #{name},</if>
            <if test="duration !=   null">duration = #{duration},</if>
            <if test="region != null">region   = #{region},</if>
        </set>
        where id = #{id}
   </update>
   
   <!-- foreach:循环 -->
    <select id="selectInLanguage"   resultType="com.yc.mybatis.bean.Movie">
        select * from movie where
        <foreach collection="languages"   item="l" open="language in (" close=")"   separator=",">
        #{l}
        </foreach>
    </select>
    
    <!-- 两种方式在插入新记录时,自动生成主键值   -->
    <!-- useGeneratedKeys属性:打开返回值 -->
    <!--
    <insert id="insert"   useGeneratedKeys="true" keyColumn="id"   keyProperty="id">
        insert into movie(name,duration)   values (#{name},#{duration})
    </insert>
     -->
    
    <!-- selectKey -->
    <insert id="insert">
    <!-- order表示该语句执行的时机 -->
        <selectKey keyColumn="id"   keyProperty="id" order="AFTER"   resultType="int">
        select last_insert_id()
        </selectKey>
        insert into movie(name,duration)   values (#{name},#{duration})
    </insert>
    
    <!-- 命名语句块 语法格式${语句块名} -->
    <select id="selectAllWithOrder"  resultType="com.yc.mybatis.bean.Movie">
    select * from movie order by ${orders};
    </select>
    
    <!-- include -->
    <!-- select <include  refid="allColumns"></include> from movie  order by ${orders}; -->
    <sql id="allColumns">
        id,name,duration,region,language
    </sql>
    
    <!-- 开启二级缓存 -->
    <cache></cache>
  
</mapper>

MovieMapper.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
package com.yc.mybatis.dao;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.yc.mybatis.bean.Movie;
public interface MovieMapper {
    
    List<Movie> selectAll();
    
    List<Movie> selectByMovie(Movie m);
    
    
    /**
        Mybatis默认参数[param1,param2]
        可以用@Param("name") 将形参名 param1  -> name
     */
    List<Movie> selectByFlag(@Param("name")  String n,@Param("flag") String f);
    
    Movie selectById(int id);
    void update(Movie m);
    
    List<Movie>  selectInLanguage(@Param("languages")  String[] l);
    
    void insert(Movie m);
    
    /*
     * 根据用户的要求进行排序
     * 命名语句块,在单参数时,也需要加@Param("")注解
     */
    List<Movie>  selectAllWithOrder(@Param("orders") String  orders);
}

MovieMapperTest.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
package com.yc.mybatis.dao;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import  org.apache.ibatis.session.SqlSessionFactory;
import  org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import com.yc.mybatis.bean.Movie;
import org.junit.Assert;
public class MovieMapperTest {
    private SqlSession session;
    
    @Before
    public void before() throws IOException  {
        // 定义mybatis配置文件路径,默认从classpath开始
        String resource = "mybatis.xml";
        // 读入配置文件
        InputStream inputStream =  Resources.getResourceAsStream(resource);
        // 创建会话工厂bean
        SqlSessionFactory sqlSessionFactory  = new  SqlSessionFactoryBuilder().build(inputStream);
        // MyBatis的会话底层包装一个JDBC连接
        session =  sqlSessionFactory.openSession();
    
    }
    
    @Test
    public void text() {
        MovieMapper mm =  session.getMapper(MovieMapper.class);
        
        List<Movie> list = mm.selectAll();
        
        //Assert.assertEquals(23,  list.size());        
    }
    
    @Test
    public void testSelectByMovie() {
        MovieMapper mm =  session.getMapper(MovieMapper.class);
        
        mm.selectByMovie(null);
        
        Movie m = new Movie();
        mm.selectByMovie(m);
        
        m.setName("少年派");
        mm.selectByMovie(m);
        
        m.setRegion("美国");
        mm.selectByMovie(m);
        
        m.setLanguage("英语");
        mm.selectByMovie(m);
    }
    
    @Test
    public void testSelectByFlag() {
        MovieMapper mm =  session.getMapper(MovieMapper.class);
        
        mm.selectByFlag("少年", "1");
        mm.selectByFlag("少年", "2");
        mm.selectByFlag("少年", "3");
        mm.selectByFlag("少年", "4");
    }
    
    @Test
    public void testUpdate() {
        MovieMapper mm =  session.getMapper(MovieMapper.class);
        Movie m = mm.selectById(1);
        mm.update(m);
        m.setName(null);
        mm.update(m);
        m.setRegion(null);
        mm.update(m);
        m.setDuration(null);
        //mm.update(m);
    }
    
    @Test
    public void testSelectInLanguage() {
        MovieMapper mm =  session.getMapper(MovieMapper.class);
        String[] languages = {"英语","国语","粤语","法语"};
        mm.selectInLanguage(languages);
    }
    
    @Test
    public void testInsert() {
        MovieMapper mm =  session.getMapper(MovieMapper.class);
        Movie m = new Movie();
        m.setName("终结者6");
        m.setDuration(1000);
        mm.insert(m);
        //提交
        session.commit();
        /*
         * 在insert完成后,直接获取到movie记录的id值
         * 需要进行表要的配置
         */
        Assert.assertEquals(true,  m.getId()>0);
    }
    
    @Test
    public void testSelectAllWithOrder() {
       MovieMapper mm =  session.getMapper(MovieMapper.class);
       mm.selectAllWithOrder(" id desc");
       System.out.println("===============================");
       mm.selectAllWithOrder(" name");
       System.out.println("===============================");
       mm.selectAllWithOrder(" name");
       System.out.println("===============================");
       mm.selectAllWithOrder(" name");
       //二级缓存要求,会话必须在提交之后,才能数据共享
       System.out.println("*******************************");
       MovieMapper mm1 =  session.getMapper(MovieMapper.class);
       System.out.println("===============================");
       mm1.selectAllWithOrder(" name");
       System.out.println("===============================");
       mm1.selectAllWithOrder(" name");
    }
}
CATALOG