1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package org.apache.juddi.datastore.jdbc;
17
18 import java.sql.Connection;
19 import java.sql.PreparedStatement;
20 import java.sql.ResultSet;
21 import java.util.Vector;
22
23 import org.apache.commons.logging.Log;
24 import org.apache.commons.logging.LogFactory;
25 import org.apache.juddi.datatype.CategoryBag;
26 import org.apache.juddi.datatype.KeyedReference;
27 import org.apache.juddi.datatype.request.FindQualifiers;
28 import org.apache.juddi.datatype.tmodel.TModel;
29 import org.apache.juddi.registry.RegistryEngine;
30 import org.apache.juddi.util.Config;
31 import org.apache.juddi.util.jdbc.DynamicQuery;
32
33 /***
34 * @author Steve Viens (sviens@apache.org)
35 */
36 class FindBindingByCategoryQuery
37 {
38
39 private static Log log = LogFactory.getLog(FindBindingByCategoryQuery.class);
40
41 static String selectSQL;
42 static String tablePrefix = "";
43 static
44 {
45 tablePrefix = Config.getStringProperty(
46 RegistryEngine.PROPNAME_TABLE_PREFIX,RegistryEngine.DEFAULT_TABLE_PREFIX);
47
48 StringBuffer sql = new StringBuffer(200);
49 sql.append("SELECT B.BINDING_KEY,B.LAST_UPDATE ");
50 sql.append("FROM ").append(tablePrefix).append("BINDING_TEMPLATE B,").append(tablePrefix).append("BINDING_CATEGORY C ");
51 selectSQL = sql.toString();
52 }
53
54 /***
55 * Select ...
56 *
57 * @param serviceKey
58 * @param categoryBag
59 * @param keysIn
60 * @param qualifiers
61 * @param connection JDBC connection
62 * @throws java.sql.SQLException
63 */
64 public static Vector select(String serviceKey,CategoryBag categoryBag,Vector keysIn,FindQualifiers qualifiers,Connection connection)
65 throws java.sql.SQLException
66 {
67
68
69
70
71 if ((keysIn != null) && (keysIn.size() == 0))
72 return keysIn;
73
74 Vector keysOut = new Vector();
75 PreparedStatement statement = null;
76 ResultSet resultSet = null;
77
78 DynamicQuery sql = new DynamicQuery(selectSQL);
79 appendWhere(sql,serviceKey,categoryBag,qualifiers);
80 appendIn(sql,keysIn);
81 appendOrderBy(sql,qualifiers);
82
83 try
84 {
85 log.debug(sql.toString());
86
87 statement = sql.buildPreparedStatement(connection);
88 resultSet = statement.executeQuery();
89 while (resultSet.next())
90 {
91 keysOut.addElement(resultSet.getString(1));
92 }
93
94 return keysOut;
95 }
96 finally
97 {
98 try {
99 resultSet.close();
100 }
101 catch (Exception e)
102 {
103 log.warn("An Exception was encountered while attempting to close " +
104 "the Find BindingTemplate ResultSet: "+e.getMessage(),e);
105 }
106
107 try {
108 statement.close();
109 }
110 catch (Exception e)
111 {
112 log.warn("An Exception was encountered while attempting to close " +
113 "the Find BindingTemplate Statement: "+e.getMessage(),e);
114 }
115 }
116 }
117
118 /***
119 *
120 */
121 private static void appendWhere(DynamicQuery sql,String serviceKey,CategoryBag categoryBag,FindQualifiers qualifiers)
122 {
123 sql.append("WHERE C.BINDING_KEY = B.BINDING_KEY ");
124 if (serviceKey != null)
125 {
126 sql.append("AND B.SERVICE_KEY = ? ");
127 sql.addValue(serviceKey);
128 }
129
130 if (categoryBag != null)
131 {
132 Vector keyedRefVector = categoryBag.getKeyedReferenceVector();
133
134 if (keyedRefVector != null)
135 {
136 int vectorSize = keyedRefVector.size();
137 if (vectorSize > 0)
138 {
139 sql.append("AND (");
140
141 for (int i=0; i<vectorSize; i++)
142 {
143 KeyedReference keyedRef = (KeyedReference)keyedRefVector.elementAt(i);
144 String key = keyedRef.getTModelKey();
145 String name = keyedRef.getKeyName();
146 String value = keyedRef.getKeyValue();
147
148 if (name == null)
149 name = "";
150
151 if (value == null)
152 value = "";
153
154
155
156
157
158
159 if (key.equals(TModel.GENERAL_KEYWORDS_TMODEL_KEY))
160 {
161 sql.append("(C.TMODEL_KEY_REF = ? AND C.KEY_NAME = ? AND C.KEY_VALUE = ?)");
162 sql.addValue(key);
163 sql.addValue(name);
164 sql.addValue(value);
165
166 if (i+1 < vectorSize)
167 sql.append(" OR ");
168 }
169 else
170 {
171 sql.append("(C.TMODEL_KEY_REF = ? AND C.KEY_VALUE = ?)");
172 sql.addValue(key);
173 sql.addValue(value);
174
175 if (i+1 < vectorSize)
176 sql.append(" OR ");
177 }
178 }
179
180 sql.append(") ");
181 }
182 }
183 }
184 }
185
186 /***
187 * Select ...
188 *
189 * @param serviceKey
190 * @param keyedRef
191 * @param keysIn
192 * @param qualifiers
193 * @param connection JDBC connection
194 * @throws java.sql.SQLException
195 */
196 public static Vector select(String serviceKey,KeyedReference keyedRef,Vector keysIn,FindQualifiers qualifiers,Connection connection)
197 throws java.sql.SQLException
198 {
199
200
201
202
203 if ((keysIn != null) && (keysIn.size() == 0))
204 return keysIn;
205
206 Vector keysOut = new Vector();
207 PreparedStatement statement = null;
208 ResultSet resultSet = null;
209
210 DynamicQuery sql = new DynamicQuery(selectSQL);
211 appendWhere(sql,serviceKey,keyedRef,qualifiers);
212 appendIn(sql,keysIn);
213 appendOrderBy(sql,qualifiers);
214
215 try
216 {
217 log.debug(sql.toString());
218
219 statement = sql.buildPreparedStatement(connection);
220 resultSet = statement.executeQuery();
221 while (resultSet.next())
222 {
223 keysOut.addElement(resultSet.getString(1));
224 }
225
226 return keysOut;
227 }
228 finally
229 {
230 try {
231 resultSet.close();
232 }
233 catch (Exception e)
234 {
235 log.warn("An Exception was encountered while attempting to close " +
236 "the Find BindingTemplate ResultSet: "+e.getMessage(),e);
237 }
238
239 try {
240 statement.close();
241 }
242 catch (Exception e)
243 {
244 log.warn("An Exception was encountered while attempting to close " +
245 "the Find BindingTemplate Statement: "+e.getMessage(),e);
246 }
247 }
248 }
249
250 /***
251 *
252 */
253 private static void appendWhere(DynamicQuery sql,String serviceKey,KeyedReference keyedRef,FindQualifiers qualifiers)
254 {
255 sql.append("WHERE C.BINDING_KEY = B.BINDING_KEY ");
256 if (serviceKey != null)
257 {
258 sql.append("AND B.SERVICE_KEY = ? ");
259 sql.addValue(serviceKey);
260 }
261
262 if (keyedRef != null)
263 {
264 sql.append("AND (");
265
266 String key = keyedRef.getTModelKey();
267 String name = keyedRef.getKeyName();
268 String value = keyedRef.getKeyValue();
269
270 if (name == null)
271 name = "";
272
273 if (value == null)
274 value = "";
275
276
277
278
279
280
281 if (key.equals(TModel.GENERAL_KEYWORDS_TMODEL_KEY))
282 {
283 sql.append("(C.TMODEL_KEY_REF = ? AND C.KEY_NAME = ? AND C.KEY_VALUE = ?)");
284 sql.addValue(key);
285 sql.addValue(name);
286 sql.addValue(value);
287 }
288 else
289 {
290 sql.append("(C.TMODEL_KEY_REF = ? AND C.KEY_VALUE = ?)");
291 sql.addValue(key);
292 sql.addValue(value);
293 }
294
295 sql.append(") ");
296 }
297 }
298
299 /***
300 * Utility method used to construct SQL "IN" statements such as
301 * the following SQL example:
302 *
303 * SELECT * FROM TABLE WHERE MONTH IN ('jan','feb','mar')
304 *
305 * @param sql StringBuffer to append the final results to
306 * @param keysIn Vector of Strings used to construct the "IN" clause
307 */
308 private static void appendIn(DynamicQuery sql,Vector keysIn)
309 {
310 if (keysIn == null)
311 return;
312
313 sql.append("AND B.BINDING_KEY IN (");
314
315 int keyCount = keysIn.size();
316 for (int i=0; i<keyCount; i++)
317 {
318 String key = (String)keysIn.elementAt(i);
319
320 sql.append("?");
321 sql.addValue(key);
322
323 if ((i+1) < keyCount)
324 sql.append(",");
325 }
326
327 sql.append(") ");
328 }
329
330 /***
331 *
332 */
333 private static void appendOrderBy(DynamicQuery sql,FindQualifiers qualifiers)
334 {
335 sql.append("ORDER BY ");
336
337 if (qualifiers == null)
338 sql.append("B.LAST_UPDATE DESC");
339 else if (qualifiers.sortByDateAsc)
340 sql.append("B.LAST_UPDATE ASC");
341 else
342 sql.append("B.LAST_UPDATE DESC");
343 }
344 }