20.动态SQL

Posted on Edited on In

 

where

添加where子句。

如果where子句为空，则不添加where子句。

如果where标签后面直接连接and, or关键字，则and, or关键字会被去掉。

if

判断条件是否满足，满足条件则拼接sql，不满足条件则不拼接sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@Test
public void queryUser01() {
    String name = "";
    String address = "";
    
    User user =new User();
    user.setName(name);
    user.setAddress(address);
    
    List<User> userList = userDao.queryUser01(user);
    for (User userVO : userList) {
        System.out.println(userVO.toString());
    }
    
    MyBatisUtil.closeSqlSession(sqlSession);
}
1
2
3
DEBUG [main] - ==>  Preparing: select id, name, age, address, birthday from user 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - <==      Total: 154

choose/when/otherwise

相当于if/if else/else

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@Test
public void queryUser02() {
    String name = "user1";
    String address = "address1";
    
    User user =new User();
    user.setName(name);
    user.setAddress(address);
    
    List<User> userList = userDao.queryUser02(user);
    for (User userVO : userList) {
        System.out.println(userVO.toString());
    }
    
    MyBatisUtil.closeSqlSession(sqlSession);
}
1
2
3
DEBUG [main] - ==>  Preparing: select id, name, age, address, birthday from user WHERE name like "%"?"%" 
DEBUG [main] - ==> Parameters: user1(String)
DEBUG [main] - <==      Total: 72
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@Test
	public void queryUser02() {
		String name = "user1";
		String address = "address1";
		
		User user =new User();
//		user.setName(name);
//		user.setAddress(address);
		
		List<User> userList = userDao.queryUser02(user);
		for (User userVO : userList) {
			System.out.println(userVO.toString());
		}
		
		MyBatisUtil.closeSqlSession(sqlSession);
	}
1
2
3
DEBUG [main] - ==>  Preparing: select id, name, age, address, birthday from user WHERE age < 80 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - <==      Total: 74

set

用于update set子句，更新满足条件的字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@Test
public void updateUserSet() {
    String name = "user1";
    String address = "address1";
    
    User user =new User();
    user.setId(30);
    user.setName(name);
    user.setAddress(address);
    
    int rowCount = userDao.updateUserSet(user);
    System.out.println("rowCount = " + rowCount);
    
    MyBatisUtil.closeSqlSession(sqlSession);
}
1
2
3
4
DEBUG [main] - ==>  Preparing: update user SET name = ?, address = ? WHERE id = ? 
DEBUG [main] - ==> Parameters: user1(String), address1(String), 30(Integer)
DEBUG [main] - <==    Updates: 1
rowCount = 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@Test
public void updateUserSet() {
    String name = "user1";
    String address = "address1";
    
    User user =new User();
    // user.setId(30);
    user.setName(name);
    user.setAddress(address);
    
    int rowCount = userDao.updateUserSet(user);
    System.out.println("rowCount = " + rowCount);
    
    MyBatisUtil.closeSqlSession(sqlSession);
}
1
2
3
4
DEBUG [main] - ==>  Preparing: update user SET name = ?, address = ? WHERE id = 20 
DEBUG [main] - ==> Parameters: user1(String), address1(String)
DEBUG [main] - <==    Updates: 1
rowCount = 1

foreach

循环遍历数组或者集合。

1
2
3
4
5
6
7
8
9
10
@Test
public void queryUserByArray() {
    int[] idArray = {10, 20, 30, 40, 50};
    List<User> userList = userDao.queryUserByArray(idArray);
    for (User userVO : userList) {
        System.out.println(userVO.toString());
    }
    
    MyBatisUtil.closeSqlSession(sqlSession);
}
1
2
3
DEBUG [main] - ==>  Preparing: select id, name, age, address, birthday from user WHERE id in ( ? , ? , ? , ? , ? ) 
DEBUG [main] - ==> Parameters: 10(Integer), 20(Integer), 30(Integer), 40(Integer), 50(Integer)
DEBUG [main] - <==      Total: 4

code

UserMapper.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
<?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.course.dao.UserDao">
	
	<select id="queryUser01" parameterType="User" resultType="User">
		select id, name, age, address, birthday
		from user
		<where>
			<if test="name != null and name !=''"> name like "%"#{name}"%" </if>
			<if test="address != null and address != ''"> and address like "%"#{address}"%" </if>
		</where>
	</select>
	
	<select id="queryUser02" parameterType="User" resultType="User">
		select id, name, age, address, birthday
		from user
		<where>
			<choose>
				<when test="name != null and name !=''"> name like "%"#{name}"%" </when>
				<when test="address != null and address != ''"> and address like "%"#{address}"%" </when>
				<otherwise>
					and age &lt; 80
				</otherwise>
			</choose>
		</where>
	</select>
	
	<update id="updateUserSet">
		update user
		<set>
			<if test="name != null and name !=''"> name = #{name}, </if>
			<if test="address != null and address != ''"> address = #{address} </if>
		</set>
		<where>
			<choose>
				<when test="id == null"> id = 20 </when>
				<otherwise> id = #{id} </otherwise>
			</choose>
		</where>
	</update>
	
	<select id="queryUserByArray" parameterType="int[]" resultType="User">
		select id, name, age, address, birthday
		from user
		<where>
			id in 
			<!-- 
				collection: 数组或者集合，不是数组或者集合形参名
				item: 数据或者集合的元素
				index: 循环遍历，索引值
				open: 开始字符串
				close: 结束字符串
				separator: 分隔符
			 -->
			<foreach collection="array" item="id" open="(" close=")" separator=",">
				#{id}
			</foreach>
		</where>
	</select>
	
</mapper>

UserDao.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
package com.course.dao;

import java.util.List;

import com.course.vo.User;

public interface UserDao {
	
	public List<User> queryUser01(User user);
	
	public List<User> queryUser02(User user);
	
	public int updateUserSet(User user);
	
	public List<User> queryUserByArray(int[] idArray);
	
}

测试类: 

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
package com.course.test;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import com.course.dao.UserDao;
import com.course.util.MyBatisUtil;
import com.course.vo.User;

public class MainTest {

	private SqlSession sqlSession = MyBatisUtil.openSqlSession();
	private UserDao userDao = sqlSession.getMapper(UserDao.class);
	
	@Test
	public void queryUser01() {
		String name = "";
		String address = "";
		
		User user =new User();
		user.setName(name);
		user.setAddress(address);
		
		List<User> userList = userDao.queryUser01(user);
		for (User userVO : userList) {
			System.out.println(userVO.toString());
		}
		
		MyBatisUtil.closeSqlSession(sqlSession);
	}
	
	@Test
	public void queryUser02() {
		String name = "user1";
		String address = "address1";
		
		User user =new User();
		user.setName(name);
		user.setAddress(address);
		
		List<User> userList = userDao.queryUser02(user);
		for (User userVO : userList) {
			System.out.println(userVO.toString());
		}
		
		MyBatisUtil.closeSqlSession(sqlSession);
	}
	
	@Test
	public void updateUserSet() {
		String name = "user1";
		String address = "address1";
		
		User user =new User();
		user.setId(30);
		user.setName(name);
		user.setAddress(address);
		
		int rowCount = userDao.updateUserSet(user);
		System.out.println("rowCount = " + rowCount);
		
		MyBatisUtil.closeSqlSession(sqlSession);
	}
	
	@Test
	public void queryUserByArray() {
		int[] idArray = {10, 20, 30, 40, 50};
		List<User> userList = userDao.queryUserByArray(idArray);
		for (User userVO : userList) {
			System.out.println(userVO.toString());
		}
		
		MyBatisUtil.closeSqlSession(sqlSession);
	}
	
}