使用jpa和QueryDSL按‘日期’进行‘模糊查询’的两种方式

约定不等于承诺〃 2022-04-27 17:34 1313阅读 0赞

> 使用QueryDSL按日期查询，如果是一般的按日期完全匹配查询，直接使用jpa的.eq()方法进行查询即可。  
> 不过有时候在实际的业务需求中，可能需要按照日期的年，或者月，或者天查询，但是数据库里存入的是一个完整的日期，此时我们便用到了日期的模糊查询，日期的模糊查询的原理是将日期转换为相同格式的字符串数据，然后使用jpa的.like()方法进行查询，这里我总结了两种按日期模糊查询的方式。

**框架：springboot  
数据库：mysql**

**第一种：使用字符串模板StringTemplate**  
此处的例子是按照**月份**查询

public List<User> findAll(Date month){
    			QUser qUser = QUser.user;
    			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM");
    			StringTemplate dateExpr = Expressions.
    			           stringTemplate("DATE_FORMAT({0},'%Y-%m')",qUser.birthday);    //此处是按照mysql的语法格式转换
    			List<User> list = jpaQueryFactory.select(Projections.bean(User.class,
    		             dateExpr.as("birthday")
    		               )).from(qUser).where(dateExpr.like(dateFormat.format(month))+"%")  //此处使用like和eq都可以，使用SimpleDateFormat 转换是避免数据库日期格式和此处格式不一致
    		               .groupBy(dateExpr).fetch();
    		    return list;
    	}

**第二种：使用ComparableExpressionBase**  
此处的例子是按照年查询

public List<User> findAll(Date month){
    			QUser qUser = QUser.user;
    			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy");
    			ComparableExpressionBase<?> year = qUser.birthday.year();  //此处是获取年份
    			List<User> list = jpaQueryFactory.select(Projections.bean(User.class,
    		             year.as("birthday")
    		               )).from(qUser).where(((NumberExpression) year).stringValue().eq(dateFormat.format(month))) //此处使用like和eq都可以，使用SimpleDateFormat 转换是避免数据库日期格式和此处格式不一致
    		               .groupBy(year).fetch();
    		    return list;
    	}

附：mysql数据库日期格式表

%Y：年，4 位  
%y：年，2 位  
%M：月名  
%m：月，数值（00-12）  
%D：带有英文前缀的月中的天  
%d：月的天，数值（00-31）  
%e：月的天，数值（0-31）

%H：小时（00-23）  
%h：小时（01-12）  
%I：小时（01-12）  
%i：分钟，数值(00-59)  
%S：秒(00-59)  
%s：秒(00-59)

%W：星期名  
%a：缩写星期名  
%b：缩写月名  
%c：月，数值  
%f：微秒  
%j：年的天 (001-366)  
%k：小时 (0-23)  
%l：小时 (1-12)

%p：AM 或 PM  
%r：时间，12-小时（hh:mm:ss AM 或 PM）  
%T：时间, 24-小时 (hh:mm:ss)

%U：周 (00-53) 星期日是一周的第一天  
%u：周 (00-53) 星期一是一周的第一天  
%V：周 (01-53) 星期日是一周的第一天，与 %X 使用  
%v：周 (01-53) 星期一是一周的第一天，与 %x 使用  
%w：周的天 （0=星期日, 6=星期六）

%X：年，其中的星期日是周的第一天，4 位，与 %V 使用  
%x：年，其中的星期一是周的第一天，4 位，与 %v 使用