View Javadoc

1   package org.sourceforge.vlibrary.user.dao;
2   
3   import java.sql.Types;
4   import java.util.ArrayList;
5   import java.util.Iterator;
6   import java.util.List;
7   import java.util.Locale;
8   import java.util.Map;
9   
10  import org.apache.commons.lang.StringUtils;
11  import org.apache.log4j.Logger;
12  import org.sourceforge.vlibrary.Constants;
13  import org.sourceforge.vlibrary.exceptions.LibraryException;
14  import org.sourceforge.vlibrary.user.domain.Location;
15  import org.sourceforge.vlibrary.util.Utils;
16  import org.springframework.context.support.ResourceBundleMessageSource;
17  import org.springframework.jdbc.core.JdbcTemplate;
18  
19  /**
20   * @version $Revision$ $Date$
21   */
22  
23  public class LocationDAO {
24      /** log4j Logger */
25      private static Logger logger =
26       Logger.getLogger(LocationDAO.class.getName());
27  
28      /**
29       * SQL injected by spring
30       */
31      private String selectAllLocationsSQL;
32      private String selectLocationOnDescriptionSQL;
33      private String insertLocationSQL;
34  
35      private JdbcTemplate jdbcTemplate;
36      private ResourceBundleMessageSource resourceBundleMessageSource;
37  
38      /**
39       * Used for Spring Dependency Injection
40       */
41      public void setResourceBundleMessageSource(
42              ResourceBundleMessageSource resourceBundleMessageSource) {
43          this.resourceBundleMessageSource = resourceBundleMessageSource;
44      }
45  
46      /**
47       * Used for Spring Dependency Injection
48       */
49      public void setInsertLocationSQL(String insertLocationSQL) {
50          this.insertLocationSQL = insertLocationSQL;
51      }
52  
53      /**
54       * Used for Spring Dependency Injection
55       */
56      public void setSelectAllLocationsSQL(String selectAllLocationsSQL) {
57          this.selectAllLocationsSQL = selectAllLocationsSQL;
58      }
59  
60      /**
61       * Used for Spring Dependency Injection
62       */
63      public void setSelectLocationOnDescriptionSQL(
64              String selectLocationOnDescriptionSQL) {
65          this.selectLocationOnDescriptionSQL = selectLocationOnDescriptionSQL;
66      }
67  
68      /**
69       * Used for Spring Dependency Injection
70       * @param jdbcTemplate The jdbcTemplate to set.
71       */
72      public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
73          this.jdbcTemplate = jdbcTemplate;
74      }
75  
76      /**
77       * Looks up a location in the database by its description. Returned Long
78       * instance wraps the subject ID.  Returns null if there is no subject with
79       * the given description.
80       *
81       * @param subject Subject instance whose (trimmed) description field is
82       * used as the key in the lookup
83       * @return Long object wrapping the subject id, if found; null otherwise
84       * @throws LibraryException if subject is null or has null description
85       */
86      public Long getLocationIdByDescription(Location location) throws LibraryException {
87          if (location == null || location.getDescription() == null) {
88              String message = resourceBundleMessageSource.getMessage(
89                 "obtaining.location.by.description",
90                  new Object[] { location==null?"":location.toString()}, Locale.US);
91              logger.error(message);
92              throw new LibraryException( message );
93          }
94  
95          if (logger.isDebugEnabled()) {
96              logger.debug( resourceBundleMessageSource.getMessage(
97                 "obtaining.location.by.description",
98                 new Object[] { location.toString()}, Locale.US));
99          }
100 
101         List rows;
102         Long id = null;
103         try {
104             location.setDescription(location.getDescription().trim());
105             rows = jdbcTemplate.queryForList(selectLocationOnDescriptionSQL,
106                  new Object[] { StringUtils.replace(location.getDescription(),
107                  Constants.APOSTROPHE, Constants.ESCAPED_APOSTROPHE)});
108         } catch (Exception se) {
109             String errString = resourceBundleMessageSource.getMessage(
110                "db.error.obtaining.location.by.description",
111                 new Object[] { location.toString()}, Locale.US);
112             logger.error(errString,se);
113             throw new LibraryException(errString,se);
114         }
115 
116         Iterator it = rows.iterator();
117         if (it.hasNext()) {
118             Map record = (Map)it.next();
119             id = new Long(((Integer) Utils.getIgnoreCase(
120                     record,"ID")).longValue());
121         }
122 
123         if (logger.isDebugEnabled()) {
124             logger.debug(resourceBundleMessageSource.getMessage(
125                "obtaining.subject.by.description.successful",
126                 new Object[] { location.toString()}, Locale.US));
127         }
128 
129         return id;
130     }
131 
132     /**
133      * Inserts a new location record into the database, using description field
134      * in location input parameter.  ID will be generated by the database engine
135      * and is not updated on the input parameter.
136      *
137      * @param subject The new Subject to insert
138      * @throws LibraryException if required fields are missing or an SQL error
139      * occurs
140      */
141     public void insertNewLocationRecord(Location location) throws LibraryException {
142         if (location == null || location.getDescription() == null) {
143             String message = resourceBundleMessageSource.getMessage(
144                     "error.inserting.new.location.record",
145              new Object[] {location==null?null:location.getDescription()},
146              Locale.US);
147             logger.error(message);
148             throw new LibraryException(message);
149         }
150 
151         if (logger.isDebugEnabled()) {
152             logger.debug( resourceBundleMessageSource.getMessage(
153                     "inserting.new.location.record",
154              new Object[] { location.toString()},
155              Locale.US));
156         }
157 
158         try {
159             jdbcTemplate.update(insertLocationSQL,
160              new Object [] {
161                 StringUtils.replace(location.getDescription(),
162                  Constants.APOSTROPHE,
163                  Constants.ESCAPED_APOSTROPHE)
164             },
165              new int[] {Types.VARCHAR}
166             );
167         } catch (Exception se) {
168             String errString = resourceBundleMessageSource.getMessage(
169                     "db.error.inserting.new.location.record",
170              new Object[] { location.toString()},
171              Locale.US);
172             logger.error(errString,se);
173             throw new LibraryException(errString,se);
174         }
175 
176         if (logger.isDebugEnabled()) {
177             logger.debug(resourceBundleMessageSource.getMessage(
178                     "inserting.new.location.record.successful",
179              new Object[] {location.toString()},
180              Locale.US));
181         }
182 
183     }
184 
185     /**
186      * Inserts a location into the database and updates the ID of the
187      * input location parameter.
188      *
189      * First checks to see if a location with same description exists.
190      * If so, no insert is performed, but id is updated to the id of
191      * the previously existing record. If the location is new, a record
192      * with the given description is inserted and the ID of the input
193      * location instance is updated to the ID of the newly inserted record.
194      *
195      * @param location The Location to insert
196      * @return fully populated Location object, incl location id
197      * @throws LibraryException if location is null or has null description
198      * or a database access error occurs
199      */
200     public Location insert(Location location) throws LibraryException {
201         if (location == null || location.getDescription() == null) {
202             String message = resourceBundleMessageSource.getMessage(
203                     "error.inserting.new.location.record",
204              new Object[] { ""},
205              Locale.US);
206             logger.error(message);
207             throw new LibraryException( message );
208         }
209 
210         if (logger.isDebugEnabled()) {
211             logger.debug(resourceBundleMessageSource.getMessage(
212                     "inserting.location",
213              new Object[] { location.toString()},
214              Locale.US));
215         }
216 
217         Long id = getLocationIdByDescription(location);
218         if (id != null) {
219             location.setId(id.longValue());
220             return location;
221         }
222 
223         insertNewLocationRecord(location);
224         id = getLocationIdByDescription(location);
225         if (id != null) {
226             location.setId(id.longValue());
227         } else {
228             String errString =
229              (resourceBundleMessageSource.getMessage(
230                      "db.error.obtaining.inserted.location.by.description",
231              new Object[] { location.toString()},
232              Locale.US));
233             logger.error(errString );
234             throw new LibraryException( errString );
235         }
236 
237         if (logger.isDebugEnabled()) {
238             logger.debug(resourceBundleMessageSource.getMessage(
239                     "location.insert.successful",
240              new Object[] {location.toString()},
241              Locale.US));
242         }
243         return location;
244     }
245 
246     /**
247      * Gets the description of the dropoff location with the given ID
248      *
249      * @param id ID of the location
250      * @return human-readable description of the dropoff location
251      * @throws LibraryException if there is no location with the given ID or an exception occurs retrieving data
252      */
253     public String getLocationDescription(long id) throws LibraryException {
254         final ArrayList<Location> locations = getLocations();
255         String out = null;
256         for (Location location : locations) {
257             if (location.getId() == id) {
258                 out = location.getDescription();
259                 break;
260             }
261         }
262         if (out == null) {
263             String errString = resourceBundleMessageSource
264                 .getMessage("error.retrieving.location.description",
265                             new Object[] {
266                                 Long.toString(id)
267                             }, Locale.US);
268             logger.error(errString);
269             throw new LibraryException(errString);
270         }
271         return out;
272     }
273 
274     /**
275      * Retrieves all locations from the database
276      *
277      * @return ArrayList of Location instances, if none found returns an empty
278      * ArrayList
279      * @throws LibraryException
280      */
281     public ArrayList<Location> getLocations() throws LibraryException {
282         if (logger.isDebugEnabled()) {
283             logger.debug(resourceBundleMessageSource.getMessage(
284                     "retrieving.locations",
285              null,
286              Locale.US));
287         }
288         ArrayList<Location> outList = new ArrayList<Location>();
289         try {
290             List rows = jdbcTemplate.queryForList(selectAllLocationsSQL);
291             if (rows != null ) {
292                 Iterator it = rows.iterator();
293                 while (it.hasNext()) {
294                     Map row = (Map) it.next();
295                     long id = ((Integer) Utils.getIgnoreCase(row, "ID")).longValue();
296                     String description = (String) Utils.getIgnoreCase(row,"DESCRIPTION");
297                     outList.add(new Location(id, description));
298                 }
299             } else {
300                 logger.debug(resourceBundleMessageSource.getMessage(
301                         "locations.retrieve.empty",
302                  null,
303                  Locale.US));
304             }
305         } catch (Exception ex) {
306             String errString = resourceBundleMessageSource.getMessage(
307                     "error.retrieving.locations",
308              null,
309              Locale.US);
310             logger.error(errString,ex);
311             throw new LibraryException(errString, ex);
312         }
313 
314         if (logger.isDebugEnabled()) {
315             logger.debug( resourceBundleMessageSource.getMessage(
316                     "locations.retrieve.successful",
317              new Object[] { outList },
318              Locale.US));
319         }
320         return outList;
321     }
322 
323     /**
324      * Walks input ArrayList, looking up and setting ID properties for
325      * each Location in the list.  Lookup is based on description property.
326      * If a Location is not found, a new entry is inserted into the database
327      * and the ID of the newly created record is assigned to the instance.
328      *
329      * @param locations ArrayList of Locations
330      * @exception LibraryException
331      */
332     public void setLocationIds(ArrayList<Location> locations) throws LibraryException {
333         if (locations == null) {
334             String errString = resourceBundleMessageSource.getMessage(
335                     "error.setting.location.ids",
336              new Object[] { locations },
337              Locale.US);
338             logger.error(errString);
339             throw new LibraryException(errString);
340         }
341 
342         if (logger.isDebugEnabled()) {
343             logger.debug(resourceBundleMessageSource.getMessage(
344                     "setting.location.ids",
345              new Object[] {locations},
346              Locale.US));
347         }
348 
349         Location curLoc = null;
350         for (int i=0;i<locations.size();i++) {
351             Location loc = new Location();
352             curLoc = locations.get(i);
353             loc.setDescription(curLoc.getDescription());
354             insert(loc); // will just set ID if location already exists
355             curLoc.setId(loc.getId());
356             loc = null;
357         }
358         curLoc = null;
359 
360         if (logger.isDebugEnabled()) {
361             logger.debug(resourceBundleMessageSource.getMessage(
362                     "setting.location.ids.successful",
363              new Object[] {locations},
364              Locale.US));
365         }
366     }
367 
368 }