KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > webdocwf > util > loader > SqlParser


1 /**
2     Copyright (C) 2002-2003 Together
3
4     This library is free software; you can redistribute it and/or
5     modify it under the terms of the GNU Lesser General Public
6     License as published by the Free Software Foundation; either
7     version 2.1 of the License, or (at your option) any later version.
8
9     This library is distributed in the hope that it will be useful,
10     but WITHOUT ANY WARRANTY; without even the implied warranty of
11     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
12     Lesser General Public License for more details.
13
14     You should have received a copy of the GNU Lesser General Public
15     License along with this library; if not, write to the Free Software
16     Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
17
18 */

19 package org.webdocwf.util.loader;
20
21 import java.io.*;
22 import java.util.*;
23 import java.sql.Statement JavaDoc;
24
25 /**
26  * Class is used for parsing sql statements.
27  *
28  * @author Zoran Milakovic
29  */

30 public class SqlParser {
31
32   public static final String JavaDoc INSERT = "insert";
33   public static final String JavaDoc UPDATE = "update";
34   public static final String JavaDoc SELECT = "select";
35
36   private static final String JavaDoc QUOTE_ESCAPE = "''";
37   private static final String JavaDoc COMMA_ESCAPE = "~#####1~";
38
39
40   private ArrayList binaryStreamObjectList = new ArrayList();
41
42   public String JavaDoc tableName;
43
44   public String JavaDoc whereStatement;
45
46   public String JavaDoc sqlType;
47
48   public String JavaDoc[] columnNames;
49
50   public String JavaDoc[] columnValues;
51
52   public String JavaDoc[] columnWhereNames;
53
54   public String JavaDoc[] columnWhereValues;
55
56   /**
57    *Gets the tableName attribute of the SqlParser object
58    *
59    * @return The tableName value
60    * @since
61    */

62   public String JavaDoc getTableName() {
63     return tableName;
64   }
65
66   /**
67    * Gets the columnNames attribute of the SqlParser object
68    *
69    * @return The columnNames value
70    * @since
71    */

72   public String JavaDoc[] getColumnNames() {
73     return columnNames;
74   }
75
76   public String JavaDoc[] getWhereColumnNames() {
77     return columnWhereNames;
78   }
79
80   public String JavaDoc[] getWhereColumnValues() {
81     return columnWhereValues;
82   }
83
84   public String JavaDoc[] getColumnValues() {
85     return columnValues;
86   }
87
88   /**
89    * Parse sql statement.
90    *
91    * @param sql defines SQL statement
92    * @exception Exception Description of Exception
93    * @since
94    */

95   public void parse(String JavaDoc sql) throws Exception JavaDoc {
96     sql = sql.trim();
97     tableName = null;
98     columnNames = new String JavaDoc[0];
99     columnValues = new String JavaDoc[0];
100     columnWhereNames = new String JavaDoc[0];
101     columnWhereValues = new String JavaDoc[0];
102     whereStatement = null;
103     sqlType = null;
104     sql = sql.trim();
105
106 //replace comma(,) in values between quotes(')
107
StringTokenizer tokQuote = new StringTokenizer(sql.toString(), "'", true);
108     StringBuffer JavaDoc sb = new StringBuffer JavaDoc();
109     boolean openParent1 = false;
110     while (tokQuote.hasMoreTokens()) {
111       String JavaDoc next = tokQuote.nextToken();
112       if (openParent1)
113         next = Utils.replaceAll(next, ",", COMMA_ESCAPE);
114       sb.append(next);
115       if (next.equalsIgnoreCase("'")) {
116         if (openParent1 == true)
117           openParent1 = false;
118         else
119           openParent1 = true;
120       }
121     }
122 //END replacement
123
sql = sb.toString();
124     String JavaDoc upperSql = sql.toUpperCase();
125
126
127 //handle unsupported statements
128
if (upperSql.startsWith("ALTER "))
129       throw new Exception JavaDoc("ALTER TABLE statements are not supported.");
130     if (upperSql.startsWith("DROP "))
131       throw new Exception JavaDoc("DROP statements are not supported.");
132
133 //SELECT
134
if ( upperSql.startsWith("SELECT ") ) {
135       if (upperSql.lastIndexOf(" FROM ") == -1) {
136         throw new Exception JavaDoc("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.substring(7,
149           fromPos), ",");
150
151       while (tokenizer.hasMoreTokens()) {
152         cols.add(tokenizer.nextToken().trim());
153       }
154
155       columnNames = new String JavaDoc[cols.size()];
156       cols.copyInto(columnNames);
157       if (wherePos != -1) {
158         String JavaDoc strWhere = sql.substring(wherePos + 7);
159         Vector whereCols = new Vector();
160         Vector whereValues = new Vector();
161         StringTokenizer tokenizerWhere = new StringTokenizer(strWhere, ",");
162
163         while (tokenizerWhere.hasMoreTokens()) {
164           String JavaDoc strToken = tokenizerWhere.nextToken();
165           if (strToken.toLowerCase().indexOf(" and ") != -1) {
166             String JavaDoc temp = strToken;
167             int andPos = 0;
168             out:
169             do {
170               andPos = temp.toLowerCase().indexOf(" and ");
171               String JavaDoc strTokenAdd;
172               if (andPos != -1)
173                 strTokenAdd = temp.substring(0, andPos).trim();
174               else
175                 strTokenAdd = temp.trim();
176               int delimiter2 = strTokenAdd.indexOf("=");
177               if (delimiter2 != -1) {
178                 String JavaDoc valueAdd = strTokenAdd.substring(delimiter2 + 1).trim();
179                 valueAdd = Utils.handleQuotedString(valueAdd);
180                 whereCols.add(strTokenAdd.substring(0, delimiter2).trim());
181                 valueAdd = Utils.replaceAll(valueAdd, COMMA_ESCAPE, ",");
182                 valueAdd = Utils.replaceAll(valueAdd, QUOTE_ESCAPE, "'");
183                 whereValues.add(valueAdd);
184               }
185               else {
186                 int delimiter3 = strTokenAdd.toLowerCase().indexOf(" is ");
187                 whereCols.add(strTokenAdd.substring(0, delimiter3).trim());
188                 whereValues.add(null);
189               }
190               temp = temp.substring(andPos + 5);
191               if (temp.toLowerCase().indexOf(" and ") == -1) {
192                 strTokenAdd = temp.trim();
193                 int delimiter4 = strTokenAdd.indexOf("=");
194                 if (delimiter4 != -1) {
195                   String JavaDoc valueAdd = strTokenAdd.substring(delimiter4 + 1).trim();
196                   valueAdd = Utils.handleQuotedString(valueAdd);
197                   whereCols.add(strTokenAdd.substring(0, delimiter4).trim());
198                   valueAdd = Utils.replaceAll(valueAdd, COMMA_ESCAPE, ",");
199                   valueAdd = Utils.replaceAll(valueAdd, QUOTE_ESCAPE, "'");
200                   whereValues.add(valueAdd);
201                 }
202                 else {
203                   int delimiter3 = strTokenAdd.toLowerCase().indexOf(" is ");
204                   whereCols.add(strTokenAdd.substring(0, delimiter3).trim());
205                   whereValues.add(null);
206                 }
207                 break out;
208               }
209
210             }
211             while (true);
212
213           }
214           else {
215             int delimiter = strToken.indexOf("=");
216             if (delimiter != -1) {
217               String JavaDoc value = strToken.substring(delimiter + 1).trim();
218               value = Utils.handleQuotedString(value);
219               whereCols.add(strToken.substring(0, delimiter).trim());
220               value = Utils.replaceAll(value, COMMA_ESCAPE, ",");
221               value = Utils.replaceAll(value, QUOTE_ESCAPE, "'");
222               whereValues.add(value);
223             }
224             else {
225               int delimiter1 = strToken.toLowerCase().indexOf(" is ");
226               whereCols.add(strToken.substring(0, delimiter1).trim());
227               whereValues.add(null);
228             }
229           }
230         }
231         columnWhereNames = new String JavaDoc[whereCols.size()];
232         columnWhereValues = new String JavaDoc[whereValues.size()];
233         whereCols.copyInto(columnWhereNames);
234         whereValues.copyInto(columnWhereValues);
235       }
236     }
237 //INSERT
238
if ( upperSql.startsWith("INSERT ") ) {
239       if (upperSql.lastIndexOf(" VALUES") == -1) {
240           throw new Exception JavaDoc("Malformed SQL. Missing VALUES statement.");
241         }
242         sqlType = INSERT;
243         int intoPos = 0;
244         if (upperSql.indexOf(" INTO ") != -1)
245           intoPos = upperSql.indexOf(" INTO ") + 6;
246         else
247           intoPos = upperSql.indexOf("INSERT ") + 7;
248         int bracketPos = upperSql.indexOf("(");
249         int lastBracketPos = upperSql.indexOf(")");
250         tableName = sql.substring(intoPos, bracketPos).trim();
251
252         Vector cols = new Vector();
253         StringTokenizer tokenizer = new StringTokenizer(upperSql.substring(
254             bracketPos + 1, lastBracketPos), ",");
255         while (tokenizer.hasMoreTokens()) {
256           cols.add(tokenizer.nextToken().trim());
257         }
258         columnNames = new String JavaDoc[cols.size()];
259         cols.copyInto(columnNames);
260
261         int valuesPos = upperSql.indexOf("VALUES");
262         String JavaDoc endStatement = sql.substring(valuesPos + 6).trim();
263         bracketPos = endStatement.indexOf("(");
264         lastBracketPos = endStatement.lastIndexOf(")");
265         Vector values = new Vector();
266         StringTokenizer tokenizer2 = new StringTokenizer(endStatement.substring(
267             bracketPos + 1, lastBracketPos), ",");
268         while (tokenizer2.hasMoreTokens()) {
269           String JavaDoc value = tokenizer2.nextToken().trim();
270           value = Utils.handleQuotedString(value);
271           value = Utils.replaceAll(value, COMMA_ESCAPE, ",");
272           value = Utils.replaceAll(value, QUOTE_ESCAPE, "'");
273           values.add(value);
274         }
275         columnValues = new String JavaDoc[values.size()];
276         values.copyInto(columnValues);
277     }
278
279
280 //UPDATE
281
if ( upperSql.startsWith("UPDATE ") ) {
282       if (upperSql.lastIndexOf(" SET ") == -1)
283             throw new Exception JavaDoc("Malformed SQL. Missing SET statement.");
284           sqlType = UPDATE;
285           int updatePos = upperSql.indexOf("UPDATE");
286           int setPos = upperSql.indexOf(" SET ");
287           int equalPos = upperSql.indexOf("=");
288           int wherePos = upperSql.indexOf(" WHERE ");
289           tableName = sql.substring(updatePos + 6, setPos).trim();
290
291           String JavaDoc setString = "";
292           if (wherePos != -1)
293             setString = sql.substring(setPos + 5, wherePos);
294           else
295             setString = sql.substring(setPos + 5, sql.length());
296           StringTokenizer tokenizerSet = new StringTokenizer(setString, ",");
297           Vector setNames = new Vector();
298           Vector setValues = new Vector();
299
300           while (tokenizerSet.hasMoreTokens()) {
301             String JavaDoc strToken = tokenizerSet.nextToken();
302             int delimiter = strToken.indexOf("=");
303             setNames.add(strToken.substring(0, delimiter).trim());
304             String JavaDoc value = strToken.substring(delimiter + 1).trim();
305             value = Utils.handleQuotedString(value);
306             value = Utils.replaceAll(value, COMMA_ESCAPE, ",");
307             value = Utils.replaceAll(value, QUOTE_ESCAPE, "'");
308             setValues.add(value);
309           }
310
311           columnNames = new String JavaDoc[setNames.size()];
312           columnValues = new String JavaDoc[setValues.size()];
313           setNames.copyInto(columnNames);
314           setValues.copyInto(columnValues);
315           if (wherePos != -1) {
316             String JavaDoc strWhere = sql.substring(wherePos + 6).trim();
317             Vector whereCols = new Vector();
318             Vector whereValues = new Vector();
319             StringTokenizer tokenizerWhere = new StringTokenizer(strWhere, ",");
320
321             while (tokenizerWhere.hasMoreTokens()) {
322               String JavaDoc strToken = tokenizerWhere.nextToken();
323               if (strToken.toLowerCase().indexOf(" and ") != -1) {
324                 String JavaDoc temp = strToken;
325                 int andPos = 0;
326                 out:
327                 do {
328                   andPos = temp.toLowerCase().indexOf(" and ");
329                   String JavaDoc strTokenAdd;
330                   if (andPos != -1)
331                     strTokenAdd = temp.substring(0, andPos).trim();
332                   else
333                     strTokenAdd = temp.trim();
334                   int delimiter2 = strTokenAdd.indexOf("=");
335                   if (delimiter2 != -1) {
336                     String JavaDoc valueAdd = strTokenAdd.substring(delimiter2 + 1).trim();
337                     valueAdd = Utils.handleQuotedString(valueAdd);
338                     whereCols.add(strTokenAdd.substring(0, delimiter2).trim());
339                     valueAdd = Utils.replaceAll(valueAdd, COMMA_ESCAPE, ",");
340                     valueAdd = Utils.replaceAll(valueAdd, QUOTE_ESCAPE, "'");
341                     whereValues.add(valueAdd);
342                   }
343                   else {
344                     int delimiter3 = strTokenAdd.toLowerCase().indexOf(" is ");
345                     whereCols.add(strTokenAdd.substring(0, delimiter3).trim());
346                     whereValues.add(null);
347                   }
348                   temp = temp.substring(andPos + 5);
349                   if (temp.toLowerCase().indexOf(" and ") == -1) {
350                     strTokenAdd = temp.trim();
351                     int delimiter4 = strTokenAdd.indexOf("=");
352                     if (delimiter4 != -1) {
353                       String JavaDoc valueAdd = strTokenAdd.substring(delimiter4 + 1).
354                           trim();
355                       valueAdd = Utils.handleQuotedString(valueAdd);
356                       whereCols.add(strTokenAdd.substring(0, delimiter4).trim());
357                       valueAdd = Utils.replaceAll(valueAdd, COMMA_ESCAPE, ",");
358                       valueAdd = Utils.replaceAll(valueAdd, QUOTE_ESCAPE, "'");
359                       whereValues.add(valueAdd);
360                     }
361                     else {
362                       int delimiter3 = strTokenAdd.toLowerCase().indexOf(" is ");
363                       whereCols.add(strTokenAdd.substring(0, delimiter3).trim());
364                       whereValues.add(null);
365                     }
366                     break out;
367                   }
368
369                 }
370                 while (true);
371
372               }
373               else {
374                 int delimiter = strToken.indexOf("=");
375                 if (delimiter != -1) {
376                   String JavaDoc value = strToken.substring(delimiter + 1).trim();
377                   value = Utils.handleQuotedString(value);
378                   whereCols.add(strToken.substring(0, delimiter).trim());
379                   value = Utils.replaceAll(value, COMMA_ESCAPE, ",");
380                   value = Utils.replaceAll(value, QUOTE_ESCAPE, "'");
381                   whereValues.add(value);
382                 }
383                 else {
384                   int delimiter1 = strToken.toLowerCase().indexOf(" is ");
385                   whereCols.add(strToken.substring(0, delimiter1).trim());
386                   whereValues.add(null);
387                 }
388               }
389             }
390             columnWhereNames = new String JavaDoc[whereCols.size()];
391             columnWhereValues = new String JavaDoc[whereValues.size()];
392             whereCols.copyInto(columnWhereNames);
393             whereValues.copyInto(columnWhereValues);
394           }
395     }
396
397   }
398
399
400 }
401
Popular Tags