MyBatis Dynamic Query

The MyBatis Dynamic Query framework makes it easier to generate "where" and "order" expression dynamically in mapper xml. mybatis-dynamic-query comes to solve four problem:

• no need write lots of code in xml.
• filtering or sorting maintained by java code.
• hot update "where" and "order" expression.
• save filter or sort descriptor and re-use them.

Docs

中文文档1.x | 中文文档2.x

Database support

• H2
• MySql
• SqlServer
• Postresql
• Oracle (TODO)

Maven

<dependency>
    <groupId>com.github.wz2cool</groupId>
    <artifactId>mybatis-dynamic-query</artifactId>
    <version>2.0.11</version>
</dependency>

Dynamic Query example

• create two tables by sql.

DELETE FROM category;
INSERT INTO category (category_id, category_name, description) VALUES
  (1, 'Beverages', 'test'),
  (2, 'Condiments', 'test'),
  (3, 'Oil', 'test');

DELETE FROM product;
INSERT INTO product (product_id, category_id, product_name, price) VALUES
  (1, 1, 'Northwind Traders Chai', 18.0000),
  (2, 2, 'Northwind Traders Syrup', 7.5000),
  (3, 2, 'Northwind Traders Cajun Seasoning', 16.5000),
  (4, 3, 'Northwind Traders Olive Oil', 16.5000);

• create a model map to this table.

public class ProductView {
    @Column(name = "product_id", table = "product")
    private Long productId;
    @Column(name = "product_name", table = "product")
    private String productName;
    @Column(name = "price", table = "product")
    private BigDecimal price;

    @Column(name = "category_id", table = "category")
    private Long categoryId;
    @Column(name = "category_name", table = "category")
    private String categoryName;
    @Column(name = "description", table = "category")
    private String description;

    // get, set method.
}

• create a dynamic select in mapper interface / xml.

List<ProductView> getProductViewsByDynamic(Map<String, Object> params);

<select id="getProductViewsByDynamic" parameterType="java.util.Map"
        resultType="com.github.wz2cool.dynamic.mybatis.db.model.entity.view.ProductView">
    SELECT
    <choose>
        <when test="columnsExpression != null and columnsExpression !=''">
            ${columnsExpression}
        </when>
        <otherwise>
            *
        </otherwise>
    </choose>
    FROM product LEFT JOIN category ON product.category_id = category.category_id
    <if test="whereExpression != null and whereExpression != ''">WHERE ${whereExpression}</if>
    <if test="orderByExpression != null and orderByExpression != ''">ORDER BY ${orderByExpression}</if>
</select>

• generate expression and param map (NOTE: expression string also put into map).

@Test
public void testMultiTablesFilter() throws Exception {
    FilterDescriptor priceFilter1 =
            new FilterDescriptor(ProductView.class, ProductView::getPrice,
                    FilterOperator.GREATER_THAN_OR_EQUAL, 6);
    FilterDescriptor priceFilter2 =
            new FilterDescriptor(ProductView.class, ProductView::getPrice,
                    FilterOperator.LESS_THAN, 10);
    FilterDescriptor categoryNameFilter =
            new FilterDescriptor(ProductView.class, ProductView::getCategoryName,
                    FilterOperator.START_WITH, "Co");

    SortDescriptor idDescSort =
            new SortDescriptor(ProductView.class, ProductView::getProductID, SortDirection.DESC);

    Map<String, Object> params =
            // NOTE: we recommend you to set "columnsExpressionPlaceholder"
            // in case of duplicated column name in two tables.
            // 这里你也可以不给列的站位，但是推荐使用，防止两个表有重复的名字
            MybatisQueryProvider
                    .createInstance(ProductView.class, "columnsExpression")
                    .addFilters("whereExpression",
                            priceFilter1, priceFilter2, categoryNameFilter)
                    .addSorts("orderByExpression", idDescSort)
                    .toQueryParam();

    List<ProductView> result = northwindDao.getProductViewsByDynamic(params);
    assertEquals(true, result.size() > 0);
}

output result

==>  Preparing: SELECT product.product_id AS product_id, product.price AS price, category.description AS description, category.category_name AS category_name, product.product_name AS product_name, category.category_id AS category_id 
FROM product LEFT JOIN category ON product.category_id = category.category_id WHERE (product.price >= ? AND product.price < ? AND category.category_name LIKE ?) 
==> Parameters: 6(Integer), 10(Integer), Co%(String)
<==    Columns: PRODUCT_ID, PRICE, DESCRIPTION, CATEGORY_NAME, PRODUCT_NAME, CATEGORY_ID
<==        Row: 2, 7.5000, test, Condiments, Northwind Traders Syrup, 2
<==      Total: 1

Dynamic Query Mapper

DynamicQueryMapper is based on tk.mybatis.mapper.

spring boot configuration

1. add dependency

<!-- base -->
<dependency>
    <groupId>com.github.wz2cool</groupId>
    <artifactId>mybatis-dynamic-query</artifactId>
    <version>2.0.2</version>
</dependency>
<!-- register mapper -->
<dependency>
    <groupId>tk.mybatis</groupId>
    <artifactId>mapper-spring-boot-starter</artifactId>
    <version>1.1.3</version>
</dependency>
<!-- mybatis -->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.4.4</version>
</dependency>
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.0</version>
</dependency>
<!-- spring boot web already has jackson-->
<!--  <dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-databind</artifactId>
    <version>2.9.0</version>
</dependency>-->
<!-- spring boot -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.2.0</version>
</dependency>

1. register DynamicQueryMapper in application.properties file.

mapper.mappers[0]=com.github.wz2cool.dynamic.mybatis.mapper.DynamicQueryMapper

1. scan mappers.

@SpringBootApplication
@MapperScan(basePackages = "com.github.wz2cool.mdqtest.mapper")
@EnableSwagger2
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

create mapper

public interface ProductDao extends DynamicQueryMapper<Product> {
}