求助：出现SQL Error:972,SQLState:42000 ORA-00972标识符过长错误

Hey there! Let's fix that ORA-00972 error you're running into. This is a super common gotcha when working with Oracle databases alongside Spring Boot and JPA/Hibernate—here's what's going on and how to fix it:

What's causing this error? #

Oracle has a hard limit: all database identifiers (table names, column names, constraint names like foreign keys or unique constraints) can't be longer than 30 characters. When Hibernate auto-generates these names (based on your entity class names, property names, or relationships) or if you've manually defined a name that exceeds this limit, you'll hit the ORA-00972 error.

Step-by-step solutions #

Let's walk through the most common fixes tailored to your Spring Boot setup:

1. Manually shorten table and column names

The simplest fix is explicitly defining short, compliant names for your entities and their properties using JPA annotations:

@Entity
@Table(name = "PHARMACY") // 确保表名不超过30字符
public class Pharmacy {
    @Id
    @Column(name = "PHARMACY_ID") // 自定义短列名
    private Long id;

    @Column(name = "PHARMACY_NAME")
    private String pharmacyName;

    // 其他属性和方法...
}

2. Fix long auto-generated constraint names

Hibernate often generates long constraint names (like foreign key names) that can blow past the 30-character limit. You can either:

• Use Hibernate's legacy naming strategy to generate shorter names:
  Add these lines to your application.properties:

  spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl
  spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
  

• Or create a custom naming strategy to automatically truncate long identifiers:
  Write a custom physical naming strategy class:

  package com.example.demo;
  
  import org.hibernate.boot.model.naming.Identifier;
  import org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl;
  import org.hibernate.engine.jdbc.env.spi.JdbcEnvironment;
  
  public class OracleTruncatingNamingStrategy extends PhysicalNamingStrategyStandardImpl {
      private static final int MAX_ORACLE_ID_LENGTH = 30;
  
      @Override
      public Identifier toPhysicalTableName(Identifier name, JdbcEnvironment context) {
          return truncateIfNeeded(name);
      }
  
      @Override
      public Identifier toPhysicalColumnName(Identifier name, JdbcEnvironment context) {
          return truncateIfNeeded(name);
      }
  
      private Identifier truncateIfNeeded(Identifier name) {
          if (name == null || name.getText().length() <= MAX_ORACLE_ID_LENGTH) {
              return name;
          }
          String truncatedName = name.getText().substring(0, MAX_ORACLE_ID_LENGTH);
          return Identifier.toIdentifier(truncatedName);
      }
  }
  

  Then configure it in application.properties:

  spring.jpa.hibernate.naming.physical-strategy=com.example.demo.OracleTruncatingNamingStrategy
  

3. Shorten join table names for relationships

If you have @ManyToMany or other relationships, Hibernate auto-generates join table names by concatenating entity names—this can get long fast. Manually specify a short join table name:

@ManyToMany
@JoinTable(
    name = "PHARMACY_MED", // 短名称，避免过长
    joinColumns = @JoinColumn(name = "PHARMACY_ID"),
    inverseJoinColumns = @JoinColumn(name = "MEDICINE_ID")
)
private List<Medicine> medicines;

Quick check list #

• Verify all table names, column names, and constraint names are ≤30 characters
• If using auto-generation, ensure Hibernate's naming strategy produces compliant names
• For relationships, explicitly define short join table names

内容的提问来源于stack exchange，提问作者ASharma7