001: /*
002: * Copyright 2003 The Apache Software Foundation.
003: *
004: * Licensed under the Apache License, Version 2.0 (the "License");
005: * you may not use this file except in compliance with the License.
006: * You may obtain a copy of the License at
007: *
008: * http://www.apache.org/licenses/LICENSE-2.0
009: *
010: * Unless required by applicable law or agreed to in writing, software
011: * distributed under the License is distributed on an "AS IS" BASIS,
012: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013: * See the License for the specific language governing permissions and
014: * limitations under the License.
015: */
016:
017: package velosurf.sql;
018:
019: import java.sql.ResultSet;
020: import java.sql.ResultSetMetaData;
021: import java.sql.SQLException;
022: import java.util.ArrayList;
023: import java.util.List;
024: import java.util.regex.Matcher;
025: import java.util.regex.Pattern;
026:
027: import velosurf.util.StringLists;
028: import velosurf.util.Logger;
029:
030: /** various SQL-related helpers.
031: *
032: * @author <a href=mailto:claude.brisson@gmail.com>Claude Brisson</a>
033: *
034: */
035:
036: public class SqlUtil {
037: // that's crazy to have to code such a method...
038: // in Ruby for instance, it's :
039: // '*' * n
040: private static String stars(int length) {
041: StringBuffer ret = new StringBuffer(length);
042: for (int i = 0; i < length; i++)
043: ret.append('*');
044: return ret.toString();
045: }
046:
047: /**
048: * add seach criteria to a query
049: * @param query query
050: * @param criteriaList list of criteria
051: * @return new query
052: */
053: public static String refineQuery(String query, List criteriaList) {
054:
055: if (criteriaList == null || criteriaList.size() == 0)
056: return query;
057:
058: try {
059: /* issue all searches on a string where all constant strings
060: * (inside quotes) and subqueries (inside parenthesis) have been filtered
061: */
062:
063: StringBuffer buffer = new StringBuffer(query.toLowerCase());
064:
065: Matcher matcher = Pattern.compile("'[^']+'")
066: .matcher(buffer);
067: while (matcher.find()) {
068: int start = matcher.start();
069: int end = matcher.end();
070: buffer.replace(start, end, stars(end - start));
071: }
072:
073: matcher = Pattern.compile("\\([^()]+\\)").matcher(buffer);
074: while (matcher.find()) {
075: int start = matcher.start();
076: int end = matcher.end();
077: buffer.replace(start, end, stars(end - start));
078: }
079:
080: Matcher where = Pattern.compile("\\Wwhere\\W").matcher(
081: buffer);
082: Matcher groupby = Pattern.compile("\\Wgroup\\W+by\\W")
083: .matcher(buffer);
084: Matcher orderby = Pattern.compile("\\Worder\\W+by\\W")
085: .matcher(buffer);
086:
087: int after = query.length();
088: if (groupby.find())
089: after = groupby.start();
090: if (orderby.find())
091: after = Math.min(after, orderby.start());
092:
093: String criteria = " ("
094: + StringLists.join(criteriaList, " ) and ( ")
095: + ") ";
096:
097: if (where.find()) {
098: // a little check
099: if (where.start() > after)
100: throw new Exception(
101: "Error: 'where' clause found after 'order by' or 'group by' clause");
102: query = query.substring(0, where.end()) + " ( "
103: + query.substring(where.end(), after)
104: + ") and " + criteria + query.substring(after);
105: } else {
106: query = query.substring(0, after) + " where "
107: + criteria + query.substring(after);
108: }
109: return query;
110: } catch (Exception ree) {
111: Logger.warn("Could not refine query: " + query);
112: Logger.log(ree);
113: return query;
114: }
115: }
116:
117: /** add an ordering clause to a query
118: *
119: * @param query initial query
120: * @param order order clause
121: * @return ordered query
122: */
123: public static String orderQuery(String query, String order) {
124:
125: if (order == null || order.length() == 0)
126: return query;
127:
128: try {
129: /* issue all searches on a string where all constant strings
130: * (inside quotes) and subqueries (inside parenthesis) have been filtered
131: */
132:
133: StringBuffer buffer = new StringBuffer(query.toLowerCase());
134:
135: Matcher matcher = Pattern.compile("'[^']+'")
136: .matcher(buffer);
137: while (matcher.find()) {
138: int start = matcher.start();
139: int end = matcher.end();
140: buffer.replace(start, end, stars(end - start));
141: }
142:
143: matcher = Pattern.compile("\\([^()]+\\)").matcher(buffer);
144: while (matcher.find()) {
145: int start = matcher.start();
146: int end = matcher.end();
147: buffer.replace(start, end, stars(end - start));
148: }
149:
150: Matcher orderby = Pattern.compile("\\Worder\\W+by\\W")
151: .matcher(buffer);
152:
153: if (orderby.find()) {
154: Logger.warn("Query has already an 'order by' clause: "
155: + query);
156: } else {
157: query = query + " order by " + order;
158: }
159: return query;
160: } catch (Exception e) {
161: Logger.log(e);
162: return null; // or query ?
163: }
164: }
165:
166: /**
167: * get the column nams of a result set
168: * @param resultSet result set
169: * @return list of columns
170: * @throws SQLException
171: */
172: public static List<String> getColumnNames(ResultSet resultSet)
173: throws SQLException {
174: List<String> columnNames = new ArrayList<String>();
175: ResultSetMetaData meta = resultSet.getMetaData();
176: int count = meta.getColumnCount();
177: for (int c = 1; c <= count; c++)
178: columnNames.add(meta.getColumnName(c));
179: return columnNames;
180: }
181: }
|