使用Java从MySQL导出关联实体数据的技术问题求助

问题描述 #

使用Java的JDBCTemplate queryForStream()实现超大规模MySQL数据库流式导出，处理3GB数据仅占用约600MB内存，普通实体导出完全正常。但当实体存在一对一关联关系时（如country与capital），无法获取关联实体的数据：导出时仅能得到country表的字段值，关联的capital字段为空，因为该字段不存在于country表中。

限制条件 #

• 无法使用mysqldump导出数据，必须在导出前对数据进行预处理

核心代码 #

数据库查询主逻辑 #

try (Stream<?> dataStream = template.queryForStream(new StreamingStatementCreator(sql), getEntityRowMapper(clazz, converter, context))) { 
    // 数据处理逻辑
}

StreamingStatementCreator.java #

public class StreamingStatementCreator implements PreparedStatementCreator {
  private final String sql;

  public StreamingStatementCreator(String sql) {
    this.sql = sql;
  }

  @Override
  public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
    final PreparedStatement statement = connection.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    statement.setFetchSize(Integer.MIN_VALUE);
    return statement;
  }
}

EntityRowMapper相关方法 #

private EntityRowMapper<?> getEntityRowMapper(Class<?> domainType, JdbcConverter converter, JdbcMappingContext context) {
    return new EntityRowMapper<>(getRequiredPersistentEntity(domainType, context), converter);
}

private <S> RelationalPersistentEntity<S> getRequiredPersistentEntity(Class<S> domainType, JdbcMappingContext context) {
    return (RelationalPersistentEntity<S>) context.getRequiredPersistentEntity(domainType);
}

数据库表结构 #

country表 #

CREATE TABLE `country` (
  `code` int(11) DEFAULT NULL,
  `updated_by` varchar(100) DEFAULT NULL,
  `created_by` varchar(100) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `updated_on` datetime(3) DEFAULT NULL,
  `created_on` datetime(3) DEFAULT NULL,
  `version` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

字段详情：

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| code       | int(11)      | YES  |     | NULL    |                |
| updated_by | varchar(100) | YES  |     | NULL    |                |
| created_by | varchar(100) | YES  |     | NULL    |                |
| name       | varchar(50)  | YES  |     | NULL    |                |
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| updated_on | datetime(3)  | YES  |     | NULL    |                |
| created_on | datetime(3)  | YES  |     | NULL    |                |
| version    | bigint(20)   | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

capital表 #

CREATE TABLE `capital` (
  `name` varchar(50) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `population` int(11) DEFAULT NULL,
  `country_capital_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sys_country_capital_id` (`country_capital_id`),
  CONSTRAINT `fk__capital__country_country_capital_id_id` FOREIGN KEY (`country_capital_id`) REFERENCES `country` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

字段详情：

+--------------------+-------------+------+-----+---------+----------------+
| Field              | Type        | Null | Key | Default | Extra          |
+--------------------+-------------+------+-----+---------+----------------+
| name               | varchar(50) | YES  |     | NULL    |                |
| id                 | int(11)     | NO   | PRI | NULL    | auto_increment |
| population         | int(11)     | YES  |     | NULL    |                |
| country_capital_id | int(11)     | YES  | UNI | NULL    |                |
+--------------------+-------------+------+-----+---------+----------------+

country表与capital表为一对一关联。

期望输出 #

导出的CSV文件需包含关联实体数据，示例如下：

|id  |code  |name   |capital                                            |createdBy   |createdOn     |updatedBy  |updatedOn      |version
|1   |91    |India  |{"id":1,"name":"New Delhi","population":1173902}   |System      |1.69E+09      |System     |1.69E+09       |0
|2   |91    |India  |{"id":2,"name":"New Delhi","population":1173902}   |System      |1.69E+09      |System     |1.69E+09       |0
|3   |91    |India  |{"id":3,"name":"New Delhi","population":1173902}   |System      |1.69E+09      |System     |1.69E+09       |0
|4   |91    |India  |{"id":4,"name":"New Delhi","population":1173902}   |System      |1.69E+09      |System     |1.69E+09       |0

需求 #

寻求可行的解决方案，实现流式导出的同时正确获取关联实体的数据。

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