Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

This article has participated in the “Digitalstar Project” and won a creative gift package to challenge the creative incentive money.

Recently in my work, I came into contact with a MyBatis extension toolkit MyBatis -ext, which can be said to greatly reduce the workload of using MyBatis. In this article, I will share this lightweight extension tool with you.

MyBatis-Ext is MyBatis enhanced extension, and we usually use MyBatis- plus is very similar, simplified MyBatis on a single table to add, delete, change, search, provide general add, delete, change, search, support functional programming, support paging query, support user custom general method, and can prevent SQL injection. Integration is also very simple, MyBatis is only enhanced without modification.

In the case of a Spring-Boot project, the integration is simple. Pom imports core dependencies:

<dependency>
    <groupId>tech.wetech.mybatis</groupId>
    <artifactId>mybatis-ext-core</artifactId>
    <version>1.5.2</version>
</dependency>
<dependency>
    <groupId>tech.wetech.mybatis</groupId>
    <artifactId>mybatis-ext-spring-boot-starter</artifactId>
    <version>1.5.2</version>
</dependency>    
Copy the code

Note that there is no need to introduce mybatis-spring-boot-starter after introducing mybatis-ext-spring-boot-starter.

As before, configure the data source in application.yml:

spring:
  datasource:
    username: dater
    password: 123456
    url: JDBC: mysql: / / 127.0.0.1:3306 / datacenter? useUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      initial-size: 8
      min-idle: 1
      max-active: 20
mybatis:
  mapper-locations: classpath:mapping/*Mapper.xml
  type-aliases-package: com.mybatis.ext.test.mybatisexttest.entity
spring:
  datasource:
    username: dater
    password: 123456
    url: JDBC: mysql: / / 127.0.0.1:3306 / datacenter? useUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      initial-size: 8
      min-idle: 1
      max-active: 20
mybatis:
  mapper-locations: classpath:mapping/*Mapper.xml
  type-aliases-package: com.mybatis.ext.test.mybatisexttest.entity
Copy the code

Create a mapped entity class:

@Data
@Table(name = "user")
public class User {
    @Id
    String identifycard;
    @Column(name="name")
    String name;
    String money;
    String card;
    String phone;
    String rate;
}
Copy the code

Mybatis -ext uses Jpa annotations and currently implements @table, @ID, @column, @TRANSIENT, @version. @table and @ID are mandatory annotations, and other annotations are optional. Use @table to specify the name of the Table and @id to specify the primary key of the Table.

The query Mapper interface inherits the BaseMapper interface and fills the entity class in the generic:

public interface UserMapper extends BaseMapper<User> {}Copy the code

BaseMapper is built with a number of generic methods that can be called directly. It is very simple:

int deleteByPrimaryKey(PK id);
<S extends T> int insert(S record);
<S extends T> int insertAll(Iterable<S> record);
<S extends T> int insertSelective(S record);
<S extends T> S selectByPrimaryKey(PK id);
<S extends T> Optional<S> selectByPrimaryKeyWithOptional(ID id);
<S extends T> int updateByPrimaryKey(S record);
<S extends T> int updateByPrimaryKeySelective(S record);
<S extends T> List<S> selectAll(a);
<S extends T> List<S> selectList(S record);
<S extends T> S selectOne(S record);
<S extends T> S selectOneWithOptional(S record);
boolean existsByPrimaryKey(PK id);
<S extends T> int count(S record);
<S extends T> List<S> selectByExample(Example<S, Object> example);
<S extends T> int countByExample(Example<S, Object> example);
<S extends T> int deleteByExample(Example<S, Object> example);
<S extends T> int updateByExample(@Param("record") S record, @Param("example") Example<S, Object> example);
<S extends T> int updateByExampleSelective(@Param("record") S record, @Param("example") Example<S, Object> example);
Copy the code

To test the interface call, try the selectAll method first:

@GetMapping("getUser")
public void getUser(a){
    List<User> users = userMapper.selectAll();
    for (User user : users) {
        System.out.println(user.getName()+""+user.getIdentifycard()); }}Copy the code

Test results:

In this way, you can query directly without writing an SQL statement by calling the built-in methods. Similarly, if you want to query by primary key, it’s easy to call the selectByPrimaryKey method directly:

@PostMapping("getUserById")
public void getUserByIdentifycard(@RequestBody User user){
    User retUser = userMapper.selectByPrimaryKey(user);
    System.out.println(retUser.toString());
}
Copy the code

Query result:

Alternatively, you can modify the primary key query method above with Optional package query:

@PostMapping("getUserById")
public void getUserByIdentifycard(@RequestBody User user){
    User retUser = userMapper.selectByPrimaryKeyWithOptional(user)
            .orElseThrow(()->new RuntimeException("No data found."));
    System.out.println(retUser.toString());
}
Copy the code

If a primary key does not exist, a custom exception will be thrown:

There are many other simple queries that you can test yourself against the apis listed above. In addition, you can use Criteria for functional queries using logical combinations:

@GetMapping("criteriaTest")
public void testCreateCriteria(a){
    List<User> list = userMapper.createCriteria()
            .andEqualTo(User::getName, "Trunks")
            .andBetween(User::getMoney, 100.300)
            .andNotLike(User::getRate, "6")
            .orIn(User::getCard, Arrays.asList("10"))
            .selectList();

    list.forEach(user -> {
        System.out.println(user.toString());
    });
}
Copy the code

Query result:

You can also use Example to query:

@GetMapping("exampleTest")
public void testExample(a){
    Example<User> example=Example.of(User.class);
    example.createCriteria()
            .andEqualTo(User::getName, "Trunks")
            .andBetween(User::getMoney, 100.300)
            .andNotLike(User::getRate, "6")
            .orIn(User::getCard, Arrays.asList("10"));

    example.setDistinct(true);
    List<User> list = userMapper.selectByExample(example);
    list.forEach(user -> {
        System.out.println(user.toString());
    });
}
Copy the code

The results are the same as using Criteria. Alternatively, multiple conditions can be combined:

GetMapping("testExampleWithSub")
public void selectByExampleWithSub(a){
    try (SqlSession session = sqlSessionFactory.openSession()) {
        UserMapper userMapper1 = session.getMapper(UserMapper.class);
        Example<User> example=Example.of(User.class);
        example.and()
                .andEqualTo(User::getName, "Trunks");
        example.and()
                .andEqualTo(User::getCard,"10");
        example.and()
                .andLessThanOrEqualTo(User::getRate,300);

        Criteria<User> criteria=newCriteria<>(); criteria.andIsNotNull(User::getPhone); example.and(criteria); List<User> list = userMapper1.selectByExample(example); list.forEach(user -> { System.out.println(user.toString()); }); }}Copy the code

Results:

In addition to the above introduction of these functions and the basic SQL add, delete, change, search, MyBatis-Ext also implements a lot of other functions, such as sorting and paging, and support custom general interface methods, etc., you can continue to explore in the use of practice.

The last

If you think it is helpful, you can like it and forward it. Thank you very much

Public number agriculture ginseng, add a friend, do a thumbs-up friend ah