Set table name in Spring JPA

Question

I think I'm trying to do something really simple. Using Spring Boot (1.3.3.RELEASE) with JPA I want to set a table name.

@Entity
@Table(name = "MyTable_name")
public class MyTableData {
  ...
}

What I expect in my database is a table with "MyTable_name". Seems completely reasonable to me. But that doesn't happen. I get a table with name "MY_TABLE_NAME" (H2 backend) or "my_table_name" (Postgre backend). From here on I'll stick with Postgre since my goal is to read an existing DB where I don't control the table names.

After some research I find posts that say I should use the spring.jpa.hibernate.naming-strategy property. This doesn't help much. Setting to the most commonly recommended org.hibernate.cfg.ImprovedNamingStrategy produces the same behavior: "my_table_name". Setting to org.hibernate.cfg.EJB3NamingStrategy produces "mytable_name". Setting to org.hibernate.cfg.DefaultNamingStrategy causes application context errors in Spring's innards.

Resigned to writing my own, I started looking at org.hibernate.cfg.ImprovedNamingStrategy. I discovered it used the deprecated org.hibernate.cfg.NamingStrategy. That suggests using NamingStrategyDelegator instead. I looked at its Java docs but not sure how to apply. I found this post. As much as I appreciate the explanation, what is trying to be done there is more complex than what I need and I had trouble applying it.

My question then is how can I get Spring JPA to just use the name I specify? Is there a new property for NamingStrategyDelegator use? Do I need to write my own strategy?

=========== Update ==========================

I think I'm converging on an answer. I created a simple Spring startup application (separate from my production project). I use H2 for the backend DB.

This discussion on Hiberate 5 Naming is very helpful. With it I figured out how to set naming strategies in Hibernate 5 like the following (in application.properties).

hibernate.implicit_naming_strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyHbmImpl
hibernate.physical_naming_strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

I created a physical naming strategy that passed through the name (like org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl does) and prints out values. From that I see that tables names are what I want through the physical naming layer.

I then set hibernate.show_sql=true to show generate SQL. In the generated SQL the names are also correct.

I am examining table names using DatabaseMetaData.

private void showTables() throws SQLException {
    DatabaseMetaData dbMetadata = getConnection().getMetaData();
    ResultSet result = dbMetadata.getTables(null, null, null, new String[] { "TABLE" });
    if (result != null) {
        boolean haveTable = false;
        while (result.next()) {
            haveTable = true;
            getLogger().info("Found table {}", result.getString("TABLE_NAME"));
        }
        if (!haveTable) {
            getLogger().info("No tables found");
        }

    }
}

I still see table names in ALL CAPS when I use the above code. This leads me to believe that DatabaseMetaData is showing all caps for some reason but the rest of the code uses the correct names. [EDIT: This conclusion is not correct. I was just confused by everything else that was happening. Later testing shows DatabaseMetaData shows table names with correct case.]

This is not yet a complete answer because there is still some strangeness in my production code that I need to investigate. But it's close and I wanted to post an update so potential readers don't waste time.

Here is my pass through physical naming strategy in case anyone is interested. I know it can help to see what others have done, especially when trying to find classes and packages in the Spring labyrinth.

package my.domain.eric;

import java.io.Serializable;

import org.hibernate.boot.model.naming.Identifier;
import org.hibernate.boot.model.naming.PhysicalNamingStrategy;
import org.hibernate.engine.jdbc.env.spi.JdbcEnvironment;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class NamingStrategyPhysicalLeaveAlone implements PhysicalNamingStrategy, Serializable {
    private static final long serialVersionUID = -5937286882099274612L;

    private static final Logger LOGGER = LoggerFactory.getLogger(NamingStrategyPhysicalLeaveAlone.class);

    protected Logger getLogger() {
        return LOGGER;
    }

    @Override
    public Identifier toPhysicalCatalogName(Identifier name, JdbcEnvironment context) {
        String nameText = name == null ? "" : name.getText();
        getLogger().info("toPhysicalCatalogName name: {}", nameText);
        return name;
    }

    @Override
    public Identifier toPhysicalSchemaName(Identifier name, JdbcEnvironment context) {
        String nameText = name == null ? "" : name.getText();
        getLogger().info("toPhysicalSchemaName name: {}", nameText);
        return name;
    }

    @Override
    public Identifier toPhysicalTableName(Identifier name, JdbcEnvironment context) {
        String nameText = name == null ? "" : name.getText();
        getLogger().info("toPhysicalTableName name: {}", nameText);
        return name;
    }

    @Override
    public Identifier toPhysicalSequenceName(Identifier name, JdbcEnvironment context) {
        String nameText = name == null ? "" : name.getText();
        getLogger().info("toPhysicalSequenceName name: {}", nameText);
        return name;
    }

    @Override
    public Identifier toPhysicalColumnName(Identifier name, JdbcEnvironment context) {
        String nameText = name == null ? "" : name.getText();
        getLogger().info("toPhysicalColumnName name: {}", nameText);
        return name;
    }
}

Answer 1

The answer to my question involves the following.

1. SQL is case insensitive, but it's not quite that simple. Quoted names are taken literally. Unquoted names are free to be interpreted. For example, PostgreSQL converts unquoted names to lower case while H2 converts them to upper case. Thus select * from MyTable_name in PostgreSQL looks for table mytable_name. In H2 the same query looks for MYTABLE_NAME. In my case the PostgreSQL table was created using a quoted name "MyTable_name" so select * from MyTable_name fails while select * from "MyTable_name" succeeds.
2. Spring JPA/Hibernate passes unquoted names to SQL.
3. In Spring JPA/Hibernate there are three methods that can be used to pass quoted names
   1. Explicitly quote the name: @Table(name = "\"MyTable_name\"")
   2. Implement a physical naming strategy that quotes names (details below)
   3. Set an undocumented attribute to quote all table and column names: spring.jpa.properties.hibernate.globally_quoted_identifiers=true (see this comment). This last is what I did because I also have column names for which I need case sensitivity.

Another source of confusion for me was that many sites refer to the old naming variable hibernate.ejb.naming_strategy or it's spring equivalent. For Hibernate 5 that is obsolete. Instead, as I mention in my question update, Hibernate 5 has implicit and physical naming strategies.

Furthermore, I was confused because there are hibernate properties and then there are Spring properties. I was using this very helpful tutorial. However it shows the unnecessary direct use of hibernate properties (as I list in my update) and then explicit configuration of LocalContainerEntityManagerFactoryBean and JpaTransactionManager. Much easier to use Spring properties and have them automatically picked up. Relevant to me are the naming strategies.

1. spring.jpa.hibernate.naming.implicit-strategy
2. spring.jpa.hibernate.naming.physical-strategy

To implement a physical naming strategy one needs to create a class that implements org.hibernate.boot.model.naming.PhysicalNamingStrategy as I show in my update above. Quoting names is actually very easy because the Identifier class passed to the method manages quoting or not. Thus the following method will quote table names.

@Override
public Identifier toPhysicalTableName(Identifier name, JdbcEnvironment context) {
    if (name == null) {
        return null;
    }
    return Identifier.quote(name);
}

Other things I learned that might be helpful to someone who came here searching for answers.

1. Using spring.jpa properties auto chooses SQL dialect. With direct hibernate I had SQL errors when I switched to Postgre.
2. Though Spring application context failures are very common, careful reading of the errors often points to solutions.
3. DatabaseMetaData reports table names correctly, I was just confused by everything else.
4. Set spring.jpa.show-sql=true to see generated SQL. Very helpful for debugging. Allowed me to see that correct table names are being used
5. spring.jpa.hibernate.ddl-auto supports at least the following values. create-drop: create tables on entry, drop on exit. create: create tables on entry but leave on exit. none: don't create or drop. I saw people use "update" as a value, but that failed for me. (For example here.) Here is a discussion on the options.
6. I had trouble in H2 using quoted column names but didn't investigate further.
7. Spring properties page is helpful but descriptions are very sparse.

Answer 2

The name is specified in the Entity annotation

@Entity(name = "MyTable_name")
public class MyTableData {
  ...
}

Answer 3

To have the exact name specified in @Table(...) as the table name in the database, came up with this solution:

application.yaml file

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/<dbname>?currentSchema=<schema-name>
    username: <username>
    password: <password>

  jpa:
    show-sql: true

    hibernate:
      # comment out ddl-auto as needed
      ddl-auto: create-drop
      naming:
        # the following property is important for this topic:
        # note that you are going implement the following java class:
        physical-strategy: paul.tuhin.sbtutorial.NamingStrategy

    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQLDialect
        default_schema: <schema-name>
        format_sql: true
        # this will quote your schema name as well:
        globally_quoted_identifiers: true

paul.tuhin.sbtutorial.NamingStrategy.java file:

package paul.tuhin.sbtutorial;

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

public class NamingStrategy extends PhysicalNamingStrategyStandardImpl {
    private static final long serialVersionUID = 1L;

    @Override
    public Identifier toPhysicalTableName(Identifier name, JdbcEnvironment context) {
        //The table name all converted to uppercase
        String tableName = name.getText();
        
        return Identifier.quote(Identifier.toIdentifier(tableName));
    }
    
    @Override
    public Identifier toPhysicalColumnName(Identifier name, JdbcEnvironment context) {
        String colnumName = name.getText();

        return Identifier.quote(Identifier.toIdentifier(colnumName));
    }
}

The solution is adapted from (with thanks): https://titanwolf.org/Network/Articles/Article?AID=b0f17470-3cfe-4ebc-9c45-25a462115be5