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 FindBusinessByNameQuery
35  {
36    // private reference to the jUDDI logger
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      // build selectSQL
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      // if there is a keysIn vector but it doesn't contain
65      // any keys then the previous query has exhausted
66      // all possibilities of a match so skip this call.
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      // construct the SQL statement
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));//("BUSINESS_KEY"));
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) // default
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             // If lang is "en" we'll need to match with "en", "en_US" or "en_UK"
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 }