· 8 years ago · Jul 17, 2018, 05:34 AM
1package dataaccess;
2
3import datatypes.Customer;
4import datatypes.Treatment;
5import java.io.PrintStream;
6import java.sql.Connection;
7import java.sql.DriverManager;
8import java.sql.PreparedStatement;
9import java.sql.ResultSet;
10import java.sql.SQLException;
11import java.sql.Statement;
12
13public final class MySQLDataAccess
14{
15 private PreparedStatement addDataToCustomersTable;
16 private PreparedStatement queryDataFromCustomersTable;
17 private PreparedStatement addDataToTreatmentsTable;
18 private PreparedStatement queryDataFromTreatmentsTableByCustID;
19 private PreparedStatement queryIDFromCustomerTable;
20 private PreparedStatement updateCustomerTableUsingID;
21 private PreparedStatement updateTreatmentsTableUsingID;
22 private PreparedStatement queryLastId;
23 private Connection conn;
24
25 private MySQLDataAccess(String url, String dbName, String dbPass)
26 throws SQLException
27 {
28 if (url == null)
29 {
30 throw new IllegalArgumentException("url cannot be null");
31 }
32
33 if (dbName == null)
34 {
35 throw new IllegalArgumentException("dbName cannot be null");
36 }
37
38 if (dbPass == null)
39 {
40 throw new IllegalArgumentException("dbPass cannot be null");
41 }
42
43 this.conn = DriverManager.getConnection(url, dbName, dbPass);
44 try
45 {
46 String adtct = "INSERT INTO customers(CustFirstName,CustLastName, CustTelephone, CustCellphone, CustEmail, CustSkinType) VALUES (?, ?, ?, ?, ?, ?)";
47
48 this.addDataToCustomersTable = this.conn.prepareStatement("INSERT INTO customers(CustFirstName,CustLastName, CustTelephone, CustCellphone, CustEmail, CustSkinType) VALUES (?, ?, ?, ?, ?, ?)");
49
50 String qdfct = "SELECT * FROM customers WHERE (CustFirstName LIKE ? AND CustLastName LIKE ?)";
51
52 this.queryDataFromCustomersTable = this.conn.prepareStatement("SELECT * FROM customers WHERE (CustFirstName LIKE ? AND CustLastName LIKE ?)");
53
54 String qidfct = "SELECT CustID FROM customers WHERE (CustFirstName = ? AND CustLastName = ? AND CustTelephone = ? AND CustCellphone = ? AND CustEmail = ? AND CustSkinType = ?)";
55
56 this.queryIDFromCustomerTable = this.conn.prepareStatement("SELECT CustID FROM customers WHERE (CustFirstName = ? AND CustLastName = ? AND CustTelephone = ? AND CustCellphone = ? AND CustEmail = ? AND CustSkinType = ?)");
57
58 String adttt = "INSERT INTO treatments(CustID, TreatmentDate,TreatmentArea, TreatmentPulseWidth, TreatmentSpotSize,TreatmentEnergy, TreatmentAmount, TreatmentComments) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
59
60 this.addDataToTreatmentsTable = this.conn.prepareStatement("INSERT INTO treatments(CustID, TreatmentDate,TreatmentArea, TreatmentPulseWidth, TreatmentSpotSize,TreatmentEnergy, TreatmentAmount, TreatmentComments) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
61
62 String uctui = "UPDATE customers SET CustFirstName = ?, CustLastName = ?, CustTelephone = ?, CustCellphone = ?, CustEmail = ?, CustSkinType = ? WHERE CustID = ?";
63
64 this.updateCustomerTableUsingID = this.conn.prepareStatement("UPDATE customers SET CustFirstName = ?, CustLastName = ?, CustTelephone = ?, CustCellphone = ?, CustEmail = ?, CustSkinType = ? WHERE CustID = ?");
65
66 String qdfttbci = "SELECT TreatmentID, TreatmentDate,TreatmentArea, TreatmentPulseWidth,TreatmentSpotSize, TreatmentEnergy, TreatmentAmount,TreatmentComments FROM treatments WHERE CustID = ?";
67
68 this.queryDataFromTreatmentsTableByCustID = this.conn.prepareStatement("SELECT TreatmentID, TreatmentDate,TreatmentArea, TreatmentPulseWidth,TreatmentSpotSize, TreatmentEnergy, TreatmentAmount,TreatmentComments FROM treatments WHERE CustID = ?");
69
70 String uttui = "UPDATE treatments SET TreatmentDate = ?, TreatmentArea = ?, TreatmentPulseWidth = ?, TreatmentSpotSize = ?, TreatmentEnergy = ?, TreatmentAmount = ?, TreatmentComments = ? WHERE TreatmentID = ?";
71
72 this.updateTreatmentsTableUsingID = this.conn.prepareStatement("UPDATE treatments SET TreatmentDate = ?, TreatmentArea = ?, TreatmentPulseWidth = ?, TreatmentSpotSize = ?, TreatmentEnergy = ?, TreatmentAmount = ?, TreatmentComments = ? WHERE TreatmentID = ?");
73
74 String qli = "SELECT LAST_INSERT_ID()";
75 this.queryLastId = this.conn.prepareStatement("SELECT LAST_INSERT_ID()");
76 }
77 catch (SQLException ex)
78 {
79 System.out.println("SQL Error: " + ex.getMessage());
80 }
81 }
82
83 public static MySQLDataAccess connect(String host, String dbName, String dbUser, String dbPass)
84 throws ClassNotFoundException, SQLException
85 {
86 if (host == null)
87 {
88 throw new IllegalArgumentException("host cannot be null");
89 }
90
91 if (dbName == null)
92 {
93 throw new IllegalArgumentException("dbname cannot be null");
94 }
95
96 if (dbUser == null)
97 {
98 throw new IllegalArgumentException("dbuser cannot be null");
99 }
100
101 if (dbPass == null)
102 {
103 throw new IllegalArgumentException("dbpass cannot be null");
104 }
105
106 Class.forName("com.mysql.jdbc.Driver");
107
108 String url = "jdbc:mysql://" + host + "/" + dbName;
109
110 MySQLDataAccess access = new MySQLDataAccess(url, dbUser, dbPass);
111
112 return access;
113 }
114
115 public Connection getConnectionObject()
116 {
117 return this.conn;
118 }
119
120 public void InsertIntoCustomerTable(String firstName, String lastName, String telephone, String cellphone, String email, String type)
121 {
122 if (firstName == null)
123 {
124 throw new IllegalArgumentException("firstName cannot be null");
125 }
126 if (lastName == null)
127 {
128 throw new IllegalArgumentException("lastName cannot be null");
129 }
130 if (telephone == null)
131 {
132 throw new IllegalArgumentException("telephone cannot be null");
133 }
134 if (cellphone == null)
135 {
136 throw new IllegalArgumentException("cellphone cannot be null");
137 }
138 if (email == null)
139 {
140 throw new IllegalArgumentException("email cannot be null");
141 }
142 if (type == null)
143 {
144 throw new IllegalArgumentException("type cannot be null");
145 }
146
147 long telephoneLong = parsePhoneDigits(telephone);
148 long cellphoneLong = parsePhoneDigits(cellphone);
149 int skintype = Integer.parseInt(type);
150 try
151 {
152 this.addDataToCustomersTable.setString(1, firstName);
153 this.addDataToCustomersTable.setString(2, lastName);
154 this.addDataToCustomersTable.setLong(3, telephoneLong);
155 this.addDataToCustomersTable.setLong(4, cellphoneLong);
156 this.addDataToCustomersTable.setString(5, email);
157 this.addDataToCustomersTable.setInt(6, skintype);
158
159 this.addDataToCustomersTable.executeUpdate();
160 }
161 catch (SQLException ex)
162 {
163 System.out.println("SQL Error: " + ex.getMessage());
164 }
165 }
166
167 public void InsertIntoCustomerTable(Customer customer)
168 {
169 if (customer == null)
170 {
171 throw new IllegalArgumentException("customer cannot be null");
172 }
173
174 try
175 {
176 this.addDataToCustomersTable.setString(1, customer.getFirstName());
177 this.addDataToCustomersTable.setString(2, customer.getLastName());
178 this.addDataToCustomersTable.setLong(3, customer.getTelephone());
179 this.addDataToCustomersTable.setLong(4, customer.getCellphone());
180 this.addDataToCustomersTable.setString(5, customer.getEmail());
181 this.addDataToCustomersTable.setInt(6, customer.getSkinType());
182
183 this.addDataToCustomersTable.executeUpdate();
184 }
185 catch (SQLException ex)
186 {
187 System.out.println("SQL Error: " + ex.getMessage());
188 }
189 }
190
191 public void InsertIntoTreatmentTable(Treatment treatment)
192 {
193 if (treatment == null)
194 {
195 throw new IllegalArgumentException("treatment cannot be null");
196 }
197
198 try
199 {
200 this.addDataToTreatmentsTable.setLong(1, treatment.getCustId());
201 this.addDataToTreatmentsTable.setString(2, treatment.getDate());
202 this.addDataToTreatmentsTable.setString(3, treatment.getArea());
203 this.addDataToTreatmentsTable.setFloat(4, treatment.getPulseWidth());
204 this.addDataToTreatmentsTable.setInt(5, treatment.getSpotSize());
205 this.addDataToTreatmentsTable.setInt(6, treatment.getEnergy());
206 this.addDataToTreatmentsTable.setFloat(7, treatment.getAmount());
207 this.addDataToTreatmentsTable.setString(8, treatment.getComments());
208
209 this.addDataToTreatmentsTable.executeUpdate();
210 }
211 catch (SQLException ex)
212 {
213 System.out.println("SQL Error: " + ex.getMessage());
214 }
215 }
216
217 public ResultSet QueryFromCustomerTable(String firstName, String lastName)
218 {
219 if (firstName == null)
220 {
221 throw new IllegalArgumentException("firstName cannot be null");
222 }
223 if (lastName == null)
224 {
225 throw new IllegalArgumentException("lastName cannot be null");
226 }
227
228 ResultSet rs = null;
229 try
230 {
231 this.queryDataFromCustomersTable.setString(1, "%" + firstName + "%");
232 this.queryDataFromCustomersTable.setString(2, "%" + lastName + "%");
233
234 rs = this.queryDataFromCustomersTable.executeQuery();
235 }
236 catch (SQLException ex)
237 {
238 System.out.println("SQL Error: " + ex.getMessage());
239 }
240
241 return rs;
242 }
243
244 public long QueryCustIDFromCustomerTable(Customer customer)
245 {
246 long id = -1L;
247 try
248 {
249 this.queryIDFromCustomerTable.setString(1, customer.getFirstName());
250 this.queryIDFromCustomerTable.setString(2, customer.getLastName());
251 this.queryIDFromCustomerTable.setLong(3, customer.getTelephone());
252 this.queryIDFromCustomerTable.setLong(4, customer.getCellphone());
253 this.queryIDFromCustomerTable.setString(5, customer.getEmail());
254 this.queryIDFromCustomerTable.setInt(6, customer.getSkinType());
255
256 ResultSet rs = this.queryIDFromCustomerTable.executeQuery();
257
258 rs.next();
259 id = rs.getLong(1);
260 }
261 catch (SQLException ex)
262 {
263 System.out.println("SQL Error: " + ex.getMessage());
264 }
265
266 return id;
267 }
268
269 public void updateCustomersTableUsingID(Customer customer)
270 {
271 if (customer == null)
272 {
273 throw new IllegalArgumentException("customer cannot be null");
274 }
275
276 try
277 {
278 this.updateCustomerTableUsingID.setString(1, customer.getFirstName());
279 this.updateCustomerTableUsingID.setString(2, customer.getLastName());
280 this.updateCustomerTableUsingID.setLong(3, customer.getTelephone());
281 this.updateCustomerTableUsingID.setLong(4, customer.getCellphone());
282 this.updateCustomerTableUsingID.setString(5, customer.getEmail());
283 this.updateCustomerTableUsingID.setInt(6, customer.getSkinType());
284 this.updateCustomerTableUsingID.setLong(7, customer.getID());
285
286 this.updateCustomerTableUsingID.executeUpdate();
287 }
288 catch (SQLException ex)
289 {
290 System.out.println("SQL Error: " + ex.getMessage());
291 }
292 }
293
294 public ResultSet QueryFromTreatmentsTable(Customer customer)
295 {
296 if (customer == null)
297 {
298 throw new IllegalArgumentException("customer cannot be null");
299 }
300
301 ResultSet rs = null;
302 try
303 {
304 long id = QueryCustIDFromCustomerTable(customer);
305 this.queryDataFromTreatmentsTableByCustID.setLong(1, id);
306
307 rs = this.queryDataFromTreatmentsTableByCustID.executeQuery();
308 }
309 catch (SQLException ex)
310 {
311 System.out.println("SQL Error: " + ex.getMessage());
312 }
313
314 return rs;
315 }
316
317 public void updateTreatment(Treatment treatment)
318 {
319 if (treatment == null)
320 {
321 throw new IllegalArgumentException("treatment cannot be null");
322 }
323
324 try
325 {
326 this.updateTreatmentsTableUsingID.setString(1, treatment.getDate());
327 this.updateTreatmentsTableUsingID.setString(2, treatment.getArea());
328 this.updateTreatmentsTableUsingID.setInt(3, treatment.getPulseWidth());
329 this.updateTreatmentsTableUsingID.setInt(4, treatment.getSpotSize());
330 this.updateTreatmentsTableUsingID.setInt(5, treatment.getEnergy());
331 this.updateTreatmentsTableUsingID.setInt(6, treatment.getAmount());
332 this.updateTreatmentsTableUsingID.setString(7, treatment.getComments());
333
334 this.updateTreatmentsTableUsingID.setLong(8, treatment.getTreatmentId());
335
336 this.updateTreatmentsTableUsingID.executeUpdate();
337 }
338 catch (SQLException ex)
339 {
340 System.out.println("SQL Error: " + ex.getMessage());
341 }
342 }
343
344 public long getLastInsertID()
345 {
346 try
347 {
348 ResultSet rs = this.queryLastId.executeQuery();
349
350 if (rs.next())
351 {
352 long id = rs.getLong(1);
353 return id;
354 }
355 }
356 catch (SQLException ex)
357 {
358 System.out.println("SQL Error: " + ex.getMessage());
359 }
360
361 return -1L;
362 }
363
364 private long parsePhoneDigits(String telephoneString)
365 {
366 String result = "";
367
368 if (telephoneString == null)
369 {
370 throw new IllegalArgumentException("telephoneString cannot be null");
371 }
372
373 for (int i = 0; i < telephoneString.length(); ++i)
374 {
375 char ch = telephoneString.charAt(i);
376
377 if (!(Character.isDigit(ch)))
378 continue;
379 result = result + ch;
380 }
381
382 try
383 {
384 long resultLong = Long.parseLong(result);
385
386 return resultLong;
387 }
388 catch (NumberFormatException ex) {
389 }
390 return 0L;
391 }
392
393 public void installDB(String dbName)
394 {
395 if (dbName == null)
396 {
397 throw new IllegalArgumentException("dbName cannot be null");
398 }
399
400 try
401 {
402 Statement stmt = this.conn.createStatement();
403
404 System.out.println("Creating DB \"" + dbName + "\"");
405 stmt.execute("CREATE DATABASE IF NOT EXISTS " + dbName);
406
407 installTables();
408 }
409 catch (SQLException ex)
410 {
411 System.out.println("SQL Error: " + ex.getMessage());
412 }
413 }
414
415 private void installTables()
416 {
417 Statement stmt;
418 try
419 {
420 stmt = this.conn.createStatement();
421
422 stmt.execute("DROP TABLE IF EXISTS customers");
423
424 String customerTable = "CREATE TABLE IF NOT EXISTS customers (CustID INT(11) AUTO_INCREMENT,CustFirstName VARCHAR(256),CustLastName VARCHAR(256),CustTelephone BIGINT(12),CustCellphone BIGINT(12),CustEmail VARCHAR(256),CustSkinType INT(4),PRIMARY KEY(CustID))";
425
426 stmt.execute("CREATE TABLE IF NOT EXISTS customers (CustID INT(11) AUTO_INCREMENT,CustFirstName VARCHAR(256),CustLastName VARCHAR(256),CustTelephone BIGINT(12),CustCellphone BIGINT(12),CustEmail VARCHAR(256),CustSkinType INT(4),PRIMARY KEY(CustID))");
427
428 System.out.println("Created table: customers");
429 }
430 catch (SQLException ex)
431 {
432 System.out.println("SQL Error: " + ex.getMessage());
433 }
434
435 try
436 {
437 ex = this.conn.createStatement();
438
439 ex.execute("DROP TABLE IF EXISTS treatments");
440
441 String treatmentTable = "CREATE TABLE IF NOT EXISTS treatments (TreatmentID BIGINT(11) AUTO_INCREMENT,CustID BIGINT(11),TreatmentDate VARCHAR(128),TreatmentArea VARCHAR(128),TreatmentPulseWidth INT(5),TreatmentSpotSize INT(5),TreatmentEnergy INT(5),TreatmentAmount INT(5),TreatmentComments VARCHAR(256),PRIMARY KEY(TreatmentID))";
442
443 ex.execute("CREATE TABLE IF NOT EXISTS treatments (TreatmentID BIGINT(11) AUTO_INCREMENT,CustID BIGINT(11),TreatmentDate VARCHAR(128),TreatmentArea VARCHAR(128),TreatmentPulseWidth INT(5),TreatmentSpotSize INT(5),TreatmentEnergy INT(5),TreatmentAmount INT(5),TreatmentComments VARCHAR(256),PRIMARY KEY(TreatmentID))");
444
445 System.out.println("Created table: treatments");
446 }
447 catch (SQLException ex)
448 {
449 System.out.println("SQL Error: " + ex.getMessage());
450 }
451 }
452}