tk.mybatis - 使用 Example 类进行条件查询

最新推荐文章于 2024-05-19 18:26:58 发布

云原生.乔豆麻袋.cn

最新推荐文章于 2024-05-19 18:26:58 发布

阅读量4.2k

点赞数 2

分类专栏： Mybatis 学习文章标签： mybatis java tk.mybatis Example

本文链接：https://blog.csdn.net/qiaohao0206/article/details/125851684

版权

Mybatis 学习专栏收录该内容

3 篇文章 0 订阅

订阅专栏

通用 Example

一、查询

1、selectOneByExample示例如下：

Example example = new Example(OrderRemark.class);
example.createCriteria().andEqualTo("id", 10);
OrderRemark orderRemark = orderRemarkMapper.selectOneByExample(example);

注意： 当查询条件匹配到多条数据时会抛异常：

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 4

	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:96)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
	at com.sun.proxy.$Proxy157.selectOne(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:160)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:87)
	at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:152)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85)
	at com.sun.proxy.$Proxy238.selectOneByExample(Unknown Source)

2、selectByExample使用示例如下：

Example example = new Example(OrderRemark.class);
example.createCriteria().andEqualTo("orderId", "Dev047");
List<OrderRemark> orderRemarks = orderRemarkMapper.selectByExample(example);

Example example = new Example(OrderRemark.class);
example.createCriteria().andEqualTo("orderId", "Dev047");
example.or().andEqualTo("orderId", "Dev021");
List<OrderRemark> orderRemarks = orderRemarkMapper.selectByExample(example);

3、动态 SQL：

Example example = new Example(OrderRemark.class);
Example.Criteria criteria = example.createCriteria();
if (Objects.nonNull(status)) {
    criteria.andEqualTo("status", status);
}
if (StringUtils.isNotBlank(orderId)) {
    criteria.andEqualTo("orderId", orderId);
}
List<OrderRemark> orderRemarks = orderRemarkMapper.selectByExample(example);

4、设置查询列：

Example example = new Example(OrderRemark.class);
example.selectProperties("id", "remark");
List<OrderRemark> orderRemarks = orderRemarkMapper.selectByExample(example);

二、排序

Example添加查询条件时，还可设置字段降序和日期范围查询：

Example example = new Example(OrderRemark.class);
example.setOrderByClause("create_time desc, remark desc");
example.createCriteria().andEqualTo("orderId", "Dev047").andBetween("createTime", LocalDate.now().minusDays(1), LocalDate.now());
List<OrderRemark> orderRemarks = orderRemarkMapper.selectByExample(example);

当排序单个字段时的使用方法

example.setOrderByClause("create_time ASC");

当排序多个字段时的使用方法，中间用逗号隔开

example.setOrderByClause("create_time desc, remark desc");

或者

example.setOrderByClause("create_time, remark desc");

设置字段升序或降序的另一种方法：

Example example = new Example(OrderRemark.class);
example.orderBy("createTime").desc().orderBy("remark").asc();

三、去重

Example example = new Example(OrderRemark.class);
example.setDistinct(true);

四、Example.builder 方式

Example example = Example.builder(OrderRemark.class)
                .select("id", "remark")
                .where(Sqls.custom().andEqualTo("orderId", "Dev047"))
                .orderByDesc("createTime")
                .build();
List<OrderRemark> orderRemarks = orderRemarkMapper.selectByExample(example);