Mybatis fuzzy query implementation method

Mybatis fuzzy query implementation method

The reverse assistant of mybatis is really easy to use and can save a lot of time in writing regular SQL statements, but it cannot automatically generate fuzzy query statements. However, fuzzy query is indispensable in development, so it is necessary to manually write fuzzy query function for mapper.

Here we first clarify the difference between # and $ in MyBatis/Ibatis:

1. # Treat all incoming data as a string, and add double quotes to the automatically incoming data. For example: order by #user_id#, if the value passed in is 111, then the value parsed into SQL is order by "111", if the value passed in is id, then the parsed SQL is order by "id".

2. $ displays the incoming data directly in sql. For example: order by $user_id$, if the value passed in is 111, then the value parsed into SQL is order by user_id, if the value passed in is id, then the parsed SQL is order by id.

3. #This method can prevent SQL injection to a great extent.

4. The $ method cannot prevent SQL injection.

5. The $ method is generally used to pass in database objects, such as table names.

6. Generally, don’t use $ when you can use #.

ps: When using mybatis, you will also encounter the usage of <![CDATA[]]>. The statements within this symbol will not be processed as strings, but directly as SQL statements, such as executing a stored procedure.

Our requirement is to perform a fuzzy query on a User. The idea is to compare all basic fields (username, gender, etc.) with the passed key (keyword).

1. Encode UserMapper.xml

 <select id="queryUserByKey" parameterType="string"
    resultType="com.lqr.pojo.User">
    select * from user where uid like CONCAT('%',#{key},'%')
    or username like CONCAT('%',#{key},'%')
    or realname like CONCAT('%',#{key},'%')
    or identification like CONCAT('%',#{key},'%')
    or email like CONCAT('%',#{key},'%')
  </select>

2. Coding UserMapper.java

List<User> queryUserByKey(String key);

The above is the scenario I encountered during development. I will continue to record other uses of mybatis fuzzy queries in the future.

If you have any questions, please leave a message or come to the community to discuss. Thank you for reading and I hope it can help you. Thank you for your support of this site!