The provided code is a method named `exportRecordSheet` that retrieves...

September 2, 2025 at 03:29 PM

private static void exportRecordSheet(Connection conn, Workbook wb, String tbl, Map<String,String> keys) throws Exception {
       Sheet sh = wb.createSheet(sanitize(tbl));
       if (keys.isEmpty()) {
           sh.createRow(0).createCell(0).setCellValue("No keys for " + tbl);
           return;
       }
       // Get column names
       ResultSet rsCol = conn.getMetaData()
           .getColumns(null, DB_USER2.toUpperCase(), tbl, null);
       List<String> cols = new ArrayList<>();
       while(rsCol.next()) cols.add(rsCol.getString("COLUMN_NAME"));
       Map<String,String> actual = new LinkedHashMap<>();
       keys.forEach((k,v)->{
           cols.stream()
               .filter(c->c.equalsIgnoreCase(k))
               .findFirst()
               .ifPresent(c->actual.put(c,v));
           if(!actual.containsKey(k)) System.out.println("Key " + k + " not in " + tbl);
       });
       if(actual.isEmpty()){
           sh.createRow(0).createCell(0).setCellValue("No matching columns for " + tbl);
           return;
       }
       String sql="SELECT * FROM "+tbl+" WHERE "+
           String.join(" AND ", actual.keySet().stream().map(k->k+"=?").toList());
       System.out.println("SQL:"+sql+" Params:"+actual.values());
       try(PreparedStatement ps=conn.prepareStatement(sql)){
           int i=1;
           //for(String v: actual.values()) ps.setString(i++, v);
		   Map<String, Integer> columnTypes = new HashMap<>();
			ResultSet rsMeta = conn.getMetaData().getColumns(null, DB_USER2.toUpperCase(), tbl, null);
			while (rsMeta.next()) {
			    columnTypes.put(rsMeta.getString("COLUMN_NAME").toUpperCase(), rsMeta.getInt("DATA_TYPE"));
			}
			for (Map.Entry<String, String> entry : actual.entrySet()) {
			    String col = entry.getKey().toUpperCase();
			    String val = entry.getValue();
			    int sqlType = columnTypes.getOrDefault(col, Types.VARCHAR);
			    switch (sqlType) {
			        case Types.INTEGER, Types.NUMERIC -> ps.setInt(i++, Integer.parseInt(val));
			        case Types.BIGINT -> ps.setLong(i++, Long.parseLong(val));
			        case Types.DOUBLE, Types.FLOAT -> ps.setDouble(i++, Double.parseDouble(val));
			        case Types.DATE -> ps.setDate(i++, java.sql.Date.valueOf(val)); // expected: yyyy-mm-dd
			        //case Types.TIMESTAMP -> ps.setTimestamp(i++, Timestamp.valueOf(val)); // expected: yyyy-mm-dd hh:mm:ss[.f...]
					case Types.TIMESTAMP -> {
					   try {
					       val = val.replace("T", " ").replace("Z", ""); // convert ISO to SQL format
					       if (val.length() == 10) val += " 00:00:00";   // add time if missing
					       ps.setTimestamp(i++, Timestamp.valueOf(val));
					   } catch (IllegalArgumentException ex) {
					       System.out.println("Invalid timestamp for column " + col + ": " + val);
					       ps.setTimestamp(i++, null); // or skip / default value
					   }
					}
			        default -> ps.setString(i++, val);
			    }
			}
           try(ResultSet rs=ps.executeQuery()){
               ResultSetMetaData md=rs.getMetaData();
               int r=0;
               while(rs.next()){
                   for(int c=1;c<=md.getColumnCount();c++){
                       Row row=sh.createRow(r++);
                       row.createCell(0).setCellValue(md.getColumnName(c));
                       row.createCell(1).setCellValue(rs.getString(c));
                   }
                   r++;
               }
               if(r==0) sh.createRow(0).createCell(0).setCellValue("No data found");
           }
       }
   }

The provided code is a method named exportRecordSheet that retrieves data from a database table and exports it to a spreadsheet (using the Apache POI library). Here's what the code does, step-by-step:

Key Functionality:

The purpose of this method is to create a new sheet in an Excel workbook (Workbook wb) and populate it with data retrieved from a database table (tbl), filtered by specified key-value conditions (keys).

Step-by-Step Explanation:

Create a new sheet in the workbook:
- A new sheet is created using the Workbook.createSheet method, named after the sanitized value of tbl (where sanitize(tbl) is presumably a method to ensure the table name is safe and valid for a sheet name).
Early exit for empty keys:
- If the keys map is empty, the method writes a message to the first cell of the sheet (indicating no keys are available for the table) and then exits.
Retrieve column names from the database:
- A database metadata query (getColumns) is used to fetch all column names for the specified table (tbl).
- The column names are added to a list called cols.
Filter the keys to match columns:
- For each key provided in the keys map, the method checks if it matches any of the columns (case-insensitive).
- If a matching column is found, the key-value pair is added to a new actual map (used as filtering criteria).
- If a key is provided that doesn't match any column, a warning is printed to the console.
Early exit for no matching columns:
- If none of the provided keys match the table's columns, a message is written to the first cell of the sheet (indicating no matching columns) and the method exits.
Construct and log the SQL query:
- A SELECT query is constructed based on the table name and the filtered columns (actual map).
- Parameters in the SQL query are represented as ?, and the query is logged to the console along with the parameter values.
Prepare the SQL query with parameters:
- A PreparedStatement is created, and the values in the actual map are bound to the query's parameters.
- A Map of column data types (columnTypes) is created by querying database metadata again, ensuring correct type-specific binding for each parameter:
  - Numeric types (INTEGER, BIGINT, DOUBLE, etc.) are parsed and bound accordingly.
  - Date and Timestamp types are expected in specific formats (yyyy-mm-dd for dates and yyyy-mm-dd hh:mm:ss for timestamps).
  - If a timestamp is in ISO 8601 format (e.g., yyyy-mm-ddThh:mm:ssZ), adjustments are made to convert it to SQL's expected format.
  - Defaults to binding the value as a string for other data types.
Execute the query and populate the spreadsheet:
- The PreparedStatement is executed, and the resulting ResultSet (containing rows from the database) is iterated over:
  - Column names are written to column 0 of each row in the Excel sheet.
  - Row data is added to the corresponding columns (one cell per value).
- If the result set is empty (no rows found), a message is written to the first cell of the sheet (indicating no data was found).
Handle exceptions:
- The method declares throws Exception, meaning it will propagate any exceptions that occur (e.g., SQL errors, number format issues, etc.).

Summary:

This method queries data from a database table (tbl) based on filtering criteria provided as key-value pairs (keys). It validates the keys, fetches matching rows, and writes the columns and their values to an Excel sheet in a POI Workbook. If no matching keys or rows are found, appropriate messages are written to the Excel sheet instead. The code also handles type-sensitive SQL parameter binding based on column metadata.

Generate your own explanations

Download our vscode extension

Read other generated explanations

Built by @thebuilderjr