feature

  • 1. Very lightweight, the entire framework only relies on Mybatis and no other third-party dependencies
  • 2. Only enhanced, supports Entity addition, deletion, modification and query, and paging query, but does not lose the original functions of Mybatis
  • 3. The built-in Db + Row tool can add, delete, modify and check the database without entity classes
  • 4. Support multiple database types, and can also continue to expand through dialects
  • 5. Support multiple (joint) primary keys and different primary key content generation strategies
  • 6. Support tombstone settings, default value configuration for updates or inserts, and settings for large fields
  • 7. Support optimistic lock field configuration, automatically perform optimistic lock detection when data is updated
  • 8. Extremely friendly SQL linkage query, IDE automatically prompts no more worries about mistakes
  • 9. More small surprises

QQ group

Group number: 532992631

start

hello world (native)

Step 1: Write the Entity entity class


@Table("tb_account")
public class Account {

    @Id(keyType = KeyType.Auto)
    private Long id;
    private String userName;
    private Date birthday;
    private int sex;

    //getter setter
}

Step 2: Start querying data

Example 1: Query 1 piece of data

class HelloWorld {
    public static void main(String... args) {

        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/mybatis-flex");
        dataSource.setUsername("username");
        dataSource.setPassword("password");

        MybatisFlexBootstrap.getInstance()
                .setDatasource(dataSource)
                .addMapper(AccountMapper.class)
                .start();


        //示例1：查询 id=100 条数据
        Account account = MybatisFlexBootstrap.getInstance()
                .execute(AccountMapper.class, mapper ->
                        mapper.selectOneById(100)
                );
    }
}

Above AccountMapper.class It is automatically generated by APT for Mybatis-Flex without manual coding. You can also turn off the automatic generation function and write AccountMapper manually. See the APT documentation for more information.

Example 2: Query List

//示例2：通过 QueryWrapper 构建条件查询数据列表
QueryWrapper query=QueryWrapper.create()
    .select()
    .from(ACCOUNT)
    .where(ACCOUNT.ID.ge(100))
    .and(ACCOUNT.USER_NAME.like("张").or(ACCOUNT.USER_NAME.like("李")));

// 执行 SQL：
// ELECT * FROM tb_account
// WHERE tb_account.id >=  100
// AND (tb_account.user_name LIKE '%张%' OR tb_account.user_name LIKE '%李%' )
List<Account> accounts = MybatisFlexBootstrap.getInstance()
    .execute(AccountMapper.class,mapper->
        mapper.selectListByQuery(query)
    );

Example 3: Paging query

// 示例3：分页查询
// 查询第 5 页，每页 10 条数据，通过 QueryWrapper 构建条件查询
QueryWrapper query=QueryWrapper.create()
    .select()
    .from(ACCOUNT)
    .where(ACCOUNT.ID.ge(100))
    .and(ACCOUNT.USER_NAME.like("张").or(ACCOUNT.USER_NAME.like("李")))
    .orderBy(ACCOUNT.ID.desc());

// 执行 SQL：
// ELECT * FROM tb_account
// WHERE id >=  100
// AND (user_name LIKE '%张%' OR user_name LIKE '%李%' )
// ORDER BY `id` DESC
// LIMIT 40,10
Page<Account> accounts = MybatisFlexBootstrap.getInstance()
.execute(AccountMapper.class,mapper->
    mapper.paginate(5,10,query)
);

QueryWrapper example

select*

QueryWrapper query=new QueryWrapper();
query.select().from(ACCOUNT)

// SQL: 
// SELECT * FROM tb_account

select columns

Simple example:

QueryWrapper query=new QueryWrapper();
query.select(ACCOUNT.ID,ACCOUNT.USER_NAME)
    .from(ACCOUNT)

// SQL: 
// SELECT id, user_name 
// FROM tb_account

Multi-table query (while showing powerful as ability):

QueryWrapper query = new QueryWrapper()
    .select(ACCOUNT.ID
        , ACCOUNT.USER_NAME
        , ARTICLE.ID.as("articleId")
        , ARTICLE.TITLE)
    .from(ACCOUNT.as("a"), ARTICLE.as("b"))
    .where(ACCOUNT.ID.eq(ARTICLE.ACCOUNT_ID));

// SQL: 
// SELECT a.id, a.user_name, b.id AS articleId, b.title 
// FROM tb_account AS a, tb_article AS b 
// WHERE a.id = b.account_id

select functions

QueryWrapper query=new QueryWrapper()
    .select(
        ACCOUNT.ID,
        ACCOUNT.USER_NAME,
        max(ACCOUNT.BIRTHDAY),
        avg(ACCOUNT.SEX).as("sex_avg")
    ).from(ACCOUNT);

// SQL: 
// SELECT id, user_name, 
// MAX(birthday), 
// AVG(sex) AS sex_avg 
// FROM tb_account

where

QueryWrapper queryWrapper=QueryWrapper.create()
    .select()
    .from(ACCOUNT)
    .where(ACCOUNT.ID.ge(100))
    .and(ACCOUNT.USER_NAME.like("michael"));

// SQL: 
// SELECT * FROM tb_account 
// WHERE id >=  ?  
// AND user_name LIKE  ?

where dynamic condition 1

boolean flag = false;
QueryWrapper queryWrapper = QueryWrapper.create()
    .select().from(ACCOUNT)
    .where(flag ? ACCOUNT.ID.ge(100) : noCondition())
    .and(ACCOUNT.USER_NAME.like("michael"));

// SQL: 
// SELECT * FROM tb_account 
// WHERE user_name LIKE  ?

where dynamic condition 2

boolean flag = false;
QueryWrapper queryWrapper = QueryWrapper.create()
    .select().from(ACCOUNT)
    .where(ACCOUNT.ID.ge(100).when(flag))
    .and(ACCOUNT.USER_NAME.like("michael"));

// SQL: 
// SELECT * FROM tb_account 
// WHERE user_name LIKE  ?

where select

QueryWrapper queryWrapper = QueryWrapper.create()
    .select()
    .from(ACCOUNT)
    .where(ACCOUNT.ID.ge(
       select(ARTICLE.ACCOUNT_ID).from(ARTICLE).where(ARTICLE.ID.ge(100))
    ));

// SQL: 
// SELECT * FROM tb_account
// WHERE id >= 
// (SELECT account_id FROM tb_article WHERE id >=  ? )

exists, not exists

QueryWrapper queryWrapper=QueryWrapper.create()
    .select()
    .from(ACCOUNT)
    .where(ACCOUNT.ID.ge(100))
    .and(
        exist(  // or notExist(...)
            selectOne().from(ARTICLE).where(ARTICLE.ID.ge(100))
        )
    );

// SQL: 
// SELECT * FROM tb_account 
// WHERE id >=  ?  
// AND EXIST (
//    SELECT 1 FROM tb_article WHERE id >=  ? 
// )

and (…) or (…)

QueryWrapper queryWrapper=QueryWrapper.create()
    .select()
    .from(ACCOUNT)
    .where(ACCOUNT.ID.ge(100))
    .and(ACCOUNT.SEX.eq(1).or(ACCOUNT.SEX.eq(2)))
    .or(ACCOUNT.AGE.in(18,19,20).and(ACCOUNT.USER_NAME.like("michael")));

// SQL: 
// SELECT * FROM tb_account 
// WHERE id >=  ?  
// AND (sex =  ? OR sex =  ? ) 
// OR (age IN (?,?,?) AND user_name LIKE ? )

group by

QueryWrapper queryWrapper=QueryWrapper.create()
    .select()
    .from(ACCOUNT)
    .groupBy(ACCOUNT.USER_NAME);

// SQL: 
// SELECT * FROM tb_account 
// GROUP BY user_name

having

QueryWrapper queryWrapper=QueryWrapper.create()
    .select()
    .from(ACCOUNT)
    .groupBy(ACCOUNT.USER_NAME)
    .having(ACCOUNT.AGE.between(18,25));

// SQL: 
// SELECT * FROM tb_account 
// GROUP BY user_name 
// HAVING age BETWEEN  ? AND ?

orderBy

QueryWrapper queryWrapper=QueryWrapper.create()
    .select()
    .from(ACCOUNT)
    .orderBy(ACCOUNT.AGE.asc()
        , ACCOUNT.USER_NAME.desc().nullsLast());

// SQL: 
// SELECT * FROM tb_account
// ORDER BY age ASC, user_name DESC NULLS LAST

join

QueryWrapper queryWrapper=QueryWrapper.create()
    .select()
    .from(ACCOUNT)
    .leftJoin(ARTICLE).on(ACCOUNT.ID.eq(ARTICLE.ACCOUNT_ID))
    .innerJoin(ARTICLE).on(ACCOUNT.ID.eq(ARTICLE.ACCOUNT_ID))
    .where(ACCOUNT.AGE.ge(10));

// SQL: 
// SELECT * FROM tb_account 
// LEFT JOIN tb_article ON tb_account.id = tb_article.account_id 
// INNER JOIN tb_article ON tb_account.id = tb_article.account_id 
// WHERE tb_account.age >=  ?

limit…offset

QueryWrapper queryWrapper = QueryWrapper.create()
    .select()
    .from(ACCOUNT)
    .orderBy(ACCOUNT.ID.desc())
    .limit(10)
    .offset(20);

// MySql: 
// SELECT * FROM `tb_account` ORDER BY `id` DESC LIMIT 20, 10

// PostgreSQL: 
// SELECT * FROM "tb_account" ORDER BY "id" DESC LIMIT 20 OFFSET 10

// Informix: 
// SELECT SKIP 20 FIRST 10 * FROM "tb_account" ORDER BY "id" DESC

// Oracle: 
// SELECT * FROM (SELECT TEMP_DATAS.*, 
//  ROWNUM RN FROM (
//          SELECT * FROM "tb_account" ORDER BY "id" DESC) 
//      TEMP_DATAS WHERE  ROWNUM <=30) 
//  WHERE RN >20

// Db2: 
// SELECT * FROM "tb_account" ORDER BY "id" DESC 
// OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

// Sybase: 
// SELECT TOP 10 START AT 21 * FROM "tb_account" ORDER BY "id" DESC

// Firebird: 
// SELECT * FROM "tb_account" ORDER BY "id" DESC ROWS 20 TO 30

In the above “limit…offset” example, Mybatis-Flex can automatically identify the current database and generate different SQL. Users can also easily pass
DialectFactory Register (add or override) your own implementation dialect.

Have questions?

Question 1: Can QueryWrapper be transmitted via RPC in a distributed project?

Answer: Yes.

Question 2: How to generate the “ACCOUNT” class required by QueryWrapper through the entity class Account.java?

Answer: Mybatis-Flex uses APT (Annotation Processing Tool) technology. When the project is compiled, it will automatically generate the “ACCOUNT” class and the Mapper class corresponding to the Entity class according to the fields defined by the Entity class. Build the project through the development tool (as follows Figure), or execute the maven compilation command: mvn clean package can be automatically generated. This principle is consistent with lombok.

For more information about Mybatis-Flex APT configuration, please click here.

Db + Row tool class

The Db + Row tool class provides database operation capabilities beyond the Entity entity class. When using Db + Row, there is no need to map the database table. Row is a subclass of HashMap, which is equivalent to a general Entity. Here are some examples of Db + Row:

//使用原生 SQL 插入数据
String sql="insert into tb_account(id,name) value (?, ?)";
Db.insertBySql(sql,1,"michael");

//使用 Row 插入数据
Row account=new Row();
account.set("id",100);
account.set("name","Michael");
Db.insertRow("tb_account",account);


//根据主键查询数据
Row row=Db.selectOneById("tb_account","id",1);

//Row 可以直接转换为 Entity 实体类，且性能极高
Account account=row.toEntity(Account.class);


//查询所有大于 18 岁的用户
String listsql="select * from tb_account where age > ?"
List<Row> rows=Db.selectListBySql(sql,18);


//分页查询：每页 10 条数据，查询第 3 页的年龄大于 18 的用户
QueryWrapper query=QueryWrapper.create()
.where(ACCOUNT.AGE.ge(18));
Page<Row> rowPage=Db.paginate("tb_account",3,10,query);

The Db tool class also provides more methods for adding, deleting, modifying, checking, and paging queries.

Specific reference: Db.java.

More about Row Insertionprimary key generation mechanismand Db’saffairs managementetc., please click here.

Entity partial field update

Compared with other frameworks on the market, this part of the function should also be regarded as one of the highlights of MyBatis-Flex. In BaseMapper, Mybatis-Flex provides the following methods:

update(T entity,boolean ignoreNulls)
  • The first parameter is an object of entity.
  • The second parameter is whether to ignore null values.

In some scenarios, we may want to update only a few fields, and some of them need to be updated to null.Need to use at this time UpdateEntity Tool class, the following is the sample code:

Account account=UpdateEntity.of(Account.class);
account.setId(100);
account.setUserName(null);
account.setSex(1);

accountMapper.update(account,false);

In the above example, the user_name field in the data whose id is 100 will be updated to null, the sex field will be updated to 1, and other fields will not be updated.That is, through UpdateEntity
The created object will only update the field whose setter method is called. If the setter method is not called, no matter what the value of the attribute in this object is, it will not be updated to the database.

The generated sql content is as follows:

update tb_account
set user_name = ?, sex = ? where id = ? 
#params: null,1,100

Custom TypeHandler

Use the @column annotation:

@Table("tb_account")
public class Account {

    @Id(keyType = KeyType.Auto)
    private Long id;

    private String userName;

    @Column(typeHandler = Fastjson2TypeHandler.class)
    private Map<String, Object> options;

    //getter setter
    
    public void addOption(String key, Object value) {
        if (options == null) {
            options = new HashMap<>();
        }
        options.put(key, value);
    }
    
}

Insert data:

Account account = new Account();
account.setUserName("test");
account.addOption("c1", 11);
account.addOption("c2", "zhang");
account.addOption("c3", new Date());

mybatis log:

==>  Preparing: INSERT INTO tb_account (user_name, options) VALUES (?, ?)
==> Parameters: test(String), {"c3":"2023-03-17 09:10:16.546","c1":11,"c2":"zhang"}(String)

multiple primary keys

Mybatis-Flex multi-primary key means that there are multiple @Id Annotation identification only, for example:


@Table("tb_account")
public class Account {

    @Id(keyType = KeyType.Auto)
    private Long id;

    @Id(keyType = KeyType.Generator, value = "uuid")
    private String otherId;

    //getter setter
}

When we save the data, the id primary key of Account is self-incrementing, while the otherId primary key is generated by uuid.

custom primary key generator

Step 1: Write a class that implements IKeyGenerator interface, for example:

public class UUIDKeyGenerator implements IKeyGenerator {

    @Override
    public Object generate(Object entity, String keyColumn) {
        return UUID.randomUUID().toString().replace("-", "");
    }
}

Step 2: Register UUIDKeyGenerator

KeyGeneratorFactory.register("myUUID",new UUIDKeyGenerator());

Step 3: Use the “myUUID” generator in Entity:


@Table("tb_account")
public class Account {

    @Id(keyType = KeyType.Generator, value = "myUUID")
    private String otherId;

    //getter setter
}

Generated using database Sequence


@Table("tb_account")
public class Account {

    @Id(keyType = KeyType.Sequence, value = "select SEQ_USER_ID.nextval as id from dual")
    private Long id;

}

For more information about primary key configuration, please click here

more documents

Still have questions?

Join the QQ exchange group: 532992631

Another open source project from the team

React-Better-Admin is a background UI system developed based on React 18, TypeScript, Ant Design v5.x, and Redux. It is permanently free and commercially available based on the MIT open source protocol. Click here to learn more.

#MybatisFlex #MybatisFlex #elegant #Mybatis #enhancement #framework

Leave a Comment

Your email address will not be published. Required fields are marked *