Nested query

With Fluent Mybatis, you can use complex nested queries in Java API constructs in dao classes without having to write a single line of XML files or Mapper files. Merge the CODE logic of the DAO with the SQL logic.

Pre-setup, Maven project setup

Use FluentMybatis to implement MyBatis dynamic SQL assembly and Fluent API syntax

In (select subquery)

  • The nested query table is the same scenario as the main query table

.column().in(query-> {query condition})

All you need to do is refer to a lambda expression inside of in, and the lambda expression takes a Query of the same name. You can set the WHERE parameter for this input parameter.

@displayName (" Nested query has the same table as the main query ")
@Test
void test_in_same_table_query(a) {
    UserQuery query = new UserQuery()
        .where.id().in(q -> q.selectId()
            .where.id().eq(3L).end())
        .and.userName().like("user")
        .and.age().gt(23).end();

    List list = mapper.listEntity(query);
    
    // Verify SQL statements using the Test4J tool
    db.sqlList().wantFirstSql()
        .eq("SELECT id, gmt_create, gmt_modified, is_deleted, account, age, avatar, birthday, bonus_points, e_mail, password, phone, status, user_name " +
            "FROM user WHERE id IN (SELECT id FROM user WHERE id = ?) " +
            "AND user_name LIKE ? " +
            "AND age > ?");
}
Copy the code
  • Nested query tables are a scenario for additional tables

.column().in(queryClass, query-> {query-> query)

The class type of the Query object is explicitly declared in the in method if the table is not the same as the same table.

@displayName (" The tables of nested queries are different from those of the main query ")
@Test
void test_in_difference_table_query(a) {
    UserQuery query = new UserQuery()
        .selectId()
        .where.addressId().in(ReceivingAddressQuery.class, q -> q.selectId()
            .where.id().in(new int[] {1.2}).end())
        .end();
    mapper.listEntity(query);
    
    // Verify SQL statements using the Test4J tool
    db.sqlList().wantFirstSql()
        .eq("SELECT id " +
            "FROM user " +
            "WHERE address_id IN (SELECT id FROM receiving_address WHERE id IN (? ,?) )");
}
Copy the code
  • Not in Nested query: Use the same method as in nested query

Exists (select subquery)

  • The nested query table is the same scenario as the main query table

An Exists query does not need to specify a field. An Exists method can be referenced directly in a query WHERE.

  1. Exists (query-> {set condition on query})

If exists queries the same table as the main query, simply use the same type of query parameter in the lambada expression, which is the same as the in method.

  1. Exists (queryClass, query-> {set condition on query})

If the exists table is inconsistent with the main query, specify the type query in the first parameter of the EXISTS method and the type query in the second parameter as in method 1.

Concrete example

@ DisplayName (EXISTS "query")
@Test
void test_exists_query(a) {
    UserQuery query = new UserQuery()
        .where.exists(ReceivingAddressQuery.class, q -> q
            .where.detailAddress().like("Hangzhou")
            .and.id().apply(" = user.address_id").end())
        .end();
    mapper.listEntity(query);
    
    // Verify SQL statements using the Test4J tool
    db.sqlList().wantFirstSql()
        .eq("SELECT id, gmt_create, gmt_modified, is_deleted, account, address_id, age, avatar, birthday, bonus_points, e_mail, password, phone, status, user_name " +
                "FROM user " +
                "WHERE EXISTS (SELECT *" +
                " FROM receiving_address" +
                " WHERE detail_address LIKE ?" +
                " AND id = user.address_id)",
            StringMode.SameAsSpace);
}
Copy the code

Note: The tests in this example use an H2 in-memory database, which you can run directly without requiring you to build additional tables. But using Test4J executing the test, you need to join the vm parameters: – javaagent: / here’s your local maven warehouse address/org/jmockit/jmockit / 1.48 / jmockit – 1.48. Jar in my native, for example, specific parameters as shown in figure:

See Gitee Address for a complete code example

FluentMybatis source address and documentation

Fluent Mybatis source code, Gitee

Fluent Mybatis documentation & examples

Fluent Mybatis source, Github