Several methods of fuzzy query in Spring Mybatis Mapper

When developing Spring in conjunction with Mybatis, implementing fuzzy queries is a common requirement. In Mybatis, LIKE queries can be implemented in a variety of ways, depending on how your query parameters are passed to Mybatis's SQL mapper. Here are a few common ways to implement fuzzy queries:

1. Use #{} directly in the Mapper interface for splicing

This method uses the LIKE keyword directly in the Mapper XML file, and then passes the parameters through #{}. You can splice the percentage sign (%) required for the fuzzy query in the Java code before passing the parameters. .

Mapper interface XML example:

<select id="findByName" resultType="com.example.YourModel">
  SELECT * FROM your_table
  WHERE name LIKE #{name}
</select> 

Call in Java code:

// Suppose there is aMapperThe interface method is calledfindByName
List<YourModel> results = yourMapper.findByName("%" + name + "%"); 

2. Using the CONCAT function in XML

If you don't want to concatenate strings in Java code, you can use SQL's CONCAT function to concatenate the percent sign and parameters in the XML mapping file.

Mapper interface XML example:

<select id="findByName" resultType="com.example.YourModel">
  SELECT * FROM your_table
  WHERE name LIKE CONCAT('%', #{name}, '%')
</select> 

3. Use <bind>tag

Mybatis provides<bind> tag allows you to create a variable in an XML file for concatenating strings or performing other logical operations. In this way, string concatenation can be achieved without modifying the Java code.

Mapper interface XML example:

<select id="findByName" resultType="com.example.YourModel">
  <bind name="pattern" value="'%' + name + '%'" />
  SELECT * FROM your_table
  WHERE name LIKE #{pattern}
</select> 

4. Use ${} for splicing (not recommended)

Although you can use ${} for string concatenation to implement LIKE query, this method can easily lead to SQL injection attacks, so it is not recommended.

Mapper interface XML example (not recommended):

<select id="findByName" resultType="com.example.YourModel">
  SELECT * FROM your_table
  WHERE name LIKE '%${name}%'
</select> 

5. Use MyBatis’ dynamic SQL choose, when, otherwise

Although not specifically used for LIKE queries, the choose, when, and otherwise tags can be used to build complex query logic, including choosing whether to execute fuzzy queries based on conditions.

Mapper interface XML example:

<select id="findByNameOrEmail" resultType="com.example.YourModel">
  SELECT * FROM your_table
  <where>
    <choose>
      <when test="name != null">
        name LIKE CONCAT('%', #{name}, '%')
      </when>
      <when test="email != null">
        email LIKE CONCAT('%', #{email}, '%')
      </when>
      <otherwise>
        1=1
      </otherwise>
    </choose>
  </where>
</select> 

In addition, there are some processing methods in the call layer code

6. Splicing the conditions of fuzzy query in Java code

You can also process the parameter values ​​into the format required for fuzzy query before calling the methods of the Mapper interface.

public interface YourMapper {
    List<YourModel> findByName(@Param("name") String name);
}

// transferMapperThe place
String searchName = "%" + name + "%";
List<YourModel> result = yourMapper.findByName(searchName); 

7. Use @Select annotation for fuzzy query

When using Mybatis Spring, you can directly use the @Select annotation on the method of the Mapper interface to define SQL statements to implement fuzzy queries.

public interface YourMapper {
    @Select("SELECT * FROM your_table WHERE name LIKE CONCAT('%', #{name}, '%')")
    List<YourModel> findByName(@Param("name") String name);
} 

The above are some common methods to implement LIKE fuzzy query in Mybatis. Choosing the appropriate method depends on the specific application scenario and personal preference. In actual use, attention should be paid to preventing the risk of SQL injection, especially when directly concatenating strings to construct query conditions.