KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > relique > jdbc > csv > CsvSqlParser


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
20 package org.relique.jdbc.csv;
21
22 import java.io.*;
23 import java.util.*;
24 import java.sql.Statement JavaDoc;
25
26 /**
27  * Class is used for parsing sql statements.
28  *
29  * @author Zoran Milakovic
30  */

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

68   public String JavaDoc getTableName() {
69     return tableName;
70   }
71
72   /**
73    * Gets the columnNames attribute of the SqlParser object
74    *
75    * @return The columnNames value
76    * @since
77    */

78   public String JavaDoc[] getColumnNames() {
79     return columnNames;
80   }
81
82   public String JavaDoc[] getWhereColumnNames() {
83     return columnWhereNames;
84   }
85
86   public String JavaDoc[] getWhereColumnValues() {
87     return columnWhereValues;
88   }
89
90   public String JavaDoc[] getColumnValues() {
91     return columnValues;
92   }
93
94   /**
95    * Parse sql statement.
96    *
97    * @param statement Statement object which wrap sql statement
98    * @exception Exception Description of Exception
99    * @since
100    */

101   public void parse(Statement JavaDoc statement) throws Exception JavaDoc {
102     String JavaDoc sql = "";
103     if ( statement instanceof CsvStatement )
104       sql = ((CsvStatement)statement).getSqlStatement();
105     else if( statement instanceof CsvPreparedStatement)
106       sql = ((CsvPreparedStatement)statement).getSqlStatement();
107     tableName = null;
108     columnNames = new String JavaDoc[0];
109     columnValues = new String JavaDoc[0];
110     columnWhereNames = new String JavaDoc[0];
111     columnWhereValues = new String JavaDoc[0];
112     whereStatement = null;
113     sqlType = null;
114
115     //removing comments
116
if( sql.indexOf("/*") != -1 ) {
117       StringBuffer JavaDoc buf = new StringBuffer JavaDoc( sql );
118       buf.delete( sql.indexOf("/*") , sql.indexOf("*/")+2 );
119       sql = buf.toString();
120     }
121     sql = sql.trim();
122
123 CsvDriver.log("sql = "+sql);
124     
125     oldValues.clear();
126 // int startIndex = 0;
127
//replace comma(,) in values between quotes(')
128
StringTokenizer tokQuote = new StringTokenizer(sql.toString(), "'", true);
129     StringBuffer JavaDoc sb = new StringBuffer JavaDoc();
130     boolean openParent1 = false;
131     while (tokQuote.hasMoreTokens()) {
132 // startIndex++;
133
String JavaDoc next = tokQuote.nextToken();
134       if (openParent1) {
135         next = Utils.replaceAll(next, ",", COMMA_ESCAPE);
136         next = Utils.replaceKeywords(next, oldValues);
137       }
138       sb.append(next);
139       if (next.equalsIgnoreCase("'")) {
140         if (openParent1 == true)
141           openParent1 = false;
142         else
143           openParent1 = true;
144       }
145     }
146 //END replacement
147
sql = sb.toString();
148     String JavaDoc upperSql = sql.toUpperCase();
149
150
151 //handle unsupported statements
152
if (upperSql.startsWith("ALTER "))
153       throw new Exception JavaDoc("ALTER TABLE statements are not supported.");
154     if (upperSql.startsWith("DROP "))
155       throw new Exception JavaDoc("DROP statements are not supported.");
156
157
158
159 //SELECT
160
if ( upperSql.startsWith("SELECT ") ) {
161       if (upperSql.lastIndexOf(" FROM ") == -1) {
162         throw new Exception JavaDoc("Malformed SQL. Missing FROM statement.");
163       }
164
165       sqlType = SELECT;
166       int fromPos = upperSql.lastIndexOf(" FROM ");
167       int wherePos = upperSql.lastIndexOf(" WHERE ");
168       if (wherePos == -1)
169         tableName = sql.substring(fromPos + 6).trim();
170       else
171         tableName = sql.substring(fromPos + 6, wherePos).trim();
172
173       Vector cols = new Vector();
174       StringTokenizer tokenizer = new StringTokenizer(upperSql.substring(7,
175           fromPos), ",");
176
177       while (tokenizer.hasMoreTokens()) {
178         cols.add(tokenizer.nextToken().trim());
179       }
180
181       columnNames = new String JavaDoc[cols.size()];
182       cols.copyInto(columnNames);
183       if (wherePos != -1) {
184         String JavaDoc strWhere = sql.substring(wherePos + 7);
185         Vector whereCols = new Vector();
186         Vector whereValues = new Vector();
187         StringTokenizer tokenizerWhere = new StringTokenizer(strWhere, ",");
188
189         while (tokenizerWhere.hasMoreTokens()) {
190           String JavaDoc strToken = tokenizerWhere.nextToken();
191           if (strToken.toLowerCase().indexOf(" and ") != -1) {
192             String JavaDoc temp = strToken;
193             int andPos = 0;
194             out:
195             do {
196               andPos = temp.toLowerCase().indexOf(" and ");
197               String JavaDoc strTokenAdd;
198               if (andPos != -1)
199                 strTokenAdd = temp.substring(0, andPos).trim();
200               else
201                 strTokenAdd = temp.trim();
202               int delimiter2 = strTokenAdd.indexOf("=");
203               if (delimiter2 != -1) {
204                 String JavaDoc valueAdd = strTokenAdd.substring(delimiter2 + 1).trim();
205                 valueAdd = Utils.handleQuotedString(valueAdd);
206                 whereCols.add(strTokenAdd.substring(0, delimiter2).trim());
207                 valueAdd = Utils.replaceAll(valueAdd, COMMA_ESCAPE, ",");
208                 valueAdd = Utils.replaceAll(valueAdd, QUOTE_ESCAPE, "'");
209                 valueAdd = Utils.replaceKeywordsBack(valueAdd, oldValues);
210                 whereValues.add(valueAdd);
211               }
212               else {
213 // int delimiter3 = strTokenAdd.toLowerCase().indexOf(" is ");
214
// whereCols.add(strTokenAdd.substring(0, delimiter3).trim());
215
// whereValues.add(null);
216
int delimiterNULL = strTokenAdd.toLowerCase().indexOf(" is null");
217                   int delimiterNOTNULL = strTokenAdd.toLowerCase().indexOf(" is not null");
218                   if(delimiterNULL != -1) {
219                       whereCols.add(strTokenAdd.substring(0, delimiterNULL).trim());
220                       whereValues.add(null);
221                   }
222                   if(delimiterNOTNULL != -1) {
223                       whereCols.add(strTokenAdd.substring(0, delimiterNOTNULL).trim());
224                       whereValues.add(Utils.NOT_NULL_STRING);
225                   }
226               }
227               temp = temp.substring(andPos + 5);
228               if (temp.toLowerCase().indexOf(" and ") == -1) {
229                 strTokenAdd = temp.trim();
230                 int delimiter4 = strTokenAdd.indexOf("=");
231                 if (delimiter4 != -1) {
232                   String JavaDoc valueAdd = strTokenAdd.substring(delimiter4 + 1).trim();
233                   valueAdd = Utils.handleQuotedString(valueAdd);
234                   whereCols.add(strTokenAdd.substring(0, delimiter4).trim());
235                   valueAdd = Utils.replaceAll(valueAdd, COMMA_ESCAPE, ",");
236                   valueAdd = Utils.replaceAll(valueAdd, QUOTE_ESCAPE, "'");
237                   valueAdd = Utils.replaceKeywordsBack(valueAdd, oldValues);
238                   whereValues.add(valueAdd);
239                 }
240                 else {
241 // int delimiter3 = strTokenAdd.toLowerCase().indexOf(" is ");
242
// whereCols.add(strTokenAdd.substring(0, delimiter3).trim());
243
// whereValues.add(null);
244
int delimiterNULL = strTokenAdd.toLowerCase().indexOf(" is null");
245                     int delimiterNOTNULL = strTokenAdd.toLowerCase().indexOf(" is not null");
246                     if(delimiterNULL != -1) {
247                         whereCols.add(strTokenAdd.substring(0, delimiterNULL).trim());
248                         whereValues.add(null);
249                     }
250                     if(delimiterNOTNULL != -1) {
251                         whereCols.add(strTokenAdd.substring(0, delimiterNOTNULL).trim());
252                         whereValues.add(Utils.NOT_NULL_STRING);
253                     }
254                 }
255                 break out;
256               }
257
258             }
259             while (true);
260
261           }
262           else {
263             int delimiter = strToken.indexOf("=");
264             if (delimiter != -1) {
265               String JavaDoc value = strToken.substring(delimiter + 1).trim();
266               value = Utils.handleQuotedString(value);
267               whereCols.add(strToken.substring(0, delimiter).trim());
268               value = Utils.replaceAll(value, COMMA_ESCAPE, ",");
269               value = Utils.replaceAll(value, QUOTE_ESCAPE, "'");
270               value = Utils.replaceKeywordsBack(value, oldValues);
271               whereValues.add(value);
272             }
273             else {
274               int delimiterNULL = strToken.toLowerCase().indexOf(" is null");
275               int delimiterNOTNULL = strToken.toLowerCase().indexOf(" is not null");
276               if(delimiterNULL != -1) {
277                   whereCols.add(strToken.substring(0, delimiterNULL).trim());
278                   whereValues.add(null);
279               }
280               if(delimiterNOTNULL != -1) {
281                   whereCols.add(strToken.substring(0, delimiterNOTNULL).trim());
282                   whereValues.add(Utils.NOT_NULL_STRING);
283               }
284             }
285           }
286         }
287         columnWhereNames = new String JavaDoc[whereCols.size()];
288         columnWhereValues = new String JavaDoc[whereValues.size()];
289         whereCols.copyInto(columnWhereNames);
290         whereValues.copyInto(columnWhereValues);
291       }
292     }
293
294
295 //INSERT
296
else if ( upperSql.startsWith("INSERT ") ) {
297       if (upperSql.lastIndexOf(" VALUES") == -1) {
298           throw new Exception JavaDoc("Malformed SQL. Missing VALUES statement.");
299         }
300         sqlType = INSERT;
301         int intoPos = 0;
302         if (upperSql.indexOf(" INTO ") != -1)
303           intoPos = upperSql.indexOf(" INTO ") + 6;
304         else
305           intoPos = upperSql.indexOf("INSERT ") + 7;
306         int bracketPos = upperSql.indexOf("(");
307         int lastBracketPos = upperSql.indexOf(")");
308         tableName = sql.substring(intoPos, bracketPos).trim();
309
310         Vector cols = new Vector();
311         StringTokenizer tokenizer = new StringTokenizer(upperSql.substring(
312             bracketPos + 1, lastBracketPos), ",");
313         while (tokenizer.hasMoreTokens()) {
314           cols.add(tokenizer.nextToken().trim());
315         }
316         columnNames = new String JavaDoc[cols.size()];
317         cols.copyInto(columnNames);
318
319         int valuesPos = upperSql.indexOf("VALUES");
320         String JavaDoc endStatement = sql.substring(valuesPos + 6).trim();
321         bracketPos = endStatement.indexOf("(");
322         lastBracketPos = endStatement.lastIndexOf(")");
323         Vector values = new Vector();
324         StringTokenizer tokenizer2 = new StringTokenizer(endStatement.substring(
325             bracketPos + 1, lastBracketPos), ",");
326         while (tokenizer2.hasMoreTokens()) {
327           String JavaDoc value = tokenizer2.nextToken().trim();
328           value = Utils.handleQuotedString(value);
329           value = Utils.replaceAll(value, COMMA_ESCAPE, ",");
330           value = Utils.replaceAll(value, QUOTE_ESCAPE, "'");
331           value = Utils.replaceAll(value, "\"", DOUBLE_QUOTE_ESCAPE);
332           value = Utils.replaceKeywordsBack(value, oldValues);
333           value = Utils.handleBinaryString(value, this.binaryStreamObjectList);
334           values.add(value);
335         }
336         columnValues = new String JavaDoc[values.size()];
337         values.copyInto(columnValues);
338         //replace all line brakes
339
columnValues = Utils.replaceLineBrakesAndCarrReturn(
340             columnValues,
341               ((CsvConnection) statement.getConnection()).getLineBreakEscape(),
342               ((CsvConnection) statement.getConnection()).getCarriageReturnEscape()
343               );
344     }
345
346
347 //UPDATE
348
else if ( upperSql.startsWith("UPDATE ") ) {
349       if (upperSql.lastIndexOf(" SET ") == -1)
350             throw new Exception JavaDoc("Malformed SQL. Missing SET statement.");
351           sqlType = UPDATE;
352           int updatePos = upperSql.indexOf("UPDATE");
353           int setPos = upperSql.indexOf(" SET ");
354           int equalPos = upperSql.indexOf("=");
355           int wherePos = upperSql.indexOf(" WHERE ");
356           tableName = sql.substring(updatePos + 6, setPos).trim();
357
358           String JavaDoc setString = "";
359           if (wherePos != -1)
360             setString = sql.substring(setPos + 5, wherePos);
361           else
362             setString = sql.substring(setPos + 5, sql.length());
363           StringTokenizer tokenizerSet = new StringTokenizer(setString, ",");
364           Vector setNames = new Vector();
365           Vector setValues = new Vector();
366
367           while (tokenizerSet.hasMoreTokens()) {
368             String JavaDoc strToken = tokenizerSet.nextToken();
369             int delimiter = strToken.indexOf("=");
370             setNames.add(strToken.substring(0, delimiter).trim());
371             String JavaDoc value = strToken.substring(delimiter + 1).trim();
372             value = Utils.handleQuotedString(value);
373             value = Utils.replaceAll(value, COMMA_ESCAPE, ",");
374             value = Utils.replaceAll(value, QUOTE_ESCAPE, "'");
375             value = Utils.replaceAll(value, "\"", DOUBLE_QUOTE_ESCAPE);
376             value = Utils.replaceKeywordsBack(value, oldValues);
377             value = Utils.handleBinaryString(value, this.binaryStreamObjectList);
378             setValues.add(value);
379           }
380
381           columnNames = new String JavaDoc[setNames.size()];
382           columnValues = new String JavaDoc[setValues.size()];
383           setNames.copyInto(columnNames);
384           setValues.copyInto(columnValues);
385           //replace all line brakes
386
columnValues = Utils.replaceLineBrakesAndCarrReturn(
387               columnValues,
388               ((CsvConnection) statement.getConnection()).getLineBreakEscape(),
389               ((CsvConnection) statement.getConnection()).getCarriageReturnEscape()
390               );
391           if (wherePos != -1) {
392             String JavaDoc strWhere = sql.substring(wherePos + 6).trim();
393             Vector whereCols = new Vector();
394             Vector whereValues = new Vector();
395             StringTokenizer tokenizerWhere = new StringTokenizer(strWhere, ",");
396
397             while (tokenizerWhere.hasMoreTokens()) {
398               String JavaDoc strToken = tokenizerWhere.nextToken();
399               if (strToken.toLowerCase().indexOf(" and ") != -1) {
400                 String JavaDoc temp = strToken;
401                 int andPos = 0;
402                 out:
403                 do {
404                   andPos = temp.toLowerCase().indexOf(" and ");
405                   String JavaDoc strTokenAdd;
406                   if (andPos != -1)
407                     strTokenAdd = temp.substring(0, andPos).trim();
408                   else
409                     strTokenAdd = temp.trim();
410                   int delimiter2 = strTokenAdd.indexOf("=");
411                   if (delimiter2 != -1) {
412                     String JavaDoc valueAdd = strTokenAdd.substring(delimiter2 + 1).trim();
413                     valueAdd = Utils.handleQuotedString(valueAdd);
414                     whereCols.add(strTokenAdd.substring(0, delimiter2).trim());
415                     valueAdd = Utils.replaceAll(valueAdd, COMMA_ESCAPE, ",");
416                     valueAdd = Utils.replaceAll(valueAdd, QUOTE_ESCAPE, "'");
417                     valueAdd = Utils.replaceKeywordsBack(valueAdd, oldValues);
418                     whereValues.add(valueAdd);
419                   }
420                   else {
421 // int delimiter3 = strTokenAdd.toLowerCase().indexOf(" is ");
422
// whereCols.add(strTokenAdd.substring(0, delimiter3).trim());
423
// whereValues.add(null);
424
int delimiterNULL = strTokenAdd.toLowerCase().indexOf(" is null");
425                       int delimiterNOTNULL = strTokenAdd.toLowerCase().indexOf(" is not null");
426                       if(delimiterNULL != -1) {
427                           whereCols.add(strTokenAdd.substring(0, delimiterNULL).trim());
428                           whereValues.add(null);
429                       }
430                       if(delimiterNOTNULL != -1) {
431                           whereCols.add(strTokenAdd.substring(0, delimiterNOTNULL).trim());
432                           whereValues.add(Utils.NOT_NULL_STRING);
433                       }
434                   }
435                   temp = temp.substring(andPos + 5);
436                   if (temp.toLowerCase().indexOf(" and ") == -1) {
437                     strTokenAdd = temp.trim();
438                     int delimiter4 = strTokenAdd.indexOf("=");
439                     if (delimiter4 != -1) {
440                       String JavaDoc valueAdd = strTokenAdd.substring(delimiter4 + 1).
441                           trim();
442                       valueAdd = Utils.handleQuotedString(valueAdd);
443                       whereCols.add(strTokenAdd.substring(0, delimiter4).trim());
444                       valueAdd = Utils.replaceAll(valueAdd, COMMA_ESCAPE, ",");
445                       valueAdd = Utils.replaceAll(valueAdd, QUOTE_ESCAPE, "'");
446                       valueAdd = Utils.replaceKeywordsBack(valueAdd, oldValues);
447                       whereValues.add(valueAdd);
448                     }
449                     else {
450 // int delimiter3 = strTokenAdd.toLowerCase().indexOf(" is ");
451
// whereCols.add(strTokenAdd.substring(0, delimiter3).trim());
452
// whereValues.add(null);
453
int delimiterNULL = strTokenAdd.toLowerCase().indexOf(" is null");
454                         int delimiterNOTNULL = strTokenAdd.toLowerCase().indexOf(" is not null");
455                         if(delimiterNULL != -1) {
456                             whereCols.add(strTokenAdd.substring(0, delimiterNULL).trim());
457                             whereValues.add(null);
458                         }
459                         if(delimiterNOTNULL != -1) {
460                             whereCols.add(strTokenAdd.substring(0, delimiterNOTNULL).trim());
461                             whereValues.add(Utils.NOT_NULL_STRING);
462                         }
463                     }
464                     break out;
465                   }
466
467                 }
468                 while (true);
469
470               }
471               else {
472                 int delimiter = strToken.indexOf("=");
473                 if (delimiter != -1) {
474                   String JavaDoc value = strToken.substring(delimiter + 1).trim();
475                   value = Utils.handleQuotedString(value);
476                   whereCols.add(strToken.substring(0, delimiter).trim());
477                   value = Utils.replaceAll(value, COMMA_ESCAPE, ",");
478                   value = Utils.replaceAll(value, QUOTE_ESCAPE, "'");
479                   value = Utils.replaceKeywordsBack(value, oldValues);
480                   whereValues.add(value);
481                 }
482                 else {
483 // int delimiter1 = strToken.toLowerCase().indexOf(" is ");
484
// whereCols.add(strToken.substring(0, delimiter1).trim());
485
// whereValues.add(null);
486
int delimiterNULL = strToken.toLowerCase().indexOf(" is null");
487                     int delimiterNOTNULL = strToken.toLowerCase().indexOf(" is not null");
488                     if(delimiterNULL != -1) {
489                         whereCols.add(strToken.substring(0, delimiterNULL).trim());
490                         whereValues.add(null);
491                     }
492                     if(delimiterNOTNULL != -1) {
493                         whereCols.add(strToken.substring(0, delimiterNOTNULL).trim());
494                         whereValues.add(Utils.NOT_NULL_STRING);
495                     }
496                 }
497               }
498             }
499             columnWhereNames = new String JavaDoc[whereCols.size()];
500             columnWhereValues = new String JavaDoc[whereValues.size()];
501             whereCols.copyInto(columnWhereNames);
502             whereValues.copyInto(columnWhereValues);
503           }
504     }
505
506
507 //CREATE TABLE
508
else if ( upperSql.startsWith("CREATE TABLE ") ) {
509       sqlType = CREATE_TABLE;
510             int createPos = upperSql.indexOf("CREATE TABLE");
511             int tableStartPos = upperSql.indexOf("(");
512             int tableEndPos = upperSql.lastIndexOf(")");
513             tableName = sql.substring(createPos + 12, tableStartPos).trim();
514             String JavaDoc createString = sql.substring(tableStartPos + 1, tableEndPos).
515                 trim();
516             StringTokenizer tokenizerCreate = new StringTokenizer(createString.
517                 toUpperCase(), "\n");
518             Vector setColumnNames = new Vector();
519             while (tokenizerCreate.hasMoreTokens()) {
520               String JavaDoc strToken = tokenizerCreate.nextToken().trim();
521               int delimiter = strToken.indexOf(" ");
522               //find out type of column
523
String JavaDoc typeOfColumn = "";
524               StringTokenizer stSpace = new StringTokenizer(strToken, " ");
525               while (stSpace.hasMoreTokens()) {
526                 String JavaDoc nextStSpace = stSpace.nextToken();
527                 if (nextStSpace.equalsIgnoreCase(CsvDriver.BINARY_TYPE))
528                   typeOfColumn = "-BINARY";
529               }
530               if (strToken.toUpperCase().indexOf("CONSTRAINT") == -1) {
531                 String JavaDoc columnName = strToken.substring(0, delimiter).trim();
532                 setColumnNames.add(columnName + typeOfColumn);
533               }
534             }
535             columnNames = new String JavaDoc[setColumnNames.size()];
536             setColumnNames.copyInto(columnNames);
537
538     } else {
539       throw new Exception JavaDoc("Malformed SQL. Wrong SQL statement.");
540     }
541
542   }
543
544
545   /**
546    * Set setBinaryStreamList.
547    * @param list
548    */

549   public void setBinaryStreamList(ArrayList list) {
550     this.binaryStreamObjectList = list;
551   }
552
553 }
554
Popular Tags