[FIXED] Hibernate issue with unquoted strategy

Issue

Starting from 5.5.3.Final, Hibernate-core changes the unquotedCaseStrategy to UPPER (while the original value is MIXED).

public class IdentifierHelperBuilder {
    private IdentifierCaseStrategy unquotedCaseStrategy = IdentifierCaseStrategy.UPPER;
    private IdentifierCaseStrategy quotedCaseStrategy = IdentifierCaseStrategy.MIXED;

Our project uses following url (with Spring boot) to access the MariaDB. After changing to hibernate-core 5.5.3.Final, our project cannot access the MariaDB correctly. During the investigation, I found that catalog name WebServiceDB will be treated as unquoted and UPPER case WEBSERVICEDB will be used as the catalog (WebServiceDB will be used before hibernate-core 5.5.3.Final) due to the unquotedCaseStrategy change.

spring:
  datasource:
    url: jdbc:mysql://1.1.1.1:3306/WebServiceDB

I want to make the catalog to be quoted and use quotedCaseStrategy, following configurations had been tried and did not work. Could you please help me? Thanks a lot.

url: jdbc:mysql://1.1.1.1:3306/"WebServiceDB"
url: jdbc:mysql://1.1.1.1:3306/[WebServiceDB]

Solution

The workaround is to add both globally_quoted_identifiers and globally_quoted_identifiers_skip_column_definitions:

spring:
  jpa:
    properties:
      hibernate:
        globally_quoted_identifiers: true
        globally_quoted_identifiers_skip_column_definitions: true

And following JIRA is opened for this issue. https://hibernate.atlassian.net/browse/HHH-15075

In my case, I am using the dialect: org.hibernate.dialect.MySQL5InnoDBDialect, and it's legacy configuration. I need to change to org.hibernate.dialect.MariaDBDialect, because our project is using MariaDB 10.4.21. MariaDBDialect would set unquotedCaseStrategy and quotedCaseStrategy to MIXED.

@Override
    public IdentifierHelper buildIdentifierHelper(IdentifierHelperBuilder builder, DatabaseMetaData dbMetaData)
            throws SQLException {

        // some MariaDB drivers does not return case strategy info
        builder.setUnquotedCaseStrategy( IdentifierCaseStrategy.MIXED );
        builder.setQuotedCaseStrategy( IdentifierCaseStrategy.MIXED );

        return super.buildIdentifierHelper( builder, dbMetaData );
    }

Answered By - Steven
Answer Checked By - Marilyn (JavaFixing Volunteer)