This document is a series, with the preceding chapters:

  • Integration of SpringBoot quickly start to add, delete, change and check
  • MybatisPlus part 2 – Application and summary of conditional builders

Although Mybatis Plus helps us to provide a large number of default methods, but we in order to achieve multi-table associated query, or according to different query conditions to transfer parameters, to achieve different dynamic SQL. In this case we still need to customize the SQL, but we need to specify the location of the mapper.xml file by configuration first anyway.

mybatis-plus:
  mapper-locations: classpath*:/mapper/*Mapper.xmlCopy the code

1. Original custom SQL methods

In my opinion, it is still Mybatis best practice to write multi-table associated query or dynamic SQL in XML file for maintenance in most scenarios. It is a best practice to use Mybatis Plus or Mybatis Generator to generate code for a single table.

  • The UserMapper interface is placed in@MapperScanBelow the configured scan path. This method is provided by Mybatis for us, and can still be used in Mybatis Plus without any delay!
  • Using the most primitive Mybatis SQL definition method, on the basis of integrating BaseMapper (Mybatis Plus), define a new interface method findUser.
public interface UserMapper extends BaseMapper<User> {
  List<User> findUser(@Param("name") String name, @Param("email") String email);
}Copy the code

Create a new usermapper. XML and place it under the mybatis-plus.mapper-locations configuration path. The following dynamic SQL representation is used:

  • When name is not null or an empty string,AND name = #{name}Conditions to take effect
  • When email is not null or an empty string,AND email = #{email}Conditions to take effect
<! - inside the dynamic SQL, multi-table associated query can be competent for - > < select id = "findUser resultType" = "com. Zimug. Example. The model. The User" > select id, name, age and email FROM user <trim prefix="WHERE" prefixOverrides="AND|OR" suffixOverrides="AND|OR"> <if test="name ! = null and name ! = '' " > AND name = #{name} </if> <if test="email ! = null and email ! = '' " > AND email= #{email} </if> </trim> </select>Copy the code

Use the test

@test public void testSQL1 () {String name = "letter "; //name is not empty String email = ""; List<User> List = usermapper.findUser (name,email); list.forEach(System.out::println); }Copy the code

The final SQL is executed (since email is an empty string, the corresponding query condition is not built in dynamic SQL) :

SELECT id,name,age,email 
FROM user 
WHERE name = ? Copy the code

2. Custom interface methods use the Wrapper condition constructor

If we want to use the Wrapper condition constructor in a custom method. You can refer to the following implementation. This approach is simple, but still only applies to a single table (it can be a multi-table associative query, but the query criteria are also based on a single table).

  • Use annotation mode + Wrapper,${ew.customSqlSegment}Is a placeholder for the query criteria and represents the Wapper query criteria. 
    @Select("select * from `user` ${ew.customSqlSegment}")
List<User> selectAll(@Param(Constants.WRAPPER) Wrapper wrapper);Copy the code
  • Use XML configuration + Wrapper 
    List<User> selectAll(@Param(Constants.WRAPPER) Wrapper wrapper);Copy the code
    <select id="selectAll" resultType="com.zimug.example.model.User">
select * from `user` ${ew.customSqlSegment}
</select>Copy the code

Query parameters are passed through Wapper

Choose either of the two methods, and both arguments are wrappers

@Test public void testCustomSQL2() { LambdaQueryWrapper<User> query = new LambdaQueryWrapper<>(); Query.eq (User::getName, "letter "); List<User> list = userMapper.selectAll(query); list.forEach(System.out::println); }Copy the code

The resulting SQL is executed (the same as the original XML dynamic SQL implementation above, but the query condition is constructed for a single table) :

SELECT id,name,age,email 
FROM user 
WHERE name = ? Copy the code

Welcome to my blog, where there are many fine collections

  • This article is reprinted with a credit (must be accompanied by a link, not only the text) : Antetokounmpo blog.

Feel helpful to you, help me like, share! Your support is my inexhaustible creative power! . In addition, the author recently a period of time output as follows boutique content, looking forward to your attention.

  • Spring Boot2.0 by Hand
  • Spring Security- JWT-OAUTH2
  • RBAC Authority Management System for Actual Combat Front-end and Back-end Separation
  • “Actual SpringCloud Micro-service from Bronze to King”
  • VUE Series