17.MyBatis模糊查询

Posted on Edited on In

 

模糊查询

只能用双引号包含%，不能用单引号包含%。

where name like '%'#{name}'%'这种模糊查询方式无效。

where name like "%"#{name}"%"这种模糊查询方式有效。

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
67
68
69
70
71
72
73
74
75
76
77
78
<?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="queryLike01" parameterType="com.course.vo.User" resultType="com.course.vo.User">
		select id, name, age, address, birthday
		from user
		where name like #{name}
        <!-- 
            DEBUG [main] - ==>  Preparing: select id, name, age, address, birthday from user where name like ? 
            DEBUG [main] - ==> Parameters: %user1%(String)
            DEBUG [main] - <==      Total: 70
         -->
	</select>
	
	<select id="queryLike02" parameterType="com.course.vo.User" resultType="com.course.vo.User">
		select id, name, age, address, birthday
		from user
		where name like "%"#{name}"%"
		<!-- 
			DEBUG [main] - ==>  Preparing: select id, name, age, address, birthday from user where name like "%"?"%" 
			DEBUG [main] - ==> Parameters: user1(String)
			DEBUG [main] - <==      Total: 70
		 -->
		
        <!-- 
			select id, name, age, address, birthday
			from user
			where name like '%'#{name}'%'

            DEBUG [main] - ==>  Preparing: select id, name, age, address, birthday from user where name like '%'?'%' 
            DEBUG [main] - ==> Parameters: user1(String)
            DEBUG [main] - <==      Total: 0
			
			返回结果数为0，这种模糊查询的方式无效。
         -->
	</select>
	
	<select id="queryLike03" parameterType="com.course.vo.User" resultType="com.course.vo.User">
		select id, name, age, address, birthday
		from user
		where name like '%${name}%'
        <!-- 
            DEBUG [main] - ==>  Preparing: select id, name, age, address, birthday from user where name like '%user1%' 
            DEBUG [main] - ==> Parameters: 
            DEBUG [main] - <==      Total: 70
         -->
	</select>
	
	<select id="queryLike04" parameterType="com.course.vo.User" resultType="com.course.vo.User">
		select id, name, age, address, birthday
		from user
		where name like concat('%', #{name}, '%')
        <!-- 
            DEBUG [main] - ==>  Preparing: select id, name, age, address, birthday from user where name like concat('%', ?, '%') 
            DEBUG [main] - ==> Parameters: user1(String)
            DEBUG [main] - <==      Total: 70
         -->
	</select>
	
	<select id="queryLike05" parameterType="com.course.vo.User" resultType="com.course.vo.User">
		<bind name="namaValue" value="'%'+name+'%'"/>
		select id, name, age, address, birthday
		from user
		where name like #{namaValue}
        <!-- 
            DEBUG [main] - ==>  Preparing: select id, name, age, address, birthday from user where name like ? 
            DEBUG [main] - ==> Parameters: %user1%(String)
            DEBUG [main] - <==      Total: 70
         -->
	</select>
	
</mapper>

UserDao

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

import java.util.List;

import com.course.vo.User;

public interface UserDao {
	
	public List<User> queryLike01(User user);
	
	public List<User> queryLike02(User user);
	
	public List<User> queryLike03(User user);
	
	public List<User> queryLike04(User user);
	
	public List<User> queryLike05(User user);
	
}

测试类: 

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.Date;
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 queryLike01() {
		User user = new User();
		user.setName("%user1%");
		List<User> userList = userDao.queryLike01(user);
		for (User userVO : userList) {
			System.out.println(userVO.toString());
		}
		
		MyBatisUtil.closeSqlSession(sqlSession);
	}
	
	@Test
	public void queryLike02() {
		User user = new User();
		user.setName("user1");
		List<User> userList = userDao.queryLike02(user);
		for (User userVO : userList) {
			System.out.println(userVO.toString());
		}
		
		MyBatisUtil.closeSqlSession(sqlSession);
	}
	
	@Test
	public void queryLike03() {
		User user = new User();
		user.setName("user1");
		List<User> userList = userDao.queryLike03(user);
		for (User userVO : userList) {
			System.out.println(userVO.toString());
		}
		
		MyBatisUtil.closeSqlSession(sqlSession);
	}
	
	@Test
	public void queryLike04() {
		User user = new User();
		user.setName("user1");
		List<User> userList = userDao.queryLike04(user);
		for (User userVO : userList) {
			System.out.println(userVO.toString());
		}
		
		MyBatisUtil.closeSqlSession(sqlSession);
	}
	
	@Test
	public void queryLike05() {
		User user = new User();
		user.setName("user1");
		List<User> userList = userDao.queryLike05(user);
		for (User userVO : userList) {
			System.out.println(userVO.toString());
		}
		
		MyBatisUtil.closeSqlSession(sqlSession);
	}
	
}

参数说明

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
<?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">
	
	<!-- 
		在mybatis3.3及以下的版本可以使用#{[index]}去取对应索引位置的参数的值，
		index从0开始
	 -->
	<select id="queryParam1" resultType="com.course.vo.User">
		select id, name, age, address, birthday
		from user
		where name = #{0} and address = #{1}
        <!-- 
            DEBUG [main] - ==>  Preparing: select id, name, age, address, birthday from user where name = ? and address = ? 
            DEBUG [main] - ==> Parameters: user30(String), address30(String)
            DEBUG [main] - <==      Total: 1
         -->
	</select>
	
	<!-- 
		在mybatis3.4及以上的版本只能使用#{param[index]}去取对应索引位置的参数的值，
		index从1开始，
		mybatis3.3及以下的版本也可以使用#{param[index]}去取对应索引位置的参数的值
	 -->
	<select id="queryParam2" resultType="com.course.vo.User">
		select id, name, age, address, birthday
		from user
		where name = #{param1} and address = #{param2}
        <!-- 
            DEBUG [main] - ==>  Preparing: select id, name, age, address, birthday from user where name = ? and address = ? 
            DEBUG [main] - ==> Parameters: user30(String), address30(String)
            DEBUG [main] - <==      Total: 1
         -->
	</select>
	
</mapper>

UserDao.java

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

import java.util.List;

import com.course.vo.User;

public interface UserDao {
	
	public List<User> queryParam1(String name, String address);
	
	public List<User> queryParam2(String name, String address);
	
}

测试类: 

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

import java.util.Date;
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 queryParam1() {
		List<User> userList = userDao.queryParam1("user30", "address30");
		for (User userVO : userList) {
			System.out.println(userVO.toString());
		}
		
		MyBatisUtil.closeSqlSession(sqlSession);
	}
	
	@Test
	public void queryParam2() {
		List<User> userList = userDao.queryParam2("user30", "address30");
		for (User userVO : userList) {
			System.out.println(userVO.toString());
		}
		
		MyBatisUtil.closeSqlSession(sqlSession);
	}
	
}