Wrap column names in double quotes in Hibernate

Question

I'm using Spring JPA 2.0.9 with Hibernate 5.3.5 and this dialect to access a FileMaker (v16) database via JDBC using the official JDBC driver taken from this distribution.

The thing is that the resulting SQL ends up being with column names prefixed by the respective table names like:

select 
marketingc0_.a__IDPK_MarketingCategory as a__IDPK_1_0_0_, marketingc0_.Active as Active2_0_0_
from MarketingCategories as marketingc0_
where marketingc0_.a__IDPK_MarketingCategory=1

which FileMaker doesn't accect complaining about the wrong syntax:

[08007][27034] [FileMaker][FileMaker JDBC] FQL0001/(1:153): There is an error in the syntax of the query.

However it gulps down the SQL without complains if the column names are wrapped in double quotes like this:

select
    marketingc0_."a__IDPK_MarketingCategory" as a__IDPK_1_0_0_, marketingc0_."Active" as Active2_0_0_
    from MarketingCategories as marketingc0_
    where marketingc0_.a__IDPK_MarketingCategory=1

The solution I've come up with is including these quotes into the entity annotations:

public class MarketingCategory {
    @Id
    @Column(name = "\"a__IDPK_MarketingCategory\"")
    private Integer id;

    @Column(name = "\"a_ID_User\"")
    private Integer userId;

    @Column(name = "\"Active\"")
    private Boolean active;

...
}

which doesn't look good.

Is it possible configure Hibernate so that it would wrap all the column names in double quotes automatically?

Shenyang

Answer 1

You can set a property named hibernate.globally_quoted_identifiers to true in your definitions and it will quote all identifiers. There is an option in addition to that to not quote the columns, but there is no option to only quote the columns.

Highams

Hint: for Spring Boot it's spring.jpa.properties.hibernate.globally_quoted_identifiers – Stationmaster

Answer 2

You can use hibernate.globally_quoted_identifiers or hibernate.keyword_auto_quoting_enabled might help. keyword_auto_quoting_enabled add automatically quotes for the reserved keywords.

but in my case, I was using column definitions. So hibernates was trying to add quotes to datatypes. For instance "BOOLEAN" or "UUID" in postgresql. That's why I modified the physical naming strategy.

hibernate.naming.physical-strategy = com.mypackage.MyCustomPhysicalNamingStrategyImpl

here is the sample naming strategy class.

public class MyCustomPhysicalNamingStrategyImpl implements PhysicalNamingStrategy, Serializable {

    public static final MyCustomPhysicalNamingStrategyImpl INSTANCE = new MyCustomPhysicalNamingStrategyImpl();

    @Override
    public Identifier toPhysicalCatalogName(final Identifier name, final JdbcEnvironment context) {

        return new Identifier(name.getText(), true);
    }

    @Override
    public Identifier toPhysicalSchemaName(final Identifier name, final JdbcEnvironment context) {

        return new Identifier(name.getText(), true);
    }

    @Override
    public Identifier toPhysicalTableName(final Identifier name, final JdbcEnvironment context) {

        return new Identifier(name.getText(), true);
    }

    @Override
    public Identifier toPhysicalSequenceName(final Identifier name, final JdbcEnvironment context) {

        return new Identifier(name.getText(), true);
    }

    @Override
    public Identifier toPhysicalColumnName(final Identifier name, final JdbcEnvironment context) {

        return new Identifier(name.getText(), true);
    }
}

Hibernate won't modify column definitions but leave the table and column names as is.

Sample Entity:

@Data
@Entity
@Table(name = "CHAT")
public class Chat {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID", nullable = false)
    private long id;

    @Column(name = "UUID", columnDefinition = "UUID", nullable = false, length = 16)
    private UUID uuid;

    @Column(name = "NAME", length = 16)
    private String name;
}

Naming will be as is:

CHAT
------------
ID
UUID
NAME

Update: Hibernate 5.2 has GLOBALLY_QUOTED_IDENTIFIERS_SKIP_COLUMN_DEFINITIONS property which is exactly what I want.

Testimony