Java > Open Source Codes > org > springframework > samples > petclinic > jdbc > AbstractJdbcClinic


1 package org.springframework.samples.petclinic.jdbc;
2
3 import java.sql.ResultSet ;
4 import java.sql.SQLException ;
5 import java.sql.Types ;
6 import java.util.ArrayList ;
7 import java.util.Collection ;
8 import java.util.HashMap ;
9 import java.util.Iterator ;
10 import java.util.List ;
11 import java.util.Map ;
12
13 import javax.sql.DataSource ;
14
15 import org.springframework.dao.DataAccessException;
16 import org.springframework.jdbc.core.SqlParameter;
17 import org.springframework.jdbc.core.support.JdbcDaoSupport;
18 import org.springframework.jdbc.object.MappingSqlQuery;
19 import org.springframework.jdbc.object.SqlUpdate;
20 import org.springframework.orm.ObjectRetrievalFailureException;
21 import org.springframework.samples.petclinic.Clinic;
22 import org.springframework.samples.petclinic.Entity;
23 import org.springframework.samples.petclinic.Owner;
24 import org.springframework.samples.petclinic.Pet;
25 import org.springframework.samples.petclinic.PetType;
26 import org.springframework.samples.petclinic.Specialty;
27 import org.springframework.samples.petclinic.Vet;
28 import org.springframework.samples.petclinic.Visit;
29 import org.springframework.samples.petclinic.util.EntityUtils;
30
31 /**
32  * Base class for JDBC implementations of the Clinic interface.
33  *
34  * @author Ken Krebs
35  * @author Juergen Hoeller
36  * @author Rob Harrop
37  */
38 public abstract class AbstractJdbcClinic extends JdbcDaoSupport implements Clinic, CachingClinic {
39
40     private VetsQuery vetsQuery;
41     private SpecialtiesQuery specialtiesQuery;
42     private VetSpecialtiesQuery vetSpecialtiesQuery;
43     private OwnersByNameQuery ownersByNameQuery;
44     private OwnerQuery ownerQuery;
45     private OwnerInsert ownerInsert;
46     private OwnerUpdate ownerUpdate;
47     private PetsByOwnerQuery petsByOwnerQuery;
48     private PetQuery petQuery;
49     private PetInsert petInsert;
50     private PetUpdate petUpdate;
51     private PetTypesQuery petTypesQuery;
52     private VisitsQuery visitsQuery;
53     private VisitInsert visitInsert;
54
55     private final List vets = new ArrayList ();
56
57
58     protected void initDao() {
59         this.vetsQuery = new VetsQuery(getDataSource());
60         this.specialtiesQuery = new SpecialtiesQuery(getDataSource());
61         this.vetSpecialtiesQuery = new VetSpecialtiesQuery(getDataSource());
62         this.petTypesQuery = new PetTypesQuery(getDataSource());
63         this.ownersByNameQuery = new OwnersByNameQuery(getDataSource());
64         this.ownerQuery = new OwnerQuery(getDataSource());
65         this.ownerInsert = new OwnerInsert(getDataSource());
66         this.ownerUpdate = new OwnerUpdate(getDataSource());
67         this.petsByOwnerQuery = new PetsByOwnerQuery(getDataSource());
68         this.petQuery = new PetQuery(getDataSource());
69         this.petInsert = new PetInsert(getDataSource());
70         this.petUpdate = new PetUpdate(getDataSource());
71         this.visitsQuery = new VisitsQuery(getDataSource());
72         this.visitInsert = new VisitInsert(getDataSource());
73     }
74
75     public void refreshVetsCache() throws DataAccessException {
76         synchronized (this.vets) {
77             logger.info("Refreshing vets cache");
78             
79             // Retrieve the list of all vets.
80 this.vets.clear();
81             this.vets.addAll(this.vetsQuery.execute());
82
83             // Retrieve the list of all possible specialties.
84 List specialties = this.specialtiesQuery.execute();
85
86                 // Build each vet's list of specialties.
87 Iterator vi = this.vets.iterator();
88             while (vi.hasNext()) {
89                 Vet vet = (Vet) vi.next();
90                 List vetSpecialtiesIds = this.vetSpecialtiesQuery.execute(vet.getId().intValue());
91                 Iterator vsi = vetSpecialtiesIds.iterator();
92                 while (vsi.hasNext()) {
93                     int specialtyId = ((Integer ) vsi.next()).intValue();
94                     Specialty specialty = (Specialty) EntityUtils.getById(specialties, Specialty.class, specialtyId);
95                     vet.addSpecialty(specialty);
96                 }
97             }
98         }
99     }
100
101
102     // START of Clinic implementation section *******************************
103
104     public Collection getVets() throws DataAccessException {
105         synchronized (this.vets) {
106             if (this.vets.isEmpty()) {
107                 refreshVetsCache();
108             }
109             return this.vets;
110         }
111     }
112
113     public Collection getPetTypes() throws DataAccessException {
114         return this.petTypesQuery.execute();
115     }
116
117     /** Method loads owners plus pets and visits if not already loaded */
118     public Collection findOwners(String lastName) throws DataAccessException {
119         List owners = this.ownersByNameQuery.execute(lastName + "%");
120         loadOwnersPetsAndVisits(owners);
121         return owners;
122     }
123
124     /** Method loads an owner plus pets and visits if not already loaded */
125     public Owner loadOwner(int id) throws DataAccessException {
126         Owner owner = (Owner) this.ownerQuery.findObject(id);
127         if (owner == null) {
128             throw new ObjectRetrievalFailureException(Owner.class, new Integer (id));
129         }
130         loadPetsAndVisits(owner);
131         return owner;
132     }
133
134     public Pet loadPet(int id) throws DataAccessException {
135         JdbcPet pet = (JdbcPet) this.petQuery.findObject(id);
136         if (pet == null) {
137             throw new ObjectRetrievalFailureException(Pet.class, new Integer (id));
138         }
139         Owner owner = loadOwner(pet.getOwnerId());
140         owner.addPet(pet);
141         loadVisits(pet);
142         return pet;
143     }
144
145     public void storeOwner(Owner owner) throws DataAccessException {
146         if (owner.isNew()) {
147             this.ownerInsert.insert(owner);
148         }
149         else {
150             this.ownerUpdate.update(owner);
151         }
152     }
153
154     public void storePet(Pet pet) throws DataAccessException {
155         if (pet.isNew()) {
156             this.petInsert.insert(pet);
157         }
158         else {
159             this.petUpdate.update(pet);
160         }
161     }
162
163     public void storeVisit(Visit visit) throws DataAccessException {
164         if (visit.isNew()) {
165             this.visitInsert.insert(visit);
166         }
167         else {
168             throw new UnsupportedOperationException ("Visit update not supported");
169         }
170     }
171
172     // END of Clinic implementation section *******************************
173
174
175     /**
176      * Method maps a List of Entity objects keyed to their ids.
177      * @param list List containing Entity objects
178      * @return Map containing Entity objects
179      */
180     protected final Map mapEntityList(List list) {
181         Map map = new HashMap ();
182         Iterator iterator = list.iterator();
183         while (iterator.hasNext()) {
184             Entity entity = (Entity) iterator.next();
185             map.put(entity.getId(), entity);
186         }
187         return map;
188     }
189
190     /**
191      * Method to retrieve the <code>Visit</code> data for a <code>Pet</code>.
192      */
193     protected void loadVisits(JdbcPet pet) {
194         pet.setType((PetType) EntityUtils.getById(getPetTypes(), PetType.class, pet.getTypeId()));
195         List visits = this.visitsQuery.execute(pet.getId().intValue());
196         Iterator vi = visits.iterator();
197         while (vi.hasNext()) {
198             Visit visit = (Visit) vi.next();
199             pet.addVisit(visit);
200         }
201     }
202
203     /**
204      * Method to retrieve the <code>Pet</code> and <code>Visit</code>
205      * data for an <code>Owner</code>.
206      */
207     protected void loadPetsAndVisits(Owner owner) {
208         List pets = this.petsByOwnerQuery.execute(owner.getId().intValue());
209         Iterator pi = pets.iterator();
210         while (pi.hasNext()) {
211             JdbcPet pet = (JdbcPet) pi.next();
212             owner.addPet(pet);
213             loadVisits(pet);
214         }
215     }
216
217     /**
218      * Method to retrieve a <code>List</code> of <code>Owner</code>s
219      * and their <code>Pet</code> and <code>Visit</code> data.
220      * @param owners <code>List</code>.
221      * @see #loadPetsAndVisits(Owner)
222      */
223     protected void loadOwnersPetsAndVisits(List owners) {
224         Iterator oi = owners.iterator();
225         while (oi.hasNext()) {
226             Owner owner = (Owner) oi.next();
227             loadPetsAndVisits(owner);
228         }
229     }
230
231     /**
232      * Retrieve and set the identity for the given entity,
233      * assuming that the last executed insert affected that entity
234      * and generated an auto-increment value for it.
235      * @param entity the entity object to retrieved the id for
236      * @see #getIdentityQuery
237      */
238     protected void retrieveIdentity(Entity entity) {
239         entity.setId(new Integer (getJdbcTemplate().queryForInt(getIdentityQuery())));
240     }
241
242     /**
243      * Return the identity query for the particular database:
244      * a query that can be used to retrieve the id of a row
245      * that has just been inserted.
246      * @return the identity query
247      */
248     protected abstract String getIdentityQuery();
249
250
251     // ************* Operation Objects section ***************
252
253     /**
254      * Base class for all <code>Vet</code> Query Objects.
255      */
256     protected class VetsQuery extends MappingSqlQuery {
257
258         /**
259          * Create a new instance of VetsQuery.
260          * @param ds the DataSource to use for the query
261          * @param sql SQL string to use for the query
262          */
263         protected VetsQuery(DataSource ds, String sql) {
264             super(ds, sql);
265         }
266
267         /**
268          * Create a new instance of VetsQuery that returns all vets.
269          * @param ds the DataSource to use for the query
270          */
271         protected VetsQuery(DataSource ds) {
272             super(ds, "SELECT id,first_name,last_name FROM vets ORDER BY last_name,first_name");
273             compile();
274         }
275
276         protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
277             Vet vet = new Vet();
278             vet.setId(new Integer (rs.getInt("id")));
279             vet.setFirstName(rs.getString("first_name"));
280             vet.setLastName(rs.getString("last_name"));
281             return vet;
282         }
283     }
284
285
286     /**
287      * All <code>Vet</code>s specialties Query Object.
288      */
289     protected class SpecialtiesQuery extends MappingSqlQuery {
290
291         /**
292          * Create a new instance of SpecialtiesQuery.
293          * @param ds the DataSource to use for the query
294          */
295         protected SpecialtiesQuery(DataSource ds) {
296             super(ds, "SELECT id,name FROM specialties");
297             compile();
298         }
299
300         protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
301             Specialty specialty = new Specialty();
302             specialty.setId(new Integer (rs.getInt("id")));
303             specialty.setName(rs.getString("name"));
304             return specialty;
305         }
306     }
307
308
309     /**
310      * A particular <code>Vet</code>'s specialties Query Object.
311      */
312     protected class VetSpecialtiesQuery extends MappingSqlQuery {
313
314         /**
315          * Create a new instance of VetSpecialtiesQuery.
316          * @param ds the DataSource to use for the query
317          */
318         protected VetSpecialtiesQuery(DataSource ds) {
319             super(ds, "SELECT specialty_id FROM vet_specialties WHERE vet_id=?");
320             declareParameter(new SqlParameter(Types.INTEGER));
321             compile();
322         }
323
324         protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
325             return new Integer (rs.getInt("specialty_id"));
326         }
327     }
328
329
330     /**
331      * Abstract base class for all <code>Owner</code> Query Objects.
332      */
333     protected abstract class OwnersQuery extends MappingSqlQuery {
334
335         /**
336          * Create a new instance of OwnersQuery.
337          * @param ds the DataSource to use for the query
338          * @param sql SQL string to use for the query
339          */
340         protected OwnersQuery(DataSource ds, String sql) {
341             super(ds, sql);
342         }
343
344         protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
345             Owner owner = new Owner();
346             owner.setId(new Integer (rs.getInt("id")));
347             owner.setFirstName(rs.getString("first_name"));
348             owner.setLastName(rs.getString("last_name"));
349             owner.setAddress(rs.getString("address"));
350             owner.setCity(rs.getString("city"));
351             owner.setTelephone(rs.getString("telephone"));
352             return owner;
353         }
354     }
355
356
357     /**
358      * <code>Owner</code>s by last name Query Object.
359      */
360     protected class OwnersByNameQuery extends OwnersQuery {
361
362         /**
363          * Create a new instance of OwnersByNameQuery.
364          * @param ds the DataSource to use for the query
365          */
366         protected OwnersByNameQuery(DataSource ds) {
367             super(ds, "SELECT id,first_name,last_name,address,city,telephone FROM owners WHERE last_name like ?");
368             declareParameter(new SqlParameter(Types.VARCHAR));
369             compile();
370         }
371     }
372
373
374     /**
375      * <code>Owner</code> by id Query Object.
376      */
377     protected class OwnerQuery extends OwnersQuery {
378
379         /**
380          * Create a new instance of OwnerQuery.
381          * @param ds the DataSource to use for the query
382          */
383         protected OwnerQuery(DataSource ds) {
384             super(ds, "SELECT id,first_name,last_name,address,city,telephone FROM owners WHERE id=?");
385             declareParameter(new SqlParameter(Types.INTEGER));
386             compile();
387         }
388     }
389
390
391     /**
392      * <code>Owner</code> Insert Object.
393      */
394     protected class OwnerInsert extends SqlUpdate {
395
396         /**
397          * Create a new instance of OwnerInsert.
398          * @param ds the DataSource to use for the insert
399          */
400         protected OwnerInsert(DataSource ds) {
401             super(ds, "INSERT INTO owners VALUES(?,?,?,?,?,?)");
402             declareParameter(new SqlParameter(Types.INTEGER));
403             declareParameter(new SqlParameter(Types.VARCHAR));
404             declareParameter(new SqlParameter(Types.VARCHAR));
405             declareParameter(new SqlParameter(Types.VARCHAR));
406             declareParameter(new SqlParameter(Types.VARCHAR));
407             declareParameter(new SqlParameter(Types.VARCHAR));
408             compile();
409         }
410
411         protected void insert(Owner owner) {
412             Object [] objs = new Object [] {
413                 null, owner.getFirstName(), owner.getLastName(),
414                 owner.getAddress(), owner.getCity(), owner.getTelephone()};
415             super.update(objs);
416             retrieveIdentity(owner);
417         }
418     }
419
420
421     /**
422      * <code>Owner</code> Update Object.
423      */
424     protected class OwnerUpdate extends SqlUpdate {
425
426         /**
427          * Create a new instance of OwnerUpdate.
428          * @param ds the DataSource to use for the update
429          */
430         protected OwnerUpdate(DataSource ds) {
431             super(ds, "UPDATE owners SET first_name=?,last_name=?,address=?,city=?,telephone=? WHERE id=?");
432             declareParameter(new SqlParameter(Types.VARCHAR));
433             declareParameter(new SqlParameter(Types.VARCHAR));
434             declareParameter(new SqlParameter(Types.VARCHAR));
435             declareParameter(new SqlParameter(Types.VARCHAR));
436             declareParameter(new SqlParameter(Types.VARCHAR));
437             declareParameter(new SqlParameter(Types.INTEGER));
438             compile();
439         }
440
441         /**
442          * Method to update an <code>Owner</code>'s data.
443          * @param owner to update
444          * @return the number of rows affected by the update
445          */
446         protected int update(Owner owner) {
447             return this.update(new Object [] {
448                 owner.getFirstName(), owner.getLastName(), owner.getAddress(),
449                 owner.getCity(), owner.getTelephone(), owner.getId()});
450         }
451     }
452
453
454     /**
455      * Abstract base class for all <code>Pet</code> Query Objects.
456      */
457     protected abstract class PetsQuery extends MappingSqlQuery {
458
459         /**
460          * Create a new instance of PetsQuery.
461          * @param ds the DataSource to use for the query
462          * @param sql SQL string to use for the query
463          */
464         protected PetsQuery(DataSource ds, String sql) {
465             super(ds, sql);
466         }
467
468         protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
469             JdbcPet pet = new JdbcPet();
470             pet.setId(new Integer (rs.getInt("id")));
471             pet.setName(rs.getString("name"));
472             pet.setBirthDate(rs.getDate("birth_date"));
473             pet.setTypeId(rs.getInt("type_id"));
474             pet.setOwnerId(rs.getInt("owner_id"));
475             return pet;
476         }
477     }
478
479
480     /**
481      * <code>Pet</code>s by <code>Owner</code> Query Object.
482      */
483     protected class PetsByOwnerQuery extends PetsQuery {
484
485         /**
486          * Create a new instance of PetsByOwnerQuery.
487          * @param ds the DataSource to use for the query
488          */
489         protected PetsByOwnerQuery(DataSource ds) {
490             super(ds, "SELECT id,name,birth_date,type_id,owner_id FROM pets WHERE owner_id=?");
491             declareParameter(new SqlParameter(Types.INTEGER));
492             compile();
493         }
494     }
495
496
497     /**
498      * <code>Pet</code> by id Query Object.
499      */
500     protected class PetQuery extends PetsQuery {
501
502         /**
503          * Create a new instance of PetQuery.
504          * @param ds the DataSource to use for the query
505          */
506         protected PetQuery(DataSource ds) {
507             super(ds, "SELECT id,name,birth_date,type_id,owner_id FROM pets WHERE id=?");
508             declareParameter(new SqlParameter(Types.INTEGER));
509             compile();
510         }
511     }
512
513
514     /**
515      * <code>Pet</code> Insert Object.
516      */
517     protected class PetInsert extends SqlUpdate {
518
519         /**
520          * Create a new instance of PetInsert.
521          * @param ds the DataSource to use for the insert
522          */
523         protected PetInsert(DataSource ds) {
524             super(ds, "INSERT INTO pets VALUES(?,?,?,?,?)");
525             declareParameter(new SqlParameter(Types.INTEGER));
526             declareParameter(new SqlParameter(Types.VARCHAR));
527             declareParameter(new SqlParameter(Types.DATE));
528             declareParameter(new SqlParameter(Types.INTEGER));
529             declareParameter(new SqlParameter(Types.INTEGER));
530             compile();
531         }
532
533         /**
534          * Method to insert a new <code>Pet</code>.
535          * @param pet to insert
536          */
537         protected void insert(Pet pet) {
538             Object [] objs = new Object [] {
539                 null, pet.getName(), new java.sql.Date (pet.getBirthDate().getTime()),
540                 pet.getType().getId(), pet.getOwner().getId()};
541             super.update(objs);
542             retrieveIdentity(pet);
543         }
544     }
545
546
547     /**
548      * <code>Pet</code> Update Object.
549      */
550     protected class PetUpdate extends SqlUpdate {
551
552         /**
553          * Create a new instance of PetUpdate.
554          * @param ds the DataSource to use for the update
555          */
556         protected PetUpdate(DataSource ds) {
557             super(ds, "UPDATE pets SET name=?,birth_date=?,type_id=?,owner_id=? WHERE id=?");
558             declareParameter(new SqlParameter(Types.VARCHAR));
559             declareParameter(new SqlParameter(Types.DATE));
560             declareParameter(new SqlParameter(Types.INTEGER));
561             declareParameter(new SqlParameter(Types.INTEGER));
562             declareParameter(new SqlParameter(Types.INTEGER));
563             compile();
564         }
565
566         /**
567          * Method to update an <code>Pet</code>'s data.
568          * @param pet to update
569          * @return the number of rows affected by the update
570          */
571         protected int update(Pet pet) {
572             return this.update(new Object [] {
573                 pet.getName(), new java.sql.Date (pet.getBirthDate().getTime()),
574                 pet.getType().getId(), pet.getOwner().getId(), pet.getId()});
575         }
576     }
577
578
579     /**
580      * All <code>Pet</code> types Query Object.
581      */
582     protected class PetTypesQuery extends MappingSqlQuery {
583
584         /**
585          * Create a new instance of PetTypesQuery.
586          * @param ds the DataSource to use for the query
587          */
588         protected PetTypesQuery(DataSource ds) {
589             super(ds, "SELECT id,name FROM types ORDER BY name");
590             compile();
591         }
592
593         protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
594             PetType type = new PetType();
595             type.setId(new Integer (rs.getInt("id")));
596             type.setName(rs.getString("name"));
597             return type;
598         }
599     }
600
601
602     /**
603      * <code>Visit</code>s by <code>Pet</code> Query Object.
604      */
605     protected class VisitsQuery extends MappingSqlQuery {
606
607         /**
608          * Create a new instance of VisitsQuery.
609          * @param ds the DataSource to use for the query
610          */
611         protected VisitsQuery(DataSource ds) {
612             super(ds, "SELECT id,visit_date,description FROM visits WHERE pet_id=?");
613             declareParameter(new SqlParameter(Types.INTEGER));
614             compile();
615         }
616
617         protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
618             Visit visit = new Visit();
619             visit.setId(new Integer (rs.getInt("id")));
620             visit.setDate(rs.getDate("visit_date"));
621             visit.setDescription(rs.getString("description"));
622             return visit;
623         }
624     }
625
626
627     /**
628      * <code>Visit</code> Insert Object.
629      */
630     protected class VisitInsert extends SqlUpdate {
631
632         /**
633          * Create a new instance of VisitInsert.
634          * @param ds the DataSource to use for the insert
635          */
636         protected VisitInsert(DataSource ds) {
637             super(ds, "INSERT INTO visits VALUES(?,?,?,?)");
638             declareParameter(new SqlParameter(Types.INTEGER));
639             declareParameter(new SqlParameter(Types.INTEGER));
640             declareParameter(new SqlParameter(Types.DATE));
641             declareParameter(new SqlParameter(Types.VARCHAR));
642             compile();
643         }
644
645         /**
646          * Method to insert a new <code>Visit</code>.
647          * @param visit to insert
648          */
649         protected void insert(Visit visit) {
650             super.update(new Object [] {
651                 null, visit.getPet().getId(), new java.sql.Date (visit.getDate().getTime()),
652                 visit.getDescription()});
653             retrieveIdentity(visit);
654         }
655     }
656
657 }
658

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags