CODEWITHSUNDEEP
hibernatejpaspring-boot
Tim
Tim

Reputation: 1695

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

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.

Upvotes: 14

Views: 35507

Answers (6)

Sanjay
Sanjay

Reputation: 477

Thanks for all your response, I would like to add one more point to the findings

  1. The name we give in table property of @Column annotation should match with the name we give in @Table and @SecondaryTable annotation.
  2. Hence when we prepend the schema name in @Table, make sure we do it in @Column
@Table(name="schema1.table")
@SecondaryTables({
    @SecondaryTable(name = "schema2.table")
})

@Column( name = "col1", table = "schema2.table")

Note: You don't have to specify table property for the primary table column

Upvotes: 0

Sofia Paixão
Sofia Paixão

Reputation: 326

I stumbled upon the same issue when dealing with hibernate envers in a spring boot project. My auditing tables are saved in a separate schema, but since I wanted to add user information to the revision info, I had to create a revision entity which would by default go to the default schema instead of the auditing one. Since in this project the development servers are MySQL but the testing and production servers must be SQL Server, specifying catalog in @Table was not an option since catalog has a different meaning for SQL Server (and according to this rejected bug report this is intended behaviour).

Sergei Ivanov's approach was giving me issues, since the NamingStrategy they're using takes a different approach for CamelCase conversion, and I didn't want to add explicit @Table(name="...") annotations to every entity. In the end I adopted an adapted version of their approach by creating a custom NamingStrategy that extends the default one, and overrides only the table name conversion so dots are not replaced with underscores:

public class TableDotSpringPhysicalNamingStrategy extends SpringPhysicalNamingStrategy {
  public Identifier toPhysicalTableName(Identifier name, JdbcEnvironment jdbcEnvironment) {
    return this.apply(name, jdbcEnvironment);
  }

  private Identifier apply(Identifier name, JdbcEnvironment jdbcEnvironment) {
    if (name == null) {
      return null;
    } else {
      StringBuilder builder = new StringBuilder(name.getText());

      for(int i = 1; i < builder.length() - 1; ++i) {
        if (this.isUnderscoreRequired(builder.charAt(i - 1), builder.charAt(i), builder.charAt(i + 1))) {
          builder.insert(i++, '_');
        }
      }

      return this.getIdentifier(builder.toString(), name.isQuoted(), jdbcEnvironment);
    }
  }

  private boolean isUnderscoreRequired(char before, char current, char after) {
    return Character.isLowerCase(before) && Character.isUpperCase(current) && Character.isLowerCase(after);
  }
}

And then add the relevant configuration in my application.properties:

spring.jpa.properties.hibernate.physical_naming_strategy=my.package.path.TableDotSpringPhysicalNamingStrategy

This way, specifying a different schema without doing away with the default schema works independent of the database being MySQL or not.

Upvotes: 1

sergei_ivanov
sergei_ivanov

Reputation: 391

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.

Upvotes: 15

Suneet Khurana
Suneet Khurana

Reputation: 449

There is no need to erase default database name in below configuration.

spring.datasource.url=jdbc:mysql://localhost:3306/defaultDb?autoReconnect=true&useUnicode=true&connectionCollation=utf8_general_ci&characterSetResults=utf8&zeroDateTimeBehavior=convertToNull&verifyServerCertificate=false&useSSL=false&rewriteBatchedStatements=true&profileSQL=false

I found two options to add database name dynamically in mysql queries with Spring JPA.

  1. If you will add catalog option in @Table annotation then only database name will be considered by catalog option and database name will be append in your query like "sales.proposalstatuses".

    @Entity
@Table(name="proposalstatuses",schema="sales",catalog = "sales")
public class ProposalStatus implements Serializable {

  2. Append databaseName with tableName in "name" option available in @Table annotation as showing below. 

    @Entity
@Table(name="sales.proposalstatuses")
public class ProposalStatus implements Serializable {

I have edited above examples as shared by https://stackoverflow.com/users/1100847/tim and above example work for me with spring boot(2.0.0.RELEASE), Sping JPA and mysql database(6.0.6)

Upvotes: 2

Tim
Tim

Reputation: 1695

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.

Upvotes: 8

Abdullah Mohammad Motiullah
Abdullah Mohammad Motiullah

Reputation: 12572

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")

Upvotes: 5

Related Questions