Is there any Simplest way to get Table metadata (c

Question

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.

Answer 1

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.

---

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.

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

Step 2: Add the repository for the entity.

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

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

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

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

---

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.