Last active
April 24, 2022 06:11
-
-
Save abhilater/8cf7482354381fba1acc21049bc8a2c0 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment