Spring Boot JPA does not prepend schema name to tables in query

Question

From what I understand (for example, from here), if I specify the schema for my entity, then it should use that schema name when creating the query.

So, if I have an entity of:

@Entity
@Table(name="proposalstatuses",schema="sales")
public class ProposalStatus implements Serializable {
    private static final long serialVersionUID = 1L;
    private int proposalStatusID;
    private String proposalStatusName;

    public ProposalStatus() {}

    public ProposalStatus(String proposalStatusName) {
        this.proposalStatusName = proposalStatusName;
    }

    @Id
    @Column(name="pk_proposalstatusid")
    @GeneratedValue(strategy = GenerationType.AUTO)
    public int getProposalStatusID() {
        return proposalStatusID;
    }

    public void setProposalStatusID(int proposalStatusID) {
        this.proposalStatusID = proposalStatusID;
    }

    @Column(name="proposalstatusname", unique=true, nullable=false)
    public String getProposalStatusName() {
        return proposalStatusName;
    }

    public void setProposalStatusName(String proposalStatusName) {
        this.proposalStatusName = proposalStatusName;
    }
}

then I would expect Hibernate to generate its queries like select ... from sales.proposalstatuses. However, instead I see:

select proposalst0_.pk_proposalstatusid as pk_propo1_8_, proposalst0_.proposalstatusname as proposal2_8_ 
from proposalstatuses proposalst0_ 
order by proposalst0_.proposalstatusname asc

This is not a huge deal for this case, but now I want to be able to use joins with a table in a different schema, and that is failing because it thinks the tables don't exist (and they don't in the default schema).

So, I have one database (one connection) with multiple schema. How do I get Hibernate to use the schema name when it references the tables? It seems like it should be very straight-forward, but I must be missing something.
Thanks!

I am using Spring Boot 1.5.7 which uses Hibernate JPA 2.1 and Hibernate Core 5.0.12. This does work if I use an H2 datasource. I am only seeing a problem if MySQL is the datasource.

I have read about creating a view in the default schema for the table I want to reference. However, that is not a feasible option, as I would have to create a great many views; and it seems like Hibernate should be able to handle this without that much effort.

Here are the configuration settings from application.properties:

spring.datasource.url=jdbc:mysql://localhost/sales?verifyServerCertificate=false&useSSL=true
spring.datasource.username=user
spring.datasource.password=pass
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
spring.datasource.testWhileIdle=true
spring.datasource.validationQuery=SELECT 1
spring.jpa.show-sql=true
spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy

Changing the name of the schema in the url allows me to access the data in that schema, but I can't get to any other schema than the one listed in the url.

Answer 1

I have just migrated an application from using a hand-crafted EntityManager configuration to Spring Boot 2.0, and then these problems immediately cropped up.

The right way of doing things would be to use

@Table(name="proposalstatuses",catalog = "sales")

...as @suneet-khurana suggests above. Note that MySQL does not support schemas, you need to use catalog instead.

However, in our case that would require changing a lot of entities, and in the short term I wanted to avoid code changes and use configuration to revert to the old behaviour of embedding schema name into the table name.

So after a bit of research and debugging I figured out that explicitly setting the following property does the trick:

spring.jpa.properties.hibernate.physical_naming_strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

It looks like the default implementation in Spring Boot JPA is SpringPhysicalNamingStrategy, which sanitises the names by replacing all dots with underscores.

Answer 2

The key that I was missing was in the configuration as well as the database type. In MySQL, there is no real distinction between a database and a schema. With the schema property in the @Table annotation, it is referencing a "real" schema, but not as it is defined in MySQL. This explains why the schema property works for an H2 database but not MySQL.

The @Table(name="SCHEMA_NAME.TABLE_NAME") annotation did not work initially because I had a default schema in my datasource url. The url needed to be

spring.datasource.url=jdbc:mysql://localhost?verifyServerCertificate=false&useSSL=true

With this change everything works with MySQL.

As one further note, in order for this to work with H2 as well, make sure you do not define the hibernate.default_schema property.

Answer 3

The way to handle this would be to specify your schemas for your entities that has a name conflict.

Use

@Table(name="TABLE_NAME", schema="SCHEMA_NAME")

or

@Table(name="SCHEMA_NAME.TABLE_NAME")