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.List;
14
15 import javax.sql.DataSource;
16
17 import org.apache.commons.logging.Log;
18 import org.apache.commons.logging.LogFactory;
19 import org.springframework.dao.DataAccessException;
20 import org.springframework.jdbc.core.SqlParameter;
21 import org.springframework.jdbc.object.MappingSqlQuery;
22 import org.springframework.jdbc.object.SqlFunction;
23 import org.springframework.jdbc.object.SqlUpdate;
24
25 import com.buckosoft.PicMan.domain.Mosaic;
26
27
28
29
30
31
32 public class MosaicsDaoJdbc implements MosaicsDao {
33 private static final boolean DEBUG = true;
34 protected final Log logger = LogFactory.getLog(getClass());
35
36 private DataSource ds;
37
38
39
40
41
42 public void setDataSource(DataSource ds) {
43 this.ds = ds;
44 }
45
46
47
48
49 public Mosaic getMosaic(int mid) {
50 if (DEBUG)
51 logger.info("getMosaic: " + mid);
52 if (sql_getMosaicQUERY == null)
53 sql_getMosaicQUERY = new MosaicsQuery(ds, mid);
54 Mosaic m = (Mosaic)sql_getMosaicQUERY.findObject(mid);
55 getMosaicConfig(m);
56 return(m);
57 }
58 private MosaicsQuery sql_getMosaicQUERY = null;
59
60
61
62
63 public Mosaic getMosaic(String masterPic, String engine, int sid, int tileHeight) {
64 if (sql_getMosaicUniQUERY == null)
65 sql_getMosaicUniQUERY = new MosaicsQuery(ds, masterPic, engine, sid, tileHeight);
66 Mosaic m = (Mosaic)sql_getMosaicUniQUERY.findObject(new Object[] {masterPic, engine, new Integer(sid), new Integer(tileHeight)});
67 getMosaicConfig(m);
68 return(m);
69 }
70 private MosaicsQuery sql_getMosaicUniQUERY = null;
71
72
73
74
75 @SuppressWarnings("unchecked")
76 public List<Mosaic> getMosaics() {
77 if (DEBUG)
78 logger.info("GetMosaicsList");
79 if (sql_getMosaicsListQUERY == null)
80 sql_getMosaicsListQUERY = new MosaicsQuery(ds);
81 List<Mosaic> mosaicList = (List<Mosaic>)sql_getMosaicsListQUERY.execute();
82 for (Mosaic m : mosaicList)
83 this.getMosaicConfig(m);
84 return(mosaicList);
85 }
86 private MosaicsQuery sql_getMosaicsListQUERY = null;
87
88
89
90
91 public void deleteMosaic(int mid) {
92 if (DEBUG)
93 logger.info("deleteMosaic");
94
95 throw new RuntimeException("Not implemented");
96 }
97
98
99
100
101 public void storeMosaic(Mosaic mosaic) {
102 if (DEBUG)
103 logger.info("storeMosaic " + mosaic.getMid());
104 if (mosaic.getMid() <= 0) {
105 MosaicsInsert mi = new MosaicsInsert(ds);
106 mi.insert(mosaic);
107 int c = getMosaicCount();
108 mosaic.setMid(c);
109 } else {
110 MosaicsUpdate mu = new MosaicsUpdate(ds);
111 mu.update(mosaic);
112 }
113 deleteMosaicConfigs(mosaic.getMid());
114 if (sql_storeMosaicConfigsINSERT == null)
115 sql_storeMosaicConfigsINSERT = new MosaicConfigsInsert(ds);
116 for (String key : mosaic.getEngineConfig().keySet()) {
117 sql_storeMosaicConfigsINSERT.insert(mosaic.getMid(), key, mosaic.getEngineConfig().get(key));
118 }
119 }
120 private MosaicConfigsInsert sql_storeMosaicConfigsINSERT = null;
121
122
123
124
125 public int getMosaicCount() {
126 if (DEBUG)
127 logger.info("getVirginCount:");
128 if (sql_getMosaicCountQUERYr == null)
129 sql_getMosaicCountQUERYr = new MosaicsCount(ds);
130 return(sql_getMosaicCountQUERYr.run());
131 }
132 private MosaicsCount sql_getMosaicCountQUERYr;
133
134 @SuppressWarnings("unchecked")
135 private void getMosaicConfig(Mosaic mosaic) {
136 if (mosaic == null)
137 return;
138 if (sql_getMosaicConfigQUERY == null)
139 sql_getMosaicConfigQUERY = new MosaicConfigsQuery(ds, 0);
140 List<KeyValue>kvlist = (List<KeyValue>)sql_getMosaicConfigQUERY.execute(mosaic.getMid());
141 for (KeyValue kv : kvlist) {
142 mosaic.getEngineConfig().put(kv.key, kv.value);
143 }
144 }
145
146 private MosaicConfigsQuery sql_getMosaicConfigQUERY = null;
147
148 private void deleteMosaicConfigs(int sid) throws DataAccessException {
149 MosaicConfigsDelete msd = new MosaicConfigsDelete(ds);
150 msd.delete(sid);
151 }
152
153
154
155
156
157 private class MosaicsQuery extends MappingSqlQuery {
158
159 MosaicsQuery(DataSource ds) {
160 super(ds, "SELECT * from mosaics");
161 compile();
162 }
163
164 MosaicsQuery(DataSource ds, int mid) {
165 super(ds, "SELECT * from mosaics where mid = ?");
166 declareParameter(new SqlParameter(Types.INTEGER));
167 compile();
168 }
169
170 MosaicsQuery(DataSource ds, String masterPic, String engine, int sid, int tileHeight) {
171 super(ds, "SELECT * from mosaics where masterPic = ? AND engine = ? AND sid = ? AND tileHeight = ? LIMIT 1");
172 declareParameter(new SqlParameter(Types.VARCHAR));
173 declareParameter(new SqlParameter(Types.VARCHAR));
174 declareParameter(new SqlParameter(Types.INTEGER));
175 declareParameter(new SqlParameter(Types.INTEGER));
176 compile();
177 }
178
179 protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
180 Mosaic m = new Mosaic();
181 m.setMid(rs.getInt("mid"));
182 m.setBatch(rs.getBoolean("isBatch"));
183 m.setName(rs.getString("name"));
184 m.setEngine(rs.getString("engine"));
185 m.setMasterPic(rs.getString("masterPic"));
186 m.setOutPic(rs.getString("outPic"));
187 m.setSid(rs.getInt("sid"));
188 m.setTileHeight(rs.getInt("tileHeight"));
189 try {
190 m.setStartTime(rs.getDate("startTime"));
191 } catch (Exception e) {logger.info(e);}
192 return(m);
193 }
194 }
195
196
197
198 class MosaicsCount extends SqlFunction {
199
200 MosaicsCount(DataSource ds) {
201 super(ds, "SELECT COUNT(*) from mosaics");
202 compile();
203 }
204 }
205
206
207
208
209 private class MosaicsInsert extends SqlUpdate {
210
211
212
213
214
215 protected MosaicsInsert(DataSource ds) {
216 super(ds, "INSERT INTO mosaics VALUES(?,?,?,?,?,?,?,?,?)");
217 declareParameter(new SqlParameter(Types.INTEGER));
218 declareParameter(new SqlParameter(Types.TINYINT));
219 declareParameter(new SqlParameter(Types.VARCHAR));
220 declareParameter(new SqlParameter(Types.VARCHAR));
221 declareParameter(new SqlParameter(Types.VARCHAR));
222 declareParameter(new SqlParameter(Types.VARCHAR));
223 declareParameter(new SqlParameter(Types.INTEGER));
224 declareParameter(new SqlParameter(Types.INTEGER));
225 declareParameter(new SqlParameter(Types.TIMESTAMP));
226 compile();
227 }
228
229 protected void insert(Mosaic m) {
230 Object[] objs = new Object[] {
231 null,
232 new Integer(m.isBatch() ? 1 : 0), m.getName(),
233 m.getEngine(), m.getMasterPic(), m.getOutPic(),
234 new Integer(m.getSid()), new Integer(m.getTileHeight()),
235 m.getStartTime()
236 };
237 super.update(objs);
238 }
239 }
240
241
242
243
244 private class MosaicsUpdate extends SqlUpdate {
245
246
247
248
249
250
251 protected MosaicsUpdate(DataSource ds) {
252 super(ds, "UPDATE mosaics SET name=?, isBatch=?, engine=?, masterPic=?, outPic=?, sid=?, tileHeight=?, startTime=? WHERE mid = ? LIMIT 1");
253 declareParameter(new SqlParameter(Types.VARCHAR));
254 declareParameter(new SqlParameter(Types.TINYINT));
255 declareParameter(new SqlParameter(Types.VARCHAR));
256 declareParameter(new SqlParameter(Types.VARCHAR));
257 declareParameter(new SqlParameter(Types.VARCHAR));
258 declareParameter(new SqlParameter(Types.INTEGER));
259 declareParameter(new SqlParameter(Types.INTEGER));
260 declareParameter(new SqlParameter(Types.TIMESTAMP));
261 declareParameter(new SqlParameter(Types.VARCHAR));
262 compile();
263 }
264
265
266
267
268
269
270 protected int update(Mosaic m) {
271 return this.update(new Object[] {
272 m.getName(), new Integer(m.isBatch() ? 1 : 0),
273 m.getEngine(), m.getMasterPic(), m.getOutPic(),
274 new Integer(m.getSid()), new Integer(m.getTileHeight()),
275 m.getStartTime(),
276 new Integer(m.getMid())
277 });
278 }
279 }
280 private class KeyValue {
281 public String key;
282 public String value;
283 }
284
285 private class MosaicConfigsQuery extends MappingSqlQuery {
286
287 MosaicConfigsQuery(DataSource ds, int mid) {
288 super(ds, "SELECT * from mosaicConfigs where mid=?");
289 declareParameter(new SqlParameter(Types.INTEGER));
290 compile();
291 }
292
293 protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
294 KeyValue k = new KeyValue();
295 k.key = rs.getString("key");
296 k.value = rs.getString("value");
297 return(k);
298 }
299 }
300
301
302
303
304 protected class MosaicConfigsDelete extends SqlUpdate {
305
306
307
308
309
310 protected MosaicConfigsDelete(DataSource ds) {
311 super(ds, "DELETE FROM mosaicConfigs WHERE mid = (?)");
312 declareParameter(new SqlParameter(Types.INTEGER));
313 compile();
314 }
315
316 protected void delete(int sid) {
317 super.update(sid);
318 }
319 }
320
321
322
323 protected class MosaicConfigsInsert extends SqlUpdate {
324
325
326
327
328
329 protected MosaicConfigsInsert(DataSource ds) {
330 super(ds, "INSERT INTO mosaicConfigs VALUES(?,?,?)");
331 declareParameter(new SqlParameter(Types.INTEGER));
332 declareParameter(new SqlParameter(Types.VARCHAR));
333 declareParameter(new SqlParameter(Types.VARCHAR));
334 compile();
335 }
336
337 protected void insert(int sid, String key, String value) {
338 Object[] objs = new Object[] {
339 new Integer(sid),
340 key,
341 value,
342 };
343 super.update(objs);
344
345 }
346 }
347 }