|
1
19
20 package org.webdocwf.util.i18njdbc;
21
22
23 import java.util.*;
24 import java.sql.Statement  ;
25
26
32 public class I18nSqlParser {
33
34 public static final String CREATE_TABLE = "create_table";
35 public static final String INSERT = "insert";
36 public static final String UPDATE = "update";
37 public static final String SELECT = "select";
38 public static final String DELETE = "delete";
39
40
41 public static final String QUOTE_ESCAPE = "''";
42 private static final String DOUBLE_QUOTE_ESCAPE = "\"";
43 private static final String COMMA_ESCAPE = "~#####1~";
44
45 public static final String BINARY_STREAM_OBJECT = "I18nDriverBinaryStreamObject";
46
47 private ArrayList binaryStreamObjectList = new ArrayList();
48
49 private HashMap oldValues = new HashMap();
50
51 public String tableName;
52
53 public String whereStatement;
54
55 public String sqlType;
56
57 public String [] columnNames;
58
59 public String [] columnValues;
60
61 public String [] columnWhereNames;
62
63 public String [] columnWhereValues;
64
65
71 public String getTableName() {
72 return tableName;
73 }
74
75
81 public String [] getColumnNames() {
82 return columnNames;
83 }
84
85 public String [] getWhereColumnNames() {
86 return columnWhereNames;
87 }
88
89 public String [] getWhereColumnValues() {
90 return columnWhereValues;
91 }
92
93 public String [] getColumnValues() {
94 return columnValues;
95 }
96
97
104 public void parse(Statement statement) throws Exception {
105 String sql = "";
106 if ( statement instanceof I18nStatement )
107 sql = ((I18nStatement)statement).getSqlStatement();
108 else if( statement instanceof I18nPreparedStatement)
109 sql = ((I18nPreparedStatement)statement).getSqlStatement();
110 tableName = null;
111 columnNames = new String [0];
112 columnValues = new String [0];
113 columnWhereNames = new String [0];
114 columnWhereValues = new String [0];
115 whereStatement = null;
116 sqlType = null;
117
118 if( sql.indexOf("/*") != -1 ) {
120 StringBuffer buf = new StringBuffer ( sql );
121 buf.delete( sql.indexOf("/*") , sql.indexOf("*/")+2 );
122 sql = buf.toString();
123 }
124 sql = sql.trim();
125
126 I18nDriver.log("sql = "+sql);
127
128 oldValues.clear();
129 StringTokenizer tokQuote = new StringTokenizer(sql.toString(), "'", true);
132 StringBuffer sb = new StringBuffer ();
133 boolean openParent1 = false;
134 while (tokQuote.hasMoreTokens()) {
135 String next = tokQuote.nextToken();
137 if (openParent1) {
138 next = Utils.replaceAll(next, ",", COMMA_ESCAPE);
139 next = Utils.replaceKeywords(next, oldValues);
140 }
141 sb.append(next);
142 if (next.equalsIgnoreCase("'")) {
143 if (openParent1 == true)
144 openParent1 = false;
145 else
146 openParent1 = true;
147 }
148 }
149 sql = sb.toString();
151 String upperSql = sql.toUpperCase();
152
153
154 if (upperSql.startsWith("ALTER "))
156 throw new Exception ("ALTER TABLE statements are not supported.");
157 if (upperSql.startsWith("DROP "))
158 throw new Exception ("DROP statements are not supported.");
159
160
161
162 if ( upperSql.startsWith("SELECT ") ) {
164 if (upperSql.lastIndexOf(" FROM ") == -1) {
165 throw new Exception ("Malformed SQL. Missing FROM statement.");
166 }
167
168 sqlType = SELECT;
169 int fromPos = upperSql.lastIndexOf(" FROM ");
170 int wherePos = upperSql.lastIndexOf(" WHERE ");
171 if (wherePos == -1)
172 tableName = sql.substring(fromPos + 6).trim();
173 else
174 tableName = sql.substring(fromPos + 6, wherePos).trim();
175
176 Vector cols = new Vector();
177 StringTokenizer tokenizer = new StringTokenizer(upperSql.substring(7,
178 fromPos), ",");
179
180 while (tokenizer.hasMoreTokens()) {
181 cols.add(tokenizer.nextToken().trim());
182 }
183
184 columnNames = new String [cols.size()];
185 cols.copyInto(columnNames);
186 if (wherePos != -1) {
187 String strWhere = sql.substring(wherePos + 7);
188 Vector whereCols = new Vector();
189 Vector whereValues = new Vector();
190 StringTokenizer tokenizerWhere = new StringTokenizer(strWhere, ",");
191
192 while (tokenizerWhere.hasMoreTokens()) {
193 String strToken = tokenizerWhere.nextToken();
194 if (strToken.toLowerCase().indexOf(" and ") != -1) {
195 String temp = strToken;
196 int andPos = 0;
197 out:
198 do {
199 andPos = temp.toLowerCase().indexOf(" and ");
200 String strTokenAdd;
201 if (andPos != -1)
202 strTokenAdd = temp.substring(0, andPos).trim();
203 else
204 strTokenAdd = temp.trim();
205 int delimiter2 = strTokenAdd.indexOf("=");
206 if (delimiter2 != -1) {
207 String valueAdd = strTokenAdd.substring(delimiter2 + 1).trim();
208 valueAdd = Utils.handleQuotedString(valueAdd);
209 whereCols.add(strTokenAdd.substring(0, delimiter2).trim());
210 valueAdd = Utils.replaceAll(valueAdd, COMMA_ESCAPE, ",");
211 valueAdd = Utils.replaceAll(valueAdd, QUOTE_ESCAPE, "'");
212 valueAdd = Utils.replaceKeywordsBack(valueAdd, oldValues);
213 whereValues.add(valueAdd);
214 }
215 else {
216 int delimiter3 = strTokenAdd.toLowerCase().indexOf(" is ");
217 whereCols.add(strTokenAdd.substring(0, delimiter3).trim());
218 whereValues.add(null);
219 }
220 temp = temp.substring(andPos + 5);
221 if (temp.toLowerCase().indexOf(" and ") == -1) {
222 strTokenAdd = temp.trim();
223 int delimiter4 = strTokenAdd.indexOf("=");
224 if (delimiter4 != -1) {
225 String valueAdd = strTokenAdd.substring(delimiter4 + 1).trim();
226 valueAdd = Utils.handleQuotedString(valueAdd);
227 whereCols.add(strTokenAdd.substring(0, delimiter4).trim());
228 valueAdd = Utils.replaceAll(valueAdd, COMMA_ESCAPE, ",");
229 valueAdd = Utils.replaceAll(valueAdd, QUOTE_ESCAPE, "'");
230 valueAdd = Utils.replaceKeywordsBack(valueAdd, oldValues);
231 whereValues.add(valueAdd);
232 }
233 else {
234 int delimiter3 = strTokenAdd.toLowerCase().indexOf(" is ");
235 whereCols.add(strTokenAdd.substring(0, delimiter3).trim());
236 whereValues.add(null);
237 }
238 break out;
239 }
240
241 }
242 while (true);
243
244 }
245 else {
246 int delimiter = strToken.indexOf("=");
247 if (delimiter != -1) {
248 String value = strToken.substring(delimiter + 1).trim();
249 value = Utils.handleQuotedString(value);
250 whereCols.add(strToken.substring(0, delimiter).trim());
251 value = Utils.replaceAll(value, COMMA_ESCAPE, ",");
252 value = Utils.replaceAll(value, QUOTE_ESCAPE, "'");
253 value = Utils.replaceKeywordsBack(value, oldValues);
254 whereValues.add(value);
255 }
256 else {
257 int delimiter1 = strToken.toLowerCase().indexOf(" is ");
258 whereCols.add(strToken.substring(0, delimiter1).trim());
259 whereValues.add(null);
260 }
261 }
262 }
263 columnWhereNames = new String [whereCols.size()];
264 columnWhereValues = new String [whereValues.size()];
265 whereCols.copyInto(columnWhereNames);
266 whereValues.copyInto(columnWhereValues);
267 }
268 }
269 else if ( upperSql.startsWith("DELETE ") ) {
271 if (upperSql.lastIndexOf(" FROM ") == -1) {
272 throw new Exception ("Malformed SQL. Missing FROM statement.");
273 }
274
275 sqlType = DELETE;
276 int fromPos = upperSql.lastIndexOf(" FROM ");
277 int wherePos = upperSql.lastIndexOf(" WHERE ");
278 if (wherePos == -1)
279 tableName = sql.substring(fromPos + 6).trim();
280 else
281 tableName = sql.substring(fromPos + 6, wherePos).trim();
282
283
291 if (wherePos != -1) {
294 String strWhere = sql.substring(wherePos + 7);
295 Vector whereCols = new Vector();
296 Vector whereValues = new Vector();
297 StringTokenizer tokenizerWhere = new StringTokenizer(strWhere, ",");
298
299 while (tokenizerWhere.hasMoreTokens()) {
300 String strToken = tokenizerWhere.nextToken();
301 if (strToken.toLowerCase().indexOf(" and ") != -1) {
302 String temp = strToken;
303 int andPos = 0;
304 out1:
305 do {
306 andPos = temp.toLowerCase().indexOf(" and ");
307 String strTokenAdd;
308 if (andPos != -1)
309 strTokenAdd = temp.substring(0, andPos).trim();
310 else
311 strTokenAdd = temp.trim();
312 int delimiter2 = strTokenAdd.indexOf("=");
313 if (delimiter2 != -1) {
314 String valueAdd = strTokenAdd.substring(delimiter2 + 1).trim();
315 valueAdd = Utils.handleQuotedString(valueAdd);
316 whereCols.add(strTokenAdd.substring(0, delimiter2).trim());
317 valueAdd = Utils.replaceAll(valueAdd, COMMA_ESCAPE, ",");
318 valueAdd = Utils.replaceAll(valueAdd, QUOTE_ESCAPE, "'");
319 valueAdd = Utils.replaceKeywordsBack(valueAdd, oldValues);
320 whereValues.add(valueAdd);
321 }
322 else {
323 int delimiter3 = strTokenAdd.toLowerCase().indexOf(" is ");
324 whereCols.add(strTokenAdd.substring(0, delimiter3).trim());
325 whereValues.add(null);
326 }
327 temp = temp.substring(andPos + 5);
328 if (temp.toLowerCase().indexOf(" and ") == -1) {
329 strTokenAdd = temp.trim();
330 int delimiter4 = strTokenAdd.indexOf("=");
331 if (delimiter4 != -1) {
332 String valueAdd = strTokenAdd.substring(delimiter4 + 1).trim();
333 valueAdd = Utils.handleQuotedString(valueAdd);
334 whereCols.add(strTokenAdd.substring(0, delimiter4).trim());
335 valueAdd = Utils.replaceAll(valueAdd, COMMA_ESCAPE, ",");
336 valueAdd = Utils.replaceAll(valueAdd, QUOTE_ESCAPE, "'");
337 valueAdd = Utils.replaceKeywordsBack(valueAdd, oldValues);
338 whereValues.add(valueAdd);
339 }
340 else {
341 int delimiter3 = strTokenAdd.toLowerCase().indexOf(" is ");
342 whereCols.add(strTokenAdd.substring(0, delimiter3).trim());
343 whereValues.add(null);
344 }
345 break out1;
346 }
347
348 }
349 while (true);
350
351 }
352 else {
353 int delimiter = strToken.indexOf("=");
354 if (delimiter != -1) {
355 String value = strToken.substring(delimiter + 1).trim();
356 value = Utils.handleQuotedString(value);
357 whereCols.add(strToken.substring(0, delimiter).trim());
358 value = Utils.replaceAll(value, COMMA_ESCAPE, ",");
359 value = Utils.replaceAll(value, QUOTE_ESCAPE, "'");
360 value = Utils.replaceKeywordsBack(value, oldValues);
361 whereValues.add(value);
362 }
363 else {
364 int delimiter1 = strToken.toLowerCase().indexOf(" is ");
365 whereCols.add(strToken.substring(0, delimiter1).trim());
366 whereValues.add(null);
367 }
368 }
369 }
370 columnWhereNames = new String [whereCols.size()];
371 columnWhereValues = new String [whereValues.size()];
372 whereCols.copyInto(columnWhereNames);
373 whereValues.copyInto(columnWhereValues);
374 }
375 }
376
377 else if ( upperSql.startsWith("INSERT ") ) {
379 if (upperSql.lastIndexOf(" VALUES") == -1) {
380 throw new Exception ("Malformed SQL. Missing VALUES statement.");
381 }
382 sqlType = INSERT;
383 int intoPos = 0;
384 if (upperSql.indexOf(" INTO ") != -1)
385 intoPos = upperSql.indexOf(" INTO ") + 6;
386 else
387 intoPos = upperSql.indexOf("INSERT ") + 7;
388 int bracketPos = upperSql.indexOf("(");
389 int lastBracketPos = upperSql.indexOf(")");
390 tableName = sql.substring(intoPos, bracketPos).trim();
391
392 Vector cols = new Vector();
393 StringTokenizer tokenizer = new StringTokenizer(upperSql.substring(
394 bracketPos + 1, lastBracketPos), ",");
395 while (tokenizer.hasMoreTokens()) {
396 cols.add(tokenizer.nextToken().trim());
397 }
398 columnNames = new String [cols.size()];
399 cols.copyInto(columnNames);
400
401 int valuesPos = upperSql.indexOf("VALUES");
402 String endStatement = sql.substring(valuesPos + 6).trim();
403 bracketPos = endStatement.indexOf("(");
404 lastBracketPos = endStatement.lastIndexOf(")");
405 Vector values = new Vector();
406 StringTokenizer tokenizer2 = new StringTokenizer(endStatement.substring(
407 bracketPos + 1, lastBracketPos), ",");
408 while (tokenizer2.hasMoreTokens()) {
409 String value = tokenizer2.nextToken().trim();
410 value = Utils.handleQuotedString(value);
411 value = Utils.replaceAll(value, COMMA_ESCAPE, ",");
412 value = Utils.replaceAll(value, QUOTE_ESCAPE, "'");
413 value = Utils.replaceAll(value, "\"", DOUBLE_QUOTE_ESCAPE);
414 value = Utils.replaceKeywordsBack(value, oldValues);
415 value = Utils.handleBinaryString(value, this.binaryStreamObjectList);
416 values.add(value);
417 }
418 columnValues = new String [values.size()];
419 values.copyInto(columnValues);
420
422 }
428
429
430 else if ( upperSql.startsWith("UPDATE ") ) {
432 if (upperSql.lastIndexOf(" SET ") == -1)
433 throw new Exception ("Malformed SQL. Missing SET statement.");
434 sqlType = UPDATE;
435 int updatePos = upperSql.indexOf("UPDATE");
436 int setPos = upperSql.indexOf(" SET ");
437 int equalPos = upperSql.indexOf("=");
438 int wherePos = upperSql.indexOf(" WHERE ");
439 tableName = sql.substring(updatePos + 6, setPos).trim();
440
441 String setString = "";
442 if (wherePos != -1)
443 setString = sql.substring(setPos + 5, wherePos);
444 else
445 setString = sql.substring(setPos + 5, sql.length());
446 StringTokenizer tokenizerSet = new StringTokenizer(setString, ",");
447 Vector setNames = new Vector();
448 Vector setValues = new Vector();
449
450 while (tokenizerSet.hasMoreTokens()) {
451 String strToken = tokenizerSet.nextToken();
452 int delimiter = strToken.indexOf("=");
453 setNames.add(strToken.substring(0, delimiter).trim());
454 String value = strToken.substring(delimiter + 1).trim();
455 value = Utils.handleQuotedString(value);
456 value = Utils.replaceAll(value, COMMA_ESCAPE, ",");
457 value = Utils.replaceAll(value, QUOTE_ESCAPE, "'");
458 value = Utils.replaceAll(value, "\"", DOUBLE_QUOTE_ESCAPE);
459 value = Utils.replaceKeywordsBack(value, oldValues);
460 value = Utils.handleBinaryString(value, this.binaryStreamObjectList);
461 setValues.add(value);
462 }
463
464 columnNames = new String [setNames.size()];
465 columnValues = new String [setValues.size()];
466 setNames.copyInto(columnNames);
467 setValues.copyInto(columnValues);
468
470 if (wherePos != -1) {
476 String strWhere = sql.substring(wherePos + 6).trim();
477 Vector whereCols = new Vector();
478 Vector whereValues = new Vector();
479 StringTokenizer tokenizerWhere = new StringTokenizer(strWhere, ",");
480
481 while (tokenizerWhere.hasMoreTokens()) {
482 String strToken = tokenizerWhere.nextToken();
483 if (strToken.toLowerCase().indexOf(" and ") != -1) {
484 String temp = strToken;
485 int andPos = 0;
486 out:
487 do {
488 andPos = temp.toLowerCase().indexOf(" and ");
489 String strTokenAdd;
490 if (andPos != -1)
491 strTokenAdd = temp.substring(0, andPos).trim();
492 else
493 strTokenAdd = temp.trim();
494 int delimiter2 = strTokenAdd.indexOf("=");
495 if (delimiter2 != -1) {
496 String valueAdd = strTokenAdd.substring(delimiter2 + 1).trim();
497 valueAdd = Utils.handleQuotedString(valueAdd);
498 whereCols.add(strTokenAdd.substring(0, delimiter2).trim());
499 valueAdd = Utils.replaceAll(valueAdd, COMMA_ESCAPE, ",");
500 valueAdd = Utils.replaceAll(valueAdd, QUOTE_ESCAPE, "'");
501 valueAdd = Utils.replaceKeywordsBack(valueAdd, oldValues);
502 whereValues.add(valueAdd);
503 }
504 else {
505 int delimiter3 = strTokenAdd.toLowerCase().indexOf(" is ");
506 whereCols.add(strTokenAdd.substring(0, delimiter3).trim());
507 whereValues.add(null);
508 }
509 temp = temp.substring(andPos + 5);
510 if (temp.toLowerCase().indexOf(" and ") == -1) {
511 strTokenAdd = temp.trim();
512 int delimiter4 = strTokenAdd.indexOf("=");
513 if (delimiter4 != -1) {
514 String valueAdd = strTokenAdd.substring(delimiter4 + 1).
515 trim();
516 valueAdd = Utils.handleQuotedString(valueAdd);
517 whereCols.add(strTokenAdd.substring(0, delimiter4).trim());
518 valueAdd = Utils.replaceAll(valueAdd, COMMA_ESCAPE, ",");
519 valueAdd = Utils.replaceAll(valueAdd, QUOTE_ESCAPE, "'");
520 valueAdd = Utils.replaceKeywordsBack(valueAdd, oldValues);
521 whereValues.add(valueAdd);
522 }
523 else {
524 int delimiter3 = strTokenAdd.toLowerCase().indexOf(" is ");
525 whereCols.add(strTokenAdd.substring(0, delimiter3).trim());
526 whereValues.add(null);
527 }
528 break out;
529 }
530
531 }
532 while (true);
533
534 }
535 else {
536 int delimiter = strToken.indexOf("=");
537 if (delimiter != -1) {
538 String value = strToken.substring(delimiter + 1).trim();
539 value = Utils.handleQuotedString(value);
540 whereCols.add(strToken.substring(0, delimiter).trim());
541 value = Utils.replaceAll(value, COMMA_ESCAPE, ",");
542 value = Utils.replaceAll(value, QUOTE_ESCAPE, "'");
543 value = Utils.replaceKeywordsBack(value, oldValues);
544 whereValues.add(value);
545 }
546 else {
547 int delimiter1 = strToken.toLowerCase().indexOf(" is ");
548 whereCols.add(strToken.substring(0, delimiter1).trim());
549 whereValues.add(null);
550 }
551 }
552 }
553 columnWhereNames = new String [whereCols.size()];
554 columnWhereValues = new String [whereValues.size()];
555 whereCols.copyInto(columnWhereNames);
556 whereValues.copyInto(columnWhereValues);
557 }
558 }
559
560
561 else if ( upperSql.startsWith("CREATE TABLE ") ) {
563 sqlType = CREATE_TABLE;
564 int createPos = upperSql.indexOf("CREATE TABLE");
565 int tableStartPos = upperSql.indexOf("(");
566 int tableEndPos = upperSql.lastIndexOf(")");
567 tableName = sql.substring(createPos + 12, tableStartPos).trim();
568 String createString = sql.substring(tableStartPos + 1, tableEndPos).
569 trim();
570 StringTokenizer tokenizerCreate = new StringTokenizer(createString.
571 toUpperCase(), "\n");
572 Vector setColumnNames = new Vector();
573 while (tokenizerCreate.hasMoreTokens()) {
574 String strToken = tokenizerCreate.nextToken().trim();
575 int delimiter = strToken.indexOf(" ");
576 String typeOfColumn = "";
578 StringTokenizer stSpace = new StringTokenizer(strToken, " ");
579 while (stSpace.hasMoreTokens()) {
580 String nextStSpace = stSpace.nextToken();
581 }
585 if (strToken.toUpperCase().indexOf("CONSTRAINT") == -1) {
586 String columnName = strToken.substring(0, delimiter).trim();
587 setColumnNames.add(columnName + typeOfColumn);
588 }
589 }
590 columnNames = new String [setColumnNames.size()];
591 setColumnNames.copyInto(columnNames);
592
593 } else {
594 throw new Exception ("Malformed SQL. Wrong SQL statement.");
595 }
596
597 }
598
599
600
604 public void setBinaryStreamList(ArrayList list) {
605 this.binaryStreamObjectList = list;
606 }
607
608 }
609 |
Popular Tags |
Java API By Example, From Geeks To Geeks.