更换数据库驱动后应用搜索出现HQL substr方法错误求助

错误信息 #

No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode  \-[METHOD_CALL] MethodNode: '('
    +-[METHOD_NAME] IdentNode: 'substr' {originalText=substr}
    \-[EXPR_LIST] SqlNode: 'exprList'
       +-[DOT] DotNode: 'addresses1_.ADDTXT' {propertyName=address,dereferenceType=PRIMITIVE,getPropertyPath=address,path=ad0.address,tableAlias=addresses1_,className=com.myproject.core.bean.Address,classAlias=ad0}
       |  +-[ALIAS_REF] IdentNode: '(addresses1_.ADLNO, addresses1_.PERSONID)' {alias=ad0, className=com.myproject.core.bean.Address, tableAlias=addresses1_}
       |  \-[IDENT] IdentNode: 'address' {originalText=address}
       +-[NUM_INT] LiteralNode: '1'
       \-[NUM_INT] LiteralNode: '5'

相关代码与配置 #

实体类代码 #

@Entity
@Table(name = "PERSON")
public class Person implements Serializable
{
    @Id
    @Column(name = "PERSONID", columnDefinition = "DECIMAL", precision = 9, scale = 0)
    private int storeID;

    @Column(name = "NAMN", columnDefinition = "CHAR", length=25)
    private String personName;

    @OneToMany(mappedBy="person", fetch=FetchType.LAZY, cascade=CascadeType.ALL, orphanRemoval = true)
    private Set<Address> addresses;

    /**getters and setters**/
}


@Entity
@Table(name="PERSONADDRESS")
public class Address implements Serializable
{
    @EmbeddedId
    private AddressKey addressKey;

    @Column(name="ADDTXT", columnDefinition="CHAR", length=25)
    private String address;

    @ManyToOne(fetch=FetchType.LAZY)
    @JoinColumn(name="ADDTYPE")
    private AddressType addressType;

    @ManyToOne(fetch=FetchType.LAZY)
    @JoinColumn(name="PERSONID", referencedColumnName="PERSONID", insertable=false, updatable=false)
    private Person person;

    /**getters and setters**/
}


@Embeddable
public class AddressKey implements Serializable
{
    @Column(name="PERSONID", columnDefinition="DECIMAL", precision=9, scale=0, nullable=false)
    private int personID;

    @Column(name="ADLNO", columnDefinition="DECIMAL", precision=5, scale=0, nullable=false)
    private int serialNumber;

    /**getters and setters**/
}

POM依赖 #

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-envers</artifactId>
    <version>4.3.11.Final</version>
</dependency>
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>4.3.11.Final</version>
</dependency>
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.0.33</version>
</dependency>

触发错误的HQL查询 #

select p 
from com.myclass.bean.core.Person p
where ('LONDON' in (
select substr(ad0.address, 1, 6) 
from p.addresses ad0 
where ad0.addressType = 'CITY'))
order by p.personID asc;

数据库配置 #

db.driverClass=com.mysql.cj.jdbc.Driver
db.jdbc.hibernate.default.schema=dbo
hibernate.dialect=org.hibernate.dialect.MySQLDialect
org.hibernate.envers.default_schema=dbo

错误含义 #

该错误是因为Hibernate在解析HQL中的substr函数时，无法将其映射到MySQL对应的数据库函数类型。MSSQL环境下substr是SUBSTRING函数的别名，Hibernate的MSSQL方言能正常识别，但切换到MySQL后，当前使用的方言不支持substr作为标准HQL函数的别名，导致函数节点无法解析出对应的数据类型，进而抛出异常。

修复方案 #

1. 替换HQL中的substr为标准HQL函数substring #

HQL的标准字符串截取函数是substring，将查询中的substr(ad0.address, 1, 6)修改为substring(ad0.address, 1, 6)，修正后的HQL如下：

select p 
from com.myclass.bean.core.Person p
where ('LONDON' in (
select substring(ad0.address, 1, 6) 
from p.addresses ad0 
where ad0.addressType.code = 'CITY'))
order by p.personID asc;

注：原HQL中ad0.addressType = 'CITY'存在逻辑问题，addressType是AddressType实体对象，无法直接与字符串比较，需改为比较其对应的属性（比如addressType表中的编码字段，如ad0.addressType.code），否则后续仍会出现类型不匹配错误。

2. 更新Hibernate方言适配MySQL 8.0 #

当前使用的org.hibernate.dialect.MySQLDialect是针对MySQL 5.x版本的，与MySQL 8.0的语法、函数兼容性较差，将配置中的方言改为：

hibernate.dialect=org.hibernate.dialect.MySQL8Dialect

3. 验证函数参数兼容性 #

MySQL的SUBSTRING函数参数格式为SUBSTRING(str, pos, len)，与HQL的substring函数参数顺序完全一致，因此替换后无需修改参数位置，原逻辑（截取从第1位开始的6个字符）保持不变。

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