-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathCartDAO.java
More file actions
168 lines (158 loc) · 5.39 KB
/
Copy pathCartDAO.java
File metadata and controls
168 lines (158 loc) · 5.39 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
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package Model;
import data.CartDTO;
import Utils.DBUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
*
* @author thien
*/
public class CartDAO {
public List<CartDTO> list(int customerID) throws SQLException {
ArrayList<CartDTO> list;
list = new ArrayList<CartDTO>();
Connection conn = null;
PreparedStatement stm = null;
ResultSet rs = null;
try {
conn = DBUtils.getConnection();
String sql = "SELECT c.customer_id, p.id AS product_id, p.name AS product_name, p.image_link AS product_image_link,\n"
+ " p.description AS product_description, p.price AS product_price, c.quantity AS cart_quantity,\n"
+ " p.quantity AS product_quantity\n"
+ "FROM [dbo].[tblCart_Item] c\n"
+ "JOIN [dbo].[tblProduct] p ON c.product_id = p.id where customer_id = ?";
stm = conn.prepareStatement(sql);
stm.setInt(1, customerID);
rs = stm.executeQuery();
while (rs.next()) {
list.add(new CartDTO(customerID, rs.getInt("product_id"), rs.getString("product_name"), rs.getInt("cart_quantity"), rs.getString("product_image_link"), rs.getString("product_description"), rs.getFloat("product_price"), rs.getInt("product_quantity")));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
rs.close();
}
if (stm != null) {
stm.close();
}
if (conn != null) {
conn.close();
}
}
return list;
}
public int cartSize(int id) throws SQLException {
return list(id).size();
}
public Long add(CartDTO newCart) throws SQLException {
int value = 0;
ArrayList<CartDTO> list;
list = new ArrayList<CartDTO>();
Connection conn = null;
PreparedStatement stm = null;
ResultSet rs = null;
try {
conn = DBUtils.getConnection();
String sql = "INSERT INTO tblCart_Item (product_id, customer_id, quantity) VALUES"
+ " (? , ? , ?); ";
stm = conn.prepareStatement(sql);
stm.setInt(1, newCart.getProductID());
stm.setInt(2, newCart.getCustomerID());
stm.setInt(3, newCart.getQuantity());
value = stm.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
rs.close();
}
if (stm != null) {
stm.close();
}
if (conn != null) {
conn.close();
}
}
return Long.valueOf(value);
}
public void delete(CartDTO oldCart) throws SQLException {
int value = 0;
Connection conn = null;
PreparedStatement stm = null;
ResultSet rs = null;
try {
conn = DBUtils.getConnection();
String sql = "DELETE FROM tblCart_Item "
+ " where product_id = ? AND customer_id = ? ";
stm = conn.prepareStatement(sql);
stm.setInt(1, oldCart.getProductID());
stm.setInt(2, oldCart.getCustomerID());
value = stm.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
rs.close();
}
if (stm != null) {
stm.close();
}
if (conn != null) {
conn.close();
}
}
}
public void Update(CartDTO newCart) throws SQLException {
int value = 0;
Connection conn = null;
PreparedStatement stm = null;
ResultSet rs = null;
try {
conn = DBUtils.getConnection();
String sql = "UPDATE tblCart_Item SET "
+ " quantity = ? WHERE product_id = ? AND customer_id = ?;";
stm = conn.prepareStatement(sql);
stm.setInt(2, newCart.getProductID());
stm.setInt(3, newCart.getCustomerID());
stm.setInt(1, newCart.getQuantity());
value = stm.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
rs.close();
}
if (stm != null) {
stm.close();
}
if (conn != null) {
conn.close();
}
}
}
public static void main(String[] args) throws SQLException {
CartDAO dao = new CartDAO();
// CartDTO newCart = new CartDTO(9, 3, 5);
// System.out.println(dao.list(4));
//// Long rs = dao.add(newCart);
//// if (rs == 0) {
//// System.out.println("0");
//// } else {
//// System.out.println("1");
//// }
// dao.Update(newCart);
for (CartDTO cart : dao.list(6)) {
System.out.println(cart);
}
}
}