|
1
19 package org.webdocwf.util.loader;
20
21 import java.io.*;
22 import java.util.*;
23 import java.sql.Statement  ;
24
25
30 public class SqlParser {
31
32 public static final String INSERT = "insert";
33 public static final String UPDATE = "update";
34 public static final String SELECT = "select";
35
36 private static final String QUOTE_ESCAPE = "''";
37 private static final String COMMA_ESCAPE = "~#####1~";
38
39
40 private ArrayList binaryStreamObjectList = new ArrayList();
41
42 public String tableName;
43
44 public String whereStatement;
45
46 public String sqlType;
47
48 public String [] columnNames;
49
50 public String [] columnValues;
51
52 public String [] columnWhereNames;
53
54 public String [] columnWhereValues;
55
56
62 public String getTableName() {
63 return tableName;
64 }
65
66
72 public String [] getColumnNames() {
73 return columnNames;
74 }
75
76 public String [] getWhereColumnNames() {
77 return columnWhereNames;
78 }
79
80 public String [] getWhereColumnValues() {
81 return columnWhereValues;
82 }
83
84 public String [] getColumnValues() {
85 return columnValues;
86 }
87
88
95 public void parse(String sql) throws Exception {
96 sql = sql.trim();
97 tableName = null;
98 columnNames = new String [0];
99 columnValues = new String [0];
100 columnWhereNames = new String [0];
101 columnWhereValues = new String [0];
102 whereStatement = null;
103 sqlType = null;
104 sql = sql.trim();
105
106 StringTokenizer tokQuote = new StringTokenizer(sql.toString(), "'", true);
108 StringBuffer sb = new StringBuffer ();
109 boolean openParent1 = false;
110 while (tokQuote.hasMoreTokens()) {
111 String 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 sql = sb.toString();
124 String upperSql = sql.toUpperCase();
125
126
127 if (upperSql.startsWith("ALTER "))
129 throw new Exception ("ALTER TABLE statements are not supported.");
130 if (upperSql.startsWith("DROP "))
131 throw new Exception ("DROP statements are not supported.");
132
133 if ( upperSql.startsWith("SELECT ") ) {
135 if (upperSql.lastIndexOf(" FROM ") == -1) {
136 throw new Exception ("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 [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(strWhere, ",");
162
163 while (tokenizerWhere.hasMoreTokens()) {
164 String strToken = tokenizerWhere.nextToken();
165 if (strToken.toLowerCase().indexOf(" and ") != -1) {
166 String temp = strToken;
167 int andPos = 0;
168 out:
169 do {
170 andPos = temp.toLowerCase().indexOf(" and ");
171 String 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 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 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 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 [whereCols.size()];
232 columnWhereValues = new String [whereValues.size()];
233 whereCols.copyInto(columnWhereNames);
234 whereValues.copyInto(columnWhereValues);
235 }
236 }
237 if ( upperSql.startsWith("INSERT ") ) {
239 if (upperSql.lastIndexOf(" VALUES") == -1) {
240 throw new Exception ("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 [cols.size()];
259 cols.copyInto(columnNames);
260
261 int valuesPos = upperSql.indexOf("VALUES");
262 String 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 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 [values.size()];
276 values.copyInto(columnValues);
277 }
278
279
280 if ( upperSql.startsWith("UPDATE ") ) {
282 if (upperSql.lastIndexOf(" SET ") == -1)
283 throw new Exception ("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 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 strToken = tokenizerSet.nextToken();
302 int delimiter = strToken.indexOf("=");
303 setNames.add(strToken.substring(0, delimiter).trim());
304 String 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 [setNames.size()];
312 columnValues = new String [setValues.size()];
313 setNames.copyInto(columnNames);
314 setValues.copyInto(columnValues);
315 if (wherePos != -1) {
316 String 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 strToken = tokenizerWhere.nextToken();
323 if (strToken.toLowerCase().indexOf(" and ") != -1) {
324 String temp = strToken;
325 int andPos = 0;
326 out:
327 do {
328 andPos = temp.toLowerCase().indexOf(" and ");
329 String 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 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 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 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 [whereCols.size()];
391 columnWhereValues = new String [whereValues.size()];
392 whereCols.copyInto(columnWhereNames);
393 whereValues.copyInto(columnWhereValues);
394 }
395 }
396
397 }
398
399
400 }
401 |
Popular Tags |
Java API By Example, From Geeks To Geeks.