1
2
3
4
5
6
7
8
9
10
11
12 package ca.spaz.cron.datasource.sql;
13
14 import java.io.*;
15 import java.lang.reflect.Field;
16 import java.util.Date;
17 import java.sql.*;
18 import java.util.*;
19
20 import org.apache.log4j.Logger;
21
22 import ca.spaz.cron.database.*;
23 import ca.spaz.cron.datasource.*;
24 import ca.spaz.sql.*;
25
26 /***
27 * A Food datasource that is based on a SQL database.
28 *
29 * @author Chris Rose
30 */
31 public class SQLDatasource extends AbstractMutableFoodDatasource {
32 /***
33 * Logger for this class
34 */
35 private static final Logger logger = Logger.getLogger(SQLDatasource.class);
36
37 protected Connection conn;
38
39 private boolean allowLocal;
40
41 public static final String FOOD_DB_ID = "food";
42
43 private static final String USER_DB_CONNECTION_ID = "user";
44
45 private String id;
46
47 public static IFoodDatasource createReadonlyFoodSource(String connectionID) {
48 Connection conn = ConnectionManager.getInstance(connectionID)
49 .getConnection();
50 String name = ConnectionManager.getConnectionName(connectionID);
51 if (null == conn) {
52 throw new IllegalArgumentException("No connection for " + connectionID);
53 }
54 return new SQLDatasource(name, connectionID, conn, false);
55 }
56
57 public static ILocalFoodDatasource createPersonalFoodSource() {
58 return createPersonalFoodSource(USER_DB_CONNECTION_ID);
59 }
60
61 public static ILocalFoodDatasource createPersonalFoodSource(String connectionId) {
62 Connection conn = ConnectionManager.getInstance(connectionId)
63 .getConnection();
64 String name = ConnectionManager.getConnectionName(connectionId);
65 if (null == conn) {
66 throw new IllegalArgumentException("No local connection for "
67 + connectionId);
68 }
69 return new SQLDatasource(name, connectionId, conn, true);
70 }
71
72 protected SQLDatasource(String name, String id, Connection conn, boolean allowLocal) {
73 super(name);
74 this.conn = conn;
75 this.setId(id);
76 this.allowLocal = allowLocal;
77 }
78
79
80
81
82
83
84
85 protected Food doConsumeFood(Serving serving) {
86 checkReadWrite();
87 validFood(serving.getFood());
88 Food ret = serving.getFood();
89 if (serving.getFood().getDataSource() != this) {
90 ret = addFood(serving.getFood());
91 serving.setFood(ret);
92 }
93 SQLFood sfood = (SQLFood) ret;
94 if (ret == null) {
95 return ret;
96 }
97 try {
98
99
100 SQLInsert ins = new SQLInsert("Serving");
101 ins.getColumns().add("fid", sfood.getID());
102 int MID = serving.getMeasure().getID();
103 if (MID >= 0) {
104 ins.getColumns().add("mid", MID);
105 }
106 ins.getColumns().add("grams", serving.getGrams());
107 ins.getColumns().add("meal", serving.getMeal());
108 ins.getColumns().add("eaten", serving.getDate());
109 ResultSet rs = ins.executeQuery(conn);
110 if (rs != null && rs.next()) {
111
112 serving.setID(rs.getInt("id"));
113 }
114
115 } catch (SQLException e) {
116 ret = null;
117 logger.error("[" + getId() + "] - addToDatabase()", e);
118 registerError(e);
119 }
120
121 return ret;
122 }
123
124 /***
125 *
126 */
127 private void checkReadWrite() {
128 if (!allowLocal) {
129 throw new UnsupportedOperationException("Not modifiable");
130 }
131 }
132
133
134
135
136
137
138
139 protected boolean doChangeServingAmount(Serving serving) {
140 checkReadWrite();
141 validFood(serving.getFood());
142 SQLFood sfood = (SQLFood) serving.getFood();
143 boolean success = true;
144 try {
145 SQLUpdate sql = new SQLUpdate("Serving");
146 sql.getColumns().add("grams", serving.getGrams());
147 int MID = serving.getMeasure().getID();
148 if (MID >= 0) {
149 sql.getColumns().add("MID", MID);
150 } else {
151 sql.getColumns().add("MID", 0);
152 }
153 sql.addWhere("fid", sfood.getID());
154 sql.addWhere("eaten", serving.getDate());
155 sql.addWhere("meal", serving.getMeal());
156 sql.execute(conn);
157 } catch (SQLException e) {
158 logger.error("[" + getId() + "] - doChangeConsumedAmount(Serving)", e);
159 success = false;
160 registerError(e);
161 }
162 return success;
163 }
164
165
166
167
168
169
170 protected boolean doUnConsumeFood(Serving serving) {
171 checkReadWrite();
172 validFood(serving.getFood());
173 SQLFood sfood = (SQLFood) serving.getFood();
174 boolean success = true;
175 try {
176 SQLDelete sql = new SQLDelete("Serving");
177 sql.addWhere("fid", sfood.getID());
178 sql.addWhere("ID", serving.getID());
179 sql.addWhere("eaten", serving.getDate());
180 sql.execute(conn);
181 } catch (SQLException e) {
182 logger.error("[" + getId() + "] - doUnConsumeFood(Serving)", e);
183 success = false;
184 registerError(e);
185 }
186 if (success) {
187 notifyObservers(FoodDataEvent.DSEVENT_SERVING_REMOVED, serving);
188 }
189 return success;
190 }
191
192 /***
193 * Update all changes made to a set of measures for the food.
194 * First removes any old Measures no longer referenced in the new list
195 * and then saves or adds all the measures in the list.
196 */
197 protected boolean doChangeMeasure(Food food, List measures) {
198 checkReadWrite();
199 validFood(food);
200 SQLFood sfood = (SQLFood) food;
201 List m = Collections.checkedList(measures, Measure.class);
202 boolean success = true;
203
204
205 List old = doGetMeasuresFor(sfood);
206 old.remove(Measure.GRAM);
207 for (Iterator iter = old.iterator(); iter.hasNext();) {
208 Measure m1 = (Measure) iter.next();
209 boolean found = false;
210 for (Iterator iter2 = measures.iterator(); iter2.hasNext();) {
211 Measure m2 = (Measure) iter2.next();
212 if (m1.getID() == m2.getID()) {
213 found = true;
214 break;
215 }
216 }
217 if (!found) {
218 doRemoveMeasure(sfood, m1);
219 }
220 }
221
222
223 for (Iterator iter = m.iterator(); iter.hasNext();) {
224 Measure meas = (Measure) iter.next();
225 boolean oldNot = getNotify();
226 setNotify(false);
227 if (meas.getID() == -1) {
228 doAddMeasure(food, meas);
229 } else {
230 doSaveMeasure(meas);
231 }
232 setNotify(oldNot);
233 }
234 if (success && getNotify()) {
235 notifyObservers(FoodDataEvent.DSEVENT_MEASURE_CHANGED);
236 }
237 return success;
238 }
239
240 /***
241 * Remove a measure from the database.
242 * Safely modifies all Servings that reference this measure,
243 * converting them to canonical GRAMS Measures.
244 * @param m the Measure to delete
245 * @return true if successful
246 */
247 protected boolean doRemoveMeasure(Food food, Measure m) {
248 checkReadWrite();
249 validFood(food);
250 boolean success = true;
251 try {
252
253 SQLUpdate sel = new SQLUpdate("Serving");
254 sel.getColumns().add("MID", -1);
255 sel.addWhere("MID", new Integer(m.getID()));
256 sel.execute(conn);
257
258
259 SQLDelete sql = new SQLDelete("Measure");
260 sql.addWhere("ID", m.getID());
261 sql.execute(conn);
262 } catch (SQLException e) {
263 logger.error("[" + getId() + "] - doDeleteMeasure(Measure)", e);
264 success = false;
265 registerError(e);
266 }
267 if (success && getNotify()) {
268 notifyObservers(FoodDataEvent.DSEVENT_MEASURE_REMOVED);
269 }
270 return success;
271 }
272
273
274
275
276
277
278
279 protected boolean doAddMeasure(Food food, Measure measure) {
280 checkReadWrite();
281 validFood(food);
282 SQLFood sfood = (SQLFood) food;
283 boolean success = true;
284 try {
285 SQLInsert sql = new SQLInsert("Measure");
286 sql.getColumns().add("FID", sfood.getID());
287 sql.getColumns().add("amount", measure.getAmount());
288 sql.getColumns().add("grams", measure.getGrams());
289 sql.getColumns().add("description", measure.getDescription());
290 ResultSet rs = sql.executeQuery(conn);
291 if (rs != null && rs.next()) {
292
293 measure.setID(rs.getInt("id"));
294 }
295 } catch (SQLException e) {
296 logger.error("[" + getId() + "] - addMeasure(Food, Measure)", e);
297 success = false;
298 registerError(e);
299 }
300 if (success && getNotify()) {
301 notifyObservers(FoodDataEvent.DSEVENT_MEASURE_ADDED, measure);
302 }
303 return success;
304 }
305
306
307
308
309
310
311
312
313 protected boolean doSaveMeasure(Measure measure) {
314 assert(measure.getID() >= 0);
315 checkReadWrite();
316 boolean success = true;
317 try {
318 SQLUpdate sql = new SQLUpdate("Measure");
319 sql.getColumns().add("amount", measure.getAmount());
320 sql.getColumns().add("grams", measure.getGrams());
321 sql.getColumns().add("description", measure.getDescription());
322 sql.addWhere("ID", measure.getID());
323 sql.execute(conn);
324 } catch (SQLException e) {
325 logger.error("[" + getId() + "] - addMeasure(Food, Measure)", e);
326 success = false;
327 registerError(e);
328 }
329 if (success && getNotify()) {
330 notifyObservers(FoodDataEvent.DSEVENT_MEASURE_ADDED, measure);
331 }
332 return success;
333 }
334
335
336
337
338
339
340 protected int doGetTimesConsumed(Food food) {
341 checkReadWrite();
342 validFood(food);
343 SQLFood sfood = (SQLFood) food;
344 int num = -1;
345 try {
346
347 SQLSelect s = new SQLSelect("Serving");
348 s.addSelection("COUNT(FID)");
349 s.addWhere("FID", sfood.getID());
350 ResultSet res = s.executeQuery(conn);
351 if (res.next()) {
352 num = res.getInt(1);
353 }
354 } catch (SQLException e) {
355 logger.error("[" + getId() + "] - getTimesConsumed(Food, Date, Date)", e);
356 registerError(e);
357 }
358 return num;
359 }
360
361
362
363
364
365
366
367 protected int doGetTimesConsumed(Food food, Date startDate, Date endDate) {
368 checkReadWrite();
369 validFood(food);
370 SQLFood sfood = (SQLFood) food;
371 int num = -1;
372 try {
373
374 SQLSelect s = new SQLSelect("Serving");
375 s.addSelection("COUNT(FID)");
376 s.addWhere("FID", sfood.getID());
377 s.addWhere("eaten", SQLSelect.GT, startDate);
378 s.addWhere("eaten", SQLSelect.LT, endDate);
379 ResultSet res = s.executeQuery(conn);
380 if (res.next()) {
381 num = res.getInt(1);
382 }
383 } catch (SQLException e) {
384 logger.error("[" + getId() + "] - getTimesConsumed(Food, Date, Date)", e);
385 registerError(e);
386 }
387 return num;
388 }
389
390
391
392
393
394
395 public List getConsumedOn(Date date) {
396 notNull(date);
397 checkReadWrite();
398 ArrayList res = new ArrayList();
399 SQLSelect sel = new SQLSelect("Serving");
400 sel.addWhere("eaten", date);
401 if (logger.isDebugEnabled()) {
402 logger.debug("[" + getId() + "] - getAllConsumedDuringDay(Date) " + sel.toString());
403 }
404 try {
405 ResultSet row = sel.executeQuery(conn);
406 while (row.next()) {
407 Serving c = new Serving(getFoodByID(row.getInt("FID")));
408 c.setGrams(row.getDouble("grams"));
409 c.setMeasure(row.getInt("MID"));
410 c.setID(row.getInt("ID"));
411 c.setDate(row.getDate("eaten"));
412 c.setMeal(row.getInt("meal"));
413 res.add(c);
414 }
415 } catch (SQLException e) {
416 logger.error("[" + getId() + "] - getAllConsumedDuringDay(Date)", e);
417 registerError(e);
418 }
419 return res;
420 }
421
422 private Food getFoodByID(int FID) throws SQLException {
423 Food f = null;
424 SQLSelect s = new SQLSelect("Food");
425 s.addWhere("id", new Integer(FID));
426 ResultSet res = s.executeQuery(conn);
427 if (res.next()) {
428 f = internalCreateFood();
429 SQLFood sfood = (SQLFood) f;
430 sfood.setID(res.getInt("ID"));
431 f.setSource(res.getString("source"));
432 f.setDescription(res.getString("description"));
433 f.setSourceUID(res.getString("sourceUID"));
434 SQLFoodGroup fg = new SQLFoodGroupImpl(res.getString("foodgroup"));
435 f.setFoodGroup(fg);
436 }
437 return f;
438 }
439
440
441
442
443
444
445 protected Food doAddFood(Food food) {
446
447 checkReadWrite();
448 boolean success = true;
449 ResultSet genKeys = null;
450 int newID = -1;
451 try {
452 addFoodGroup(food.getFoodGroup());
453 SQLInsert sql = new SQLInsert("Food");
454 sql.getColumns().add("description", food.getDescription());
455 sql.getColumns().add("foodgroup", food.getFoodGroup());
456 sql.getColumns().add("source", getName());
457 sql.getColumns().add("sourceUID", food.getSourceUID());
458 genKeys = sql.executeQuery(conn);
459 if (genKeys != null && genKeys.next()) {
460
461 newID = genKeys.getInt("id");
462 } else {
463 SQLSelect sel = new SQLSelect("Food");
464 sel.addWhere("sourceUID", food.getSourceUID());
465 sel.addSelection("id");
466 sel.addOrderBy("id");
467 ResultSet res = sel.executeQuery(conn);
468 while (res.next()) {
469 newID = res.getInt("id");
470 }
471 }
472 } catch (SQLException e) {
473 logger.error("[" + getId() + "] - addFood(Food)", e);
474 registerError(e);
475 success = false;
476 }
477 SQLFood newFood = null;
478 if (success) {
479 try {
480 newFood = (SQLFood) createNewFood();
481 newFood.setDescription(food.getDescription());
482 newFood.setFoodGroup(food.getFoodGroup());
483 newFood.setID(newID);
484 newFood.setSource(food.getSource());
485 newFood.setSourceUID(food.getSourceUID());
486
487 addNutrients(newFood, food.getAminoAcids());
488 addNutrients(newFood, food.getMacroNutrients());
489 addNutrients(newFood, food.getMinerals());
490 addNutrients(newFood, food.getVitamins());
491 addNutrients(newFood, food.getLipids());
492 for (int i=0; i<food.getMeasures().size(); i++) {
493 Measure m = (Measure)food.getMeasures().get(i);
494 if (m != Measure.GRAM) {
495 addMeasure(newFood, m);
496 }
497 }
498 notifyObservers(FoodDataEvent.DSEVENT_FOOD_ADDED, food);
499 } catch (SQLException e) {
500 logger.error("[" + getId() + "] - doAddFood(Food)", e);
501 success = false;
502 newFood = null;
503 registerError(e);
504 }
505 }
506 return newFood;
507 }
508
509
510
511
512
513
514
515 protected boolean doSaveFood(Food food) {
516 checkReadWrite();
517 validFood(food);
518 boolean success = true;
519 SQLFood sfood = (SQLFood) food;
520 if (sfood.getID() < 0) {
521
522 Food ret = doAddFood(sfood);
523 success = (ret != null);
524 } else {
525
526 addFoodGroup(food.getFoodGroup());
527 try {
528 SQLUpdate sql = new SQLUpdate("Food");
529 sql.getColumns().add("description", sfood.getDescription());
530 sql.getColumns().add("foodgroup", sfood.getFoodGroup());
531 sql.getColumns().add("source", sfood.getSource());
532 sql.getColumns().add("sourceUID", sfood.getSourceUID());
533 sql.addWhere("ID", Integer.toString(sfood.getID()));
534 sql.execute(conn);
535 saveNutrients(sfood, sfood.getAminoAcids());
536 saveNutrients(sfood, sfood.getMacroNutrients());
537 saveNutrients(sfood, sfood.getMinerals());
538 saveNutrients(sfood, sfood.getVitamins());
539 saveNutrients(sfood, sfood.getLipids());
540 changeMeasure(sfood, sfood.getMeasures());
541 } catch (SQLException e) {
542 logger.error("[" + getId() + "] - changeFood(Food)", e);
543 success = false;
544 registerError(e);
545 }
546 }
547 if (success) {
548 notifyObservers(FoodDataEvent.DSEVENT_FOOD_CHANGED, sfood);
549 }
550 return success;
551 }
552
553 private void saveNutrients(SQLFood food, NutrientTable table)
554 throws SQLException {
555 SQLUpdate sql = new SQLUpdate(table.getTableName());
556 Field[] fields = table.getClass().getFields();
557 for (int i = 0; i < fields.length; i++) {
558 try {
559 sql.getColumns().add(fields[i].getName(), fields[i].getDouble(table));
560 } catch (Exception e) {
561 logger.error("[" + getId() + "] - saveNutrients(Food,NutrientTable)", e);
562 registerError(e);
563 }
564 }
565 sql.addWhere("FID", food.getID());
566 sql.execute(conn);
567 }
568
569 /***
570 * Add nutrients table if the table does not yet exist
571 * @param food the food to which the nutrients are linked
572 * @param table the nutrient table to add to the food
573 * @throws SQLException
574 */
575 private void addNutrients(SQLFood food, NutrientTable table) throws SQLException {
576 SQLInsert sql = new SQLInsert(table.getTableName());
577 Field[] fields = table.getClass().getFields();
578 for (int i = 0; i < fields.length; i++) {
579 try {
580 sql.getColumns().add(fields[i].getName(), fields[i].getDouble(table));
581 } catch (Exception e) {
582 logger.error("[" + getId() + "] - saveNutrients(Food,NutrientTable)", e);
583 registerError(e);
584 }
585 }
586 sql.getColumns().add("FID", food.getID());
587 sql.execute(conn);
588 }
589
590 private void validFood(Food f) {
591 if (!(f instanceof SQLFood)) {
592 throw new IllegalArgumentException("SQL Datasource requires SQL food objects");
593 }
594 }
595
596
597
598
599
600
601 protected boolean doRemoveFood(Food food) {
602 checkReadWrite();
603 validFood(food);
604 SQLFood sfood = (SQLFood) food;
605 boolean success = true;
606 try {
607
608 SQLDelete sql = new SQLDelete("Food");
609 sql.addWhere("id", sfood.getID());
610 sql.execute(conn);
611 } catch (SQLException e) {
612 logger.error("[" + getId() + "] - removeFood(Food) - ", e);
613 success = false;
614 registerError(e);
615 }
616 if (success) {
617 notifyObservers(FoodDataEvent.DSEVENT_FOOD_DELETED, sfood);
618 }
619 return success;
620 }
621
622
623
624
625
626
627 protected List doFindFoods(String[] parts) {
628 return doFindFoods(parts, null, null);
629 }
630
631
632
633
634
635
636
637 protected List doFindFoods(String[] keys, String foodGroup, String source) {
638 List result = new ArrayList();
639 SQLSelect select = new SQLSelect("Food");
640
641
642
643 for (int j = 0; j < keys.length; j++) {
644 if (keys[j].length() > 0) {
645 if (logger.isDebugEnabled()) {
646 logger.debug("[" + getId() + "] - doDBSearch() - Split part: " + keys[j]);
647 }
648 select.addWhereLike("description", "%" + keys[j] + "%");
649 select.addWhere("source", getName());
650 }
651 }
652
653 if (null != foodGroup) {
654 select.addWhere("foodgroup", foodGroup);
655 }
656
657 if (null != source) {
658 select.addWhere("source", source);
659 }
660
661 if (logger.isDebugEnabled()) {
662 logger.debug("[" + getId() + "] - doDBSearch() - Query: " + select.toString());
663 }
664
665 try {
666 ResultSet res = select.executeQuery(conn);
667 result.clear();
668 buildFoodList(result, res);
669 } catch (SQLException e) {
670 logger.error("[" + getId() + "] - doDBSearch()", e);
671 registerError(e);
672 }
673 return result;
674 }
675
676 /***
677 * @param result
678 * @param res
679 * @throws SQLException
680 */
681 private void buildFoodList(List result, ResultSet res) throws SQLException {
682 while (res.next()) {
683 Food f = internalCreateFood();
684 SQLFood sfood = (SQLFood) f;
685 sfood.setID(res.getInt("ID"));
686 f.setSource(res.getString("source"));
687 f.setDescription(res.getString("description"));
688 f.setSourceUID(res.getString("sourceUID"));
689 SQLFoodGroup fg = new SQLFoodGroupImpl(res.getString("foodgroup"));
690 f.setFoodGroup(fg);
691 result.add(f);
692 }
693 }
694
695
696
697
698
699
700 protected List doFindAllFoods() {
701 List result = new ArrayList();
702 SQLSelect sel = new SQLSelect("Food");
703 sel.addWhere("source", getName());
704 try {
705 ResultSet res = sel.executeQuery(conn);
706 buildFoodList(result, res);
707 } catch (SQLException e) {
708 logger.error("[" + getId() + "] - doFindAllFoods()", e);
709 registerError(e);
710 }
711 return result;
712 }
713
714
715
716
717
718
719 protected List doGetMeasuresFor(Food food) {
720 ArrayList weights = new ArrayList();
721 weights.add(Measure.GRAM);
722 validFood(food);
723 SQLFood sfood = (SQLFood) food;
724 try {
725 SQLSelect s = new SQLSelect("Measure");
726 s.addWhere("FID", new Integer(sfood.getID()));
727 ResultSet res = s.executeQuery(conn);
728 while (res.next()) {
729 Measure w = new Measure();
730 w.setFoodID(sfood.getID());
731 w.setID(res.getInt("ID"));
732 w.setAmount(res.getDouble("amount"));
733 w.setDescription(res.getString("description"));
734 w.setGrams(res.getDouble("grams"));
735 weights.add(w);
736 }
737 } catch (SQLException e) {
738 logger.error("[" + getId() + "] - getMeasuresFor(Food)", e);
739 registerError(e);
740 }
741
742 return weights;
743 }
744
745
746
747
748
749
750 protected void doGetNutrientsFor(Food food, NutrientTable nutrients) {
751 validFood(food);
752 SQLSelect s = new SQLSelect(nutrients.getTableName());
753 SQLFood sfood = (SQLFood) food;
754 s.addWhere("FID", new Integer(sfood.getID()));
755 try {
756 ResultSet res = s.executeQuery(conn);
757 if (res.next()) {
758 try {
759 DBRow.load(res, nutrients);
760 } catch (Exception e) {
761 logger.error("NutrientTable(Food)", e);
762 registerError(e);
763 }
764 }
765 } catch (SQLException e) {
766 logger.error("[" + getId() + "] - getNutrientsFor() - food: " + food.toString()
767 + ", nutrients: " + nutrients.toString(), e);
768 registerError(e);
769 }
770
771 }
772
773
774
775
776
777
778 public List getSources() {
779 List sources = new ArrayList();
780 try {
781 SQLSelect s = new SQLSelect("Food");
782 s.addSelection("DISTINCT(source)");
783 ResultSet res = s.executeQuery(conn);
784 while (res.next()) {
785 sources.add(res.getString("source"));
786 }
787 } catch (SQLException e) {
788 logger.error("[" + getId() + "] - getAllSources()", e);
789 registerError(e);
790 }
791 return sources;
792 }
793
794
795
796
797
798
799 public List getFoodGroups() {
800 List groups = new ArrayList();
801 try {
802 SQLSelect s = new SQLSelect("Food");
803 s.addSelection("DISTINCT(foodgroup)");
804 ResultSet res = s.executeQuery(conn);
805 while (res.next()) {
806 SQLFoodGroup fg = new SQLFoodGroupImpl(res.getString("foodgroup"));
807 groups.add(fg);
808 }
809 } catch (SQLException e) {
810 logger.error("[" + getId() + "] - getAllFoodGroups()", e);
811 registerError(e);
812 }
813 return groups;
814 }
815
816
817
818
819
820
821 public boolean isSearchable() {
822 return true;
823 }
824
825
826
827
828
829
830 public boolean isAvailable() {
831 boolean ret = true;
832 try {
833 if (allowLocal) {
834 conn.createStatement().execute("SELECT * FROM Measure");
835 }
836 if (!conn.createStatement().execute("SELECT * FROM Food")) {
837 ret = false;
838 }
839 } catch (SQLException e) {
840 ret = false;
841 }
842 return ret;
843 }
844
845
846
847
848
849
850 public boolean isListable() {
851 return true;
852 }
853
854
855
856
857 protected Food doCreateNewFood() {
858 checkReadWrite();
859 return internalCreateFood();
860 }
861
862 private Food internalCreateFood() {
863 return new SQLFoodImpl(this);
864 }
865
866
867
868
869 public void close() {
870 try {
871 if (!conn.isClosed()) {
872 if (conn.getMetaData().getURL().startsWith("jdbc:hsqldb:file") ||
873 conn.getMetaData().getURL().startsWith("jdbc:hsqldb:mem")) {
874 conn.createStatement().execute("SHUTDOWN");
875 logger.info("[" + getId() + "] - Shutdown called");
876 }
877 conn.close();
878 logger.info("[" + getId() + "] - closed");
879 }
880 } catch (SQLException e) {
881 logger.error("[" + getId() + "] - close()", e);
882 registerError(e);
883 }
884 }
885
886
887
888
889 public void initialize() {
890
891
892
893
894
895
896
897 if (logger.isInfoEnabled()) {
898 logger.info("Initializing " + getId() + ".");
899 }
900 try {
901 InputStream resource = getClass().getResourceAsStream("/sql/" + getId() + ".sql");
902 if (null == resource) {
903 throw new FileNotFoundException("/sql/" + getId() + ".sql");
904 }
905 BufferedReader br = new BufferedReader(new InputStreamReader(resource));
906 String line = null;
907 try {
908 Statement stmt = conn.createStatement();
909 line = br.readLine();
910 while (line != null) {
911 line = line.trim();
912 if (line.length() > 0) {
913 if (!line.startsWith("--")) {
914 stmt.execute(line);
915 }
916 }
917 line = br.readLine();
918 }
919 } catch (SQLException e) {
920 throw new IllegalStateException("Unable to execute '" + line + "'", e);
921 }
922 } catch (IOException e) {
923 throw new IllegalStateException("Unable to retrieve resource for initializing " + this.getName(), e);
924 }
925 if (!isAvailable()) {
926 throw new IllegalStateException("Successful initialization, but " + getName() + " is uninitialized.");
927 }
928 }
929
930
931
932
933 protected void doAddFoodGroup(FoodGroup foodGroup) {
934 checkReadWrite();
935 try {
936 SQLSelect sel = new SQLSelect("FoodGroup");
937 sel.addWhere("fg_name", foodGroup.getFoodGroupName());
938 ResultSet res = sel.executeQuery(conn);
939 if (res.next()) {
940
941 return;
942 }
943 } catch (SQLException e) {
944 logger.error("doAddFoodGroup(FoodGroup) - Unable to verify food group", e);
945 }
946 SQLInsert ins = new SQLInsert("FoodGroup");
947 ins.getColumns().add("fg_name", foodGroup.getFoodGroupName());
948 try {
949 ins.execute(conn);
950 } catch (SQLException e) {
951 logger.error("doAddFoodGroup(FoodGroup) - unable to add food group", e);
952 }
953 }
954
955 /***
956 * Set the <code>id</code> in the <code>SQLDatasource</code>.
957 * @param id The id to set.
958 */
959 public void setId(String id) {
960 this.id = id;
961 }
962
963 /***
964 * Retrieve the <code>id</code> from the <code>SQLDatasource</code>. This will be
965 * unique between all SQL data source instances.
966 *
967 * @return Returns the id.
968 */
969 public String getId() {
970 return id;
971 }
972
973 protected Food findFoodBySourceUID(String sourceUID) {
974 SQLSelect sel = new SQLSelect("Food");
975 sel.addWhere("SourceUID", sourceUID);
976 sel.addSelection("ID");
977 ResultSet res;
978 Food ret = null;
979 try {
980 res = sel.executeQuery(conn);
981 if (!res.next()) {
982 ret = null;
983 } else {
984 int fid = res.getInt("ID");
985 ret = getFoodByID(fid);
986 }
987 } catch (SQLException e) {
988
989 e.printStackTrace();
990 }
991 return ret;
992 }
993
994 public boolean containsFood(Food food) {
995 SQLSelect sel = new SQLSelect("Food");
996 sel.addWhere("SourceUID", food.getSourceUID());
997 boolean return_value = false;
998 try {
999 ResultSet res = sel.executeQuery(conn);
1000 return_value = res.next();
1001 res.close();
1002 } catch (SQLException e) {
1003 logger.error("containsFood(Food)", e);
1004 }
1005 return return_value;
1006 }
1007
1008 }