使用QUARTER函数的JPA查询报错问题求助

在Eclipse的Maven项目中使用EntityManager映射数据库实体，需要筛选MySQL表中Date类型字段属于指定季度的记录。直接在数据库执行含QUARTER函数的SQL可以正常运行，但通过JPA执行时提示语法错误，报错信息显示右表达式无效，怀疑JPA不支持QUARTER这类MySQL原生函数。

相关SQL（直接在数据库可正常执行） #

SELECT 
COUNT(*) 
FROM mle.Draw d 
WHERE (d.deleted IS NULL OR d.deleted != 1) 
AND d.lotteryName = 'ITA_LOTTO'
AND d.wheel = 'BARI' 
AND QUARTER(d.drawDate) = 3;

JPA执行时报错信息 #

Exception Description: Syntax error parsing [SELECT d FROM Draw d WHERE (d.deleted IS NULL OR d.deleted !=1) AND (d.lotteryName = :lotteryName) AND (d.wheel = :wheel) AND (QUARTER(d.drawDate) = :px)].
[127, 154] The right expression is not a valid expression. and encryptedText={}. Error: {}
java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager:
Exception Description: Syntax error parsing [SELECT d FROM Draw d WHERE (d.deleted IS NULL OR d.deleted !=1) AND (d.lotteryName = :lotteryName) AND (d.wheel = :wheel) AND (QUARTER(d.drawDate) = :px)].
[127, 154] The right expression is not a valid expression.

方法1：使用原生SQL查询 #

直接用JPA的原生SQL查询功能，绕过JPQL的语法检查，直接调用MySQL的QUARTER函数。示例代码：

String sql = "SELECT COUNT(*) FROM mle.Draw d WHERE (d.deleted IS NULL OR d.deleted != 1) AND d.lotteryName = ?1 AND d.wheel = ?2 AND QUARTER(d.drawDate) = ?3";
Query query = entityManager.createNativeQuery(sql);
query.setParameter(1, "ITA_LOTTO");
query.setParameter(2, "BARI");
query.setParameter(3, 3);
Long count = (Long) query.getSingleResult();

方法2：注册自定义函数（针对Hibernate实现） #

如果使用Hibernate作为JPA实现，可在配置中注册QUARTER函数，让JPQL识别它：

1. 自定义方言类，注册函数：

public class CustomMySQLDialect extends MySQL8Dialect {
    public CustomMySQLDialect() {
        super();
        registerFunction("quarter", new SQLFunction() {
            @Override
            public boolean hasArguments() { return true; }
            @Override
            public boolean hasParenthesesIfNoArguments() { return true; }
            @Override
            public Type getReturnType(Type firstArgumentType, Mapping mapping) {
                return IntegerType.INSTANCE;
            }
            @Override
            public String render(Type firstArgumentType, List args, SessionFactoryImplementor factory) {
                return "QUARTER(" + args.get(0) + ")";
            }
        });
    }
}

2. 在persistence.xml中指定自定义方言：

<property name="hibernate.dialect" value="com.yourpackage.CustomMySQLDialect"/>

完成后即可在JPQL中正常使用QUARTER函数。

方法3：用JPA标准日期函数计算季度 #

不依赖MySQL原生函数，通过JPA支持的FUNCTION('month', ...)函数手动计算季度，避免语法错误。JPQL示例：

SELECT COUNT(d) 
FROM Draw d 
WHERE (d.deleted IS NULL OR d.deleted != 1) 
AND d.lotteryName = :lotteryName
AND d.wheel = :wheel 
AND FLOOR((FUNCTION('month', d.drawDate) - 1) / 3) + 1 = :quarter

该表达式会被JPA转换为对应SQL执行，无需依赖数据库特定函数。

内容的提问来源于stack exchange，提问作者JJ Lazzard