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