View Javadoc
1   /******************************************************************************
2    * PicsDaoJdbc.java - Implement the Dao interface for the Pics
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.Calendar;
14  import java.util.Date;
15  import java.util.HashMap;
16  import java.util.Iterator;
17  import java.util.LinkedList;
18  import java.util.List;
19  
20  import javax.sql.DataSource;
21  
22  import org.apache.commons.logging.Log;
23  import org.apache.commons.logging.LogFactory;
24  import org.springframework.dao.DataAccessException;
25  import org.springframework.jdbc.core.SqlParameter;
26  import org.springframework.jdbc.object.MappingSqlQuery;
27  import org.springframework.jdbc.object.SqlFunction;
28  import org.springframework.jdbc.object.SqlUpdate;
29  
30  import com.buckosoft.PicMan.domain.Pic;
31  
32  /** Implement the Dao Interface for the {@link com.buckosoft.PicMan.domain.Pic}s.
33   * @author Dick Balaska
34   * @since 2005/07/31
35   * @see <a href="http://cvs.buckosoft.com/Projects/java/PicMan/PicMan/src/com/buckosoft/PicMan/db/PicsDaoJdbc.java">PicsDaoJdbc.java</a>
36   */
37  public class PicsDaoJdbc implements PicsDao {
38  
39  	private final static boolean DEBUG = false;
40  	private final Log logger = LogFactory.getLog(getClass());
41  	
42  	private DataSource ds;
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  		this.ds = ds;
50  	}
51  	
52  	/** Get a List of all the pics in the database
53  	 * @return The (probably big) List
54  	 */
55  	@SuppressWarnings("unchecked")
56  	public List<Pic> getPics() {
57  		if (DEBUG)
58  			logger.info("GetPicsList");
59  //		PicsQuery pq = new PicsQuery(ds);
60  //		return pq.execute();
61  		if (sql_getPicsListQUERY == null)
62  			sql_getPicsListQUERY = new PicsQuery(ds);
63  		return(sql_getPicsListQUERY.execute());
64  	}
65  	private	PicsQuery	sql_getPicsListQUERY = null;
66  
67  	/** Get a single named Pic
68  	 * @param name The name of the pic to fetch
69  	 */
70  	public Pic getPic(String name) {
71  //		if (DEBUG)
72  //			logger.info("getPic: " + name);
73  		if (sql_getPicQUERY == null)
74  			sql_getPicQUERY = new PicsQuery(ds, name);
75          return (Pic)sql_getPicQUERY.findObject(name);
76  	}
77  	private	PicsQuery	sql_getPicQUERY = null;
78  
79  	/** Get a single Pic
80  	 * @param pid The pid of the pic to fetch
81  	 */
82  	public Pic getPic(int pid) {
83  //		if (DEBUG)
84  //			logger.info("getPic: " + pid);
85  		if (sql_getPicPidQUERY == null)
86  			sql_getPicPidQUERY = new PicsQuery(ds, pid);
87          return (Pic)sql_getPicPidQUERY.findObject(pid);
88  	}
89  	private	PicsQuery	sql_getPicPidQUERY = null;
90  
91  	/* (non-Javadoc)
92  	 * @see com.buckosoft.PicMan.db.PicsDao#getNewestPic()
93  	 */
94  	public Pic getNewestPic() {
95  		if (DEBUG)
96  			logger.info("getNewestPic:");
97  		if (sql_getPicNewQUERY == null)
98  			sql_getPicNewQUERY = new PicsQuery(ds, ds);
99          return (Pic)sql_getPicNewQUERY.execute().get(0);
100 	}
101 	private	PicsQuery	sql_getPicNewQUERY = null;
102 
103 	/* (non-Javadoc)
104 	 * @see com.buckosoft.PicMan.db.PicsDao#getRandomPic()
105 	 */
106 	public Pic getRandomPic() {
107 		int max = this.getPicCount();
108 		long r = Math.round(Math.random()*max);
109 		return(getPic((int)r));
110 	}
111 
112 	/* (non-Javadoc)
113 	 * @see com.buckosoft.PicMan.db.PicsDao#getRandomPic(int)
114 	 */
115 	public Pic getRandomPic(int rid) {
116 		int max = this.getPicCount(rid);
117 		long r = Math.round(Math.random()*max);
118 		if (sql_getPicRandQUERY == null)
119 			sql_getPicRandQUERY = new PicsQuery(ds, rid, r);
120 		Pic p = (Pic)sql_getPicRandQUERY.findObject(rid, (int)r);
121 		if (DEBUG)
122 			logger.info("getRandomPic: " + rid + " " + r + " = " + p.getName());
123         return(p);
124 	}
125 	private	PicsQuery	sql_getPicRandQUERY = null;
126 
127 
128 	/** Get a list of Pics, wildcards supported
129 	 * @param name The name of the Pic to match (user must supply sql '%' for wildcards)
130 	 * @return The List
131 	 */
132 	@SuppressWarnings("unchecked")
133 	public List<Pic> getPics(String name) {
134 		if (DEBUG)
135 			logger.info("getPics: " + name);
136 		if (sql_getPicsQUERY == null)
137 			sql_getPicsQUERY = new PicsQuery(ds, name, true);
138         return (sql_getPicsQUERY.execute(name));
139 	}
140 	private	PicsQuery	sql_getPicsQUERY = null;
141 
142 	
143 	/* (non-Javadoc)
144 	 * @see com.buckosoft.PicMan.db.PicsDao#getPicsByMD5Sum(long)
145 	 */
146 	@Override
147 	@SuppressWarnings("unchecked")
148 	public List<Pic> getPicsByMD5Sum(long md5sum) {
149 		if (sql_getPicsMD5QUERY == null)
150 			sql_getPicsMD5QUERY = new PicMD5SumQuery(ds, 0);
151 		return(sql_getPicsMD5QUERY.execute(md5sum));
152 	}
153 	private	PicMD5SumQuery	sql_getPicsMD5QUERY = null;
154 
155 	/* (non-Javadoc)
156 	 * @see com.buckosoft.PicMan.db.PicsDao#getPics(java.util.List)
157 	 */
158 	public List<Pic> getPics(List<String> list) {
159 		LinkedList<Pic> picList = new LinkedList<Pic>();
160 		Iterator<String> iter = list.iterator();
161 		while (iter.hasNext()) {
162 			String s = iter.next();
163 			picList.add(getPic(s));
164 		}
165 		return(picList);
166 	}
167 
168 	/* (non-Javadoc)
169 	 * @see com.buckosoft.PicMan.db.PicsDao#getPicsInDir(int rid, java.lang.String)
170 	 */
171 	@SuppressWarnings("unchecked")
172 	public List<Pic> getPicsInDir(int rid, String dirName) {
173 		if (DEBUG)
174 			logger.info("getPics: " + dirName);
175 		if (sql_getPicsDirQUERY == null)
176 			sql_getPicsDirQUERY = new PicsQuery(ds, dirName, rid);
177         return (sql_getPicsDirQUERY.execute(new Object[] {dirName, new Integer(rid)}));
178 	}
179 	private	PicsQuery	sql_getPicsDirQUERY = null;
180 
181 	/*
182 	 * (non-Javadoc)
183 	 * @see com.buckosoft.PicMan.db.PicsDao#getPicsNewerThan(java.util.Calendar)
184 	 */
185 	@SuppressWarnings("unchecked")
186 	public List<Pic> getPicsNewerThan(Calendar calendar) {
187 		if (DEBUG)
188 			logger.info("getPicsNewerThan: " + calendar.getTime().toString());
189 		if (sql_getPicsNewerQUERY == null)
190 			sql_getPicsNewerQUERY = new PicsQuery(ds, calendar);
191         return (sql_getPicsNewerQUERY.execute(new Object[] {calendar.getTime()}));
192 	}
193 	private	PicsQuery	sql_getPicsNewerQUERY = null;
194 
195 	/* (non-Javadoc)
196 	 * @see com.buckosoft.PicMan.db.PicsDao#getPicsMap()
197 	 */
198 	public HashMap<String, Date> getPicsMap() {
199 		if (DEBUG)
200 			logger.info("GetPicsMap");
201 		List<Pic> l = getPics();
202 		HashMap<String, Date> hm = new HashMap<String, Date>();
203 		Iterator<Pic> i = l.iterator();
204 		Pic p;
205 		while (i.hasNext()) {
206 			p = (Pic)i.next();
207 			hm.put(p.getName(), p.getDate());	// need something for the value, might as well use timestamp
208 		}
209 		return(hm);
210 	}
211 
212 	/* (non-Javadoc)
213 	 * @see com.buckosoft.PicMan.db.PicsDao#getPicNames()
214 	 */
215 	@SuppressWarnings("unchecked")
216 	public List<String> getPicNames() {
217 		PicNameQuery pnq = new PicNameQuery(ds);
218 		return(pnq.execute());
219 	}
220 
221 	/* (non-Javadoc)
222 	 * @see com.buckosoft.PicMan.db.PicsDao#getPicNamesByDateFunc(java.lang.String, java.lang.String)
223 	 */
224 	@SuppressWarnings("unchecked")
225 	public List<String> getPicNamesByDateFunc(String operator, String operand) {
226 		PicNameQuery pnq = new PicNameQuery(ds, "DATE(`timestamp`)", operator, operand);
227 		return(pnq.execute());
228 	}
229 
230 
231 	/* (non-Javadoc)
232 	 * @see com.buckosoft.PicMan.db.PicsDao#getPicNamesByRootFunc(java.lang.String, int)
233 	 */
234 	@SuppressWarnings("unchecked")
235 	public List<String> getPicNamesByRootFunc(String operator, int rid) {
236 		PicNameQuery pnq = new PicNameQuery(ds, "rid", operator, rid);
237 		return(pnq.execute());
238 	}
239 
240 /*	@SuppressWarnings("unchecked")
241 	public List<String> getPicNamesInSet(Set set) {
242 		if (DEBUG)
243 			logger.info("Getting pics from set \"" + set.getName() + "\"!");
244 		PicsQuery pq = new PicsQuery(ds, set);
245 		return(pq.execute());   	
246 	}
247 */
248 
249 	/* (non-Javadoc)
250 	 * @see com.buckosoft.PicMan.db.PicsDao#addPic(com.buckosoft.PicMan.domain.Pic)
251 	 */
252 	public void	addPic(Pic pic) {
253 		if (DEBUG)
254 			logger.info("addPic: " + pic.getName());
255 		PicInsert pi = new PicInsert(ds);
256 		pi.insert(pic);
257 		Pic pic1 = getPic(pic.getName());
258 		pic.setPid(pic1.getPid());
259 		PicMD5SumUpdate psu = new PicMD5SumUpdate(ds);
260 		psu.update(pic);
261 	}
262 
263 
264 	/** Update this <code>Pic</code> in the database
265 	 * @param pic The Pic to store
266 	 */
267 	public void updatePic(Pic pic) throws DataAccessException {
268 		PicUpdate pu = new PicUpdate(ds);
269 		pu.update(pic);	
270 		PicMD5SumUpdate psu = new PicMD5SumUpdate(ds);
271 		psu.update(pic);
272 	}
273 	
274 	/* (non-Javadoc)
275 	 * @see com.buckosoft.PicMan.db.PicsDao#getMaxThumbCacheDirUsed()
276 	 */
277 	@SuppressWarnings("unchecked")
278 	public int getMaxThumbCacheDirUsed() {
279 		PicsQuery pq = new PicsQuery(ds, false);
280 		List<Pic> list = pq.execute();
281 		if (list.size() == 0)
282 			return(0);
283 		Pic pic = list.get(0);
284 		if (pic == null)
285 			return(0);
286 		return(pic.getCacheDir());
287 	}
288 
289 	public int getPicCount() {
290 		PicCount pc = new PicCount(ds);
291 		return(pc.run());
292 		
293 	}
294 	/* (non-Javadoc)
295 	 * @see com.buckosoft.PicMan.db.PicsDao#getPicCount(int)
296 	 */
297 	public int getPicCount(int rid) {
298 		PicCount pc = new PicCount(ds, rid, false);
299 		return(pc.run(rid));
300 	}
301 
302 
303 	/* (non-Javadoc)
304 	 * @see com.buckosoft.PicMan.db.PicsDao#getThumbCacheFillCount(int)
305 	 */
306 	public int getThumbCacheFillCount(int cacheDir) {
307 		PicCount pc = new PicCount(ds, cacheDir);
308 		return(pc.run(cacheDir));
309 	}
310 
311 	///////////////////////////////////////////////////////////////////////////
312 	/**
313 	 * Object to Query for Pics
314 	 */
315 	private class PicsQuery extends MappingSqlQuery {
316 		
317 		PicsQuery(DataSource ds) {
318 			super(ds, "SELECT * from pics");
319 			compile();
320 		}
321 		
322 		PicsQuery(DataSource ds, String name) {
323 			super(ds, "SELECT * from pics where name = ?");
324             declareParameter(new SqlParameter(Types.VARCHAR));
325 			compile();
326 		}
327 		PicsQuery(DataSource ds, int pid) {
328 			super(ds, "SELECT * from pics where pid = ?");
329             declareParameter(new SqlParameter(Types.INTEGER));
330 			compile();
331 		}
332 		PicsQuery(DataSource ds, String name, boolean unused) {
333 			super(ds, "SELECT * from pics where name like ?");
334             declareParameter(new SqlParameter(Types.VARCHAR));
335 			compile();
336 		}
337 /*		PicsQuery(DataSource ds, Set set) {
338 			super(ds, "SELECT * from pics where " + set.getName() + " != 0");
339 			compile();
340 		}
341 */
342 		PicsQuery(DataSource ds, Calendar calendar) {
343 			super(ds, "SELECT * from pics where timestamp > ?");
344 			declareParameter(new SqlParameter(Types.TIMESTAMP));
345 			compile();
346 		}
347 		PicsQuery(DataSource ds, String dirName, int rid) {
348 			super(ds, "SELECT * from pics where location = ? AND rid = ? ORDER BY name");
349             declareParameter(new SqlParameter(Types.VARCHAR));
350             declareParameter(new SqlParameter(Types.INTEGER));
351 			compile();			
352 		}
353 		PicsQuery(DataSource ds, DataSource unused) {
354 			super(ds, "SELECT * from pics ORDER BY timestamp DESC LIMIT 1");
355 			compile();			
356 		}
357 
358 /*		PicsQuery(DataSource ds, DataSource unused, boolean unused1) {
359 			super(ds, "SELECT * from pics ORDER BY RAND() LIMIT 1");
360 			compile();			
361 		}
362 */
363 		PicsQuery(DataSource ds, int rid, long r) {
364 			super(ds, "SELECT * from pics where rid = ? LIMIT ?, 1");
365             declareParameter(new SqlParameter(Types.INTEGER));
366             declareParameter(new SqlParameter(Types.INTEGER));
367 			compile();			
368 		}
369 
370 		PicsQuery(DataSource ds, boolean unused) {
371 			super(ds, "SELECT * from pics ORDER BY cacheDir DESC LIMIT 1");
372 			compile();			
373 		}
374 		protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
375 			Pic pic = new Pic();
376 			pic.setName(rs.getString("name"));
377 			if (false)
378 				logger.info("picName=" + pic.getName());
379 			try {
380 				pic.getDate().setTime(rs.getTimestamp("timestamp").getTime());
381 			} catch (Exception e) {}
382 			pic.setPid(rs.getInt("pid"));
383 			pic.setWidth(rs.getInt("width"));
384 			pic.setHeight(rs.getInt("height"));
385 			pic.setRid(rs.getInt("rid"));
386 			pic.setCacheDir(rs.getInt("cacheDir"));
387 			pic.setLocation(rs.getString("location"));
388 			return pic;
389 		}
390 	}
391 
392 	private class PicMD5SumQuery extends MappingSqlQuery {
393 		PicMD5SumQuery(DataSource ds, long sum) {
394 			super(ds, "SELECT * FROM pics p left join picmd5sums s on p.pid = s.pid WHERE s.md5sum = ?");
395             declareParameter(new SqlParameter(Types.BIGINT));
396 			compile();			
397 		}
398 		protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
399 			Pic pic = new Pic();
400 			pic.setName(rs.getString("name"));
401 			if (false)
402 				logger.info("picName=" + pic.getName());
403 			try {
404 				pic.getDate().setTime(rs.getTimestamp("timestamp").getTime());
405 			} catch (Exception e) {}
406 			pic.setPid(rs.getInt("pid"));
407 			pic.setWidth(rs.getInt("width"));
408 			pic.setHeight(rs.getInt("height"));
409 			pic.setRid(rs.getInt("rid"));
410 			pic.setCacheDir(rs.getInt("cacheDir"));
411 			pic.setLocation(rs.getString("location"));
412 			pic.setMd5(rs.getLong("md5sum"));
413 			return pic;
414 		}
415 
416 	}
417 
418 	/**
419 	 * <code>Pic</code> Query Object for just the name.
420 	 * @author dick
421 	 *
422 	 */
423 	private class PicNameQuery extends MappingSqlQuery {
424 
425 		PicNameQuery(DataSource ds) {
426 			super(ds, "SELECT name FROM pics");
427 			compile();
428 
429 		}
430 		PicNameQuery(DataSource ds, String column, String operator, String operand) {
431 			super(ds, "SELECT name FROM pics WHERE " +  column + " " + operator + " \"" + operand + "\"");
432 			compile();
433 			if (DEBUG)
434 				logger.info("PicNameQuery() WHERE " +  column + " " + operator + " \"" + operand + "\"");
435 
436 		}
437 		PicNameQuery(DataSource ds, String column, String operator, int operand) {
438 			super(ds, "SELECT name FROM pics WHERE " +  column + " " + operator + " \"" + operand + "\"");
439 			compile();
440 			if (DEBUG)
441 				logger.info("PicNameQuery() WHERE " +  column + " " + operator + " \"" + operand + "\"");
442 
443 		}
444 		
445 		protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
446 //			if (DEBUG)
447 //				logger.info("row=" + rowNum);
448 			return(rs.getString("name"));
449 		}
450 	}
451 	
452 	/**
453 	 * <code>Pic</code> Count Object.
454 	 */
455 	private class PicCount extends SqlFunction {
456 		
457 		PicCount(DataSource ds) {
458 			super(ds, "SELECT COUNT(*) from pics");
459 			compile();
460 		}
461 		/** Count the entries in a cacheDir
462 		 * @param ds
463 		 * @param cacheDir
464 		 */
465 		PicCount(DataSource ds, int cacheDir) {
466 			super(ds, "SELECT COUNT(*) from pics WHERE cacheDir = ?");
467 			declareParameter(new SqlParameter(Types.INTEGER));
468 			compile();
469 		}		
470 		/** Count the entries in a root
471 		 * @param ds
472 		 * @param rid The Root id to query
473 		 * @param unused a boolean
474 		 */
475 		PicCount(DataSource ds, int rid, boolean unused) {
476 			super(ds, "SELECT COUNT(*) from pics WHERE rid = ?");
477 			declareParameter(new SqlParameter(Types.INTEGER));
478 			compile();
479 		}		
480 	}
481 
482 	/**
483 	 * <code>Pic</code> Insert Object.
484 	 */
485 	private class PicInsert extends SqlUpdate {
486 		
487 		/**
488 		 * Create a new instance of PicInsert.
489 		 * @param ds the DataSource to use for the insert
490 		 */
491 		protected PicInsert(DataSource ds) {
492 			super(ds, "INSERT INTO pics VALUES(?,?,?,?,?,?,?,?)");
493 			declareParameter(new SqlParameter(Types.INTEGER));
494 			declareParameter(new SqlParameter(Types.VARCHAR));
495 			declareParameter(new SqlParameter(Types.INTEGER));
496 			declareParameter(new SqlParameter(Types.INTEGER));
497 			declareParameter(new SqlParameter(Types.INTEGER));
498 			declareParameter(new SqlParameter(Types.VARCHAR));
499 			declareParameter(new SqlParameter(Types.INTEGER));
500 			declareParameter(new SqlParameter(Types.TIMESTAMP));
501 			compile();
502 		}
503 
504 		protected void insert(Pic p) {
505 			Object[] objs = new Object[] {
506 					new Integer(0),
507 					p.getName(), new Integer(p.getWidth()), new Integer(p.getHeight()), 
508 					new Integer(p.getRid()), p.getLocation(),
509 					new Integer(p.getCacheDir()),
510 					p.getDate()
511 				};
512 			super.update(objs);
513 		}
514 	}
515 	///////////////////////////////////////////////////////////////////////////////////////////////
516 	/**
517 	 * <code>Pic</code> Update Object.
518 	 */
519 	private class PicUpdate extends SqlUpdate {
520 		//private	DataSource	ds;
521 		
522 		/**
523 		 * Create a new instance of PicUpdate.
524 		 * @param ds the DataSource to use for the update
525 		 */
526 		protected PicUpdate(DataSource ds) {
527 			super(ds, "UPDATE pics SET width=?, height=?, rid=?, location=?, cacheDir=?, timestamp=? WHERE name = ? LIMIT 1");
528 			declareParameter(new SqlParameter(Types.INTEGER));
529 			declareParameter(new SqlParameter(Types.INTEGER));
530 			declareParameter(new SqlParameter(Types.INTEGER));
531 			declareParameter(new SqlParameter(Types.VARCHAR));
532 			declareParameter(new SqlParameter(Types.INTEGER));
533 			declareParameter(new SqlParameter(Types.TIMESTAMP));
534 			declareParameter(new SqlParameter(Types.VARCHAR));
535 			compile();
536 		}
537 		/**
538 		 * Method to update the <code>Set</code>'s data.
539 		 * @param List sets The sets that should be stored
540 		 * @return 0
541 		 */
542 
543 		protected int update(Pic pic) {
544 			return this.update(new Object[] {
545 					new Integer(pic.getWidth()),
546 					new Integer(pic.getHeight()),
547 					new Integer(pic.getRid()),
548 					pic.getLocation(),
549 					new Integer(pic.getCacheDir()),
550 					pic.getDate(),
551 					pic.getName(),
552 			});
553 		}
554 	}
555 	
556 	private	class PicMD5SumUpdate extends SqlUpdate {
557 		protected PicMD5SumUpdate(DataSource ds) {
558 			super(ds, "REPLACE INTO picmd5sums VALUES(?,?) ");
559 			declareParameter(new SqlParameter(Types.INTEGER));
560 			declareParameter(new SqlParameter(Types.BIGINT));
561 		}
562 		protected void update(Pic p) {
563 			Object[] objs = new Object[] {
564 					new Integer(p.getPid()),
565 					new Long(p.getMd5())
566 					};
567 			this.update(objs);
568 		}
569 	}
570 	
571 }