Snippet1.java

public class CartRepository {

    private static Properties dbConnProps;

    public static List<Cart> getCartItems(String userId) throws SQLException {
        List<Cart> records = new ArrayList<>();
        
        Connection connection = DriverManager.getConnection(dbConnProps.getProperty("url"),
                dbConnProps.getProperty("user"),
                dbConnProps.getProperty("password"));
        PreparedStatement stmt = connection.prepareStatement("SELECT * FROM carts WHERE owner_id = ?");
        stmt.setString(1, userId);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            records.add(new Cart(rs));
        }
        return records;
    }

    public static int deleteCartItem(String itemId) throws SQLException {
        Connection connection = DriverManager.getConnection(dbConnProps.getProperty("url"),
                dbConnProps.getProperty("user"),
                dbConnProps.getProperty("password"));
        PreparedStatement stmt = connection.prepareStatement("DELETE FROM carts WHERE item_id = ?");
        stmt.setString(1, itemId);
        return stmt.executeUpdate();
    }

    static class Cart {
        String itemId;
        String productName;
        String productLink;
        int qty;
        float unitPrice;
      
        public Cart(ResultSet rs) throws SQLException {
            this.itemId = rs.getString("item_id");
            this.productName = rs.getString("product_name");
            this.productLink = rs.getString("product_link");
            this.qty = rs.getInt("qty");
            this.unitPrice = rs.getFloat("unit_price");
        }
    }
}


...
...
public static List<Cart> getCartItems(String userId) throws SQLException {
        List<Cart> records = new ArrayList<>();
        try (Connection connection = DriverManager.getConnection(dbConnProps.getProperty("url"),
                dbConnProps.getProperty("user"),
                dbConnProps.getProperty("password"));
             PreparedStatement stmt = connection.prepareStatement("SELECT * FROM carts WHERE owner_id = ?")) {
            stmt.setString(1, userId);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    records.add(new Cart(rs));
                }
            }
        }
        return records;
    }

...
...
    
public class CartRepository {

    // You probably have some means of injecting / discovering
    // a JDBC DataSource
    DataSource ds;

    public static List<Cart> getCartItems(String userId) throws SQLException {
        List<Cart> records = new ArrayList<>();
        // DataSource will automatically acquire and
        // close the JDBC Connection for you, so the last remaining
        // resource has also disappeared from your client code.
        for (Record record : DSL.using(ds, SQLDialect.PostgreSQL)
                .fetch("SELECT * FROM carts WHERE owner_id = ?", List.of(userId)) {
            // map Record to Cart
            records.add(mapRecordToCart(record));
        }
        return records;
    }