Analysis and Solution for Duplicate Database Query Results in Java JDBC

Problem Background and Phenomenon Analysis

In Java web development, it is common to read data from databases and display it on HTML pages. A frequent issue occurs when developers attempt to store database query results in an ArrayList, only to find that the page repeatedly displays the last record instead of all records. This phenomenon typically happens during JDBC database operations, especially when developers reuse the same object reference.

Error Code Analysis

The original problematic code contains a critical flaw: CustomerDTO customer = null is initialized outside the while loop, and the same object reference is reused for property assignments inside the loop. This implementation causes all ArrayList elements to point to the same memory address, ultimately resulting in all element values being overwritten by the last iteration's results.

// Erroneous implementation example
ArrayList<CustomerDTO> customers = new ArrayList<CustomerDTO>();
CustomerDTO customer = null; // Declared outside loop

try {
    Connection c = openConnection();
    Statement statement = c.createStatement();
    ResultSet rs = statement.executeQuery("SELECT * FROM customer");
    
    while (rs.next()) {
        // Error: Reusing same object reference
        customer.setId(rs.getInt("id"));
        customer.setName(rs.getString("name"));
        // ... Other property assignments
        customers.add(customer); // All elements point to same object
    }
} catch (Exception e) {
    System.out.println(e);
}

Correct Implementation Solution

The key to resolving this issue lies in creating new object instances during each loop iteration. Below is the correct implementation based on the best answer:

public static ArrayList<Customer> getAllCustomer() throws ClassNotFoundException, SQLException {
    Connection conn = DBConnection.getDBConnection().getConnection();
    Statement stm = conn.createStatement();
    String sql = "Select * From Customer";
    ResultSet rst = stm.executeQuery(sql);
    
    ArrayList<Customer> customerList = new ArrayList<>();
    
    while (rst.next()) {
        // Correct: Create new object each iteration
        Customer customer = new Customer(
            rst.getString("id"),
            rst.getString("name"),
            rst.getString("address"),
            rst.getDouble("salary")
        );
        customerList.add(customer);
    }
    
    return customerList;
}

Data Model Design

A proper data model class should include all necessary properties and access methods:

public class Customer {
    private String id;
    private String name;
    private String address;
    private String salary;
    
    // Constructor
    public Customer(String id, String name, String address, String salary) {
        this.id = id;
        this.name = name;
        this.address = address;
        this.salary = salary;
    }
    
    // Getter and Setter methods
    public String getId() { return id; }
    public void setId(String id) { this.id = id; }
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    public String getAddress() { return address; }
    public void setAddress(String address) { this.address = address; }
    public String getSalary() { return salary; }
    public void setSalary(String salary) { this.salary = salary; }
}

Frontend Data Display

In Swing or Java web applications, data can be displayed in tables using the following approach:

private void reloadButtonActionPerformed(java.awt.event.ActionEvent evt) {
    try {
        ArrayList<Customer> customerList = CustomerController.getAllCustomer();
        DefaultTableModel tableModel = (DefaultTableModel) customerTable.getModel();
        tableModel.setRowCount(0); // Clear existing data
        
        for (Customer customer : customerList) {
            Object[] rowData = {
                customer.getId(),
                customer.getName(), 
                customer.getAddress(),
                customer.getSalary()
            };
            tableModel.addRow(rowData);
        }
    } catch (Exception ex) {
        Logger.getLogger(ViewCustomerForm.class.getName()).log(Level.SEVERE, null, ex);
    }
}

Performance Optimization Considerations

When handling large datasets (such as the 400,000 records mentioned in the reference article), the following optimization strategies should be considered:

SQL Query Optimization: Optimize query statements at the database level, use EXPLAIN PLAN to analyze execution plans, properly utilize indexes, and avoid unnecessary table joins. For complex queries, consider creating database views to encapsulate common query logic.

Connection Management: Use connection pools to manage database connections, avoiding the overhead of frequent connection creation and closure. Ensure proper closure of ResultSet, Statement, and Connection resources in finally blocks.

Data Filtering: Perform data filtering at the database level whenever possible, retrieving only the required data rather than loading all data into memory for subsequent filtering.

Caching Mechanisms: For infrequently changing data, consider implementing caching mechanisms. However, pay attention to cache consistency and memory usage, particularly in multi-user environments.

Best Practices Summary

1. Create new objects in loops: Ensure new object instances are created during each iteration to avoid reference reuse issues.

2. Use PreparedStatement: For parameterized queries, use PreparedStatement to improve performance and prevent SQL injection.

3. Resource management: Use try-with-resources statements or ensure proper closure of all database resources in finally blocks.

4. Exception handling: Implement appropriate exception handling mechanisms with detailed error logging for debugging.

5. Pagination handling: For large datasets, consider implementing pagination mechanisms to avoid loading all data at once.