This code performs the following tasks: ### 1. **Purpose of the...

September 1, 2025 at 06:31 AM

public static void exportRecordData(Map<String, Map<String, Object>> tableKeyMap, Config config) {
    try (Connection conn = DriverManager.getConnection(config.getSchema2Url(), config.getSchema2Username(), config.getSchema2Password())) {
        for (String table : tableKeyMap.keySet()) {
            Map<String, Object> keys = tableKeyMap.get(table);
            // Step 1: Fetch column types from USER_TAB_COLUMNS
            Map<String, Integer> columnTypes = new HashMap<>();
            try (PreparedStatement ps = conn.prepareStatement(
                    "SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ?")) {
                ps.setString(1, table.toUpperCase());
                try (ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        String col = rs.getString("COLUMN_NAME").toUpperCase();
                        String type = rs.getString("DATA_TYPE").toUpperCase();
                        int sqlType;
                        switch (type) {
                            case "NUMBER" -> sqlType = Types.NUMERIC;
                            case "INTEGER" -> sqlType = Types.INTEGER;
                            case "BIGINT" -> sqlType = Types.BIGINT;
                            case "FLOAT" -> sqlType = Types.FLOAT;
                            case "DOUBLE PRECISION" -> sqlType = Types.DOUBLE;
                            case "DATE" -> sqlType = Types.DATE;
                            case "TIMESTAMP" -> sqlType = Types.TIMESTAMP;
                            case "VARCHAR2", "CHAR", "NVARCHAR2" -> sqlType = Types.VARCHAR;
                            default -> sqlType = Types.VARCHAR;
                        }

                        columnTypes.put(col, sqlType);

                    }

                }

            }

            // Step 2: Build dynamic WHERE clause
            StringBuilder query = new StringBuilder("SELECT * FROM ").append(table).append(" WHERE 1=1");
            List<Object> params = new ArrayList<>();
            for (Map.Entry<String, Object> entry : keys.entrySet()) {
                String key = entry.getKey().toUpperCase();
                Object val = entry.getValue();
                int sqlType = columnTypes.getOrDefault(key, Types.VARCHAR);
                if (sqlType == Types.DATE) {

                    // Inline substitution for DATE
                    query.append(" AND TRUNC(").append(key).append(") = TO_DATE('")
                         .append(val.toString()).append("','YYYY-MM-DD')");
                } else {
                    query.append(" AND ").append(key).append(" = ?");
                    params.add(val);
                }
            }
            System.out.println("Executing Query: " + query);

            // Step 3: Execute query
            try (PreparedStatement ps = conn.prepareStatement(query.toString())) {
                int idx = 1;
                for (Object param : params) {
                    ps.setObject(idx++, param);
                }
                try (ResultSet rs = ps.executeQuery()) {
                    // Step 4: Write to Excel (transposed)
                    writeResultSetToExcel(rs, table, config.getOutputCsv());
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

This code performs the following tasks:

1. Purpose of the Method

The method exportRecordData retrieves data from a database (based on a provided table-column mapping and query filter criteria) and exports it to an Excel file. Here's a step-by-step breakdown of the process:

2. Method Parameters

tableKeyMap: A Map<String, Map<String, Object>> that maps table names to another map of key column names and their filter values. This is used to dynamically construct queries for each table based on specific filtering criteria.
config: An instance of a Config object, which provides database connection information (e.g., Schema2Url, Schema2Username, Schema2Password) and the output file path for the exported Excel or CSV file.

3. Steps

Step 1: Database Connection

The method opens a connection to the database using credentials from the config object.

Step 2: Retrieve Column Metadata

For each table in tableKeyMap:

It fetches metadata (COLUMN_NAME and DATA_TYPE) corresponding to the table from the Oracle USER_TAB_COLUMNS system table.
This information is used to create a map (columnTypes) that maps column names to their SQL types (e.g., VARCHAR, DATE, NUMBER). SQL data types are converted to corresponding Java Types using a switch statement.

Step 3: Dynamic Query Construction

The dynamic query starts as SELECT * FROM <table> WHERE 1=1.
For each key-value pair in the keys map of a table:
- A WHERE clause is appended to filter rows based on the column value.
- If the column type is DATE, the code constructs a filter with TRUNC(column) = TO_DATE(...) (to perform date-only comparisons).
- For other types, a parameterized ? is added, and its value is stored in a list (params) for later binding.

An example query for a table EMPLOYEES with filters on EMP_ID=101 and HIRE_DATE=2023-01-01 might look like:

SELECT * FROM EMPLOYEES WHERE 1=1
  AND EMP_ID = ?
  AND TRUNC(HIRE_DATE) = TO_DATE('2023-01-01', 'YYYY-MM-DD')

Step 4: Execute the Query

The constructed query is executed using a PreparedStatement, with the parameters in params being bound sequentially before execution.

Step 5: Export Results

The result of the query (retrieved as a ResultSet) is passed to a helper method writeResultSetToExcel. This method (assumed to be defined elsewhere) handles exporting the data to a transposed Excel or CSV file, likely formatting rows as columns.

4. Error Handling

Exceptions are caught in a try-catch block, and their stack traces are printed.

5. Summary of What the Code Does

Connects to a database.
For each table in the provided tableKeyMap:
- Fetches its column metadata.
- Dynamically constructs and executes a SELECT query with filters based on the mapping.
- Exports the query result to an Excel/CSV file.
Handles different column data types (e.g., DATE, NUMBER, VARCHAR) appropriately.

This method serves as a utility for extracting and exporting filtered database records in a flexible and dynamic manner.

Generate your own explanations

Download our vscode extension

Read other generated explanations

Built by @thebuilderjr