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