How do I verify the schemas in Java POJOs and the DB match?-CodePudding

I have Java POJOs with @Table, @Id, @Column, etc. How do I verify the schema represented in the Java POJOs matches the schema in the DB?

I am using EclipseLink. I know about eclipselink.ddl-generation=create-or-extend-tables, but I would like to make sure the Java POJOs "schema" matches the DB before I enable this.

CodePudding user response：

Well, it's interesting question. I don't know any tool that can do it automatically but I can imagine how to do it.

So, you write a test.

In the test you retrieve all fields from your model Java POJOs with reflection to an array, it's something like

Field[] allFields = YourJavaPOJO.class.getDeclaredFields();

Then you retrieve all columns names from database to another array. To do it you select one row from your table to a ResultSet and extract columns names from the ResultSet.

public String comparePojoWithDb() {
    return jdbcTemplate.query("SELECT * FROM the_table fetch first 1 row only",  new comparePojoWithDbExtractor());
  }
  private static final class comparePojoWithDbExtractor implements ResultSetExtractor<String> {
    @Override
    public String extractData(ResultSet resultSet) throws SQLException {
      return convertResultSetToColumnNamesArray(resultSet);
    }
  }
public static String convertResultSetToColumnNamesArray(ResultSet resultSet) throws SQLException {
    List<String> list = new ArrayList<String>();
        while (resultSet.next()) {
          for (int i = 0; i < resultSet.getMetaData().getColumnCount(); i  ) {
           list.add(resultSet.getMetaData().getColumnLabel(i   1));
          }
        }
        return list;
      }

(!!! I skipped obvious convertions List <-> String[] <--> String <--> array)

The column's names can also be extracted in JPA with reflection from baseEntity.getClass().getFields()

So you got two arrays and you need to compare them.

The table can be empty, names of columns can differ in POJO because of columns annotations but all that generally can be done.

CodePudding user response：

EclipseLink can validate the ORM metadata against the schema using its IntegrityChecker. Enabling it will have it get the DB metadata from the JDBC driver and validate that each mapping has the required columns in the database tables.