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.
- H2
- MySql
- SqlServer
- Postresql
- Oracle (TODO)
<dependency>
<groupId>com.github.wz2cool</groupId>
<artifactId>mybatis-dynamic-query</artifactId>
<version>2.0.11</version>
</dependency>- 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: 1DynamicQueryMapper is based on tk.mybatis.mapper.
- 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>- register DynamicQueryMapper in
application.propertiesfile.
mapper.mappers[0]=com.github.wz2cool.dynamic.mybatis.mapper.DynamicQueryMapper- 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);
}
}public interface ProductDao extends DynamicQueryMapper<Product> {
}
![dependabot[bot] avatar](https://avatars.githubusercontent.com/in/29110?v=4 "dependabot[bot]")
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent