001: /* ====================================================================
002: * The LateralNZ Software License, Version 1.0
003: *
004: * Copyright (c) 2003 LateralNZ. All rights reserved.
005: *
006: * Redistribution and use in source and binary forms, with or without
007: * modification, are permitted provided that the following conditions
008: * are met:
009: *
010: * 1. Redistributions of source code must retain the above copyright
011: * notice, this list of conditions and the following disclaimer.
012: *
013: * 2. Redistributions in binary form must reproduce the above copyright
014: * notice, this list of conditions and the following disclaimer in
015: * the documentation and/or other materials provided with the
016: * distribution.
017: *
018: * 3. The end-user documentation included with the redistribution,
019: * if any, must include the following acknowledgment:
020: * "This product includes software developed by
021: * LateralNZ (http://www.lateralnz.org/) and other third parties."
022: * Alternately, this acknowledgment may appear in the software itself,
023: * if and wherever such third-party acknowledgments normally appear.
024: *
025: * 4. The names "LateralNZ" must not be used to endorse or promote
026: * products derived from this software without prior written
027: * permission. For written permission, please
028: * contact oss@lateralnz.org.
029: *
030: * 5. Products derived from this software may not be called "Panther",
031: * or "Lateral" or "LateralNZ", nor may "PANTHER" or "LATERAL" or
032: * "LATERALNZ" appear in their name, without prior written
033: * permission of LateralNZ.
034: *
035: * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
036: * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
037: * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
038: * DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
039: * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
040: * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
041: * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
042: * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
043: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
044: * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
045: * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
046: * SUCH DAMAGE.
047: * ====================================================================
048: *
049: * This software consists of voluntary contributions made by many
050: * individuals on behalf of LateralNZ. For more
051: * information on Lateral, please see http://www.lateralnz.com/ or
052: * http://www.lateralnz.org
053: *
054: */
055: package org.lateralnz.c3d.util;
056:
057: import java.sql.Date;
058: import java.sql.PreparedStatement;
059: import java.sql.ResultSet;
060: import java.sql.SQLException;
061: import java.sql.Time;
062: import java.sql.Timestamp;
063: import java.sql.Types;
064: import java.math.BigDecimal;
065: import java.util.regex.*;
066: import java.util.HashMap;
067: import java.util.Iterator;
068: import java.util.ArrayList;
069: import java.util.List;
070: import java.util.Map;
071: import java.util.NoSuchElementException;
072: import java.util.StringTokenizer;
073:
074: import org.lateralnz.common.util.Constants;
075: import org.lateralnz.common.util.StringUtils;
076:
077: /**
078: * general utilities
079: */
080: public class DBUtils implements Constants {
081:
082: public static final int DELETE_STATEMENT = 1;
083: public static final int INSERT_STATEMENT = 2;
084: public static final int SELECT_STATEMENT = 3;
085: public static final int UPDATE_STATEMENT = 4;
086: public static final int CLEAR_CACHE_STATEMENT = 5;
087: public static final int QUERY_CACHE_STATEMENT = 6;
088: public static final int QUERY_CACHE_DUMP_STATEMENT = 7;
089: public static final int QUERY_STATS_STATEMENT = 8;
090:
091: public static final String SEP = "::";
092:
093: private static final String IGNORE_SPACES_TO_END = "\\s*$";
094: private static final String SET_CLAUSE_DELIMS = ",=";
095:
096: private static final Pattern SPLIT_PATTERN = Pattern
097: .compile("\\G(?:^|;)([^';]*+(?:'(?:[^'\\\\]|\\\\')*+'[^';]*+)*+)");
098: private static final Pattern CACHE_NAME_PATTERN = Pattern.compile(
099: "clear|query\\s*cache\\s*([^\\s]*).*", Pattern.DOTALL);
100: private static final Pattern DUMP_NAME_PATTERN = Pattern.compile(
101: "query\\s*cache\\s.*\\sdump\\s*'(.*)'$", Pattern.DOTALL);
102: private static final Pattern WHERE_CLAUSE_PATTERN = Pattern
103: .compile("\\swhere\\s+(.*?)$", Pattern.DOTALL);
104: private static final Pattern INSERT_TABLE_PATTERN = Pattern
105: .compile("insert\\s*into\\s*([^\\s]*).*", Pattern.DOTALL);
106: private static final Pattern UPDATE_TABLE_PATTERN = Pattern
107: .compile("update\\s*([^\\s]*).*", Pattern.DOTALL);
108: private static final Pattern DELETE_TABLE_PATTERN = Pattern
109: .compile("delete\\s*from\\s*([^\\s]*).*", Pattern.DOTALL);
110:
111: private static final Pattern CLEAR_CACHE_PATTERN = Pattern.compile(
112: "^\\s*clear\\s*cache.*", Pattern.DOTALL);
113: private static final Pattern DELETE_PATTERN = Pattern.compile(
114: "^\\s*delete\\s*from.+", Pattern.DOTALL);
115: private static final Pattern INSERT_PATTERN = Pattern.compile(
116: "^\\s*insert\\s*into.+", Pattern.DOTALL);
117: private static final Pattern QUERY_CACHE_PATTERN = Pattern.compile(
118: "^\\s*query\\s*cache.*", Pattern.DOTALL);
119: private static final Pattern QUERY_CACHE_DUMP_PATTERN = Pattern
120: .compile("^\\s*query\\s*cache.*\\sdump\\s.*",
121: Pattern.DOTALL);
122: private static final Pattern QUERY_STATS_PATTERN = Pattern.compile(
123: "^\\s*query\\s*stats.*", Pattern.DOTALL);
124: private static final Pattern SELECT_PATTERN = Pattern.compile(
125: "^(/\\*.*\\*/)*\\s*select\\s.+", Pattern.DOTALL);
126: private static final Pattern UPDATE_PATTERN = Pattern.compile(
127: "^\\s*update\\s.+", Pattern.DOTALL);
128:
129: private DBUtils() {
130: }
131:
132: /**
133: * count the selects that appear in a list of SQL statements
134: */
135: public static final int countSelects(List l) {
136: Iterator iter = l.iterator();
137: int count = 0;
138: while (iter.hasNext()) {
139: String s = (String) iter.next();
140: if (isStatementType(s, SELECT_STATEMENT)) {
141: count++;
142: }
143: }
144: return count;
145: }
146:
147: /**
148: * create a column object from the resultset using an array of db types and the index
149: * of the column we're creating
150: */
151: public static final Column createColumn(ResultSet rs, int[] types,
152: int idx) throws SQLException {
153: Column col = new Column(rs.getBytes(idx), types[idx - 1]);
154: col.setNull(rs.wasNull());
155: return col;
156: }
157:
158: public static final Column createColumn(String value) {
159: Column col = new Column(value.getBytes(), Types.VARCHAR);
160: col.setNull(false);
161: return col;
162: }
163:
164: public static final Column[] createRow(String[] values) {
165: Column[] cols = new Column[values.length];
166: for (int i = 0; i < values.length; i++) {
167: if (values[i] == null) {
168: cols[i] = createColumn(EMPTY);
169: } else {
170: cols[i] = createColumn(values[i]);
171: }
172: }
173: return cols;
174: }
175:
176: /**
177: * flatten an array of columns into a string
178: */
179: public static final String flatten(Column[] cols, String delim) {
180: if (cols == null) {
181: return EMPTY;
182: }
183: StringBuffer sb = new StringBuffer();
184: int max = cols.length - 1;
185: for (int i = 0; i < cols.length; i++) {
186: Object obj = cols[i].getObjectValue();
187: if (obj != null) {
188: sb.append(cols[i].getObjectValue().toString());
189: if (i < max) {
190: sb.append(delim);
191: }
192: }
193: }
194:
195: return sb.toString();
196: }
197:
198: public static final String getCacheNameFromKey(String key) {
199: String[] s = key.split(SEP);
200: if (s == null) {
201: return EMPTY;
202: } else {
203: return s[0];
204: }
205: }
206:
207: /**
208: * return the column data based on an array of primary key columns.
209: * For example, if the primary keys are in columns 1, 5 and 6 of the resultset,
210: * this will return the data in those columns as a 3 element array.
211: */
212: public static final String[] getKeyColumnData(int[] keyColumns,
213: ResultSet rs) throws SQLException {
214: String[] keyData = new String[keyColumns.length];
215: for (int i = 0; i < keyColumns.length; i++) {
216: keyData[i] = rs.getString(keyColumns[i]);
217: }
218: return keyData;
219: }
220:
221: public static final String getInsertTableName(String sql) {
222: Matcher matcher = INSERT_TABLE_PATTERN.matcher(sql);
223: String table = null;
224: if (matcher.find()) {
225: return matcher.group(1);
226: } else {
227: return null;
228: }
229: }
230:
231: public static final Object getObjectFromMap(Object key, Map m,
232: Class newObjClass) throws SQLException {
233: if (!m.containsKey(key)) {
234: synchronized (m) {
235: if (!m.containsKey(key)) {
236: try {
237: m.put(key, newObjClass.newInstance());
238: } catch (Exception e) {
239: throw new SQLException("system error: "
240: + e.getMessage());
241: }
242: }
243: }
244: }
245:
246: return m.get(key);
247: }
248:
249: public static final String getResultSetDataKey(String cache,
250: String[] keyColumns) {
251: StringBuffer sb = new StringBuffer();
252: sb.append(cache).append(SEP);
253: int len = keyColumns.length - 1;
254: for (int i = 0; i < keyColumns.length; i++) {
255: sb.append(keyColumns[i]);
256: if (i < len) {
257: sb.append(PIPE);
258: }
259: }
260: return sb.toString();
261: }
262:
263: public static final String getTargetName(String sql, int type) {
264: Pattern pat;
265: switch (type) {
266: case INSERT_STATEMENT:
267: pat = INSERT_TABLE_PATTERN;
268: break;
269: case DELETE_STATEMENT:
270: pat = DELETE_TABLE_PATTERN;
271: break;
272: case UPDATE_STATEMENT:
273: pat = UPDATE_TABLE_PATTERN;
274: break;
275: case CLEAR_CACHE_STATEMENT:
276: case QUERY_CACHE_STATEMENT:
277: pat = CACHE_NAME_PATTERN;
278: break;
279: case QUERY_CACHE_DUMP_STATEMENT:
280: pat = DUMP_NAME_PATTERN;
281: break;
282: default:
283: return null;
284: }
285:
286: Matcher matcher = pat.matcher(sql);
287: String table = null;
288: if (matcher.find()) {
289: return matcher.group(1);
290: } else {
291: return null;
292: }
293: }
294:
295: /**
296: * get the where clause of an SQL statement
297: */
298: public static final String getWhereClause(String sql) {
299: Matcher matcher = WHERE_CLAUSE_PATTERN.matcher(sql);
300: if (matcher.find()) {
301: return matcher.group(1);
302: } else {
303: return EMPTY;
304: }
305: }
306:
307: public static final boolean isStatementType(String s, int type) {
308: Pattern pat;
309: switch (type) {
310: case DELETE_STATEMENT:
311: pat = DELETE_PATTERN;
312: break;
313: case INSERT_STATEMENT:
314: pat = INSERT_PATTERN;
315: break;
316: case SELECT_STATEMENT:
317: pat = SELECT_PATTERN;
318: break;
319: case UPDATE_STATEMENT:
320: pat = UPDATE_PATTERN;
321: break;
322: case CLEAR_CACHE_STATEMENT:
323: pat = CLEAR_CACHE_PATTERN;
324: break;
325: case QUERY_CACHE_DUMP_STATEMENT:
326: pat = QUERY_CACHE_DUMP_PATTERN;
327: break;
328: case QUERY_CACHE_STATEMENT:
329: pat = QUERY_CACHE_PATTERN;
330: break;
331: case QUERY_STATS_STATEMENT:
332: pat = QUERY_STATS_PATTERN;
333: break;
334:
335: default:
336: return false;
337: }
338: s = s.trim();
339: Matcher m = pat.matcher(s);
340: return m.matches();
341: }
342:
343: /**
344: * given an array of Column objects set the values of those columns as parameters in
345: * a prepared statement
346: */
347: public static final void setParams(PreparedStatement ps,
348: Column[] cols, int offset, int length) throws SQLException {
349: for (int i = offset, j = 1; i < cols.length && j <= length; i++, j++) {
350: if (cols[i].getNull()) {
351: if (cols[i].getObjectValue() == null) {
352: ps.setNull(j, cols[i].getType());
353: } else {
354: ps.setNull(j, cols[i].getType(), (String) cols[i]
355: .getObjectValue());
356: }
357: continue;
358: }
359:
360: switch (cols[i].getType()) {
361: case Types.BIGINT:
362: ps.setLong(j, ((Long) cols[i].getObjectValue())
363: .longValue());
364: break;
365: case Types.BOOLEAN:
366: ps.setBoolean(j, Boolean.valueOf(
367: (String) cols[i].getObjectValue())
368: .booleanValue());
369: break;
370: case Types.DATE:
371: ps.setDate(j, (Date) cols[i].getObjectValue());
372: break;
373: case Types.DOUBLE:
374: ps.setDouble(j, ((Double) cols[i].getObjectValue())
375: .doubleValue());
376: break;
377: case Types.FLOAT:
378: ps.setFloat(j, ((Float) cols[i].getObjectValue())
379: .floatValue());
380: break;
381: case Types.INTEGER:
382: ps.setInt(j, ((Integer) cols[i].getObjectValue())
383: .intValue());
384: break;
385: case Types.NUMERIC:
386: ps.setBigDecimal(j, (BigDecimal) cols[i]
387: .getObjectValue());
388: break;
389: case Types.SMALLINT:
390: ps.setShort(j, ((Short) cols[i].getObjectValue())
391: .shortValue());
392: break;
393: case Types.TIME:
394: ps.setTime(j, (Time) cols[i].getObjectValue());
395: break;
396: case Types.TIMESTAMP:
397: ps
398: .setTimestamp(j, (Timestamp) cols[i]
399: .getObjectValue());
400: break;
401: case Types.TINYINT:
402: ps.setByte(j, ((Byte) cols[i].getObjectValue())
403: .byteValue());
404: break;
405: case Types.VARBINARY:
406: ps.setBytes(j, cols[i].getValue());
407: break;
408: case Types.VARCHAR:
409: ps.setString(j, (String) cols[i].getObjectValue());
410: break;
411: }
412: }
413: }
414:
415: /**
416: * split an SQL statement into SQL fragments separated by ;
417: * for example, select * from table1;insert into table2
418: * should result in 2 separate statements
419: */
420: public static final List splitSQL(String s) {
421: ArrayList ll = new ArrayList();
422:
423: Matcher matcher = SPLIT_PATTERN.matcher(s);
424: while (matcher.find()) {
425: String field = matcher.group(1);
426:
427: field = field.trim();
428: if (!StringUtils.isEmpty(field)) {
429: ll.add(field);
430: }
431: }
432: return ll;
433: }
434:
435: /**
436: * split a SQL set clause into a map of column=value
437: */
438: public static final Map splitSet(String setSQL) throws SQLException {
439: StringTokenizer st = new StringTokenizer(setSQL,
440: SET_CLAUSE_DELIMS, true);
441: try {
442: HashMap rtn = new HashMap();
443: while (st.hasMoreTokens()) {
444: String col = st.nextToken();
445: if (!st.nextToken().equals(EQUALS)) {
446: throw new SQLException(
447: "unexpected token in update columns");
448: }
449: String tmp;
450: StringBuffer val = new StringBuffer();
451: while (st.hasMoreTokens()) {
452: tmp = st.nextToken();
453: if (tmp.equals(COMMA)
454: && (StringUtils.countOccurrences(val, '\'') % 2) == 0) {
455: break;
456: }
457: val.append(tmp);
458: }
459: rtn.put(col, val.toString());
460: }
461: return rtn;
462: } catch (SQLException se) {
463: throw se;
464: } catch (NoSuchElementException nsee) {
465: throw new SQLException("error parsing columns in update");
466: }
467: }
468:
469: }
|