Prologue: Use the annotations provided with Myatis3 to progressively replace XML, such as using@SelectAnnotate directly write SQL to complete the data query, using@SelectProviderAdvanced annotations can also write dynamic SQL to address complex business requirements.

1. Basic notes

MyBatis provides the following CRUD annotations:

  • @Select
  • @Insert
  • @Update
  • @Delete

Add, delete, modify, and review account for the majority of business operations, so it is important to master the use of these basic annotations, such as the following code to query data without XML:

@Mapper
public interface UserMapper {
    @Select("select * from t_user")
    List<User> list();
}
Copy the code

Those of you who have used Hibernate may wonder why property injection can be done without configuring mappings. Children’s shoes that have used Mybatis in a traditional project may quickly realize this because global hump mapping is enabled in the configuration file, which is also easier and faster to do in SpringBoot.

If the User attribute mobileNum is used and the corresponding database attribute phoneNum is used, the query result will be null:

[
  {
    "userId": "1",
    "username": "admin",
    "password": "admin",
    "mobileNum": null
  },
  {
    "userId": "2",
    "username": "roots",
    "password": "roots",
    "mobileNum": null
  }
]
Copy the code

To solve the problem of inconsistent object attributes and field humps, we can specify the mapping using the mapping annotation @Results.

2. Mapping annotations

Mybatis mainly provides these mapping annotations:

  • @results is used to fill in the mapping of multiple fields in the result set.
  • @result is used to fill in the mapping of individual fields in the Result set.
  • @resultMap Associates < ResultMap > in the XML according to the ID.

For example, in the list method above, we can specify the mapping relationship of the returned result set based on the SQL query, where property represents the attribute name of the entity object and column represents the corresponding database field name.

   @Results({
            @Result(property = "userId", column = "USER_ID"),
            @Result(property = "username", column = "USERNAME"),
            @Result(property = "password", column = "PASSWORD"),
            @Result(property = "mobileNum", column = "PHONE_NUM")
    })
    @Select("select * from t_user")
    List<User> list();
Copy the code

The query results are as follows:

[
  {
    "userId": "1",
    "username": "admin",
    "password": "admin",
    "mobileNum": "15011791234"
  },
  {
    "userId": "2",
    "username": "roots",
    "password": "roots",
    "mobileNum": "18812342017"
  }
]
Copy the code

For the sake of demonstration and to avoid writing mappings manually, here is a quick way to generate a mapping result set as follows:

Public static String getResultsStr(Class Origin) {StringBuilder StringBuilder = new StringBuilder(); stringBuilder.append("@Results({\n"); for (Field field : origin.getDeclaredFields()) { String property = field.getName(); // Mapping: Object properties (Hump)-> Database field (underscore) String Column = new PropertyNamingStrategy.SnakeCaseStrategy().translate(field.getName()).toUpperCase(); stringBuilder.append(String.format("@Result(property = \"%s\", column = \"%s\"),\n", property, column)); } stringBuilder.append("})"); return stringBuilder.toString(); }Copy the code

The Main method works like this: Then we copy the console print to the interface method.

Advanced notes

MyBatis-3 mainly provides the following advanced annotations for CRUD:

  • @SelectProvider
  • @InsertProvider
  • @UpdateProvider
  • @DeleteProvider

As the name implies, these advanced annotations are mainly used for dynamic SQL. In this case, @selectProvider is used as an example. They contain two annotation properties, type representing the utility class and Method representing a method of the utility class, which is used to return specific SQL.

@Mapper
public interface UserMapper {
    @SelectProvider(type = UserSqlProvider.class, method = "list222")
    List<User> list2();
}
Copy the code

The code of the utility class is as follows:

public class UserSqlProvider {
    public String list222() {
        return "select * from t_user ;
    }
Copy the code

Detailed tutorial

With some understanding of the above annotations, we use project examples to further reinforce their practical use.

Specific steps

1. Introduce dependencies

In order to facilitate the demonstration, the first choice is to build a Web environment, and the database is Mysql 5.5+.

<dependencies> <dependency> <! GroupId >org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency>  <dependency> <! GroupId > <artifactId> Mybatis -spring-boot-starter</artifactId> The < version > 1.3.1 < / version > < / dependency > < the dependency > <! <artifactId> connection-java </artifactId> <scope>runtime</scope> </dependency> <dependency><! GroupId >org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies>Copy the code

2. Add the configuration

This is to add data sources, configure hump mapping, and enable console printing of SQL logs. In the project’s resource directory, add application.yml configuration as follows:

Spring: a datasource: # connect MySQL url: JDBC: MySQL: / / localhost: 3306 / socks? useSSL=false username: root password: root driver-class-name: com.mysql.jdbc.Driver mybatis: configuration: # Config item: Enable automatic conversion of underline to hump. Effect: Automatically injects database fields into object properties according to the hump rule. Map-underscore -to-camel-case: true logging: level: # Print SQL messages com.hehe.mapper: debugCopy the code

3. Write data layer code

Here we are familiar with the user information as an example, to write the UserMapper interface and the case to use the UserSqlProvider.

3.1 UserMapper

Add UserMapper interface for data query:

package com.hehe.mapper; @mapper public interface UserMapper {/** * Method 1: Use annotations to write SQL. */ @Select("select * from t_user") List<User> list(); /** * Mode 2: */ @selectProvider (type = usersqlProvider.class, method = "listByUsername") List<User> listByUsername(String username); /** * extension: Either way you can append the @results annotation to specify the mapping of the result set. ** PS: if the match matches the underline to hump, you can omit it. */ @Results({ @Result(property = "userId", column = "USER_ID"), @Result(property = "username", column = "USERNAME"), @Result(property = "password", column = "PASSWORD"), @Result(property = "mobileNum", column = "PHONE_NUM") }) @Select("select * from t_user") List<User> listSample(); /** * extension: Either way, if more than one parameter is involved, the @param annotation must be used, otherwise the parameter cannot be retrieved using an EL expression. */ @Select("select * from t_user where username like #{username} and password like #{password}") User get(@Param("username") String username, @Param("password") String password); @SelectProvider(type = UserSqlProvider.class, method = "getBadUser") User getBadUser(@Param("username") String username, @Param("password") String password); }Copy the code

3.2 UserSqlProvider

Add UserSqlProvider, the utility class used to generate SQL.

package com.hehe.mapper; /** * Main purpose: to dynamically generate SQL based on complex business requirements. * <p> * Goal: To replace traditional XML files with Java utility classes (e.g. Java <-- usermapper.xml) */ public class UserSqlProvider {/** * Method 1: In the tool class method, you can manually write SQL. */ public String listByUsername(String username) { return "select * from t_user where username =#{username}"; } /** * Method 2: you can also write dynamic SQL according to the official API. */ public String getBadUser(@Param("username") String username, @Param("password") String password) { return new SQL() {{ SELECT("*"); FROM("t_user"); if (username ! = null && password ! = null) { WHERE("username like #{username} and password like #{password}"); } else { WHERE("1=2"); } }}.toString(); }}Copy the code

3.3 Entity Class User

Add the entity class User

public class User {
    private String userId;
    private String username;
    private String password;
    private String mobileNum;
    //Getters & Setters
}
Copy the code

3.4 Adding Database Records

Open the Navicat query window and simply use the following script.

USE `SOCKS`;
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
  `USER_ID` varchar(50) ,
  `USERNAME` varchar(50) ,
  `PASSWORD` varchar(50) ,
    `PHONE_NUM` varchar(15) 
) ;

INSERT INTO `t_user` VALUES ('1', 'admin', 'admin','15011791234');
INSERT INTO `t_user` VALUES ('2', 'roots', 'roots','18812342017');
Copy the code

4. Write code for the control layer

package com.hehe.controller; @RestController @RequestMapping("/user/*") public class UserController { @SuppressWarnings("all") @Autowired UserMapper userMapper; @GetMapping("list") public List<User> list() { return userMapper.list(); } @GetMapping("list/{username}") public List<User> listByUsername(@PathVariable("username") String username) { return userMapper.listByUsername(username); } @GetMapping("get/{username}/{password}") public User get(@PathVariable("username") String username, @PathVariable("password") String password) { return userMapper.get(username, password); } @GetMapping("get/bad/{username}/{password}") public User getBadUser(@PathVariable("username") String username, @PathVariable("password") String password) { return userMapper.getBadUser(username, password); }}Copy the code

5. Start and test

After start the project, visit http://localhost:8080/user/list to view the list of users is as follows:

Visit http://localhost:8080/user/list/admin can query called admin user information:

Source code and documentation

SpringBoot-MyBatis-Annotation

Official documentation: Mybatis3- Chinese manual

Originally written by Yizhiwazi


Original link:
SpringBoot quickly integrate Mybatis (de-xmlized + annotated advanced)


Copyright: This article is written by APPx Applets (
appx.dreawer.com) signed blogger contribution, copyright belongs to the author all, reprint please indicate, have any questions, please contact us, thank you!