|
1
5 package com.opensymphony.workflow.spi.jdbc;
6
7 import com.opensymphony.module.propertyset.PropertySet;
8 import com.opensymphony.module.propertyset.PropertySetManager;
9
10 import com.opensymphony.workflow.StoreException;
11 import com.opensymphony.workflow.query.Expression;
12 import com.opensymphony.workflow.query.FieldExpression;
13 import com.opensymphony.workflow.query.NestedExpression;
14 import com.opensymphony.workflow.query.WorkflowExpressionQuery;
15 import com.opensymphony.workflow.query.WorkflowQuery;
16 import com.opensymphony.workflow.spi.SimpleStep;
17 import com.opensymphony.workflow.spi.SimpleWorkflowEntry;
18 import com.opensymphony.workflow.spi.Step;
19 import com.opensymphony.workflow.spi.WorkflowEntry;
20 import com.opensymphony.workflow.spi.WorkflowStore;
21
22 import org.apache.commons.logging.Log;
23 import org.apache.commons.logging.LogFactory;
24
25 import java.sql.*;
26
27 import java.util.*;
28 import java.util.Date  ;
29
30 import javax.naming.InitialContext ;
31 import javax.naming.NamingException ;
32
33 import javax.sql.DataSource ;
34
35
36
68 public class JDBCWorkflowStore implements WorkflowStore {
69
71 private static final Log log = LogFactory.getLog(JDBCWorkflowStore.class);
72
73
75 protected DataSource ds;
76 protected String currentPrevTable;
77 protected String currentTable;
78 protected String entryId;
79 protected String entryName;
80 protected String entrySequence;
81 protected String entryState;
82 protected String entryTable;
83 protected String historyPrevTable;
84 protected String historyTable;
85 protected String stepActionId;
86 protected String stepCaller;
87 protected String stepDueDate;
88 protected String stepEntryId;
89 protected String stepFinishDate;
90 protected String stepId;
91 protected String stepOwner;
92 protected String stepPreviousId;
93 protected String stepSequence;
94 protected String stepStartDate;
95 protected String stepStatus;
96 protected String stepStepId;
97 protected boolean closeConnWhenDone = false;
98
99
101 public void setEntryState(long id, int state) throws StoreException {
102 Connection conn = null;
103 PreparedStatement ps = null;
104
105 try {
106 conn = getConnection();
107
108 String sql = "UPDATE " + entryTable + " SET " + entryState + " = ? WHERE " + entryId + " = ?";
109 ps = conn.prepareStatement(sql);
110 ps.setInt(1, state);
111 ps.setLong(2, id);
112 ps.executeUpdate();
113 } catch (SQLException e) {
114 throw new StoreException("Unable to update state for workflow instance #" + id + " to " + state, e);
115 } finally {
116 cleanup(conn, ps, null);
117 }
118 }
119
120 public PropertySet getPropertySet(long entryId) {
121 HashMap args = new HashMap(1);
122 args.put("globalKey", "osff_" + entryId);
123
124 return PropertySetManager.getInstance("jdbc", args);
125 }
126
127 public Step createCurrentStep(long entryId, int wfStepId, String owner, Date startDate, Date dueDate, String status, long[] previousIds) throws StoreException {
128 Connection conn = null;
129
130 try {
131 conn = getConnection();
132
133 long id = createCurrentStep(conn, entryId, wfStepId, owner, startDate, dueDate, status);
134 addPreviousSteps(conn, id, previousIds);
135
136 return new SimpleStep(id, entryId, wfStepId, 0, owner, startDate, dueDate, null, status, previousIds, null);
137 } catch (SQLException e) {
138 throw new StoreException("Unable to create current step for workflow instance #" + entryId, e);
139 } finally {
140 cleanup(conn, null, null);
141 }
142 }
143
144 public WorkflowEntry createEntry(String workflowName) throws StoreException {
145 Connection conn = null;
146 PreparedStatement stmt = null;
147
148 try {
149 conn = getConnection();
150
151 String sql = "INSERT INTO " + entryTable + " (" + entryId + ", " + entryName + ", " + entryState + ") VALUES (?,?,?)";
152
153 if (log.isDebugEnabled()) {
154 log.debug("Executing SQL statement: " + sql);
155 }
156
157 stmt = conn.prepareStatement(sql);
158
159 long id = getNextEntrySequence(conn);
160 stmt.setLong(1, id);
161 stmt.setString(2, workflowName);
162 stmt.setInt(3, WorkflowEntry.CREATED);
163 stmt.executeUpdate();
164
165 return new SimpleWorkflowEntry(id, workflowName, WorkflowEntry.CREATED);
166 } catch (SQLException e) {
167 throw new StoreException("Error creating new workflow instance", e);
168 } finally {
169 cleanup(conn, stmt, null);
170 }
171 }
172
173 public List findCurrentSteps(long entryId) throws StoreException {
174 Connection conn = null;
175 PreparedStatement stmt = null;
176 ResultSet rset = null;
177 PreparedStatement stmt2 = null;
178
179 try {
180 conn = getConnection();
181
182 String sql = "SELECT " + stepId + ", " + stepStepId + ", " + stepActionId + ", " + stepOwner + ", " + stepStartDate + ", " + stepDueDate + ", " + stepFinishDate + ", " + stepStatus + ", " + stepCaller + " FROM " + currentTable + " WHERE " + stepEntryId + " = ?";
183 String sql2 = "SELECT " + stepPreviousId + " FROM " + currentPrevTable + " WHERE " + stepId + " = ?";
184
185 if (log.isDebugEnabled()) {
186 log.debug("Executing SQL statement: " + sql);
187 }
188
189 stmt = conn.prepareStatement(sql);
190
191 if (log.isDebugEnabled()) {
192 log.debug("Executing SQL statement: " + sql2);
193 }
194
195 stmt2 = conn.prepareStatement(sql2);
196 stmt.setLong(1, entryId);
197
198 rset = stmt.executeQuery();
199
200 ArrayList currentSteps = new ArrayList();
201
202 while (rset.next()) {
203 long id = rset.getLong(1);
204 int stepId = rset.getInt(2);
205 int actionId = rset.getInt(3);
206 String owner = rset.getString(4);
207 Date startDate = rset.getTimestamp(5);
208 Date dueDate = rset.getTimestamp(6);
209 Date finishDate = rset.getTimestamp(7);
210 String status = rset.getString(8);
211 String caller = rset.getString(9);
212
213 ArrayList prevIdsList = new ArrayList();
214 stmt2.setLong(1, id);
215
216 ResultSet rs = stmt2.executeQuery();
217
218 while (rs.next()) {
219 long prevId = rs.getLong(1);
220 prevIdsList.add(new Long (prevId));
221 }
222
223 long[] prevIds = new long[prevIdsList.size()];
224 int i = 0;
225
226 for (Iterator iterator = prevIdsList.iterator();
227 iterator.hasNext();) {
228 Long aLong = (Long ) iterator.next();
229 prevIds[i] = aLong.longValue();
230 i++;
231 }
232
233 SimpleStep step = new SimpleStep(id, entryId, stepId, actionId, owner, startDate, dueDate, finishDate, status, prevIds, caller);
234 currentSteps.add(step);
235 }
236
237 return currentSteps;
238 } catch (SQLException e) {
239 throw new StoreException("Unable to locate current steps for workflow instance #" + entryId, e);
240 } finally {
241 cleanup(null, stmt2, null);
242 cleanup(conn, stmt, rset);
243 }
244 }
245
246 public WorkflowEntry findEntry(long theEntryId) throws StoreException {
247 Connection conn = null;
248 PreparedStatement stmt = null;
249 ResultSet rset = null;
250
251 try {
252 conn = getConnection();
253
254 String sql = "SELECT " + entryName + ", " + entryState + " FROM " + entryTable + " WHERE " + entryId + " = ?";
255
256 if (log.isDebugEnabled()) {
257 log.debug("Executing SQL statement: " + sql);
258 }
259
260 stmt = conn.prepareStatement(sql);
261 stmt.setLong(1, theEntryId);
262
263 rset = stmt.executeQuery();
264 rset.next();
265
266 String workflowName = rset.getString(1);
267 int state = rset.getInt(2);
268
269 return new SimpleWorkflowEntry(theEntryId, workflowName, state);
270 } catch (SQLException e) {
271 throw new StoreException("Error finding workflow instance #" + entryId);
272 } finally {
273 cleanup(conn, stmt, rset);
274 }
275 }
276
277 public List findHistorySteps(long entryId) throws StoreException {
278 Connection conn = null;
279 PreparedStatement stmt = null;
280 PreparedStatement stmt2 = null;
281 ResultSet rset = null;
282
283 try {
284 conn = getConnection();
285
286 String sql = "SELECT " + stepId + ", " + stepStepId + ", " + stepActionId + ", " + stepOwner + ", " + stepStartDate + ", " + stepDueDate + ", " + stepFinishDate + ", " + stepStatus + ", " + stepCaller + " FROM " + historyTable + " WHERE " + stepEntryId + " = ? ORDER BY " + stepId + " DESC";
287 String sql2 = "SELECT " + stepPreviousId + " FROM " + historyPrevTable + " WHERE " + stepId + " = ?";
288
289 if (log.isDebugEnabled()) {
290 log.debug("Executing SQL statement: " + sql);
291 }
292
293 stmt = conn.prepareStatement(sql);
294
295 if (log.isDebugEnabled()) {
296 log.debug("Executing SQL statement: " + sql2);
297 }
298
299 stmt2 = conn.prepareStatement(sql2);
300 stmt.setLong(1, entryId);
301
302 rset = stmt.executeQuery();
303
304 ArrayList currentSteps = new ArrayList();
305
306 while (rset.next()) {
307 long id = rset.getLong(1);
308 int stepId = rset.getInt(2);
309 int actionId = rset.getInt(3);
310 String owner = rset.getString(4);
311 Date startDate = rset.getTimestamp(5);
312 Date dueDate = rset.getTimestamp(6);
313 Date finishDate = rset.getTimestamp(7);
314 String status = rset.getString(8);
315 String caller = rset.getString(9);
316
317 ArrayList prevIdsList = new ArrayList();
318 stmt2.setLong(1, id);
319
320 ResultSet rs = stmt2.executeQuery();
321
322 while (rs.next()) {
323 long prevId = rs.getLong(1);
324 prevIdsList.add(new Long (prevId));
325 }
326
327 long[] prevIds = new long[prevIdsList.size()];
328 int i = 0;
329
330 for (Iterator iterator = prevIdsList.iterator();
331 iterator.hasNext();) {
332 Long aLong = (Long ) iterator.next();
333 prevIds[i] = aLong.longValue();
334 i++;
335 }
336
337 SimpleStep step = new SimpleStep(id, entryId, stepId, actionId, owner, startDate, dueDate, finishDate, status, prevIds, caller);
338 currentSteps.add(step);
339 }
340
341 return currentSteps;
342 } catch (SQLException e) {
343 throw new StoreException("Unable to locate history steps for workflow instance #" + entryId, e);
344 } finally {
345 cleanup(null, stmt2, null);
346 cleanup(conn, stmt, rset);
347 }
348 }
349
350 public void init(Map props) throws StoreException {
351 entrySequence = getInitProperty(props, "entry.sequence", "SELECT nextVal('seq_os_wfentry')");
352 stepSequence = getInitProperty(props, "step.sequence", "SELECT nextVal('seq_os_currentsteps')");
353 entryTable = getInitProperty(props, "entry.table", "OS_WFENTRY");
354 entryId = getInitProperty(props, "entry.id", "ID");
355 entryName = getInitProperty(props, "entry.name", "NAME");
356 entryState = getInitProperty(props, "entry.state", "STATE");
357 historyTable = getInitProperty(props, "history.table", "OS_HISTORYSTEP");
358 currentTable = getInitProperty(props, "current.table", "OS_CURRENTSTEP");
359 currentPrevTable = getInitProperty(props, "currentPrev.table", "OS_CURRENTSTEP_PREV");
360 historyPrevTable = getInitProperty(props, "historyPrev.table", "OS_HISTORYSTEP_PREV");
361 stepId = getInitProperty(props, "step.id", "ID");
362 stepEntryId = getInitProperty(props, "step.entryId", "ENTRY_ID");
363 stepStepId = getInitProperty(props, "step.stepId", "STEP_ID");
364 stepActionId = getInitProperty(props, "step.actionId", "ACTION_ID");
365 stepOwner = getInitProperty(props, "step.owner", "OWNER");
366 stepCaller = getInitProperty(props, "step.caller", "CALLER");
367 stepStartDate = getInitProperty(props, "step.startDate", "START_DATE");
368 stepFinishDate = getInitProperty(props, "step.finishDate", "FINISH_DATE");
369 stepDueDate = getInitProperty(props, "step.dueDate", "DUE_DATE");
370 stepStatus = getInitProperty(props, "step.status", "STATUS");
371 stepPreviousId = getInitProperty(props, "step.previousId", "PREVIOUS_ID");
372
373 String jndi = (String ) props.get("datasource");
374
375 if (jndi != null) {
376 try {
377 ds = (DataSource ) lookup(jndi);
378
379 if (ds == null) {
380 ds = (DataSource ) new javax.naming.InitialContext ().lookup(jndi);
381 }
382 } catch (Exception e) {
383 throw new StoreException("Error looking up DataSource at " + jndi, e);
384 }
385 }
386 }
387
388 public Step markFinished(Step step, int actionId, Date finishDate, String status, String caller) throws StoreException {
389 Connection conn = null;
390 PreparedStatement stmt = null;
391
392 try {
393 conn = getConnection();
394
395 String sql = "UPDATE " + currentTable + " SET " + stepStatus + " = ?, " + stepActionId + " = ?, " + stepFinishDate + " = ?, " + stepCaller + " = ? WHERE " + stepId + " = ?";
396
397 if (log.isDebugEnabled()) {
398 log.debug("Executing SQL statement: " + sql);
399 }
400
401 stmt = conn.prepareStatement(sql);
402 stmt.setString(1, status);
403 stmt.setInt(2, actionId);
404 stmt.setTimestamp(3, new Timestamp(finishDate.getTime()));
405 stmt.setString(4, caller);
406 stmt.setLong(5, step.getId());
407 stmt.executeUpdate();
408
409 SimpleStep theStep = (SimpleStep) step;
410 theStep.setActionId(actionId);
411 theStep.setFinishDate(finishDate);
412 theStep.setStatus(status);
413 theStep.setCaller(caller);
414
415 return theStep;
416 } catch (SQLException e) {
417 throw new StoreException("Unable to mark step finished for #" + step.getEntryId(), e);
418 } finally {
419 cleanup(conn, stmt, null);
420 }
421 }
422
423 public void moveToHistory(Step step) throws StoreException {
424 Connection conn = null;
425 PreparedStatement stmt = null;
426
427 try {
428 conn = getConnection();
429
430 String sql = "INSERT INTO " + historyTable + " (" + stepId + "," + stepEntryId + ", " + stepStepId + ", " + stepActionId + ", " + stepOwner + ", " + stepStartDate + ", " + stepFinishDate + ", " + stepStatus + ", " + stepCaller + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
431
432 if (log.isDebugEnabled()) {
433 log.debug("Executing SQL statement: " + sql);
434 }
435
436 stmt = conn.prepareStatement(sql);
437 stmt.setLong(1, step.getId());
438 stmt.setLong(2, step.getEntryId());
439 stmt.setInt(3, step.getStepId());
440 stmt.setInt(4, step.getActionId());
441 stmt.setString(5, step.getOwner());
442 stmt.setTimestamp(6, new Timestamp(step.getStartDate().getTime()));
443
444 if (step.getFinishDate() != null) {
445 stmt.setTimestamp(7, new Timestamp(step.getFinishDate().getTime()));
446 } else {
447 stmt.setNull(7, Types.TIMESTAMP);
448 }
449
450 stmt.setString(8, step.getStatus());
451 stmt.setString(9, step.getCaller());
452 stmt.executeUpdate();
453
454 long[] previousIds = step.getPreviousStepIds();
455
456 if ((previousIds != null) && (previousIds.length > 0)) {
457 sql = "INSERT INTO " + historyPrevTable + " (" + stepId + ", " + stepPreviousId + ") VALUES (?, ?)";
458 log.debug("Executing SQL statement: " + sql);
459 cleanup(null, stmt, null);
460 stmt = conn.prepareStatement(sql);
461
462 for (int i = 0; i < previousIds.length; i++) {
463 long previousId = previousIds[i];
464 stmt.setLong(1, step.getId());
465 stmt.setLong(2, previousId);
466 stmt.executeUpdate();
467 }
468 }
469
470 sql = "DELETE FROM " + currentPrevTable + " WHERE " + stepId + " = ?";
471
472 if (log.isDebugEnabled()) {
473 log.debug("Executing SQL statement: " + sql);
474 }
475
476 cleanup(null, stmt, null);
477 stmt = conn.prepareStatement(sql);
478 stmt.setLong(1, step.getId());
479 stmt.executeUpdate();
480
481 sql = "DELETE FROM " + currentTable + " WHERE " + stepId + " = ?";
482
483 if (log.isDebugEnabled()) {
484 log.debug("Executing SQL statement: " + sql);
485 }
486
487 cleanup(null, stmt, null);
488 stmt = conn.prepareStatement(sql);
489 stmt.setLong(1, step.getId());
490 stmt.executeUpdate();
491 } catch (SQLException e) {
492 throw new StoreException("Unable to move current step to history step for #" + step.getEntryId(), e);
493 } finally {
494 cleanup(conn, stmt, null);
495 }
496 }
497
498 public List query(WorkflowExpressionQuery query) throws StoreException {
499 Expression expression = query.getExpression();
500
501 StringBuffer sel = new StringBuffer ();
502 List values = new ArrayList();
503 String columnName = null;
504
505 if (expression.isNested()) {
506 columnName = buildNested((NestedExpression) expression, sel, values);
507 } else {
508 columnName = buildSimple((FieldExpression) expression, sel, values);
509 }
510
511 if (query.getSortOrder() != WorkflowExpressionQuery.SORT_NONE) {
512 sel.append(" ORDER BY ");
513 sel.append(fieldName(query.getOrderBy()));
514
515 if (query.getSortOrder() == WorkflowExpressionQuery.SORT_ASC) {
516 sel.append(" ASC");
517 } else {
518 sel.append(" DESC");
519 }
520 }
521
522 List results = doExpressionQuery(sel.toString(), columnName, values);
523
524 return results;
525 }
526
527 public List query(WorkflowQuery query) throws StoreException {
528 List results = new ArrayList();
529
530 String sel;
532 String table;
533
534 int qtype = query.getType();
535
536 if (qtype == 0) {
540 if (query.getLeft() != null) {
541 qtype = query.getLeft().getType();
542 }
543 }
544
545 if (qtype == WorkflowQuery.CURRENT) {
546 table = currentTable;
547 } else {
548 table = historyTable;
549 }
550
551 sel = "SELECT DISTINCT(" + stepEntryId + ") FROM " + table + " WHERE ";
552 sel += queryWhere(query);
553
554 if (log.isDebugEnabled()) {
555 log.debug(sel);
556 }
557
558 Connection conn = null;
559 Statement stmt = null;
560 ResultSet rs = null;
561
562 try {
563 conn = getConnection();
564 stmt = conn.createStatement();
565 rs = stmt.executeQuery(sel);
566
567 while (rs.next()) {
568 Long id = new Long (rs.getLong(stepEntryId));
570 results.add(id);
571 }
572 } catch (SQLException ex) {
573 throw new StoreException("SQL Exception in query: " + ex.getMessage());
574 } finally {
575 cleanup(conn, stmt, rs);
576 }
577
578 return results;
579 }
580
581 protected Connection getConnection() throws SQLException {
582 closeConnWhenDone = true;
583
584 return ds.getConnection();
585 }
586
587 protected long getNextEntrySequence(Connection c) throws SQLException {
588 if (log.isDebugEnabled()) {
589 log.debug("Executing SQL statement: " + entrySequence);
590 }
591
592 PreparedStatement stmt = null;
593 ResultSet rset = null;
594
595 try {
596 stmt = c.prepareStatement(entrySequence);
597 rset = stmt.executeQuery();
598 rset.next();
599
600 long id = rset.getLong(1);
601
602 return id;
603 } finally {
604 cleanup(null, stmt, rset);
605 }
606 }
607
608 protected long getNextStepSequence(Connection c) throws SQLException {
609 if (log.isDebugEnabled()) {
610 log.debug("Executing SQL statement: " + stepSequence);
611 }
612
613 PreparedStatement stmt = null;
614 ResultSet rset = null;
615
616 try {
617 stmt = c.prepareStatement(stepSequence);
618 rset = stmt.executeQuery();
619 rset.next();
620
621 long id = rset.getLong(1);
622
623 return id;
624 } finally {
625 cleanup(null, stmt, rset);
626 }
627 }
628
629 protected void addPreviousSteps(Connection conn, long id, long[] previousIds) throws SQLException {
630 if ((previousIds != null) && (previousIds.length > 0)) {
631 if (!((previousIds.length == 1) && (previousIds[0] == 0))) {
632 String sql = "INSERT INTO " + currentPrevTable + " (" + stepId + ", " + stepPreviousId + ") VALUES (?, ?)";
633 log.debug("Executing SQL statement: " + sql);
634
635 PreparedStatement stmt = conn.prepareStatement(sql);
636
637 for (int i = 0; i < previousIds.length; i++) {
638 long previousId = previousIds[i];
639 stmt.setLong(1, id);
640 stmt.setLong(2, previousId);
641 stmt.executeUpdate();
642 }
643
644 cleanup(null, stmt, null);
645 }
646 }
647 }
648
649 protected void cleanup(Connection connection, Statement statement, ResultSet result) {
650 if (result != null) {
651 try {
652 result.close();
653 } catch (SQLException ex) {
654 log.error("Error closing resultset", ex);
655 }
656 }
657
658 if (statement != null) {
659 try {
660 statement.close();
661 } catch (SQLException ex) {
662 log.error("Error closing statement", ex);
663 }
664 }
665
666 if ((connection != null) && closeConnWhenDone) {
667 try {
668 connection.close();
669 } catch (SQLException ex) {
670 log.error("Error closing connection", ex);
671 }
672 }
673 }
674
675 protected long createCurrentStep(Connection conn, long entryId, int wfStepId, String owner, Date startDate, Date dueDate, String status) throws SQLException {
676 String sql = "INSERT INTO " + currentTable + " (" + stepId + "," + stepEntryId + ", " + stepStepId + ", " + stepActionId + ", " + stepOwner + ", " + stepStartDate + ", " + stepDueDate + ", " + stepFinishDate + ", " + stepStatus + ", " + stepCaller + " ) VALUES (?, ?, ?, null, ?, ?, ?, null, ?, null)";
677
678 if (log.isDebugEnabled()) {
679 log.debug("Executing SQL statement: " + sql);
680 }
681
682 PreparedStatement stmt = conn.prepareStatement(sql);
683
684 long id = getNextStepSequence(conn);
685 stmt.setLong(1, id);
686 stmt.setLong(2, entryId);
687 stmt.setInt(3, wfStepId);
688 stmt.setString(4, owner);
689 stmt.setTimestamp(5, new Timestamp(startDate.getTime()));
690
691 if (dueDate != null) {
692 stmt.setTimestamp(6, new Timestamp(dueDate.getTime()));
693 } else {
694 stmt.setNull(6, Types.TIMESTAMP);
695 }
696
697 stmt.setString(7, status);
698 stmt.executeUpdate();
699 cleanup(null, stmt, null);
700
701 return id;
702 }
703
704 private String getInitProperty(Map props, String strName, String strDefault) {
705 Object o = props.get(strName);
706
707 if (o == null) {
708 return strDefault;
709 }
710
711 return (String ) o;
712 }
713
714 private String buildNested(NestedExpression nestedExpression, StringBuffer sel, List values) {
715 sel.append("SELECT DISTINCT(");
716 sel.append(entryId);
717 sel.append(") FROM ");
718 sel.append(entryTable);
719
720 for (int i = 0; i < nestedExpression.getExpressionCount(); i++) {
721 Expression expression = nestedExpression.getExpression(i);
722
723 if (i == 0) {
724 sel.append(" WHERE ");
725 } else {
726 if (nestedExpression.getExpressionOperator() == NestedExpression.AND) {
727 sel.append(" AND ");
728 } else {
729 sel.append(" OR ");
730 }
731 }
732
733 if (expression.isNegate()) {
734 sel.append(" NOT ");
735 }
736
737 sel.append(entryId);
738 sel.append(" IN (");
739
740 if (expression.isNested()) {
741 this.buildNested((NestedExpression) nestedExpression.getExpression(i), sel, values);
742 } else {
743 FieldExpression sub = (FieldExpression) nestedExpression.getExpression(i);
744 this.buildSimple(sub, sel, values);
745 }
746
747 sel.append(")");
748 }
749
750 return (entryId);
751 }
752
753 private String buildSimple(FieldExpression fieldExpression, StringBuffer sel, List values) {
754 String table;
755 String columnName;
756
757 if (fieldExpression.getContext() == FieldExpression.CURRENT_STEPS) {
758 table = currentTable;
759 columnName = stepEntryId;
760 } else if (fieldExpression.getContext() == FieldExpression.HISTORY_STEPS) {
761 table = historyTable;
762 columnName = stepEntryId;
763 } else {
764 table = entryTable;
765 columnName = entryId;
766 }
767
768 sel.append("SELECT DISTINCT(");
769 sel.append(columnName);
770 sel.append(") FROM ");
771 sel.append(table);
772 sel.append(" WHERE ");
773 queryComparison(fieldExpression, sel, values);
774
775 return columnName;
776 }
777
778 private List doExpressionQuery(String sel, String columnName, List values) throws StoreException {
779 if (log.isDebugEnabled()) {
780 log.debug(sel);
781 }
782
783 Connection conn = null;
784 PreparedStatement stmt = null;
785 ResultSet rs = null;
786 List results = new ArrayList();
787
788 try {
789 conn = getConnection();
790 stmt = conn.prepareStatement(sel);
791
792 if (!values.isEmpty()) {
793 for (int i = 1; i <= values.size(); i++) {
794 stmt.setObject(i, values.get(i - 1));
795 }
796 }
797
798 rs = stmt.executeQuery();
799
800 while (rs.next()) {
801 Long id = new Long (rs.getLong(columnName));
803 results.add(id);
804 }
805
806 return results;
807 } catch (SQLException ex) {
808 throw new StoreException("SQL Exception in query: " + ex.getMessage());
809 } finally {
810 cleanup(conn, stmt, rs);
811 }
812 }
813
814 private static String escape(String s) {
815 StringBuffer sb = new StringBuffer (s);
816
817 char c;
818 char[] chars = s.toCharArray();
819
820 for (int i = 0; i < chars.length; i++) {
821 c = chars[i];
822
823 switch (c) {
824 case '\'':
825 sb.insert(i, '\'');
826 i++;
827
828 break;
829
830 case '\\':
831 sb.insert(i, '\\');
832 i++;
833 }
834 }
835
836 return sb.toString();
837 }
838
839 private String fieldName(int field) {
840 switch (field) {
841 case FieldExpression.ACTION: return stepActionId;
843
844 case FieldExpression.CALLER:
845 return stepCaller;
846
847 case FieldExpression.FINISH_DATE:
848 return stepFinishDate;
849
850 case FieldExpression.OWNER:
851 return stepOwner;
852
853 case FieldExpression.START_DATE:
854 return stepStartDate;
855
856 case FieldExpression.STEP: return stepStepId;
858
859 case FieldExpression.STATUS:
860 return stepStatus;
861
862 case FieldExpression.STATE:
863 return entryState;
864
865 case FieldExpression.NAME:
866 return entryName;
867
868 default:
869 return "1";
870 }
871 }
872
873 private Object lookup(String location) throws NamingException {
874 try {
875 InitialContext context = new InitialContext ();
876
877 try {
878 return context.lookup(location);
879 } catch (NamingException e) {
880 return context.lookup("java:comp/env/" + location);
882 }
883 } catch (NamingException e) {
884 throw e;
885 }
886 }
887
888 private String queryComparison(WorkflowQuery query) {
889 Object value = query.getValue();
890 int operator = query.getOperator();
891 int field = query.getField();
892
893 String oper;
895
896 switch (operator) {
897 case WorkflowQuery.EQUALS:
898 oper = " = ";
899
900 break;
901
902 case WorkflowQuery.NOT_EQUALS:
903 oper = " <> ";
904
905 break;
906
907 case WorkflowQuery.GT:
908 oper = " > ";
909
910 break;
911
912 case WorkflowQuery.LT:
913 oper = " < ";
914
915 break;
916
917 default:
918 oper = " = ";
919 }
920
921 String left;
922 String right;
923
924 switch (field) {
925 case WorkflowQuery.ACTION: left = stepActionId;
927
928 break;
929
930 case WorkflowQuery.CALLER:
931 left = stepCaller;
932
933 break;
934
935 case WorkflowQuery.FINISH_DATE:
936 left = stepFinishDate;
937
938 break;
939
940 case WorkflowQuery.OWNER:
941 left = stepOwner;
942
943 break;
944
945 case WorkflowQuery.START_DATE:
946 left = stepStartDate;
947
948 break;
949
950 case WorkflowQuery.STEP: left = stepStepId;
952
953 break;
954
955 case WorkflowQuery.STATUS:
956 left = stepStatus;
957
958 break;
959
960 default:
961 left = "1";
962 }
963
964 if (value != null) {
965 right = "'" + escape(value.toString()) + "'";
966 } else {
967 right = "null";
968 }
969
970 return left + oper + right;
971 }
972
973
980 private void queryComparison(FieldExpression expression, StringBuffer sel, List values) {
981 Object value = expression.getValue();
982 int operator = expression.getOperator();
983 int field = expression.getField();
984
985 String oper;
986
987 switch (operator) {
988 case FieldExpression.EQUALS:
989
990 if (value == null) {
991 oper = " IS ";
992 } else {
993 oper = " = ";
994 }
995
996 break;
997
998 case FieldExpression.NOT_EQUALS:
999
1000 if (value == null) {
1001 oper = " IS NOT ";
1002 } else {
1003 oper = " <> ";
1004 }
1005
1006 break;
1007
1008 case FieldExpression.GT:
1009 oper = " > ";
1010
1011 break;
1012
1013 case FieldExpression.LT:
1014 oper = " < ";
1015
1016 break;
1017
1018 default:
1019 oper = " = ";
1020 }
1021
1022 String left = fieldName(field);
1023 String right = "?";
1024
1025 switch (field) {
1026 case FieldExpression.FINISH_DATE:
1027 values.add(new Timestamp(((Date ) value).getTime()));
1028
1029 break;
1030
1031 case FieldExpression.START_DATE:
1032 values.add(new Timestamp(((Date ) value).getTime()));
1033
1034 break;
1035
1036 default:
1037
1038 if (value == null) {
1039 right = "null";
1040 } else {
1041 values.add(value);
1042 }
1043 }
1044
1045 sel.append(left);
1046 sel.append(oper);
1047 sel.append(right);
1048 }
1049
1050 private String queryWhere(WorkflowQuery query) {
1051 if (query.getLeft() == null) {
1052 return queryComparison(query);
1054 } else {
1055 int operator = query.getOperator();
1056 WorkflowQuery left = query.getLeft();
1057 WorkflowQuery right = query.getRight();
1058
1059 switch (operator) {
1060 case WorkflowQuery.AND:
1061 return "(" + queryWhere(left) + " AND " + queryWhere(right) + ")";
1062
1063 case WorkflowQuery.OR:
1064 return "(" + queryWhere(left) + " OR " + queryWhere(right) + ")";
1065
1066 case WorkflowQuery.XOR:
1067 return "(" + queryWhere(left) + " XOR " + queryWhere(right) + ")";
1068 }
1069 }
1070
1071 return ""; }
1073}
1074 |
Popular Tags |
Java API By Example, From Geeks To Geeks.