How to specifically ignore the hibernate quoted identifier attribute on a column in spring?

Question

I have tables in postgres that all of them and their columns named with the doublequotes "". To make it easier, then I set the globally_quoted_identifier to true:

spring.jpa.properties.hibernate.globally_quoted_identifiers=true

However, I have a table that doesn't have a primary key that can have multiple rows with absolutely same data (In this case, this is necessary), thus I need to set the @Id column to the CTID column of the table (CTID is default row id in postgres). This resulted error as the CTID is supposedly not enclosed by the quotes.

I know that I can set the property above to false and put doublequotes with escapes on the POJOs. However, I currently have 6 POJOs and each having around 5 columns.

Then, is it more feasible/easier way to ignore the doublequote on the CTID column so it can run properly?

Answer 1

Hibernate has only 3 quoting options:

• hibernate.globally_quoted_identifiers
• hibernate.globally_quoted_identifiers_skip_column_definitions
• hibernate.auto_quote_keyword

You can skip quoting only for column definitions out of the box that's all.
Also it is hard and tedious to define quoting for each column manually.

Solution:
But fortunately, you can define a custom naming strategy.
You can just extend standard CamelCaseToUnderscoresNamingStrategy.
There you can skip quoting for specific column names, just create Identifier without quoting getIdentifier(name.getText(), false, jdbcEnvironment) for CTID column.

package com.project.naming;

import org.hibernate.boot.model.naming.CamelCaseToUnderscoresNamingStrategy;
import org.hibernate.boot.model.naming.Identifier;
import org.hibernate.engine.jdbc.env.spi.JdbcEnvironment;

public class CustomNamingStrategy extends CamelCaseToUnderscoresNamingStrategy {
    @Override
    public Identifier toPhysicalColumnName(Identifier name, JdbcEnvironment jdbcEnvironment) {
        if ("CTID".equalsIgnoreCase(name.getText())) {
            name = getIdentifier(name.getText(), false, jdbcEnvironment);
        }
        return super.toPhysicalColumnName(name, jdbcEnvironment);
    }
}

Define your custom naming strategy in server properties:

spring.jpa.properties.hibernate.globally_quoted_identifiers=true
spring.jpa.properties.hibernate.physical_naming_strategy=com.project.naming.CustomNamingStrategy

Example of DDL generation:

    create table "example" (
         ctid bigint generated by default as identity,
        "name" varchar(15),
        "owner_id" bigint not null)

Example of generated query:

select 
       example0_.ctid as ctid1_1_, 
       example0_."name" as name2_1_, 
       example0_."owner_id" as owner_id3_1_ 
from 
       "example" example0_ 
where 
       example0_.ctid1_1_ = ?