1
2
3
4
5
6
7
8 package com.buckosoft.PicMan.db;
9
10 import java.util.Date;
11 import java.util.HashMap;
12 import java.util.Iterator;
13 import java.util.List;
14
15 import java.sql.ResultSet;
16 import java.sql.SQLException;
17 import java.sql.Types;
18
19 import javax.sql.DataSource;
20
21 import org.apache.commons.logging.Log;
22 import org.apache.commons.logging.LogFactory;
23
24 import org.springframework.jdbc.core.SqlParameter;
25 import org.springframework.jdbc.object.MappingSqlQuery;
26 import org.springframework.jdbc.object.SqlUpdate;
27
28 import com.buckosoft.PicMan.domain.Chain;
29 import com.buckosoft.PicMan.domain.Contact;
30 import com.buckosoft.PicMan.domain.ContactParams;
31 import com.buckosoft.PicMan.domain.SetSize;
32
33
34
35
36
37 public class ContactsDaoJdbc implements ContactsDao {
38
39 private final static boolean DEBUG = false;
40 private final Log logger = LogFactory.getLog(getClass());
41
42 private DataSource ds;
43
44 private Contact lastContactAdded = null;
45
46
47
48
49 public void setDataSource(DataSource ds) {
50 this.ds = ds;
51 lastContactAdded = getNewestContact();
52 }
53
54
55
56
57 public void addContact(Contact c) {
58 String s = "DELETE FROM contacts WHERE name = \"" + c.getName() + "\" AND cid =\"" + c.getCid() + "\"";
59 if (DEBUG)
60 logger.info(s);
61 SqlUpdate sf = new SqlUpdate(ds, s);
62 sf.update();
63 s = "DELETE FROM contactSummary WHERE name = \"" + c.getName() + "\" AND cid =\"" + c.getCid() + "\"";
64 if (DEBUG)
65 logger.info(s);
66 SqlUpdate sf1 = new SqlUpdate(ds, s);
67 sf1.update();
68 ContactInsert ci = new ContactInsert(ds);
69 Frag f = new Frag();
70 f.name = c.getName();
71 f.cid = c.getCid();
72 String pic;
73 for (int row = 0; row<c.getRowCount(); row++) {
74 for (int col = 0; ; col++) {
75 pic = c.getPic(row, col);
76 if (pic == null)
77 break;
78 f.row = row;
79 f.col = col;
80 f.pic = pic;
81 ci.insert(f);
82 }
83 }
84 ContactSummaryInsert csi = new ContactSummaryInsert(ds);
85 csi.insert(c);
86 lastContactAdded = c;
87 }
88
89
90
91
92 public Contact getNewestContact() {
93 if (lastContactAdded != null)
94 return(lastContactAdded);
95
96 Date longago = new Date(0);
97 if (sql_getNewestContactDateQUERYr == null)
98 sql_getNewestContactDateQUERYr = new ContactsSummaryQuery(ds, longago);
99
100 Contact c = (Contact)sql_getNewestContactDateQUERYr.findObject(new Object[0]);
101 if (DEBUG)
102 logger.info("newest contact date: " + ((c != null) ? c.getStartTime() : longago));
103 return(c);
104 }
105 private ContactsSummaryQuery sql_getNewestContactDateQUERYr;
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145 public HashMap<String, Date> getContactOldestMap(Chain chain) {
146 if (DEBUG)
147 logger.info("getContactOldestMap: " + chain.getName());
148 HashMap<String, Date> hm = new HashMap<String, Date>();
149 Iterator<SetSize> iter = chain.getSetSizes().iterator();
150 while (iter.hasNext()) {
151 SetSize ss = (SetSize)iter.next();
152 String uuid = ss.getSetSizeDash() + "%";
153 if (sql_getContactOldestMapQUERY == null)
154 sql_getContactOldestMapQUERY = new ContactsSummaryQuery(ds, 0, 0);
155 if (DEBUG)
156 logger.info("getContactOldestMap: " + chain.getCid() + " '" + uuid + "'");
157 Contact c = null;
158 c = (Contact)sql_getContactOldestMapQUERY.findObject(new Object[] {new Integer(chain.getCid()), uuid});
159 if (c != null) {
160 if (DEBUG)
161 logger.info("map: " + uuid + " : " + c.getStartTime());
162 hm.put(ss.getGuiSetSize(), c.getStartTime());
163 }
164 }
165 return(hm);
166 }
167 private ContactsSummaryQuery sql_getContactOldestMapQUERY;
168
169
170
171
172 public Contact getContact(int cid, String uuid) {
173 if (DEBUG)
174 logger.info("getContact: '" + uuid + "'");
175 Contact c;
176 if (sql_getContactSUMMARYQUERY == null)
177 sql_getContactSUMMARYQUERY = new ContactsSummaryQuery(ds, cid, uuid);
178
179 c = (Contact)sql_getContactSUMMARYQUERY.findObject(new Object[]{new Integer(cid), uuid});
180 if (c == null)
181 return(null);
182
183 if (sql_getContactQUERY == null)
184 sql_getContactQUERY = new ContactQuery(ds, cid, uuid);
185
186 @SuppressWarnings("unchecked")
187 List<Frag> l = sql_getContactQUERY.execute(new Object[] {new Integer(cid), uuid});
188 if (l.isEmpty())
189 return(null);
190 c.setName(uuid);
191 for (Frag f : l) {
192 c.setPic(f.pic, f.row, f.col);
193 }
194 return(c);
195 }
196 private ContactsSummaryQuery sql_getContactSUMMARYQUERY;
197 private ContactQuery sql_getContactQUERY;
198
199
200
201
202
203 public Contact getContact(int cid, String setName, int size, int index) {
204 return(getContact(cid, (new ContactParams(cid, setName, size, index)).getUuid()));
205 }
206
207
208
209
210 protected class Frag {
211 int cid;
212 String name;
213 int row;
214 int col;
215 String pic;
216 }
217
218 class ContactQuery extends MappingSqlQuery {
219
220 ContactQuery(DataSource ds, int cid, String name) {
221 super(ds, "SELECT * FROM contacts WHERE cid = ? and name = ? ORDER BY row,col");
222 declareParameter(new SqlParameter(Types.INTEGER));
223 declareParameter(new SqlParameter(Types.VARCHAR));
224 compile();
225 }
226
227 protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
228 Frag f = new Frag();
229 f.cid = rs.getInt("cid");
230 f.name = rs.getString("name");
231 f.row = rs.getInt("row");
232 f.col = rs.getInt("col");
233 f.pic = rs.getString("pic");
234 return(f);
235 }
236
237 }
238
239
240
241
242 protected class ContactInsert extends SqlUpdate {
243
244
245
246
247
248 protected ContactInsert(DataSource ds) {
249 super(ds, "INSERT INTO contacts VALUES(?,?,?,?,?)");
250 declareParameter(new SqlParameter(Types.INTEGER));
251 declareParameter(new SqlParameter(Types.VARCHAR));
252 declareParameter(new SqlParameter(Types.INTEGER));
253 declareParameter(new SqlParameter(Types.INTEGER));
254 declareParameter(new SqlParameter(Types.VARCHAR));
255 compile();
256 }
257
258 protected void insert(Frag f) {
259 Object[] objs = new Object[] {
260 new Integer(f.cid), f.name,
261 new Integer(f.row), new Integer(f.col), f.pic};
262 super.update(objs);
263 }
264 }
265 class ContactsSummaryQuery extends MappingSqlQuery {
266
267 ContactsSummaryQuery(DataSource ds) {
268 super(ds, "SELECT * from contactSummary");
269 compile();
270 }
271
272 ContactsSummaryQuery(DataSource ds, String name) {
273 super(ds, "SELECT * from contactSummary where name = ?");
274 declareParameter(new SqlParameter(Types.VARCHAR));
275 compile();
276 }
277
278 ContactsSummaryQuery(DataSource ds, int cid, String name) {
279 super(ds, "SELECT * from contactSummary where cid = ? and name = ?");
280 declareParameter(new SqlParameter(Types.INTEGER));
281 declareParameter(new SqlParameter(Types.VARCHAR));
282 compile();
283 }
284
285 ContactsSummaryQuery(DataSource ds, Date date) {
286 super(ds, "SELECT * from contactSummary ORDER BY startTime DESC LIMIT 1");
287 compile();
288 }
289
290
291
292
293
294
295 ContactsSummaryQuery(DataSource ds, int unused, int unused1) {
296 super(ds, "SELECT * from contactSummary WHERE cid = ? AND name LIKE ? ORDER BY `startTime` ASC LIMIT 1");
297 declareParameter(new SqlParameter(Types.INTEGER));
298 declareParameter(new SqlParameter(Types.VARCHAR));
299 compile();
300 }
301
302 ContactsSummaryQuery(DataSource ds, int unused0, int unused1, String s) {
303 super(ds, s);
304 compile();
305 }
306
307 protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
308 Contact c = new Contact();
309 c.setCid(rs.getInt("cid"));
310 c.setName(rs.getString("name"));
311 try {
312 c.setStartTime(rs.getTimestamp("startTime"));
313 } catch (Exception e) {}
314 try {
315 c.setEndTime(rs.getTimestamp("endTime"));
316 } catch (Exception e) {}
317 return(c);
318 }
319 }
320
321
322
323 protected class ContactSummaryInsert extends SqlUpdate {
324
325
326
327
328
329 protected ContactSummaryInsert(DataSource ds) {
330 super(ds, "INSERT INTO contactSummary VALUES(?,?,?,?)");
331 declareParameter(new SqlParameter(Types.INTEGER));
332 declareParameter(new SqlParameter(Types.VARCHAR));
333 declareParameter(new SqlParameter(Types.TIMESTAMP));
334 declareParameter(new SqlParameter(Types.TIMESTAMP));
335 compile();
336 }
337
338 protected void insert(Contact c) {
339 Object[] objs = new Object[] {
340 new Integer(c.getCid()),
341 c.getName(), c.getStartTime(), c.getEndTime() };
342 super.update(objs);
343 }
344 }
345 }