1
2
3
4
5
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.Iterator;
14 import java.util.List;
15
16 import javax.sql.DataSource;
17
18 import org.apache.commons.logging.Log;
19 import org.apache.commons.logging.LogFactory;
20 import org.springframework.dao.DataAccessException;
21 import org.springframework.jdbc.core.SqlParameter;
22 import org.springframework.jdbc.object.MappingSqlQuery;
23 import org.springframework.jdbc.object.SqlFunction;
24 import org.springframework.jdbc.object.SqlUpdate;
25
26 import com.buckosoft.PicMan.domain.Chain;
27 import com.buckosoft.PicMan.domain.SetSize;
28
29
30
31
32
33
34
35 public class ChainsDaoJdbc implements ChainsDao {
36
37 private static final boolean DEBUG = false;
38 protected final Log logger = LogFactory.getLog(getClass());
39
40 private DataSource ds;
41
42 private List<Chain> chainsCache;
43
44
45
46
47 public void setDataSource(DataSource ds) {
48 if (DEBUG)
49 logger.info("Set Datasource.");
50 this.ds = ds;
51 }
52
53
54
55
56 public List<Chain> getChains() throws DataAccessException {
57 if (chainsCache != null)
58 return(chainsCache);
59 if (DEBUG)
60 logger.info("getChains()");
61 ChainsQuery sq = new ChainsQuery(ds);
62 @SuppressWarnings("unchecked")
63 List<Chain> l = sq.execute();
64 chainsCache = l;
65 return(l);
66 }
67
68
69
70
71 public Chain getChain(String name) throws DataAccessException {
72 if (DEBUG)
73 logger.info("getChain: " + name);
74 if (sql_getChainQUERY == null)
75 sql_getChainQUERY = new ChainsQuery(ds, name);
76 return (Chain)sql_getChainQUERY.findObject(name);
77
78 }
79 private ChainsQuery sql_getChainQUERY = null;
80
81
82
83
84 public Chain getChain(int cid) throws DataAccessException {
85 if (DEBUG)
86 logger.info("getChain: " + cid);
87 if (chainsCache != null) {
88 for (Chain c : chainsCache) {
89 if (c.getCid() == cid)
90 return(c);
91 }
92 }
93 if (sql_getChainCidQUERY == null)
94 sql_getChainCidQUERY = new ChainsQuery(ds, cid);
95 return (Chain)sql_getChainCidQUERY.findObject(cid);
96
97 }
98 private ChainsQuery sql_getChainCidQUERY = null;
99
100
101
102
103 private void addChain(Chain c) throws DataAccessException {
104 c.setCid(getChainCount());
105 if (sql_addChainINSERT == null)
106 sql_addChainINSERT = new ChainInsert(ds);
107 chainsCache = null;
108 sql_addChainINSERT.insert(c);
109 storeChainSetSizes(c);
110 }
111 private ChainInsert sql_addChainINSERT = null;
112
113 private void updateChain(Chain c) throws DataAccessException {
114 ChainUpdate cu = new ChainUpdate(ds);
115 chainsCache = null;
116 cu.update(c);
117 storeChainSetSizes(c);
118
119 }
120
121
122
123
124 public void storeChain(Chain chain) throws DataAccessException {
125 if (chain.getCid() == -1)
126 addChain(chain);
127 else
128 updateChain(chain);
129 }
130
131 private void storeChainSetSizes(Chain c) throws DataAccessException {
132 ChainSetSizesDelete cssd = new ChainSetSizesDelete(ds);
133 cssd.delete(c);
134 Iterator<SetSize> iter = c.getSetSizes().iterator();
135 while (iter.hasNext()) {
136 SetSize ss = (SetSize)iter.next();
137 if (sql_storeChainSetSizesINSERT == null)
138 sql_storeChainSetSizesINSERT = new ChainSetSizesInsert(ds);
139 sql_storeChainSetSizesINSERT.insert(c.getCid(), ss.getGuiSetSize());
140 }
141 }
142 private ChainSetSizesInsert sql_storeChainSetSizesINSERT = null;
143
144
145
146
147 public void deleteChain(Chain chain) throws DataAccessException {
148 ChainDelete sd = new ChainDelete(ds);
149 sd.delete(chain);
150 ChainSetSizesDelete cssd = new ChainSetSizesDelete(ds);
151 cssd.delete(chain);
152 chainsCache = null;
153 }
154
155 private void getChainSetSizes(Chain c) throws DataAccessException {
156 if (sql_getChainSetSizesQUERY == null)
157 sql_getChainSetSizesQUERY = new ChainSetSizesQuery(ds);
158 List<?> l = sql_getChainSetSizesQUERY.execute(c.getCid());
159 if (l.isEmpty())
160 return;
161 Iterator<?> iter = l.iterator();
162 while (iter.hasNext()) {
163 SetSize ss = (SetSize)iter.next();
164 c.addSetSize(ss);
165 }
166 }
167 private ChainSetSizesQuery sql_getChainSetSizesQUERY;
168
169
170
171
172 public int getChainCount() {
173 if (sql_getChainCountCOUNTr == null)
174 sql_getChainCountCOUNTr = new ChainCount(ds);
175 if (DEBUG)
176 logger.info("getChainCount:");
177 return(sql_getChainCountCOUNTr.run());
178 }
179 private ChainCount sql_getChainCountCOUNTr;
180
181
182
183
184
185 class ChainsQuery extends MappingSqlQuery {
186
187 ChainsQuery(DataSource ds) {
188 super(ds, "SELECT * from chains");
189 compile();
190 }
191 ChainsQuery(DataSource ds, String name) {
192 super(ds, "SELECT * from chains where name = ?");
193 declareParameter(new SqlParameter(Types.VARCHAR));
194 compile();
195 }
196
197 ChainsQuery(DataSource ds, int cid) {
198 super(ds, "SELECT * from chains where cid = ?");
199 declareParameter(new SqlParameter(Types.INTEGER));
200 compile();
201 }
202
203 protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
204 Chain c = new Chain();
205 c.setCid(rs.getInt("cid"));
206 c.setName(rs.getString("name"));
207 c.setShortName(rs.getString("shortName"));
208 c.setDescription(rs.getString("description"));
209 c.setActive(rs.getBoolean("active"));
210 c.setEngine(rs.getString("engine"));
211 c.setContactPrefix(rs.getString("contactPrefix"));
212 c.setContactDirectory(rs.getString("contactDirectory"));
213 c.setContactCount(rs.getInt("contactCount"));
214 c.setContactWidth(rs.getInt("contactWidth"));
215 c.setContactHeight(rs.getInt("contactHeight"));
216 c.setPicSelector(rs.getInt("picSelector"));
217 c.setRatingMin(rs.getInt("ratingMin"));
218 c.setRatingMax(rs.getInt("ratingMax"));
219 c.setShowUnusedSets(rs.getBoolean("showUnused"));
220
221 getChainSetSizes(c);
222 return(c);
223 }
224 }
225
226
227
228 class ChainCount extends SqlFunction {
229
230 ChainCount(DataSource ds) {
231 super(ds, "SELECT COUNT(*) from chains");
232 compile();
233 }
234 }
235
236
237
238
239
240 protected class ChainUpdate extends SqlUpdate {
241
242
243
244
245
246 protected ChainUpdate(DataSource ds) {
247 super(ds, "UPDATE chains SET name=?, shortName=?, description=?, active=?, engine=?, "
248 + "contactPrefix=?, contactDirectory=?, contactCount=?, contactWidth=?, contactHeight=?, "
249 + "picSelector=?, ratingMin=?, ratingMax=?, showUnused=? WHERE cid = ? LIMIT 1");
250 declareParameter(new SqlParameter(Types.VARCHAR));
251 declareParameter(new SqlParameter(Types.VARCHAR));
252 declareParameter(new SqlParameter(Types.VARCHAR));
253 declareParameter(new SqlParameter(Types.TINYINT));
254 declareParameter(new SqlParameter(Types.VARCHAR));
255 declareParameter(new SqlParameter(Types.VARCHAR));
256 declareParameter(new SqlParameter(Types.VARCHAR));
257 declareParameter(new SqlParameter(Types.INTEGER));
258 declareParameter(new SqlParameter(Types.INTEGER));
259 declareParameter(new SqlParameter(Types.INTEGER));
260 declareParameter(new SqlParameter(Types.TINYINT));
261 declareParameter(new SqlParameter(Types.TINYINT));
262 declareParameter(new SqlParameter(Types.TINYINT));
263 declareParameter(new SqlParameter(Types.TINYINT));
264 declareParameter(new SqlParameter(Types.INTEGER));
265 compile();
266 }
267
268
269
270
271
272
273 protected int update(Chain c) {
274 return this.update(new Object[] {
275 c.getName(), c.getShortName(),
276 c.getDescription(), new Integer(c.isActive() ? 1 : 0),
277 c.getEngine(), c.getContactPrefix(),
278 c.getContactDirectory(), new Integer(c.getContactCount()),
279 new Integer(c.getContactWidth()), new Integer(c.getContactHeight()),
280 new Integer(c.getPicSelector()), new Integer(c.getRatingMin()), new Integer(c.getRatingMax()),
281 new Integer(c.isShowUnusedSets() ? 1 : 0),
282 new Integer(c.getCid()),
283
284 });
285 }
286 }
287
288
289
290
291 protected class ChainInsert extends SqlUpdate {
292
293
294
295
296
297 protected ChainInsert(DataSource ds) {
298 super(ds, "INSERT INTO chains VALUES(?,?,?, ?,?,?, ?,?,?, ?,?,?, ?,?,?)");
299 declareParameter(new SqlParameter(Types.INTEGER));
300 declareParameter(new SqlParameter(Types.VARCHAR));
301 declareParameter(new SqlParameter(Types.VARCHAR));
302 declareParameter(new SqlParameter(Types.VARCHAR));
303 declareParameter(new SqlParameter(Types.TINYINT));
304 declareParameter(new SqlParameter(Types.VARCHAR));
305 declareParameter(new SqlParameter(Types.VARCHAR));
306 declareParameter(new SqlParameter(Types.VARCHAR));
307 declareParameter(new SqlParameter(Types.INTEGER));
308 declareParameter(new SqlParameter(Types.INTEGER));
309 declareParameter(new SqlParameter(Types.INTEGER));
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 compile();
315 }
316
317 protected void insert(Chain c) {
318 Object[] objs = new Object[] {
319 new Integer(c.getCid()),
320 c.getName(), c.getShortName(),
321 c.getDescription(), new Integer(c.isActive() ? 1 : 0),
322 c.getEngine(), c.getContactPrefix(),
323 c.getContactDirectory(), new Integer(c.getContactCount()),
324 new Integer(c.getContactWidth()), new Integer(c.getContactHeight()),
325 new Integer(c.getPicSelector()), new Integer(c.getRatingMin()), new Integer(c.getRatingMax()),
326 new Integer(c.isShowUnusedSets() ? 1 : 0),
327 };
328 super.update(objs);
329 }
330 }
331
332
333
334
335 protected class ChainDelete extends SqlUpdate {
336
337
338
339
340
341 protected ChainDelete(DataSource ds) {
342 super(ds, "DELETE FROM chains WHERE cid = (?) LIMIT 1");
343 declareParameter(new SqlParameter(Types.INTEGER));
344 compile();
345 }
346
347 protected void delete(Chain c) {
348 Object[] objs = new Object[] {
349 new Integer(c.getCid())};
350 super.update(objs);
351 }
352 }
353
354
355
356 class ChainSetSizesQuery extends MappingSqlQuery {
357
358 ChainSetSizesQuery(DataSource ds) {
359 super(ds, "SELECT * FROM chainsSetSizes WHERE cid = ?");
360 declareParameter(new SqlParameter(Types.INTEGER));
361 compile();
362 }
363
364 protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
365 SetSize ss = new SetSize();
366 ss.setSetSize(rs.getString("setsSize"));
367 return(ss);
368 }
369 }
370
371
372
373
374 protected class ChainSetSizesInsert extends SqlUpdate {
375
376
377
378
379
380 protected ChainSetSizesInsert(DataSource ds) {
381 super(ds, "INSERT INTO chainsSetSizes VALUES(?,?)");
382 declareParameter(new SqlParameter(Types.INTEGER));
383 declareParameter(new SqlParameter(Types.VARCHAR));
384 compile();
385 }
386
387 protected void insert(int cid, String setSizeName) {
388 Object[] objs = new Object[] {
389 new Integer(cid), setSizeName,
390 };
391 super.update(objs);
392 }
393 }
394
395
396
397
398 protected class ChainSetSizesDelete extends SqlUpdate {
399
400
401
402
403
404 protected ChainSetSizesDelete(DataSource ds) {
405 super(ds, "DELETE FROM chainsSetSizes WHERE cid = (?)");
406 declareParameter(new SqlParameter(Types.INTEGER));
407 compile();
408 }
409
410 protected void delete(Chain c) {
411 Object[] objs = new Object[] {
412 new Integer(c.getCid())};
413 super.update(objs);
414 }
415 }
416
417 }