hooyantsing's Blog

第103次课程_Mybatis动态SQL

字数统计: 2.2k阅读时长: 12 min
2020/02/12

源辰76班

第103次课程

2020.02.12

内容

Mybatis动态SQL

1.基础复习

MovieMapper.xml:对SQL语句定义

MovieMapper.java:接口,java调用内部函数

mybatis.xml:注册Mapper

MovieMapperTest.java:测试

mybatis.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
<?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>
    <!-- 数据环境配置 -->
    <environments default="test">
       <environment id="development">
           <!-- 设置事务管理 -->
           <transactionManager type="JDBC"  />
           <!-- 连接参数-->
           <dataSource type="POOLED">
               <property name="driver"  value="${driver}" />
               <property name="url"  value="${url}" />
               <property name="username"  value="${username}" />
               <property name="password"  value="${password}" />
           </dataSource>
       </environment>
       
       <environment id="test">
           <transactionManager type="JDBC"  />
           <dataSource type="POOLED">
               <property name="driver"  value="com.mysql.jdbc.Driver" />
               <property name="url"  value="jdbc:mysql://127.0.0.1/C0-S3-Ply-mybatis" />
               <property name="username"  value="root" />
               <property name="password"  value="a" />
           </dataSource>
       </environment>
       
       <environment id="production">
           <transactionManager type="JDBC"  />
           <dataSource type="POOLED">
               <property name="driver"  value="${driver}" />
               <property name="url"  value="${url}" />
               <property name="username"  value="${username}" />
               <property name="password"  value="${password}" />
           </dataSource>
       </environment>
    </environments>
    
    <mappers>
       
       <!-- 注册SQL映射文件 -->
       <!--
       <mapper  resource="com/yc/mybatis/dao/HallMapper.xml"  />
       <mapper  resource="com/yc/mybatis/dao/CinemaMapper.xml" />
        -->
       <!-- package 可以设置包扫描,自动的将该包下的映射文件注册进来 -->
      <package name="com.yc.mybatis.dao"/>
    </mappers>
</configuration>

MovieMapper.xml

1
2
3
4
5
6
7
8
9
10
<?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>
  
</mapper>

MovieMapper.java

1
2
3
4
5
6
7
package com.yc.mybatis.dao;
import java.util.List;
import com.yc.mybatis.bean.Movie;
public interface MovieMapper {
    
    List<Movie> selectAll();
}

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
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());
       
    }
    
}

2.where/if 和 where/choose/when|otherwise 标签

where/if相当于if/else

where/choose/when|otherwise相当于switch/case|default

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
<?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>
  
</mapper>

MovieMapper.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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);
}

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
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");
    }
    
}

3.update 和 foreach 标签

foreach相当于for循环

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
<?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>
  
</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
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);
}

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
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);
    }
    
}
CATALOG