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.Name;
26 import org.apache.juddi.datatype.request.FindQualifiers;
27 import org.apache.juddi.registry.RegistryEngine;
28 import org.apache.juddi.util.Config;
29 import org.apache.juddi.util.jdbc.DynamicQuery;
30
31 /***
32 * @author Steve Viens (sviens@apache.org)
33 */
34 class FindServiceByNameQuery
35 {
36
37 private static Log log = LogFactory.getLog(FindServiceByNameQuery.class);
38
39 static String selectSQL;
40 static String tablePrefix;
41 static
42 {
43 tablePrefix = Config.getStringProperty(
44 RegistryEngine.PROPNAME_TABLE_PREFIX,RegistryEngine.DEFAULT_TABLE_PREFIX);
45
46 StringBuffer sql = new StringBuffer(200);
47 sql.append("SELECT S.SERVICE_KEY,S.LAST_UPDATE,N.NAME ");
48 sql.append("FROM ").append(tablePrefix).append("BUSINESS_SERVICE S,").append(tablePrefix).append("SERVICE_NAME N ");
49 selectSQL = sql.toString();
50 }
51
52 /***
53 * Select ...
54 *
55 * @param businessKey primary key value
56 * @param names
57 * @param keysIn
58 * @param qualifiers
59 * @param connection JDBC connection
60 * @throws java.sql.SQLException
61 */
62 public static Vector select(String businessKey,Vector names,Vector keysIn,FindQualifiers qualifiers,Connection connection)
63 throws java.sql.SQLException
64 {
65
66
67
68 if ((keysIn != null) && (keysIn.size() == 0))
69 return keysIn;
70
71 Vector keysOut = new Vector();
72 PreparedStatement statement = null;
73 ResultSet resultSet = null;
74
75
76 DynamicQuery sql = new DynamicQuery(selectSQL);
77 appendWhere(sql,businessKey,names,qualifiers);
78 appendIn(sql,keysIn);
79 appendOrderBy(sql,qualifiers);
80
81 try
82 {
83 log.debug(sql.toString());
84
85 statement = sql.buildPreparedStatement(connection);
86 resultSet = statement.executeQuery();
87
88 while (resultSet.next())
89 keysOut.addElement(resultSet.getString(1));
90
91 return keysOut;
92 }
93 finally
94 {
95 try {
96 resultSet.close();
97 }
98 catch (Exception e)
99 {
100 log.warn("An Exception was encountered while attempting to close " +
101 "the Find BusinessService ResultSet: "+e.getMessage(),e);
102 }
103
104 try {
105 statement.close();
106 }
107 catch (Exception e)
108 {
109 log.warn("An Exception was encountered while attempting to close " +
110 "the Find BusinessService Statement: "+e.getMessage(),e);
111 }
112 }
113 }
114
115 /***
116 *
117 */
118 private static void appendWhere(DynamicQuery sql,String businessKey,Vector names,FindQualifiers qualifiers)
119 {
120 sql.append("WHERE N.SERVICE_KEY = S.SERVICE_KEY ");
121
122
123
124 if((businessKey != null) && (businessKey.length() > 0))
125 {
126 sql.append("AND S.BUSINESS_KEY = ? ");
127 sql.addValue(businessKey);
128 }
129
130 if (names != null)
131 {
132 int nameSize = names.size();
133 if (nameSize > 0)
134 {
135 sql.append("AND (");
136
137 for (int i=0; i<nameSize; i++)
138 {
139 Name name = (Name)names.elementAt(i);
140 String text = name.getValue();
141 String lang = name.getLanguageCode();
142
143 if ((text != null) && (text.length() > 0))
144 {
145 if (qualifiers == null)
146 {
147 sql.append("(UPPER(NAME) LIKE ?");
148 sql.addValue(text.endsWith("%") ? text.toUpperCase() : text.toUpperCase()+"%");
149 }
150 else if ((qualifiers.caseSensitiveMatch) && (qualifiers.exactNameMatch))
151 {
152 sql.append("(NAME = ?");
153 sql.addValue(text);
154 }
155 else if ((!qualifiers.caseSensitiveMatch) && (qualifiers.exactNameMatch))
156 {
157 sql.append("(UPPER(NAME) = ?");
158 sql.addValue(text.toUpperCase());
159 }
160 else if ((qualifiers.caseSensitiveMatch) && (!qualifiers.exactNameMatch))
161 {
162 sql.append("(NAME LIKE ?");
163 sql.addValue(text.endsWith("%") ? text : text+"%");
164 }
165 else if ((!qualifiers.caseSensitiveMatch) && (!qualifiers.exactNameMatch))
166 {
167 sql.append("(UPPER(NAME) LIKE ?");
168 sql.addValue(text.endsWith("%") ? text.toUpperCase() : text.toUpperCase()+"%");
169 }
170
171
172 if ((lang != null) && (lang.length() > 0))
173 {
174 sql.append(" AND (UPPER(LANG_CODE) LIKE ?)");
175 sql.addValue(lang.toUpperCase()+"%");
176 }
177
178 sql.append(")");
179
180 if (i+1 < nameSize)
181 sql.append(" OR ");
182 }
183 }
184 }
185
186 sql.append(") ");
187 }
188 }
189
190 /***
191 * Utility method used to construct SQL "IN" statements such as
192 * the following SQL example:
193 *
194 * SELECT * FROM TABLE WHERE MONTH IN ('jan','feb','mar')
195 *
196 * @param sql StringBuffer to append the final results to
197 * @param keysIn Vector of Strings used to construct the "IN" clause
198 */
199 private static void appendIn(DynamicQuery sql,Vector keysIn)
200 {
201 if (keysIn == null)
202 return;
203
204 sql.append("AND S.SERVICE_KEY IN (");
205
206 int keyCount = keysIn.size();
207 for (int i=0; i<keyCount; i++)
208 {
209 String key = (String)keysIn.elementAt(i);
210 sql.append("?");
211 sql.addValue(key);
212
213 if ((i+1) < keyCount)
214 sql.append(",");
215 }
216
217 sql.append(") ");
218 }
219
220 /***
221 *
222 */
223 private static void appendOrderBy(DynamicQuery sql,FindQualifiers qualifiers)
224 {
225 sql.append("ORDER BY ");
226
227 if ((qualifiers == null) ||
228 ((!qualifiers.sortByNameAsc) && (!qualifiers.sortByNameDesc) &&
229 (!qualifiers.sortByDateAsc) && (!qualifiers.sortByDateDesc)))
230 {
231 sql.append("N.NAME ASC,S.LAST_UPDATE DESC");
232 }
233 else if (qualifiers.sortByNameAsc || qualifiers.sortByNameDesc)
234 {
235 if (qualifiers.sortByDateAsc || qualifiers.sortByDateDesc)
236 {
237 if (qualifiers.sortByNameAsc && qualifiers.sortByDateDesc)
238 sql.append("N.NAME ASC,S.LAST_UPDATE DESC");
239 else if (qualifiers.sortByNameAsc && qualifiers.sortByDateAsc)
240 sql.append("N.NAME ASC,S.LAST_UPDATE ASC");
241 else if (qualifiers.sortByNameDesc && qualifiers.sortByDateDesc)
242 sql.append("N.NAME DESC,S.LAST_UPDATE DESC");
243 else
244 sql.append("N.NAME DESC,S.LAST_UPDATE ASC");
245 }
246 else
247 {
248 if (qualifiers.sortByNameAsc)
249 sql.append("N.NAME ASC,S.LAST_UPDATE DESC");
250 else
251 sql.append("N.NAME DESC,S.LAST_UPDATE DESC");
252 }
253 }
254 else if (qualifiers.sortByDateAsc || qualifiers.sortByDateDesc)
255 {
256 if (qualifiers.sortByDateDesc)
257 sql.append("S.LAST_UPDATE DESC,N.NAME ASC");
258 else
259 sql.append("S.LAST_UPDATE ASC,N.NAME ASC");
260 }
261 }
262 }