如何编写可同时在Oracle和MySQL生效的排除NULL空字符串的JPQL查询

问题根因 #

• Oracle数据库默认会将空字符串''识别为NULL存储，SQL中所有对NULL使用=/!=的判断结果均为未知，原有语句中的transaction.message != ''在Oracle中等价于transaction.message != NULL，永远不成立，因此查询不生效。
• MySQL原生区分NULL和空字符串''，所以原有判断逻辑在MySQL中可以正常运行。

兼容方案 #

使用JPQL标准函数COALESCE统一处理两种数据库的空值逻辑，COALESCE函数会返回参数列表中第一个非NULL的值，我们可以将NULL和空字符串统一转换为空串后统一判断，修改后的代码如下：

public List<Transaction> getAllTransactions() {
    return getEntityManager()
            .createQuery("from Transaction as transaction where COALESCE(transaction.message, '') != ''", Transaction.class)
            .getResultList();
}

方案说明 #

• MySQL场景下：如果message为NULL，COALESCE会返回''，判断!= ''不成立被过滤；如果message为空字符串，判断同样不成立被过滤，仅返回有实际内容的记录。
• Oracle场景下：空字符串本身会被识别为NULL，COALESCE统一转为''后过滤，和MySQL逻辑完全一致。
• 该函数属于JPQL标准规范，所有JPA实现均支持，不需要额外配置数据库方言。

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