View Javadoc

1   /*
2    * Copyright 2001-2004 The Apache Software Foundation.
3    * 
4    * Licensed under the Apache License, Version 2.0 (the "License");
5    * you may not use this file except in compliance with the License.
6    * You may obtain a copy of the License at
7    * 
8    *      http://www.apache.org/licenses/LICENSE-2.0
9    * 
10   * Unless required by applicable law or agreed to in writing, software
11   * distributed under the License is distributed on an "AS IS" BASIS,
12   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13   * See the License for the specific language governing permissions and
14   * limitations under the License.
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    // private reference to the jUDDI logger
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      // build selectSQL
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      // if there is a keysIn vector but it doesn't contain
66      // any keys then the previous query has exhausted
67      // all possibilities of a match so skip this call.
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      // construct the SQL statement
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));//("SERVICE_KEY"));
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     // per UDDI v2.0 Programmers API Errata (pg. 14), businessKey is
123     // is no longer a required attribute of the find_service server.
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) // default
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             // If lang is "en" we'll need to match with "en", "en_US" or "en_UK"
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 }