View Javadoc
1   /******************************************************************************
2    * SetsDaoJdbc.java - Implement the Dao interface for the Sets
3    *
4    * PicMan - The BuckoSoft Picture Manager in Java
5    * Copyright(c) 2005 - Dick Balaska
6    * 
7    */
8   package com.buckosoft.PicMan.db;
9   
10  import java.sql.ResultSet;
11  import java.sql.SQLException;
12  import java.sql.Types;
13  import java.util.Date;
14  import java.util.Iterator;
15  import java.util.List;
16  
17  import javax.sql.DataSource;
18  
19  import org.apache.commons.logging.Log;
20  import org.apache.commons.logging.LogFactory;
21  import org.springframework.dao.DataAccessException;
22  import org.springframework.jdbc.core.SqlParameter;
23  import org.springframework.jdbc.object.MappingSqlQuery;
24  import org.springframework.jdbc.object.SqlUpdate;
25  
26  import com.buckosoft.PicMan.domain.Set;
27  
28  /** Implement the Dao Interface for the {@link com.buckosoft.PicMan.domain.Set}s.
29   * @author Dick Balaska
30   * @since 2005/07/30
31   * @version $Revision: 1.1 $ <br> $Date: 2013/12/26 01:25:41 $
32   * @see <a href="http://cvs.buckosoft.com/Projects/java/PicMan/PicMan/src/com/buckosoft/PicMan/db/SetsDaoJdbc.java">SetsDaoJdbc.java</a>
33   */
34  public class SetsDaoJdbc implements SetsDao {
35  
36  	/** Logger for this class and subclasses */
37  	private static final boolean DEBUG = false;
38  	protected final Log logger = LogFactory.getLog(getClass());
39  
40  	private DataSource ds;
41  
42  	private	List<Set>	setsCache = null;
43  	
44  	/** Set the reference to the JDBC datasource.
45  	 * @param ds The datasource as configured by Spring.
46  	 * @see <a href="http://cvs.buckosoft.com/Projects/java/PicMan/PicMan/WebContent/WEB-INF/picManDatabase/picManDatabase.xml">picManDatabase.xml</a>
47  	 */ 
48  	public void setDataSource(DataSource ds) {
49  		if (DEBUG)
50  			logger.info("Set Datasource.");
51          this.ds = ds;
52      }
53  
54  	/* (non-Javadoc)
55  	 * @see com.buckosoft.PicMan.db.SetsDao#getSets()
56  	 */
57  	public List<Set> getSets() throws DataAccessException {
58  		if (setsCache != null)
59  			return(setsCache);
60  		if (DEBUG)
61  			logger.info("getSets()");
62          SetsQuery sq = new SetsQuery(ds);
63      	@SuppressWarnings("unchecked")
64          List<Set> l = sq.execute();
65  		l.add(0, new Set(Set.FUNC_SID_DATE, Set.FUNC_NAME_DATE));
66  		l.add(0, new Set(Set.FUNC_SID_ROOT, Set.FUNC_NAME_ROOT));
67      	setSetsCache(l);
68          return(l);
69  	}
70  
71  	private synchronized void setSetsCache(List<Set> list) {
72  		this.setsCache = list;
73  	}
74  
75  	/* (non-Javadoc)
76  	 * @see com.buckosoft.PicMan.db.SetsDao#getSetsClone()
77  	 */
78  	public List<Set> getSetsClone() {
79          SetsQuery sq = new SetsQuery(ds);
80      	@SuppressWarnings("unchecked")
81          List<Set> l = sq.execute();
82  		l.add(0, new Set(Set.FUNC_SID_DATE, Set.FUNC_NAME_DATE));
83  		l.add(0, new Set(Set.FUNC_SID_ROOT, Set.FUNC_NAME_ROOT));
84          return(l);
85  	}
86  
87  	/* (non-Javadoc)
88  	 * @see com.buckosoft.PicMan.db.SetsDao#getInactiveSets()
89  	 */
90  	public List<Set> getInactiveSets() throws DataAccessException {
91          SetsQuery sq = new SetsQuery(ds);
92      	@SuppressWarnings("unchecked")
93          List<Set> inactiveSets = sq.execute();
94  		Iterator<Set> siter = inactiveSets.iterator();
95  		while (siter.hasNext()) {
96  			Set set = siter.next();
97  			if (set.isActive())
98  				siter.remove();
99  		}
100 		return(inactiveSets);
101 	}
102 
103 	/* (non-Javadoc)
104 	 * @see com.buckosoft.PicMan.db.SetsDao#getActiveSets()
105 	 */
106 	public List<Set> getActiveSets() throws DataAccessException {
107         SetsQuery sq = new SetsQuery(ds);
108     	@SuppressWarnings("unchecked")
109         List<Set> activeSets = sq.execute();
110 		Iterator<Set> siter = activeSets.iterator();
111 		while (siter.hasNext()) {
112 			Set set = siter.next();
113 			if (!set.isActive())
114 				siter.remove();
115 		}
116 		return(activeSets);
117 	}
118 
119 	/* (non-Javadoc)
120 	 * @see com.buckosoft.PicMan.db.SetsDao#getSetCount()
121 	 */
122 	public int	getSetCount() throws DataAccessException {
123 		if (setsCache == null)
124 			getSets();
125 		return(setsCache.size());
126 	}
127 
128 	/* (non-Javadoc)
129 	 * @see com.buckosoft.PicMan.db.SetsDao#getSet(int)
130 	 */
131 	public	Set	getSet(int sid) throws DataAccessException {
132 		if (setsCache == null)
133 			getSets();
134 		Iterator<Set> iter = setsCache.iterator();
135 		while (iter.hasNext()) {
136 			Set set = iter.next();
137 			if (set.getSid() == sid)
138 				return(set);
139 		}
140 		return(null);
141 	}
142 
143 	/* (non-Javadoc)
144 	 * @see com.buckosoft.PicMan.db.SetsDao#getSet(java.lang.String)
145 	 */
146 	public	Set	getSet(String setName) throws DataAccessException {
147 		if (setsCache == null)
148 			getSets();
149 		Iterator<Set> iter = setsCache.iterator();
150 		while (iter.hasNext()) {
151 			Set set = iter.next();
152 			if (set.getName().equals(setName))
153 				return(set);
154 		}
155 		return(null);
156 	}
157 	/** Update this <code>Set</code> in the database
158 	 * @param set The Set to store
159 	 */
160 	public void storeSet(Set set) throws DataAccessException {
161 		if (set.getSid() <= 0)
162 			return;			// throw up!
163 		setSetsCache(null);
164 		SetsUpdate su = new SetsUpdate(ds, set);
165 		su.update(set);			
166 //		SetInsert si = new SetInsert(ds);
167 //		si.insert(set);
168 	}
169 
170 	/* (non-Javadoc)
171 	 * @see com.buckosoft.PicMan.db.SetsDao#setSets(java.util.List)
172 	 */
173 	public void setSets(List<Set> sets) throws DataAccessException {
174 		setSetsCache(null);
175 		SetsUpdate su = new SetsUpdate(ds);
176 		su.update(sets);
177 	}
178 
179 	/* (non-Javadoc)
180 	 * @see com.buckosoft.PicMan.db.SetsDao#addSet(com.buckosoft.PicMan.domain.Set)
181 	 */
182 	public void addSet(Set set) throws DataAccessException {
183 		set.setSid(getNextAvailableSetNumber());
184 		setSetsCache(null);
185 		SetInsert si = new SetInsert(ds);
186 		si.insert(set);
187 	}
188 
189 	/* (non-Javadoc)
190 	 * @see com.buckosoft.PicMan.db.SetsDao#deleteSet(com.buckosoft.PicMan.domain.Set)
191 	 */
192 	public	void	deleteSet(Set s) throws DataAccessException {
193 		setSetsCache(null);
194 		SetDelete sd = new SetDelete(ds);
195 		sd.delete(s);
196 	}
197 
198 	private	int	getNextAvailableSetNumber() {
199 		int high = 1;
200 		for (Set set : getSets()) {
201 			if (set.getSid() > high)
202 				high = set.getSid();
203 		}
204 		return(high+1);
205 	}
206 	
207 	/**
208 	 * <code>Set</code> Query object.
209 	 */
210 	class SetsQuery extends MappingSqlQuery {
211 
212 		SetsQuery(DataSource ds) {
213             super(ds, "SELECT * from sets ORDER BY name");
214             compile();
215         }
216  
217         protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
218         	Set s = new Set();
219         	s.setSid(rs.getInt("sid"));
220         	s.setName(rs.getString("name"));
221         	s.setDescription(rs.getString("description"));
222         	s.setActive(rs.getBoolean("active"));
223         	s.setMetaSet(rs.getBoolean("metaSet"));
224         	s.setMicroSet(rs.getBoolean("microSet"));
225         	s.setNanoSet(rs.getBoolean("nanoSet"));
226         	try {
227 				s.getEditDate().setTime(rs.getTimestamp("editDate").getTime());
228 			} catch (java.sql.SQLException e) {
229 				s.setEditDate(new Date(0));
230 			}
231         	return(s);
232         }
233 	}
234 
235 	/**
236 	 * <code>Set</code> Update Object.
237 	 */
238 	protected class SetsUpdate extends SqlUpdate {
239 		private	DataSource	ds;
240 		
241 		/**
242 		 * Create a new instance of OwnerUpdate.
243 		 * @param ds the DataSource to use for the update
244 		 */
245 		protected SetsUpdate(DataSource ds) {
246 			this.ds = ds;
247 		}
248 
249 		protected SetsUpdate(DataSource ds, Set set) {
250 			super(ds, "UPDATE sets SET name=?, description=?, active=?, metaSet=?, microSet=?, nanoSet=?, editDate=? WHERE sid = ? LIMIT 1");
251 			declareParameter(new SqlParameter(Types.VARCHAR));
252 			declareParameter(new SqlParameter(Types.VARCHAR));
253 			declareParameter(new SqlParameter(Types.TINYINT));
254 			declareParameter(new SqlParameter(Types.TINYINT));
255 			declareParameter(new SqlParameter(Types.TINYINT));
256 			declareParameter(new SqlParameter(Types.TINYINT));
257 			declareParameter(new SqlParameter(Types.TIMESTAMP));
258 			declareParameter(new SqlParameter(Types.INTEGER));
259 			compile();
260 		}
261 		/**
262 		 * Method to update the <code>Set</code>'s data.
263 		 * @param sets The sets that should be stored
264 		 * @return 0
265 		 */
266 		protected int update(List<Set> sets) {
267 			// Empty the existing table
268 			SqlUpdate sf = new SqlUpdate(ds, "TRUNCATE TABLE sets");
269 			sf.compile();
270 			int ret = sf.update();
271 			logger.info("sf.empty() returned" + ret);
272 
273 			// Save each of our attributes
274 			SetInsert si = new SetInsert(ds);
275 			Iterator<Set> it = sets.iterator();
276 			while (it.hasNext())
277 				si.insert(it.next());
278 			return(0);
279 		}
280 
281 		protected int update(Set set) {
282 			return this.update(new Object[] {
283 					set.getName(),
284 					set.getDescription(), 
285 					new Integer(set.isActive() ? 1 : 0),
286 					new Integer(set.isMetaSet() ? 1 : 0),
287 					new Integer(set.isMicroSet() ? 1 : 0),
288 					new Integer(set.isNanoSet() ? 1 : 0),
289 					set.getEditDate(),
290 					new Integer(set.getSid()),
291 			});
292 		}
293 	}
294 
295 
296 	/**
297 	 * <code>Set</code> Insert Object.
298 	 */
299 	protected class SetInsert extends SqlUpdate {
300 
301 		/**
302 		 * Create a new instance of SetInsert.
303 		 * @param ds the DataSource to use for the insert
304 		 */
305 		protected SetInsert(DataSource ds) {
306 			super(ds, "REPLACE INTO sets VALUES(?,?,?,?,?,?,?,?)");
307 			declareParameter(new SqlParameter(Types.INTEGER));
308 			declareParameter(new SqlParameter(Types.VARCHAR));
309 			declareParameter(new SqlParameter(Types.VARCHAR));
310 			declareParameter(new SqlParameter(Types.TINYINT));
311 			declareParameter(new SqlParameter(Types.TINYINT));
312 			declareParameter(new SqlParameter(Types.TINYINT));
313 			declareParameter(new SqlParameter(Types.TINYINT));
314 			declareParameter(new SqlParameter(Types.TIMESTAMP));
315 			compile();
316 		}
317 
318 		protected void insert(Set s) {
319 			Object[] objs = new Object[] {
320 				new Integer(s.getSid()),
321 				s.getName(), s.getDescription(),
322 				new Integer(s.isActive() ? 1 : 0),
323 				new Integer(s.isMetaSet() ? 1 : 0),
324 				new Integer(s.isMicroSet() ? 1 : 0),
325 				new Integer(s.isNanoSet() ? 1 : 0),
326 				s.getEditDate(),
327 				};
328 			super.update(objs);
329 		}
330 	}
331 	/**
332 	 * <code>Set</code> Delete Object.
333 	 */
334 	protected class SetDelete extends SqlUpdate {
335 		
336 		/**
337 		 * Create a new instance of SetDelete.
338 		 * @param ds the DataSource to use for the delete
339 		 */
340 		protected SetDelete(DataSource ds) {
341 			super(ds, "DELETE FROM sets WHERE name = (?)");
342 			declareParameter(new SqlParameter(Types.VARCHAR));
343 			compile();
344 		}
345 		
346 		protected void delete(Set set) {
347 			Object[] objs = new Object[] {
348 					set.getName()};
349 			super.update(objs);
350 		}
351 	}
352 }