java模糊查询_DM7与mybatis(三)——模糊查询

在《DM7与mybatis(一)——基本CRUD》中，我们介绍了dm7与mybatis的环境集成和基本配置，实现基本的CRUD操作。在《DM7与mybatis(二)——ID生成》中，我们介绍了利用dm7的序列和自增列实现ID自动生成的多种方式。

模糊查询是数据库记录检索的重要功能，通常利用sql语句中的操作符like、通配符%配合查询关键字来实现，本文将介绍如何利用mybatis实现对DM7的模糊查询。

环境准备参见《DM7与mybatis(一)——基本CRUD》，我们仍然基于表t_user和domain对象User来进行演示。为查看模糊查询效果，我们以test用户登录，执行下面脚本，向t_user表中插入几条数据。

-- 以test用户登录执行脚本

INSERT INTO t_user VALUES(2,'u002','12345','u002@sina.com');

INSERT INTO t_user VALUES(3,'u003','54321','abc@163.com');

INSERT INTO t_user VALUES(4,'佚名','0000','a@b.c');

INSERT INTO t_user VALUES(5,'admin','888888','admin@dameng.com');

commit;

注意最后要执行commit语句，因为DM7管理工具缺省没有打开自动提交，不执行commit语句，这些记录不会真正提交，就会出现演示过程中查询不到数据的现象。

一、使用’%${key}%’

在Mybatis中使用’%${key}%’表达式，可直接生成形如 like ‘%key%’ 的sql语句，从而实现对key值的模糊查询。下面我们在name字段上演示这个方法。

1、XML映射文件

在resourcesmybatis-config.xml中，新增一个mapper配置，加载QueryMapper.xml。

……

<!-- 配置映射文件 -->

<mappers>

<mapper resource="org/dmstudy/mybatis/crud/dao/UserMapper.xml" />

<mapper resource="org/dmstudy/mybatis/id/dao/IdMapper.xml" />

<mapper resource="org/dmstudy/mybatis/query/dao/QueryMapper.xml" />

</mappers>

……

新增 org/dmstudy/mybatis/query/dao/QueryMapper.xml文件，其内容如下：

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper

PUBLIC "-//http://mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="org.dmstudy.mybatis.query.dao.QueryMapper">

<select id="query" parameterType="map" resultType="User">

select

id,name,phone,email

from t_user

<where>

<if test="name != null">

and name like '%${name}%'

</if>

</where>

</select>

</mapper>

注意namespace值对应为"org.dmstudy.mybatis.query.dao.QueryMapper"。

<if>元素的作用是当test对应的表达式为真（此处"name != null"表示输入参数中name属性不为空）时，则输出元素内的内容。

<where>元素的作用是用来生成where语句，当元素内无输出时，它什么也不会输出（保证不会出现孤立的where）；当元素内有输出时，它会输出where以及内容，并且会自动去掉元素内容中最前面多余的and/or，防止语法错误。

parameterType="map"表示输入参数为一个java.util.Map对象，这样输入参数可以非常灵活，配合<if>元素能够实现复杂的where条件。

2、Mapper接口

创建org.dmstudy.mybatis.query.dao.QueryMapper.java文件，内容如下:

package org.dmstudy.mybatis.query.dao;

import java.util.List;

import java.util.Map;

import org.dmstudy.mybatis.domain.User;

public interface QueryMapper {

List<User> query(Map param);

}

注意接口的全名org.dmstudy.mybatis.query.dao.QueryMapper、接口方法query与QueryMapper.xml中的namespace、语句映射节点id保持一致。

3、调用代码

package org.dmstudy.mybatis.query;

……

public class QueryApp {

……

public void query1() {

System.out.println("--- query1 ---");

SqlSession sqlSession = sqlSessionFactory.openSession();

try {

// 获取mapper对象

QueryMapper mapper = sqlSession.getMapper(QueryMapper.class);

// 使用 %${name}%的方式查询

Map<String,Object> param = new HashMap<String,Object>();

param.put("name", "00");

List<User> user_list = mapper.query(param);

System.out.println("查询到" + user_list.size() + "条记录！ ");

for (User user : user_list) {

System.out.println("User[id= " + user.getId() + ", name= " + user.getName() + "]");

}

} catch (Exception e) {

System.out.println("query1执行失败 ,原因:" + e.getMessage());

e.printStackTrace();

} finally {

sqlSession.close();

}

}

}

查看执行过程输出log4j日志信息

--- query1 ---

DEBUG [main] - Opening JDBC Connection

DEBUG [main] - Created connection 257608164.

DEBUG [main] - Setting autocommit to false on JDBC Connection [dm.jdbc.driver.DmdbConnection@f5ac9e4]

DEBUG [main] - ==> Preparing: select id,name,phone,email from t_user WHERE name like '%00%'

DEBUG [main] - ==> Parameters:

DEBUG [main] - <== Total: 2

查询到2条记录！

User[id= 2, name= u002]

User[id= 3, name= u003]

DEBUG [main] - Resetting autocommit to true on JDBC Connection [dm.jdbc.driver.DmdbConnection@f5ac9e4]

DEBUG [main] - Closing JDBC Connection [dm.jdbc.driver.DmdbConnection@f5ac9e4]

DEBUG [main] - Returned connection 257608164 to pool.

我们在输入参数param.put("name", "00")，可以看到生成的where条件为 WHERE name like '%00%'，与我们的期望一致。最终查到了2条匹配记录，其name中均包含了’00’。

使用“%${…}“方式虽然简单，但由于很多场景下参数值直接来自用户输入，容易因用户输入值中的特殊字符（如单引号）导致生成的sql语句语法错误，甚至被恶意用户利用来实施SQL注入攻击。因而使用这种方案必须加强对输入参数的检查。

二、使用'%'||#{phone}||'%'

为解决方法一存在SQL注入的问题，可以采用#{…}生成预编译SQL语句再设置参数。但DM7不支持like '%"?"%' 或者 like '%?%'的方式，需要用连接符将%和?连接起来，生成like ‘%’||?||’%’的形式。下面我们在phone字段上演示这种方法。

1、XML映射文件

在QueryMapper.xml文件中，在 “query “的<where>元素中，增加一个新的<if>元素，添加基于phone字段的条件。：

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper

PUBLIC "-//http://mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="org.dmstudy.mybatis.query.dao.QueryMapper">

<select id="query" parameterType="map" resultType="User">

select

id,name,phone,email

from t_user

<where>

<if test="name != null">

and name like '%${name}%'

</if>

<if test="phone != null">

and phone like '%'||#{phone}||'%'

</if>

</where>

</select>

</mapper>

2、调用代码

package org.dmstudy.mybatis.query;

……

public class QueryApp {

……

public void query2() {

System.out.println("--- query2 ---");

SqlSession sqlSession = sqlSessionFactory.openSession();

try {

// 获取mapper对象

QueryMapper mapper = sqlSession.getMapper(QueryMapper.class);

// 使用 '%'||#{phone}||'%'的方式查询

Map<String,Object> param = new HashMap<String,Object>();

param.put("phone", "8");

List<User> user_list = mapper.query(param);

System.out.println("查询到" + user_list.size() + "条记录！ ");

for (User user : user_list) {

System.out.println("User[id= " + user.getId() + ", phone= " + user.getPhone() + "]");

}

} catch (Exception e) {

System.out.println("query2执行失败 ,原因:" + e.getMessage());

e.printStackTrace();

} finally {

sqlSession.close();

}

}

}

查看执行过程输出log4j日志信息

--- query2 ---

DEBUG [main] - Opening JDBC Connection

DEBUG [main] - Checked out connection 1847008471 from pool.

DEBUG [main] - Setting autocommit to false on JDBC Connection [dm.jdbc.driver.DmdbConnection@6e171cd7]

DEBUG [main] - ==> Preparing: select id,name,phone,email from t_user WHERE phone like '%'||?||'%'

DEBUG [main] - ==> Parameters: 8(String)

DEBUG [main] - <== Total: 1

查询到1条记录！

User[id= 5, phone= 888888]

DEBUG [main] - Resetting autocommit to true on JDBC Connection [dm.jdbc.driver.DmdbConnection@6e171cd7]

DEBUG [main] - Closing JDBC Connection [dm.jdbc.driver.DmdbConnection@6e171cd7]

DEBUG [main] - Returned connection 1847008471 to pool.

我们在输入参数param.put("phone", "8")，可以看到生成的where条件为 WHERE phone like '%'||?||'%'，并通过PreparedStatement的方式执行，能够防止SQL注入。

三、使用concat函数

除了使用”||”连接符，DM7还可以使用concat函数连接多个字符串，这次我们在email字段上演示。

1、XML映射文件

在QueryMapper.xml文件中，在 “query “的<where>元素中，再增加一个<if>元素，加入基于email字段的条件。：

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper

PUBLIC "-//http://mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="org.dmstudy.mybatis.query.dao.QueryMapper">

<select id="query" parameterType="map" resultType="User">

select

id,name,phone,email

from t_user

<where>

<if test="name != null">

and name like '%${name}%'

</if>

<if test="phone != null">

and phone like '%'||#{phone}||'%'

</if>

<if test="email != null">

and email like concat('%',#{email},'%')

</if>

</where>

</select>

</mapper>

2、调用代码

package org.dmstudy.mybatis.query;

……

public class QueryApp {

……

public void query3() {

System.out.println("--- query3 ---");

SqlSession sqlSession = sqlSessionFactory.openSession();

try {

// 获取mapper对象

QueryMapper mapper = sqlSession.getMapper(QueryMapper.class);

// 使用 concat('%',#{email},'%')的方式查询

Map<String,Object> param = new HashMap<String,Object>();

param.put("email", "b");

List<User> user_list = mapper.query(param);

System.out.println("查询到" + user_list.size() + "条记录！ ");

for (User user : user_list) {

System.out.println("User[id= " + user.getId() + ", email= " + user.getEmail() + "]");

}

} catch (Exception e) {

System.out.println("query3执行失败 ,原因:" + e.getMessage());

e.printStackTrace();

} finally {

sqlSession.close();

}

}

}

查看执行过程输出log4j日志信息

--- query3 ---

DEBUG [main] - Opening JDBC Connection

DEBUG [main] - Checked out connection 1847008471 from pool.

DEBUG [main] - Setting autocommit to false on JDBC Connection [dm.jdbc.driver.DmdbConnection@6e171cd7]

DEBUG [main] - ==> Preparing: select id,name,phone,email from t_user WHERE email like concat('%',?,'%')

DEBUG [main] - ==> Parameters: b(String)

DEBUG [main] - <== Total: 2

查询到2条记录！

User[id= 3, email= abc@163.com]

User[id= 4, email= a@b.c]

DEBUG [main] - Resetting autocommit to true on JDBC Connection [dm.jdbc.driver.DmdbConnection@6e171cd7]

DEBUG [main] - Closing JDBC Connection [dm.jdbc.driver.DmdbConnection@6e171cd7]

DEBUG [main] - Returned connection 1847008471 to pool

输入参数param.put("email", "c")，可以看到生成的where条件为 WHERE email like concat('%',?,'%')，通过PreparedStatement的方式执行。

四、使用bind元素

无论是”||”连接符，还是concat函数，不同数据库之间存在着语法差异，也增加了服务器端的运算量。Mybatis提供了<bind>元素可以从 OGNL 表达式中创建一个变量并将其绑定到上下文，我们可以利用这个功能为原始参数值添加”%”前缀、”%”后缀。

1、XML映射文件

在QueryMapper.xml文件中，新增一个语句映射节点“queryUseBind “，其内容如下。：

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper

PUBLIC "-//http://mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="org.dmstudy.mybatis.query.dao.QueryMapper">

……

<select id="queryUseBind" parameterType="map" resultType="User">

select

id,name,phone,email

from t_user

<where>

<if test="name != null">

<bind name="var1" value="'%' + name + '%'" />

and name like #{var1}

</if>

<if test="phone != null">

<bind name="var2" value="'%' + phone + '%'" />

and phone like #{var2}

</if>

<if test="email != null">

<bind name="var3" value="'%' + email + '%'" />

and email like #{var3}

</if>

</where>

</select>

</mapper>

<bind>元素配置中，name属性配置的是添加到上下文的变量名，如”var1”。value属性值则是一个OGNL表达式，如"'%' + name + '%'"，name是代表着从输入参数中属性名为”name”的属性值，表达式的结果是为这个值前后添加'%'。<bind>元素内容执行后，在<if>元素内容解析时，就可以使用#{var1}来访问这个变量值。

2、调用代码

package org.dmstudy.mybatis.query;

……

public class QueryApp {

……

public void query4() {

System.out.println("--- query4 ---");

SqlSession sqlSession = sqlSessionFactory.openSession();

try {

// 获取mapper对象

QueryMapper mapper = sqlSession.getMapper(QueryMapper.class);

// 使用 <bind name="var1" value="'%' + name + '%'" /> 的方式查询

Map<String,Object> param = new HashMap<String,Object>();

param.put("name", "00");

param.put("phone", "23");

List<User> user_list = mapper.queryUseBind(param);

System.out.println("查询到" + user_list.size() + "条记录！ ");

for (User user : user_list) {

System.out.println("User[id= " + user.getId() + ", name= " + user.getName() + "]");

}

} catch (Exception e) {

System.out.println("query4执行失败 ,原因:" + e.getMessage());

e.printStackTrace();

} finally {

sqlSession.close();

}

}

}

查看执行过程输出log4j日志信息

--- query4 ---

DEBUG [main] - Opening JDBC Connection

DEBUG [main] - Checked out connection 1847008471 from pool.

DEBUG [main] - Setting autocommit to false on JDBC Connection [dm.jdbc.driver.DmdbConnection@6e171cd7]

DEBUG [main] - ==> Preparing: select id,name,phone,email from t_user WHERE name like ? and phone like ?

DEBUG [main] - ==> Parameters: %00%(String), %23%(String)

DEBUG [main] - <== Total: 1

查询到1条记录！

User[id= 2, name= u002]

DEBUG [main] - Resetting autocommit to true on JDBC Connection [dm.jdbc.driver.DmdbConnection@6e171cd7]

DEBUG [main] - Closing JDBC Connection [dm.jdbc.driver.DmdbConnection@6e171cd7]

DEBUG [main] - Returned connection 1847008471 to pool.

这次我们输入了两个参数，param.put("name", "00")，param.put("phone", "23")，可以看到生成的where条件为WHERE name like ? and phone like ?，而为PreparedStatement提供的两个参数值为”%00%”、”%23%”，正好是在原参数值的前后添加了通配符”%”之后的结果。