|
1
17
18
21 package org.quartz.impl.jdbcjobstore.oracle;
22
23 import java.io.ByteArrayOutputStream  ;
24 import java.io.IOException ;
25 import java.io.InputStream ;
26 import java.io.ObjectInputStream ;
27 import java.math.BigDecimal ;
28 import java.sql.Blob ;
29 import java.sql.Connection ;
30 import java.sql.PreparedStatement ;
31 import java.sql.ResultSet ;
32 import java.sql.SQLException ;
33
34 import org.apache.commons.logging.Log;
35 import org.quartz.Calendar;
36 import org.quartz.CronTrigger;
37 import org.quartz.JobDetail;
38 import org.quartz.SimpleTrigger;
39 import org.quartz.Trigger;
40 import org.quartz.impl.jdbcjobstore.StdJDBCDelegate;
41
42
55 public class OracleDelegate extends StdJDBCDelegate {
56
66 public OracleDelegate(Log logger, String tablePrefix, String instanceId) {
67 super(logger, tablePrefix, instanceId);
68 }
69
70
82 public OracleDelegate(Log logger, String tablePrefix, String instanceId,
83 Boolean useProperties) {
84 super(logger, tablePrefix, instanceId, useProperties);
85 }
86
87 public static final String UPDATE_ORACLE_JOB_DETAIL = "UPDATE "
88 + TABLE_PREFIX_SUBST + TABLE_JOB_DETAILS + " SET "
89 + COL_DESCRIPTION + " = ?, " + COL_JOB_CLASS + " = ?, "
90 + COL_IS_DURABLE + " = ?, " + COL_IS_VOLATILE + " = ?, "
91 + COL_IS_STATEFUL + " = ?, " + COL_REQUESTS_RECOVERY + " = ? "
92 + " WHERE " + COL_JOB_NAME + " = ? AND " + COL_JOB_GROUP + " = ?";
93
94 public static final String UPDATE_ORACLE_JOB_DETAIL_BLOB = "UPDATE "
95 + TABLE_PREFIX_SUBST + TABLE_JOB_DETAILS + " SET "
96 + COL_JOB_DATAMAP + " = ? " + " WHERE " + COL_JOB_NAME
97 + " = ? AND " + COL_JOB_GROUP + " = ?";
98
99 public static final String UPDATE_ORACLE_JOB_DETAIL_EMPTY_BLOB = "UPDATE "
100 + TABLE_PREFIX_SUBST + TABLE_JOB_DETAILS + " SET "
101 + COL_JOB_DATAMAP + " = EMPTY_BLOB() " + " WHERE " + COL_JOB_NAME
102 + " = ? AND " + COL_JOB_GROUP + " = ?";
103
104 public static final String SELECT_ORACLE_JOB_DETAIL_BLOB = "SELECT "
105 + COL_JOB_DATAMAP + " FROM " + TABLE_PREFIX_SUBST
106 + TABLE_JOB_DETAILS + " WHERE " + COL_JOB_NAME + " = ? AND "
107 + COL_JOB_GROUP + " = ? FOR UPDATE";
108
109 public static final String UPDATE_ORACLE_TRIGGER = "UPDATE "
110 + TABLE_PREFIX_SUBST + TABLE_TRIGGERS + " SET " + COL_JOB_NAME
111 + " = ?, " + COL_JOB_GROUP + " = ?, " + COL_IS_VOLATILE + " = ?, "
112 + COL_DESCRIPTION + " = ?, " + COL_NEXT_FIRE_TIME + " = ?, "
113 + COL_PREV_FIRE_TIME + " = ?, " + COL_TRIGGER_STATE + " = ?, "
114 + COL_TRIGGER_TYPE + " = ?, " + COL_START_TIME + " = ?, "
115 + COL_END_TIME + " = ?, " + COL_CALENDAR_NAME + " = ?, "
116 + COL_MISFIRE_INSTRUCTION + " = ?, "
117 + COL_PRIORITY + " = ? WHERE "
118 + COL_TRIGGER_NAME + " = ? AND " + COL_TRIGGER_GROUP + " = ?";
119
120
121 public static final String SELECT_ORACLE_TRIGGER_JOB_DETAIL_BLOB = "SELECT "
122 + COL_JOB_DATAMAP + " FROM " + TABLE_PREFIX_SUBST
123 + TABLE_TRIGGERS + " WHERE " + COL_TRIGGER_NAME + " = ? AND "
124 + COL_TRIGGER_GROUP + " = ? FOR UPDATE";
125
126 public static final String UPDATE_ORACLE_TRIGGER_JOB_DETAIL_BLOB = "UPDATE "
127 + TABLE_PREFIX_SUBST + TABLE_TRIGGERS + " SET "
128 + COL_JOB_DATAMAP + " = ? " + " WHERE " + COL_TRIGGER_NAME
129 + " = ? AND " + COL_TRIGGER_GROUP + " = ?";
130
131 public static final String UPDATE_ORACLE_TRIGGER_JOB_DETAIL_EMPTY_BLOB = "UPDATE "
132 + TABLE_PREFIX_SUBST + TABLE_TRIGGERS + " SET "
133 + COL_JOB_DATAMAP + " = EMPTY_BLOB() " + " WHERE " + COL_TRIGGER_NAME
134 + " = ? AND " + COL_TRIGGER_GROUP + " = ?";
135
136
137 public static final String INSERT_ORACLE_CALENDAR = "INSERT INTO "
138 + TABLE_PREFIX_SUBST + TABLE_CALENDARS + " (" + COL_CALENDAR_NAME
139 + ", " + COL_CALENDAR + ") " + " VALUES(?, EMPTY_BLOB())";
140
141 public static final String SELECT_ORACLE_CALENDAR_BLOB = "SELECT "
142 + COL_CALENDAR + " FROM " + TABLE_PREFIX_SUBST + TABLE_CALENDARS
143 + " WHERE " + COL_CALENDAR_NAME + " = ? FOR UPDATE";
144
145 public static final String UPDATE_ORACLE_CALENDAR_BLOB = "UPDATE "
146 + TABLE_PREFIX_SUBST + TABLE_CALENDARS + " SET " + COL_CALENDAR
147 + " = ? " + " WHERE " + COL_CALENDAR_NAME + " = ?";
148
149
153 protected Object getObjectFromBlob(ResultSet rs, String colName)
154 throws ClassNotFoundException , IOException , SQLException {
155
156 Object obj = null;
157 InputStream binaryInput = rs.getBinaryStream(colName);
158 if (binaryInput != null) {
159 ObjectInputStream in = new ObjectInputStream (binaryInput);
160 try {
161 obj = in.readObject();
162 } finally {
163 in.close();
164 }
165 }
166
167 return obj;
168 }
169
170 public int insertJobDetail(Connection conn, JobDetail job)
171 throws IOException , SQLException {
172
173 ByteArrayOutputStream baos = serializeJobData(job.getJobDataMap());
174 byte[] data = baos.toByteArray();
175 PreparedStatement ps = null;
176 ResultSet rs = null;
177
178 try {
179 ps = conn.prepareStatement(rtp(INSERT_JOB_DETAIL));
180 ps.setString(1, job.getName());
181 ps.setString(2, job.getGroup());
182 ps.setString(3, job.getDescription());
183 ps.setString(4, job.getJobClass().getName());
184 setBoolean(ps, 5, job.isDurable());
185 setBoolean(ps, 6, job.isVolatile());
186 setBoolean(ps, 7, job.isStateful());
187 setBoolean(ps, 8, job.requestsRecovery());
188
189 ps.setBinaryStream(9, null, 0);
190 ps.executeUpdate();
191 ps.close();
192
193 ps = conn
194 .prepareStatement(rtp(UPDATE_ORACLE_JOB_DETAIL_EMPTY_BLOB));
195 ps.setString(1, job.getName());
196 ps.setString(2, job.getGroup());
197 ps.executeUpdate();
198 ps.close();
199
200 ps = conn.prepareStatement(rtp(SELECT_ORACLE_JOB_DETAIL_BLOB));
201 ps.setString(1, job.getName());
202 ps.setString(2, job.getGroup());
203
204 rs = ps.executeQuery();
205
206 int res = 0;
207
208 Blob dbBlob = null;
209 if (rs.next()) {
210 dbBlob = writeDataToBlob(rs, 1, data);
211 } else {
212 return res;
213 }
214
215 rs.close();
216 ps.close();
217
218 ps = conn.prepareStatement(rtp(UPDATE_ORACLE_JOB_DETAIL_BLOB));
219 ps.setBlob(1, dbBlob);
220 ps.setString(2, job.getName());
221 ps.setString(3, job.getGroup());
222
223 res = ps.executeUpdate();
224
225 if (res > 0) {
226 String [] jobListeners = job.getJobListenerNames();
227 for (int i = 0; jobListeners != null && i < jobListeners.length; i++) {
228 insertJobListener(conn, job, jobListeners[i]);
229 }
230 }
231
232 return res;
233 } finally {
234 closeResultSet(rs);
235 closeStatement(ps);
236 }
237
238 }
239
240 protected Object getJobDetailFromBlob(ResultSet rs, String colName)
241 throws ClassNotFoundException , IOException , SQLException {
242
243 if (canUseProperties()) {
244 InputStream binaryInput = rs.getBinaryStream(colName);
245 return binaryInput;
246 }
247
248 return getObjectFromBlob(rs, colName);
249 }
250
251 public int updateJobDetail(Connection conn, JobDetail job)
252 throws IOException , SQLException {
253
254 ByteArrayOutputStream baos = serializeJobData(job.getJobDataMap());
255 byte[] data = baos.toByteArray();
256
257 PreparedStatement ps = null;
258 PreparedStatement ps2 = null;
259 ResultSet rs = null;
260
261 try {
262 ps = conn.prepareStatement(rtp(UPDATE_ORACLE_JOB_DETAIL));
263 ps.setString(1, job.getDescription());
264 ps.setString(2, job.getJobClass().getName());
265 setBoolean(ps, 3, job.isDurable());
266 setBoolean(ps, 4, job.isVolatile());
267 setBoolean(ps, 5, job.isStateful());
268 setBoolean(ps, 6, job.requestsRecovery());
269 ps.setString(7, job.getName());
270 ps.setString(8, job.getGroup());
271
272 ps.executeUpdate();
273 ps.close();
274
275 ps = conn
276 .prepareStatement(rtp(UPDATE_ORACLE_JOB_DETAIL_EMPTY_BLOB));
277 ps.setString(1, job.getName());
278 ps.setString(2, job.getGroup());
279 ps.executeUpdate();
280 ps.close();
281
282 ps = conn.prepareStatement(rtp(SELECT_ORACLE_JOB_DETAIL_BLOB));
283 ps.setString(1, job.getName());
284 ps.setString(2, job.getGroup());
285
286 rs = ps.executeQuery();
287
288 int res = 0;
289
290 if (rs.next()) {
291 Blob dbBlob = writeDataToBlob(rs, 1, data);
292 ps2 = conn.prepareStatement(rtp(UPDATE_ORACLE_JOB_DETAIL_BLOB));
293
294 ps2.setBlob(1, dbBlob);
295 ps2.setString(2, job.getName());
296 ps2.setString(3, job.getGroup());
297
298 res = ps2.executeUpdate();
299 }
300
301 if (res > 0) {
302 deleteJobListeners(conn, job.getName(), job.getGroup());
303 String [] jobListeners = job.getJobListenerNames();
304 for (int i = 0; jobListeners != null && i < jobListeners.length; i++) {
305 insertJobListener(conn, job, jobListeners[i]);
306 }
307 }
308
309 return res;
310
311 } finally {
312 closeResultSet(rs);
313 closeStatement(ps);
314 closeStatement(ps2);
315 }
316 }
317
318 public int insertTrigger(Connection conn, Trigger trigger, String state,
319 JobDetail jobDetail) throws SQLException , IOException {
320
321 byte[] data = null;
322 if (trigger.getJobDataMap().size() > 0) {
323 data = serializeJobData(trigger.getJobDataMap()).toByteArray();
324 }
325
326 PreparedStatement ps = null;
327 ResultSet rs = null;
328
329 int insertResult = 0;
330
331 try {
332 ps = conn.prepareStatement(rtp(INSERT_TRIGGER));
333 ps.setString(1, trigger.getName());
334 ps.setString(2, trigger.getGroup());
335 ps.setString(3, trigger.getJobName());
336 ps.setString(4, trigger.getJobGroup());
337 setBoolean(ps, 5, trigger.isVolatile());
338 ps.setString(6, trigger.getDescription());
339 ps.setBigDecimal(7, new BigDecimal (String.valueOf(trigger
340 .getNextFireTime().getTime())));
341 long prevFireTime = -1;
342 if (trigger.getPreviousFireTime() != null) {
343 prevFireTime = trigger.getPreviousFireTime().getTime();
344 }
345 ps.setBigDecimal(8, new BigDecimal (String.valueOf(prevFireTime)));
346 ps.setString(9, state);
347 if (trigger.getClass() == SimpleTrigger.class) {
348 ps.setString(10, TTYPE_SIMPLE);
349 } else if (trigger.getClass() == CronTrigger.class) {
350 ps.setString(10, TTYPE_CRON);
351 } else {
352 ps.setString(10, TTYPE_BLOB);
353 }
354 ps.setBigDecimal(11, new BigDecimal (String.valueOf(trigger
355 .getStartTime().getTime())));
356 long endTime = 0;
357 if (trigger.getEndTime() != null) {
358 endTime = trigger.getEndTime().getTime();
359 }
360 ps.setBigDecimal(12, new BigDecimal (String.valueOf(endTime)));
361 ps.setString(13, trigger.getCalendarName());
362 ps.setInt(14, trigger.getMisfireInstruction());
363 ps.setBinaryStream(15, null, 0);
364 ps.setInt(16, trigger.getPriority());
365
366 insertResult = ps.executeUpdate();
367
368 if(data != null) {
369 ps.close();
370
371 ps = conn
372 .prepareStatement(rtp(UPDATE_ORACLE_TRIGGER_JOB_DETAIL_EMPTY_BLOB));
373 ps.setString(1, trigger.getName());
374 ps.setString(2, trigger.getGroup());
375 ps.executeUpdate();
376 ps.close();
377
378 ps = conn.prepareStatement(rtp(SELECT_ORACLE_TRIGGER_JOB_DETAIL_BLOB));
379 ps.setString(1, trigger.getName());
380 ps.setString(2, trigger.getGroup());
381
382 rs = ps.executeQuery();
383
384 int res = 0;
385
386 Blob dbBlob = null;
387 if (rs.next()) {
388 dbBlob = writeDataToBlob(rs, 1, data);
389 } else {
390 return res;
391 }
392
393 rs.close();
394 ps.close();
395
396 ps = conn.prepareStatement(rtp(UPDATE_ORACLE_TRIGGER_JOB_DETAIL_BLOB));
397 ps.setBlob(1, dbBlob);
398 ps.setString(2, trigger.getName());
399 ps.setString(3, trigger.getGroup());
400
401 res = ps.executeUpdate();
402 }
403
404 } finally {
405 closeResultSet(rs);
406 closeStatement(ps);
407 }
408
409 if (insertResult > 0) {
410 String [] trigListeners = trigger.getTriggerListenerNames();
411 for (int i = 0; trigListeners != null && i < trigListeners.length; i++) {
412 insertTriggerListener(conn, trigger, trigListeners[i]);
413 }
414 }
415
416 return insertResult;
417 }
418
419 public int updateTrigger(Connection conn, Trigger trigger, String state,
420 JobDetail jobDetail) throws SQLException , IOException {
421
422 boolean updateJobData = trigger.getJobDataMap().isDirty();
424 byte[] data = null;
425 if (updateJobData && trigger.getJobDataMap().size() > 0) {
426 data = serializeJobData(trigger.getJobDataMap()).toByteArray();
427 }
428
429 PreparedStatement ps = null;
430 PreparedStatement ps2 = null;
431 ResultSet rs = null;
432
433 int insertResult = 0;
434
435
436 try {
437 ps = conn.prepareStatement(rtp(UPDATE_ORACLE_TRIGGER));
438
439 ps.setString(1, trigger.getJobName());
440 ps.setString(2, trigger.getJobGroup());
441 setBoolean(ps, 3, trigger.isVolatile());
442 ps.setString(4, trigger.getDescription());
443 long nextFireTime = -1;
444 if (trigger.getNextFireTime() != null) {
445 nextFireTime = trigger.getNextFireTime().getTime();
446 }
447 ps.setBigDecimal(5, new BigDecimal (String.valueOf(nextFireTime)));
448 long prevFireTime = -1;
449 if (trigger.getPreviousFireTime() != null) {
450 prevFireTime = trigger.getPreviousFireTime().getTime();
451 }
452 ps.setBigDecimal(6, new BigDecimal (String.valueOf(prevFireTime)));
453 ps.setString(7, state);
454 if (trigger.getClass() == SimpleTrigger.class) {
455 ps.setString(8, TTYPE_SIMPLE);
457 } else if (trigger.getClass() == CronTrigger.class) {
458 ps.setString(8, TTYPE_CRON);
460 } else {
461 ps.setString(8, TTYPE_BLOB);
463 }
464 ps.setBigDecimal(9, new BigDecimal (String.valueOf(trigger
465 .getStartTime().getTime())));
466 long endTime = 0;
467 if (trigger.getEndTime() != null) {
468 endTime = trigger.getEndTime().getTime();
469 }
470 ps.setBigDecimal(10, new BigDecimal (String.valueOf(endTime)));
471 ps.setString(11, trigger.getCalendarName());
472 ps.setInt(12, trigger.getMisfireInstruction());
473 ps.setInt(13, trigger.getPriority());
474 ps.setString(14, trigger.getName());
475 ps.setString(15, trigger.getGroup());
476
477 insertResult = ps.executeUpdate();
478
479 if(updateJobData) {
480 ps.close();
481
482 ps = conn
483 .prepareStatement(rtp(UPDATE_ORACLE_TRIGGER_JOB_DETAIL_EMPTY_BLOB));
484 ps.setString(1, trigger.getName());
485 ps.setString(2, trigger.getGroup());
486 ps.executeUpdate();
487 ps.close();
488
489 ps = conn.prepareStatement(rtp(SELECT_ORACLE_TRIGGER_JOB_DETAIL_BLOB));
490 ps.setString(1, trigger.getName());
491 ps.setString(2, trigger.getGroup());
492
493 rs = ps.executeQuery();
494
495 int res = 0;
496
497 if (rs.next()) {
498 Blob dbBlob = writeDataToBlob(rs, 1, data);
499 ps2 = conn.prepareStatement(rtp(UPDATE_ORACLE_TRIGGER_JOB_DETAIL_BLOB));
500
501 ps2.setBlob(1, dbBlob);
502 ps2.setString(2, trigger.getName());
503 ps2.setString(3, trigger.getGroup());
504
505 res = ps2.executeUpdate();
506 }
507 }
508
509 } finally {
510 closeResultSet(rs);
511 closeStatement(ps);
512 closeStatement(ps2);
513 }
514
515 if (insertResult > 0) {
516 deleteTriggerListeners(conn, trigger.getName(), trigger.getGroup());
517
518 String [] trigListeners = trigger.getTriggerListenerNames();
519 for (int i = 0; trigListeners != null && i < trigListeners.length; i++) {
520 insertTriggerListener(conn, trigger, trigListeners[i]);
521 }
522 }
523
524 return insertResult;
525 }
526
527 public int insertCalendar(Connection conn, String calendarName,
528 Calendar calendar) throws IOException , SQLException {
529 ByteArrayOutputStream baos = serializeObject(calendar);
530
531 PreparedStatement ps = null;
532 PreparedStatement ps2 = null;
533 ResultSet rs = null;
534
535 try {
536 ps = conn.prepareStatement(rtp(INSERT_ORACLE_CALENDAR));
537 ps.setString(1, calendarName);
538
539 ps.executeUpdate();
540 ps.close();
541
542 ps = conn.prepareStatement(rtp(SELECT_ORACLE_CALENDAR_BLOB));
543 ps.setString(1, calendarName);
544
545 rs = ps.executeQuery();
546
547 if (rs.next()) {
548 Blob dbBlob = writeDataToBlob(rs, 1, baos.toByteArray());
549 ps2 = conn.prepareStatement(rtp(UPDATE_ORACLE_CALENDAR_BLOB));
550
551 ps2.setBlob(1, dbBlob);
552 ps2.setString(2, calendarName);
553
554 return ps2.executeUpdate();
555 }
556
557 return 0;
558
559 } finally {
560 closeResultSet(rs);
561 closeStatement(ps);
562 closeStatement(ps2);
563 }
564 }
565
566 public int updateCalendar(Connection conn, String calendarName,
567 Calendar calendar) throws IOException , SQLException {
568 ByteArrayOutputStream baos = serializeObject(calendar);
569
570 PreparedStatement ps = null;
571 PreparedStatement ps2 = null;
572 ResultSet rs = null;
573
574 try {
575 ps = conn.prepareStatement(rtp(SELECT_ORACLE_CALENDAR_BLOB));
576 ps.setString(1, calendarName);
577
578 rs = ps.executeQuery();
579
580 if (rs.next()) {
581 Blob dbBlob = writeDataToBlob(rs, 1, baos.toByteArray());
582 ps2 = conn.prepareStatement(rtp(UPDATE_ORACLE_CALENDAR_BLOB));
583
584 ps2.setBlob(1, dbBlob);
585 ps2.setString(2, calendarName);
586
587 return ps2.executeUpdate();
588 }
589
590 return 0;
591
592 } finally {
593 closeResultSet(rs);
594 closeStatement(ps);
595 closeStatement(ps2);
596 }
597 }
598
599 public int updateJobData(Connection conn, JobDetail job)
600 throws IOException , SQLException {
601
602 ByteArrayOutputStream baos = serializeJobData(job.getJobDataMap());
603 byte[] data = baos.toByteArray();
604
605 PreparedStatement ps = null;
606 PreparedStatement ps2 = null;
607 ResultSet rs = null;
608
609 try {
610 ps = conn.prepareStatement(rtp(SELECT_ORACLE_JOB_DETAIL_BLOB));
611 ps.setString(1, job.getName());
612 ps.setString(2, job.getGroup());
613
614 rs = ps.executeQuery();
615
616 int res = 0;
617
618 if (rs.next()) {
619 Blob dbBlob = writeDataToBlob(rs, 1, data);
620 ps2 = conn.prepareStatement(rtp(UPDATE_ORACLE_JOB_DETAIL_BLOB));
621
622 ps2.setBlob(1, dbBlob);
623 ps2.setString(2, job.getName());
624 ps2.setString(3, job.getGroup());
625
626 res = ps2.executeUpdate();
627 }
628
629 return res;
630 } finally {
631 closeResultSet(rs);
632 closeStatement(ps);
633 closeStatement(ps2);
634 }
635 }
636
637 protected Blob writeDataToBlob(ResultSet rs, int column, byte[] data) throws SQLException {
638
639 Blob blob = rs.getBlob(column);
641 if (blob == null) {
642 throw new SQLException ("Driver's Blob representation is null!");
643 }
644
645 if (blob instanceof oracle.sql.BLOB) { ((oracle.sql.BLOB) blob).putBytes(1, data);
647 return blob;
648 } else {
649 throw new SQLException (
650 "Driver's Blob representation is of an unsupported type: "
651 + blob.getClass().getName());
652 }
653 }
654 }
655
656 |
Popular Tags |
Java API By Example, From Geeks To Geeks.