Postgres UUID 和 Hibernate → 未找到任何列 [英] Postgres UUID and Hibernate → no column found
问题描述
我在使用 Postgres UUID 类型、java.util.UUID 和 Hibernate 时遇到问题.
I am having troubles with using Postgres UUID type, java.util.UUID and Hibernate.
本地查询一切正常，但是当我尝试使用 HQL 时，它告诉我它找不到 id 列:column huser0_.id 不存在.
Everything works fine with native queries, but when I try to use HQL it tells me it could not find id column: column huser0_.id does not exist.
这是存储库的代码:
import com.xobotun.common.HUser;
import java.util.UUID;
@org.springframework.stereotype.Repository
public interface ReadOnlyUserRepository extends Repository<HUser, UUID> {
// @Query("select u from HUser u where u.id = :id")
@Query(value = "select * from \"user\" where id = :id", nativeQuery = true)
HUser getById(@Param("id") UUID id);
}
这样它就打印出预期的
HUser(id=fbd3c9e2-8fa4-11e9-bc42-526af7764f64, name=test, about=test, isPermabanned=false, created=2019-06-15T19:37:30.503, lastActive=2019-06-15T19:37:33.512)
但是当注释掉原生查询并使用 HQL 时，它突然停止工作:
But when comment out the native query and use HQL one, it suddenly stops working:
org.postgresql.util.PSQLException: ERROR: column huser0_.id does not exist
有人遇到过这样的问题吗?谢谢.
Has anyone encountered such an issue? Thanks.
了解问题并检查我是否有任何拼写错误的更多信息.:)
Some more info to understand the question and to check if I have any typos. :)
表 DDL:
CREATE TABLE "user" (
id UUID NOT NULL PRIMARY KEY,
name TEXT NOT NULL,
is_permabanned BOOLEAN DEFAULT FALSE NOT NULL,
created TIMESTAMP DEFAULT now() NOT NULL,
last_active TIMESTAMP,
about TEXT
);
实体类:
package com.xobotun.common;
import lombok.Data;
import javax.persistence.*;
import java.time.LocalDateTime;
import java.util.UUID;
@Data
@Entity
@Table(name = "user")
public class HUser {
@Id
@Column(name = "id") //, columnDefinition = "uuid", updatable = false)
// @Type(type="pg-uuid")
private UUID id;
@Column(name = "name")
private String name;
@Column(name = "about")
private String about;
@Column(name = "is_permabanned")
private Boolean isPermabanned;
@Column(name = "created")
private LocalDateTime created;
@Column(name = "last_active")
private LocalDateTime lastActive;
}
如您所见，我在 id 字段上尝试了各种选项，但没有一个适用于 HQL 查询.
As you can see, I experimented with various options on id field, but none of them worked with HQL query.
Java 版本是 11，PostgreSQL 也是 11，这里是相关的依赖项:
Java version is 11, PostgreSQL is also 11 and here are related dependencies:
dependencies {
compile 'org.springframework.data:spring-data-jpa:2.1.5.RELEASE'
compile 'javax.persistence:javax.persistence-api:2.2'
compile 'com.vladmihalcea:hibernate-types-52:2.4.4'
implementation 'org.hibernate:hibernate-core:5.4.3.Final'
implementation 'org.postgresql:postgresql:42.2.5.jre7'
}
另外，我尝试了这些问题的解决方案，但都没有帮助:1 2 3 4
Also, I tried solutions in these questions, but they were of no help: 1 2 3 4
UPD1:这是 Hibernate 在查询失败时生成的 SQL:
UPD1: Here's SQL generated by Hibernate on failing query:
select
huser0_.id as id1_0_,
huser0_.about as about2_0_,
huser0_.created as created3_0_,
huser0_.is_permabanned as is_perma4_0_,
huser0_.last_active as last_act5_0_,
huser0_.name as name6_0_
from
user huser0_
where
huser0_.id=?
推荐答案
感谢@JBNizet 的善意评论，我发现问题不在于奇怪的 UUID 行为，而是 Hibernate 默认不会转义标识符.
Thanks to @JBNizet kind comment I found out the problem was not in weird UUID behaviours, but that Hibernate does not escape identifiers by default.
这个问题实际上有三个简单的解决方案:
There are actually three easy solutions to the question:
不要使用保留关键字，将表名更改为其他名称.
Do not use reserved keywords, change table name to something else.
手动转义表名(如 HUser.java 中的 @Table(name = "\"user\"")).
Manually escape table name (like @Table(name = "\"user\"") in HUser.java).
将行 hibernate.globally_quoted_identifiers=true 添加到您的配置中.我想知道为什么默认情况下它不是 true ......参见 this 了解更多详情.
Add line hibernate.globally_quoted_identifiers=true to your config. I wonder why is it not true by default... See this for more details.
这篇关于Postgres UUID 和 Hibernate → 未找到任何列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
