Java JPA / Hibernate with SQL Server - how to determine when columns in db are not mapped to an entity?

Question

I have a Spring webapp using JPA / Hibernate for entity matching, and this works great to perform all the usual tasks, for example. CRUD.

I can also detect when a field in the model is missing from the database (hibernate throws "Invalid column name").

My problem is that now I have a requirement to detect and warn the user when there are new columns in the database that are not represented by the model.

The reason for this is that the application should always allow the user to set / view all the fields present in the database and not transparently ignore the available field without notification.

Is there an easy way to use JPA / Hibernate for

• output an error if the database has a new non-displayed column, or
• manually check if all columns are counted?

If there is no simple method, I would suggest that I can request the info_schema, but I would like to avoid this if at all possible.

Thanks in advance!

Answer 1

There is no such option hbm2ddl.autoif you are looking for something like that. For example, it updatewill only check mapped columns and ignore additional columns - at least it will not lose them.

Thus, you have to go a hard way and check manually:

SessionFactoryImplementor sessionFactory = (SessionFactoryImplementor) ((Session) this.em.getDelegate()).getSessionFactory();

// Find all mapped tables with all mapped columns
final Map<String, Set<String>> tableColumns = new HashMap<>();

for (ClassMetadata metadata : sessionFactory.getAllClassMetadata().values()) {
  AbstractEntityPersister persister = (AbstractEntityPersister) metadata;
  Set<String> columns = new HashSet<>();
  tableColumns.put(persister.getTableName(), columns);
  for (String propertyName : persister.getPropertyNames()) {
    for (String propertyColumnName : persister.getPropertyColumnNames(propertyName)) {
      columns.add(propertyColumnName);
    }
  }
}

for (CollectionMetadata metadata : sessionFactory.getAllCollectionMetadata().values()) {
  AbstractCollectionPersister persister = (AbstractCollectionPersister) metadata;
  // ... extract key, index and element columns from persister, similar to AbstractEntityPersister
}

// Compare columns with existing metadata
sessionFactory.getCurrentSession().doWork(new Work() {
  public void execute(Connection connection) throws SQLException {
    for (Map.Entry<String, Set<String>> entry : tableColumns.entrySet()) {
      String tableName = entry.getKey();
      ResultSet rs = connection.getMetaData().getColumns(null, null, tableName, null);
      try {
        while (rs.next()) {
          String columnName = resultSet.getString("COLUMN_NAME");
          if (!entry.getValue().remove(columnName)) {
            log.error("Column not mapped: {}.{}", tableName, columnName));
          }
        }
        if (!entry.getValue().isEmpty()) {
          log.error("Columns not defined: {}.{}", tableName, entry.getValue()));
        }
      } finally {
        rs.close();
      }
    }
  }
});

Answer 2

( , , ), "" .

public void printMetadata() {
    Set<String> dbColumns = new HashSet<>();
    Set<String> entityProperties = null;
    sessionFactory.getCurrentSession().doWork(new Work() {
        @Override
        public void execute(Connection connection) throws SQLException {
        //**Post** is EntityClass with `@Table` & `@Entity` annotated.               
        ResultSet resultSet =  connection.getMetaData().getColumns(null, null,
                    Post.class.getDeclaredAnnotation(Table.class).name(), null);
            while (resultSet.next()) {
                dbColumns.add(resultSet.getString("COLUMN_NAME"));
            }
        }
    });

    ClassMetadata classMetadata = sessionFactory.getClassMetadata(Post.class);
    entityProperties = new HashSet<>(Arrays.asList(classMetadata.getPropertyNames()));

    if (entityProperties.size() != dbColumns.size()) {
        //Throw Error.
        //Else, you can compare ColumnName with Property using some pattern.
    }
}