Handle relationships while querying the data from a MySQL database using java

Question

Problem Statement: I am trying to export a huge database using java. I have implemented it using queryForStream() of JDBCTemplate as it provides streaming feature. For normal entities, its working perfectly. For, let's say ~3GB of data, its consuming around 600MB of memory, which is fine. The problem comes when relationships are introduced in the entity. There I am unable to get the data of entities which are in relationship with other entity. Example: I have an entity E1 which has 1:many relationship with entity E2. When I hit my export API, it exports whatever is in the table of entity E1, which makes sense, as my sql query asks it to do it like that, while the columns which has relationships are coming blank as it does not exist in the table.

Any idea on how to proceed?

Edit: I cannot use mysqldump to export the data, as I am doing some processing before exporting it.

Edit: Main logic to hit DB: try (Stream<?> dataStream = template.queryForStream(new StreamingStatementCreator(sql), getEntityRowMapper(clazz, converter, context))) { //processing logic goes here }

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

getEntityRowMapper(clazz, converter, context)):

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

Edit:

Table-1
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    |                |
+------------+--------------+------+-----+---------+----------------+

Table-2
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    |                |
+--------------------+-------------+------+-----+---------+----------------+

Table-1 is in ONE_TO_ONE relationship with Table-2

Expected result is a csv file with the expected value would be:

|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