KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > infoglue > cms > util > workflow > InfoGlueJDBCWorkflowStore


1 /* ===============================================================================
2 *
3 * Part of the InfoGlue Content Management Platform (www.infoglue.org)
4 *
5 * ===============================================================================
6 *
7 * Copyright (C)
8 *
9 * This program is free software; you can redistribute it and/or modify it under
10 * the terms of the GNU General Public License version 2, as published by the
11 * Free Software Foundation. See the file LICENSE.html for more information.
12 *
13 * This program is distributed in the hope that it will be useful, but WITHOUT
14 * ANY WARRANTY, including the implied warranty of MERCHANTABILITY or FITNESS
15 * FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 *
17 * You should have received a copy of the GNU General Public License along with
18 * this program; if not, write to the Free Software Foundation, Inc. / 59 Temple
19 * Place, Suite 330 / Boston, MA 02111-1307 / USA.
20 *
21 * ===============================================================================
22 */

23
24 package org.infoglue.cms.util.workflow;
25
26 import java.sql.Connection JavaDoc;
27 import java.sql.DriverManager JavaDoc;
28 import java.sql.PreparedStatement JavaDoc;
29 import java.sql.ResultSet JavaDoc;
30 import java.sql.SQLException JavaDoc;
31 import java.sql.Statement JavaDoc;
32 import java.sql.Timestamp JavaDoc;
33 import java.sql.Types JavaDoc;
34 import java.util.ArrayList JavaDoc;
35 import java.util.Date JavaDoc;
36 import java.util.Iterator JavaDoc;
37 import java.util.List JavaDoc;
38 import java.util.Map JavaDoc;
39
40 import org.apache.log4j.Logger;
41
42 import com.opensymphony.workflow.StoreException;
43 import com.opensymphony.workflow.query.WorkflowQuery;
44 import com.opensymphony.workflow.spi.SimpleStep;
45 import com.opensymphony.workflow.spi.SimpleWorkflowEntry;
46 import com.opensymphony.workflow.spi.Step;
47 import com.opensymphony.workflow.spi.WorkflowEntry;
48 import com.opensymphony.workflow.spi.jdbc.JDBCWorkflowStore;
49
50
51 /**
52  * JDBC implementation just overiding the init-method of the default JDBCWorkflowStore as it demanded DataStores.
53  *
54  * The following properties are all <b>required</b>:
55  * <ul>
56  * <li><b>datasource</b> - the JNDI location for the DataSource that is to be used.</li>
57  * <li><b>entry.sequence</b> - SQL query that returns the next ID for a workflow entry</li>
58  * <li><b>entry.table</b> - table name for workflow entry</li>
59  * <li><b>entry.id</b> - column name for workflow entry ID field</li>
60  * <li><b>entry.name</b> - column name for workflow entry name field</li>
61  * <li><b>entry.state</b> - column name for workflow entry state field</li>
62  * <li><b>step.sequence</b> - SQL query that returns the next ID for a workflow step</li>
63  * <li><b>history.table</b> - table name for steps in history</li>
64  * <li><b>current.table</b> - table name for current steps</li>
65  * <li><b>step.id</b> - column name for step ID field</li>
66  * <li><b>step.entryId</b> - column name for workflow entry ID field (foreign key relationship to [entry.table].[entry.id])</li>
67  * <li><b>step.stepId</b> - column name for step workflow definition step field</li>
68  * <li><b>step.actionId</b> - column name for step action field</li>
69  * <li><b>step.owner</b> - column name for step owner field</li>
70  * <li><b>step.caller</b> - column name for step caller field</li>
71  * <li><b>step.startDate</b> - column name for step start date field</li>
72  * <li><b>step.dueDate</b> - column name for optional step due date field</li>
73  * <li><b>step.finishDate</b> - column name for step finish date field</li>
74  * <li><b>step.status</b> - column name for step status field</li>
75  * <li><b>currentPrev.table</b> - table name for the previous IDs for current steps</li>
76  * <li><b>historyPrev.table</b> - table name for the previous IDs for history steps</li>
77  * <li><b>step.previousId</b> - column name for step ID field (foreign key relation to [history.table].[step.id] or [current.table].[step.id])</li>
78  * </ul>
79  *
80  * @author Mattias Bogeblad
81  */

82 public class InfoGlueJDBCWorkflowStore extends JDBCWorkflowStore
83 {
84     private final static Logger logger = Logger.getLogger(InfoGlueJDBCWorkflowStore.class.getName());
85
86     //~ Instance fields ////////////////////////////////////////////////////////
87
/*
88     protected DataSource ds;
89     protected String currentPrevTable;
90     protected String currentTable;
91     protected String entryId;
92     protected String entryName;
93     protected String entrySequence;
94     protected String entryState;
95     protected String entryTable;
96     protected String historyPrevTable;
97     protected String historyTable;
98     protected String stepActionId;
99     protected String stepCaller;
100     protected String stepDueDate;
101     protected String stepEntryId;
102     protected String stepFinishDate;
103     protected String stepId;
104     protected String stepOwner;
105     protected String stepPreviousId;
106     protected String stepSequence;
107     protected String stepStartDate;
108     protected String stepStatus;
109     protected String stepStepId;
110     protected boolean closeConnWhenDone = false;
111     */

112     private String JavaDoc userName;
113     private String JavaDoc password;
114     private String JavaDoc driverClassName;
115     private String JavaDoc url;
116     
117     public void init(Map JavaDoc props) throws StoreException
118     {
119         super.init(props);
120         
121         userName = getInitProperty(props, "username", "root");
122         password = getInitProperty(props, "password", "");
123         driverClassName = getInitProperty(props, "driverClassName", "com.mysql.jdbc.Driver");
124         url = getInitProperty(props, "url", "jdbc:mysql://localhost/infoglueWM?autoReconnect=true");
125
126         /*
127         entrySequence = getInitProperty(props, "entry.sequence", "SELECT nextVal('seq_os_wfentry')");
128         stepSequence = getInitProperty(props, "step.sequence", "SELECT nextVal('seq_os_currentsteps')");
129         entryTable = getInitProperty(props, "entry.table", "OS_WFENTRY");
130         entryId = getInitProperty(props, "entry.id", "ID");
131         entryName = getInitProperty(props, "entry.name", "NAME");
132         entryState = getInitProperty(props, "entry.state", "STATE");
133         historyTable = getInitProperty(props, "history.table", "OS_HISTORYSTEP");
134         currentTable = getInitProperty(props, "current.table", "OS_CURRENTSTEP");
135         currentPrevTable = getInitProperty(props, "currentPrev.table", "OS_CURRENTSTEP_PREV");
136         historyPrevTable = getInitProperty(props, "historyPrev.table", "OS_HISTORYSTEP_PREV");
137         stepId = getInitProperty(props, "step.id", "ID");
138         stepEntryId = getInitProperty(props, "step.entryId", "ENTRY_ID");
139         stepStepId = getInitProperty(props, "step.stepId", "STEP_ID");
140         stepActionId = getInitProperty(props, "step.actionId", "ACTION_ID");
141         stepOwner = getInitProperty(props, "step.owner", "OWNER");
142         stepCaller = getInitProperty(props, "step.caller", "CALLER");
143         stepStartDate = getInitProperty(props, "step.startDate", "START_DATE");
144         stepFinishDate = getInitProperty(props, "step.finishDate", "FINISH_DATE");
145         stepDueDate = getInitProperty(props, "step.dueDate", "DUE_DATE");
146         stepStatus = getInitProperty(props, "step.status", "STATUS");
147         stepPreviousId = getInitProperty(props, "step.previousId", "PREVIOUS_ID");
148         */

149     }
150     
151     protected Connection JavaDoc getConnection() throws SQLException JavaDoc
152     {
153         closeConnWhenDone = true;
154
155         Connection JavaDoc conn = null;
156         
157         logger.info("Establishing connection to database '" + this.url + "'");
158
159         try
160         {
161             Class.forName(this.driverClassName).newInstance();
162             conn = DriverManager.getConnection(url, this.userName, this.password);
163         }
164         catch (Exception JavaDoc ex)
165         {
166             ex.printStackTrace();
167         }
168         
169         return conn;
170     }
171
172     public void setEntryState(long id, int state) throws StoreException {
173         Connection JavaDoc conn = null;
174         PreparedStatement JavaDoc ps = null;
175
176         try {
177             conn = getConnection();
178
179             String JavaDoc sql = "UPDATE " + entryTable + " SET " + entryState + " = ? WHERE " + entryId + " = ?";
180             ps = conn.prepareStatement(sql);
181             ps.setInt(1, state);
182             ps.setLong(2, id);
183             ps.executeUpdate();
184         } catch (SQLException JavaDoc e) {
185             throw new StoreException("Unable to update state for workflow instance #" + id + " to " + state, e);
186         } finally {
187             cleanup(conn, ps, null);
188         }
189     }
190     
191     public Step createCurrentStep(long entryId, int wfStepId, String JavaDoc owner, Date JavaDoc startDate, Date JavaDoc dueDate, String JavaDoc status, long[] previousIds) throws StoreException {
192         Connection JavaDoc conn = null;
193
194         try {
195             conn = getConnection();
196
197             long id = createCurrentStep(conn, entryId, wfStepId, owner, startDate, dueDate, status);
198             addPreviousSteps(conn, id, previousIds);
199
200             return new SimpleStep(id, entryId, wfStepId, 0, owner, startDate, dueDate, null, status, previousIds, null);
201         } catch (SQLException JavaDoc e) {
202             throw new StoreException("Unable to create current step for workflow instance #" + entryId, e);
203         } finally {
204             cleanup(conn, null, null);
205         }
206     }
207
208     public WorkflowEntry createEntry(String JavaDoc workflowName) throws StoreException {
209         Connection JavaDoc conn = null;
210         PreparedStatement JavaDoc stmt = null;
211
212         try {
213             conn = getConnection();
214
215             String JavaDoc sql = "INSERT INTO " + entryTable + " (" + entryId + ", " + entryName + ", " + entryState + ") VALUES (?,?,?)";
216
217             if (logger.isDebugEnabled()) {
218                 logger.debug("Executing SQL statement: " + sql);
219             }
220
221             stmt = conn.prepareStatement(sql);
222
223             long id = getNextEntrySequence(conn);
224             stmt.setLong(1, id);
225             stmt.setString(2, workflowName);
226             stmt.setInt(3, WorkflowEntry.CREATED);
227             stmt.executeUpdate();
228
229             return new SimpleWorkflowEntry(id, workflowName, WorkflowEntry.CREATED);
230         } catch (SQLException JavaDoc e) {
231             e.printStackTrace();
232             throw new StoreException("Error creating new workflow instance", e);
233         } finally {
234             cleanup(conn, stmt, null);
235         }
236     }
237
238     public List JavaDoc findCurrentSteps(long entryId) throws StoreException {
239         Connection JavaDoc conn = null;
240         PreparedStatement JavaDoc stmt = null;
241         ResultSet JavaDoc rset = null;
242         PreparedStatement JavaDoc stmt2 = null;
243
244         try {
245             conn = getConnection();
246
247             String JavaDoc sql = "SELECT " + stepId + ", " + stepStepId + ", " + stepActionId + ", " + stepOwner + ", " + stepStartDate + ", " + stepDueDate + ", " + stepFinishDate + ", " + stepStatus + ", " + stepCaller + " FROM " + currentTable + " WHERE " + stepEntryId + " = ?";
248             String JavaDoc sql2 = "SELECT " + stepPreviousId + " FROM " + currentPrevTable + " WHERE " + stepId + " = ?";
249
250             if (logger.isDebugEnabled()) {
251                 logger.debug("Executing SQL statement: " + sql);
252             }
253
254             stmt = conn.prepareStatement(sql);
255
256             if (logger.isDebugEnabled()) {
257                 logger.debug("Executing SQL statement: " + sql2);
258             }
259
260             stmt2 = conn.prepareStatement(sql2);
261             stmt.setLong(1, entryId);
262
263             rset = stmt.executeQuery();
264
265             ArrayList JavaDoc currentSteps = new ArrayList JavaDoc();
266
267             while (rset.next()) {
268                 long id = rset.getLong(1);
269                 int stepId = rset.getInt(2);
270                 int actionId = rset.getInt(3);
271                 String JavaDoc owner = rset.getString(4);
272                 Date JavaDoc startDate = rset.getTimestamp(5);
273                 Date JavaDoc dueDate = rset.getTimestamp(6);
274                 Date JavaDoc finishDate = rset.getTimestamp(7);
275                 String JavaDoc status = rset.getString(8);
276                 String JavaDoc caller = rset.getString(9);
277
278                 ArrayList JavaDoc prevIdsList = new ArrayList JavaDoc();
279                 stmt2.setLong(1, id);
280
281                 ResultSet JavaDoc rs = stmt2.executeQuery();
282
283                 while (rs.next()) {
284                     long prevId = rs.getLong(1);
285                     prevIdsList.add(new Long JavaDoc(prevId));
286                 }
287
288                 long[] prevIds = new long[prevIdsList.size()];
289                 int i = 0;
290
291                 for (Iterator JavaDoc iterator = prevIdsList.iterator();
292                         iterator.hasNext();) {
293                     Long JavaDoc aLong = (Long JavaDoc) iterator.next();
294                     prevIds[i] = aLong.longValue();
295                     i++;
296                 }
297
298                 SimpleStep step = new SimpleStep(id, entryId, stepId, actionId, owner, startDate, dueDate, finishDate, status, prevIds, caller);
299                 currentSteps.add(step);
300             }
301
302             return currentSteps;
303         } catch (SQLException JavaDoc e) {
304             throw new StoreException("Unable to locate current steps for workflow instance #" + entryId, e);
305         } finally {
306             cleanup(null, stmt2, null);
307             cleanup(conn, stmt, rset);
308         }
309     }
310
311     public WorkflowEntry findEntry(long theEntryId) throws StoreException {
312         Connection JavaDoc conn = null;
313         PreparedStatement JavaDoc stmt = null;
314         ResultSet JavaDoc rset = null;
315
316         try {
317             conn = getConnection();
318
319             String JavaDoc sql = "SELECT " + entryName + ", " + entryState + " FROM " + entryTable + " WHERE " + entryId + " = ?";
320
321             if (logger.isDebugEnabled()) {
322                 logger.debug("Executing SQL statement: " + sql);
323             }
324
325             stmt = conn.prepareStatement(sql);
326             stmt.setLong(1, theEntryId);
327
328             rset = stmt.executeQuery();
329             rset.next();
330
331             String JavaDoc workflowName = rset.getString(1);
332             int state = rset.getInt(2);
333
334             return new SimpleWorkflowEntry(theEntryId, workflowName, state);
335         } catch (SQLException JavaDoc e) {
336             throw new StoreException("Error finding workflow instance #" + entryId);
337         } finally {
338             cleanup(conn, stmt, rset);
339         }
340     }
341
342     public List JavaDoc findHistorySteps(long entryId) throws StoreException {
343         Connection JavaDoc conn = null;
344         PreparedStatement JavaDoc stmt = null;
345         PreparedStatement JavaDoc stmt2 = null;
346         ResultSet JavaDoc rset = null;
347
348         try {
349             conn = getConnection();
350
351             String JavaDoc sql = "SELECT " + stepId + ", " + stepStepId + ", " + stepActionId + ", " + stepOwner + ", " + stepStartDate + ", " + stepDueDate + ", " + stepFinishDate + ", " + stepStatus + ", " + stepCaller + " FROM " + historyTable + " WHERE " + stepEntryId + " = ? ORDER BY " + stepId + " DESC";
352             String JavaDoc sql2 = "SELECT " + stepPreviousId + " FROM " + historyPrevTable + " WHERE " + stepId + " = ?";
353
354             if (logger.isDebugEnabled()) {
355                 logger.debug("Executing SQL statement: " + sql);
356             }
357
358             stmt = conn.prepareStatement(sql);
359
360             if (logger.isDebugEnabled()) {
361                 logger.debug("Executing SQL statement: " + sql2);
362             }
363
364             stmt2 = conn.prepareStatement(sql2);
365             stmt.setLong(1, entryId);
366
367             rset = stmt.executeQuery();
368
369             ArrayList JavaDoc currentSteps = new ArrayList JavaDoc();
370
371             while (rset.next()) {
372                 long id = rset.getLong(1);
373                 int stepId = rset.getInt(2);
374                 int actionId = rset.getInt(3);
375                 String JavaDoc owner = rset.getString(4);
376                 Date JavaDoc startDate = rset.getTimestamp(5);
377                 Date JavaDoc dueDate = rset.getTimestamp(6);
378                 Date JavaDoc finishDate = rset.getTimestamp(7);
379                 String JavaDoc status = rset.getString(8);
380                 String JavaDoc caller = rset.getString(9);
381
382                 ArrayList JavaDoc prevIdsList = new ArrayList JavaDoc();
383                 stmt2.setLong(1, id);
384
385                 ResultSet JavaDoc rs = stmt2.executeQuery();
386
387                 while (rs.next()) {
388                     long prevId = rs.getLong(1);
389                     prevIdsList.add(new Long JavaDoc(prevId));
390                 }
391
392                 long[] prevIds = new long[prevIdsList.size()];
393                 int i = 0;
394
395                 for (Iterator JavaDoc iterator = prevIdsList.iterator();
396                         iterator.hasNext();) {
397                     Long JavaDoc aLong = (Long JavaDoc) iterator.next();
398                     prevIds[i] = aLong.longValue();
399                     i++;
400                 }
401
402                 SimpleStep step = new SimpleStep(id, entryId, stepId, actionId, owner, startDate, dueDate, finishDate, status, prevIds, caller);
403                 currentSteps.add(step);
404             }
405
406             return currentSteps;
407         } catch (SQLException JavaDoc e) {
408             throw new StoreException("Unable to locate history steps for workflow instance #" + entryId, e);
409         } finally {
410             cleanup(null, stmt2, null);
411             cleanup(conn, stmt, rset);
412         }
413     }
414     
415     public Step markFinished(Step step, int actionId, Date JavaDoc finishDate, String JavaDoc status, String JavaDoc caller) throws StoreException
416     {
417         Connection JavaDoc conn = null;
418         PreparedStatement JavaDoc stmt = null;
419
420         try
421         {
422             conn = getConnection();
423
424             String JavaDoc sql = "UPDATE " + currentTable + " SET " + stepStatus + " = ?, " + stepActionId + " = ?, " + stepFinishDate + " = ?, " + stepCaller + " = ? WHERE " + stepId + " = ?";
425
426             logger.info("Executing SQL statement: " + sql);
427             logger.info("status: " + status);
428             logger.info("actionId: " + actionId);
429             logger.info("new Timestamp(finishDate.getTime()): " + new Timestamp JavaDoc(finishDate.getTime()));
430             logger.info("caller: " + caller);
431             logger.info("step.getId(): " + step.getId());
432             
433             if (logger.isDebugEnabled())
434             {
435                 logger.debug("Executing SQL statement: " + sql);
436             }
437
438             stmt = conn.prepareStatement(sql);
439             stmt.setString(1, status);
440             stmt.setInt(2, actionId);
441             stmt.setTimestamp(3, new Timestamp JavaDoc(finishDate.getTime()));
442             stmt.setString(4, caller);
443             stmt.setLong(5, step.getId());
444             stmt.executeUpdate();
445
446             SimpleStep theStep = (SimpleStep) step;
447             theStep.setActionId(actionId);
448             theStep.setFinishDate(finishDate);
449             theStep.setStatus(status);
450             theStep.setCaller(caller);
451
452             return theStep;
453         }
454         catch (SQLException JavaDoc e)
455         {
456             throw new StoreException("Unable to mark step finished for #" + step.getEntryId(), e);
457         }
458         finally
459         {
460             cleanup(conn, stmt, null);
461         }
462     }
463
464     public void moveToHistory(Step step) throws StoreException
465     {
466         Connection JavaDoc conn = null;
467         PreparedStatement JavaDoc stmt = null;
468
469         try
470         {
471             conn = getConnection();
472
473             String JavaDoc sql = "INSERT INTO " + historyTable + " (" + stepId + "," + stepEntryId + ", " + stepStepId + ", " + stepActionId + ", " + stepOwner + ", " + stepStartDate + ", " + stepFinishDate + ", " + stepStatus + ", " + stepCaller + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
474
475             logger.info("Executing SQL statement: " + sql);
476             logger.info("step.getId(): " + step.getId());
477             logger.info("step.getEntryId(): " + step.getEntryId());
478             logger.info("step.getStepId(): " + step.getStepId());
479             logger.info("step.getActionId(): " + step.getActionId());
480             logger.info("step.getOwner(): " + step.getOwner());
481             logger.info("new Timestamp(step.getStartDate().getTime()): " + new Timestamp JavaDoc(step.getStartDate().getTime()));
482             
483             if (logger.isDebugEnabled())
484             {
485                 logger.debug("Executing SQL statement: " + sql);
486             }
487
488             stmt = conn.prepareStatement(sql);
489             stmt.setLong(1, step.getId());
490             stmt.setLong(2, step.getEntryId());
491             stmt.setInt(3, step.getStepId());
492             stmt.setInt(4, step.getActionId());
493             stmt.setString(5, step.getOwner());
494             stmt.setTimestamp(6, new Timestamp JavaDoc(step.getStartDate().getTime()));
495
496             if (step.getFinishDate() != null)
497             {
498                 stmt.setTimestamp(7, new Timestamp JavaDoc(step.getFinishDate().getTime()));
499             }
500             else
501             {
502                 stmt.setNull(7, Types.TIMESTAMP);
503             }
504
505             stmt.setString(8, step.getStatus());
506             stmt.setString(9, step.getCaller());
507             stmt.executeUpdate();
508
509             long[] previousIds = step.getPreviousStepIds();
510
511             if ((previousIds != null) && (previousIds.length > 0))
512             {
513                 sql = "INSERT INTO " + historyPrevTable + " (" + stepId + ", " + stepPreviousId + ") VALUES (?, ?)";
514                 logger.debug("Executing SQL statement: " + sql);
515                 cleanup(null, stmt, null);
516                 stmt = conn.prepareStatement(sql);
517
518                 for (int i = 0; i < previousIds.length; i++)
519                 {
520                     long previousId = previousIds[i];
521                     stmt.setLong(1, step.getId());
522                     stmt.setLong(2, previousId);
523                     stmt.executeUpdate();
524                 }
525             }
526
527             sql = "DELETE FROM " + currentPrevTable + " WHERE " + stepId + " = ?";
528
529             if (logger.isDebugEnabled())
530             {
531                 logger.debug("Executing SQL statement: " + sql);
532             }
533
534             cleanup(null, stmt, null);
535             stmt = conn.prepareStatement(sql);
536             stmt.setLong(1, step.getId());
537             stmt.executeUpdate();
538
539             sql = "DELETE FROM " + currentTable + " WHERE " + stepId + " = ?";
540
541             if (logger.isDebugEnabled())
542             {
543                 logger.debug("Executing SQL statement: " + sql);
544             }
545
546             cleanup(null, stmt, null);
547             stmt = conn.prepareStatement(sql);
548             stmt.setLong(1, step.getId());
549             stmt.executeUpdate();
550         }
551         catch (SQLException JavaDoc e)
552         {
553             throw new StoreException("Unable to move current step to history step for #" + step.getEntryId(), e);
554         }
555         finally
556         {
557             cleanup(conn, stmt, null);
558         }
559     }
560
561     public List JavaDoc query(WorkflowQuery query) throws StoreException
562     {
563         List JavaDoc results = new ArrayList JavaDoc();
564
565         // going to try to do all the comparisons in one query
566
String JavaDoc sel;
567         String JavaDoc table;
568
569         int qtype = query.getType();
570
571         if (qtype == 0) { // then not set, so look in sub queries
572
// todo: not sure if you would have a query that would look in both old and new, if so, i'll have to change this - TR
573
// but then again, why are there redundant tables in the first place? the data model should probably change
574

575             if (query.getLeft() != null)
576             {
577                 qtype = query.getLeft().getType();
578             }
579         }
580
581         if (qtype == WorkflowQuery.CURRENT) {
582             table = currentTable;
583         } else {
584             table = historyTable;
585         }
586
587         sel = "SELECT DISTINCT(" + stepEntryId + ") FROM " + table + " WHERE ";
588         sel += queryWhere(query);
589
590         if (logger.isDebugEnabled()) {
591             logger.debug(sel);
592         }
593
594         Connection JavaDoc conn = null;
595         Statement JavaDoc stmt = null;
596         ResultSet JavaDoc rs = null;
597
598         try {
599             conn = getConnection();
600             stmt = conn.createStatement();
601             rs = stmt.executeQuery(sel);
602
603             while (rs.next()) {
604                 // get entryIds and add to results list
605
Long JavaDoc id = new Long JavaDoc(rs.getLong(stepEntryId));
606                 results.add(id);
607             }
608         } catch (SQLException JavaDoc ex) {
609             throw new StoreException("SQL Exception in query: " + ex.getMessage());
610         } finally {
611             cleanup(conn, stmt, rs);
612         }
613
614         return results;
615     }
616
617
618     private List JavaDoc doExpressionQuery(String JavaDoc sel, String JavaDoc columnName, List JavaDoc values) throws StoreException {
619         if (logger.isDebugEnabled()) {
620             logger.debug(sel);
621         }
622
623         Connection JavaDoc conn = null;
624         PreparedStatement JavaDoc stmt = null;
625         ResultSet JavaDoc rs = null;
626         List JavaDoc results = new ArrayList JavaDoc();
627
628         try {
629             conn = getConnection();
630             stmt = conn.prepareStatement(sel);
631
632             if (!values.isEmpty()) {
633                 for (int i = 1; i <= values.size(); i++) {
634                     stmt.setObject(i, values.get(i - 1));
635                 }
636             }
637
638             rs = stmt.executeQuery();
639
640             while (rs.next()) {
641                 // get entryIds and add to results list
642
Long JavaDoc id = new Long JavaDoc(rs.getLong(columnName));
643                 results.add(id);
644             }
645
646             return results;
647         } catch (SQLException JavaDoc ex) {
648             throw new StoreException("SQL Exception in query: " + ex.getMessage());
649         } finally {
650             cleanup(conn, stmt, rs);
651         }
652     }
653
654     private String JavaDoc getInitProperty(Map JavaDoc props, String JavaDoc strName, String JavaDoc strDefault)
655     {
656         Object JavaDoc o = props.get(strName);
657
658         if (o == null) {
659             return strDefault;
660         }
661
662         return (String JavaDoc) o;
663     }
664     
665     private String JavaDoc queryWhere(WorkflowQuery query) {
666         if (query.getLeft() == null) {
667             // leaf node
668
return queryComparison(query);
669         } else {
670             int operator = query.getOperator();
671             WorkflowQuery left = query.getLeft();
672             WorkflowQuery right = query.getRight();
673
674             switch (operator) {
675             case WorkflowQuery.AND:
676                 return "(" + queryWhere(left) + " AND " + queryWhere(right) + ")";
677
678             case WorkflowQuery.OR:
679                 return "(" + queryWhere(left) + " OR " + queryWhere(right) + ")";
680
681             case WorkflowQuery.XOR:
682                 return "(" + queryWhere(left) + " XOR " + queryWhere(right) + ")";
683             }
684         }
685
686         return ""; // not sure if we should throw an exception or how this should be handled
687
}
688     
689     private String JavaDoc queryComparison(WorkflowQuery query) {
690         Object JavaDoc value = query.getValue();
691         int operator = query.getOperator();
692         int field = query.getField();
693
694         //int type = query.getType();
695
String JavaDoc oper;
696
697         switch (operator) {
698         case WorkflowQuery.EQUALS:
699             oper = " = ";
700
701             break;
702
703         case WorkflowQuery.NOT_EQUALS:
704             oper = " <> ";
705
706             break;
707
708         case WorkflowQuery.GT:
709             oper = " > ";
710
711             break;
712
713         case WorkflowQuery.LT:
714             oper = " < ";
715
716             break;
717
718         default:
719             oper = " = ";
720         }
721
722         String JavaDoc left;
723         String JavaDoc right;
724
725         switch (field) {
726         case WorkflowQuery.ACTION: // actionId
727
left = stepActionId;
728
729             break;
730
731         case WorkflowQuery.CALLER:
732             left = stepCaller;
733
734             break;
735
736         case WorkflowQuery.FINISH_DATE:
737             left = stepFinishDate;
738
739             break;
740
741         case WorkflowQuery.OWNER:
742             left = stepOwner;
743
744             break;
745
746         case WorkflowQuery.START_DATE:
747             left = stepStartDate;
748
749             break;
750
751         case WorkflowQuery.STEP: // stepId
752
left = stepStepId;
753
754             break;
755
756         case WorkflowQuery.STATUS:
757             left = stepStatus;
758
759             break;
760
761         default:
762             left = "1";
763         }
764
765         if (value != null) {
766             right = "'" + escape(value.toString()) + "'";
767         } else {
768             right = "null";
769         }
770
771         return left + oper + right;
772     }
773     
774     private static String JavaDoc escape(String JavaDoc s) {
775         StringBuffer JavaDoc sb = new StringBuffer JavaDoc(s);
776
777         char c;
778         char[] chars = s.toCharArray();
779
780         for (int i = 0; i < chars.length; i++) {
781             c = chars[i];
782
783             switch (c) {
784             case '\'':
785                 sb.insert(i, '\'');
786                 i++;
787
788                 break;
789
790             case '\\':
791                 sb.insert(i, '\\');
792                 i++;
793             }
794         }
795
796         return sb.toString();
797     }
798  
799 }
800
801
Popular Tags