001: /**
002: Copyright (C) 2002-2003 Together
003:
004: This library is free software; you can redistribute it and/or
005: modify it under the terms of the GNU Lesser General Public
006: License as published by the Free Software Foundation; either
007: version 2.1 of the License, or (at your option) any later version.
008:
009: This library is distributed in the hope that it will be useful,
010: but WITHOUT ANY WARRANTY; without even the implied warranty of
011: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
012: Lesser General Public License for more details.
013:
014: You should have received a copy of the GNU Lesser General Public
015: License along with this library; if not, write to the Free Software
016: Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
017:
018: */package org.webdocwf.util.loader;
019:
020: import java.io.*;
021: import java.util.*;
022: import java.sql.Statement;
023:
024: /**
025: * Class is used for parsing sql statements.
026: *
027: * @author Zoran Milakovic
028: */
029: public class SqlParser {
030:
031: public static final String INSERT = "insert";
032: public static final String UPDATE = "update";
033: public static final String SELECT = "select";
034:
035: private static final String QUOTE_ESCAPE = "''";
036: private static final String COMMA_ESCAPE = "~#####1~";
037:
038: private ArrayList binaryStreamObjectList = new ArrayList();
039:
040: public String tableName;
041:
042: public String whereStatement;
043:
044: public String sqlType;
045:
046: public String[] columnNames;
047:
048: public String[] columnValues;
049:
050: public String[] columnWhereNames;
051:
052: public String[] columnWhereValues;
053:
054: /**
055: *Gets the tableName attribute of the SqlParser object
056: *
057: * @return The tableName value
058: * @since
059: */
060: public String getTableName() {
061: return tableName;
062: }
063:
064: /**
065: * Gets the columnNames attribute of the SqlParser object
066: *
067: * @return The columnNames value
068: * @since
069: */
070: public String[] getColumnNames() {
071: return columnNames;
072: }
073:
074: public String[] getWhereColumnNames() {
075: return columnWhereNames;
076: }
077:
078: public String[] getWhereColumnValues() {
079: return columnWhereValues;
080: }
081:
082: public String[] getColumnValues() {
083: return columnValues;
084: }
085:
086: /**
087: * Parse sql statement.
088: *
089: * @param sql defines SQL statement
090: * @exception Exception Description of Exception
091: * @since
092: */
093: public void parse(String sql) throws Exception {
094: sql = sql.trim();
095: tableName = null;
096: columnNames = new String[0];
097: columnValues = new String[0];
098: columnWhereNames = new String[0];
099: columnWhereValues = new String[0];
100: whereStatement = null;
101: sqlType = null;
102: sql = sql.trim();
103:
104: //replace comma(,) in values between quotes(')
105: StringTokenizer tokQuote = new StringTokenizer(sql.toString(),
106: "'", true);
107: StringBuffer sb = new StringBuffer();
108: boolean openParent1 = false;
109: while (tokQuote.hasMoreTokens()) {
110: String next = tokQuote.nextToken();
111: if (openParent1)
112: next = Utils.replaceAll(next, ",", COMMA_ESCAPE);
113: sb.append(next);
114: if (next.equalsIgnoreCase("'")) {
115: if (openParent1 == true)
116: openParent1 = false;
117: else
118: openParent1 = true;
119: }
120: }
121: //END replacement
122: sql = sb.toString();
123: String upperSql = sql.toUpperCase();
124:
125: //handle unsupported statements
126: if (upperSql.startsWith("ALTER "))
127: throw new Exception(
128: "ALTER TABLE statements are not supported.");
129: if (upperSql.startsWith("DROP "))
130: throw new Exception("DROP statements are not supported.");
131:
132: //SELECT
133: if (upperSql.startsWith("SELECT ")) {
134: if (upperSql.lastIndexOf(" FROM ") == -1) {
135: throw new Exception(
136: "Malformed SQL. Missing FROM statement.");
137: }
138:
139: sqlType = SELECT;
140: int fromPos = upperSql.lastIndexOf(" FROM ");
141: int wherePos = upperSql.lastIndexOf(" WHERE ");
142: if (wherePos == -1)
143: tableName = sql.substring(fromPos + 6).trim();
144: else
145: tableName = sql.substring(fromPos + 6, wherePos).trim();
146:
147: Vector cols = new Vector();
148: StringTokenizer tokenizer = new StringTokenizer(upperSql
149: .substring(7, fromPos), ",");
150:
151: while (tokenizer.hasMoreTokens()) {
152: cols.add(tokenizer.nextToken().trim());
153: }
154:
155: columnNames = new String[cols.size()];
156: cols.copyInto(columnNames);
157: if (wherePos != -1) {
158: String strWhere = sql.substring(wherePos + 7);
159: Vector whereCols = new Vector();
160: Vector whereValues = new Vector();
161: StringTokenizer tokenizerWhere = new StringTokenizer(
162: strWhere, ",");
163:
164: while (tokenizerWhere.hasMoreTokens()) {
165: String strToken = tokenizerWhere.nextToken();
166: if (strToken.toLowerCase().indexOf(" and ") != -1) {
167: String temp = strToken;
168: int andPos = 0;
169: out: do {
170: andPos = temp.toLowerCase()
171: .indexOf(" and ");
172: String strTokenAdd;
173: if (andPos != -1)
174: strTokenAdd = temp.substring(0, andPos)
175: .trim();
176: else
177: strTokenAdd = temp.trim();
178: int delimiter2 = strTokenAdd.indexOf("=");
179: if (delimiter2 != -1) {
180: String valueAdd = strTokenAdd
181: .substring(delimiter2 + 1)
182: .trim();
183: valueAdd = Utils
184: .handleQuotedString(valueAdd);
185: whereCols.add(strTokenAdd.substring(0,
186: delimiter2).trim());
187: valueAdd = Utils.replaceAll(valueAdd,
188: COMMA_ESCAPE, ",");
189: valueAdd = Utils.replaceAll(valueAdd,
190: QUOTE_ESCAPE, "'");
191: whereValues.add(valueAdd);
192: } else {
193: int delimiter3 = strTokenAdd
194: .toLowerCase().indexOf(" is ");
195: whereCols.add(strTokenAdd.substring(0,
196: delimiter3).trim());
197: whereValues.add(null);
198: }
199: temp = temp.substring(andPos + 5);
200: if (temp.toLowerCase().indexOf(" and ") == -1) {
201: strTokenAdd = temp.trim();
202: int delimiter4 = strTokenAdd
203: .indexOf("=");
204: if (delimiter4 != -1) {
205: String valueAdd = strTokenAdd
206: .substring(delimiter4 + 1)
207: .trim();
208: valueAdd = Utils
209: .handleQuotedString(valueAdd);
210: whereCols.add(strTokenAdd
211: .substring(0, delimiter4)
212: .trim());
213: valueAdd = Utils
214: .replaceAll(valueAdd,
215: COMMA_ESCAPE, ",");
216: valueAdd = Utils
217: .replaceAll(valueAdd,
218: QUOTE_ESCAPE, "'");
219: whereValues.add(valueAdd);
220: } else {
221: int delimiter3 = strTokenAdd
222: .toLowerCase().indexOf(
223: " is ");
224: whereCols.add(strTokenAdd
225: .substring(0, delimiter3)
226: .trim());
227: whereValues.add(null);
228: }
229: break out;
230: }
231:
232: } while (true);
233:
234: } else {
235: int delimiter = strToken.indexOf("=");
236: if (delimiter != -1) {
237: String value = strToken.substring(
238: delimiter + 1).trim();
239: value = Utils.handleQuotedString(value);
240: whereCols.add(strToken.substring(0,
241: delimiter).trim());
242: value = Utils.replaceAll(value,
243: COMMA_ESCAPE, ",");
244: value = Utils.replaceAll(value,
245: QUOTE_ESCAPE, "'");
246: whereValues.add(value);
247: } else {
248: int delimiter1 = strToken.toLowerCase()
249: .indexOf(" is ");
250: whereCols.add(strToken.substring(0,
251: delimiter1).trim());
252: whereValues.add(null);
253: }
254: }
255: }
256: columnWhereNames = new String[whereCols.size()];
257: columnWhereValues = new String[whereValues.size()];
258: whereCols.copyInto(columnWhereNames);
259: whereValues.copyInto(columnWhereValues);
260: }
261: }
262: //INSERT
263: if (upperSql.startsWith("INSERT ")) {
264: if (upperSql.lastIndexOf(" VALUES") == -1) {
265: throw new Exception(
266: "Malformed SQL. Missing VALUES statement.");
267: }
268: sqlType = INSERT;
269: int intoPos = 0;
270: if (upperSql.indexOf(" INTO ") != -1)
271: intoPos = upperSql.indexOf(" INTO ") + 6;
272: else
273: intoPos = upperSql.indexOf("INSERT ") + 7;
274: int bracketPos = upperSql.indexOf("(");
275: int lastBracketPos = upperSql.indexOf(")");
276: tableName = sql.substring(intoPos, bracketPos).trim();
277:
278: Vector cols = new Vector();
279: StringTokenizer tokenizer = new StringTokenizer(upperSql
280: .substring(bracketPos + 1, lastBracketPos), ",");
281: while (tokenizer.hasMoreTokens()) {
282: cols.add(tokenizer.nextToken().trim());
283: }
284: columnNames = new String[cols.size()];
285: cols.copyInto(columnNames);
286:
287: int valuesPos = upperSql.indexOf("VALUES");
288: String endStatement = sql.substring(valuesPos + 6).trim();
289: bracketPos = endStatement.indexOf("(");
290: lastBracketPos = endStatement.lastIndexOf(")");
291: Vector values = new Vector();
292: StringTokenizer tokenizer2 = new StringTokenizer(
293: endStatement.substring(bracketPos + 1,
294: lastBracketPos), ",");
295: while (tokenizer2.hasMoreTokens()) {
296: String value = tokenizer2.nextToken().trim();
297: value = Utils.handleQuotedString(value);
298: value = Utils.replaceAll(value, COMMA_ESCAPE, ",");
299: value = Utils.replaceAll(value, QUOTE_ESCAPE, "'");
300: values.add(value);
301: }
302: columnValues = new String[values.size()];
303: values.copyInto(columnValues);
304: }
305:
306: //UPDATE
307: if (upperSql.startsWith("UPDATE ")) {
308: if (upperSql.lastIndexOf(" SET ") == -1)
309: throw new Exception(
310: "Malformed SQL. Missing SET statement.");
311: sqlType = UPDATE;
312: int updatePos = upperSql.indexOf("UPDATE");
313: int setPos = upperSql.indexOf(" SET ");
314: int equalPos = upperSql.indexOf("=");
315: int wherePos = upperSql.indexOf(" WHERE ");
316: tableName = sql.substring(updatePos + 6, setPos).trim();
317:
318: String setString = "";
319: if (wherePos != -1)
320: setString = sql.substring(setPos + 5, wherePos);
321: else
322: setString = sql.substring(setPos + 5, sql.length());
323: StringTokenizer tokenizerSet = new StringTokenizer(
324: setString, ",");
325: Vector setNames = new Vector();
326: Vector setValues = new Vector();
327:
328: while (tokenizerSet.hasMoreTokens()) {
329: String strToken = tokenizerSet.nextToken();
330: int delimiter = strToken.indexOf("=");
331: setNames.add(strToken.substring(0, delimiter).trim());
332: String value = strToken.substring(delimiter + 1).trim();
333: value = Utils.handleQuotedString(value);
334: value = Utils.replaceAll(value, COMMA_ESCAPE, ",");
335: value = Utils.replaceAll(value, QUOTE_ESCAPE, "'");
336: setValues.add(value);
337: }
338:
339: columnNames = new String[setNames.size()];
340: columnValues = new String[setValues.size()];
341: setNames.copyInto(columnNames);
342: setValues.copyInto(columnValues);
343: if (wherePos != -1) {
344: String strWhere = sql.substring(wherePos + 6).trim();
345: Vector whereCols = new Vector();
346: Vector whereValues = new Vector();
347: StringTokenizer tokenizerWhere = new StringTokenizer(
348: strWhere, ",");
349:
350: while (tokenizerWhere.hasMoreTokens()) {
351: String strToken = tokenizerWhere.nextToken();
352: if (strToken.toLowerCase().indexOf(" and ") != -1) {
353: String temp = strToken;
354: int andPos = 0;
355: out: do {
356: andPos = temp.toLowerCase()
357: .indexOf(" and ");
358: String strTokenAdd;
359: if (andPos != -1)
360: strTokenAdd = temp.substring(0, andPos)
361: .trim();
362: else
363: strTokenAdd = temp.trim();
364: int delimiter2 = strTokenAdd.indexOf("=");
365: if (delimiter2 != -1) {
366: String valueAdd = strTokenAdd
367: .substring(delimiter2 + 1)
368: .trim();
369: valueAdd = Utils
370: .handleQuotedString(valueAdd);
371: whereCols.add(strTokenAdd.substring(0,
372: delimiter2).trim());
373: valueAdd = Utils.replaceAll(valueAdd,
374: COMMA_ESCAPE, ",");
375: valueAdd = Utils.replaceAll(valueAdd,
376: QUOTE_ESCAPE, "'");
377: whereValues.add(valueAdd);
378: } else {
379: int delimiter3 = strTokenAdd
380: .toLowerCase().indexOf(" is ");
381: whereCols.add(strTokenAdd.substring(0,
382: delimiter3).trim());
383: whereValues.add(null);
384: }
385: temp = temp.substring(andPos + 5);
386: if (temp.toLowerCase().indexOf(" and ") == -1) {
387: strTokenAdd = temp.trim();
388: int delimiter4 = strTokenAdd
389: .indexOf("=");
390: if (delimiter4 != -1) {
391: String valueAdd = strTokenAdd
392: .substring(delimiter4 + 1)
393: .trim();
394: valueAdd = Utils
395: .handleQuotedString(valueAdd);
396: whereCols.add(strTokenAdd
397: .substring(0, delimiter4)
398: .trim());
399: valueAdd = Utils
400: .replaceAll(valueAdd,
401: COMMA_ESCAPE, ",");
402: valueAdd = Utils
403: .replaceAll(valueAdd,
404: QUOTE_ESCAPE, "'");
405: whereValues.add(valueAdd);
406: } else {
407: int delimiter3 = strTokenAdd
408: .toLowerCase().indexOf(
409: " is ");
410: whereCols.add(strTokenAdd
411: .substring(0, delimiter3)
412: .trim());
413: whereValues.add(null);
414: }
415: break out;
416: }
417:
418: } while (true);
419:
420: } else {
421: int delimiter = strToken.indexOf("=");
422: if (delimiter != -1) {
423: String value = strToken.substring(
424: delimiter + 1).trim();
425: value = Utils.handleQuotedString(value);
426: whereCols.add(strToken.substring(0,
427: delimiter).trim());
428: value = Utils.replaceAll(value,
429: COMMA_ESCAPE, ",");
430: value = Utils.replaceAll(value,
431: QUOTE_ESCAPE, "'");
432: whereValues.add(value);
433: } else {
434: int delimiter1 = strToken.toLowerCase()
435: .indexOf(" is ");
436: whereCols.add(strToken.substring(0,
437: delimiter1).trim());
438: whereValues.add(null);
439: }
440: }
441: }
442: columnWhereNames = new String[whereCols.size()];
443: columnWhereValues = new String[whereValues.size()];
444: whereCols.copyInto(columnWhereNames);
445: whereValues.copyInto(columnWhereValues);
446: }
447: }
448:
449: }
450:
451: }
|