Implementation of mybatis Fuzzy Query

Implementation of mybatis Fuzzy Query

The reverse assistant of mybatis is really easy to use, which can save a lot of time to write conventional sql statements, but it can't automatically generate fuzzy query statements, but fuzzy query is essential in development, so it is necessary to manually write fuzzy query function for mapper.

Let's clarify the difference between # and $in MyBatis/Ibatis:

1. # Treat all the incoming data as a string, and enclose a double quotation mark on the automatically incoming data. For example: order by # user_id #, if the passed-in value is 111, the value when resolved to sql is order by "111", and if the passed-in value is id, the resolved sql is order by "id".

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

3. # mode can prevent sql injection to a great extent.

4. The $approach does not prevent Sql injection.

5. Mode 1 is generally used for passing in database objects, such as passing in table names.

6. If you can use # like 1, don't use $.

ps: Also encountered while using mybatis < ![CDATA[]] > The statement inside the symbol will not be treated as a string, but as an sql statement, such as executing a stored procedure.

Our requirement is a fuzzy query for an User, with the idea of comparing all the base fields (username, gender, etc.) to the incoming key (keyword).

1. Encoding 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. Encoding UserMapper. java

List<User> queryUserByKey(String key);

The above is the situation I encountered in the development, and the use of other mybatis fuzzy queries will continue to be recorded in the future.

If you have any questions, please leave a message or go to this site community to exchange and discuss, thank you for reading, hope to help everyone, thank you for your support to this site!