Non blocking mybatis based on MySQL asynchronous driver

Time:2021-10-14

Although spring5 also launched Webflux, a set of asynchronous technology stack, which greatly improves the throughput, has become popular in node, but asynchrony is still not the mainstream in the Java world. Vertx has done a lot of support for asynchrony, but its encapsulation of the data access layer is still very simple, Traditional javaer still can’t stand this tool library without object mapping, so I tried to transplant mybatis to asynchronous driver to make the work of data access layer easier. Give me an example:

@Sql(User.class)
public interface CommonMapper {
   @Select(columns = "id,age,username")
   @OrderBy("id desc")
   @Page
   @ModelConditions({
           @ModelCondition(field = "username", criterion = Criterions.EQUAL),
           @ModelCondition(field = "maxAge", column = "age", criterion = Criterions.LESS),
           @ModelCondition(field = "minAge", column = "age", criterion = Criterions.GREATER)
   })
   void query(UserSearch userSearch, DataHandler<List<User>> handler);
}

The above is the mapper interface definition. The last parameter of the method becomes a callback because of asynchrony. The difference is that there are many annotations to express SQL. It should not be difficult to guess the SQL statement when you see these annotations. If you don’t like it, of course you can continue to use mapper.xml to write SQL.

Move to the codebase for more~


AsyncDao

Asyncdao is a data access layer tool under asynchronous non blocking model.

  • MySQL only. Asynchronous driver based on MySQL
  • Using mybatis mapping and dynamic SQL for reference, mybatiser can switch seamlessly
  • The ability of annotations to express SQL
  • Transaction support
  • Springboot support

Mybatis like

The usage is almost the same as that of mybatis. Due to the asynchronous and non blocking relationship, the return of data will be completed through the callback datahandler, so the last parameter of the method definition must be the datahandler type. Since you need to extract the parameter name of the method, you need to add the compilation parameters-parameters, please configure it in IDE and Maven.

public interface CommonDao {

    void query(User user, DataHandler<List<User>> handler);

    void querySingle(User user, DataHandler<User> handler);

    void querySingleMap(User user, DataHandler<Map> handler);

    void insert(User user,DataHandler<Long> handler);

    void update(User user,DataHandler<Long> handler);

    void delete(User user,DataHandler<Long> handler);
}

Mapper.xml is written in almost the same way as mybatis (common tags are covered, some uncommon tags may not be supported, and annotation SQL function is recommended for dynamic SQL)

<?xml version="1.0" encoding="UTF-8"?>
<mapper namespace="com.tg.async.mapper.CommonDao">
    <resultMap id="BaseResultMap" type="com.tg.async.mapper.User">
        <id column="id" property="id"/>
        <result column="old_address" property="oldAddress"/>
        <result column="created_at" property="createdAt"/>
        <result column="password" property="password"/>
        <result column="now_address" property="nowAddress"/>
        <result column="state" property="state"/>
        <result column="age" property="age"/>
        <result column="username" property="username"/>
        <result column="updated_at" property="updatedAt"/>
    </resultMap>

    <select id="query" resultMap="BaseResultMap">select * from T_User
        <where>
            <if test="user.username!=null and user.username!=''">AND username = #{user.username}</if>
            <if test="user.age != null">OR age > #{user.age}</if>
        </where>
        order by id desc
    </select>


    <insert id="insert" useGeneratedKeys="true" keyProperty="id">insert into T_User
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="user.oldAddress != null">old_address,</if>
            <if test="user.createdAt != null">created_at,</if>
            <if test="user.password != null">password,</if>
            <if test="user.nowAddress != null">now_address,</if>
            <if test="user.state != null">state,</if>
            <if test="user.age != null">age,</if>
            <if test="user.username != null">username,</if>
            <if test="user.updatedAt != null">updated_at,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="user.oldAddress != null">#{user.oldAddress},</if>
            <if test="user.createdAt != null">#{user.createdAt},</if>
            <if test="user.password != null">#{user.password},</if>
            <if test="user.nowAddress != null">#{user.nowAddress},</if>
            <if test="user.state != null">#{user.state},</if>
            <if test="user.age != null">#{user.age},</if>
            <if test="user.username != null">#{user.username},</if>
            <if test="user.updatedAt != null">#{user.updatedAt},</if>
        </trim>
    </insert>

    <update id="update">
        update T_User
        <set>
            <if test="user.password != null">password=#{user.password},</if>
            <if test="user.age != null">age=#{user.age},</if>
        </set>
        where id = #{user.id}
    </update>
</mapper>

Annotation SQL

Writing SQL in XML is really repetitive work for some common SQL. So here you are allowed to use annotations to express SQL. What should you do?

Table is associated with model

@Table(name = "T_User")
public class User {
    @Id("id")
    private Long id;
    //It is recommended to use all wrapper types, and pay attention to the corresponding relationship between field types and Java types in MySQL. MySQL int will not be automatically replaced with long here

    private String username;
    private Integer age;

    @Column("now_address")
    private String nowAddress;

    @Column("created_at")
    private LocalDateTime createdAt;
    //The SQL time type in asyncdao uses joda. Note that it is not the one provided by jdk8, but the third-party package org.joda.time

    @Ignore
    private String remrk;

@Table records the name of the data table @ ID records the primary key information @ column maps the relationship between the table field and the attribute. If the table field has the same name as the class attribute, this annotation can be omitted @ ingore ignores the class attribute, and no table field is associated with it.

Define interface

@Sql(User.class)
public interface CommonDao {
    @Select(columns = "id,age,username")
    @OrderBy("id desc")
    @Page
    @ModelConditions({
            @ModelCondition(field = "username", criterion = Criterions.EQUAL),
            @ModelCondition(field = "maxAge", column = "age", criterion = Criterions.LESS),
            @ModelCondition(field = "minAge", column = "age", criterion = Criterions.GREATER)
    })
    void query(UserSearch userSearch, DataHandler<List<User>> handler);


    @Select(columns = "age,username")
    @OrderBy("id desc")
    void queryParam(@Condition String username,
                    @Condition(criterion = Criterions.GREATER) Integer age,
                    @OffSet int offset,
                    @Limit int limit,
                    DataHandler<List<User>> handler);


    @Select(columns = "username,age", sqlMode = SqlMode.COMMON)
    void queryList(@Condition(criterion = Criterions.IN, column = "id") int[] ids, DataHandler<List<User>> handler);

    @Insert(useGeneratedKeys = true, keyProperty = "id")
    void insert(User user, DataHandler<Long> handler);

    @Update
    @ModelConditions(@ModelCondition(field = "id"))
    void update(User user, DataHandler<Long> handler);

    @Delete
    @ModelConditions(@ModelCondition(field = "id"))
    void delete(User user, DataHandler<Long> handler);
}

After seeing these annotations, you should be able to guess what SQL looks like. Next, explain these annotations

query

@Select(columns = "id,age,username")
@OrderBy("id desc")
@Page
@ModelConditions({
       @ModelCondition(field = "username", criterion = Criterions.EQUAL),
       @ModelCondition(field = "maxAge", column = "age", criterion = Criterions.LESS),
       @ModelCondition(field = "minAge", column = "age", criterion = Criterions.GREATER)
})
void query(UserSearch userSearch, DataHandler<List<User>> handler);
@Select
  • columns: defaultselect *Can configurecolumns("username,age")Select some fields;
  • SqlMode: there are two options, sqlmode.selective and sqlmode.common. The difference is that selective will check whether the field of the query condition is null to realize dynamic query, that is, when the value is null, it will not become a query condition. also@Select@Count@Update@DeleteBothselectiveThis property.
@Condition
  • criterion: query criteria,=,<,>,inSee for detailsCriterions
  • column: corresponds to the table field. If it is the same as the field name, it can not be configured
  • attach: connectionand,or, default isand
  • test: sqlmode is the judgment expression under selective, similar to mybatis<if test="username != null">The test attribute in the dynamic query criteria

@Limit@OffSetIs a paging field.
The parameters of the method will be treated as query conditions without any annotation. For example, the effects of the following two functions are the same:

@Select()
void queryUser(Integer age,DataHandler<List<User>> handler);

@Select()
void queryUser(@Condition(criterion = Criterions.EQUAL, column = "age") Integer age,DataHandler<List<User>> handler);

Query model

In the above example, when there are many query conditions, there will be more method parameters. We can encapsulate the query conditions into a class and use@ModelConditionsTo annotate query conditions, note that@ModelConditionsAnnotated methods can only have two parameters, one is query model and the other is datahandler.

@Select
@Page
@ModelConditions({
       @ModelCondition(field = "username", criterion = Criterions.EQUAL),
       @ModelCondition(field = "minAge", column = "age", criterion = Criterions.GREATER),
       @ModelCondition(field = "maxAge", column = "age", criterion = Criterions.LESS),
       @ModelCondition(field = "ids", column = "id", criterion = Criterions.IN)
})
void queryUser5(UserSearch userSearch,DataHandler<List<User>> handler);
@ModelCondition
  • field: required, the attribute corresponding to the class in the query criteria
  • column: corresponding table field
  • test: judgment expression of dynamic SQL

@PageIt can only be used for queries under modelconditions, and the class of method parameters should haveoffsetlimitThese two properties, or use@Page(offsetField = "offset",limitField = "limit")Specify specific fields

Statistics

@Count
void count(DataHandler<Integer> handler);// Return long type

insert

@Insert (usegeneratedkeys = true, keyproperty = "Id") // returns the auto increment ID
void insert(User user, DataHandler<Long> handler);

to update

@Update (columns = "username, age") // select some columns to update
void update(User user, DataHandler<Long> handler);// Return affectedrows

delete

@Delete
int delete(@Condition(criterion = Criterions.GREATER, column = "age") int min,
          @Condition(criterion = Criterions.LESS, column = "age") int max,
          DataHandler<Long> handler);

@Delete
@ModelConditions(@ModelCondition(field = "id"))
void delete(User user, DataHandler<Long> handler);

use

Simple programming

AsyncConfig asyncConfig = new AsyncConfig();
PoolConfiguration configuration = new PoolConfiguration("username", "localhost", 3306, "password", "database-name");
asyncConfig.setPoolConfiguration(configuration);
asyncConfig.setMapperPackages("com.tg.async.mapper");// Mapper interface
asyncConfig.setXmlLocations("mapper/");// XML directory, relative path of classpath, absolute path is not supported
AsyncDaoFactory asyncDaoFactory = AsyncDaoFactory.build(asyncConfig);
CommonDao commonDao = asyncDaoFactory.getMapper(CommonDao.class);
   
UserSearch userSearch = new UserSearch();
userSearch.setUsername("ha");
userSearch.setMaxAge(28);
userSearch.setMinAge(8);
userSearch.setLimit(5);
CountDownLatch latch = new CountDownLatch(1);
commonDao.query(user, users -> {
  System.out.println(users);
  latch.countDown();
});
latch.await();

affair

Mybatis and spring system have a very useful@TranslactionalNote: we know that the essence of a transaction is to rely on the rollback and other operations of a connection. Therefore, multiple SQL statements under a transaction must share this connection. How to share it? ThreadLocal has become the perfect solution to achieve this under the traditional blocking system. In the asynchronous world, it is very difficult to implement the upper API like mybatis spring to complete transaction operations. The difficulty is that the API is too upper to realize connection sharing. So here we can go back to the second place, use a programmatic way to use transactions, and Abstract ATranslaction, the specific mappertranslaction.getMapper()To get through the sameTranslactionAll mappers will share a connection.

CountDownLatch latch = new CountDownLatch(1);
AsyncConfig asyncConfig = new AsyncConfig();
PoolConfiguration configuration = new PoolConfiguration("username", "localhost", 3306, "password", "database-name");
asyncConfig.setPoolConfiguration(configuration);
asyncConfig.setMapperPackages("com.tg.async.mapper");
asyncConfig.setXmlLocations("mapper/");
asyncDaoFactory = AsyncDaoFactory.build(asyncConfig);
asyncDaoFactory.startTranslation(res -> {
    Translaction translaction = res.result();
    System.out.println(translaction);
    CommonDao commonDao = translaction.getMapper(CommonDao.class);
    User user = new User();
    user.setUsername("insert");
    user.setPassword("1234");
    user.setAge(28);
    commonDao.insert(user, id -> {
        System.out.println(id);
        translaction.rollback(Void -> {
            latch.countDown();
        });
    });
});
latch.await();

SpringBoot

Although spring 5 has launched Webflux, asynchronous system is still not the mainstream in spring. In the process of asynchronous transformation, most people tend to keep spring’s IOC and leave the rest to vertx. Therefore, asyncdao’s support for spring is to inject mapper into the IOC container.

quick start

Yaml profile:

async:
    dao:
     Maperlocations: / mapper #xml directory, relative path of classpath, absolute path is not supported
     Basepackages: com.tg.mapper #mapper package
     username: username
     host: localhost
     port: 3306
     password: pass
     database: database-name
     maxTotal: 12
     maxIdle: 12
     minIdle: 1
     maxWaitMillis: 10000

add to@MapperTo achieve injection

@Mapper
@Sql(User.class)
public interface CommonDao {
    @Select(columns = "id,age,username")
    @OrderBy("id desc")
    @Page(offsetField = "offset", limitField = "limit")
    @ModelConditions({
            @ModelCondition(field = "username", criterion = Criterions.EQUAL),
            @ModelCondition(field = "maxAge", column = "age", criterion = Criterions.LESS),
            @ModelCondition(field = "minAge", column = "age", criterion = Criterions.GREATER)
    })
    void query(UserSearch userSearch, DataHandler<List<User>> handler);
}

adopt@EnableAsyncDaoTo enable support, a simple example:

@SpringBootApplication
@EnableAsyncDao
public class DemoApplication {

    public static void main(String[] args){
        ApplicationContext applicationContext = SpringApplication.run(DemoApplication.class);
        CommonDao commonDao = applicationContext.getBean(CommonDao.class);

        UserSearch userSearch = new UserSearch();
        userSearch.setUsername("ha");
        userSearch.setMaxAge(28);
        userSearch.setMinAge(8);
        userSearch.setLimit(5);

        commonDao.query(userSearch, users -> {
            System.out.println("result: " + users);
        });
    }
}

Recommended Today

The selector returned by ngrx store createselector performs one-step debugging of fetching logic

Test source code: import { Component } from ‘@angular/core’; import { createSelector } from ‘@ngrx/store’; export interface State { counter1: number; counter2: number; } export const selectCounter1 = (state: State) => state.counter1; export const selectCounter2 = (state: State) => state.counter2; export const selectTotal = createSelector( selectCounter1, selectCounter2, (counter1, counter2) => counter1 + counter2 ); // […]