Custom Query Methods in Spring Boot

In practical scenarios of Spring Boot Web development, database operations often constitute a core part of the application logic. Although ORM frameworks like MyBatis-Plus and JPA (Hibernate) provide rich fundamental features, predefined query methods often fall short when facing complex business scenarios, such as multi-table filtering, dynamic condition concatenation, or specific aggregation statistics requirements. At this point, writing custom SQL statements and encapsulating them into Java methods via the AOP proxy mechanism—known as "custom queries"—becomes a key approach to improving development efficiency and code maintainability. This article systematically explains how to efficiently implement custom query methods in Spring Boot projects.
Custom query methods in Spring Boot projects do not execute SQL directly; instead, they rely on the underlying mechanisms of MyBatis-Plus. The core technology lies in binding Java code methods with database table structures and intercepting method calls through AOP (Aspect-Oriented Programming) proxy technology to ultimately generate corresponding SQL statements. For complex dynamic logic, XML mapping files are typically used as auxiliary tools to define specific SELECT, INSERT, or UPDATE operations. This approach retains the type safety and readability of Java code while leveraging the flexibility of XML to handle non-standard query requirements.

Basic Configuration: Enabling Custom SQL Support

Before writing specific logic, it is crucial to ensure that the project has correctly introduced the MyBatis-Plus dependency and enabled relevant features. The first step involves adding the following dependency declarations via Maven or Gradle:

mybatis-plus-boot-starter: Provides core annotations and interceptor support.
spring-boot-starter-web: Serves as the foundational framework for Web development.

After configuration, corresponding field mapping relationships must be defined in Entity classes. For example, if there is a user table containing fields such as name and age, the Entity class should use the @TableField(name = "user_name") annotation to explicitly specify the correspondence between database column names and Java field names; this is a prerequisite for custom queries to accurately retrieve data.

Implementing Dynamic Condition Concatenation: LambdaQueryWrapper

When facing complex filtering conditions (e.g., "age greater than 25 AND city is Beijing"), hardcoding SQL is not only difficult to maintain but also prone to injection risks. MyBatis-Plus provides the LambdaQueryWrapper class to elegantly solve this problem. Developers simply need to construct query objects through chained calls, and the framework will automatically handle the concatenation of logical operators and the generation of parameter placeholders.

Below is a typical dynamic query example:

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
// Assume UserEntity entity class is already defined
public List<User> queryUsersByAgeAndCity() {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    
    // Add condition: age greater than 25
    wrapper.and(w -> w.gt(User::getAge, 25));
    
    // Add condition: city equals "Beijing" (Note: adjust based on actual field name)
    wrapper.eq(User::getCity, "北京");

    return userMapper.selectList(wrapper);
}

In the code above, LambdaQueryWrapper automatically converts gt and eq into SQL fragments similar to age > ? AND city = ?, significantly reducing the difficulty of writing complex queries.

Handling Multi-Table Joins and Custom Logic: XML Mapping Files

When business requirements involve joint queries across multiple tables (Joins) or require execution of specific aggregate functions, annotations alone may not be intuitive or flexible enough. In such cases, one should utilize the XML mapping mechanism provided by MyBatis-Plus to write complex SQL statements. By combining the @Select annotation with a custom Mapper interface method name, part of the logic can be shifted to the XML file, keeping the Java code concise.

In the XML configuration file (typically located at mapper/CustomQueryMapper.xml), the following query fragment can be defined:

<select id="selectComplexUsers" resultType="User">
    SELECT 
        u.id, u.name, a.age, c.city_name
    FROM user AS u
    LEFT JOIN address AS a ON u.address_id = a.id
    LEFT JOIN city_info AS ci ON a.city_code = ci.code
    WHERE u.status = 1 AND (a.is_active = true OR ci.region_type = 'urban')
</select>

The corresponding Mapper interface method signature is as follows:

public interface CustomQueryMapper {
    @Select("SELECT * FROM user") // Can be combined with id="selectComplexUsers" in XML
    List<User> selectAll();

    // Method to invoke custom SQL logic; ensure parameter types match or handle as Map
    List<Map<String, Object>> getDetailedUserInfo(User entity); 
}

Through this method, developers can flexibly extend database operation capabilities without breaking the existing code structure.

Best Practices and Precautions

When implementing custom query methods, the following points must be observed to ensure system robustness:

Prevent SQL Injection: Always use the parameterized query mechanism provided by MyBatis-Plus (e.g., ?); strictly prohibit directly concatenating user input into strings for execution.
Transaction Management: Custom operations involving data writes should be paired with Spring transaction annotations to ensure data atomicity.
Performance Optimization: For complex queries executed frequently, it is recommended to establish appropriate indexes at the database level in advance to avoid performance bottlenecks caused by full table scans.

In summary, mastering the techniques for writing custom query methods is an essential course for every Spring Boot Web developer advancing their skills. It not only enables handling of complex business logic challenges but also significantly enhances code readability and maintainability, aiding in the construction of high-performance enterprise-level application systems.