0

I am having troubles with using Postgres UUID type, java.util.UUID and Hibernate.

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.

Here's the code for the repository:

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);

}

This way it prints the expected 

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)

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. :)

Table 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
);

Entity class:

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;
}

As you can see, I experimented with various options on id field, but none of them worked with HQL query.

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'
}

Also, I tried solutions in these questions, but they were of no help: 1 2 3 4

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=?
Xobotun
  • 1,121
  • 1
  • 18
  • 29
  • 1
    user is a Postgresql keyword. Choose another name for your table, which doesn't need quoting. – JB Nizet Jun 15 '19 at 20:15
  • @JBNizet, it is rather embarassing, but yes, changing table name to `t_user` did actually resolved the problem. I was aware that `user` was a reserved word, but I have seen a project that had it without any problems... Weird. Anyways, thanks. – Xobotun Jun 15 '19 at 20:35

1 Answers1

1

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:

  1. Do not use reserved keywords, change table name to something else.

  2. Manually escape table name (like @Table(name = "\"user\"") in HUser.java).

  3. Add line hibernate.globally_quoted_identifiers=true to your config. I wonder why is it not true by default... See this for more details.

Xobotun
  • 1,121
  • 1
  • 18
  • 29
  • 1
    Tip: The SQL standard explicitly promises to never use a **trailing underscore** in any keyword. So naming your identifiers (table name, column name, index name, etc.) with a trailing underscore ensures you will never have a conflict. Ex: `user_`, `id_`, `name_`. – Basil Bourque Jun 15 '19 at 20:47
  • 1
    FYI: Be aware that while Postgres has built-in support for *storing* a UUID, *generating* a UUID requires that you install a plug-in (known as "extension" in SQL-speak). See [*Generating a UUID in Postgres for Insert statement?*](https://stackoverflow.com/q/12505158/642706). – Basil Bourque Jun 15 '19 at 20:51
  • @BasilBourque, thank you. I have never known about that SQL feature_. Now I do. :) And yes, I use `gen_random_uuid()` from `pgcrypto`. It just went that way historically on the project I work, so I did it this way too... – Xobotun Jun 16 '19 at 15:39