|
1
23
24 package org.infoglue.cms.util.workflow;
25
26 import java.sql.Connection  ;
27 import java.sql.DriverManager ;
28 import java.sql.PreparedStatement ;
29 import java.sql.ResultSet ;
30 import java.sql.SQLException ;
31 import java.sql.Statement ;
32 import java.sql.Timestamp ;
33 import java.sql.Types ;
34 import java.util.ArrayList ;
35 import java.util.Date ;
36 import java.util.Iterator ;
37 import java.util.List ;
38 import java.util.Map ;
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
82 public class InfoGlueJDBCWorkflowStore extends JDBCWorkflowStore
83 {
84 private final static Logger logger = Logger.getLogger(InfoGlueJDBCWorkflowStore.class.getName());
85
86
112 private String userName;
113 private String password;
114 private String driverClassName;
115 private String url;
116
117 public void init(Map 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
149 }
150
151 protected Connection getConnection() throws SQLException
152 {
153 closeConnWhenDone = true;
154
155 Connection 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 ex)
165 {
166 ex.printStackTrace();
167 }
168
169 return conn;
170 }
171
172 public void setEntryState(long id, int state) throws StoreException {
173 Connection conn = null;
174 PreparedStatement ps = null;
175
176 try {
177 conn = getConnection();
178
179 String 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 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 owner, Date startDate, Date dueDate, String status, long[] previousIds) throws StoreException {
192 Connection 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 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 workflowName) throws StoreException {
209 Connection conn = null;
210 PreparedStatement stmt = null;
211
212 try {
213 conn = getConnection();
214
215 String 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 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 findCurrentSteps(long entryId) throws StoreException {
239 Connection conn = null;
240 PreparedStatement stmt = null;
241 ResultSet rset = null;
242 PreparedStatement stmt2 = null;
243
244 try {
245 conn = getConnection();
246
247 String sql = "SELECT " + stepId + ", " + stepStepId + ", " + stepActionId + ", " + stepOwner + ", " + stepStartDate + ", " + stepDueDate + ", " + stepFinishDate + ", " + stepStatus + ", " + stepCaller + " FROM " + currentTable + " WHERE " + stepEntryId + " = ?";
248 String 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 currentSteps = new ArrayList ();
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 owner = rset.getString(4);
272 Date startDate = rset.getTimestamp(5);
273 Date dueDate = rset.getTimestamp(6);
274 Date finishDate = rset.getTimestamp(7);
275 String status = rset.getString(8);
276 String caller = rset.getString(9);
277
278 ArrayList prevIdsList = new ArrayList ();
279 stmt2.setLong(1, id);
280
281 ResultSet rs = stmt2.executeQuery();
282
283 while (rs.next()) {
284 long prevId = rs.getLong(1);
285 prevIdsList.add(new Long (prevId));
286 }
287
288 long[] prevIds = new long[prevIdsList.size()];
289 int i = 0;
290
291 for (Iterator iterator = prevIdsList.iterator();
292 iterator.hasNext();) {
293 Long aLong = (Long ) 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 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 conn = null;
313 PreparedStatement stmt = null;
314 ResultSet rset = null;
315
316 try {
317 conn = getConnection();
318
319 String 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 workflowName = rset.getString(1);
332 int state = rset.getInt(2);
333
334 return new SimpleWorkflowEntry(theEntryId, workflowName, state);
335 } catch (SQLException e) {
336 throw new StoreException("Error finding workflow instance #" + entryId);
337 } finally {
338 cleanup(conn, stmt, rset);
339 }
340 }
341
342 public List findHistorySteps(long entryId) throws StoreException {
343 Connection conn = null;
344 PreparedStatement stmt = null;
345 PreparedStatement stmt2 = null;
346 ResultSet rset = null;
347
348 try {
349 conn = getConnection();
350
351 String sql = "SELECT " + stepId + ", " + stepStepId + ", " + stepActionId + ", " + stepOwner + ", " + stepStartDate + ", " + stepDueDate + ", " + stepFinishDate + ", " + stepStatus + ", " + stepCaller + " FROM " + historyTable + " WHERE " + stepEntryId + " = ? ORDER BY " + stepId + " DESC";
352 String 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 currentSteps = new ArrayList ();
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 owner = rset.getString(4);
376 Date startDate = rset.getTimestamp(5);
377 Date dueDate = rset.getTimestamp(6);
378 Date finishDate = rset.getTimestamp(7);
379 String status = rset.getString(8);
380 String caller = rset.getString(9);
381
382 ArrayList prevIdsList = new ArrayList ();
383 stmt2.setLong(1, id);
384
385 ResultSet rs = stmt2.executeQuery();
386
387 while (rs.next()) {
388 long prevId = rs.getLong(1);
389 prevIdsList.add(new Long (prevId));
390 }
391
392 long[] prevIds = new long[prevIdsList.size()];
393 int i = 0;
394
395 for (Iterator iterator = prevIdsList.iterator();
396 iterator.hasNext();) {
397 Long aLong = (Long ) 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 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 finishDate, String status, String caller) throws StoreException
416 {
417 Connection conn = null;
418 PreparedStatement stmt = null;
419
420 try
421 {
422 conn = getConnection();
423
424 String 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 (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 (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 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 conn = null;
467 PreparedStatement stmt = null;
468
469 try
470 {
471 conn = getConnection();
472
473 String 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 (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 (step.getStartDate().getTime()));
495
496 if (step.getFinishDate() != null)
497 {
498 stmt.setTimestamp(7, new Timestamp (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 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 query(WorkflowQuery query) throws StoreException
562 {
563 List results = new ArrayList ();
564
565 String sel;
567 String table;
568
569 int qtype = query.getType();
570
571 if (qtype == 0) {
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 conn = null;
595 Statement stmt = null;
596 ResultSet rs = null;
597
598 try {
599 conn = getConnection();
600 stmt = conn.createStatement();
601 rs = stmt.executeQuery(sel);
602
603 while (rs.next()) {
604 Long id = new Long (rs.getLong(stepEntryId));
606 results.add(id);
607 }
608 } catch (SQLException 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 doExpressionQuery(String sel, String columnName, List values) throws StoreException {
619 if (logger.isDebugEnabled()) {
620 logger.debug(sel);
621 }
622
623 Connection conn = null;
624 PreparedStatement stmt = null;
625 ResultSet rs = null;
626 List results = new ArrayList ();
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 Long id = new Long (rs.getLong(columnName));
643 results.add(id);
644 }
645
646 return results;
647 } catch (SQLException ex) {
648 throw new StoreException("SQL Exception in query: " + ex.getMessage());
649 } finally {
650 cleanup(conn, stmt, rs);
651 }
652 }
653
654 private String getInitProperty(Map props, String strName, String strDefault)
655 {
656 Object o = props.get(strName);
657
658 if (o == null) {
659 return strDefault;
660 }
661
662 return (String ) o;
663 }
664
665 private String queryWhere(WorkflowQuery query) {
666 if (query.getLeft() == null) {
667 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 ""; }
688
689 private String queryComparison(WorkflowQuery query) {
690 Object value = query.getValue();
691 int operator = query.getOperator();
692 int field = query.getField();
693
694 String 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 left;
723 String right;
724
725 switch (field) {
726 case WorkflowQuery.ACTION: 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: 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 escape(String s) {
775 StringBuffer sb = new StringBuffer (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 |
Java API By Example, From Geeks To Geeks.