Shulou(Shulou.com)06/03 Report--

This article mainly explains "how to solve the problem of invalidation when fuzzy search uses like matching with% characters in MyBatis". The content in the article is simple and clear, and it is easy to learn and understand. Please follow Xiaobian's train of thought to study and learn "how to solve the invalidation problem of fuzzy search using like matching with% characters in MyBatis".

Catalogue

1. Problem background

two。 Solution method

2.1. Special symbol relpace conversion on the input parameter SearchVo

2.2. Use ESCAPE

2.3. Summary

1. Problem background

Mybatis is a framework that is often used in our daily projects. In projects, we usually use like queries as fuzzy matching characters for search and matching. The following Mapper.xml is a common way for us to use like for fuzzy matching in projects:

And idwl.detail like concat ('%', # {paramVo.detail, jdbcType=VARCHAR},'%')

In this way, 90% of the matching search function can be solved by using fuzzy query in paged search, but 10% is still caused by sewer capsizing. For example, now I want to match the file with'%'in the file name detail, using this statement will invalidate the search and directly return all the limit data in the table.

The reason for this result is that characters such as'%'or'_ 'are wildcards and need to be escaped during fuzzy matching, so that the mysql execution parser will match it as a single character, otherwise it will fuzzy match according to the matching of two' 'characters and get the error result of the full table search.

two。 Solution 2.1. Special symbol relpace conversion on the input parameter SearchVo

Use the Vo input parameter receiving class to distinguish and process the detail field passed in from the front end, and give priority to replacing replace special characters:

Public class SerachParamVO {private String productVersion; private String detail; private Integer releaseType; private String createUser; private String createUserAccount; private Date createTime; private String description; public void setDetail (String detail) {this.detail = detail.replaceAll ("%", "\%") .replaceAll ("_", "\ _");}} 2.2. Use ESCAPE

The use of ESCAPE:escape simply means escape 'character', which means that it no longer has a special meaning as a wildcard character in like. For the understanding of escape, please refer to another blog:

The usage of MYSQL escape will not be described in detail here.

The corresponding solutions are as follows:

① modifies the sql query statement to add escape:

And idwl.detail like concat ('%', # {paramVo.detail, jdbcType=VARCHAR},'%') escape'/'

② is passed into SearchVo for wildcard setting:

Public class SerachParamVO {private String productVersion; private String detail; private Integer releaseType; private String createUser; private String createUserAccount; private Date createTime; private String description; public void setDetail (String detail) {this.detail = detail.replaceAll ("%", "/%") .replaceAll ("_", "/ _") }} Thank you for your reading, the above is the content of "how to solve the problem of invalidation when fuzzy search in MyBatis uses like matching with% characters". After the study of this article, I believe you have a deeper understanding of how to solve the problem of invalidation of fuzzy search using like matching with% characters in MyBatis, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.