咨询：如何用Spring Data JPA为ETL工具推断表结构并创建动态模型

Great question—this is such a common pain point when building generic ETL tools where you can't predict the source schema upfront. Let's break down your options:

Can Spring Data JPA infer tables and schemas dynamically? #

The short answer is: Not really, at least not with its core, out-of-the-box features.

Spring Data JPA is built around static entity classes annotated with @Entity—it relies on these pre-defined mappings to generate queries, manage persistence, and provide repository abstractions. Since you don't know the tables or columns ahead of time, you can't create these entity classes, which means you can't use standard Spring Data repositories (like JpaRepository) as intended.

That said, you can leverage JPA's underlying EntityManager to work around this:

• You can extract database metadata directly via the JDBC connection wrapped by the EntityManager. This lets you discover tables, columns, and their types at runtime.
• You can execute native SQL queries and map results to generic structures like Map<String, Object> instead of typed entities.

Here's a quick example of how to fetch schema metadata with EntityManager:

@Autowired
private EntityManager entityManager;

public void discoverSchema() throws SQLException {
    DatabaseMetaData metaData = entityManager.unwrap(Connection.class).getMetaData();
    
    // Fetch all tables in the database
    ResultSet tables = metaData.getTables(null, null, "%", new String[]{"TABLE"});
    while (tables.next()) {
        String tableName = tables.getString("TABLE_NAME");
        System.out.println("Found table: " + tableName);
        
        // Fetch columns for the current table
        ResultSet columns = metaData.getColumns(null, null, tableName, "%");
        while (columns.next()) {
            String columnName = columns.getString("COLUMN_NAME");
            String columnType = columns.getString("TYPE_NAME");
            System.out.println("  Column: " + columnName + " (" + columnType + ")");
        }
    }
}

But let's be real: this approach feels clunky with Spring Data JPA. You're bypassing almost all of its convenience features (like query derivation, CRUD methods) and essentially just using JPA as a wrapper for JDBC. It's doable, but not ideal for keeping your solution clean.

Spring JDBC is a far better fit for this scenario #

If you want a clean, straightforward solution, Spring JDBC is exactly what you need. It's designed for flexibility when you don't have pre-defined entities, and it avoids the overhead of JPA's ORM layer.

With Spring JDBC's JdbcTemplate or NamedParameterJdbcTemplate, you can:

1. Discover schema metadata the same way as with JPA (using DatabaseMetaData), but more directly.
2. Query dynamic tables and get results as List<Map<String, Object>>—each map represents a row, with keys as column names and values as the corresponding data.
3. Persist transformed data to the target database using dynamic SQL statements, no entity classes required.

Here's a simplified example of reading data from a dynamic table with JdbcTemplate:

@Autowired
private JdbcTemplate jdbcTemplate;

public List<Map<String, Object>> readDynamicTable(String tableName) {
    // Safely query the table (you'll want to add validation to avoid SQL injection!)
    String sql = "SELECT * FROM " + tableName;
    return jdbcTemplate.queryForList(sql);
}

// Then process the rows:
List<Map<String, Object>> rows = readDynamicTable("actor");
for (Map<String, Object> row : rows) {
    for (Map.Entry<String, Object> entry : row.entrySet()) {
        String column = entry.getKey();
        Object value = entry.getValue();
        // Perform your transformation logic here
        // Then persist to the target database
    }
}

This approach is lightweight, easy to maintain, and perfectly aligns with your goal of building a generic ETL tool where schema is unknown at runtime.

Final Verdict #

While you can hack together a solution with Spring Data JPA's low-level APIs, it's not a natural fit for dynamic schema scenarios. Spring JDBC is the cleaner, more straightforward choice here—it lets you focus on the ETL logic without fighting against JPA's static entity requirements.

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