1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18 package com.octo.captcha.engine.bufferedengine.buffer;
19
20 import com.octo.captcha.Captcha;
21 import org.apache.commons.collections.buffer.UnboundedFifoBuffer;
22 import org.apache.commons.logging.Log;
23 import org.apache.commons.logging.LogFactory;
24
25 import javax.sql.DataSource;
26 import java.io.*;
27 import java.sql.Connection;
28 import java.sql.PreparedStatement;
29 import java.sql.ResultSet;
30 import java.sql.SQLException;
31 import java.util.*;
32
33 /***
34 * A database Captcha Buffer.
35 * <p/>
36 * The database should have the following structure : default Column Name , type </p> <ul> <li> timemillis , long </li>
37 * <li> hashCode , long </li> <li> locale , string </li> <li> captcha , object </li> </ul>
38 *
39 * @author <a href="mailto:marc.antoine.garrigue@gmail.com">Marc-Antoine Garrigue</a>
40 * @version 1.0
41 */
42 public class DatabaseCaptchaBuffer implements CaptchaBuffer {
43
44 private static final Log log = LogFactory.getLog(DatabaseCaptchaBuffer.class.getName());
45
46
47 private DataSource datasource;
48 private String table = "JCAPTCHA_T";
49 private String timeMillisColumn = "timemillis";
50 private String hashCodeColumn = "hashCode";
51 private String localeColumn = "locale";
52 private String captchaColumn = "captcha";
53 private static final String DB_ERROR = "SQL Error :";
54
55
56 public DatabaseCaptchaBuffer(DataSource datasource) {
57 log.info("Initializing Buffer");
58 this.datasource = datasource;
59 log.info("Buffer size : " + size());
60 log.info("Buffer initialized");
61 }
62
63 public DatabaseCaptchaBuffer(DataSource datasource, String table) {
64 log.info("Initializing Buffer");
65 this.datasource = datasource;
66 this.table = table;
67 log.info("Buffer size : " + size());
68 log.info("Buffer initialized");
69 }
70
71 public DatabaseCaptchaBuffer(DataSource datasource, String table, String timeMillisColumn, String hashCodeColumn, String captchaColumn, String localeColumn) {
72 log.info("Initializing Buffer");
73 this.datasource = datasource;
74 this.table = table;
75 this.timeMillisColumn = timeMillisColumn;
76 this.hashCodeColumn = hashCodeColumn;
77 this.captchaColumn = captchaColumn;
78 this.localeColumn = localeColumn;
79 log.info("Buffer size : " + size());
80 log.info("Buffer initialized");
81 }
82
83
84
85
86 /***
87 * remove a captcha from the buffer
88 *
89 * @return a captcha
90 *
91 * @throws java.util.NoSuchElementException
92 * if there is no captcha throw NoSuchElementException
93 */
94 public Captcha removeCaptcha() throws NoSuchElementException {
95 return removeCaptcha(Locale.getDefault());
96 }
97
98 /***
99 * remove a captcha from the buffer corresponding to the locale
100 *
101 * @param locale The locale the catcha to remove
102 *
103 * @return a captcha correponding to the locale
104 *
105 * @throws NoSuchElementException if there is no captcha throw NoSuchElementException
106 */
107 public Captcha removeCaptcha(Locale locale) throws NoSuchElementException {
108 Collection col = removeCaptcha(1, locale);
109 if (col != null && col.size() > 0) {
110 return (Captcha) col.iterator().next();
111 } else {
112 throw new NoSuchElementException("no captcha in this buffer for locale " + locale);
113 }
114 }
115
116 /***
117 * Remove a precise number of captcha
118 *
119 * @param number The number of captchas to remove
120 *
121 * @return a collection of captchas
122 */
123 public Collection removeCaptcha(int number) {
124 return removeCaptcha(number, Locale.getDefault());
125 }
126
127 /***
128 * Remove a precise number of captcha with a locale
129 *
130 * @param number The number of captchas to remove
131 * @param locale The locale of the removed captchas
132 *
133 * @return a collection of captchas
134 */
135 public Collection removeCaptcha(int number, Locale locale) {
136 Connection con = null;
137 PreparedStatement ps = null;
138 PreparedStatement psdel = null;
139 ResultSet rs = null;
140 Collection collection = new UnboundedFifoBuffer();
141 Collection temp = new UnboundedFifoBuffer();
142 if (number < 1) {
143 return collection;
144 }
145 try {
146 if (log.isDebugEnabled()) {
147 log.debug("try to remove " + number + " captchas");
148 }
149 ;
150 con = datasource.getConnection();
151
152
153 ps = con.prepareStatement("select * from " + table + " where " + localeColumn
154 + " = ? order by " + timeMillisColumn);
155
156 psdel = con.prepareStatement("delete from " + table + " where " + timeMillisColumn
157 + "= ? and " + hashCodeColumn
158 + "= ? ");
159
160 ps.setString(1, locale.toString());
161 ps.setMaxRows(number);
162
163 rs = ps.executeQuery();
164 int i = 0;
165 while (rs.next() && i < number) {
166 try {
167 i++;
168 InputStream in = rs.getBinaryStream(captchaColumn);
169 ObjectInputStream objstr = new ObjectInputStream(in);
170 Object captcha = objstr.readObject();
171 temp.add(captcha);
172
173 long time = rs.getLong(timeMillisColumn);
174 long hash = rs.getLong(hashCodeColumn);
175 psdel.setLong(1, time);
176 psdel.setLong(2, hash);
177
178 psdel.addBatch();
179
180 if (log.isDebugEnabled()) {
181 log.debug("remove captcha added to batch : " + time + ";" + hash);
182 }
183
184 } catch (IOException e) {
185 log.error("error during captcha deserialization, " +
186 "check your class versions. removing row from database", e);
187 psdel.execute();
188 } catch (ClassNotFoundException e) {
189 log.error("Serialized captcha class in database is not in your classpath!", e);
190 }
191
192 }
193
194 psdel.executeBatch();
195 log.debug("batch executed");
196 rs.close();
197
198 con.commit();
199 log.debug("batch commited");
200
201 collection.addAll(temp);
202 } catch (SQLException e) {
203 log.error(DB_ERROR, e);
204 if (rs != null) {
205 try {
206 rs.close();
207 } catch (SQLException ex) {
208 }
209 }
210
211 } finally {
212
213 if (ps != null) {
214 try {
215 ps.close();
216 }
217 catch (SQLException e) {
218 }
219 }
220 if (con != null) {
221 try {
222 con.close();
223 }
224 catch (SQLException e) {
225 }
226 }
227 }
228 return collection;
229 }
230
231 /***
232 * Put a captcha with default locale
233 */
234 public void putCaptcha(Captcha captcha) {
235 putCaptcha(captcha, Locale.getDefault());
236 }
237
238 /***
239 * Put a captcha with a locale
240 *
241 * @param captcha The captcha to add
242 * @param locale the locale of the captcha
243 */
244 public void putCaptcha(Captcha captcha, Locale locale) {
245 if (captcha != null) {
246 Set set = new HashSet();
247 set.add(captcha);
248 putAllCaptcha(set, locale);
249 }
250 }
251
252 /***
253 * Put a collection of captchas with the default locale
254 *
255 * @param captchas The captchas to add
256 */
257 public void putAllCaptcha(Collection captchas) {
258 putAllCaptcha(captchas, Locale.getDefault());
259 }
260
261 /***
262 * Put a collection of captchas with his locale
263 *
264 * @param captchas The captchas to add
265 * @param locale The locale of the captchas
266 */
267 public void putAllCaptcha(Collection captchas, Locale locale) {
268 Connection con = null;
269 PreparedStatement ps = null;
270
271
272 if (captchas != null && captchas.size() > 0) {
273 Iterator captIt = captchas.iterator();
274 if (log.isDebugEnabled()) {
275 log.debug("try to insert " + captchas.size() + " captchas");
276 }
277
278 try {
279 con = datasource.getConnection();
280 con.setAutoCommit(false);
281 ps = con.prepareStatement("insert into " + table + "(" + timeMillisColumn + "," +
282 hashCodeColumn + "," + localeColumn + "," + captchaColumn + ") values (?,?,?,?)");
283
284
285 while (captIt.hasNext()) {
286
287 Captcha captcha = (Captcha) captIt.next();
288 try {
289 long currenttime = System.currentTimeMillis();
290 long hash = captcha.hashCode();
291
292 ps.setLong(1, currenttime);
293 ps.setLong(2, hash);
294 ps.setString(3, locale.toString());
295
296 final ByteArrayOutputStream outstr = new ByteArrayOutputStream();
297 final ObjectOutputStream objstr = new ObjectOutputStream(outstr);
298 objstr.writeObject(captcha);
299 objstr.close();
300 final ByteArrayInputStream inpstream = new ByteArrayInputStream(outstr.toByteArray());
301
302 ps.setBinaryStream(4, inpstream, outstr.size());
303
304 ps.addBatch();
305
306 if (log.isDebugEnabled()) {
307 log.debug("insert captcha added to batch : " + currenttime + ";" + hash);
308 }
309
310 } catch (IOException e) {
311 log.warn("error during captcha serialization, " +
312 "check your class versions. removing row from database", e);
313 }
314 }
315
316
317 ps.executeBatch();
318 log.debug("batch executed");
319
320 con.commit();
321 log.debug("batch commited");
322
323 } catch (SQLException e) {
324 log.error(DB_ERROR, e);
325
326 } finally {
327 if (ps != null) {
328 try {
329 ps.close();
330 } catch (SQLException e) {
331 }
332 }
333 if (con != null) {
334 try {
335 con.close();
336 } catch (SQLException e) {
337 }
338 }
339 }
340 }
341
342
343 }
344
345 /***
346 * Get the size of the buffer for all locales
347 *
348 * @return The size of the buffer
349 */
350 public int size() {
351 Connection con = null;
352 PreparedStatement ps = null;
353 ResultSet rs = null;
354 int size = 0;
355
356 try {
357 con = datasource.getConnection();
358 ps = con.prepareStatement("select count(*) from " + table);
359 rs = ps.executeQuery();
360 if (rs.next()) {
361 size = rs.getInt(1);
362 }
363 rs.close();
364 con.commit();
365 } catch (SQLException e) {
366 log.error(DB_ERROR, e);
367 if (rs != null) {
368 try {
369 rs.close();
370 } catch (SQLException ex) {
371 }
372 }
373 } finally {
374 if (ps != null) {
375 try {
376 ps.close();
377 } catch (SQLException e) {
378 }
379 }
380 if (con != null) {
381 try {
382 con.close();
383 } catch (SQLException e) {
384 }
385 }
386 }
387
388 return size;
389
390 }
391
392 /***
393 * Get the size of the buffer for a locale
394 *
395 * @param locale the locale to get the size
396 *
397 * @return The size of the buffer
398 */
399 public int size(Locale locale) {
400 Connection con = null;
401 PreparedStatement ps = null;
402 ResultSet rs = null;
403 int size = 0;
404
405 try {
406 con = datasource.getConnection();
407 ps = con.prepareStatement("select count(*) from " + table + " where " + localeColumn + "=?");
408 ps.setString(1, locale.toString());
409 rs = ps.executeQuery();
410 if (rs.next()) {
411 size = rs.getInt(1);
412 }
413 rs.close();
414 con.commit();
415 } catch (SQLException e) {
416 log.error(DB_ERROR, e);
417 if (rs != null) {
418 try {
419 rs.close();
420 } catch (SQLException ex) {
421 }
422 }
423 } finally {
424 if (ps != null) {
425 try {
426 ps.close();
427 } catch (SQLException e) {
428 }
429 }
430 if (con != null) {
431 try {
432 con.close();
433 } catch (SQLException e) {
434 }
435 }
436 }
437
438 return size;
439 }
440
441 /***
442 * Release all the ressources and close the buffer.
443 */
444 public void dispose() {
445 }
446
447 /***
448 * Clear the buffer from all locale
449 */
450 public void clear() {
451 Connection con = null;
452 PreparedStatement ps = null;
453 ResultSet rs = null;
454
455 try {
456 con = datasource.getConnection();
457 ps = con.prepareStatement("delete from " + table);
458 ps.execute();
459 con.commit();
460
461 } catch (SQLException e) {
462 log.error(DB_ERROR, e);
463 if (rs != null) {
464 try {
465 rs.close();
466 } catch (SQLException ex) {
467 }
468 }
469 } finally {
470 if (ps != null) {
471 try {
472 ps.close();
473 } catch (SQLException e) {
474 }
475 }
476 if (con != null) {
477 try {
478 con.close();
479 } catch (SQLException e) {
480 }
481 }
482 }
483
484
485 }
486
487 /***
488 * Get all the locales used
489 */
490 public Collection getLocales() {
491 Connection con = null;
492 PreparedStatement ps = null;
493 ResultSet rs = null;
494 Set set = new HashSet();
495
496 try {
497 con = datasource.getConnection();
498 ps = con.prepareStatement("select distinct " + localeColumn + " from " + table);
499 rs = ps.executeQuery();
500 while (rs.next()) {
501 set.add(rs.getString(1));
502 }
503 rs.close();
504 con.commit();
505 } catch (SQLException e) {
506 log.error(DB_ERROR, e);
507 if (rs != null) {
508 try {
509 rs.close();
510 } catch (SQLException ex) {
511 }
512 }
513 } finally {
514 if (ps != null) {
515 try {
516 ps.close();
517 } catch (SQLException e) {
518 }
519 }
520 if (con != null) {
521 try {
522 con.close();
523 } catch (SQLException e) {
524 }
525 }
526 }
527
528 return set;
529 }
530
531
532 }