Using MyBatisPlus to Execute MySQL's find_in_set Function

In real-world application development, there are frequently scenarios where we need to query database records containing a specific value within a comma-separated field. MySQL provides the find_in_set function specifically for this purpose. When working with Java applications, we can leverage MyBatisPlus to execute such queries conveniently and efficiently.

Understanding the find_in_set Function

The find_in_set function is a string manipulation function in MySQL that searches for a specified string within a comma-separated list and returns its position. The syntax is as follows:

find_in_set(search_string, comma_separated_list)

Parameters:

• search_string - The string to search for
• comma_separated_list - A string containing comma-separated values

If the search string is found, the functon returns its position in the list (starting from 1). If not found, it returns 0.

Implementing find_in_set Queries with MyBatisPlus

Let's demonstrate how to execute find_in_set queries using MyBatisPlus with a practical example.

Consider a user table with a permissions field that stores user permissions as comma-separated values:

To find all users who have permission "3", we can use the following MyBatisPlus implementation:

// Define the Mapper interface
public interface UserMapper extends BaseMapper<User> {
    
    @Select("SELECT * FROM user WHERE find_in_set('3', permissions) > 0")
    List<User> fetchUsersByPermission();
}

// Invoke from Service layer
List<User> userList = userMapper.fetchUsersByPermission();
userList.forEach(user -> System.out.println(user.getUsername()));

In this implementation, the @Select annotation allows us to write raw SQL statements directly. The fetchUsersWithPermission method returns all users containing the specified permission.

It's important to note the > 0 condition in the SQL query. Since find_in_set returns 0 when no match is found, adding this condition ensures we only retrieve records where the permission actually exists in the list.

Alternative Approach Using QueryWrapper

MyBatisPlus also provides a dynamic query approach using the QueryWrapper class:

public List<User> findUsersWithPermission(String permission) {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.apply("find_in_set({0}, permissions) > 0", permission);
    return userMapper.selectList(queryWrapper);
}

This approach offers better SQL injection protection through proper parameter binding.