如何为已提取至临时文件的数据库记录的CreationDate字段添加日期

Great question! You're already halfway there—you've got the export logic working, and you've generated the formatted date value you need. The core challenge is ensuring you only update the exact records you extracted to your temp file, and there are two reliable approaches to solve this based on your business requirements:

Approach 1: Batch Update Using the Original Filter (Simple & Efficient) #

If you can guarantee no new records will match status='A' AND ver_status IS NULL between exporting the data and running the update (or if you want to update all current matching records), you can reuse your original query conditions to perform a batch update.

Step 1: Refine Your Date Formatting (Optional but Cleaner) #

First, let's make your date formatting more readable—instead of manually calculating the integer date, use Java's built-in date APIs:

// Java 8+ (recommended)
LocalDate today = LocalDate.now();
// If your CreationDate is a numeric field (like INT storing YYYYMMDD)
int formattedDate = Integer.parseInt(today.format(DateTimeFormatter.ofPattern("yyyyMMdd")));
// If your CreationDate is a DATE type in the database
java.sql.Date sqlDate = java.sql.Date.valueOf(today);

Step 2: Add the Update Logic After Export #

Right after you finish writing to the temp file and closing your result set/statement, execute an UPDATE statement using your original filter:

// After closing your result set and select statement...
String updateSql = "UPDATE database SET CreationDate = ? WHERE status='A' AND ver_status IS NULL";
PreparedStatement updateStmt = db_cn.prepareStatement(updateSql);

// Use this if CreationDate is numeric
updateStmt.setInt(1, formattedDate);
// OR use this if CreationDate is a DATE type
// updateStmt.setDate(1, sqlDate);

int updatedRows = updateStmt.executeUpdate();
updateStmt.close();

Pros: Simple to implement, no extra storage needed.
Cons: Will update any new records that match the filter between export and update.

Approach 2: Track Primary Keys for Precise Updates (Most Reliable) #

If you need to ensure you only update the exact records you exported (critical for high-concurrency environments where new matching records might be added), track the primary keys (or unique identifier combination) of the exported records, then use those keys to target your update.

Step 1: Modify Export to Collect Primary Keys #

Assuming your table uses a unique primary key (e.g., id) or a composite key (like og_id, kz, sap, sap_order), collect these values while exporting:

protected int doWork() throws Exception { 
    String data = ""; 
    File tempFile = File.createTempFile("Test",".txt"); 
    Connection db_cn = currentDataSource.getDbConnection();
    // Enable transaction to ensure export and update are atomic
    db_cn.setAutoCommit(false);

    try {
        // Modify SELECT to include primary key(s)
        String str_sql = "SELECT id, sap, og_id, kz, sap_order FROM database WHERE status='A' AND ver_status IS NULL ORDER BY og_id, kz, sap, sap_order"; 
        Statement select_stmt = db_cn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 
        ResultSet r_set = select_stmt.executeQuery(str_sql); 

        // Collect primary keys in a list
        List<Integer> exportedIds = new ArrayList<>();

        FileWriter fileWriter = new FileWriter(tempFile.getCanonicalPath(), true); 
        BufferedWriter bw = new BufferedWriter(fileWriter); 
        while (r_set.next()) { 
            // Capture the primary key
            int recordId = r_set.getInt("id");
            exportedIds.add(recordId);

            // Write your existing export data
            String sap = r_set.getString("SAP"); 
            String ogId = r_set.getString("OG_ID"); 
            String kz = r_set.getString("KZ"); 
            String order = r_set.getString("SAP_ORDER"); 
            data = sap + "\t" + ogId + "\t" + kz + "\t" + order + "\n"; 
            bw.write(data); 
        } 
        // Clean up export resources
        bw.close(); 
        fileWriter.close(); 
        r_set.close(); 
        select_stmt.close(); 

        // Perform precise update using collected keys
        if (!exportedIds.isEmpty()) {
            // Build IN clause with placeholders
            String placeholders = String.join(",", Collections.nCopies(exportedIds.size(), "?"));
            String updateSql = "UPDATE database SET CreationDate = ? WHERE id IN (" + placeholders + ")";
            PreparedStatement updateStmt = db_cn.prepareStatement(updateSql);

            // Set date parameter
            LocalDate today = LocalDate.now();
            updateStmt.setDate(1, java.sql.Date.valueOf(today));

            // Set primary key parameters
            for (int i = 0; i < exportedIds.size(); i++) {
                updateStmt.setInt(i + 2, exportedIds.get(i));
            }

            int updatedRows = updateStmt.executeUpdate();
            updateStmt.close();
        }

        // Commit transaction if all steps succeed
        db_cn.commit();
    } catch (Exception e) {
        // Roll back if anything fails to avoid partial updates
        db_cn.rollback();
        throw e;
    } finally {
        db_cn.setAutoCommit(true);
        db_cn.close();
    }
    return 0; 
}

If you're using a composite key (no single primary key), adjust the logic to collect the combination of og_id, kz, sap, sap_order and build an update with multiple conditions:

// Example for composite key batch update
String updateSql = "UPDATE database SET CreationDate = ? WHERE og_id = ? AND kz = ? AND sap = ? AND sap_order = ?";
PreparedStatement updateStmt = db_cn.prepareStatement(updateSql);

// Loop through your collected composite key entries
for (CompositeKey key : exportedKeys) {
    updateStmt.setDate(1, java.sql.Date.valueOf(LocalDate.now()));
    updateStmt.setString(2, key.getOgId());
    updateStmt.setString(3, key.getKz());
    updateStmt.setString(4, key.getSap());
    updateStmt.setString(5, key.getSapOrder());
    updateStmt.addBatch();
}

updateStmt.executeBatch();
updateStmt.close();

Pros: Guarantees only exported records are updated, safe for concurrent environments.
Cons: Requires tracking additional data (primary keys) during export.

Key Notes #

• Always use PreparedStatements instead of plain Statements for updates to avoid SQL injection risks.
• Wrap export and update logic in a transaction to ensure data consistency—if either step fails, neither the export nor update will persist.

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