MyBatis-Plus custom SQL and complex queries implementation

MyBatis-Plus is an enhanced version of MyBatis that provides many CRUD operations out of the box. However, in real projects, developers often need to write custom SQL to handle more complex query requirements. MyBatis-Plus provides a flexible mechanism that allows developers to customize SQL through annotations or XML, and can also support advanced operations such as complex queries and dynamic SQL.

1. Two ways to customize SQL

MyBatis-Plus supports two ways to customize SQL:

Annotation method: DirectlyMapperWrite SQL statements on the interface through annotations.
XML method:passMapperWrite custom SQL in the form of configuration files.

2. Write custom SQL through annotations

MyBatis-Plus supports it inMapperWrite annotations directly on interface methods to write SQL statements. Common annotations include:

@Select: Used for querying.
@Insert: Used for insertion.
@Update: Used for updates.
@Delete: Used to delete.

2.1 Query Example

Suppose there is oneUserTable, includingid、usernameandemailField. Write custom SQL to query user information through annotations:

import ;
import ;
import ;
import ;

import ;

@Mapper
public interface UserMapper extends BaseMapper&lt;User&gt; {

    // Query the user based on the user name    @Select("SELECT * FROM user WHERE username = #{username}")
    User findByUsername(String username);

    // Query all users    @Select("SELECT * FROM user")
    List&lt;User&gt; findAll();
}

2.2 Insert Example

Insert user records through annotation:

import ;

@Mapper
public interface UserMapper extends BaseMapper<User> {

    @Insert("INSERT INTO user (username, email) VALUES (#{username}, #{email})")
    void insertUser(User user);
}

2.3 Dynamic SQL

MyBatis-Plus also supports passing@SelectProvider、@UpdateProviderSuch annotations to implement dynamic SQL. The following is passed@SelectProviderExamples of dynamically generating query SQL:

import ;
import ;
import ;

@Mapper
public interface UserMapper extends BaseMapper<User> {

    @SelectProvider(type = , method = "findByCondition")
    List<User> findByCondition(Map<String, Object> conditions);

    class SqlProvider {
        public String findByCondition(Map<String, Object> params, ProviderContext context) {
            StringBuilder sql = new StringBuilder("SELECT * FROM user WHERE 1=1");
            if (("username") != null) {
                (" AND username = #{username}");
            }
            if (("email") != null) {
                (" AND email = #{email}");
            }
            return ();
        }
    }
}

In this example, by@SelectProviderDynamically generate SQL and query users based on incoming conditions.

3. Write custom SQL through XML

In addition to annotation methods, MyBatis-Plus also supports writing custom SQL through XML files. The XML configuration method allows for more complex and flexible queries and dynamic SQL.

3.1 Basic XML configuration structure

First, inresources/mapper/Created in the directory, and passnamespaceBindUserMapperInterface.

&lt;?xml version="1.0" encoding="UTF-8" ?&gt;
&lt;!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN"
    "/dtd/"&gt;
&lt;mapper namespace=""&gt;

    &lt;!-- Query all users --&gt;
    &lt;select  resultType=""&gt;
        SELECT * FROM user
    &lt;/select&gt;

    &lt;!-- Query user by username --&gt;
    &lt;select  resultType=""&gt;
        SELECT * FROM user WHERE username = #{username}
    &lt;/select&gt;

    &lt;!-- Insert the user --&gt;
    &lt;insert &gt;
        INSERT INTO user (username, email) VALUES (#{username}, #{email})
    &lt;/insert&gt;

&lt;/mapper&gt;

3.2 XML Dynamic SQL Example

The dynamic SQL tags of MyBatis can be used to build flexible SQL statements. Common dynamic SQL tags are:

<if>: Splicing SQL according to conditions.
<choose>: Similar toswitch-case。
<where>: Smart splicing conditions.
<foreach>: Used to process collections (such asINQuery).

The following example shows how to use dynamic SQL to query users based on conditions:

<select  resultType="">
    SELECT * FROM user
    <where>
        <if test="username != null">
            AND username = #{username}
        </if>
        <if test="email != null">
            AND email = #{email}
        </if>
    </where>
</select>

4. Implementation of complex queries

In actual development, complex queries often involve multi-table association queries, pagination, dynamic condition queries, etc. MyBatis-Plus provides good support, combining mechanisms such as SQL annotations, XML and query constructors to achieve flexible and complex queries.

4.1 Multi-table association query

MyBatis-Plus supports multi-table association query through custom SQL (such asJOINQuery).

<select  resultType="">
    SELECT , , r.role_name
    FROM user u
    LEFT JOIN role r ON u.role_id = 
    WHERE  = #{id}
</select>

In this example,userTable passedrole_idRelatedroleTable, query user role information.

4.2 Dynamic pagination query

Combined with the paging plug-in provided by MyBatis-Plus and dynamic SQL, complex paging queries can be implemented. First, we need to introduce a paging plugin:

import ;
import ;
import ;

@Configuration
public class MyBatisPlusConfig {

    @Bean
    public PaginationInterceptor paginationInterceptor() {
        return new PaginationInterceptor();
    }
}

Then inImplement paging query:

<select  resultType="">
    SELECT * FROM user
    <where>
        <if test="username != null">
            AND username = #{username}
        </if>
        <if test="email != null">
            AND email = #{email}
        </if>
    </where>
</select>

Use the pagination function when calling in the service layer:

import ;
import ;
import ;
import ;
import ;

import ;

@Service
public class UserService {

    @Autowired
    private UserMapper userMapper;

    public Page<User> findUsersByCondition(Page<User> page, Map<String, Object> conditions) {
        return (page, conditions);
    }
}

5. Use query constructor to perform complex queries

MyBatis-Plus provides a query constructorWrapper, used to simplify conditional query. Common query constructors are:

QueryWrapper: used to build query conditions.
UpdateWrapper: Used to build update conditions.

5.1 Use QueryWrapper to perform complex queries

QueryWrapperIt is a condition constructor provided by MyBatis-Plus, allowing developers to build query conditions using chained syntax.

import ;
import ;
import ;
import ;
import ;

import ;

@Service
public class UserService {

    @Autowired
    private UserMapper userMapper;

    public List<User> findUsersByCondition(String username, String email) {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        (username != null, "username", username)
                    .like(email != null, "email", email);
        return (queryWrapper);
    }
}

In this example,QueryWrapperUsed to dynamically build SQL queries based on conditions,eqandlikeThe method will generate corresponding SQL statements based on the passed parameters.

5.2 Use UpdateWrapper for complex updates

Similar to query,UpdateWrapperUsed to build update conditions.

import ;

public

 void updateUserEmail(Integer id, String email) {
    UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
    ("id", id).set("email", email);
    (null, updateWrapper);
}

6. Summary

MyBatis-Plus provides flexible custom SQL and complex query mechanisms, supporting flexible database operations through annotations, XML and query constructors.

Annotation method: Simple and intuitive, suitable for rapid development of common SQL operations.
XML method: Suitable for complex SQL and dynamic SQL. Through the tags provided by MyBatis, you can flexibly write conditional queries, multi-table association queries, etc.
Query constructor:passQueryWrapperandUpdateWrapper, developers can construct complex query conditions in chain syntax.

This is the end of this article about the implementation of MyBatis-Plus custom SQL and complex queries. For more related contents of MyBatis-Plus custom SQL and complex queries, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!