在Spring JPA中设置表名称 [英] Set table name in Spring JPA

问题描述

我想我正在尝试做一些非常简单的事情.通过JPA使用Spring Boot(1.3.3.RELEASE)，我想设置一个表名.

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 {
  ...
}

我在数据库中期望的是带有"MyTable_name"的表.对我来说似乎完全合理.但这不会发生.我得到一个名称为"MY_TABLE_NAME"(H2后端)或"my_table_name"(Postgre后端)的表.从这里开始，我将继续使用Postgre，因为我的目标是在不控制表名的情况下读取现有的数据库.

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.

经过一番研究，我发现一些帖子说我应该使用spring.jpa.hibernate.naming-strategy属性.这没有太大帮助.设置为最常用的org.hibernate.cfg.ImprovedNamingStrategy会产生相同的行为:"my_table_name".设置为org.hibernate.cfg.EJB3NamingStrategy会生成"mytable_name".设置为org.hibernate.cfg.DefaultNamingStrategy会导致Spring内部出现应用程序上下文错误.

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.

辞职去写我自己的书，我开始看org.hibernate.cfg.ImprovedNamingStrategy.我发现它使用了已弃用的org.hibernate.cfg.NamingStrategy.这建议改为使用NamingStrategyDelegator.我看了看它的 Java文档但不确定如何申请.我发现了这篇文章.就我所理解的解释而言，在那里要做的事情比我需要的复杂得多，我在应用它时遇到了麻烦.

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.

然后我的问题是如何使Spring JPA仅使用我指定的名称?是否有供NamingStrategyDelegator使用的新属性?我需要编写自己的策略吗?

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 ==========================

我想我正在寻找答案.我创建了一个简单的Spring启动应用程序(与生产项目分开).我将H2用于后端数据库.

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.

="a 此讨论 .com/questions/32165694/spring-hibernate-5-naming-strategy-configuration>休眠5命名非常有用.有了它，我弄清楚了如何在Hibernate 5中设置命名策略，如下所示(在application.properties中).

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

我创建了一个通过名称传递的物理命名策略(如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.

然后我将hibernate.show_sql = true设置为显示生成SQL.在生成的SQL中，名称也是正确的.

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

我正在使用DatabaseMetaData检查表名.

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

    }
}

当我使用上面的代码时，我仍然在ALL CAPS中看到表名.这使我相信DatabaseMetaData出于某种原因显示所有大写字母，但其余代码使用正确的名称. [编辑:此结论不正确.我只是对正在发生的其他一切感到困惑.以后的测试显示DatabaseMetaData显示具有正确大小写的表名.]

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.

如果有人感兴趣，这里是我通过物理命名策略的指南.我知道这有助于查看其他人的所作所为，特别是在尝试在Spring迷宫中查找类和包时.

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;
    }
}

推荐答案

我的问题的答案涉及以下内容.

The answer to my question involves the following.

1. SQL不区分大小写，但并不是那么简单.引用的名称是按字面意义使用的.不带引号的名称可以自由解释.例如，PostgreSQL将未引用的名称转换为小写，而H2将其转换为大写.因此，在PostgreSQL的MyTable_name中选择*寻找表mytable_name.在H2中，同一查询将查找MYTABLE_NAME.在我的情况下，PostgreSQL表是使用带引号的名称"MyTable_name"创建的，因此select * from MyTable_name失败，而select * from"MyTable_name"成功.
2. Spring JPA/Hibernate将未加引号的名称传递给SQL.
3. 在Spring JPA/Hibernate中，可以使用三种方法来传递带引号的名称

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. 明确引用名称:@Table(name ="\" MyTable_name \")
2. 实施引用名称的物理命名策略(详细信息如下)
3. 设置一个未记录的属性来引用所有表和列名称:spring.jpa.properties.hibernate.globally_quoted_identifiers = true(请参阅

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.

让我感到困惑的另一个原因是，许多站点都引用了旧的命名变量hibernate.ejb.naming_strategy或与之等效的spring.对于过时的Hibernate 5.相反，正如我在问题更新中提到的那样，Hibernate 5具有隐式和物理命名策略.

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.

此外，我很困惑，因为有休眠属性，然后有Spring属性.我正在使用这很有帮助的教程.但是，它显示了不必要的直接使用休眠属性(如我在更新中列出的那样)，然后显式配置LocalContainerEntityManagerFactoryBean和JpaTransactionManager.使用Spring属性并使它们自动拾取起来要容易得多.与我有关的是命名策略.

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

要实现物理命名策略，需要创建一个实现org.hibernate.boot.model.naming.PhysicalNamingStrategy的类，如我在上面的更新中所示.实际上，对名称进行引用非常容易，因为传递给该方法的Identifier类可以管理引用或不引用.因此，以下方法将引用表名.

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. 使用spring.jpa属性自动选择SQL方言.使用直接休眠模式时，我切换到Postgre时遇到了SQL错误.
2. 尽管Spring应用程序上下文故障非常普遍，但是仔细阅读错误通常会指出解决方案.
3. DatabaseMetaData正确报告表名称，其他一切让我感到困惑.
4. 设置spring.jpa.show-sql = true以查看生成的SQL.对调试非常有帮助.让我看到正在使用正确的表名
5. spring.jpa.hibernate.ddl-auto至少支持以下值. create-drop:在入口处创建表，在出口处创建表. create:在入口处创建表，但在出口处保留. none:不创建或删除.我看到人们使用更新"作为值，但这对我来说是失败的. (例如，此处.)这是关于选项的讨论.
6. 我在H2中使用带引号的列名遇到了麻烦，但没有进一步调查.
7. Spring属性页面很有帮助，但说明很少.

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.

这篇关于在Spring JPA中设置表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！