001: /*
002: * Copyright 2004-2006 the original author or authors.
003: *
004: * Licensed under the Apache License, Version 2.0 (the "License");
005: * you may not use this file except in compliance with the License.
006: * You may obtain a copy of the License at
007: *
008: * http://www.apache.org/licenses/LICENSE-2.0
009: *
010: * Unless required by applicable law or agreed to in writing, software
011: * distributed under the License is distributed on an "AS IS" BASIS,
012: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013: * See the License for the specific language governing permissions and
014: * limitations under the License.
015: */
016:
017: package org.compass.sample.petclinic.jdbc;
018:
019: import java.sql.ResultSet;
020: import java.sql.SQLException;
021: import java.sql.Timestamp;
022: import java.sql.Types;
023: import java.util.ArrayList;
024: import java.util.Collection;
025: import java.util.Date;
026: import java.util.HashMap;
027: import java.util.Iterator;
028: import java.util.List;
029: import java.util.Map;
030:
031: import javax.sql.DataSource;
032:
033: import org.compass.sample.petclinic.Clinic;
034: import org.compass.sample.petclinic.Entity;
035: import org.compass.sample.petclinic.Owner;
036: import org.compass.sample.petclinic.Pet;
037: import org.compass.sample.petclinic.PetType;
038: import org.compass.sample.petclinic.Specialty;
039: import org.compass.sample.petclinic.Vet;
040: import org.compass.sample.petclinic.Visit;
041: import org.compass.sample.petclinic.util.EntityUtils;
042: import org.springframework.dao.DataAccessException;
043: import org.springframework.jdbc.core.SqlParameter;
044: import org.springframework.jdbc.core.support.JdbcDaoSupport;
045: import org.springframework.jdbc.object.MappingSqlQuery;
046: import org.springframework.jdbc.object.SqlUpdate;
047: import org.springframework.orm.ObjectRetrievalFailureException;
048:
049: /**
050: * Base class for JDBC implementations of the Clinic interface.
051: *
052: * @author Ken Krebs
053: * @author Juergen Hoeller
054: * @author Rob Harrop
055: */
056: public abstract class AbstractJdbcClinic extends JdbcDaoSupport
057: implements Clinic, CachingClinic {
058:
059: private VetsQuery vetsQuery;
060:
061: private SpecialtiesQuery specialtiesQuery;
062:
063: private VetSpecialtiesQuery vetSpecialtiesQuery;
064:
065: private OwnersByNameQuery ownersByNameQuery;
066:
067: private OwnerQuery ownerQuery;
068:
069: private OwnerInsert ownerInsert;
070:
071: private OwnerUpdate ownerUpdate;
072:
073: private PetsByOwnerQuery petsByOwnerQuery;
074:
075: private PetQuery petQuery;
076:
077: private PetInsert petInsert;
078:
079: private PetUpdate petUpdate;
080:
081: private PetTypesQuery petTypesQuery;
082:
083: private VisitsQuery visitsQuery;
084:
085: private VisitInsert visitInsert;
086:
087: private final List vets = new ArrayList();
088:
089: protected void initDao() {
090: this .vetsQuery = new VetsQuery(getDataSource());
091: this .specialtiesQuery = new SpecialtiesQuery(getDataSource());
092: this .vetSpecialtiesQuery = new VetSpecialtiesQuery(
093: getDataSource());
094: this .petTypesQuery = new PetTypesQuery(getDataSource());
095: this .ownersByNameQuery = new OwnersByNameQuery(getDataSource());
096: this .ownerQuery = new OwnerQuery(getDataSource());
097: this .ownerInsert = new OwnerInsert(getDataSource());
098: this .ownerUpdate = new OwnerUpdate(getDataSource());
099: this .petsByOwnerQuery = new PetsByOwnerQuery(getDataSource());
100: this .petQuery = new PetQuery(getDataSource());
101: this .petInsert = new PetInsert(getDataSource());
102: this .petUpdate = new PetUpdate(getDataSource());
103: this .visitsQuery = new VisitsQuery(getDataSource());
104: this .visitInsert = new VisitInsert(getDataSource());
105: }
106:
107: public void refreshVetsCache() throws DataAccessException {
108: synchronized (this .vets) {
109: logger.info("Refreshing vets cache");
110:
111: // Retrieve the list of all vets.
112: this .vets.clear();
113: this .vets.addAll(this .vetsQuery.execute());
114:
115: // Retrieve the list of all possible specialties.
116: List specialties = this .specialtiesQuery.execute();
117:
118: // Build each vet's list of specialties.
119: Iterator vi = this .vets.iterator();
120: while (vi.hasNext()) {
121: Vet vet = (Vet) vi.next();
122: List vetSpecialtiesIds = this .vetSpecialtiesQuery
123: .execute(vet.getId().intValue());
124: Iterator vsi = vetSpecialtiesIds.iterator();
125: while (vsi.hasNext()) {
126: int specialtyId = ((Integer) vsi.next()).intValue();
127: Specialty specialty = (Specialty) EntityUtils
128: .getById(specialties, Specialty.class,
129: specialtyId);
130: vet.addSpecialty(specialty);
131: }
132: }
133: }
134: }
135:
136: // START of Clinic implementation section *******************************
137:
138: public Collection getPets() throws DataAccessException {
139: // No need to implement
140: return null;
141: }
142:
143: public Collection getVets() throws DataAccessException {
144: synchronized (this .vets) {
145: if (this .vets.isEmpty()) {
146: refreshVetsCache();
147: }
148: return this .vets;
149: }
150: }
151:
152: public Collection getPetTypes() throws DataAccessException {
153: return this .petTypesQuery.execute();
154: }
155:
156: /** Method loads owners plus pets and visits if not already loaded */
157: public Collection findOwners(String lastName)
158: throws DataAccessException {
159: List owners = this .ownersByNameQuery.execute(lastName + "%");
160: loadOwnersPetsAndVisits(owners);
161: return owners;
162: }
163:
164: /** Method loads an owner plus pets and visits if not already loaded */
165: public Owner loadOwner(int id) throws DataAccessException {
166: Owner owner = (Owner) this .ownerQuery.findObject(id);
167: if (owner == null) {
168: throw new ObjectRetrievalFailureException(Owner.class,
169: new Integer(id));
170: }
171: loadPetsAndVisits(owner);
172: return owner;
173: }
174:
175: public Pet loadPet(int id) throws DataAccessException {
176: JdbcPet pet = (JdbcPet) this .petQuery.findObject(id);
177: if (pet == null) {
178: throw new ObjectRetrievalFailureException(Pet.class,
179: new Integer(id));
180: }
181: Owner owner = loadOwner(pet.getOwnerId());
182: owner.addPet(pet);
183: loadVisits(pet);
184: return pet;
185: }
186:
187: public void storeOwner(Owner owner) throws DataAccessException {
188: if (owner.isNew()) {
189: this .ownerInsert.insert(owner);
190: } else {
191: this .ownerUpdate.update(owner);
192: }
193: }
194:
195: public void storePet(Pet pet) throws DataAccessException {
196: if (pet.isNew()) {
197: this .petInsert.insert(pet);
198: } else {
199: this .petUpdate.update(pet);
200: }
201: }
202:
203: public void storeVisit(Visit visit) throws DataAccessException {
204: if (visit.isNew()) {
205: this .visitInsert.insert(visit);
206: } else {
207: throw new UnsupportedOperationException(
208: "Visit update not supported");
209: }
210: }
211:
212: // END of Clinic implementation section *******************************
213:
214: /**
215: * Method maps a List of Entity objects keyed to their ids.
216: *
217: * @param list
218: * List containing Entity objects
219: * @return Map containing Entity objects
220: */
221: protected final Map mapEntityList(List list) {
222: Map map = new HashMap();
223: Iterator iterator = list.iterator();
224: while (iterator.hasNext()) {
225: Entity entity = (Entity) iterator.next();
226: map.put(entity.getId(), entity);
227: }
228: return map;
229: }
230:
231: /**
232: * Method to retrieve the <code>Visit</code> data for a <code>Pet</code>.
233: */
234: protected void loadVisits(JdbcPet pet) {
235: pet.setType((PetType) EntityUtils.getById(getPetTypes(),
236: PetType.class, pet.getTypeId()));
237: List visits = this .visitsQuery.execute(pet.getId().intValue());
238: Iterator vi = visits.iterator();
239: while (vi.hasNext()) {
240: Visit visit = (Visit) vi.next();
241: pet.addVisit(visit);
242: }
243: }
244:
245: /**
246: * Method to retrieve the <code>Pet</code> and <code>Visit</code> data
247: * for an <code>Owner</code>.
248: */
249: protected void loadPetsAndVisits(Owner owner) {
250: List pets = this .petsByOwnerQuery.execute(owner.getId()
251: .intValue());
252: Iterator pi = pets.iterator();
253: while (pi.hasNext()) {
254: JdbcPet pet = (JdbcPet) pi.next();
255: owner.addPet(pet);
256: loadVisits(pet);
257: }
258: }
259:
260: /**
261: * Method to retrieve a <code>List</code> of <code>Owner</code>s and
262: * their <code>Pet</code> and <code>Visit</code> data.
263: *
264: * @param owners
265: * <code>List</code>.
266: * @see #loadPetsAndVisits(Owner)
267: */
268: protected void loadOwnersPetsAndVisits(List owners) {
269: Iterator oi = owners.iterator();
270: while (oi.hasNext()) {
271: Owner owner = (Owner) oi.next();
272: loadPetsAndVisits(owner);
273: }
274: }
275:
276: /**
277: * Retrieve and set the identity for the given entity, assuming that the
278: * last executed insert affected that entity and generated an auto-increment
279: * value for it.
280: *
281: * @param entity
282: * the entity object to retrieved the id for
283: * @see #getIdentityQuery
284: */
285: protected void retrieveIdentity(Entity entity) {
286: entity.setId(new Integer(getJdbcTemplate().queryForInt(
287: getIdentityQuery())));
288: }
289:
290: /**
291: * Return the identity query for the particular database: a query that can
292: * be used to retrieve the id of a row that has just been inserted.
293: *
294: * @return the identity query
295: */
296: protected abstract String getIdentityQuery();
297:
298: // ************* Operation Objects section ***************
299:
300: /**
301: * Base class for all <code>Vet</code> Query Objects.
302: */
303: protected class VetsQuery extends MappingSqlQuery {
304:
305: /**
306: * Create a new instance of VetsQuery.
307: *
308: * @param ds
309: * the DataSource to use for the query
310: * @param sql
311: * SQL string to use for the query
312: */
313: protected VetsQuery(DataSource ds, String sql) {
314: super (ds, sql);
315: }
316:
317: /**
318: * Create a new instance of VetsQuery that returns all vets.
319: *
320: * @param ds
321: * the DataSource to use for the query
322: */
323: protected VetsQuery(DataSource ds) {
324: super (ds,
325: "SELECT id,first_name,last_name FROM vets ORDER BY last_name,first_name");
326: compile();
327: }
328:
329: protected Object mapRow(ResultSet rs, int rownum)
330: throws SQLException {
331: Vet vet = new Vet();
332: vet.setId(new Integer(rs.getInt("id")));
333: vet.setFirstName(rs.getString("first_name"));
334: vet.setLastName(rs.getString("last_name"));
335: return vet;
336: }
337: }
338:
339: /**
340: * All <code>Vet</code>s specialties Query Object.
341: */
342: protected class SpecialtiesQuery extends MappingSqlQuery {
343:
344: /**
345: * Create a new instance of SpecialtiesQuery.
346: *
347: * @param ds
348: * the DataSource to use for the query
349: */
350: protected SpecialtiesQuery(DataSource ds) {
351: super (ds, "SELECT id,name FROM specialties");
352: compile();
353: }
354:
355: protected Object mapRow(ResultSet rs, int rownum)
356: throws SQLException {
357: Specialty specialty = new Specialty();
358: specialty.setId(new Integer(rs.getInt("id")));
359: specialty.setName(rs.getString("name"));
360: return specialty;
361: }
362: }
363:
364: /**
365: * A particular <code>Vet</code>'s specialties Query Object.
366: */
367: protected class VetSpecialtiesQuery extends MappingSqlQuery {
368:
369: /**
370: * Create a new instance of VetSpecialtiesQuery.
371: *
372: * @param ds
373: * the DataSource to use for the query
374: */
375: protected VetSpecialtiesQuery(DataSource ds) {
376: super (ds,
377: "SELECT specialty_id FROM vet_specialties WHERE vet_id=?");
378: declareParameter(new SqlParameter(Types.INTEGER));
379: compile();
380: }
381:
382: protected Object mapRow(ResultSet rs, int rownum)
383: throws SQLException {
384: return new Integer(rs.getInt("specialty_id"));
385: }
386: }
387:
388: /**
389: * Abstract base class for all <code>Owner</code> Query Objects.
390: */
391: protected abstract class OwnersQuery extends MappingSqlQuery {
392:
393: /**
394: * Create a new instance of OwnersQuery.
395: *
396: * @param ds
397: * the DataSource to use for the query
398: * @param sql
399: * SQL string to use for the query
400: */
401: protected OwnersQuery(DataSource ds, String sql) {
402: super (ds, sql);
403: }
404:
405: protected Object mapRow(ResultSet rs, int rownum)
406: throws SQLException {
407: Owner owner = new Owner();
408: owner.setId(new Integer(rs.getInt("id")));
409: owner.setFirstName(rs.getString("first_name"));
410: owner.setLastName(rs.getString("last_name"));
411: owner.setAddress(rs.getString("address"));
412: owner.setCity(rs.getString("city"));
413: owner.setTelephone(rs.getString("telephone"));
414: return owner;
415: }
416: }
417:
418: /**
419: * <code>Owner</code>s by last name Query Object.
420: */
421: protected class OwnersByNameQuery extends OwnersQuery {
422:
423: /**
424: * Create a new instance of OwnersByNameQuery.
425: *
426: * @param ds
427: * the DataSource to use for the query
428: */
429: protected OwnersByNameQuery(DataSource ds) {
430: super (
431: ds,
432: "SELECT id,first_name,last_name,address,city,telephone FROM owners WHERE last_name like ?");
433: declareParameter(new SqlParameter(Types.VARCHAR));
434: compile();
435: }
436: }
437:
438: /**
439: * <code>Owner</code> by id Query Object.
440: */
441: protected class OwnerQuery extends OwnersQuery {
442:
443: /**
444: * Create a new instance of OwnerQuery.
445: *
446: * @param ds
447: * the DataSource to use for the query
448: */
449: protected OwnerQuery(DataSource ds) {
450: super (ds,
451: "SELECT id,first_name,last_name,address,city,telephone FROM owners WHERE id=?");
452: declareParameter(new SqlParameter(Types.INTEGER));
453: compile();
454: }
455: }
456:
457: /**
458: * <code>Owner</code> Insert Object.
459: */
460: protected class OwnerInsert extends SqlUpdate {
461:
462: /**
463: * Create a new instance of OwnerInsert.
464: *
465: * @param ds
466: * the DataSource to use for the insert
467: */
468: protected OwnerInsert(DataSource ds) {
469: super (ds, "INSERT INTO owners VALUES(?,?,?,?,?,?,?)");
470: declareParameter(new SqlParameter(Types.INTEGER));
471: declareParameter(new SqlParameter(Types.VARCHAR));
472: declareParameter(new SqlParameter(Types.VARCHAR));
473: declareParameter(new SqlParameter(Types.VARCHAR));
474: declareParameter(new SqlParameter(Types.VARCHAR));
475: declareParameter(new SqlParameter(Types.VARCHAR));
476: declareParameter(new SqlParameter(Types.TIMESTAMP));
477: compile();
478: }
479:
480: protected void insert(Owner owner) {
481: Object[] objs = new Object[] { null, owner.getFirstName(),
482: owner.getLastName(), owner.getAddress(),
483: owner.getCity(), owner.getTelephone(),
484: new Timestamp(new java.util.Date().getTime()) };
485: super .update(objs);
486: retrieveIdentity(owner);
487: }
488: }
489:
490: /**
491: * <code>Owner</code> Update Object.
492: */
493: protected class OwnerUpdate extends SqlUpdate {
494:
495: /**
496: * Create a new instance of OwnerUpdate.
497: *
498: * @param ds
499: * the DataSource to use for the update
500: */
501: protected OwnerUpdate(DataSource ds) {
502: super (
503: ds,
504: "UPDATE owners SET first_name=?,last_name=?,address=?,city=?,telephone=?,version=? WHERE id=?");
505: declareParameter(new SqlParameter(Types.VARCHAR));
506: declareParameter(new SqlParameter(Types.VARCHAR));
507: declareParameter(new SqlParameter(Types.VARCHAR));
508: declareParameter(new SqlParameter(Types.VARCHAR));
509: declareParameter(new SqlParameter(Types.VARCHAR));
510: declareParameter(new SqlParameter(Types.TIMESTAMP));
511: declareParameter(new SqlParameter(Types.INTEGER));
512: compile();
513: }
514:
515: /**
516: * Method to update an <code>Owner</code>'s data.
517: *
518: * @param owner
519: * to update
520: * @return the number of rows affected by the update
521: */
522: protected int update(Owner owner) {
523: return this .update(new Object[] { owner.getFirstName(),
524: owner.getLastName(), owner.getAddress(),
525: owner.getCity(), owner.getTelephone(),
526: new Timestamp(new java.util.Date().getTime()),
527: owner.getId() });
528: }
529: }
530:
531: /**
532: * Abstract base class for all <code>Pet</code> Query Objects.
533: */
534: protected abstract class PetsQuery extends MappingSqlQuery {
535:
536: /**
537: * Create a new instance of PetsQuery.
538: *
539: * @param ds
540: * the DataSource to use for the query
541: * @param sql
542: * SQL string to use for the query
543: */
544: protected PetsQuery(DataSource ds, String sql) {
545: super (ds, sql);
546: }
547:
548: protected Object mapRow(ResultSet rs, int rownum)
549: throws SQLException {
550: JdbcPet pet = new JdbcPet();
551: pet.setId(new Integer(rs.getInt("id")));
552: pet.setName(rs.getString("name"));
553: pet.setBirthDate(rs.getDate("birth_date"));
554: pet.setTypeId(rs.getInt("type_id"));
555: pet.setOwnerId(rs.getInt("owner_id"));
556: return pet;
557: }
558: }
559:
560: /**
561: * <code>Pet</code>s by <code>Owner</code> Query Object.
562: */
563: protected class PetsByOwnerQuery extends PetsQuery {
564:
565: /**
566: * Create a new instance of PetsByOwnerQuery.
567: *
568: * @param ds
569: * the DataSource to use for the query
570: */
571: protected PetsByOwnerQuery(DataSource ds) {
572: super (ds,
573: "SELECT id,name,birth_date,type_id,owner_id FROM pets WHERE owner_id=?");
574: declareParameter(new SqlParameter(Types.INTEGER));
575: compile();
576: }
577: }
578:
579: /**
580: * <code>Pet</code> by id Query Object.
581: */
582: protected class PetQuery extends PetsQuery {
583:
584: /**
585: * Create a new instance of PetQuery.
586: *
587: * @param ds
588: * the DataSource to use for the query
589: */
590: protected PetQuery(DataSource ds) {
591: super (ds,
592: "SELECT id,name,birth_date,type_id,owner_id FROM pets WHERE id=?");
593: declareParameter(new SqlParameter(Types.INTEGER));
594: compile();
595: }
596: }
597:
598: /**
599: * <code>Pet</code> Insert Object.
600: */
601: protected class PetInsert extends SqlUpdate {
602:
603: /**
604: * Create a new instance of PetInsert.
605: *
606: * @param ds
607: * the DataSource to use for the insert
608: */
609: protected PetInsert(DataSource ds) {
610: super (ds, "INSERT INTO pets VALUES(?,?,?,?,?,?)");
611: declareParameter(new SqlParameter(Types.INTEGER));
612: declareParameter(new SqlParameter(Types.VARCHAR));
613: declareParameter(new SqlParameter(Types.DATE));
614: declareParameter(new SqlParameter(Types.INTEGER));
615: declareParameter(new SqlParameter(Types.INTEGER));
616: declareParameter(new SqlParameter(Types.TIMESTAMP));
617: compile();
618: }
619:
620: /**
621: * Method to insert a new <code>Pet</code>.
622: *
623: * @param pet
624: * to insert
625: */
626: protected void insert(Pet pet) {
627: Object[] objs = new Object[] { null, pet.getName(),
628: new java.sql.Date(pet.getBirthDate().getTime()),
629: pet.getType().getId(), pet.getOwner().getId(),
630: new Timestamp(new java.util.Date().getTime()) };
631: super .update(objs);
632: retrieveIdentity(pet);
633: }
634: }
635:
636: /**
637: * <code>Pet</code> Update Object.
638: */
639: protected class PetUpdate extends SqlUpdate {
640:
641: /**
642: * Create a new instance of PetUpdate.
643: *
644: * @param ds
645: * the DataSource to use for the update
646: */
647: protected PetUpdate(DataSource ds) {
648: super (ds,
649: "UPDATE pets SET name=?,birth_date=?,type_id=?,owner_id=?,version=? WHERE id=?");
650: declareParameter(new SqlParameter(Types.VARCHAR));
651: declareParameter(new SqlParameter(Types.DATE));
652: declareParameter(new SqlParameter(Types.INTEGER));
653: declareParameter(new SqlParameter(Types.INTEGER));
654: declareParameter(new SqlParameter(Types.TIMESTAMP));
655: declareParameter(new SqlParameter(Types.INTEGER));
656: compile();
657: }
658:
659: /**
660: * Method to update an <code>Pet</code>'s data.
661: *
662: * @param pet
663: * to update
664: * @return the number of rows affected by the update
665: */
666: protected int update(Pet pet) {
667: return this .update(new Object[] { pet.getName(),
668: new java.sql.Date(pet.getBirthDate().getTime()),
669: pet.getType().getId(), pet.getOwner().getId(),
670: new Timestamp(new Date().getTime()), pet.getId() });
671: }
672: }
673:
674: /**
675: * All <code>Pet</code> types Query Object.
676: */
677: protected class PetTypesQuery extends MappingSqlQuery {
678:
679: /**
680: * Create a new instance of PetTypesQuery.
681: *
682: * @param ds
683: * the DataSource to use for the query
684: */
685: protected PetTypesQuery(DataSource ds) {
686: super (ds, "SELECT id,name FROM types ORDER BY name");
687: compile();
688: }
689:
690: protected Object mapRow(ResultSet rs, int rownum)
691: throws SQLException {
692: PetType type = new PetType();
693: type.setId(new Integer(rs.getInt("id")));
694: type.setName(rs.getString("name"));
695: return type;
696: }
697: }
698:
699: /**
700: * <code>Visit</code>s by <code>Pet</code> Query Object.
701: */
702: protected class VisitsQuery extends MappingSqlQuery {
703:
704: /**
705: * Create a new instance of VisitsQuery.
706: *
707: * @param ds
708: * the DataSource to use for the query
709: */
710: protected VisitsQuery(DataSource ds) {
711: super (ds,
712: "SELECT id,visit_date,description FROM visits WHERE pet_id=?");
713: declareParameter(new SqlParameter(Types.INTEGER));
714: compile();
715: }
716:
717: protected Object mapRow(ResultSet rs, int rownum)
718: throws SQLException {
719: Visit visit = new Visit();
720: visit.setId(new Integer(rs.getInt("id")));
721: visit.setDate(rs.getDate("visit_date"));
722: visit.setDescription(rs.getString("description"));
723: return visit;
724: }
725: }
726:
727: /**
728: * <code>Visit</code> Insert Object.
729: */
730: protected class VisitInsert extends SqlUpdate {
731:
732: /**
733: * Create a new instance of VisitInsert.
734: *
735: * @param ds
736: * the DataSource to use for the insert
737: */
738: protected VisitInsert(DataSource ds) {
739: super (ds, "INSERT INTO visits VALUES(?,?,?,?,?)");
740: declareParameter(new SqlParameter(Types.INTEGER));
741: declareParameter(new SqlParameter(Types.INTEGER));
742: declareParameter(new SqlParameter(Types.DATE));
743: declareParameter(new SqlParameter(Types.VARCHAR));
744: declareParameter(new SqlParameter(Types.TIMESTAMP));
745: compile();
746: }
747:
748: /**
749: * Method to insert a new <code>Visit</code>.
750: *
751: * @param visit
752: * to insert
753: */
754: protected void insert(Visit visit) {
755: super .update(new Object[] { null, visit.getPet().getId(),
756: new java.sql.Date(visit.getDate().getTime()),
757: visit.getDescription(),
758: new Timestamp(new java.util.Date().getTime()) });
759: retrieveIdentity(visit);
760: }
761: }
762:
763: }
|