View Javadoc

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