· 6 years ago · May 10, 2019, 06:06 PM
1package database;
2import java.sql.Connection;
3import java.sql.DatabaseMetaData;
4import java.sql.Date;
5import java.sql.DriverManager;
6import java.sql.ResultSet;
7import java.sql.SQLException;
8import java.sql.Statement;
9import java.util.ArrayList;
10import java.util.List;
11import java.text.DateFormat;
12
13import javax.xml.crypto.Data;
14
15import it.polito.latazza.data.Beverage;
16import it.polito.latazza.data.Employee;
17
18public class InterfaceDB {
19
20 private static Connection conn;
21 private static String sql;
22 private static Statement statement;
23
24 public static void createNewDatabase() {
25
26 String url = "jdbc:sqlite:latazza.db";
27 statement = null;
28
29 try (Connection conn = DriverManager.getConnection(url)) {
30 if (conn != null) {
31 DatabaseMetaData meta = conn.getMetaData();
32 System.out.println("The driver name is " + meta.getDriverName());
33 System.out.println("A new database has been created.");
34
35 statement=conn.createStatement();
36 sql = "CREATE TABLE IF NOT EXISTS EMPLOYEE" +
37 "(ID INTEGER NOT NULL PRIMARY KEY,"+
38 "NAME TEXT NOT NULL,"+
39 "SURNAME TEXT NOT NULL,"+
40 "BALANCE INT NOT NULL)";
41 statement.executeUpdate(sql);
42 statement.close();
43
44
45
46
47 statement=conn.createStatement();
48 sql = "CREATE TABLE IF NOT EXISTS LOGS" +
49 "(ID INT PRIMARY KEY,"+
50 "D DATE NOT NULL,"+
51 "RECHARGE INT NOT NULL,"+
52 "BUY INT NOT NULL,"+
53 "VISITOR INT NOT NULL,"+
54 "CB INT NOT NULL,"+
55 "IDEMP INT NOT NULL,"+
56 "IDBEV INT NOT NULL,"+
57 "AMQU NOT NULL)";
58 statement.executeUpdate(sql);
59 statement.close();
60
61 statement=conn.createStatement();
62 sql = "CREATE TABLE IF NOT EXISTS LATAZZAACCOUNT" +
63 "(ID INT PRIMARY KEY NOT NULL, BALANCE INT)";
64 statement.executeUpdate(sql);
65 statement.close();
66
67 statement=conn.createStatement();
68 sql = "CREATE TABLE IF NOT EXISTS BEVERAGE" +
69 "(ID INT PRIMARY KEY,"+
70 "NAME TEXT NOT NULL,"+
71 "PRICE INT NOT NULL,"+
72 "CAPSULES_BOX INT NOT NULL,"+
73 "BOX_PRICE INT NOT NULL,"+
74 "QUANTITY INT NOT NULL)";
75 statement.executeUpdate(sql);
76 statement.close();
77
78
79
80
81
82 }
83
84 } catch (SQLException e) {
85 System.out.println(e.getMessage());
86 }
87 }
88
89 public static void connect() {
90 conn = null;
91 try {
92 // db parameters
93 String url = "jdbc:sqlite:latazza.db";
94 // create a connection to the database
95 conn = DriverManager.getConnection(url);
96
97 System.out.println("Connection to SQLite has been established.");
98
99 } catch (SQLException e) {
100 System.out.println(e.getMessage());
101 } /*finally {
102 try {
103 if (conn != null) {
104 conn.close();
105 }
106 } catch (SQLException ex) {
107 System.out.println(ex.getMessage());
108 }
109 }*/
110 }
111
112 public static List<Employee> getEmployees()
113 {
114 List<Employee> employees = new ArrayList<Employee>();
115
116 try
117 {
118 statement=conn.createStatement();
119 sql="SELECT * FROM EMPLOYEE";
120 ResultSet rs = statement.executeQuery(sql);
121
122 while(rs.next())
123 {
124 Employee employee = new Employee(rs.getInt("ID"),rs.getString("NAME"),rs.getString("SURNAME"),rs.getInt("BALANCE"));
125 employees.add(employee);
126 Employee.setLast(rs.getInt("ID"));
127 }
128
129 statement.close();
130
131 }catch (SQLException e)
132 {
133 System.out.println(e.getMessage());
134 }
135 return employees;
136 }
137
138 public static void insertEmployee(Integer id,String name, String surname, Integer balance)
139 {
140 try
141 {
142 statement=conn.createStatement();
143 sql="INSERT INTO EMPLOYEE (ID,NAME,SURNAME,BALANCE) "+"VALUES("+id+",'"+name+"','"+surname+"',"+balance+");";
144 statement.executeUpdate(sql);
145 statement.close();
146
147 }catch (SQLException e)
148 {
149 System.out.println(e.getMessage());
150 }
151
152
153 }
154
155 public static void updateEmployee(Integer id, String name, String surname, Integer balance)
156 {
157 try
158 {
159 statement=conn.createStatement();
160 sql="UPDATE EMPLOYEE SET NAME='"+name+"',SURNAME='"+surname+"',BALANCE="+balance+" WHERE ID="+id+";";
161 statement.executeUpdate(sql);
162 statement.close();
163
164
165 }catch (SQLException e)
166 {
167 System.out.println(e.getMessage());
168 }
169
170 }
171
172
173 public static void insertBeverage(Integer id,Integer quantity,Integer price, Integer capsulesPerBox, Integer boxPrice, String name)
174 {
175 try
176 {
177 Statement statement=conn.createStatement();
178 String sql="INSERT INTO BEVERAGE (ID,NAME,PRICE,CAPSULES_BOX,BOX_PRICE,QUANTITY)"
179 +"VALUES("+id+",'" +name+"',"+price+","+capsulesPerBox+","+boxPrice+","+quantity+");";
180 //statement.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
181 statement.executeUpdate(sql);
182 statement.close();
183
184 }catch (SQLException e)
185 {
186 System.out.println(e.getMessage());
187 }
188
189 }
190
191 public static void updateBeverage(Integer id, Integer capsulesPerBox, Integer boxPrice, String name,Integer quantity)
192 {
193 try
194 {
195 Statement statement=conn.createStatement();
196 String sql="UPDATE BEVERAGE SET NAME = '"+name+"', CAPSULES_BOX = "+capsulesPerBox+", BOX_PRICE = "+boxPrice+", QUANTITY = "+quantity+" WHERE ID = "+id+";";
197 statement.executeUpdate(sql);
198 statement.close();
199
200 }catch (SQLException e)
201 {
202 System.out.println(e.getMessage());
203 }
204
205 }
206
207 public static List<Beverage> getBeverages() {
208 List<Beverage> beverages = new ArrayList<Beverage>();
209 String sql="SELECT * FROM BEVERAGE";
210 Statement statement = null;
211 try{
212
213
214 statement=conn.createStatement();
215 ResultSet rs = statement.executeQuery(sql);
216 while(rs.next())
217 {
218 Beverage b = new Beverage(rs.getInt("ID"),rs.getInt("PRICE"),rs.getInt("QUANTITY"),rs.getInt("CAPSULES_BOX"),rs.getInt("BOX_PRICE"),rs.getString("NAME"));
219 beverages.add(b);
220 Beverage.setLast(rs.getInt("ID"));
221 }
222 statement.close();
223
224 } catch (SQLException e) {
225 System.out.println(e.getMessage());
226 }
227
228 return beverages;
229 }
230
231 public static Integer getBalance() {
232
233
234
235 Statement statement;
236 Statement statement1;
237 Integer balance=0;
238
239 try {
240 statement=conn.createStatement();
241 sql="INSERT INTO LATAZZAACCOUNT (ID, BALANCE) "
242 +"VALUES(0,0);";
243 statement.executeUpdate(sql);
244 statement.close();
245 } catch (SQLException e) {
246 System.out.println("RIGA GIA' PRESENTE");
247 System.out.println(e.getMessage());
248
249 sql="SELECT * FROM LATAZZAACCOUNT";
250 ResultSet rs;
251 try {
252 statement1=conn.createStatement();
253 rs = statement1.executeQuery(sql);
254 while(rs.next())
255 {
256
257 balance=rs.getInt("BALANCE");
258 }
259 statement1.close();
260
261 } catch (SQLException e1) {
262 // TODO Auto-generated catch block
263 e1.printStackTrace();
264 }
265
266
267
268 }
269 return balance;
270
271 }
272
273 public static void updateAccount(Integer amount) {
274
275 String sql;
276 Statement statement;
277
278 try {
279 statement=conn.createStatement();
280 sql="UPDATE LATAZZAACCOUNT SET BALANCE="+amount+" WHERE ID=0;";
281 statement.executeUpdate(sql);
282 statement.close();
283 } catch (SQLException e) {
284 System.out.println(e.getMessage());
285 }
286
287 }
288
289 public static void logBox(Integer id, java.util.Date date,Integer idb,Integer qnt)
290 {
291 try
292 {
293 Statement statement=conn.createStatement();
294 String sql="INSERT INTO LOGS (ID,D,RECHARGE,BUY,VISITOR,CB,IDEMP,IDBEV,AMQU)"
295 +"VALUES("+id+",'" +date+"',0,1,0,0,0,"+idb+"," + qnt + ");";
296 //statement.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
297 statement.executeUpdate(sql);
298 statement.close();
299
300 }catch (SQLException e)
301 {
302 System.out.println(e.getMessage());
303 }
304
305 }
306
307
308 public static void logRecharge(Integer id, java.util.Date date,Integer amount,Integer idEmp)
309
310
311 {
312 try
313 {
314 Statement statement=conn.createStatement();
315 String sql="INSERT INTO LOGS (ID,D,RECHARGE,BUY,VISITOR,CB,IDEMP,IDBEV,AMQU)"
316 +"VALUES("+id+",'" +date+"',1,0,0,0,"+idEmp+",0," + amount + ");";
317 //statement.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
318 statement.executeUpdate(sql);
319 statement.close();
320
321 }catch (SQLException e)
322 {
323 System.out.println(e.getMessage());
324 }
325
326 }
327
328
329public static void logEmployee(Integer id, java.util.Date date,Integer idEm,Integer idBe, Integer qnt, Integer m)
330
331
332 {
333 try
334 {
335 Statement statement=conn.createStatement();
336 String sql="INSERT INTO LOGS (ID,D,RECHARGE,BUY,VISITOR,CB,IDEMP,IDBEV,AMQU)"
337 +"VALUES("+id+",'" +date+"',0,0,0,"+m+","+idEm+","+idBe+"," + qnt + ");";
338 //statement.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
339 statement.executeUpdate(sql);
340 statement.close();
341
342 }catch (SQLException e)
343 {
344 System.out.println(e.getMessage());
345 }
346
347 }
348
349public static void logVisitor(Integer id, java.util.Date date,Integer idBe, Integer qnt)
350
351
352{
353 try
354 {
355 Statement statement=conn.createStatement();
356 String sql="INSERT INTO LOGS (ID,D,RECHARGE,BUY,VISITOR,CB,IDEMP,IDBEV,AMQU)"
357 +"VALUES("+id+",'" +date+"',0,0,1,0,0,"+idBe+"," + qnt + ");";
358 //statement.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
359 statement.executeUpdate(sql);
360 statement.close();
361
362 }catch (SQLException e)
363 {
364 System.out.println(e.getMessage());
365 }
366
367}
368
369
370
371}