Mark
#1
Automating data archiving is essential to keep databases fast and lightweight. Using Spring Boot with Firebird, you can automatically move old records into archive tables or files without manual work.

1. Understanding the Goal

Data archiving removes outdated records from active tables while keeping them for reference. For example, you can move logs older than 6 months into an archive table to improve performance.

2. Creating an Archive Table

First, define a target table for archived data: 
CREATE TABLE archived_logs AS
SELECT * FROM logs WHERE 1=0;
This creates a new table with the same structure as logs, but empty.

3. Writing the Archiving Logic

Use JdbcTemplate to handle the move and delete operations in a single scheduled task: 
@Component
public class ArchivingService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Scheduled(cron = "0 0 1 * * *") // Run daily at 1 AM
    public void archiveOldLogs() {
        String moveSql = "INSERT INTO archived_logs SELECT * FROM logs WHERE created_at < CURRENT_DATE - 180";
        String deleteSql = "DELETE FROM logs WHERE created_at < CURRENT_DATE - 180";
        jdbcTemplate.update(moveSql);
        jdbcTemplate.update(deleteSql);
        System.out.println("Old logs archived successfully");
    }
}
This job runs automatically every night and moves data older than 180 days.

4. Using Transactions for Safety

Wrap operations in a transaction to ensure consistency: 
@Transactional
public void archiveWithTransaction() {
    jdbcTemplate.update("INSERT INTO archived_logs SELECT * FROM logs WHERE created_at < CURRENT_DATE - 180");
    jdbcTemplate.update("DELETE FROM logs WHERE created_at < CURRENT_DATE - 180");
}
If one query fails, the transaction will roll back automatically.

5. Archiving to External Files

If you prefer to store archives as files, you can export using Firebird’s isql command or a simple file writer in Java: 
try (FileWriter writer = new FileWriter("archived_logs.csv")) {
    jdbcTemplate.query("SELECT * FROM archived_logs", rs -> {
        writer.write(rs.getInt("id") + "," + rs.getString("message") + "\n");
    });
}
This lets you keep historical data outside the main database.

6. Monitoring and Logs

Enable detailed logging to track archiving jobs: 
logging.level.org.springframework.scheduling=INFO
Combine this with email or Slack alerts when a job fails.

7. Best Practices

  • Always test your archiving SQL before automating.
  • Use transactions for critical operations.
  • Schedule during off-peak hours.
  • Monitor disk usage of archived files.
#ads

image quote pre code
Share: