-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathProductDAO.java
More file actions
227 lines (197 loc) · 7.95 KB
/
ProductDAO.java
File metadata and controls
227 lines (197 loc) · 7.95 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
package services;
import DB.DBConnection;
import model.Product;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* Data Access Object for Product entity.
* Handles all database operations related to products.
*/
public class ProductDAO extends BaseDAO {
// SQL Queries
private static final String CHECK_PRODUCT_EXISTS = "SELECT COUNT(*) FROM product WHERE productId = ?";
private static final String INSERT_PRODUCT = "INSERT INTO product (productId, name, category, originalPrice, salePrice, priceByUnit, priceByCarton, quantity) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
private static final String SELECT_ALL_PRODUCTS = "SELECT productId, name, category, originalPrice, salePrice, priceByUnit, priceByCarton, quantity FROM product";
private static final String UPDATE_PRODUCT_QUANTITY = "UPDATE product SET quantity = quantity - ? WHERE productId = ?";
private static final String SELECT_PRODUCT_BY_ID = "SELECT productId, name, category, originalPrice, salePrice, priceByUnit, priceByCarton, quantity FROM product WHERE productId = ?";
private static final String UPDATE_PRODUCT = "UPDATE product SET name = ?, category = ?, originalPrice = ?, salePrice = ?, priceByUnit = ?, priceByCarton = ?, quantity = ? WHERE productId = ?";
private static final String DELETE_PRODUCT = "DELETE FROM product WHERE productId = ?";
/**
* Add a new product to the database
*
* @param product the product to add
* @return success message or error message
*/
public String addProduct(Product product) {
if (product == null) {
return "Error: Product cannot be null";
}
// Check if product already exists
if (exists(CHECK_PRODUCT_EXISTS, new Object[] { product.getProductId() })) {
return "Product already exists, cannot add duplicate";
}
// Insert the product
Object[] params = {
product.getProductId(),
product.getName(),
product.getCategory(),
product.getOriginalPrice(),
product.getSalePrice(),
product.getPriceByUnit(),
product.getPriceByCarton(),
product.getQuantity()
};
int rowsAffected = executeUpdate(INSERT_PRODUCT, params);
if (rowsAffected > 0) {
System.out.println("Product added successfully: " + product.getProductId());
return SUCCESS_MESSAGE;
} else {
return ERROR_MESSAGE;
}
}
/**
* Get all products from the database
*
* @return list of all products
*/
public List<Product> getAllProducts() {
return executeQuery(SELECT_ALL_PRODUCTS, null, this::mapResultSetToProduct);
}
/**
* Get a product by its ID
*
* @param productId the product ID to search for
* @return the product if found, null otherwise
*/
public Product getProductById(String productId) {
List<Product> products = executeQuery(SELECT_PRODUCT_BY_ID, new Object[] { productId },
this::mapResultSetToProduct);
return products.isEmpty() ? null : products.get(0);
}
/**
* Update product quantities after a sale
*
* @param soldProducts map of products and their sold quantities
* @return true if update was successful, false otherwise
*/
public boolean updateProductQuantities(Map<Product, Integer> soldProducts) {
if (soldProducts == null || soldProducts.isEmpty()) {
return true; // Nothing to update
}
List<Object[]> batchParams = new ArrayList<>();
for (Map.Entry<Product, Integer> entry : soldProducts.entrySet()) {
Product product = entry.getKey();
Integer soldQuantity = entry.getValue();
if (product != null && soldQuantity != null && soldQuantity > 0) {
batchParams.add(new Object[] { soldQuantity, product.getProductId() });
}
}
if (batchParams.isEmpty()) {
return true;
}
int[] results = executeBatchUpdate(UPDATE_PRODUCT_QUANTITY, batchParams);
// Check if all updates were successful
for (int result : results) {
if (result <= 0) {
return false;
}
}
return true;
}
/**
* Update an existing product
*
* @param product the product to update
* @return true if update was successful, false otherwise
*/
public boolean updateProduct(Product product) {
if (product == null) {
return false;
}
Object[] params = {
product.getName(),
product.getCategory(),
product.getOriginalPrice(),
product.getSalePrice(),
product.getPriceByUnit(),
product.getPriceByCarton(),
product.getQuantity(),
product.getProductId()
};
int rowsAffected = executeUpdate(UPDATE_PRODUCT, params);
return rowsAffected > 0;
}
/**
* Delete a product by its ID
*
* @param productId the product ID to delete
* @return true if deletion was successful, false otherwise
*/
public boolean deleteProduct(String productId) {
if (productId == null || productId.trim().isEmpty()) {
return false;
}
int rowsAffected = executeUpdate(DELETE_PRODUCT, new Object[] { productId });
return rowsAffected > 0;
}
/**
* Search products by name (case-insensitive partial match)
*
* @param searchTerm the search term
* @return list of matching products
*/
public List<Product> searchProductsByName(String searchTerm) {
if (searchTerm == null || searchTerm.trim().isEmpty()) {
return new ArrayList<>();
}
String searchQuery = "SELECT productId, name, category, originalPrice, salePrice, priceByUnit, priceByCarton, quantity FROM product WHERE LOWER(name) LIKE LOWER(?)";
return executeQuery(searchQuery, new Object[] { "%" + searchTerm + "%" }, this::mapResultSetToProduct);
}
/**
* Get products by category
*
* @param category the category to filter by
* @return list of products in the specified category
*/
public List<Product> getProductsByCategory(String category) {
if (category == null || category.trim().isEmpty()) {
return new ArrayList<>();
}
String categoryQuery = "SELECT productId, name, category, originalPrice, salePrice, priceByUnit, priceByCarton, quantity FROM product WHERE category = ?";
return executeQuery(categoryQuery, new Object[] { category }, this::mapResultSetToProduct);
}
/**
* Get low stock products (quantity less than threshold)
*
* @param threshold the quantity threshold
* @return list of products with low stock
*/
public List<Product> getLowStockProducts(int threshold) {
String lowStockQuery = "SELECT productId, name, category, originalPrice, salePrice, priceByUnit, priceByCarton, quantity FROM product WHERE quantity < ?";
return executeQuery(lowStockQuery, new Object[] { threshold }, this::mapResultSetToProduct);
}
/**
* Map ResultSet row to Product object
*
* @param rs the ResultSet
* @return Product object
*/
private Product mapResultSetToProduct(ResultSet rs) {
try {
return new Product(
rs.getString("productId"),
rs.getString("name"),
rs.getString("category"),
rs.getDouble("originalPrice"),
rs.getDouble("salePrice"),
rs.getDouble("priceByUnit"),
rs.getDouble("priceByCarton"),
rs.getInt("quantity"));
} catch (SQLException e) {
logError("Error mapping ResultSet to Product", e);
return null;
}
}
}