|
1
16 package nl.hippo.osworkflow;
17
18 import com.opensymphony.workflow.StoreException;
19 import com.opensymphony.workflow.query.Expression;
20 import com.opensymphony.workflow.query.FieldExpression;
21 import com.opensymphony.workflow.query.NestedExpression;
22 import com.opensymphony.workflow.query.WorkflowExpressionQuery;
23 import com.opensymphony.workflow.spi.jdbc.MySQLWorkflowStore;
24 import java.sql.Connection  ;
25 import java.sql.PreparedStatement ;
26 import java.sql.ResultSet ;
27 import java.sql.SQLException ;
28 import java.sql.Timestamp ;
29 import java.util.ArrayList ;
30 import java.util.Date ;
31 import java.util.Iterator ;
32 import java.util.List ;
33 import java.util.Map ;
34
35
36
41 public class MySQLJoinQueriesWorkflowStore extends MySQLWorkflowStore {
42
43 private static final String JOINED_TABLE_PREFIX = "T";
44
45 private boolean m_useSubqueries;
46
47 public MySQLJoinQueriesWorkflowStore() {
48 super();
49 }
50
51 public void init(Map properties) throws StoreException {
52 super.init(properties);
53 String useSubqueries = (String ) properties.get("sql.useSubqueries");
54 m_useSubqueries = useSubqueries != null && useSubqueries.equals("true");
55 }
56
57 public boolean useSubqueries()
58 {
59 return m_useSubqueries;
60 }
61
62 public List query(WorkflowExpressionQuery query) throws StoreException
63 {
64 List result;
65 if (useSubqueries())
66 {
67 result = super.query(query);
68 }
69 else
70 {
71 if (query.getSortOrder() != WorkflowExpressionQuery.SORT_NONE)
72 {
73 throw new StoreException("Unsupported sort order for query: " + query.getSortOrder());
74 }
75
76 Expression expression = query.getExpression();
77
78 List joinedTables = new ArrayList ();
80 List values = new ArrayList ();
81 StringBuffer condition = new StringBuffer ();
82 if (expression.isNested())
83 {
84 buildCondition((NestedExpression) expression, condition, joinedTables, values);
85 }
86 else
87 {
88 buildCondition((FieldExpression) expression, condition, joinedTables, values);
89 }
90
91 StringBuffer sqlQuery = new StringBuffer ();
92 sqlQuery.append("SELECT ");
93 sqlQuery.append(entryTable);
94 sqlQuery.append('.');
95 sqlQuery.append(entryId);
96 sqlQuery.append(" FROM ");
97 sqlQuery.append(entryTable);
98
99 int tableIndex = 1;
100 for (Iterator joinedTablesIterator = joinedTables.iterator(); joinedTablesIterator
101 .hasNext();)
102 {
103 Integer context = (Integer ) joinedTablesIterator.next();
104 String joinedTableName;
105 String joinedTableIdColumnName;
106 switch (context.intValue())
107 {
108 case FieldExpression.CURRENT_STEPS :
109 joinedTableName = currentTable;
110 joinedTableIdColumnName = stepEntryId;
111 break;
112 case FieldExpression.ENTRY :
113 joinedTableName = entryTable;
114 joinedTableIdColumnName = entryId;
115 break;
116 case FieldExpression.HISTORY_STEPS :
117 joinedTableName = historyTable;
118 joinedTableIdColumnName = stepEntryId;
119 break;
120 default :
121 throw new StoreException("Unsupported context for field expression: "
122 + context);
123 }
124
125 sqlQuery.append(" LEFT JOIN ");
126 sqlQuery.append(joinedTableName);
127 sqlQuery.append(" AS ");
128 sqlQuery.append(JOINED_TABLE_PREFIX);
129 sqlQuery.append(tableIndex);
130 sqlQuery.append(" ON ");
131 sqlQuery.append(entryTable);
132 sqlQuery.append('.');
133 sqlQuery.append(entryId);
134 sqlQuery.append(" = ");
135 sqlQuery.append(JOINED_TABLE_PREFIX);
136 sqlQuery.append(tableIndex);
137 sqlQuery.append('.');
138 sqlQuery.append(joinedTableIdColumnName);
139
140 tableIndex++;
141 }
142
143 sqlQuery.append(" WHERE ");
144 sqlQuery.append(condition);
145
146 result = executeQuery(sqlQuery.toString(), entryId, values);
147 }
148 return result;
149 }
150
151 private void buildCondition(NestedExpression nestedExpression, StringBuffer condition,
152 List joinedTables, List values) throws StoreException
153 {
154 condition.append('(');
155
156 for (int i = 0; i < nestedExpression.getExpressionCount(); i++)
157 {
158 Expression expression = nestedExpression.getExpression(i);
159
160 if (i > 0)
161 {
162 if (nestedExpression.getExpressionOperator() == NestedExpression.AND)
163 {
164 condition.append(" AND ");
165 }
166 else if (nestedExpression.getExpressionOperator() == NestedExpression.AND)
167 {
168 condition.append(" OR ");
169 }
170 else
171 {
172 throw new StoreException("Unsupported operator for nested expression: "
173 + nestedExpression.getExpressionOperator());
174 }
175 }
176
177 if (expression.isNested())
178 {
179 this.buildCondition((NestedExpression) nestedExpression.getExpression(i),
180 condition, joinedTables, values);
181 }
182 else
183 {
184 this.buildCondition((FieldExpression) nestedExpression.getExpression(i), condition,
185 joinedTables, values);
186 }
187 }
188
189 condition.append(')');
190 }
191
192 private void buildCondition(FieldExpression expression, StringBuffer condition,
193 List joinedTables, List values) throws StoreException
194 {
195 joinedTables.add(new Integer (expression.getContext()));
196 String tableName = JOINED_TABLE_PREFIX + joinedTables.size();
197
198 if (expression.isNegate())
199 {
200 condition.append("NOT ");
201 }
202
203 condition.append('(');
204
205 condition.append(tableName);
206 condition.append('.');
207 condition.append(getFieldName(expression.getField()));
208
209 condition.append(' ');
210
211 condition.append(getOperator(expression.getOperator(), expression.getValue()));
212
213 condition.append(' ');
214
215 String rightHandSide = "?";
216 Object value = expression.getValue();
217 if (isDateField(expression.getField()))
218 {
219 values.add(new Timestamp (((Date ) value).getTime()));
220 }
221 else
222 {
223 if (value == null)
224 {
225 rightHandSide = "null";
226 }
227 else
228 {
229 values.add(value);
230 }
231 }
232 condition.append(rightHandSide);
233
234 condition.append(')');
235 }
236
237 private String getFieldName(int field) throws StoreException
238 {
239 switch (field)
240 {
241 case FieldExpression.ACTION : return stepActionId;
243 case FieldExpression.CALLER :
244 return stepCaller;
245 case FieldExpression.FINISH_DATE :
246 return stepFinishDate;
247 case FieldExpression.OWNER :
248 return stepOwner;
249 case FieldExpression.START_DATE :
250 return stepStartDate;
251 case FieldExpression.STEP : return stepStepId;
253 case FieldExpression.STATUS :
254 return stepStatus;
255 case FieldExpression.STATE :
256 return entryState;
257 case FieldExpression.NAME :
258 return entryName;
259 default :
260 throw new StoreException("Unsupported field for field expression: " + field);
261 }
262 }
263
264 private String getOperator(int operatorAsInteger, Object value) throws StoreException
265 {
266 String result;
267 switch (operatorAsInteger)
268 {
269 case FieldExpression.EQUALS :
270 if (value == null)
271 {
272 result = "IS";
273 }
274 else
275 {
276 result = "=";
277 }
278 break;
279 case FieldExpression.NOT_EQUALS :
280 if (value == null)
281 {
282 result = "IS NOT";
283 }
284 else
285 {
286 result = "<>";
287 }
288 break;
289 case FieldExpression.GT :
290 result = ">";
291 break;
292 case FieldExpression.LT :
293 result = "<";
294 break;
295 default :
296 throw new StoreException("Unsupported operator for field expression: "
297 + operatorAsInteger);
298 }
299 return result;
300 }
301
302 private List executeQuery(String query, String columnName, List values) throws StoreException
303 {
304 Connection conn = null;
305 PreparedStatement stmt = null;
306 ResultSet rs = null;
307 List results = new ArrayList ();
308
309 try
310 {
311 conn = getConnection();
312 stmt = conn.prepareStatement(query);
313
314 for (int i = 1; i <= values.size(); i++)
315 {
316 stmt.setObject(i, values.get(i - 1));
317 }
318
319 rs = stmt.executeQuery();
320
321 while (rs.next())
322 {
323 Long id = new Long (rs.getLong(columnName));
325 results.add(id);
326 }
327
328 return results;
329 }
330 catch (SQLException ex)
331 {
332 throw new StoreException("SQL Exception in query: " + ex.getMessage());
333 }
334 finally
335 {
336 cleanup(conn, stmt, rs);
337 }
338 }
339
340 private boolean isDateField(int field)
341 {
342 return field == FieldExpression.FINISH_DATE || field == FieldExpression.START_DATE;
343 }
344
345 } |
Popular Tags |
Java API By Example, From Geeks To Geeks.