|
1
25
26 package org.snipsnap.snip.storage;
27
28 import org.radeox.util.logging.Logger;
29 import org.snipsnap.app.Application;
30 import org.snipsnap.interceptor.Aspects;
31 import org.snipsnap.jdbc.*;
32 import org.snipsnap.snip.Links;
33 import org.snipsnap.snip.Snip;
34 import org.snipsnap.snip.SnipFactory;
35 import org.snipsnap.snip.attachment.Attachments;
36 import org.snipsnap.snip.label.Labels;
37 import org.snipsnap.user.Permissions;
38 import org.snipsnap.util.ApplicationAwareMap;
39 import org.snipsnap.util.ConnectionManager;
40 import org.snipsnap.util.log.SQLLogger;
41
42 import javax.sql.DataSource  ;
43 import java.sql.*;
44 import java.util.ArrayList ;
45 import java.util.List ;
46
47
53
54 public class JDBCSnipStorage implements SnipStorage, CacheableStorage {
55 private DataSource ds;
56 private FinderFactory finders;
57 private ApplicationAwareMap cache;
58
59 private static final String SNIP_SELECT = "SELECT applicationOid, name, content, cTime, mTime, cUser, mUser, parentSnip, commentSnip, permissions, " +
60 " oUser, backLinks, snipLinks, labels, attachments, viewCount, version " +
61 " FROM Snip ";
62
63 public JDBCSnipStorage(DataSource ds) {
64 this.ds = ds;
65 this.finders = new FinderFactory(ds, SNIP_SELECT);
66 }
67
68 public static void createStorage() {
69 DataSource datasource = ConnectionManager.getDataSource();
70
71 System.err.println("JDBCSnipStorage: dropping Snip SQL table");
72 JDBCTemplate dropTemplate = new JDBCTemplate(datasource);
73 try {
74 dropTemplate.update("DROP TABLE Snip");
75 } catch (Exception e) {
76 SQLLogger.warn("JDBCSnipStorage: unable to drop table (new install?)", e);
77 }
78
79 System.err.println("JDBCSnipStorage: creating Snip SQL table");
80 JDBCTemplate template = new JDBCTemplate(datasource);
81 template.update(
82 " CREATE TABLE Snip ( " +
83 " name VARCHAR(100) NOT NULL, " +
84 " applicationOid VARCHAR(100) NOT NULL, " +
85 " content TEXT, " +
86 " cTime TIMESTAMP, " +
87 " mTime TIMESTAMP, " +
88 " cUser VARCHAR(55), " +
89 " mUser VARCHAR(55), " +
90 " oUser VARCHAR(55), " +
91 " parentSnip VARCHAR(100), " +
92 " commentSnip VARCHAR(100), " +
93 " backLinks TEXT, " +
94 " snipLinks TEXT, " +
95 " labels TEXT, " +
96 " attachments TEXT, " +
97 " viewCount INTEGER, " +
98 " permissions VARCHAR(200), " +
99 " version INTEGER" +
100 " )");
101 return;
102 }
103
104 public void setCache(ApplicationAwareMap cache) {
105 this.cache = cache;
106 }
107
108 public int storageCount() {
109 final String applicationOid = (String ) Application.get().getObject(Application.OID);
110 final IntHolder holder = new IntHolder(-1);
111
112 JDBCTemplate template = new JDBCTemplate(ds);
113 template.query(
114 "SELECT count(*) " +
115 " FROM Snip WHERE applicationOid=?"
116 , new RowCallbackHandler() {
117 public void processRow(ResultSet rs) throws SQLException {
118 holder.setValue(rs.getInt(1));
119 }
120 }, new PreparedStatementSetter() {
121 public void setValues(PreparedStatement ps) throws SQLException {
122 ps.setString(1, applicationOid);
123 }
124 }
125 );
126 return holder.getValue();
127 }
128
129 public List storageAll(final String applicationOid) {
130 final List list = new ArrayList ();
131 JDBCTemplate template = new JDBCTemplate(ds);
132 template.query(SNIP_SELECT + "WHERE applicationOid=? ORDER BY name", new RowCallbackHandler() {
133 public void processRow(ResultSet rs) throws SQLException {
134 list.add(createSnip(rs));
135 }
136 }, new PreparedStatementSetter() {
137 public void setValues(PreparedStatement ps) throws SQLException {
138 ps.setString(1, applicationOid);
139 }
140 }
141 );
142 return list;
143 }
144
145 public List storageAll() {
146 String applicationOid = (String ) Application.get().getObject(Application.OID);
147 return storageAll(applicationOid);
148 }
149
150 public List storageByHotness(int size) {
151 final String applicationOid = (String ) Application.get().getObject(Application.OID);
152 JDBCTemplate template = new JDBCTemplate(ds);
153 final List list = new ArrayList ();
154 template.query(SNIP_SELECT + "WHERE applicationOid=? ORDER BY viewCount DESC", new RowCallbackHandler() {
155 public void processRow(ResultSet rs) throws SQLException {
156 list.add(createSnip(rs));
157 }
158 }, new PreparedStatementSetter() {
159 public void setValues(PreparedStatement ps) throws SQLException {
160 ps.setString(1, applicationOid);
161 }
162 });
163 return list;
164 }
165
166 public List storageByUser(String login) {
167 String applicationOid = (String ) Application.get().getObject(Application.OID);
168 Finder finder = finders.getFinder("WHERE cUser=? AND applicationOid=?")
169 .setString(1, login)
170 .setString(2, applicationOid);
171 List list = createObjects(finder.execute());
172 finder.close();
173 return list;
174 }
175
176 public List storageByDateSince(Timestamp date) {
177 String applicationOid = (String ) Application.get().getObject(Application.OID);
178 Finder finder = finders.getFinder("WHERE mTime>=? AND applicationOid=?")
179 .setDate(1, date)
180 .setString(2, applicationOid);
181 List list = createObjects(finder.execute());
182 finder.close();
183 return list;
184 }
185
186 public List storageByRecent(final String applicationOid, int size) {
187 JDBCTemplate template = new JDBCTemplate(ds);
188 final List list = new ArrayList ();
189 template.query(SNIP_SELECT + "WHERE applicationOid=? ORDER BY mTime DESC", new RowCallbackHandler() {
190 public void processRow(ResultSet rs) throws SQLException {
191 list.add(createSnip(rs));
192 }
193 }, new PreparedStatementSetter() {
194 public void setValues(PreparedStatement ps) throws SQLException {
195 ps.setString(1, applicationOid);
196 }
197 });
198 return list;
199 }
200
201 public List storageByComments(Snip parent) {
202 String applicationOid = (String ) Application.get().getObject(Application.OID);
203 Finder finder = finders.getFinder("WHERE commentSnip=? AND applicationOid=? ORDER BY cTime")
204 .setString(1, parent.getName())
205 .setString(2, applicationOid);
206 List list = createObjects(finder.execute());
207 finder.close();
208 return list;
209 }
210
211 public List storageByParent(Snip parent) {
212 String applicationOid = (String ) Application.get().getObject(Application.OID);
213 Finder finder = finders.getFinder("WHERE parentSnip=? AND applicationOid=?")
214 .setString(1, parent.getName())
215 .setString(2, applicationOid);
216 List list = createObjects(finder.execute());
217 finder.close();
218 return list;
219 }
220
221 public List storageByParentNameOrder(Snip parent, int count) {
222 String applicationOid = (String ) Application.get().getObject(Application.OID);
223 Finder finder = finders.getFinder("WHERE parentSnip=? AND applicationOid=? ORDER BY name DESC ")
224 .setString(1, parent.getName())
225 .setString(2, applicationOid);
226 List list = createObjects(finder.execute(), count);
227 finder.close();
228 return list;
229 }
230
231 public List storageByParentModifiedOrder(Snip parent, int count) {
232 String applicationOid = (String ) Application.get().getObject(Application.OID);
233 Finder finder = finders.getFinder("WHERE parentSnip=? AND applicationOid=? ORDER BY mTime DESC ")
234 .setString(1, parent.getName())
235 .setString(2, applicationOid);
236 List list = createObjects(finder.execute(), count);
237 finder.close();
238 return list;
239 }
240
241 public List storageByDateInName(String nameSpace, String start, String end) {
242 System.err.println("JDBCSnipStorage: ERROR storageByDateInName() DOES NOT WORK.");
244
245 String applicationOid = (String ) Application.get().getObject(Application.OID);
246 Finder finder = finders.getFinder("WHERE name>=? and name<=? and parentSnip=? AND applicationOid=?" +
247 " ORDER BY name")
248 .setString(1, start)
249 .setString(2, end)
250 .setString(3, Application.get().getConfiguration().getStartSnip())
251 .setString(4, applicationOid);
252 List list = createObjects(finder.execute());
253 finder.close();
254 return list;
255 }
256
257 public Snip[] match(String pattern) {
259 String applicationOid = (String ) Application.get().getObject(Application.OID);
260 Finder finder = finders.getFinder("WHERE name LIKE ? AND applicationOid=?" +
261 " ORDER BY name")
262 .setString(1, pattern.toUpperCase() + "%")
263 .setString(2, applicationOid);
264 List list = createObjects(finder.execute());
265 finder.close();
266 return (Snip[]) list.toArray(new Snip[list.size()]);
267 }
268
269 public Snip[] match(String start, String end) {
270 String applicationOid = (String ) Application.get().getObject(Application.OID);
271 Finder finder = finders.getFinder("WHERE name>=? and name<=? AND applicationOid=?" +
273 " ORDER BY name")
274 .setString(1, start.toUpperCase())
275 .setString(2, end.toUpperCase())
276 .setString(4, applicationOid);
277 List list = createObjects(finder.execute());
278 finder.close();
279 return (Snip[]) list.toArray(new Snip[list.size()]);
280 }
281
282 public Snip storageLoad(String name) {
283
285 if (cache.getMap().containsKey(name.toUpperCase())) {
286 return (Snip) cache.getMap().get(name.toUpperCase());
287 }
288
289 Application app = Application.get();
290 String applicationOid = (String ) app.getObject(Application.OID);
291 long start = app.start();
292 Snip snip = null;
293 PreparedStatement statement = null;
294 ResultSet result = null;
295 Connection connection = null;
296
297 try {
298 connection = ds.getConnection();
299
300 statement = connection.prepareStatement(SNIP_SELECT +
301 " WHERE UPPER(name)=? AND applicationOid=?");
302 statement.setString(1, name.toUpperCase());
303 statement.setString(2, applicationOid);
304
305 result = statement.executeQuery();
306 if (result.next()) {
307 snip = createSnip(result);
308 }
309 } catch (SQLException e) {
310 SQLLogger.warn("JDBCSnipStorage: unable to load snip " + name, e);
311 } finally {
312 ConnectionManager.close(result);
313 ConnectionManager.close(statement);
314 ConnectionManager.close(connection);
315 }
316 app.stop(start, "storageLoad - " + name);
317 return snip;
318 }
319
320 public void storageStore(final Snip snip) {
321 JDBCTemplate template = new JDBCTemplate(ds);
323 template.update(
324 " UPDATE Snip SET name=?, content=?, cTime=?, mTime=?, " +
325 " cUser=?, mUser=?, parentSnip=?, commentSnip=?, permissions=?, " +
326 " oUser=?, backLinks=?, snipLinks=?, labels=?, attachments=?, viewCount=?, version=? " +
327 " WHERE name=? AND applicationOid=?",
328 new PreparedStatementSetter() {
329 public void setValues(PreparedStatement ps) throws SQLException {
330 ps.setString(1, snip.getName());
331 ps.setString(2, snip.getContent());
332 ps.setTimestamp(3, snip.getCTime());
333 ps.setTimestamp(4, snip.getMTime());
334 ps.setString(5, snip.getCUser());
335 ps.setString(6, snip.getMUser());
336 Snip parent = snip.getParent();
337 if (null == parent) {
338 ps.setNull(7, Types.VARCHAR);
339 } else {
340 ps.setString(7, parent.getName());
341 }
342 Snip comment = snip.getCommentedSnip();
343 if (null == comment) {
344 ps.setNull(8, Types.VARCHAR);
345 } else {
346 ps.setString(8, comment.getName());
347 }
348 ps.setString(9, snip.getPermissions().toString());
349 ps.setString(10, snip.getOUser());
350 ps.setString(11, snip.getBackLinks().toString());
351 ps.setString(12, snip.getSnipLinks().toString());
352 ps.setString(13, snip.getLabels().toString());
353 ps.setString(14, snip.getAttachments().toString());
354 ps.setInt(15, snip.getViewCount());
355 ps.setInt(16, snip.getVersion());
356 ps.setString(17, snip.getName());
357 ps.setString(18, snip.getApplication());
358 }
359 });
360 return;
361 }
362
363 public Snip storageCreate(final String name, final String content) {
364 Application app = Application.get();
365 String applicationOid = (String ) app.getObject(Application.OID);
366 final String login = app.getUser().getLogin();
367 final Snip snip = SnipFactory.createSnip(name, content);
368 final Timestamp cTime = new Timestamp(new java.util.Date ().getTime());
369 final Timestamp mTime = (Timestamp) cTime.clone();
370 snip.setCTime(cTime);
371 snip.setMTime(mTime);
372 snip.setCUser(login);
373 snip.setMUser(login);
374 snip.setOUser(login);
375 snip.setPermissions(new Permissions());
376 snip.setBackLinks(new Links());
377 snip.setSnipLinks(new Links());
378 snip.setLabels(new Labels());
379 snip.setAttachments(new Attachments());
380 snip.setApplication(applicationOid);
381
382 JDBCTemplate template = new JDBCTemplate(ds);
383 template.update(
384 "INSERT INTO Snip (name, content, cTime, mTime, " +
385 " cUser, mUser, parentSnip, commentSnip, permissions, " +
386 " oUser, backLinks, snipLinks, labels, attachments, viewCount, applicationOid, version " +
387 " ) VALUES (?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?)",
388 new PreparedStatementSetter() {
389 public void setValues(PreparedStatement ps) throws SQLException {
390 ps.setString(1, name);
391 ps.setString(2, content);
392 ps.setTimestamp(3, cTime);
393 ps.setTimestamp(4, mTime);
394 ps.setString(5, login);
395 ps.setString(6, login);
396 Snip parent = snip.getParent();
397 if (null == parent) {
398 ps.setNull(7, Types.VARCHAR);
399 } else {
400 ps.setString(7, parent.getName());
401 }
402 Snip comment = snip.getCommentedSnip();
403 if (null == comment) {
404 ps.setNull(8, Types.VARCHAR);
405 } else {
406 ps.setString(8, comment.getName());
407 }
408 ps.setString(9, snip.getPermissions().toString());
409 ps.setString(10, login);
410 ps.setString(11, snip.getBackLinks().toString());
411 ps.setString(12, snip.getSnipLinks().toString());
412 ps.setString(13, snip.getLabels().toString());
413 ps.setString(14, snip.getAttachments().toString());
414 ps.setInt(15, snip.getViewCount());
415 ps.setString(16, snip.getApplication());
416 ps.setInt(17, snip.getVersion());
417 }
418 });
419 return (Snip) Aspects.newInstance(snip, Snip.class);
420 }
421
422 public void storageRemove(final Snip snip) {
423 JDBCTemplate template = new JDBCTemplate(ds);
424 template.update(
425 "DELETE FROM Snip WHERE name=? AND applicationOid=?",
426 new PreparedStatementSetter() {
427 public void setValues(PreparedStatement ps) throws SQLException {
428 ps.setString(1, snip.getName());
429 ps.setString(2, snip.getApplication());
430 }
431 });
432 return;
433 }
434
435 public List createObjects(ResultSet result) {
436 return createObjects(result, Integer.MAX_VALUE);
437 }
438
439 public synchronized List createObjects(ResultSet result, int size) {
440 List resultList = new ArrayList ();
441 if (null != result) {
442 try {
443 while (result.next() && size-- > 0) {
444 resultList.add(createSnip(result));
446 }
447 } catch (SQLException e) {
448 Logger.warn("Finder: SQL Error", e);
449 }
450 }
451 return resultList;
452 }
453
454 public synchronized Snip createSnip(ResultSet result) throws SQLException {
455 String applicationOid = result.getString("applicationOid");
456 String name = result.getString("name");
457 if (cache.getMap(applicationOid).containsKey(name.toUpperCase())) {
463 return (Snip) cache.getMap(applicationOid).get(name.toUpperCase());
464 }
465
466 String content = result.getString("content");
467
468 Snip snip = SnipFactory.createSnip(name, content);
469 snip.setApplication(applicationOid);
470 snip.setCTime(result.getTimestamp("cTime"));
471 snip.setMTime(result.getTimestamp("mTime"));
472 snip.setCUser(result.getString("cUser"));
473 snip.setMUser(result.getString("mUser"));
474 String commentString = result.getString("commentSnip");
475 if (!result.wasNull()) {
476 snip.setCommentedName(commentString);
477 }
478 String parentString = result.getString("parentSnip");
479 if (!result.wasNull()) {
480 snip.setParentName(parentString);
481 }
482 snip.setPermissions(new Permissions(result.getString("permissions")));
483 snip.setBackLinks(new Links(result.getString("backLinks")));
484 snip.setSnipLinks(new Links(result.getString("snipLinks")));
485 snip.setLabels(new Labels(snip, result.getString("labels")));
486 snip.setAttachments(new Attachments(result.getString("attachments")));
487 snip.setViewCount(result.getInt("viewCount"));
488 snip.setVersion(result.getInt("version"));
489
490 snip = (Snip) Aspects.newInstance(snip, Snip.class);
493
494 cache.getMap(applicationOid).put(name.toUpperCase(), snip);
495 return snip;
496 }
497
498 private Snip getProxy(String name) {
500 if (cache.getMap().containsKey(name)) {
501 return (Snip) cache.getMap().get(name);
502 } else {
503 Snip snip = null; cache.getMap().put(name, snip);
505 return snip;
506 }
507 }
508 }
509 |
Popular Tags |
Java API By Example, From Geeks To Geeks.