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 FindBusinessByNameQuery
35 {
36
37 private static Log log = LogFactory.getLog(FindBusinessByNameQuery.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 B.BUSINESS_KEY,B.LAST_UPDATE,N.NAME ");
48 sql.append("FROM ").append(tablePrefix).append("BUSINESS_ENTITY B,").append(tablePrefix).append("BUSINESS_NAME N ");
49 selectSQL = sql.toString();
50 }
51
52 /***
53 * Select ...
54 *
55 * @param names
56 * @param keysIn
57 * @param qualifiers
58 * @param connection JDBC connection
59 * @throws java.sql.SQLException
60 */
61 public static Vector select(Vector names,Vector keysIn,FindQualifiers qualifiers,Connection connection)
62 throws java.sql.SQLException
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,names,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 if (resultSet != null) {
96 resultSet.close();
97 }
98 }
99 catch (Exception e)
100 {
101 log.warn("An Exception was encountered while attempting to close " +
102 "the Find BusinessEntity ResultSet: "+e.getMessage(),e);
103 }
104
105 try {
106 statement.close();
107 }
108 catch (Exception e)
109 {
110 log.warn("An Exception was encountered while attempting to close " +
111 "the Find BusinessEntity Statement: "+e.getMessage(),e);
112 }
113 }
114 }
115
116 /***
117 *
118 */
119 private static void appendWhere(DynamicQuery sql,Vector names,FindQualifiers qualifiers)
120 {
121 sql.append("WHERE B.BUSINESS_KEY = N.BUSINESS_KEY ");
122
123 if (names != null)
124 {
125 int nameSize = names.size();
126 if (nameSize > 0)
127 {
128 sql.append("AND (");
129
130 for (int i=0; i<nameSize; i++)
131 {
132 Name name = (Name)names.elementAt(i);
133 String text = name.getValue();
134 String lang = name.getLanguageCode();
135
136 if ((text != null) && (text.length() > 0))
137 {
138 if (qualifiers == null)
139 {
140 sql.append("(UPPER(NAME) LIKE ?");
141 sql.addValue(text.endsWith("%") ? text.toUpperCase() : text.toUpperCase()+"%");
142 }
143 else if ((qualifiers.caseSensitiveMatch) && (qualifiers.exactNameMatch))
144 {
145 sql.append("(NAME = ?");
146 sql.addValue(text);
147 }
148 else if ((!qualifiers.caseSensitiveMatch) && (qualifiers.exactNameMatch))
149 {
150 sql.append("(UPPER(NAME) = ?");
151 sql.addValue(text.toUpperCase());
152 }
153 else if ((qualifiers.caseSensitiveMatch) && (!qualifiers.exactNameMatch))
154 {
155 sql.append("(NAME LIKE ?");
156 sql.addValue(text.endsWith("%") ? text : text+"%");
157 }
158 else if ((!qualifiers.caseSensitiveMatch) && (!qualifiers.exactNameMatch))
159 {
160 sql.append("(UPPER(NAME) LIKE ?");
161 sql.addValue(text.endsWith("%") ? text.toUpperCase() : text.toUpperCase()+"%");
162 }
163
164
165 if ((lang != null) && (lang.length() > 0))
166 {
167 sql.append(" AND (UPPER(LANG_CODE) LIKE ?)");
168 sql.addValue(lang.toUpperCase()+"%");
169 }
170
171 sql.append(")");
172
173 if (i+1 < nameSize)
174 sql.append(" OR ");
175 }
176 }
177 }
178
179 sql.append(") ");
180 }
181 }
182
183 /***
184 * Utility method used to construct SQL "IN" statements such as
185 * the following SQL example:
186 *
187 * SELECT * FROM TABLE WHERE MONTH IN ('jan','feb','mar')
188 *
189 * @param sql StringBuffer to append the final results to
190 * @param keysIn Vector of Strings used to construct the "IN" clause
191 */
192 private static void appendIn(DynamicQuery sql,Vector keysIn)
193 {
194 if (keysIn == null)
195 return;
196
197 sql.append("AND B.BUSINESS_KEY IN (");
198
199 int keyCount = keysIn.size();
200 for (int i=0; i<keyCount; i++)
201 {
202 String key = (String)keysIn.elementAt(i);
203 sql.append("?");
204 sql.addValue(key);
205
206 if ((i+1) < keyCount)
207 sql.append(",");
208 }
209
210 sql.append(") ");
211 }
212
213 /***
214 *
215 */
216 private static void appendOrderBy(DynamicQuery sql,FindQualifiers qualifiers)
217 {
218 sql.append("ORDER BY ");
219
220 if ((qualifiers == null) ||
221 ((!qualifiers.sortByNameAsc) && (!qualifiers.sortByNameDesc) &&
222 (!qualifiers.sortByDateAsc) && (!qualifiers.sortByDateDesc)))
223 {
224 sql.append("N.NAME ASC,B.LAST_UPDATE DESC");
225 }
226 else if (qualifiers.sortByNameAsc || qualifiers.sortByNameDesc)
227 {
228 if (qualifiers.sortByDateAsc || qualifiers.sortByDateDesc)
229 {
230 if (qualifiers.sortByNameAsc && qualifiers.sortByDateDesc)
231 sql.append("N.NAME ASC,B.LAST_UPDATE DESC");
232 else if (qualifiers.sortByNameAsc && qualifiers.sortByDateAsc)
233 sql.append("N.NAME ASC,B.LAST_UPDATE ASC");
234 else if (qualifiers.sortByNameDesc && qualifiers.sortByDateDesc)
235 sql.append("N.NAME DESC,B.LAST_UPDATE DESC");
236 else
237 sql.append("N.NAME DESC,B.LAST_UPDATE ASC");
238 }
239 else
240 {
241 if (qualifiers.sortByNameAsc)
242 sql.append("N.NAME ASC,B.LAST_UPDATE DESC");
243 else
244 sql.append("N.NAME DESC,B.LAST_UPDATE DESC");
245 }
246 }
247 else if (qualifiers.sortByDateAsc || qualifiers.sortByDateDesc)
248 {
249 if (qualifiers.sortByDateDesc)
250 sql.append("B.LAST_UPDATE DESC,N.NAME ASC");
251 else
252 sql.append("B.LAST_UPDATE ASC,N.NAME ASC");
253 }
254 }
255 }