StdJDBCDelegate


1   /* 
2    * Copyright 2004-2005 OpenSymphony 
3    * 
4    * Licensed under the Apache License, Version 2.0 (the "License"); you may not 
5    * use this file except in compliance with the License. You may obtain a copy 
6    * of the License at 
7    * 
8    *   http://www.apache.org/licenses/LICENSE-2.0 
9    *   
10   * Unless required by applicable law or agreed to in writing, software 
11   * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT 
12   * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the 
13   * License for the specific language governing permissions and limitations 
14   * under the License.
15   * 
16   */
17  
18  /*
19   * Previously Copyright (c) 2001-2004 James House
20   */
21  package org.quartz.impl.jdbcjobstore;
22  
23  import java.io.ByteArrayInputStream  ;
24  import java.io.ByteArrayOutputStream  ;
25  import java.io.IOException  ;
26  import java.io.InputStream  ;
27  import java.io.NotSerializableException  ;
28  import java.io.ObjectInputStream  ;
29  import java.io.ObjectOutputStream  ;
30  import java.math.BigDecimal  ;
31  import java.sql.Blob  ;
32  import java.sql.Connection  ;
33  import java.sql.PreparedStatement  ;
34  import java.sql.ResultSet  ;
35  import java.sql.SQLException  ;
36  import java.sql.Statement  ;
37  import java.util.ArrayList  ;
38  import java.util.Date  ;
39  import java.util.HashMap  ;
40  import java.util.HashSet  ;
41  import java.util.Iterator  ;
42  import java.util.LinkedList  ;
43  import java.util.List  ;
44  import java.util.Map  ;
45  import java.util.Properties  ;
46  import java.util.Set  ;
47  import java.util.TimeZone  ;
48  
49  import org.apache.commons.logging.Log;
50  import org.quartz.Calendar;
51  import org.quartz.CronTrigger;
52  import org.quartz.JobDataMap;
53  import org.quartz.JobDetail;
54  import org.quartz.Scheduler;
55  import org.quartz.SimpleTrigger;
56  import org.quartz.Trigger;
57  import org.quartz.spi.ClassLoadHelper;
58  import org.quartz.utils.Key;
59  import org.quartz.utils.TriggerStatus;
60  
61  /**
62   * <p>
63   * This is meant to be an abstract base class for most, if not all, <code>{@link org.quartz.impl.jdbcjobstore.DriverDelegate}</code>
64   * implementations. Subclasses should override only those methods that need
65   * special handling for the DBMS driver in question.
66   * </p>
67   * 
68   * @author <a HREF="mailto:jeff@binaryfeed.org">Jeffrey Wescott</a>
69   * @author James House
70   * @author Eric Mueller
71   */
72  public class StdJDBCDelegate implements DriverDelegate, StdJDBCConstants {
73  
74      /*
75       * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
76       * 
77       * Data members.
78       * 
79       * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
80       */
81  
82      protected Log logger = null;
83  
84      protected String   tablePrefix = DEFAULT_TABLE_PREFIX;
85  
86      protected String   instanceId;
87  
88      protected boolean useProperties;
89  
90      /*
91       * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
92       * 
93       * Constructors.
94       * 
95       * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
96       */
97  
98      /**
99       * <p>
100      * Create new StdJDBCDelegate instance.
101      * </p>
102      * 
103      * @param logger
104      *          the logger to use during execution
105      * @param tablePrefix
106      *          the prefix of all table names
107      */
108     public StdJDBCDelegate(Log logger, String   tablePrefix, String   instanceId) {
109         this.logger = logger;
110         this.tablePrefix = tablePrefix;
111         this.instanceId = instanceId;
112     }
113 
114     /**
115      * <p>
116      * Create new StdJDBCDelegate instance.
117      * </p>
118      * 
119      * @param logger
120      *          the logger to use during execution
121      * @param tablePrefix
122      *          the prefix of all table names
123      */
124     public StdJDBCDelegate(Log logger, String   tablePrefix, String   instanceId,
125             Boolean   useProperties) {
126         this.logger = logger;
127         this.tablePrefix = tablePrefix;
128         this.instanceId = instanceId;
129         this.useProperties = useProperties.booleanValue();
130     }
131 
132     /*
133      * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
134      * 
135      * Interface.
136      * 
137      * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
138      */
139 
140     protected boolean canUseProperties() {
141         return useProperties;
142     }
143 
144     //---------------------------------------------------------------------------
145     // startup / recovery
146     //---------------------------------------------------------------------------
147 
148     /**
149      * <p>
150      * Insert the job detail record.
151      * </p>
152      * 
153      * @param conn
154      *          the DB Connection
155      * @param newState
156      *          the new state for the triggers
157      * @param oldState1
158      *          the first old state to update
159      * @param oldState2
160      *          the second old state to update
161      * @return number of rows updated
162      */
163     public int updateTriggerStatesFromOtherStates(Connection   conn,
164             String   newState, String   oldState1, String   oldState2)
165         throws SQLException   {
166         PreparedStatement   ps = null;
167 
168         try {
169             ps = conn
170                     .prepareStatement(rtp(UPDATE_TRIGGER_STATES_FROM_OTHER_STATES));
171             ps.setString(1, newState);
172             ps.setString(2, oldState1);
173             ps.setString(3, oldState2);
174             return ps.executeUpdate();
175         } finally {
176             closeStatement(ps);
177         }
178     }
179 
180     /**
181      * <p>
182      * Get the names of all of the triggers that have misfired.
183      * </p>
184      * 
185      * @param conn
186      *          the DB Connection
187      * @return an array of <code>{@link
188      * org.quartz.utils.Key}</code> objects
189      */
190     public Key[] selectMisfiredTriggers(Connection   conn, long ts)
191         throws SQLException   {
192         PreparedStatement   ps = null;
193         ResultSet   rs = null;
194 
195         try {
196             ps = conn.prepareStatement(rtp(SELECT_MISFIRED_TRIGGERS));
197             ps.setBigDecimal(1, new BigDecimal  (String.valueOf(ts)));
198             rs = ps.executeQuery();
199 
200             ArrayList   list = new ArrayList  ();
201             while (rs.next()) {
202                 String   triggerName = rs.getString(COL_TRIGGER_NAME);
203                 String   groupName = rs.getString(COL_TRIGGER_GROUP);
204                 list.add(new Key(triggerName, groupName));
205             }
206             Object  [] oArr = list.toArray();
207             Key[] kArr = new Key[oArr.length];
208             System.arraycopy(oArr, 0, kArr, 0, oArr.length);
209             return kArr;
210         } finally {
211             closeResultSet(rs);
212             closeStatement(ps);
213         }
214     }
215 
216     /**
217      * <p>
218      * Select all of the triggers in a given state.
219      * </p>
220      * 
221      * @param conn
222      *          the DB Connection
223      * @param state
224      *          the state the triggers must be in
225      * @return an array of trigger <code>Key</code> s
226      */
227     public Key[] selectTriggersInState(Connection   conn, String   state)
228         throws SQLException   {
229         PreparedStatement   ps = null;
230         ResultSet   rs = null;
231 
232         try {
233             ps = conn.prepareStatement(rtp(SELECT_TRIGGERS_IN_STATE));
234             ps.setString(1, state);
235             rs = ps.executeQuery();
236 
237             ArrayList   list = new ArrayList  ();
238             while (rs.next()) {
239                 list.add(new Key(rs.getString(1), rs.getString(2)));
240             }
241 
242             Key[] sArr = (Key[]) list.toArray(new Key[list.size()]);
243             return sArr;
244         } finally {
245             closeResultSet(rs);
246             closeStatement(ps);
247         }
248     }
249 
250     public Key[] selectMisfiredTriggersInState(Connection   conn, String   state,
251             long ts) throws SQLException   {
252         PreparedStatement   ps = null;
253         ResultSet   rs = null;
254 
255         try {
256             ps = conn.prepareStatement(rtp(SELECT_MISFIRED_TRIGGERS_IN_STATE));
257             ps.setBigDecimal(1, new BigDecimal  (String.valueOf(ts)));
258             ps.setString(2, state);
259             rs = ps.executeQuery();
260 
261             ArrayList   list = new ArrayList  ();
262             while (rs.next()) {
263                 String   triggerName = rs.getString(COL_TRIGGER_NAME);
264                 String   groupName = rs.getString(COL_TRIGGER_GROUP);
265                 list.add(new Key(triggerName, groupName));
266             }
267             Object  [] oArr = list.toArray();
268             Key[] kArr = new Key[oArr.length];
269             System.arraycopy(oArr, 0, kArr, 0, oArr.length);
270             return kArr;
271         } finally {
272             closeResultSet(rs);
273             closeStatement(ps);
274         }
275     }
276 
277     /**
278      * <p>
279      * Get the names of all of the triggers in the given states that have
280      * misfired - according to the given timestamp.  No more than count will
281      * be returned.
282      * </p>
283      * 
284      * @param conn The DB Connection
285      * @param count The most misfired triggers to return, negative for all
286      * @param resultList Output parameter.  A List of 
287      *      <code>{@link org.quartz.utils.Key}</code> objects.  Must not be null.
288      *          
289      * @return Whether there are more misfired triggers left to find beyond
290      *         the given count.
291      */
292     public boolean selectMisfiredTriggersInStates(Connection   conn, String   state1, String   state2,
293         long ts, int count, List   resultList) throws SQLException   {
294         PreparedStatement   ps = null;
295         ResultSet   rs = null;
296 
297         try {
298             ps = conn.prepareStatement(rtp(SELECT_MISFIRED_TRIGGERS_IN_STATES));
299             ps.setBigDecimal(1, new BigDecimal  (String.valueOf(ts)));
300             ps.setString(2, state1);
301             ps.setString(3, state2);
302             rs = ps.executeQuery();
303 
304             boolean hasReachedLimit = false;
305             while (rs.next() && (hasReachedLimit == false)) {
306                 if (resultList.size() == count) {
307                     hasReachedLimit = true;
308                 } else {
309                     String   triggerName = rs.getString(COL_TRIGGER_NAME);
310                     String   groupName = rs.getString(COL_TRIGGER_GROUP);
311                     resultList.add(new Key(triggerName, groupName));
312                 }
313             }
314             
315             return hasReachedLimit;
316         } finally {
317             closeResultSet(rs);
318             closeStatement(ps);
319         }
320     }
321     
322     /**
323      * <p>
324      * Get the number of triggers in the given states that have
325      * misfired - according to the given timestamp.
326      * </p>
327      * 
328      * @param conn the DB Connection
329      */
330     public int countMisfiredTriggersInStates(
331             Connection   conn, String   state1, String   state2, long ts) throws SQLException   {
332         PreparedStatement   ps = null;
333         ResultSet   rs = null;
334 
335         try {
336             ps = conn.prepareStatement(rtp(COUNT_MISFIRED_TRIGGERS_IN_STATES));
337             ps.setBigDecimal(1, new BigDecimal  (String.valueOf(ts)));
338             ps.setString(2, state1);
339             ps.setString(3, state2);
340             rs = ps.executeQuery();
341 
342             if (rs.next()) {
343                 return rs.getInt(1);
344             }
345 
346             throw new SQLException  ("No misfired trigger count returned.");
347         } finally {
348             closeResultSet(rs);
349             closeStatement(ps);
350         }
351     }
352 
353     /**
354      * <p>
355      * Get the names of all of the triggers in the given group and state that
356      * have misfired.
357      * </p>
358      * 
359      * @param conn
360      *          the DB Connection
361      * @return an array of <code>{@link
362      * org.quartz.utils.Key}</code> objects
363      */
364     public Key[] selectMisfiredTriggersInGroupInState(Connection   conn,
365             String   groupName, String   state, long ts) throws SQLException   {
366         PreparedStatement   ps = null;
367         ResultSet   rs = null;
368 
369         try {
370             ps = conn
371                     .prepareStatement(rtp(SELECT_MISFIRED_TRIGGERS_IN_GROUP_IN_STATE));
372             ps.setBigDecimal(1, new BigDecimal  (String.valueOf(ts)));
373             ps.setString(2, groupName);
374             ps.setString(3, state);
375             rs = ps.executeQuery();
376 
377             ArrayList   list = new ArrayList  ();
378             while (rs.next()) {
379                 String   triggerName = rs.getString(COL_TRIGGER_NAME);
380                 list.add(new Key(triggerName, groupName));
381             }
382             Object  [] oArr = list.toArray();
383             Key[] kArr = new Key[oArr.length];
384             System.arraycopy(oArr, 0, kArr, 0, oArr.length);
385             return kArr;
386         } finally {
387             closeResultSet(rs);
388             closeStatement(ps);
389         }
390     }
391 
392     /**
393      * <p>
394      * Select all of the triggers for jobs that are requesting recovery. The
395      * returned trigger objects will have unique "recoverXXX" trigger names and
396      * will be in the <code>{@link
397      * org.quartz.Scheduler}.DEFAULT_RECOVERY_GROUP</code>
398      * trigger group.
399      * </p>
400      * 
401      * <p>
402      * In order to preserve the ordering of the triggers, the fire time will be
403      * set from the <code>COL_FIRED_TIME</code> column in the <code>TABLE_FIRED_TRIGGERS</code>
404      * table. The caller is responsible for calling <code>computeFirstFireTime</code>
405      * on each returned trigger. It is also up to the caller to insert the
406      * returned triggers to ensure that they are fired.
407      * </p>
408      * 
409      * @param conn
410      *          the DB Connection
411      * @return an array of <code>{@link org.quartz.Trigger}</code> objects
412      */
413     public Trigger[] selectTriggersForRecoveringJobs(Connection   conn)
414         throws SQLException  , IOException  , ClassNotFoundException   {
415         PreparedStatement   ps = null;
416         ResultSet   rs = null;
417 
418         try {
419             ps = conn
420                     .prepareStatement(rtp(SELECT_INSTANCES_RECOVERABLE_FIRED_TRIGGERS));
421             ps.setString(1, instanceId);
422             setBoolean(ps, 2, true);
423             rs = ps.executeQuery();
424 
425             long dumId = System.currentTimeMillis();
426             ArrayList   list = new ArrayList  ();
427             while (rs.next()) {
428                 String   jobName = rs.getString(COL_JOB_NAME);
429                 String   jobGroup = rs.getString(COL_JOB_GROUP);
430                 String   trigName = rs.getString(COL_TRIGGER_NAME);
431                 String   trigGroup = rs.getString(COL_TRIGGER_GROUP);
432                 long firedTime = rs.getLong(COL_FIRED_TIME);
433                 int priority = rs.getInt(COL_PRIORITY);
434                 SimpleTrigger rcvryTrig = new SimpleTrigger("recover_"
435                         + instanceId + "_" + String.valueOf(dumId++),
436                         Scheduler.DEFAULT_RECOVERY_GROUP, new Date  (firedTime));
437                 rcvryTrig.setJobName(jobName);
438                 rcvryTrig.setJobGroup(jobGroup);
439                 rcvryTrig.setPriority(priority);
440                 rcvryTrig
441                         .setMisfireInstruction(SimpleTrigger.MISFIRE_INSTRUCTION_FIRE_NOW);
442 
443                 JobDataMap jd = selectTriggerJobDataMap(conn, trigName, trigGroup);
444                 jd.put(Scheduler.FAILED_JOB_ORIGINAL_TRIGGER_NAME, trigName);
445                 jd.put(Scheduler.FAILED_JOB_ORIGINAL_TRIGGER_GROUP, trigGroup);
446                 jd.put(Scheduler.FAILED_JOB_ORIGINAL_TRIGGER_FIRETIME_IN_MILLISECONDS, String.valueOf(firedTime));
447                 rcvryTrig.setJobDataMap(jd);
448                 
449                 list.add(rcvryTrig);
450             }
451             Object  [] oArr = list.toArray();
452             Trigger[] tArr = new Trigger[oArr.length];
453             System.arraycopy(oArr, 0, tArr, 0, oArr.length);
454             return tArr;
455         } finally {
456             closeResultSet(rs);
457             closeStatement(ps);
458         }
459     }
460 
461     /**
462      * <p>
463      * Delete all fired triggers.
464      * </p>
465      * 
466      * @param conn
467      *          the DB Connection
468      * @return the number of rows deleted
469      */
470     public int deleteFiredTriggers(Connection   conn) throws SQLException   {
471         PreparedStatement   ps = null;
472 
473         try {
474             ps = conn.prepareStatement(rtp(DELETE_FIRED_TRIGGERS));
475 
476             return ps.executeUpdate();
477         } finally {
478             closeStatement(ps);
479         }
480     }
481 
482     public int deleteFiredTriggers(Connection   conn, String   instanceId)
483         throws SQLException   {
484         PreparedStatement   ps = null;
485 
486         try {
487             ps = conn.prepareStatement(rtp(DELETE_INSTANCES_FIRED_TRIGGERS));
488             ps.setString(1, instanceId);
489 
490             return ps.executeUpdate();
491         } finally {
492             closeStatement(ps);
493         }
494     }
495 
496     //---------------------------------------------------------------------------
497     // jobs
498     //---------------------------------------------------------------------------
499 
500     /**
501      * <p>
502      * Insert the job detail record.
503      * </p>
504      * 
505      * @param conn
506      *          the DB Connection
507      * @param job
508      *          the job to insert
509      * @return number of rows inserted
510      * @throws IOException
511      *           if there were problems serializing the JobDataMap
512      */
513     public int insertJobDetail(Connection   conn, JobDetail job)
514         throws IOException  , SQLException   {
515         ByteArrayOutputStream   baos = serializeJobData(job.getJobDataMap());
516 
517         PreparedStatement   ps = null;
518 
519         int insertResult = 0;
520 
521         try {
522             ps = conn.prepareStatement(rtp(INSERT_JOB_DETAIL));
523             ps.setString(1, job.getName());
524             ps.setString(2, job.getGroup());
525             ps.setString(3, job.getDescription());
526             ps.setString(4, job.getJobClass().getName());
527             setBoolean(ps, 5, job.isDurable());
528             setBoolean(ps, 6, job.isVolatile());
529             setBoolean(ps, 7, job.isStateful());
530             setBoolean(ps, 8, job.requestsRecovery());
531             setBytes(ps, 9, baos);
532 
533             insertResult = ps.executeUpdate();
534         } finally {
535             closeStatement(ps);
536         }
537 
538         if (insertResult > 0) {
539             String  [] jobListeners = job.getJobListenerNames();
540             for (int i = 0; jobListeners != null && i < jobListeners.length; i++) {
541                 insertJobListener(conn, job, jobListeners[i]);
542             }
543         }
544 
545         return insertResult;
546     }
547 
548     /**
549      * <p>
550      * Update the job detail record.
551      * </p>
552      * 
553      * @param conn
554      *          the DB Connection
555      * @param job
556      *          the job to update
557      * @return number of rows updated
558      * @throws IOException
559      *           if there were problems serializing the JobDataMap
560      */
561     public int updateJobDetail(Connection   conn, JobDetail job)
562         throws IOException  , SQLException   {
563         ByteArrayOutputStream   baos = serializeJobData(job.getJobDataMap());
564 
565         PreparedStatement   ps = null;
566 
567         int insertResult = 0;
568 
569         try {
570             ps = conn.prepareStatement(rtp(UPDATE_JOB_DETAIL));
571             ps.setString(1, job.getDescription());
572             ps.setString(2, job.getJobClass().getName());
573             setBoolean(ps, 3, job.isDurable());
574             setBoolean(ps, 4, job.isVolatile());
575             setBoolean(ps, 5, job.isStateful());
576             setBoolean(ps, 6, job.requestsRecovery());
577             setBytes(ps, 7, baos);
578             ps.setString(8, job.getName());
579             ps.setString(9, job.getGroup());
580 
581             insertResult = ps.executeUpdate();
582         } finally {
583             closeStatement(ps);
584         }
585 
586         if (insertResult > 0) {
587             deleteJobListeners(conn, job.getName(), job.getGroup());
588 
589             String  [] jobListeners = job.getJobListenerNames();
590             for (int i = 0; jobListeners != null && i < jobListeners.length; i++) {
591                 insertJobListener(conn, job, jobListeners[i]);
592             }
593         }
594 
595         return insertResult;
596     }
597 
598     /**
599      * <p>
600      * Get the names of all of the triggers associated with the given job.
601      * </p>
602      * 
603      * @param conn
604      *          the DB Connection
605      * @param jobName
606      *          the name of the job
607      * @param groupName
608      *          the group containing the job
609      * @return an array of <code>{@link
610      * org.quartz.utils.Key}</code> objects
611      */
612     public Key[] selectTriggerNamesForJob(Connection   conn, String   jobName,
613             String   groupName) throws SQLException   {
614         PreparedStatement   ps = null;
615         ResultSet   rs = null;
616 
617         try {
618             ps = conn.prepareStatement(rtp(SELECT_TRIGGERS_FOR_JOB));
619             ps.setString(1, jobName);
620             ps.setString(2, groupName);
621             rs = ps.executeQuery();
622 
623             ArrayList   list = new ArrayList  (10);
624             while (rs.next()) {
625                 String   trigName = rs.getString(COL_TRIGGER_NAME);
626                 String   trigGroup = rs.getString(COL_TRIGGER_GROUP);
627                 list.add(new Key(trigName, trigGroup));
628             }
629             Object  [] oArr = list.toArray();
630             Key[] kArr = new Key[oArr.length];
631             System.arraycopy(oArr, 0, kArr, 0, oArr.length);
632             return kArr;
633         } finally {
634             closeResultSet(rs);
635             closeStatement(ps);
636         }
637     }
638 
639     /**
640      * <p>
641      * Delete all job listeners for the given job.
642      * </p>
643      * 
644      * @param conn
645      *          the DB Connection
646      * @param jobName
647      *          the name of the job
648      * @param groupName
649      *          the group containing the job
650      * @return the number of rows deleted
651      */
652     public int deleteJobListeners(Connection   conn, String   jobName,
653             String   groupName) throws SQLException   {
654         PreparedStatement   ps = null;
655 
656         try {
657             ps = conn.prepareStatement(rtp(DELETE_JOB_LISTENERS));
658             ps.setString(1, jobName);
659             ps.setString(2, groupName);
660             return ps.executeUpdate();
661         } finally {
662             closeStatement(ps);
663         }
664     }
665 
666     /**
667      * <p>
668      * Delete the job detail record for the given job.
669      * </p>
670      * 
671      * @param conn
672      *          the DB Connection
673      * @param jobName
674      *          the name of the job
675      * @param groupName
676      *          the group containing the job
677      * @return the number of rows deleted
678      */
679     public int deleteJobDetail(Connection   conn, String   jobName, String   groupName)
680         throws SQLException   {
681         PreparedStatement   ps = null;
682 
683         try {
684             if (logger.isDebugEnabled()) {
685                 logger.debug("Deleting job: " + groupName + "." + jobName);
686             }
687             ps = conn.prepareStatement(rtp(DELETE_JOB_DETAIL));
688             ps.setString(1, jobName);
689             ps.setString(2, groupName);
690             return ps.executeUpdate();
691         } finally {
692             closeStatement(ps);
693         }
694     }
695 
696     /**
697      * <p>
698      * Check whether or not the given job is stateful.
699      * </p>
700      * 
701      * @param conn
702      *          the DB Connection
703      * @param jobName
704      *          the name of the job
705      * @param groupName
706      *          the group containing the job
707      * @return true if the job exists and is stateful, false otherwise
708      */
709     public boolean isJobStateful(Connection   conn, String   jobName,
710             String   groupName) throws SQLException   {
711         PreparedStatement   ps = null;
712         ResultSet   rs = null;
713 
714         try {
715             ps = conn.prepareStatement(rtp(SELECT_JOB_STATEFUL));
716             ps.setString(1, jobName);
717             ps.setString(2, groupName);
718             rs = ps.executeQuery();
719             if (!rs.next()) { return false; }
720             return getBoolean(rs, COL_IS_STATEFUL);
721         } finally {
722             closeResultSet(rs);
723             closeStatement(ps);
724         }
725     }
726 
727     /**
728      * <p>
729      * Check whether or not the given job exists.
730      * </p>
731      * 
732      * @param conn
733      *          the DB Connection
734      * @param jobName
735      *          the name of the job
736      * @param groupName
737      *          the group containing the job
738      * @return true if the job exists, false otherwise
739      */
740     public boolean jobExists(Connection   conn, String   jobName, String   groupName)
741         throws SQLException   {
742         PreparedStatement   ps = null;
743         ResultSet   rs = null;
744 
745         try {
746             ps = conn.prepareStatement(rtp(SELECT_JOB_EXISTENCE));
747             ps.setString(1, jobName);
748             ps.setString(2, groupName);
749             rs = ps.executeQuery();
750             if (rs.next()) {
751                 return true;
752             } else {
753                 return false;
754             }
755         } finally {
756             closeResultSet(rs);
757             closeStatement(ps);
758         }
759 
760     }
761 
762     /**
763      * <p>
764      * Update the job data map for the given job.
765      * </p>
766      * 
767      * @param conn
768      *          the DB Connection
769      * @param job
770      *          the job to update
771      * @return the number of rows updated
772      */
773     public int updateJobData(Connection   conn, JobDetail job)
774         throws IOException  , SQLException   {
775         ByteArrayOutputStream   baos = serializeJobData(job.getJobDataMap());
776 
777         PreparedStatement   ps = null;
778 
779         try {
780             ps = conn.prepareStatement(rtp(UPDATE_JOB_DATA));
781             setBytes(ps, 1, baos);
782             ps.setString(2, job.getName());
783             ps.setString(3, job.getGroup());
784 
785             return ps.executeUpdate();
786         } finally {
787             closeStatement(ps);
788         }
789     }
790 
791     /**
792      * <p>
793      * Associate a listener with a job.
794      * </p>
795      * 
796      * @param conn
797      *          the DB Connection
798      * @param job
799      *          the job to associate with the listener
800      * @param listener
801      *          the listener to insert
802      * @return the number of rows inserted
803      */
804     public int insertJobListener(Connection   conn, JobDetail job, String   listener)
805         throws SQLException   {
806         PreparedStatement   ps = null;
807 
808         try {
809             ps = conn.prepareStatement(rtp(INSERT_JOB_LISTENER));
810             ps.setString(1, job.getName());
811             ps.setString(2, job.getGroup());
812             ps.setString(3, listener);
813 
814             return ps.executeUpdate();
815         } finally {
816             closeStatement(ps);
817         }
818     }
819 
820     /**
821      * <p>
822      * Get all of the listeners for a given job.
823      * </p>
824      * 
825      * @param conn
826      *          the DB Connection
827      * @param jobName
828      *          the job name whose listeners are wanted
829      * @param groupName
830      *          the group containing the job
831      * @return array of <code>String</code> listener names
832      */
833     public String  [] selectJobListeners(Connection   conn, String   jobName,
834             String   groupName) throws SQLException   {
835         PreparedStatement   ps = null;
836         ResultSet   rs = null;
837 
838         try {
839             ArrayList   list = new ArrayList  ();
840             ps = conn.prepareStatement(rtp(SELECT_JOB_LISTENERS));
841             ps.setString(1, jobName);
842             ps.setString(2, groupName);
843             rs = ps.executeQuery();
844 
845             while (rs.next()) {
846                 list.add(rs.getString(1));
847             }
848 
849             Object  [] oArr = list.toArray();
850             String  [] sArr = new String  [oArr.length];
851             System.arraycopy(oArr, 0, sArr, 0, oArr.length);
852             return sArr;
853         } finally {
854             closeResultSet(rs);
855             closeStatement(ps);
856         }
857     }
858 
859     /**
860      * <p>
861      * Select the JobDetail object for a given job name / group name.
862      * </p>
863      * 
864      * @param conn
865      *          the DB Connection
866      * @param jobName
867      *          the job name whose listeners are wanted
868      * @param groupName
869      *          the group containing the job
870      * @return the populated JobDetail object
871      * @throws ClassNotFoundException
872      *           if a class found during deserialization cannot be found or if
873      *           the job class could not be found
874      * @throws IOException
875      *           if deserialization causes an error
876      */
877     public JobDetail selectJobDetail(Connection   conn, String   jobName,
878             String   groupName, ClassLoadHelper loadHelper)
879         throws ClassNotFoundException  , IOException  , SQLException   {
880         PreparedStatement   ps = null;
881         ResultSet   rs = null;
882 
883         try {
884             ps = conn.prepareStatement(rtp(SELECT_JOB_DETAIL));
885             ps.setString(1, jobName);
886             ps.setString(2, groupName);
887             rs = ps.executeQuery();
888 
889             JobDetail job = null;
890 
891             if (rs.next()) {
892                 job = new JobDetail();
893 
894                 job.setName(rs.getString(COL_JOB_NAME));
895                 job.setGroup(rs.getString(COL_JOB_GROUP));
896                 job.setDescription(rs.getString(COL_DESCRIPTION));
897                 job.setJobClass(loadHelper.loadClass(rs
898                         .getString(COL_JOB_CLASS)));
899                 job.setDurability(getBoolean(rs, COL_IS_DURABLE));
900                 job.setVolatility(getBoolean(rs, COL_IS_VOLATILE));
901                 job.setRequestsRecovery(getBoolean(rs, COL_REQUESTS_RECOVERY));
902 
903                 Map   map = null;
904                 if (canUseProperties()) {
905                     map = getMapFromProperties(rs);
906                 } else {
907                     map = (Map  ) getObjectFromBlob(rs, COL_JOB_DATAMAP);
908                 }
909 
910                 if (null != map) {
911                     job.setJobDataMap(new JobDataMap(map));
912                 }
913             }
914 
915             return job;
916         } finally {
917             closeResultSet(rs);
918             closeStatement(ps);
919         }
920     }
921 
922     /**
923      * build Map from java.util.Properties encoding.
924      */
925     private Map   getMapFromProperties(ResultSet   rs)
926         throws ClassNotFoundException  , IOException  , SQLException   {
927         Map   map;
928         InputStream   is = (InputStream  ) getJobDetailFromBlob(rs, COL_JOB_DATAMAP);
929         if(is == null) {
930             return null;
931         }
932         Properties   properties = new Properties  ();
933         if (is != null) {
934             try {
935                 properties.load(is);
936             } finally {
937                 is.close();
938             }
939         }
940         map = convertFromProperty(properties);
941         return map;
942     }
943 
944     /**
945      * <p>
946      * Select the total number of jobs stored.
947      * </p>
948      * 
949      * @param conn
950      *          the DB Connection
951      * @return the total number of jobs stored
952      */
953     public int selectNumJobs(Connection   conn) throws SQLException   {
954         PreparedStatement   ps = null;
955         ResultSet   rs = null;
956 
957         try {
958             int count = 0;
959             ps = conn.prepareStatement(rtp(SELECT_NUM_JOBS));
960             rs = ps.executeQuery();
961 
962             if (rs.next()) {
963                 count = rs.getInt(1);
964             }
965 
966             return count;
967         } finally {
968             closeResultSet(rs);
969             closeStatement(ps);
970         }
971     }
972 
973     /**
974      * <p>
975      * Select all of the job group names that are stored.
976      * </p>
977      * 
978      * @param conn
979      *          the DB Connection
980      * @return an array of <code>String</code> group names
981      */
982     public String  [] selectJobGroups(Connection   conn) throws SQLException   {
983         PreparedStatement   ps = null;
984         ResultSet   rs = null;
985 
986         try {
987             ps = conn.prepareStatement(rtp(SELECT_JOB_GROUPS));
988             rs = ps.executeQuery();
989 
990             ArrayList   list = new ArrayList  ();
991             while (rs.next()) {
992                 list.add(rs.getString(1));
993             }
994 
995             Object  [] oArr = list.toArray();
996             String  [] sArr = new String  [oArr.length];
997             System.arraycopy(oArr, 0, sArr, 0, oArr.length);
998             return sArr;
999         } finally {
1000            closeResultSet(rs);
1001            closeStatement(ps);
1002        }
1003    }
1004
1005    /**
1006     * <p>
1007     * Select all of the jobs contained in a given group.
1008     * </p>
1009     * 
1010     * @param conn
1011     *          the DB Connection
1012     * @param groupName
1013     *          the group containing the jobs
1014     * @return an array of <code>String</code> job names
1015     */
1016    public String  [] selectJobsInGroup(Connection   conn, String   groupName)
1017        throws SQLException   {
1018        PreparedStatement   ps = null;
1019        ResultSet   rs = null;
1020
1021        try {
1022            ps = conn.prepareStatement(rtp(SELECT_JOBS_IN_GROUP));
1023            ps.setString(1, groupName);
1024            rs = ps.executeQuery();
1025
1026            ArrayList   list = new ArrayList  ();
1027            while (rs.next()) {
1028                list.add(rs.getString(1));
1029            }
1030
1031            Object  [] oArr = list.toArray();
1032            String  [] sArr = new String  [oArr.length];
1033            System.arraycopy(oArr, 0, sArr, 0, oArr.length);
1034            return sArr;
1035        } finally {
1036            closeResultSet(rs);
1037            closeStatement(ps);
1038        }
1039    }
1040
1041    //---------------------------------------------------------------------------
1042    // triggers
1043    //---------------------------------------------------------------------------
1044
1045    /**
1046     * <p>
1047     * Insert the base trigger data.
1048     * </p>
1049     * 
1050     * @param conn
1051     *          the DB Connection
1052     * @param trigger
1053     *          the trigger to insert
1054     * @param state
1055     *          the state that the trigger should be stored in
1056     * @return the number of rows inserted
1057     */
1058    public int insertTrigger(Connection   conn, Trigger trigger, String   state,
1059            JobDetail jobDetail) throws SQLException  , IOException   {
1060
1061        ByteArrayOutputStream   baos = null;
1062        if(trigger.getJobDataMap().size() > 0) {
1063            baos = serializeJobData(trigger.getJobDataMap());
1064        }
1065        
1066        PreparedStatement   ps = null;
1067
1068        int insertResult = 0;
1069
1070        try {
1071            ps = conn.prepareStatement(rtp(INSERT_TRIGGER));
1072            ps.setString(1, trigger.getName());
1073            ps.setString(2, trigger.getGroup());
1074            ps.setString(3, trigger.getJobName());
1075            ps.setString(4, trigger.getJobGroup());
1076            setBoolean(ps, 5, trigger.isVolatile());
1077            ps.setString(6, trigger.getDescription());
1078            ps.setBigDecimal(7, new BigDecimal  (String.valueOf(trigger
1079                    .getNextFireTime().getTime())));
1080            long prevFireTime = -1;
1081            if (trigger.getPreviousFireTime() != null) {
1082                prevFireTime = trigger.getPreviousFireTime().getTime();
1083            }
1084            ps.setBigDecimal(8, new BigDecimal  (String.valueOf(prevFireTime)));
1085            ps.setString(9, state);
1086            if (trigger.getClass() == SimpleTrigger.class) {
1087                ps.setString(10, TTYPE_SIMPLE);
1088            } else if (trigger.getClass() == CronTrigger.class) {
1089                ps.setString(10, TTYPE_CRON);
1090            } else {
1091                ps.setString(10, TTYPE_BLOB);
1092            }
1093            ps.setBigDecimal(11, new BigDecimal  (String.valueOf(trigger
1094                    .getStartTime().getTime())));
1095            long endTime = 0;
1096            if (trigger.getEndTime() != null) {
1097                endTime = trigger.getEndTime().getTime();
1098            }
1099            ps.setBigDecimal(12, new BigDecimal  (String.valueOf(endTime)));
1100            ps.setString(13, trigger.getCalendarName());
1101            ps.setInt(14, trigger.getMisfireInstruction());
1102            setBytes(ps, 15, baos);
1103            ps.setInt(16, trigger.getPriority());
1104            
1105            insertResult = ps.executeUpdate();
1106        } finally {
1107            closeStatement(ps);
1108        }
1109
1110        if (insertResult > 0) {
1111            String  [] trigListeners = trigger.getTriggerListenerNames();
1112            for (int i = 0; trigListeners != null && i < trigListeners.length; i++) {
1113                insertTriggerListener(conn, trigger, trigListeners[i]);
1114            }
1115        }
1116
1117        return insertResult;
1118    }
1119
1120    /**
1121     * <p>
1122     * Insert the simple trigger data.
1123     * </p>
1124     * 
1125     * @param conn
1126     *          the DB Connection
1127     * @param trigger
1128     *          the trigger to insert
1129     * @return the number of rows inserted
1130     */
1131    public int insertSimpleTrigger(Connection   conn, SimpleTrigger trigger)
1132        throws SQLException   {
1133        PreparedStatement   ps = null;
1134
1135        try {
1136            ps = conn.prepareStatement(rtp(INSERT_SIMPLE_TRIGGER));
1137            ps.setString(1, trigger.getName());
1138            ps.setString(2, trigger.getGroup());
1139            ps.setInt(3, trigger.getRepeatCount());
1140            ps.setBigDecimal(4, new BigDecimal  (String.valueOf(trigger
1141                    .getRepeatInterval())));
1142            ps.setInt(5, trigger.getTimesTriggered());
1143
1144            return ps.executeUpdate();
1145        } finally {
1146            closeStatement(ps);
1147        }
1148    }
1149
1150    /**
1151     * <p>
1152     * Insert the cron trigger data.
1153     * </p>
1154     * 
1155     * @param conn
1156     *          the DB Connection
1157     * @param trigger
1158     *          the trigger to insert
1159     * @return the number of rows inserted
1160     */
1161    public int insertCronTrigger(Connection   conn, CronTrigger trigger)
1162        throws SQLException   {
1163        PreparedStatement   ps = null;
1164
1165        try {
1166            ps = conn.prepareStatement(rtp(INSERT_CRON_TRIGGER));
1167            ps.setString(1, trigger.getName());
1168            ps.setString(2, trigger.getGroup());
1169            ps.setString(3, trigger.getCronExpression());
1170            ps.setString(4, trigger.getTimeZone().getID());
1171
1172            return ps.executeUpdate();
1173        } finally {
1174            closeStatement(ps);
1175        }
1176    }
1177
1178    /**
1179     * <p>
1180     * Insert the blob trigger data.
1181     * </p>
1182     * 
1183     * @param conn
1184     *          the DB Connection
1185     * @param trigger
1186     *          the trigger to insert
1187     * @return the number of rows inserted
1188     */
1189    public int insertBlobTrigger(Connection   conn, Trigger trigger)
1190        throws SQLException  , IOException   {
1191        PreparedStatement   ps = null;
1192        ByteArrayOutputStream   os = null;
1193
1194        try {
1195            // update the blob
1196            os = new ByteArrayOutputStream  ();
1197            ObjectOutputStream   oos = new ObjectOutputStream  (os);
1198            oos.writeObject(trigger);
1199            oos.close();
1200
1201            byte[] buf = os.toByteArray();
1202            ByteArrayInputStream   is = new ByteArrayInputStream  (buf);
1203
1204            ps = conn.prepareStatement(rtp(INSERT_BLOB_TRIGGER));
1205            ps.setString(1, trigger.getName());
1206            ps.setString(2, trigger.getGroup());
1207            ps.setBinaryStream(3, is, buf.length);
1208
1209            return ps.executeUpdate();
1210        } finally {
1211            closeStatement(ps);
1212        }
1213    }
1214
1215    /**
1216     * <p>
1217     * Update the base trigger data.
1218     * </p>
1219     * 
1220     * @param conn
1221     *          the DB Connection
1222     * @param trigger
1223     *          the trigger to insert
1224     * @param state
1225     *          the state that the trigger should be stored in
1226     * @return the number of rows updated
1227     */
1228    public int updateTrigger(Connection   conn, Trigger trigger, String   state,
1229            JobDetail jobDetail) throws SQLException  , IOException   {
1230
1231        // save some clock cycles by unnecessarily writing job data blob ...
1232        boolean updateJobData = trigger.getJobDataMap().isDirty();
1233        ByteArrayOutputStream   baos = null;
1234        if(updateJobData && trigger.getJobDataMap().size() > 0) {
1235            baos = serializeJobData(trigger.getJobDataMap());
1236        }
1237                
1238        PreparedStatement   ps = null;
1239
1240        int insertResult = 0;
1241
1242
1243        try {
1244            if(updateJobData) {
1245                ps = conn.prepareStatement(rtp(UPDATE_TRIGGER));
1246            } else {
1247                ps = conn.prepareStatement(rtp(UPDATE_TRIGGER_SKIP_DATA));
1248            }
1249                
1250            ps.setString(1, trigger.getJobName());
1251            ps.setString(2, trigger.getJobGroup());
1252            setBoolean(ps, 3, trigger.isVolatile());
1253            ps.setString(4, trigger.getDescription());
1254            long nextFireTime = -1;
1255            if (trigger.getNextFireTime() != null) {
1256                nextFireTime = trigger.getNextFireTime().getTime();
1257            }
1258            ps.setBigDecimal(5, new BigDecimal  (String.valueOf(nextFireTime)));
1259            long prevFireTime = -1;
1260            if (trigger.getPreviousFireTime() != null) {
1261                prevFireTime = trigger.getPreviousFireTime().getTime();
1262            }
1263            ps.setBigDecimal(6, new BigDecimal  (String.valueOf(prevFireTime)));
1264            ps.setString(7, state);
1265            if (trigger.getClass() == SimpleTrigger.class) {
1266                //                updateSimpleTrigger(conn, (SimpleTrigger)trigger);
1267                ps.setString(8, TTYPE_SIMPLE);
1268            } else if (trigger.getClass() == CronTrigger.class) {
1269                //                updateCronTrigger(conn, (CronTrigger)trigger);
1270                ps.setString(8, TTYPE_CRON);
1271            } else {
1272                //                updateBlobTrigger(conn, trigger);
1273                ps.setString(8, TTYPE_BLOB);
1274            }
1275            ps.setBigDecimal(9, new BigDecimal  (String.valueOf(trigger
1276                    .getStartTime().getTime())));
1277            long endTime = 0;
1278            if (trigger.getEndTime() != null) {
1279                endTime = trigger.getEndTime().getTime();
1280            }
1281            ps.setBigDecimal(10, new BigDecimal  (String.valueOf(endTime)));
1282            ps.setString(11, trigger.getCalendarName());
1283            ps.setInt(12, trigger.getMisfireInstruction());
1284            ps.setInt(13, trigger.getPriority());
1285
1286            if(updateJobData) {
1287                setBytes(ps, 14, baos);
1288                ps.setString(15, trigger.getName());
1289                ps.setString(16, trigger.getGroup());
1290            } else {
1291                ps.setString(14, trigger.getName());
1292                ps.setString(15, trigger.getGroup());
1293            }
1294
1295            insertResult = ps.executeUpdate();
1296        } finally {
1297            closeStatement(ps);
1298        }
1299
1300        if (insertResult > 0) {
1301            deleteTriggerListeners(conn, trigger.getName(), trigger.getGroup());
1302
1303            String  [] trigListeners = trigger.getTriggerListenerNames();
1304            for (int i = 0; trigListeners != null && i < trigListeners.length; i++) {
1305                insertTriggerListener(conn, trigger, trigListeners[i]);
1306            }
1307        }
1308
1309        return insertResult;
1310    }
1311
1312    /**
1313     * <p>
1314     * Update the simple trigger data.
1315     * </p>
1316     * 
1317     * @param conn
1318     *          the DB Connection
1319     * @param trigger
1320     *          the trigger to insert
1321     * @return the number of rows updated
1322     */
1323    public int updateSimpleTrigger(Connection   conn, SimpleTrigger trigger)
1324        throws SQLException   {
1325        PreparedStatement   ps = null;
1326
1327        try {
1328            ps = conn.prepareStatement(rtp(UPDATE_SIMPLE_TRIGGER));
1329
1330            ps.setInt(1, trigger.getRepeatCount());
1331            ps.setBigDecimal(2, new BigDecimal  (String.valueOf(trigger
1332                    .getRepeatInterval())));
1333            ps.setInt(3, trigger.getTimesTriggered());
1334            ps.setString(4, trigger.getName());
1335            ps.setString(5, trigger.getGroup());
1336
1337            return ps.executeUpdate();
1338        } finally {
1339            closeStatement(ps);
1340        }
1341    }
1342
1343    /**
1344     * <p>
1345     * Update the cron trigger data.
1346     * </p>
1347     * 
1348     * @param conn
1349     *          the DB Connection
1350     * @param trigger
1351     *          the trigger to insert
1352     * @return the number of rows updated
1353     */
1354    public int updateCronTrigger(Connection   conn, CronTrigger trigger)
1355        throws SQLException   {
1356        PreparedStatement   ps = null;
1357
1358        try {
1359            ps = conn.prepareStatement(rtp(UPDATE_CRON_TRIGGER));
1360            ps.setString(1, trigger.getCronExpression());
1361            ps.setString(2, trigger.getName());
1362            ps.setString(3, trigger.getGroup());
1363
1364            return ps.executeUpdate();
1365        } finally {
1366            closeStatement(ps);
1367        }
1368    }
1369
1370    /**
1371     * <p>
1372     * Update the blob trigger data.
1373     * </p>
1374     * 
1375     * @param conn
1376     *          the DB Connection
1377     * @param trigger
1378     *          the trigger to insert
1379     * @return the number of rows updated
1380     */
1381    public int updateBlobTrigger(Connection   conn, Trigger trigger)
1382        throws SQLException  , IOException   {
1383        PreparedStatement   ps = null;
1384        ByteArrayOutputStream   os = null;
1385
1386        try {
1387            // update the blob
1388            os = new ByteArrayOutputStream  ();
1389            ObjectOutputStream   oos = new ObjectOutputStream  (os);
1390            oos.writeObject(trigger);
1391            oos.close();
1392
1393            byte[] buf = os.toByteArray();
1394            ByteArrayInputStream   is = new ByteArrayInputStream  (buf);
1395
1396            ps = conn.prepareStatement(rtp(UPDATE_BLOB_TRIGGER));
1397            ps.setBinaryStream(1, is, buf.length);
1398            ps.setString(2, trigger.getName());
1399            ps.setString(3, trigger.getGroup());
1400
1401            return ps.executeUpdate();
1402        } finally {
1403            closeStatement(ps);
1404            if (os != null) {
1405                os.close();
1406            }
1407        }
1408    }
1409
1410    /**
1411     * <p>
1412     * Check whether or not a trigger exists.
1413     * </p>
1414     * 
1415     * @param conn
1416     *          the DB Connection
1417     * @param triggerName
1418     *          the name of the trigger
1419     * @param groupName
1420     *          the group containing the trigger
1421     * @return true if the trigger exists, false otherwise
1422     */
1423    public boolean triggerExists(Connection   conn, String   triggerName,
1424            String   groupName) throws SQLException   {
1425        PreparedStatement   ps = null;
1426        ResultSet   rs = null;
1427
1428        try {
1429            ps = conn.prepareStatement(rtp(SELECT_TRIGGER_EXISTENCE));
1430            ps.setString(1, triggerName);
1431            ps.setString(2, groupName);
1432            rs = ps.executeQuery();
1433
1434            if (rs.next()) {
1435                return true;
1436            } else {
1437                return false;
1438            }
1439        } finally {
1440            closeResultSet(rs);
1441            closeStatement(ps);
1442        }
1443    }
1444
1445    /**
1446     * <p>
1447     * Update the state for a given trigger.
1448     * </p>
1449     * 
1450     * @param conn
1451     *          the DB Connection
1452     * @param triggerName
1453     *          the name of the trigger
1454     * @param groupName
1455     *          the group containing the trigger
1456     * @param state
1457     *          the new state for the trigger
1458     * @return the number of rows updated
1459     */
1460    public int updateTriggerState(Connection   conn, String   triggerName,
1461            String   groupName, String   state) throws SQLException   {
1462        PreparedStatement   ps = null;
1463
1464        try {
1465            ps = conn.prepareStatement(rtp(UPDATE_TRIGGER_STATE));
1466            ps.setString(1, state);
1467            ps.setString(2, triggerName);
1468            ps.setString(3, groupName);
1469            return ps.executeUpdate();
1470        } finally {
1471            closeStatement(ps);
1472        }
1473    }
1474
1475    /**
1476     * <p>
1477     * Update the given trigger to the given new state, if it is one of the
1478     * given old states.
1479     * </p>
1480     * 
1481     * @param conn
1482     *          the DB connection
1483     * @param triggerName
1484     *          the name of the trigger
1485     * @param groupName
1486     *          the group containing the trigger
1487     * @param newState
1488     *          the new state for the trigger
1489     * @param oldState1
1490     *          one of the old state the trigger must be in
1491     * @param oldState2
1492     *          one of the old state the trigger must be in
1493     * @param oldState3
1494     *          one of the old state the trigger must be in
1495     * @return int the number of rows updated
1496     * @throws SQLException
1497     */
1498    public int updateTriggerStateFromOtherStates(Connection   conn,
1499            String   triggerName, String   groupName, String   newState,
1500            String   oldState1, String   oldState2, String   oldState3)
1501        throws SQLException   {
1502        PreparedStatement   ps = null;
1503
1504        try {
1505            ps = conn.prepareStatement(rtp(UPDATE_TRIGGER_STATE_FROM_STATES));
1506            ps.setString(1, newState);
1507            ps.setString(2, triggerName);
1508            ps.setString(3, groupName);
1509            ps.setString(4, oldState1);
1510            ps.setString(5, oldState2);
1511            ps.setString(6, oldState3);
1512
1513            return ps.executeUpdate();
1514        } finally {
1515            closeStatement(ps);
1516        }
1517    }
1518
1519    public int updateTriggerStateFromOtherStatesBeforeTime(Connection   conn,
1520            String   newState, String   oldState1, String   oldState2, long time)
1521        throws SQLException   {
1522        PreparedStatement   ps = null;
1523
1524        try {
1525            ps = conn
1526                    .prepareStatement(rtp(UPDATE_TRIGGER_STATE_FROM_OTHER_STATES_BEFORE_TIME));
1527            ps.setString(1, newState);
1528            ps.setString(2, oldState1);
1529            ps.setString(3, oldState2);
1530            ps.setLong(4, time);
1531
1532            return ps.executeUpdate();
1533        } finally {
1534            closeStatement(ps);
1535        }
1536    }
1537
1538    /**
1539     * <p>
1540     * Update all triggers in the given group to the given new state, if they
1541     * are in one of the given old states.
1542     * </p>
1543     * 
1544     * @param conn
1545     *          the DB connection
1546     * @param groupName
1547     *          the group containing the trigger
1548     * @param newState
1549     *          the new state for the trigger
1550     * @param oldState1
1551     *          one of the old state the trigger must be in
1552     * @param oldState2
1553     *          one of the old state the trigger must be in
1554     * @param oldState3
1555     *          one of the old state the trigger must be in
1556     * @return int the number of rows updated
1557     * @throws SQLException
1558     */
1559    public int updateTriggerGroupStateFromOtherStates(Connection   conn,
1560            String   groupName, String   newState, String   oldState1,
1561            String   oldState2, String   oldState3) throws SQLException   {
1562        PreparedStatement   ps = null;
1563
1564        try {
1565            ps = conn
1566                    .prepareStatement(rtp(UPDATE_TRIGGER_GROUP_STATE_FROM_STATES));
1567            ps.setString(1, newState);
1568            ps.setString(2, groupName);
1569            ps.setString(3, oldState1);
1570            ps.setString(4, oldState2);
1571            ps.setString(5, oldState3);
1572
1573            return ps.executeUpdate();
1574        } finally {
1575            closeStatement(ps);
1576        }
1577    }
1578
1579    /**
1580     * <p>
1581     * Update the given trigger to the given new state, if it is in the given
1582     * old state.
1583     * </p>
1584     * 
1585     * @param conn
1586     *          the DB connection
1587     * @param triggerName
1588     *          the name of the trigger
1589     * @param groupName
1590     *          the group containing the trigger
1591     * @param newState
1592     *          the new state for the trigger
1593     * @param oldState
1594     *          the old state the trigger must be in
1595     * @return int the number of rows updated
1596     * @throws SQLException
1597     */
1598    public int updateTriggerStateFromOtherState(Connection   conn,
1599            String   triggerName, String   groupName, String   newState,
1600            String   oldState) throws SQLException   {
1601        PreparedStatement   ps = null;
1602
1603        try {
1604            ps = conn.prepareStatement(rtp(UPDATE_TRIGGER_STATE_FROM_STATE));
1605            ps.setString(1, newState);
1606            ps.setString(2, triggerName);
1607            ps.setString(3, groupName);
1608            ps.setString(4, oldState);
1609
1610            return ps.executeUpdate();
1611        } finally {
1612            closeStatement(ps);
1613        }
1614    }
1615
1616    /**
1617     * <p>
1618     * Update all of the triggers of the given group to the given new state, if
1619     * they are in the given old state.
1620     * </p>
1621     * 
1622     * @param conn
1623     *          the DB connection
1624     * @param groupName
1625     *          the group containing the triggers
1626     * @param newState
1627     *          the new state for the trigger group
1628     * @param oldState
1629     *          the old state the triggers must be in
1630     * @return int the number of rows updated
1631     * @throws SQLException
1632     */
1633    public int updateTriggerGroupStateFromOtherState(Connection   conn,
1634            String   groupName, String   newState, String   oldState)
1635        throws SQLException   {
1636        PreparedStatement   ps = null;
1637
1638        try {
1639            ps = conn
1640                    .prepareStatement(rtp(UPDATE_TRIGGER_GROUP_STATE_FROM_STATE));
1641            ps.setString(1, newState);
1642            ps.setString(2, groupName);
1643            ps.setString(3, oldState);
1644
1645            return ps.executeUpdate();
1646        } finally {
1647            closeStatement(ps);
1648        }
1649    }
1650
1651    /**
1652     * <p>
1653     * Update the states of all triggers associated with the given job.
1654     * </p>
1655     * 
1656     * @param conn
1657     *          the DB Connection
1658     * @param jobName
1659     *          the name of the job
1660     * @param groupName
1661     *          the group containing the job
1662     * @param state
1663     *          the new state for the triggers
1664     * @return the number of rows updated
1665     */
1666    public int updateTriggerStatesForJob(Connection   conn, String   jobName,
1667            String   groupName, String   state) throws SQLException   {
1668        PreparedStatement   ps = null;
1669
1670        try {
1671            ps = conn.prepareStatement(rtp(UPDATE_JOB_TRIGGER_STATES));
1672            ps.setString(1, state);
1673            ps.setString(2, jobName);
1674            ps.setString(3, groupName);
1675
1676            return ps.executeUpdate();
1677        } finally {
1678            closeStatement(ps);
1679        }
1680    }
1681
1682    public int updateTriggerStatesForJobFromOtherState(Connection   conn,
1683            String   jobName, String   groupName, String   state, String   oldState)
1684        throws SQLException   {
1685        PreparedStatement   ps = null;
1686
1687        try {
1688            ps = conn
1689                    .prepareStatement(rtp(UPDATE_JOB_TRIGGER_STATES_FROM_OTHER_STATE));
1690            ps.setString(1, state);
1691            ps.setString(2, jobName);
1692            ps.setString(3, groupName);
1693            ps.setString(4, oldState);
1694
1695            return ps.executeUpdate();
1696        } finally {
1697            closeStatement(ps);
1698        }
1699    }
1700
1701    /**
1702     * <p>
1703     * Delete all of the listeners associated with a given trigger.
1704     * </p>
1705     * 
1706     * @param conn
1707     *          the DB Connection
1708     * @param triggerName
1709     *          the name of the trigger whose listeners will be deleted
1710     * @param groupName
1711     *          the name of the group containing the trigger
1712     * @return the number of rows deleted
1713     */
1714    public int deleteTriggerListeners(Connection   conn, String   triggerName,
1715            String   groupName) throws SQLException   {
1716        PreparedStatement   ps = null;
1717
1718        try {
1719            ps = conn.prepareStatement(rtp(DELETE_TRIGGER_LISTENERS));
1720            ps.setString(1, triggerName);
1721            ps.setString(2, groupName);
1722            return ps.executeUpdate();
1723        } finally {
1724            closeStatement(ps);
1725        }
1726    }
1727
1728    /**
1729     * <p>
1730     * Associate a listener with the given trigger.
1731     * </p>
1732     * 
1733     * @param conn
1734     *          the DB Connection
1735     * @param trigger
1736     *          the trigger
1737     * @param listener
1738     *          the name of the listener to associate with the trigger
1739     * @return the number of rows inserted
1740     */
1741    public int insertTriggerListener(Connection   conn, Trigger trigger,
1742            String   listener) throws SQLException   {
1743        PreparedStatement   ps = null;
1744
1745        try {
1746            ps = conn.prepareStatement(rtp(INSERT_TRIGGER_LISTENER));
1747            ps.setString(1, trigger.getName());
1748            ps.setString(2, trigger.getGroup());
1749            ps.setString(3, listener);
1750
1751            return ps.executeUpdate();
1752        } finally {
1753            closeStatement(ps);
1754        }
1755    }
1756
1757    /**
1758     * <p>
1759     * Select the listeners associated with a given trigger.
1760     * </p>
1761     * 
1762     * @param conn
1763     *          the DB Connection
1764     * @param triggerName
1765     *          the name of the trigger
1766     * @param groupName
1767     *          the group containing the trigger
1768     * @return array of <code>String</code> trigger listener names
1769     */
1770    public String  [] selectTriggerListeners(Connection   conn, String   triggerName,
1771            String   groupName) throws SQLException   {
1772        PreparedStatement   ps = null;
1773        ResultSet   rs = null;
1774
1775        try {
1776            ps = conn.prepareStatement(rtp(SELECT_TRIGGER_LISTENERS));
1777            ps.setString(1, triggerName);
1778            ps.setString(2, groupName);
1779            rs = ps.executeQuery();
1780
1781            ArrayList   list = new ArrayList  ();
1782            while (rs.next()) {
1783                list.add(rs.getString(1));
1784            }
1785            Object  [] oArr = list.toArray();
1786            String  [] sArr = new String  [oArr.length];
1787            System.arraycopy(oArr, 0, sArr, 0, oArr.length);
1788            return sArr;
1789        } finally {
1790            closeResultSet(rs);
1791            closeStatement(ps);
1792        }
1793    }
1794
1795    /**
1796     * <p>
1797     * Delete the simple trigger data for a trigger.
1798     * </p>
1799     * 
1800     * @param conn
1801     *          the DB Connection
1802     * @param triggerName
1803     *          the name of the trigger
1804     * @param groupName
1805     *          the group containing the trigger
1806     * @return the number of rows deleted
1807     */
1808    public int deleteSimpleTrigger(Connection   conn, String   triggerName,
1809            String   groupName) throws SQLException   {
1810        PreparedStatement   ps = null;
1811
1812        try {
1813            ps = conn.prepareStatement(rtp(DELETE_SIMPLE_TRIGGER));
1814            ps.setString(1, triggerName);
1815            ps.setString(2, groupName);
1816
1817            return ps.executeUpdate();
1818        } finally {
1819            closeStatement(ps);
1820        }
1821    }
1822
1823    /**
1824     * <p>
1825     * Delete the cron trigger data for a trigger.
1826     * </p>
1827     * 
1828     * @param conn
1829     *          the DB Connection
1830     * @param triggerName
1831     *          the name of the trigger
1832     * @param groupName
1833     *          the group containing the trigger
1834     * @return the number of rows deleted
1835     */
1836    public int deleteCronTrigger(Connection   conn, String   triggerName,
1837            String   groupName) throws SQLException   {
1838        PreparedStatement   ps = null;
1839
1840        try {
1841            ps = conn.prepareStatement(rtp(DELETE_CRON_TRIGGER));
1842            ps.setString(1, triggerName);
1843            ps.setString(2, groupName);
1844
1845            return ps.executeUpdate();
1846        } finally {
1847            closeStatement(ps);
1848        }
1849    }
1850
1851    /**
1852     * <p>
1853     * Delete the cron trigger data for a trigger.
1854     * </p>
1855     * 
1856     * @param conn
1857     *          the DB Connection
1858     * @param triggerName
1859     *          the name of the trigger
1860     * @param groupName
1861     *          the group containing the trigger
1862     * @return the number of rows deleted
1863     */
1864    public int deleteBlobTrigger(Connection   conn, String   triggerName,
1865            String   groupName) throws SQLException   {
1866        PreparedStatement   ps = null;
1867
1868        try {
1869            ps = conn.prepareStatement(rtp(DELETE_BLOB_TRIGGER));
1870            ps.setString(1, triggerName);
1871            ps.setString(2, groupName);
1872
1873            return ps.executeUpdate();
1874        } finally {
1875            closeStatement(ps);
1876        }
1877    }
1878
1879    /**
1880     * <p>
1881     * Delete the base trigger data for a trigger.
1882     * </p>
1883     * 
1884     * @param conn
1885     *          the DB Connection
1886     * @param triggerName
1887     *          the name of the trigger
1888     * @param groupName
1889     *          the group containing the trigger
1890     * @return the number of rows deleted
1891     */
1892    public int deleteTrigger(Connection   conn, String   triggerName,
1893            String   groupName) throws SQLException   {
1894        PreparedStatement   ps = null;
1895
1896        try {
1897            ps = conn.prepareStatement(rtp(DELETE_TRIGGER));
1898            ps.setString(1, triggerName);
1899            ps.setString(2, groupName);
1900
1901            return ps.executeUpdate();
1902        } finally {
1903            closeStatement(ps);
1904        }
1905    }
1906
1907    /**
1908     * <p>
1909     * Select the number of triggers associated with a given job.
1910     * </p>
1911     * 
1912     * @param conn
1913     *          the DB Connection
1914     * @param jobName
1915     *          the name of the job
1916     * @param groupName
1917     *          the group containing the job
1918     * @return the number of triggers for the given job
1919     */
1920    public int selectNumTriggersForJob(Connection   conn, String   jobName,
1921            String   groupName) throws SQLException   {
1922        PreparedStatement   ps = null;
1923        ResultSet   rs = null;
1924
1925        try {
1926            ps = conn.prepareStatement(rtp(SELECT_NUM_TRIGGERS_FOR_JOB));
1927            ps.setString(1, jobName);
1928            ps.setString(2, groupName);
1929            rs = ps.executeQuery();
1930
1931            if (rs.next()) {
1932                return rs.getInt(1);
1933            } else {
1934                return 0;
1935            }
1936        } finally {
1937            closeResultSet(rs);
1938            closeStatement(ps);
1939        }
1940    }
1941
1942    /**
1943     * <p>
1944     * Select the job to which the trigger is associated.
1945     * </p>
1946     * 
1947     * @param conn
1948     *          the DB Connection
1949     * @param triggerName
1950     *          the name of the trigger
1951     * @param groupName
1952     *          the group containing the trigger
1953     * @return the <code>{@link org.quartz.JobDetail}</code> object
1954     *         associated with the given trigger
1955     * @throws SQLException
1956     * @throws ClassNotFoundException
1957     */
1958    public JobDetail selectJobForTrigger(Connection   conn, String   triggerName,
1959            String   groupName, ClassLoadHelper loadHelper) throws ClassNotFoundException  , SQLException   {
1960        PreparedStatement   ps = null;
1961        ResultSet   rs = null;
1962
1963        try {
1964            ps = conn.prepareStatement(rtp(SELECT_JOB_FOR_TRIGGER));
1965            ps.setString(1, triggerName);
1966            ps.setString(2, groupName);
1967            rs = ps.executeQuery();
1968
1969            if (rs.next()) {
1970                JobDetail job = new JobDetail();
1971                job.setName(rs.getString(1));
1972                job.setGroup(rs.getString(2));
1973                job.setDurability(getBoolean(rs, 3));
1974                job.setJobClass(loadHelper.loadClass(rs
1975                        .getString(4)));
1976                job.setRequestsRecovery(getBoolean(rs, 5));
1977                
1978                return job;
1979            } else {
1980                if (logger.isDebugEnabled()) {
1981                    logger.debug("No job for trigger '" + groupName + "."
1982                            + triggerName + "'.");
1983                }
1984                return null;
1985            }
1986        } finally {
1987            closeResultSet(rs);
1988            closeStatement(ps);
1989        }
1990    }
1991
1992    /**
1993     * <p>
1994     * Select the triggers for a job
1995     * </p>
1996     * 
1997     * @param conn
1998     *          the DB Connection
1999     * @param jobName
2000     *          the name of the trigger
2001     * @param groupName
2002     *          the group containing the trigger
2003     * @return an array of <code>(@link org.quartz.Trigger)</code> objects
2004     *         associated with a given job.
2005     * @throws SQLException
2006     */
2007    public Trigger[] selectTriggersForJob(Connection   conn, String   jobName,
2008            String   groupName) throws SQLException  , ClassNotFoundException  ,
2009            IOException   {
2010
2011        ArrayList   trigList = new ArrayList  ();
2012        PreparedStatement   ps = null;
2013        ResultSet   rs = null;
2014
2015        try {
2016            ps = conn.prepareStatement(rtp(SELECT_TRIGGERS_FOR_JOB));
2017            ps.setString(1, jobName);
2018            ps.setString(2, groupName);
2019            rs = ps.executeQuery();
2020
2021            while (rs.next()) {
2022                Trigger t = selectTrigger(conn,
2023                        rs.getString(COL_TRIGGER_NAME), 
2024                        rs.getString(COL_TRIGGER_GROUP));
2025                if(t != null) {
2026                    trigList.add(t);
2027                }
2028            }
2029        } finally {
2030            closeResultSet(rs);
2031            closeStatement(ps);
2032        }
2033
2034        return (Trigger[]) trigList.toArray(new Trigger[trigList.size()]);
2035    }
2036
2037    public Trigger[] selectTriggersForCalendar(Connection   conn, String   calName)
2038        throws SQLException  , ClassNotFoundException  , IOException   {
2039
2040        ArrayList   trigList = new ArrayList  ();
2041        PreparedStatement   ps = null;
2042        ResultSet   rs = null;
2043
2044        try {
2045            ps = conn.prepareStatement(rtp(SELECT_TRIGGERS_FOR_CALENDAR));
2046            ps.setString(1, calName);
2047            rs = ps.executeQuery();
2048
2049            while (rs.next()) {
2050                trigList.add(selectTrigger(conn,
2051                        rs.getString(COL_TRIGGER_NAME), rs
2052                                .getString(COL_TRIGGER_GROUP)));
2053            }
2054        } finally {
2055            closeResultSet(rs);
2056            closeStatement(ps);
2057        }
2058
2059        return (Trigger[]) trigList.toArray(new Trigger[trigList.size()]);
2060    }
2061    
2062    public List   selectStatefulJobsOfTriggerGroup(Connection   conn,
2063            String   groupName) throws SQLException   {
2064        ArrayList   jobList = new ArrayList  ();
2065        PreparedStatement   ps = null;
2066        ResultSet   rs = null;
2067
2068        try {
2069            ps = conn
2070                    .prepareStatement(rtp(SELECT_STATEFUL_JOBS_OF_TRIGGER_GROUP));
2071            ps.setString(1, groupName);
2072            setBoolean(ps, 2, true);
2073            rs = ps.executeQuery();
2074
2075            while (rs.next()) {
2076                jobList.add(new Key(rs.getString(COL_JOB_NAME), rs
2077                        .getString(COL_JOB_GROUP)));
2078            }
2079        } finally {
2080            closeResultSet(rs);
2081            closeStatement(ps);
2082        }
2083
2084        return jobList;
2085    }
2086
2087    /**
2088     * <p>
2089     * Select a trigger.
2090     * </p>
2091     * 
2092     * @param conn
2093     *          the DB Connection
2094     * @param triggerName
2095     *          the name of the trigger
2096     * @param groupName
2097     *          the group containing the trigger
2098     * @return the <code>{@link org.quartz.Trigger}</code> object
2099     */
2100    public Trigger selectTrigger(Connection   conn, String   triggerName,
2101            String   groupName) throws SQLException  , ClassNotFoundException  ,
2102            IOException   {
2103        PreparedStatement   ps = null;
2104        ResultSet   rs = null;
2105
2106        try {
2107            Trigger trigger = null;
2108
2109            ps = conn.prepareStatement(rtp(SELECT_TRIGGER));
2110            ps.setString(1, triggerName);
2111            ps.setString(2, groupName);
2112            rs = ps.executeQuery();
2113
2114            if (rs.next()) {
2115                String   jobName = rs.getString(COL_JOB_NAME);
2116                String   jobGroup = rs.getString(COL_JOB_GROUP);
2117                boolean volatility = getBoolean(rs, COL_IS_VOLATILE);
2118                String   description = rs.getString(COL_DESCRIPTION);
2119                long nextFireTime = rs.getLong(COL_NEXT_FIRE_TIME);
2120                long prevFireTime = rs.getLong(COL_PREV_FIRE_TIME);
2121                String   triggerType = rs.getString(COL_TRIGGER_TYPE);
2122                long startTime = rs.getLong(COL_START_TIME);
2123                long endTime = rs.getLong(COL_END_TIME);
2124                String   calendarName = rs.getString(COL_CALENDAR_NAME);
2125                int misFireInstr = rs.getInt(COL_MISFIRE_INSTRUCTION);
2126                int priority = rs.getInt(COL_PRIORITY);
2127
2128                Map   map = null;
2129                if (canUseProperties()) {
2130                    map = getMapFromProperties(rs);
2131                } else {
2132                    map = (Map  ) getObjectFromBlob(rs, COL_JOB_DATAMAP);
2133                }
2134                
2135                Date   nft = null;
2136                if (nextFireTime > 0) {
2137                    nft = new Date  (nextFireTime);
2138                }
2139
2140                Date   pft = null;
2141                if (prevFireTime > 0) {
2142                    pft = new Date  (prevFireTime);
2143                }
2144                Date   startTimeD = new Date  (startTime);
2145                Date   endTimeD = null;
2146                if (endTime > 0) {
2147                    endTimeD = new Date  (endTime);
2148                }
2149
2150                rs.close();
2151                ps.close();
2152
2153                if (triggerType.equals(TTYPE_SIMPLE)) {
2154                    ps = conn.prepareStatement(rtp(SELECT_SIMPLE_TRIGGER));
2155                    ps.setString(1, triggerName);
2156                    ps.setString(2, groupName);
2157                    rs = ps.executeQuery();
2158
2159                    if (rs.next()) {
2160                        int repeatCount = rs.getInt(COL_REPEAT_COUNT);
2161                        long repeatInterval = rs.getLong(COL_REPEAT_INTERVAL);
2162                        int timesTriggered = rs.getInt(COL_TIMES_TRIGGERED);
2163
2164                        SimpleTrigger st = new SimpleTrigger(triggerName,
2165                                groupName, jobName, jobGroup, startTimeD,
2166                                endTimeD, repeatCount, repeatInterval);
2167                        st.setCalendarName(calendarName);
2168                        st.setMisfireInstruction(misFireInstr);
2169                        st.setTimesTriggered(timesTriggered);
2170                        st.setVolatility(volatility);
2171                        st.setNextFireTime(nft);
2172                        st.setPreviousFireTime(pft);
2173                        st.setDescription(description);
2174                        st.setPriority(priority);
2175                        if (null != map) {
2176                            st.setJobDataMap(new JobDataMap(map));
2177                        }
2178                        trigger = st;
2179                    }
2180                } else if (triggerType.equals(TTYPE_CRON)) {
2181                    ps = conn.prepareStatement(rtp(SELECT_CRON_TRIGGER));
2182                    ps.setString(1, triggerName);
2183                    ps.setString(2, groupName);
2184                    rs = ps.executeQuery();
2185
2186                    if (rs.next()) {
2187                        String   cronExpr = rs.getString(COL_CRON_EXPRESSION);
2188                        String   timeZoneId = rs.getString(COL_TIME_ZONE_ID);
2189
2190                        CronTrigger ct = null;
2191                        try {
2192                            TimeZone   timeZone = null;
2193                            if (timeZoneId != null) {
2194                                timeZone = TimeZone.getTimeZone(timeZoneId);
2195                            }
2196                            ct = new CronTrigger(triggerName, groupName,
2197                                    jobName, jobGroup, startTimeD, endTimeD,
2198                                    cronExpr, timeZone);
2199                        } catch (Exception   neverHappens) {
2200                            // expr must be valid, or it never would have
2201                            // gotten to the store...
2202                        }
2203                        if (null != ct) {
2204                            ct.setCalendarName(calendarName);
2205                            ct.setMisfireInstruction(misFireInstr);
2206                            ct.setVolatility(volatility);
2207                            ct.setNextFireTime(nft);
2208                            ct.setPreviousFireTime(pft);
2209                            ct.setDescription(description);
2210                            ct.setPriority(priority);
2211                            if (null != map) {
2212                                ct.setJobDataMap(new JobDataMap(map));
2213                            }
2214                            trigger = ct;
2215                        }
2216                    }
2217                } else if (triggerType.equals(TTYPE_BLOB)) {
2218                    ps = conn.prepareStatement(rtp(SELECT_BLOB_TRIGGER));
2219                    ps.setString(1, triggerName);
2220                    ps.setString(2, groupName);
2221                    rs = ps.executeQuery();
2222
2223                    if (rs.next()) {
2224                        trigger = (Trigger) getObjectFromBlob(rs, COL_BLOB);
2225                    }
2226                } else {
2227                    throw new ClassNotFoundException  ("class for trigger type '"
2228                            + triggerType + "' not found.");
2229                }
2230            }
2231
2232            return trigger;
2233        } finally {
2234            closeResultSet(rs);
2235            closeStatement(ps);
2236        }
2237    }
2238
2239    /**
2240     * <p>
2241     * Select a trigger's JobDataMap.
2242     * </p>
2243     * 
2244     * @param conn
2245     *          the DB Connection
2246     * @param triggerName
2247     *          the name of the trigger
2248     * @param groupName
2249     *          the group containing the trigger
2250     * @return the <code>{@link org.quartz.JobDataMap}</code> of the Trigger,
2251     * never null, but possibly empty.
2252     */
2253    public JobDataMap selectTriggerJobDataMap(Connection   conn, String   triggerName,
2254            String   groupName) throws SQLException  , ClassNotFoundException  ,
2255            IOException   {
2256        
2257        PreparedStatement   ps = null;
2258        ResultSet   rs = null;
2259
2260        try {
2261            Trigger trigger = null;
2262
2263            ps = conn.prepareStatement(rtp(SELECT_TRIGGER_DATA));
2264            ps.setString(1, triggerName);
2265            ps.setString(2, groupName);
2266            rs = ps.executeQuery();
2267
2268            if (rs.next()) {
2269
2270                Map   map = null;
2271                if (canUseProperties()) { 
2272                    map = getMapFromProperties(rs);
2273                } else {
2274                    map = (Map  ) getObjectFromBlob(rs, COL_JOB_DATAMAP);
2275                }
2276                
2277                rs.close();
2278                ps.close();
2279
2280                if (null != map) {
2281                    return new JobDataMap(map);
2282                }
2283            }
2284        } finally {
2285            closeResultSet(rs);
2286            closeStatement(ps);
2287        }
2288        
2289        return new JobDataMap();
2290    }
2291            
2292
2293    /**
2294     * <p>
2295     * Select a trigger' state value.
2296     * </p>
2297     * 
2298     * @param conn
2299     *          the DB Connection
2300     * @param triggerName
2301     *          the name of the trigger
2302     * @param groupName
2303     *          the group containing the trigger
2304     * @return the <code>{@link org.quartz.Trigger}</code> object
2305     */
2306    public String   selectTriggerState(Connection   conn, String   triggerName,
2307            String   groupName) throws SQLException   {
2308        PreparedStatement   ps = null;
2309        ResultSet   rs = null;
2310
2311        try {
2312            String   state = null;
2313
2314            ps = conn.prepareStatement(rtp(SELECT_TRIGGER_STATE));
2315            ps.setString(1, triggerName);
2316            ps.setString(2, groupName);
2317            rs = ps.executeQuery();
2318
2319            if (rs.next()) {
2320                state = rs.getString(COL_TRIGGER_STATE);
2321            } else {
2322                state = STATE_DELETED;
2323            }
2324
2325            return state.intern();
2326        } finally {
2327            closeResultSet(rs);
2328            closeStatement(ps);
2329        }
2330
2331    }
2332
2333    /**
2334     * <p>
2335     * Select a trigger' status (state & next fire time).
2336     * </p>
2337     * 
2338     * @param conn
2339     *          the DB Connection
2340     * @param triggerName
2341     *          the name of the trigger
2342     * @param groupName
2343     *          the group containing the trigger
2344     * @return a <code>TriggerStatus</code> object, or null
2345     */
2346    public TriggerStatus selectTriggerStatus(Connection   conn,
2347            String   triggerName, String   groupName) throws SQLException   {
2348        PreparedStatement   ps = null;
2349        ResultSet   rs = null;
2350
2351        try {
2352            TriggerStatus status = null;
2353
2354            ps = conn.prepareStatement(rtp(SELECT_TRIGGER_STATUS));
2355            ps.setString(1, triggerName);
2356            ps.setString(2, groupName);
2357            rs = ps.executeQuery();
2358
2359            if (rs.next()) {
2360                String   state = rs.getString(COL_TRIGGER_STATE);
2361                long nextFireTime = rs.getLong(COL_NEXT_FIRE_TIME);
2362                String   jobName = rs.getString(COL_JOB_NAME);
2363                String   jobGroup = rs.getString(COL_JOB_GROUP);
2364
2365                Date   nft = null;
2366                if (nextFireTime > 0) {
2367                    nft = new Date  (nextFireTime);
2368                }
2369
2370                status = new TriggerStatus(state, nft);
2371                status.setKey(new Key(triggerName, groupName));
2372                status.setJobKey(new Key(jobName, jobGroup));
2373            }
2374
2375            return status;
2376        } finally {
2377            closeResultSet(rs);
2378            closeStatement(ps);
2379        }
2380
2381    }
2382
2383    /**
2384     * <p>
2385     * Select the total number of triggers stored.
2386     * </p>
2387     * 
2388     * @param conn
2389     *          the DB Connection
2390     * @return the total number of triggers stored
2391     */
2392    public int selectNumTriggers(Connection   conn) throws SQLException   {
2393        PreparedStatement   ps = null;
2394        ResultSet   rs = null;
2395
2396        try {
2397            int count = 0;
2398            ps = conn.prepareStatement(rtp(SELECT_NUM_TRIGGERS));
2399            rs = ps.executeQuery();
2400
2401            if (rs.next()) {
2402                count = rs.getInt(1);
2403            }
2404
2405            return count;
2406        } finally {
2407            closeResultSet(rs);
2408            closeStatement(ps);
2409        }
2410    }
2411
2412    /**
2413     * <p>
2414     * Select all of the trigger group names that are stored.
2415     * </p>
2416     * 
2417     * @param conn
2418     *          the DB Connection
2419     * @return an array of <code>String</code> group names
2420     */
2421    public String  [] selectTriggerGroups(Connection   conn) throws SQLException   {
2422        PreparedStatement   ps = null;
2423        ResultSet   rs = null;
2424
2425        try {
2426            ps = conn.prepareStatement(rtp(SELECT_TRIGGER_GROUPS));
2427            rs = ps.executeQuery();
2428
2429            ArrayList   list = new ArrayList  ();
2430            while (rs.next()) {
2431                list.add(rs.getString(1));
2432            }
2433
2434            Object  [] oArr = list.toArray();
2435            String  [] sArr = new String  [oArr.length];
2436            System.arraycopy(oArr, 0, sArr, 0, oArr.length);
2437            return sArr;
2438        } finally {
2439            closeResultSet(rs);
2440            closeStatement(ps);
2441        }
2442    }
2443
2444    /**
2445     * <p>
2446     * Select all of the triggers contained in a given group.
2447     * </p>
2448     * 
2449     * @param conn
2450     *          the DB Connection
2451     * @param groupName
2452     *          the group containing the triggers
2453     * @return an array of <code>String</code> trigger names
2454     */
2455    public String  [] selectTriggersInGroup(Connection   conn, String   groupName)
2456        throws SQLException   {
2457        PreparedStatement   ps = null;
2458        ResultSet   rs = null;
2459
2460        try {
2461            ps = conn.prepareStatement(rtp(SELECT_TRIGGERS_IN_GROUP));
2462            ps.setString(1, groupName);
2463            rs = ps.executeQuery();
2464
2465            ArrayList   list = new ArrayList  ();
2466            while (rs.next()) {
2467                list.add(rs.getString(1));
2468            }
2469
2470            Object  [] oArr = list.toArray();
2471            String  [] sArr = new String  [oArr.length];
2472            System.arraycopy(oArr, 0, sArr, 0, oArr.length);
2473            return sArr;
2474        } finally {
2475            closeResultSet(rs);
2476            closeStatement(ps);
2477        }
2478    }
2479
2480    public int insertPausedTriggerGroup(Connection   conn, String   groupName)
2481        throws SQLException   {
2482        PreparedStatement   ps = null;
2483
2484        try {
2485            ps = conn.prepareStatement(rtp(INSERT_PAUSED_TRIGGER_GROUP));
2486            ps.setString(1, groupName);
2487            int rows = ps.executeUpdate();
2488
2489            return rows;
2490        } finally {
2491            closeStatement(ps);
2492        }
2493    }
2494
2495    public int deletePausedTriggerGroup(Connection   conn, String   groupName)
2496        throws SQLException   {
2497        PreparedStatement   ps = null;
2498
2499        try {
2500            ps = conn.prepareStatement(rtp(DELETE_PAUSED_TRIGGER_GROUP));
2501            ps.setString(1, groupName);
2502            int rows = ps.executeUpdate();
2503
2504            return rows;
2505        } finally {
2506            closeStatement(ps);
2507        }
2508    }
2509
2510    public int deleteAllPausedTriggerGroups(Connection   conn)
2511        throws SQLException   {
2512        PreparedStatement   ps = null;
2513
2514        try {
2515            ps = conn.prepareStatement(rtp(DELETE_PAUSED_TRIGGER_GROUPS));
2516            int rows = ps.executeUpdate();
2517
2518            return rows;
2519        } finally {
2520            closeStatement(ps);
2521        }
2522    }
2523
2524    public boolean isTriggerGroupPaused(Connection   conn, String   groupName)
2525        throws SQLException   {
2526        PreparedStatement   ps = null;
2527        ResultSet   rs = null;
2528
2529        try {
2530            ps = conn.prepareStatement(rtp(SELECT_PAUSED_TRIGGER_GROUP));
2531            ps.setString(1, groupName);
2532            rs = ps.executeQuery();
2533
2534            return rs.next();
2535        } finally {
2536            closeResultSet(rs);
2537            closeStatement(ps);
2538        }
2539    }
2540
2541    public boolean isExistingTriggerGroup(Connection   conn, String   groupName)
2542        throws SQLException   {
2543        PreparedStatement   ps = null;
2544        ResultSet   rs = null;
2545
2546        try {
2547            ps = conn.prepareStatement(rtp(SELECT_NUM_TRIGGERS_IN_GROUP));
2548            ps.setString(1, groupName);
2549            rs = ps.executeQuery();
2550
2551            if (!rs.next()) {
2552                return false;
2553            }
2554
2555            return (rs.getInt(1) > 0);
2556        } finally {
2557            closeResultSet(rs);
2558            closeStatement(ps);
2559        }
2560    }
2561
2562    //---------------------------------------------------------------------------
2563    // calendars
2564    //---------------------------------------------------------------------------
2565
2566    /**
2567     * <p>
2568     * Insert a new calendar.
2569     * </p>
2570     * 
2571     * @param conn
2572     *          the DB Connection
2573     * @param calendarName
2574     *          the name for the new calendar
2575     * @param calendar
2576     *          the calendar
2577     * @return the number of rows inserted
2578     * @throws IOException
2579     *           if there were problems serializing the calendar
2580     */
2581    public int insertCalendar(Connection   conn, String   calendarName,
2582            Calendar calendar) throws IOException  , SQLException   {
2583        ByteArrayOutputStream   baos = serializeObject(calendar);
2584
2585        PreparedStatement   ps = null;
2586
2587        try {
2588            ps = conn.prepareStatement(rtp(INSERT_CALENDAR));
2589            ps.setString(1, calendarName);
2590            setBytes(ps, 2, baos);
2591
2592            return ps.executeUpdate();
2593        } finally {
2594            closeStatement(ps);
2595        }
2596    }
2597
2598    /**
2599     * <p>
2600     * Update a calendar.
2601     * </p>
2602     * 
2603     * @param conn
2604     *          the DB Connection
2605     * @param calendarName
2606     *          the name for the new calendar
2607     * @param calendar
2608     *          the calendar
2609     * @return the number of rows updated
2610     * @throws IOException
2611     *           if there were problems serializing the calendar
2612     */
2613    public int updateCalendar(Connection   conn, String   calendarName,
2614            Calendar calendar) throws IOException  , SQLException   {
2615        ByteArrayOutputStream   baos = serializeObject(calendar);
2616
2617        PreparedStatement   ps = null;
2618
2619        try {
2620            ps = conn.prepareStatement(rtp(UPDATE_CALENDAR));
2621            setBytes(ps, 1, baos);
2622            ps.setString(2, calendarName);
2623
2624            return ps.executeUpdate();
2625        } finally {
2626            closeStatement(ps);
2627        }
2628    }
2629
2630    /**
2631     * <p>
2632     * Check whether or not a calendar exists.
2633     * </p>
2634     * 
2635     * @param conn
2636     *          the DB Connection
2637     * @param calendarName
2638     *          the name of the calendar
2639     * @return true if the trigger exists, false otherwise
2640     */
2641    public boolean calendarExists(Connection   conn, String   calendarName)
2642        throws SQLException   {
2643        PreparedStatement   ps = null;
2644        ResultSet   rs = null;
2645
2646        try {
2647            ps = conn.prepareStatement(rtp(SELECT_CALENDAR_EXISTENCE));
2648            ps.setString(1, calendarName);
2649            rs = ps.executeQuery();
2650
2651            if (rs.next()) {
2652                return true;
2653            } else {
2654                return false;
2655            }
2656        } finally {
2657            closeResultSet(rs);
2658            closeStatement(ps);
2659        }
2660    }
2661
2662    /**
2663     * <p>
2664     * Select a calendar.
2665     * </p>
2666     * 
2667     * @param conn
2668     *          the DB Connection
2669     * @param calendarName
2670     *          the name of the calendar
2671     * @return the Calendar
2672     * @throws ClassNotFoundException
2673     *           if a class found during deserialization cannot be found be
2674     *           found
2675     * @throws IOException
2676     *           if there were problems deserializing the calendar
2677     */
2678    public Calendar selectCalendar(Connection   conn, String   calendarName)
2679        throws ClassNotFoundException  , IOException  , SQLException   {
2680        PreparedStatement   ps = null;
2681        ResultSet   rs = null;
2682        try {
2683            String   selCal = rtp(SELECT_CALENDAR);
2684            ps = conn.prepareStatement(selCal);
2685            ps.setString(1, calendarName);
2686            rs = ps.executeQuery();
2687
2688            Calendar cal = null;
2689            if (rs.next()) {
2690                cal = (Calendar) getObjectFromBlob(rs, COL_CALENDAR);
2691            }
2692            if (null == cal) {
2693                logger.warn("Couldn't find calendar with name '" + calendarName
2694                        + "'.");
2695            }
2696            return cal;
2697        } finally {
2698            closeResultSet(rs);
2699            closeStatement(ps);
2700        }
2701    }
2702
2703    /**
2704     * <p>
2705     * Check whether or not a calendar is referenced by any triggers.
2706     * </p>
2707     * 
2708     * @param conn
2709     *          the DB Connection
2710     * @param calendarName
2711     *          the name of the calendar
2712     * @return true if any triggers reference the calendar, false otherwise
2713     */
2714    public boolean calendarIsReferenced(Connection   conn, String   calendarName)
2715        throws SQLException   {
2716        PreparedStatement   ps = null;
2717        ResultSet   rs = null;
2718        try {
2719            ps = conn.prepareStatement(rtp(SELECT_REFERENCED_CALENDAR));
2720            ps.setString(1, calendarName);
2721            rs = ps.executeQuery();
2722
2723            if (rs.next()) {
2724                return true;
2725            } else {
2726                return false;
2727            }
2728        } finally {
2729            closeResultSet(rs);
2730            closeStatement(ps);
2731        }
2732    }
2733
2734    /**
2735     * <p>
2736     * Delete a calendar.
2737     * </p>
2738     * 
2739     * @param conn
2740     *          the DB Connection
2741     * @param calendarName
2742     *          the name of the trigger
2743     * @return the number of rows deleted
2744     */
2745    public int deleteCalendar(Connection   conn, String   calendarName)
2746        throws SQLException   {
2747        PreparedStatement   ps = null;
2748
2749        try {
2750            ps = conn.prepareStatement(rtp(DELETE_CALENDAR));
2751            ps.setString(1, calendarName);
2752
2753            return ps.executeUpdate();
2754        } finally {
2755            closeStatement(ps);
2756        }
2757    }
2758
2759    /**
2760     * <p>
2761     * Select the total number of calendars stored.
2762     * </p>
2763     * 
2764     * @param conn
2765     *          the DB Connection
2766     * @return the total number of calendars stored
2767     */
2768    public int selectNumCalendars(Connection   conn) throws SQLException   {
2769        PreparedStatement   ps = null;
2770        ResultSet   rs = null;
2771
2772        try {
2773            int count = 0;
2774            ps = conn.prepareStatement(rtp(SELECT_NUM_CALENDARS));
2775
2776            rs = ps.executeQuery();
2777
2778            if (rs.next()) {
2779                count = rs.getInt(1);
2780            }
2781
2782            return count;
2783        } finally {
2784            closeResultSet(rs);
2785            closeStatement(ps);
2786        }
2787    }
2788
2789    /**
2790     * <p>
2791     * Select all of the stored calendars.
2792     * </p>
2793     * 
2794     * @param conn
2795     *          the DB Connection
2796     * @return an array of <code>String</code> calendar names
2797     */
2798    public String  [] selectCalendars(Connection   conn) throws SQLException   {
2799        PreparedStatement   ps = null;
2800        ResultSet   rs = null;
2801
2802        try {
2803            ps = conn.prepareStatement(rtp(SELECT_CALENDARS));
2804            rs = ps.executeQuery();
2805
2806            ArrayList   list = new ArrayList  ();
2807            while (rs.next()) {
2808                list.add(rs.getString(1));
2809            }
2810
2811            Object  [] oArr = list.toArray();
2812            String  [] sArr = new String  [oArr.length];
2813            System.arraycopy(oArr, 0, sArr, 0, oArr.length);
2814            return sArr;
2815        } finally {
2816            closeResultSet(rs);
2817            closeStatement(ps);
2818        }
2819    }
2820
2821    //---------------------------------------------------------------------------
2822    // trigger firing
2823    //---------------------------------------------------------------------------
2824
2825    /**
2826     * <p>
2827     * Select the next time that a trigger will be fired.
2828     * </p>
2829     * 
2830     * @param conn
2831     *          the DB Connection
2832     * @return the next fire time, or 0 if no trigger will be fired
2833     * 
2834     * @deprecated Does not account for misfires.
2835     */
2836    public long selectNextFireTime(Connection   conn) throws SQLException   {
2837        PreparedStatement   ps = null;
2838        ResultSet   rs = null;
2839        try {
2840            ps = conn.prepareStatement(rtp(SELECT_NEXT_FIRE_TIME));
2841            ps.setString(1, STATE_WAITING);
2842            rs = ps.executeQuery();
2843
2844            if (rs.next()) {
2845                return rs.getLong(ALIAS_COL_NEXT_FIRE_TIME);
2846            } else {
2847                return 0l;
2848            }
2849        } finally {
2850            closeResultSet(rs);
2851            closeStatement(ps);
2852        }
2853    }
2854
2855    /**
2856     * <p>
2857     * Select the trigger that will be fired at the given fire time.
2858     * </p>
2859     * 
2860     * @param conn
2861     *          the DB Connection
2862     * @param fireTime
2863     *          the time that the trigger will be fired
2864     * @return a <code>{@link org.quartz.utils.Key}</code> representing the
2865     *         trigger that will be fired at the given fire time, or null if no
2866     *         trigger will be fired at that time
2867     */
2868    public Key selectTriggerForFireTime(Connection   conn, long fireTime)
2869        throws SQLException   {
2870        PreparedStatement   ps = null;
2871        ResultSet   rs = null;
2872        try {
2873            ps = conn.prepareStatement(rtp(SELECT_TRIGGER_FOR_FIRE_TIME));
2874            ps.setString(1, STATE_WAITING);
2875            ps.setBigDecimal(2, new BigDecimal  (String.valueOf(fireTime)));
2876            rs = ps.executeQuery();
2877
2878            if (rs.next()) {
2879                return new Key(rs.getString(COL_TRIGGER_NAME), rs
2880                        .getString(COL_TRIGGER_GROUP));
2881            } else {
2882                return null;
2883            }
2884        } finally {
2885            closeResultSet(rs);
2886            closeStatement(ps);
2887        }
2888    }
2889
2890    /**
2891     * <p>
2892     * Select the next trigger which will fire to fire between the two given timestamps 
2893     * in ascending order of fire time, and then descending by priority.
2894     * </p>
2895     * 
2896     * @param conn
2897     *          the DB Connection
2898     * @param noLaterThan
2899     *          highest value of <code>getNextFireTime()</code> of the triggers (exclusive)
2900     * @param noEarlierThan 
2901     *          highest value of <code>getNextFireTime()</code> of the triggers (inclusive)
2902     *          
2903     * @return The next identifier of the next trigger to be fired.
2904     */
2905    public Key selectTriggerToAcquire(Connection   conn, long noLaterThan, long noEarlierThan)
2906        throws SQLException   {
2907        PreparedStatement   ps = null;
2908        ResultSet   rs = null;
2909        try {
2910            ps = conn.prepareStatement(rtp(SELECT_NEXT_TRIGGER_TO_ACQUIRE));
2911            
2912            // Try to give jdbc driver a hint to hopefully not pull over 
2913            // more than the one row we actually need.
2914            ps.setFetchSize(1);
2915            ps.setMaxRows(1);
2916            
2917            ps.setString(1, STATE_WAITING);
2918            ps.setBigDecimal(2, new BigDecimal  (String.valueOf(noLaterThan)));
2919            ps.setBigDecimal(3, new BigDecimal  (String.valueOf(noEarlierThan)));
2920            rs = ps.executeQuery();
2921            
2922            if (rs.next()) {
2923                return new Key(
2924                        rs.getString(COL_TRIGGER_NAME),
2925                        rs.getString(COL_TRIGGER_GROUP));
2926            }
2927            
2928            return null;
2929        } finally {
2930            closeResultSet(rs);
2931            closeStatement(ps);
2932        }      
2933    }
2934
2935    /**
2936     * <p>
2937     * Insert a fired trigger.
2938     * </p>
2939     * 
2940     * @param conn
2941     *          the DB Connection
2942     * @param trigger
2943     *          the trigger
2944     * @param state
2945     *          the state that the trigger should be stored in
2946     * @return the number of rows inserted
2947     */
2948    public int insertFiredTrigger(Connection   conn, Trigger trigger,
2949            String   state, JobDetail job) throws SQLException   {
2950        PreparedStatement   ps = null;
2951        try {
2952            ps = conn.prepareStatement(rtp(INSERT_FIRED_TRIGGER));
2953            ps.setString(1, trigger.getFireInstanceId());
2954            ps.setString(2, trigger.getName());
2955            ps.setString(3, trigger.getGroup());
2956            setBoolean(ps, 4, trigger.isVolatile());
2957            ps.setString(5, instanceId);
2958            ps.setBigDecimal(6, new BigDecimal  (String.valueOf(trigger
2959                    .getNextFireTime().getTime())));
2960            ps.setString(7, state);
2961            if (job != null) {
2962                ps.setString(8, trigger.getJobName());
2963                ps.setString(9, trigger.getJobGroup());
2964                setBoolean(ps, 10, job.isStateful());
2965                setBoolean(ps, 11, job.requestsRecovery());
2966            } else {
2967                ps.setString(8, null);
2968                ps.setString(9, null);
2969                setBoolean(ps, 10, false);
2970                setBoolean(ps, 11, false);
2971            }
2972            ps.setInt(12, trigger.getPriority());
2973            
2974
2975            return ps.executeUpdate();
2976        } finally {
2977            closeStatement(ps);
2978        }
2979    }
2980
2981    /**
2982     * <p>
2983     * Select the states of all fired-trigger records for a given trigger, or
2984     * trigger group if trigger name is <code>null</code>.
2985     * </p>
2986     * 
2987     * @return a List of FiredTriggerRecord objects.
2988     */
2989    public List   selectFiredTriggerRecords(Connection   conn, String   triggerName,
2990            String   groupName) throws SQLException   {
2991        PreparedStatement   ps = null;
2992        ResultSet   rs = null;
2993        try {
2994            List   lst = new LinkedList  ();
2995
2996            if (triggerName != null) {
2997                ps = conn.prepareStatement(rtp(SELECT_FIRED_TRIGGER));
2998                ps.setString(1, triggerName);
2999                ps.setString(2, groupName);
3000            } else {
3001                ps = conn.prepareStatement(rtp(SELECT_FIRED_TRIGGER_GROUP));
3002                ps.setString(1, groupName);
3003            }
3004            rs = ps.executeQuery();
3005
3006            while (rs.next()) {
3007                FiredTriggerRecord rec = new FiredTriggerRecord();
3008
3009                rec.setFireInstanceId(rs.getString(COL_ENTRY_ID));
3010                rec.setFireInstanceState(rs.getString(COL_ENTRY_STATE));
3011                rec.setFireTimestamp(rs.getLong(COL_FIRED_TIME));
3012                rec.setPriority(rs.getInt(COL_PRIORITY));
3013                rec.setSchedulerInstanceId(rs.getString(COL_INSTANCE_NAME));
3014                rec.setTriggerIsVolatile(getBoolean(rs, COL_IS_VOLATILE));
3015                rec.setTriggerKey(new Key(rs.getString(COL_TRIGGER_NAME), rs
3016                        .getString(COL_TRIGGER_GROUP)));
3017                if (!rec.getFireInstanceState().equals(STATE_ACQUIRED)) {
3018                    rec.setJobIsStateful(getBoolean(rs, COL_IS_STATEFUL));
3019                    rec.setJobRequestsRecovery(rs
3020                            .getBoolean(COL_REQUESTS_RECOVERY));
3021                    rec.setJobKey(new Key(rs.getString(COL_JOB_NAME), rs
3022                            .getString(COL_JOB_GROUP)));
3023                }
3024                lst.add(rec);
3025            }
3026
3027            return lst;
3028        } finally {
3029            closeResultSet(rs);
3030            closeStatement(ps);
3031        }
3032    }
3033
3034    /**
3035     * <p>
3036     * Select the states of all fired-trigger records for a given job, or job
3037     * group if job name is <code>null</code>.
3038     * </p>
3039     * 
3040     * @return a List of FiredTriggerRecord objects.
3041     */
3042    public List   selectFiredTriggerRecordsByJob(Connection   conn, String   jobName,
3043            String   groupName) throws SQLException   {
3044        PreparedStatement   ps = null;
3045        ResultSet   rs = null;
3046        try {
3047            List   lst = new LinkedList  ();
3048
3049            if (jobName != null) {
3050                ps = conn.prepareStatement(rtp(SELECT_FIRED_TRIGGERS_OF_JOB));
3051                ps.setString(1, jobName);
3052                ps.setString(2, groupName);
3053            } else {
3054                ps = conn
3055                        .prepareStatement(rtp(SELECT_FIRED_TRIGGERS_OF_JOB_GROUP));
3056                ps.setString(1, groupName);
3057            }
3058            rs = ps.executeQuery();
3059
3060            while (rs.next()) {
3061                FiredTriggerRecord rec = new FiredTriggerRecord();
3062
3063                rec.setFireInstanceId(rs.getString(COL_ENTRY_ID));
3064                rec.setFireInstanceState(rs.getString(COL_ENTRY_STATE));
3065                rec.setFireTimestamp(rs.getLong(COL_FIRED_TIME));
3066                rec.setPriority(rs.getInt(COL_PRIORITY));
3067                rec.setSchedulerInstanceId(rs.getString(COL_INSTANCE_NAME));
3068                rec.setTriggerIsVolatile(getBoolean(rs, COL_IS_VOLATILE));
3069                rec.setTriggerKey(new Key(rs.getString(COL_TRIGGER_NAME), rs
3070                        .getString(COL_TRIGGER_GROUP)));
3071                if (!rec.getFireInstanceState().equals(STATE_ACQUIRED)) {
3072                    rec.setJobIsStateful(getBoolean(rs, COL_IS_STATEFUL));
3073                    rec.setJobRequestsRecovery(rs
3074                            .getBoolean(COL_REQUESTS_RECOVERY));
3075                    rec.setJobKey(new Key(rs.getString(COL_JOB_NAME), rs
3076                            .getString(COL_JOB_GROUP)));
3077                }
3078                lst.add(rec);
3079            }
3080
3081            return lst;
3082        } finally {
3083            closeResultSet(rs);
3084            closeStatement(ps);
3085        }
3086
3087    }
3088
3089    public List   selectInstancesFiredTriggerRecords(Connection   conn,
3090            String   instanceName) throws SQLException   {
3091        PreparedStatement   ps = null;
3092        ResultSet   rs = null;
3093        try {
3094            List   lst = new LinkedList  ();
3095
3096            ps = conn.prepareStatement(rtp(SELECT_INSTANCES_FIRED_TRIGGERS));
3097            ps.setString(1, instanceName);
3098            rs = ps.executeQuery();
3099
3100            while (rs.next()) {
3101                FiredTriggerRecord rec = new FiredTriggerRecord();
3102
3103                rec.setFireInstanceId(rs.getString(COL_ENTRY_ID));
3104                rec.setFireInstanceState(rs.getString(COL_ENTRY_STATE));
3105                rec.setFireTimestamp(rs.getLong(COL_FIRED_TIME));
3106                rec.setSchedulerInstanceId(rs.getString(COL_INSTANCE_NAME));
3107                rec.setTriggerIsVolatile(getBoolean(rs, COL_IS_VOLATILE));
3108                rec.setTriggerKey(new Key(rs.getString(COL_TRIGGER_NAME), rs
3109                        .getString(COL_TRIGGER_GROUP)));
3110                if (!rec.getFireInstanceState().equals(STATE_ACQUIRED)) {
3111                    rec.setJobIsStateful(getBoolean(rs, COL_IS_STATEFUL));
3112                    rec.setJobRequestsRecovery(rs
3113                            .getBoolean(COL_REQUESTS_RECOVERY));
3114                    rec.setJobKey(new Key(rs.getString(COL_JOB_NAME), rs
3115                            .getString(COL_JOB_GROUP)));
3116                }
3117                rec.setPriority(rs.getInt(COL_PRIORITY));
3118                lst.add(rec);
3119            }
3120
3121            return lst;
3122        } finally {
3123            closeResultSet(rs);
3124            closeStatement(ps);
3125        }
3126    }
3127
3128    /**
3129     * <p>
3130     * Select the distinct instance names of all fired-trigger records.
3131     * </p>
3132     * 
3133     * <p>
3134     * This is useful when trying to identify orphaned fired triggers (a 
3135     * fired trigger without a scheduler state record.) 
3136     * </p>
3137     * 
3138     * @return a Set of String objects.
3139     */
3140    public Set   selectFiredTriggerInstanceNames(Connection   conn) 
3141        throws SQLException   {
3142        PreparedStatement   ps = null;
3143        ResultSet   rs = null;
3144        try {
3145            Set   instanceNames = new HashSet  ();
3146
3147            ps = conn.prepareStatement(rtp(SELECT_FIRED_TRIGGER_INSTANCE_NAMES));
3148            rs = ps.executeQuery();
3149
3150            while (rs.next()) {
3151                instanceNames.add(rs.getString(COL_INSTANCE_NAME));
3152            }
3153
3154            return instanceNames;
3155        } finally {
3156            closeResultSet(rs);
3157            closeStatement(ps);
3158        }
3159    }
3160    
3161    /**
3162     * <p>
3163     * Delete a fired trigger.
3164     * </p>
3165     * 
3166     * @param conn
3167     *          the DB Connection
3168     * @param entryId
3169     *          the fired trigger entry to delete
3170     * @return the number of rows deleted
3171     */
3172    public int deleteFiredTrigger(Connection   conn, String   entryId)
3173        throws SQLException   {
3174        PreparedStatement   ps = null;
3175        try {
3176            ps = conn.prepareStatement(rtp(DELETE_FIRED_TRIGGER));
3177            ps.setString(1, entryId);
3178
3179            return ps.executeUpdate();
3180        } finally {
3181            closeStatement(ps);
3182        }
3183    }
3184
3185    public int selectJobExecutionCount(Connection   conn, String   jobName,
3186            String   jobGroup) throws SQLException   {
3187        PreparedStatement   ps = null;
3188        ResultSet   rs = null;
3189
3190        try {
3191            ps = conn.prepareStatement(rtp(SELECT_JOB_EXECUTION_COUNT));
3192            ps.setString(1, jobName);
3193            ps.setString(2, jobGroup);
3194
3195            rs = ps.executeQuery();
3196
3197            return (rs.next()) ? rs.getInt(1) : 0;
3198        } finally {
3199            closeResultSet(rs);
3200            closeStatement(ps);
3201        }
3202    }
3203
3204    public int deleteVolatileFiredTriggers(Connection   conn) throws SQLException   {
3205        PreparedStatement   ps = null;
3206        try {
3207            ps = conn.prepareStatement(rtp(DELETE_VOLATILE_FIRED_TRIGGERS));
3208            setBoolean(ps, 1, true);
3209
3210            return ps.executeUpdate();
3211        } finally {
3212            closeStatement(ps);
3213        }
3214    }
3215    
3216    public int insertSchedulerState(Connection   conn, String   instanceId,
3217            long checkInTime, long interval)
3218        throws SQLException   {
3219        PreparedStatement   ps = null;
3220        try {
3221            ps = conn.prepareStatement(rtp(INSERT_SCHEDULER_STATE));
3222            ps.setString(1, instanceId);
3223            ps.setLong(2, checkInTime);
3224            ps.setLong(3, interval);
3225
3226            return ps.executeUpdate();
3227        } finally {
3228            closeStatement(ps);
3229        }
3230    }
3231
3232    public int deleteSchedulerState(Connection   conn, String   instanceId)
3233        throws SQLException   {
3234        PreparedStatement   ps = null;
3235        try {
3236            ps = conn.prepareStatement(rtp(DELETE_SCHEDULER_STATE));
3237            ps.setString(1, instanceId);
3238
3239            return ps.executeUpdate();
3240        } finally {
3241            closeStatement(ps);
3242        }
3243    }
3244
3245    public int updateSchedulerState(Connection   conn, String   instanceId, long checkInTime)
3246        throws SQLException   {
3247        PreparedStatement   ps = null;
3248        try {
3249            ps = conn.prepareStatement(rtp(UPDATE_SCHEDULER_STATE));
3250            ps.setLong(1, checkInTime);
3251            ps.setString(2, instanceId);
3252        
3253            return ps.executeUpdate();
3254        } finally {
3255            closeStatement(ps);
3256        }
3257    }
3258        
3259    public List   selectSchedulerStateRecords(Connection   conn, String   instanceId)
3260        throws SQLException   {
3261        PreparedStatement   ps = null;
3262        ResultSet   rs = null;
3263        try {
3264            List   lst = new LinkedList  ();
3265
3266            if (instanceId != null) {
3267                ps = conn.prepareStatement(rtp(SELECT_SCHEDULER_STATE));
3268                ps.setString(1, instanceId);
3269            } else {
3270                ps = conn.prepareStatement(rtp(SELECT_SCHEDULER_STATES));
3271            }
3272            rs = ps.executeQuery();
3273
3274            while (rs.next()) {
3275                SchedulerStateRecord rec = new SchedulerStateRecord();
3276
3277                rec.setSchedulerInstanceId(rs.getString(COL_INSTANCE_NAME));
3278                rec.setCheckinTimestamp(rs.getLong(COL_LAST_CHECKIN_TIME));
3279                rec.setCheckinInterval(rs.getLong(COL_CHECKIN_INTERVAL));
3280
3281                lst.add(rec);
3282            }
3283
3284            return lst;
3285        } finally {
3286            closeResultSet(rs);
3287            closeStatement(ps);
3288        }
3289
3290    }
3291
3292    //---------------------------------------------------------------------------
3293    // protected methods that can be overridden by subclasses
3294    //---------------------------------------------------------------------------
3295
3296    /**
3297     * <p>
3298     * Replace the table prefix in a query by replacing any occurrences of
3299     * "{0}" with the table prefix.
3300     * </p>
3301     * 
3302     * @param query
3303     *          the unsubstitued query
3304     * @return the query, with proper table prefix substituted
3305     */
3306    protected final String   rtp(String   query) {
3307        return Util.rtp(query, tablePrefix);
3308    }
3309
3310    /**
3311     * <p>
3312     * Create a serialized <code>java.util.ByteArrayOutputStream</code>
3313     * version of an Object.
3314     * </p>
3315     * 
3316     * @param obj
3317     *          the object to serialize
3318     * @return the serialized ByteArrayOutputStream
3319     * @throws IOException
3320     *           if serialization causes an error
3321     */
3322    protected ByteArrayOutputStream   serializeObject(Object   obj)
3323        throws IOException   {
3324        ByteArrayOutputStream   baos = new ByteArrayOutputStream  ();
3325        if (null != obj) {
3326            ObjectOutputStream   out = new ObjectOutputStream  (baos);
3327            out.writeObject(obj);
3328            out.flush();
3329        }
3330        return baos;
3331    }
3332
3333    /**
3334     * <p>
3335     * Remove the transient data from and then create a serialized <code>java.util.ByteArrayOutputStream</code>
3336     * version of a <code>{@link org.quartz.JobDataMap}</code>.
3337     * </p>
3338     * 
3339     * @param data
3340     *          the JobDataMap to serialize
3341     * @return the serialized ByteArrayOutputStream
3342     * @throws IOException
3343     *           if serialization causes an error
3344     */
3345    protected ByteArrayOutputStream   serializeJobData(JobDataMap data)
3346        throws IOException   {
3347        if (canUseProperties()) {
3348            return serializeProperties(data);
3349        }
3350
3351        try {
3352            return serializeObject(data);
3353        } catch (NotSerializableException   e) {
3354            throw new NotSerializableException  (
3355                "Unable to serialize JobDataMap for insertion into " + 
3356                "database because the value of property '" + 
3357                getKeyOfNonSerializableValue(data) + 
3358                "' is not serializable: " + e.getMessage());
3359        }
3360    }
3361
3362    /**
3363     * Find the key of the first non-serializable value in the given Map.
3364     * 
3365     * @return The key of the first non-serializable value in the given Map or 
3366     * null if all values are serializable.
3367     */
3368    protected Object   getKeyOfNonSerializableValue(Map   data) {
3369        for (Iterator   entryIter = data.entrySet().iterator(); entryIter.hasNext();) {
3370            Map.Entry   entry = (Map.Entry  )entryIter.next();
3371            
3372            ByteArrayOutputStream   baos = null;
3373            try {
3374                serializeObject(entry.getValue());
3375            } catch (IOException   e) {
3376                return entry.getKey();
3377            } finally {
3378                if (baos != null) {
3379                    try { baos.close(); } catch (IOException   ignore) {}
3380                }
3381            }
3382        }
3383        
3384        // As long as it is true that the Map was not serializable, we should
3385        // not hit this case.
3386        return null;   
3387    }
3388    
3389    /**
3390     * serialize the java.util.Properties
3391     */
3392    private ByteArrayOutputStream   serializeProperties(JobDataMap data)
3393        throws IOException   {
3394        ByteArrayOutputStream   ba = new ByteArrayOutputStream  ();
3395        if (null != data) {
3396            Properties   properties = convertToProperty(data.getWrappedMap());
3397            properties.store(ba, "");
3398        }
3399
3400        return ba;
3401    }
3402
3403    /**
3404     * convert the JobDataMap into a list of properties
3405     */
3406    protected Map   convertFromProperty(Properties   properties) throws IOException   {
3407        return new HashMap  (properties);
3408    }
3409
3410    /**
3411     * convert the JobDataMap into a list of properties
3412     */
3413    protected Properties   convertToProperty(Map   data) throws IOException   {
3414        Properties   properties = new Properties  ();
3415        
3416        for (Iterator   entryIter = data.entrySet().iterator(); entryIter.hasNext();) {
3417            Map.Entry   entry = (Map.Entry  )entryIter.next();
3418            
3419            Object   key = entry.getKey();
3420            Object   val = (entry.getValue() == null) ? "" : entry.getValue();
3421            
3422            if(!(key instanceof String  )) {
3423                throw new IOException  ("JobDataMap keys/values must be Strings " 
3424                        + "when the 'useProperties' property is set. " 
3425                        + " offending Key: " + key);
3426            }
3427            
3428            if(!(val instanceof String  )) {
3429                throw new IOException  ("JobDataMap values must be Strings " 
3430                        + "when the 'useProperties' property is set. " 
3431                        + " Key of offending value: " + key);
3432            }
3433            
3434            properties.put(key, val);
3435        }
3436        
3437        return properties;
3438    }
3439
3440    /**
3441     * <p>
3442     * This method should be overridden by any delegate subclasses that need
3443     * special handling for BLOBs. The default implementation uses standard
3444     * JDBC <code>java.sql.Blob</code> operations.
3445     * </p>
3446     * 
3447     * @param rs
3448     *          the result set, already queued to the correct row
3449     * @param colName
3450     *          the column name for the BLOB
3451     * @return the deserialized Object from the ResultSet BLOB
3452     * @throws ClassNotFoundException
3453     *           if a class found during deserialization cannot be found
3454     * @throws IOException
3455     *           if deserialization causes an error
3456     */
3457    protected Object   getObjectFromBlob(ResultSet   rs, String   colName)
3458        throws ClassNotFoundException  , IOException  , SQLException   {
3459        Object   obj = null;
3460
3461        Blob   blobLocator = rs.getBlob(colName);
3462        if (blobLocator != null) {
3463            InputStream   binaryInput = blobLocator.getBinaryStream();
3464
3465            if (null != binaryInput) {
3466                if (binaryInput instanceof ByteArrayInputStream  
3467                    && ((ByteArrayInputStream  ) binaryInput).available() == 0 ) {
3468                    //do nothing
3469                }
3470                else {
3471                    ObjectInputStream   in = new ObjectInputStream  (binaryInput);
3472                    try {
3473                        obj = in.readObject();
3474                    } finally {
3475                        in.close();
3476                    }
3477                }
3478            }
3479
3480        }
3481        return obj;
3482    }
3483
3484    public Key[] selectVolatileTriggers(Connection   conn) throws SQLException   {
3485        PreparedStatement   ps = null;
3486        ResultSet   rs = null;
3487
3488        try {
3489            ps = conn.prepareStatement(rtp(SELECT_VOLATILE_TRIGGERS));
3490            setBoolean(ps, 1, true);
3491            rs = ps.executeQuery();
3492
3493            ArrayList   list = new ArrayList  ();
3494            while (rs.next()) {
3495                String   triggerName = rs.getString(COL_TRIGGER_NAME);
3496                String   groupName = rs.getString(COL_TRIGGER_GROUP);
3497                list.add(new Key(triggerName, groupName));
3498            }
3499            Object  [] oArr = list.toArray();
3500            Key[] kArr = new Key[oArr.length];
3501            System.arraycopy(oArr, 0, kArr, 0, oArr.length);
3502            return kArr;
3503        } finally {
3504            closeResultSet(rs);
3505            closeStatement(ps);
3506        }
3507    }
3508
3509    public Key[] selectVolatileJobs(Connection   conn) throws SQLException   {
3510        PreparedStatement   ps = null;
3511        ResultSet   rs = null;
3512
3513        try {
3514            ps = conn.prepareStatement(rtp(SELECT_VOLATILE_JOBS));
3515            setBoolean(ps, 1, true);
3516            rs = ps.executeQuery();
3517
3518            ArrayList   list = new ArrayList  ();
3519            while (rs.next()) {
3520                String   triggerName = rs.getString(COL_JOB_NAME);
3521                String   groupName = rs.getString(COL_JOB_GROUP);
3522                list.add(new Key(triggerName, groupName));
3523            }
3524            Object  [] oArr = list.toArray();
3525            Key[] kArr = new Key[oArr.length];
3526            System.arraycopy(oArr, 0, kArr, 0, oArr.length);
3527            return kArr;
3528        } finally {
3529            closeResultSet(rs);
3530            closeStatement(ps);
3531        }
3532    }
3533
3534    /**
3535     * <p>
3536     * This method should be overridden by any delegate subclasses that need
3537     * special handling for BLOBs for job details. The default implementation
3538     * uses standard JDBC <code>java.sql.Blob</code> operations.
3539     * </p>
3540     * 
3541     * @param rs
3542     *          the result set, already queued to the correct row
3543     * @param colName
3544     *          the column name for the BLOB
3545     * @return the deserialized Object from the ResultSet BLOB
3546     * @throws ClassNotFoundException
3547     *           if a class found during deserialization cannot be found
3548     * @throws IOException
3549     *           if deserialization causes an error
3550     */
3551    protected Object   getJobDetailFromBlob(ResultSet   rs, String   colName)
3552        throws ClassNotFoundException  , IOException  , SQLException   {
3553        if (canUseProperties()) {
3554            Blob   blobLocator = rs.getBlob(colName);
3555            if (blobLocator != null) {
3556                InputStream   binaryInput = blobLocator.getBinaryStream();
3557                return binaryInput;
3558            } else {
3559                return null;
3560            }
3561        }
3562
3563        return getObjectFromBlob(rs, colName);
3564    }
3565
3566    /** 
3567     * @see org.quartz.impl.jdbcjobstore.DriverDelegate#selectPausedTriggerGroups(java.sql.Connection)
3568     */
3569    public Set   selectPausedTriggerGroups(Connection   conn) throws SQLException   {
3570        PreparedStatement   ps = null;
3571        ResultSet   rs = null;
3572
3573        HashSet   set = new HashSet  ();
3574        try {
3575            ps = conn.prepareStatement(rtp(SELECT_PAUSED_TRIGGER_GROUPS));
3576            rs = ps.executeQuery();
3577
3578            while (rs.next()) {
3579                String   groupName = rs.getString(COL_TRIGGER_GROUP);
3580                set.add(groupName);
3581            }
3582            return set;
3583        } finally {
3584            closeResultSet(rs);
3585            closeStatement(ps);
3586        }
3587    }
3588
3589    /**
3590     * Cleanup helper method that closes the given <code>ResultSet</code>
3591     * while ignoring any errors.
3592     */
3593    protected void closeResultSet(ResultSet   rs) {
3594        if (null != rs) {
3595            try {
3596                rs.close();
3597            } catch (SQLException   ignore) {
3598            }
3599        }
3600    }
3601
3602    /**
3603     * Cleanup helper method that closes the given <code>Statement</code>
3604     * while ignoring any errors.
3605     */
3606    protected void closeStatement(Statement   statement) {
3607        if (null != statement) {
3608            try {
3609                statement.close();
3610            } catch (SQLException   ignore) {
3611            }
3612        }
3613    }
3614    
3615
3616    /**
3617     * Sets the designated parameter to the given Java <code>boolean</code> value.
3618     * This just wraps <code>{@link PreparedStatement#setBoolean(int, boolean)}</code>
3619     * by default, but it can be overloaded by subclass delegates for databases that
3620     * don't explicitly support the boolean type.
3621     */
3622    protected void setBoolean(PreparedStatement   ps, int index, boolean val) throws SQLException   {
3623        ps.setBoolean(index, val);
3624    }
3625
3626    /**
3627     * Retrieves the value of the designated column in the current row as
3628     * a <code>boolean</code>.
3629     * This just wraps <code>{@link ResultSet#getBoolean(java.lang.String)}</code>
3630     * by default, but it can be overloaded by subclass delegates for databases that
3631     * don't explicitly support the boolean type.
3632     */
3633    protected boolean getBoolean(ResultSet   rs, String   columnName) throws SQLException   {
3634        return rs.getBoolean(columnName);
3635    }
3636    
3637    /**
3638     * Retrieves the value of the designated column index in the current row as
3639     * a <code>boolean</code>.
3640     * This just wraps <code>{@link ResultSet#getBoolean(java.lang.String)}</code>
3641     * by default, but it can be overloaded by subclass delegates for databases that
3642     * don't explicitly support the boolean type.
3643     */
3644    protected boolean getBoolean(ResultSet   rs, int columnIndex) throws SQLException   {
3645        return rs.getBoolean(columnIndex);
3646    }
3647    
3648    /**
3649     * Sets the designated parameter to the byte array of the given
3650     * <code>ByteArrayOutputStream</code>.  Will set parameter value to null if the 
3651     * <code>ByteArrayOutputStream</code> is null.
3652     * This just wraps <code>{@link PreparedStatement#setBytes(int, byte[])}</code>
3653     * by default, but it can be overloaded by subclass delegates for databases that
3654     * don't explicitly support storing bytes in this way.
3655     */
3656    protected void setBytes(PreparedStatement   ps, int index, ByteArrayOutputStream   baos) throws SQLException   {
3657        ps.setBytes(index, (baos == null) ? new byte[0] : baos.toByteArray());
3658    }
3659}
3660
3661// EOF
3662
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags