Spring Boot中HQL ORDER BY使用DATE_ADD日期运算报错的解决求助

问题 #

使用Spring Boot框架，需要在MySQL数据库的HQL查询ORDER BY子句中，将SectionInfo的auditDate字段加上关联SectionAudit的auditDays天数后排序，执行原代码出现SQL语法错误。

原查询代码：

List<SectionInfo> dataList = new ArrayList<>();
Query query = session.createQuery("FROM SectionInfo where active = true and sectionId.id = :sectionId ORDER BY DATE_ADD(auditDate, auditId.auditDays)");
query.setParameter("sectionId", sectionId);
query.setFirstResult(page == 1 ? 0 : calculateOffset(page, maxCount));
query.setMaxResults(maxCount);
dataList = query.list();

报错信息：

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sectionaud1_.audit_days) limit 20' at line 1
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

相关实体类：

SectionInfo #

public class SectionInfo implements Serializable {

     @Id
     @GeneratedValue(strategy = GenerationType.IDENTITY)
     @Column(name = "id")
     private long id;

     @JoinColumn(name = "section_id", referencedColumnName = "id", nullable = false)
     @OneToOne(optional = false)
     private Section sectionId;

     @JoinColumn(name = "audit_id", referencedColumnName = "id", nullable = false)
     @OneToOne(optional = false)
     private SectionAudit auditId;

     @Column(name="comment")
     private String comment;

     @Column(name = "audit_date")
     @JsonAdapter(DateJsonSerializer.class)
     private Date auditDate;

     public SectionInfo() {
     }
 }

SectionAudit #

public class SectionAudit implements Serializable {

     @Id
     @GeneratedValue(strategy = GenerationType.IDENTITY)
     @Column(name = "id")
     private long id;

     @Column(name="name")
     private String name;

     @Column(name="audit_days")
     private int auditDays;
}

解决方法 #

方法1：使用HQL兼容的日期函数（推荐） #

HQL不直接支持MySQL原生的DATE_ADD参数格式，需使用HQL标准的DATEADD函数并明确指定时间单位，Hibernate会自动转换为MySQL对应的语法：

Query query = session.createQuery("FROM SectionInfo where active = true and sectionId.id = :sectionId ORDER BY DATEADD(auditDate, auditId.auditDays, 'DAY')");

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

如果HQL函数转换存在问题，可直接使用原生SQL，完全遵循MySQL语法规则：

String sql = "SELECT si.* FROM section_info si " +
             "JOIN section_audit sa ON si.audit_id = sa.id " +
             "WHERE si.active = true AND si.section_id = :sectionId " +
             "ORDER BY DATE_ADD(si.audit_date, INTERVAL sa.audit_days DAY)";
Query query = session.createNativeQuery(sql, SectionInfo.class);
query.setParameter("sectionId", sectionId);
query.setFirstResult(page == 1 ? 0 : calculateOffset(page, maxCount));
query.setMaxResults(maxCount);
dataList = query.list();

方法3：在实体类中预计算排序字段 #

通过@Formula注解在实体类中定义实时计算的字段，查询时直接对该字段排序：

public class SectionInfo implements Serializable {
    // 原有字段保持不变

    @Formula("DATE_ADD(audit_date, INTERVAL (SELECT audit_days FROM section_audit WHERE id = audit_id) DAY)")
    private Date calculatedAuditDate;

    // 添加getter方法
    public Date getCalculatedAuditDate() {
        return calculatedAuditDate;
    }
}

修改后的查询代码：

Query query = session.createQuery("FROM SectionInfo where active = true and sectionId.id = :sectionId ORDER BY calculatedAuditDate");

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