Spring Boot JPA不会在查询表中添加模式名称 [英] 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;
    }
}

然后我希望Hibernate生成它的查询，如select ... from sales.proposalstatuses.但是，相反，我看到了:

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

因此，我有一个具有多个架构的数据库(一个连接).当Hibernate引用表时，如何使其使用架构名称?似乎应该很简单，但是我一定要缺少一些东西.
谢谢！

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!

我正在使用Spring Boot 1.5.7，该版本使用Hibernate JPA 2.1和Hibernate Core 5.0.12. 如果我使用H2数据源，则可以使用.如果MySQL是数据源，我只会看到一个问题.

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.

我已经阅读了有关在默认架构中为要引用的表创建视图的信息.但是，这不是一个可行的选择，因为我将不得不创建很多视图.看来，Hibernate应该可以轻松解决这一问题.

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.

这是来自application.properties的配置设置:

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

在URL中更改模式名称可以使我访问该模式中的数据，但是除了URL中列出的模式之外，我无法访问任何其他模式.

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.

推荐答案

我缺少的关键在于配置以及数据库类型.在MySQL中，数据库和模式之间没有真正的区别.使用@Table批注中的schema属性，它引用的是真实"模式，但不是在MySQL中定义的.这解释了为什么schema属性适用于H2数据库而不适用于MySQL.

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.

@Table(name ="SCHEMA_NAME.TABLE_NAME")注释最初不起作用，因为我的数据源URL中具有默认模式.网址必须为

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

通过此更改，所有内容都可以与MySQL一起使用.

With this change everything works with MySQL.

还要注意一点，为了使它也能与H2一起使用，请确保您不定义hibernate.default_schema属性.

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.

这篇关于Spring Boot JPA不会在查询表中添加模式名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！