问题描述

我想使用 spring data jpa 获取所有表的列列表，我的数据库很灵活，所以查询应该适用于所有类型的数据库.

I am in a situation where I want to get all table's column list using spring data jpa, my database is flexible so, the query should be work on all kind of database.

推荐答案

JPA 规范包含 Metamodel API，允许您查询有关托管类型及其托管字段的信息.但是，它不涵盖底层数据库.因此，在 JPA 中还没有任何现成可用的方法来查询数据库元数据.

JPA specification contains the Metamodel API that allows you to query information about the managed types and their managed fields. It does not however cover the underlying database. So, there is nothing out-of-the-box in JPA yet for querying the database metadata.

每个 RDBMS 存储元信息的方式也不同，因此不可能有一个简单的、与数据库无关的解决方案.

The way each RDBMS stores meta information is also different so there cannot be a simple, database-agnostic solution.

然而，你想要的可以通过几跳来实现.

What you want can however be achieved through a few hops.

第 1 步:定义一个将保存元数据信息的实体类.

Step 1: Define an entity class that will hold metadata information.

@Entity
@IdClass(TableMetadataKey.class)
@Table(name = "table_metadata")
class TableMetadata {
  @Column(name = "column_name")
  @Id
  String columnName;

  @Column(name = "table_name")
  @Id
  String tableName;

  public static class TableMetadataKey implements Serializable {
    String columnName;
    String tableName;
  }
}

第 2 步:为实体添加存储库.

public interface TableMetadataRepository extends JpaRepository<TableMetadata, TableMetadataKey>
{
  TableMetadata findByTableName(String tableName);
}

第 3 步:定义一个名为 table_metadata 的数据库视图以映射到实体类.这必须使用特定于数据库的查询来定义(因为每个数据库都有不同的元数据存储方式).

Step 3: Define a database view named table_metadata to be mapped to the entity class. This will have to be defined using a database-specific query (because each database has a different way of storing its metadata).

可以在此步骤上执行特定于数据库的优化，例如，将物化视图与 Oracle 一起使用以加快访问速度等.

Database-specific optimizations can be performed on this step, such as, using materialized views with Oracle for faster access, etc.

或者，可以使用所需的列创建名为 table_metadata 的表，并使用 SQL 脚本定期填充.

Alternatively, a table named table_metadata can be created with the required columns and populated periodically using a SQL script.

现在应用程序可以完全访问所需的元数据.

Now the application has full access to the required metadata.

List<TableMetadata> metadata = tableMetadataRepository.findAll()
TableMetadata metadata = tableMetadataRepository.findByTableName("myTable");

<小时>

需要注意的一个问题是，并非架构中的所有表都可以映射为 JPA 实体，或者并非所有表中的所有列都可以映射为实体字段.因此，直接查询数据库元数据可能会得到与实体类和字段不匹配的结果.

One issue to be noted is that not all tables in a schema may be mapped as JPA entities or not all columns in all tables may be mapped as entity fields. Therefore, directly querying the database metadata may give results that do not match the entity classes and fields.