By wz2cool

更新日期:

文章目录
  1. 1. 前言
  2. 2. 更新内容
    1. 2.1. selectFirstByDynamicQuery
    2. 2.2. selectMaxByDynamicQuery
    3. 2.3. selectMinByDynamicQuery
  3. 3. 小结

项目地址： mybatis-dynamic-query

前言

更新版本到 3.1.7，这次主要是一个小版本的更新，加了三个比较有用的方法，selectFirstByDynamicQuery, selectMaxByDynamicQuery, selectMinByDynamicQuery

更新内容

selectFirstByDynamicQuery

选择第一个, 这个和 selectOne 要区别开来，selectOne 是有且只有个一个，多了要报错，selectFirst 是你有多个也没关系，但是我只取第一个

1
2
3
4
5
6
7
8
9
10
11
12
@Test
public void testSelectFirst() {
    DynamicQuery<ProductsDO> query = DynamicQuery.createQuery(ProductsDO.class)
            .and(ProductsDO::getId, greaterThan(2))
            .orderBy(ProductsDO::getId, asc());
    // 因为过滤掉了2 ， 再按照id 升序，那么第二个id值一定是3
    Optional<ProductsDO> productsDOOptional = productMapper.selectFirstByDynamicQuery(query);
    Assert.assertTrue(productsDOOptional.isPresent());
    Integer expectedValue = 3;
    Integer actualValue = productsDOOptional.get().getId();
    Assert.assertEquals(expectedValue, actualValue);
}

selectMaxByDynamicQuery

选择最大值，这个就比较好理解了，就是调用 max 函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@Test
public void testSelectMax() {
    // 查看最大值的比较可以用，我们倒叙获得的值和最大值进行比较
    // 直接用max 函数获取价格最大值
    DynamicQuery<ProductsDO> selectMaxQuery = DynamicQuery.createQuery(ProductsDO.class)
            .and(ProductsDO::getId, greaterThan(10));
    Optional<BigDecimal> maxPriceOptional =
            productMapper.selectMaxByDynamicQuery(ProductsDO::getListPrice, selectMaxQuery);
    Assert.assertTrue(maxPriceOptional.isPresent());
    BigDecimal actualMaxPrice = maxPriceOptional.get();
    // 间接倒叙获得价格最大值
    DynamicQuery<ProductsDO> selectFirstQuery = DynamicQuery.createQuery(ProductsDO.class)
            .select(ProductsDO::getListPrice)
            .and(ProductsDO::getId, greaterThan(10))
            .orderBy(ProductsDO::getListPrice, desc());
    Optional<ProductsDO> productsDOOptional = productMapper.selectFirstByDynamicQuery(selectFirstQuery);
    Assert.assertTrue(productsDOOptional.isPresent());
    BigDecimal expectedMaxPrice = productsDOOptional.get().getListPrice();
    Assert.assertEquals(expectedMaxPrice, actualMaxPrice);
}

我们也可以看一下日志输出

1
2
3
4
5
6
7
8
9
10
--  12  2019-11-20 22:51:57.634 DEBUG 11812 --- [           main] c.g.w.m.m.P.selectMaxByDynamicQuery      : ==>
SELECT MAX(list_price)
        FROM products
WHERE (id > 10);
------------------------------------------------------------------------------------------------------------------------
--  13  2019-11-20 22:51:58.508 DEBUG 11812 --- [           main] .g.w.m.m.P.selectRowBoundsByDynamicQuery : ==>
SELECT list_price AS list_price
FROM products
WHERE (id > 10) ORDER BY list_price DESC
LIMIT 1;

selectMinByDynamicQuery

选择最小值，就是调用 min 函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
@Test
public void testSelectMin() {
    // 直接用min 函数获取价格最小值
    DynamicQuery<ProductsDO> selectMinQuery = DynamicQuery.createQuery(ProductsDO.class)
            .and(ProductsDO::getId, greaterThan(10));
    Optional<BigDecimal> minPriceOptional =
            productMapper.selectMinByDynamicQuery(ProductsDO::getListPrice, selectMinQuery);
    Assert.assertTrue(minPriceOptional.isPresent());
    BigDecimal actualMinPrice = minPriceOptional.get();
    // 间接正序获取价格最小值
    DynamicQuery<ProductsDO> selectFirstQuery = DynamicQuery.createQuery(ProductsDO.class)
            .select(ProductsDO::getListPrice)
            .and(ProductsDO::getId, greaterThan(10))
            .orderBy(ProductsDO::getListPrice, asc());
    Optional<ProductsDO> productsDOOptional = productMapper.selectFirstByDynamicQuery(selectFirstQuery);
    Assert.assertTrue(productsDOOptional.isPresent());
    BigDecimal expectedMinPrice = productsDOOptional.get().getListPrice();
    Assert.assertEquals(expectedMinPrice, actualMinPrice);
}

我们也可以看一下日志输出

1
2
3
4
5
6
7
8
9
10
--  26  2019-11-20 23:25:32.043 DEBUG 14740 --- [           main] c.g.w.m.m.P.selectMinByDynamicQuery      : ==>
SELECT MIN(list_price)
 FROM products
 WHERE (id > 10);
------------------------------------------------------------------------------------------------------------------------
--  27  2019-11-20 23:25:32.749 DEBUG 14740 --- [           main] .g.w.m.m.P.selectRowBoundsByDynamicQuery : ==>
SELECT list_price AS list_price
 FROM products
 WHERE (id > 10) ORDER BY list_price ASC
 LIMIT 1;

小结

忙了 3 个月了，终于有空更新一下… 后面已经在构思动态查询查 elasticsearch。

文章目录
  1. 1. 前言
  2. 2. 更新内容
    1. 2.1. selectFirstByDynamicQuery
    2. 2.2. selectMaxByDynamicQuery
    3. 2.3. selectMinByDynamicQuery
  3. 3. 小结