|
1
15
16 package org.webdocwf.util.xml;
17
18 import java.util.ArrayList  ;
19 import java.util.Vector ;
20 import java.util.StringTokenizer ;
21 import java.util.HashMap ;
22 import java.io.*;
23
24
29 public class XmlSqlParser {
30
35
36 public static final String CREATE_TABLE = "create_table";
37 public static final String DROP_TABLE = "drop_table";
38 public static final String INSERT = "insert";
39 public static final String UPDATE = "update";
40 public static final String SELECT = "select";
41 public static final String DELETE = "delete";
42
43 public static final String quoteEscape = "''";
44 private static final String commaEscape = "~#####1~";
45 public static final String equalEscape = "~EQUAL~";
46 public static final String atEscape = "~AT~";
47 public static final String slashEscape = "~SLASH~";
48
49 public static final String BINARY_STREAM_OBJECT =
50 "XmlDriverBinaryStreamObject";
51
52 private ArrayList binaryStreamObjectList = new ArrayList ();
53
54 private HashMap oldValues = new HashMap ();
55
56 private boolean isAutoCommit = true;
57
58 private String fileName = "";
59
60 public String tableName;
61
62 public String whereStatement;
63
64 public String sqlType;
65
66 private String sqlStatement = "";
67
68 public String [] columnNames;
69
70 public String [] primaryKeyColumns;
71
72 public String [] notnullColumns;
73
74 public String [] columnValues;
75
76 public String [] columnWhereNames;
77
78 public String [] columnWhereValues;
79
80 public XmlSqlParser() {
81 }
82
83 public XmlSqlParser( String fileName, boolean isAutoCommit ) {
84 this.fileName = fileName;
85 this.isAutoCommit = isAutoCommit;
86 }
87
88
94 public String getTableName() {
95 return tableName;
96 }
97
98
104 public String [] getNotnullColumns() {
105 return this.notnullColumns;
106 }
107
108
114 public String getSQLType() {
115 return this.sqlType;
116 }
117
118
124 public String [] getColumnNames() {
125 return columnNames;
126 }
127
128 public String [] getWhereColumnNames() {
129 return columnWhereNames;
130 }
131
132
136 public String [] getColumnValues() {
137 return columnValues;
138 }
139
140
144 public String [] getPrimaryKeys() {
145 return primaryKeyColumns;
146 }
147
148 public String [] getWhereColumnValues() {
149 return columnWhereValues;
150 }
151
152 public String getSqlStatement() {
153 return this.sqlStatement;
154 }
155
156
163 public void parse( String sql ) throws Exception {
164 this.sqlStatement = sql;
165 tableName = null;
166 columnNames = new String [0];
167 columnValues = new String [0];
168 columnWhereNames = new String [0];
169 columnWhereValues = new String [0];
170 whereStatement = null;
171 sqlType = null;
172
173 if ( sql.indexOf( "/*" ) != -1 ) {
175 StringBuffer buf = new StringBuffer ( sql );
176 buf.delete( sql.indexOf( "/*" ), sql.indexOf( "*/" ) + 2 );
177 sql = buf.toString();
178 }
179 sql = sql.trim();
180
181 oldValues.clear();
182 StringTokenizer tokQuote = new StringTokenizer ( sql.toString(), "'", true );
185 StringBuffer sb = new StringBuffer ();
186 boolean openParent1 = false;
187 while ( tokQuote.hasMoreTokens() ) {
188 String next = tokQuote.nextToken();
190 if ( openParent1 ) {
191 next = Utils.replaceAll( next, ",", commaEscape );
192 next = Utils.replaceAll( next, "=", equalEscape );
193 next = Utils.replaceAll( next, "@", atEscape );
194 next = Utils.replaceAll( next, "/", slashEscape );
195 next = Utils.replaceKeywords(next, oldValues );
196 }
197 sb.append( next );
198 if ( next.equalsIgnoreCase( "'" ) ) {
199 if ( openParent1 == true )
200 openParent1 = false;
201 else
202 openParent1 = true;
203 }
204 }
205
206 sql = sb.toString();
207 String upperSql = sql.toUpperCase();
208
209 XmlDriver.log("sql = "+sql);
210
211 if ( upperSql.startsWith( "ALTER " ) )
213 throw new Exception ( "ALTER TABLE statements are not supported." );
214
215 if ( upperSql.startsWith( "DROP TABLE" ) ) {
217 sqlType = DROP_TABLE;
218 int dropPos = upperSql.indexOf( "DROP TABLE" );
219 tableName = sql.substring( dropPos + 10 ).trim().toUpperCase();
220 }
221
222 else if ( upperSql.startsWith( "DELETE " ) ) {
224 sqlType = DELETE;
225 int deletePos = upperSql.indexOf( "DELETE" );
226 int wherePos = upperSql.indexOf( " WHERE " );
227 int fromPos = upperSql.lastIndexOf( " FROM " );
228 if ( wherePos == -1 )
229 tableName = sql.substring( fromPos + 6 ).trim().toUpperCase();
230 else
231 tableName = sql.substring( fromPos + 6, wherePos ).trim().toUpperCase();
232
233 if ( wherePos != -1 ) {
234 String strWhere = sql.substring( wherePos + 6 ).trim();
235 Vector whereCols = new Vector ();
236 Vector whereValues = new Vector ();
237 StringTokenizer tokenizerWhere = new StringTokenizer ( strWhere, "," );
238 while ( tokenizerWhere.hasMoreTokens() ) {
239 String strToken = tokenizerWhere.nextToken();
240 if ( strToken.toUpperCase().indexOf( " AND " ) != -1 ) {
241 String temp = strToken;
242 int andPos = 0;
243 out:
244 do {
245 andPos = temp.toUpperCase().indexOf( " AND " );
246 String strTokenAdd;
247 if ( andPos != -1 )
248 strTokenAdd = temp.substring( 0, andPos ).trim();
249 else
250 strTokenAdd = temp.trim();
251 int delimiter2 = strTokenAdd.indexOf( "=" );
252 if ( delimiter2 != -1 ) {
253 String valueAdd = strTokenAdd.substring( delimiter2 + 1 ).trim();
254 valueAdd = Utils.handleQuotedString(valueAdd);
255 whereCols.add( strTokenAdd.substring( 0, delimiter2 ).trim().toUpperCase() );
256 valueAdd = Utils.replaceAll( valueAdd, commaEscape, "," );
257 valueAdd = Utils.replaceAll( valueAdd, quoteEscape, "'" );
258 valueAdd = Utils.replaceKeywordsBack(valueAdd, oldValues);
259 whereValues.add( valueAdd );
260 }
261 else {
262 int delimiter3 = strTokenAdd.toLowerCase().indexOf( " is " );
263 whereCols.add( strTokenAdd.substring( 0, delimiter3 ).trim().
264 toUpperCase() );
265 whereValues.add( null );
266 }
267 temp = temp.substring( andPos + 5 );
268 if ( temp.toUpperCase().indexOf( " AND " ) == -1 ) {
269 strTokenAdd = temp.trim();
270 int delimiter4 = strTokenAdd.indexOf( "=" );
271 if ( delimiter4 != -1 ) {
272 String valueAdd = strTokenAdd.substring( delimiter4 + 1 ).
273 trim();
274 valueAdd = Utils.handleQuotedString(valueAdd);
275 whereCols.add( strTokenAdd.substring( 0, delimiter4 ).trim().toUpperCase() );
276 valueAdd = Utils.replaceAll( valueAdd, commaEscape, "," );
277 valueAdd = Utils.replaceAll( valueAdd, quoteEscape, "'" );
278 valueAdd = Utils.replaceKeywordsBack(valueAdd, oldValues);
279 whereValues.add( valueAdd );
280 }
281 else {
282 int delimiter3 = strTokenAdd.toLowerCase().indexOf( " is " );
283 whereCols.add( strTokenAdd.substring( 0, delimiter3 ).trim().
284 toUpperCase() );
285 whereValues.add( null );
286 }
287 break out;
288 }
289
290 }
291 while ( true );
292
293 }
294 else {
295 int delimiter = strToken.indexOf( "=" );
296 if ( delimiter != -1 ) {
297 String value = strToken.substring( delimiter + 1 ).trim();
298 value = Utils.handleQuotedString(value);
299 whereCols.add( strToken.substring( 0, delimiter ).trim().toUpperCase() );
300 value = Utils.replaceAll( value, commaEscape, "," );
301 value = Utils.replaceAll( value, quoteEscape, "'" );
302 value = Utils.replaceKeywordsBack(value, oldValues);
303 whereValues.add( value );
304 }
305 else {
306 int delimiter1 = strToken.toLowerCase().indexOf( " is " );
307 whereCols.add( strToken.substring( 0, delimiter1 ).trim().
308 toUpperCase() );
309 whereValues.add( null );
310 }
311 }
312 }
313 columnWhereNames = new String [whereCols.size()];
314 columnWhereValues = new String [whereValues.size()];
315 whereCols.copyInto( columnWhereNames );
316 whereValues.copyInto( columnWhereValues );
317 }
318 }
319
320 else if ( upperSql.startsWith( "SELECT " ) ) {
322
323 if ( upperSql.lastIndexOf( " FROM " ) == -1 ) {
324 throw new Exception ( "Malformed SQL. Missing FROM statement." );
325 }
326
327 sqlType = SELECT;
328 int fromPos = upperSql.lastIndexOf( " FROM " );
329 int wherePos = upperSql.lastIndexOf( " WHERE " );
330 if ( wherePos == -1 )
331 tableName = sql.substring( fromPos + 6 ).trim().toUpperCase();
332 else
333 tableName = sql.substring( fromPos + 6, wherePos ).trim().toUpperCase();
334 String columnNamesSql = sql.substring( 0, fromPos );
335 if ( columnNamesSql.indexOf( "*" ) == -1 ) {
336 Vector cols = new Vector ();
337 StringTokenizer tokenizer = new StringTokenizer ( upperSql.substring( 7,
338 fromPos ), "," );
339
340 while ( tokenizer.hasMoreTokens() ) {
341 cols.add( tokenizer.nextToken().trim() );
342 }
343
344 columnNames = new String [cols.size()];
345 cols.copyInto( columnNames );
346 }
347 else {
348 columnNames = ( String [] ) ( new XmlWriter( this.fileName,
349 this.isAutoCommit ).getTableProperties( this.tableName ).get( 0 ) );
350 }
351 if ( wherePos != -1 ) {
352 String strWhere = sql.substring( wherePos + 7 );
353 Vector whereCols = new Vector ();
354 Vector whereValues = new Vector ();
355 String strToken = strWhere;
356 if ( strToken.toUpperCase().indexOf( " AND " ) != -1 ) {
357 String temp = strToken;
358 int andPos = 0;
359 out:
360 do {
361 andPos = temp.toUpperCase().indexOf( " AND " );
362 String strTokenAdd;
363 if ( andPos != -1 )
364 strTokenAdd = temp.substring( 0, andPos ).trim();
365 else
366 strTokenAdd = temp.trim();
367 int delimiter2 = strTokenAdd.indexOf( "=" );
368 if ( delimiter2 != -1 ) {
369 String valueAdd = strTokenAdd.substring( delimiter2 + 1 ).trim();
370 valueAdd = Utils.handleQuotedString(valueAdd);
371 whereCols.add( strTokenAdd.substring( 0, delimiter2 ).trim().
372 toUpperCase() );
373 valueAdd = Utils.replaceAll( valueAdd, commaEscape, "," );
374 valueAdd = Utils.replaceAll( valueAdd, quoteEscape, "'" );
375 valueAdd = Utils.replaceKeywordsBack(valueAdd, oldValues);
376 whereValues.add( valueAdd );
377 }
378 else {
379 int delimiter3 = strTokenAdd.toLowerCase().indexOf( " is " );
380 whereCols.add( strTokenAdd.substring( 0, delimiter3 ).trim().
381 toUpperCase() );
382 whereValues.add( null );
383 }
384 temp = temp.substring( andPos + 5 );
385 if ( temp.toUpperCase().indexOf( " AND " ) == -1 ) {
386 strTokenAdd = temp.trim();
387 int delimiter4 = strTokenAdd.indexOf( "=" );
388 if ( delimiter4 != -1 ) {
389 String valueAdd = strTokenAdd.substring( delimiter4 + 1 ).trim();
390 valueAdd = Utils.handleQuotedString(valueAdd);
391 whereCols.add( strTokenAdd.substring( 0, delimiter4 ).trim().toUpperCase() );
392 valueAdd = Utils.replaceAll( valueAdd, commaEscape, "," );
393 valueAdd = Utils.replaceAll( valueAdd, quoteEscape, "'" );
394 valueAdd = Utils.replaceKeywordsBack(valueAdd, oldValues);
395 whereValues.add( valueAdd );
396 }
397 else {
398 int delimiter3 = strTokenAdd.toLowerCase().indexOf( " is " );
399 whereCols.add( strTokenAdd.substring( 0, delimiter3 ).trim().
400 toUpperCase() );
401 whereValues.add( null );
402 }
403 break out;
404 }
405
406 }
407 while ( true );
408
409 }
410 else {
411 int delimiter = strToken.indexOf( "=" );
412 if ( delimiter != -1 ) {
413 String value = strToken.substring( delimiter + 1 ).trim();
414 value = Utils.handleQuotedString(value);
415 whereCols.add( strToken.substring( 0, delimiter ).trim().toUpperCase() );
416 value = Utils.replaceAll( value, commaEscape, "," );
417 value = Utils.replaceAll( value, quoteEscape, "'" );
418 value = Utils.replaceKeywordsBack(value, oldValues);
419 whereValues.add( value );
420 }
421 else {
422 int delimiter1 = strToken.toLowerCase().indexOf( " is " );
423 whereCols.add( strToken.substring( 0, delimiter1 ).trim().
424 toUpperCase() );
425 whereValues.add( null );
426 }
427 }
428
429 columnWhereNames = new String [whereCols.size()];
430 columnWhereValues = new String [whereValues.size()];
431 whereCols.copyInto( columnWhereNames );
432 whereValues.copyInto( columnWhereValues );
433 }
434 }
435
436 else if ( upperSql.startsWith( "INSERT " ) ) {
438 if ( upperSql.lastIndexOf( " VALUES" ) == -1 ) {
439 throw new Exception ( "Malformed SQL. Missing VALUES statement." );
440 }
441 sqlType = INSERT;
442 int intoPos = 0;
443 if ( upperSql.indexOf( " INTO " ) != -1 )
444 intoPos = upperSql.indexOf( " INTO " ) + 6;
445 else
446 intoPos = upperSql.indexOf( "INSERT " ) + 7;
447 int bracketPos = upperSql.indexOf( "(" );
448 int lastBracketPos = upperSql.indexOf( ")" );
449 tableName = sql.substring( intoPos, bracketPos ).trim().toUpperCase();
450 Vector cols = new Vector ();
451 StringTokenizer tokenizer = new StringTokenizer ( upperSql.substring(
452 bracketPos + 1, lastBracketPos ), "," );
453 while ( tokenizer.hasMoreTokens() ) {
454 cols.add( tokenizer.nextToken().trim() );
455 }
456 columnNames = new String [cols.size()];
457 cols.copyInto( columnNames );
458
459 int valuesPos = upperSql.indexOf( "VALUES" );
460 String endStatement = sql.substring( valuesPos + 6 ).trim();
461 bracketPos = endStatement.indexOf( "(" );
462 lastBracketPos = endStatement.lastIndexOf( ")" );
463 Vector values = new Vector ();
464
465 StringTokenizer tokenizer2 = new StringTokenizer ( endStatement.substring(
466 bracketPos + 1, lastBracketPos ), "," );
467 while ( tokenizer2.hasMoreTokens() ) {
468 String value = tokenizer2.nextToken().trim();
469 value = Utils.handleQuotedString( value );
470 value = Utils.replaceAll( value, commaEscape, "," );
471 value = Utils.replaceAll( value, quoteEscape, "'" );
472 value = Utils.replaceKeywordsBack(value, oldValues);
473 value = Utils.handleBinaryString(value, this.binaryStreamObjectList);
474 values.add( value );
475 }
476
477 columnValues = new String [values.size()];
478 values.copyInto( columnValues );
479 }
480
481 else if ( upperSql.startsWith( "UPDATE " ) ) {
483 if ( upperSql.lastIndexOf( " SET " ) == -1 )
484 throw new Exception ( "Malformed SQL. Missing SET statement." );
485 sqlType = UPDATE;
486 int updatePos = upperSql.indexOf( "UPDATE" );
487 int setPos = upperSql.indexOf( " SET " );
488 int equalPos = upperSql.indexOf( "=" );
489 int wherePos = upperSql.indexOf( " WHERE " );
490 tableName = sql.substring( updatePos + 6, setPos ).trim().toUpperCase();
491 String setString = "";
492 if ( wherePos != -1 ) {
494 setString = sql.substring( setPos + 5, wherePos );
495 }
496 else {
497 setString = sql.substring( setPos + 5 );
498 }
499
500 StringTokenizer tokenizerSet = new StringTokenizer ( setString, "," );
501 Vector setNames = new Vector ();
502 Vector setValues = new Vector ();
503
504 while ( tokenizerSet.hasMoreTokens() ) {
505 String strToken = tokenizerSet.nextToken();
506 int delimiter = strToken.indexOf( "=" );
507 setNames.add( strToken.substring( 0, delimiter ).trim().toUpperCase() );
508 String value = strToken.substring( delimiter + 1 ).trim();
509 value = Utils.handleQuotedString(value);
510 value = Utils.replaceAll( value, commaEscape, "," );
511 value = Utils.replaceAll( value, quoteEscape, "'" );
512 value = Utils.replaceKeywordsBack(value, oldValues);
513 value = Utils.handleBinaryString(value, this.binaryStreamObjectList);
514 setValues.add( value );
515 }
516 columnNames = new String [setNames.size()];
517 columnValues = new String [setValues.size()];
518 setNames.copyInto( columnNames );
519 setValues.copyInto( columnValues );
520
521 if ( wherePos != -1 ) {
522 String strWhere = sql.substring( wherePos + 6 ).trim();
523 Vector whereCols = new Vector ();
524 Vector whereValues = new Vector ();
525 StringTokenizer tokenizerWhere = new StringTokenizer ( strWhere, "," );
526
527 while ( tokenizerWhere.hasMoreTokens() ) {
528 String strToken = tokenizerWhere.nextToken();
529 if ( strToken.toUpperCase().indexOf( " AND " ) != -1 ) {
530 String temp = strToken;
531 int andPos = 0;
532 out:
533 do {
534 andPos = temp.toUpperCase().indexOf( " AND " );
535 String strTokenAdd;
536 if ( andPos != -1 )
537 strTokenAdd = temp.substring( 0, andPos ).trim();
538 else
539 strTokenAdd = temp.trim();
540 int delimiter2 = strTokenAdd.indexOf( "=" );
541 if ( delimiter2 != -1 ) {
542 String valueAdd = strTokenAdd.substring( delimiter2 + 1 ).trim();
543 valueAdd = Utils.handleQuotedString(valueAdd);
544 whereCols.add( strTokenAdd.substring( 0, delimiter2 ).trim().toUpperCase() );
545 valueAdd = Utils.replaceAll( valueAdd, commaEscape, "," );
546 valueAdd = Utils.replaceAll( valueAdd, quoteEscape, "'" );
547 valueAdd = Utils.replaceKeywordsBack(valueAdd, oldValues);
548 whereValues.add( valueAdd );
549 }
550 else {
551 int delimiter3 = strTokenAdd.toLowerCase().indexOf( " is " );
552 whereCols.add( strTokenAdd.substring( 0, delimiter3 ).trim().
553 toUpperCase() );
554 whereValues.add( null );
555 }
556 temp = temp.substring( andPos + 5 );
557 if ( temp.toUpperCase().indexOf( " AND " ) == -1 ) {
558 strTokenAdd = temp.trim();
559 int delimiter4 = strTokenAdd.indexOf( "=" );
560 if ( delimiter4 != -1 ) {
561 String valueAdd = strTokenAdd.substring( delimiter4 + 1 ).
562 trim();
563 valueAdd = Utils.handleQuotedString(valueAdd);
564 whereCols.add( strTokenAdd.substring( 0, delimiter4 ).trim().toUpperCase() );
565 valueAdd = Utils.replaceAll( valueAdd, commaEscape, "," );
566 valueAdd = Utils.replaceAll( valueAdd, quoteEscape, "'" );
567 valueAdd = Utils.replaceKeywordsBack(valueAdd, oldValues);
568 whereValues.add( valueAdd );
569 }
570 else {
571 int delimiter3 = strTokenAdd.toLowerCase().indexOf( " is " );
572 whereCols.add( strTokenAdd.substring( 0, delimiter3 ).trim().
573 toUpperCase() );
574 whereValues.add( null );
575 }
576 break out;
577 }
578
579 }
580 while ( true );
581
582 }
583 else {
584 int delimiter = strToken.indexOf( "=" );
585 if ( delimiter != -1 ) {
586 String value = strToken.substring( delimiter + 1 ).trim();
587 value = Utils.handleQuotedString(value);
588 whereCols.add( strToken.substring( 0, delimiter ).trim().toUpperCase() );
589 value = Utils.replaceAll( value, commaEscape, "," );
590 value = Utils.replaceAll( value, quoteEscape, "'" );
591 value = Utils.replaceKeywordsBack(value, oldValues);
592 whereValues.add( value );
593 }
594 else {
595 int delimiter1 = strToken.toLowerCase().indexOf( " is " );
596 whereCols.add( strToken.substring( 0, delimiter1 ).trim().toUpperCase() );
597 whereValues.add( null );
598 }
599 }
600 }
601 columnWhereNames = new String [whereCols.size()];
602 columnWhereValues = new String [whereValues.size()];
603 whereCols.copyInto( columnWhereNames );
604 whereValues.copyInto( columnWhereValues );
605 }
606 }
607
608 else if ( upperSql.startsWith( "CREATE TABLE " ) ) {
610 sql = Utils.replaceAll( sql, "\n", "" );
612 upperSql = sql.toUpperCase();
613 sqlType = CREATE_TABLE;
614 int createPos = upperSql.indexOf( "CREATE TABLE" );
615 int tableStartPos = upperSql.indexOf( "(" );
616 int tableEndPos = upperSql.lastIndexOf( ")" );
617
618 tableName = sql.substring( createPos + 12, tableStartPos ).trim().
619 toUpperCase();
620 String createString = sql.substring( tableStartPos + 1, tableEndPos ).trim();
621 ArrayList setColumnNames = new ArrayList ();
622 ArrayList setPrimaryKeys = new ArrayList ();
623 ArrayList setNotnullColumns = new ArrayList ();
624 String token = ( ( String ) upperSql.substring( tableStartPos + 1,tableEndPos ) ).trim();
625 StringTokenizer comma = new StringTokenizer ( token, "," );
626 while ( comma.hasMoreTokens() ) {
627 String nextComma = comma.nextToken().trim();
628 StringTokenizer space = new StringTokenizer ( nextComma, " " );
629 String nextSpace = space.nextToken();
630 setColumnNames.add( nextSpace );
631 if ( nextComma.indexOf( "PRIMARYKEY" ) != -1 ) {
632 setPrimaryKeys.add( nextSpace );
633 setNotnullColumns.add( nextSpace );
634 }
635 if ( nextComma.indexOf( "NOT NULL" ) != -1 ) {
636 setNotnullColumns.add( nextSpace );
637 }
638 }
639 columnNames = new String [setColumnNames.size()];
640 setColumnNames.toArray( columnNames );
641 this.primaryKeyColumns = new String [setPrimaryKeys.size()];
642 setPrimaryKeys.toArray( this.primaryKeyColumns );
643 this.notnullColumns = new String [setNotnullColumns.size()];
644 setNotnullColumns.toArray( this.notnullColumns );
645
646 }
647 else {
648 throw new Exception ( "Malformed SQL. Wrong SQL statement." );
649 }
650 }
651
652
656 public void setBinaryStreamList( ArrayList list ) {
657 this.binaryStreamObjectList = list;
658 }
659
660 }
661 |
Popular Tags |
Java API By Example, From Geeks To Geeks.