|
1
19
20 package org.relique.jdbc.csv;
21
22 import java.io.*;
23 import java.util.*;
24 import java.sql.Statement  ;
25
26
31 public class CsvSqlParser {
32
33 public static final String CREATE_TABLE = "create_table";
34 public static final String INSERT = "insert";
35 public static final String UPDATE = "update";
36 public static final String SELECT = "select";
37
38 public static final String QUOTE_ESCAPE = "''";
39 public static final String DOUBLE_QUOTE_ESCAPE = "\"\"";
40 private static final String COMMA_ESCAPE = "~#####1~";
41
42 public static final String BINARY_STREAM_OBJECT = "CsvDriverBinaryStreamObject";
43
44 private ArrayList binaryStreamObjectList = new ArrayList();
45
46 private HashMap oldValues = new HashMap();
47
48 public String tableName;
49
50 public String whereStatement;
51
52 public String sqlType;
53
54 public String [] columnNames;
55
56 public String [] columnValues;
57
58 public String [] columnWhereNames;
59
60 public String [] columnWhereValues;
61
62
68 public String getTableName() {
69 return tableName;
70 }
71
72
78 public String [] getColumnNames() {
79 return columnNames;
80 }
81
82 public String [] getWhereColumnNames() {
83 return columnWhereNames;
84 }
85
86 public String [] getWhereColumnValues() {
87 return columnWhereValues;
88 }
89
90 public String [] getColumnValues() {
91 return columnValues;
92 }
93
94
101 public void parse(Statement statement) throws Exception {
102 String 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 [0];
109 columnValues = new String [0];
110 columnWhereNames = new String [0];
111 columnWhereValues = new String [0];
112 whereStatement = null;
113 sqlType = null;
114
115 if( sql.indexOf("/*") != -1 ) {
117 StringBuffer buf = new StringBuffer ( 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 StringTokenizer tokQuote = new StringTokenizer(sql.toString(), "'", true);
129 StringBuffer sb = new StringBuffer ();
130 boolean openParent1 = false;
131 while (tokQuote.hasMoreTokens()) {
132 String 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 sql = sb.toString();
148 String upperSql = sql.toUpperCase();
149
150
151 if (upperSql.startsWith("ALTER "))
153 throw new Exception ("ALTER TABLE statements are not supported.");
154 if (upperSql.startsWith("DROP "))
155 throw new Exception ("DROP statements are not supported.");
156
157
158
159 if ( upperSql.startsWith("SELECT ") ) {
161 if (upperSql.lastIndexOf(" FROM ") == -1) {
162 throw new Exception ("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 [cols.size()];
182 cols.copyInto(columnNames);
183 if (wherePos != -1) {
184 String 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 strToken = tokenizerWhere.nextToken();
191 if (strToken.toLowerCase().indexOf(" and ") != -1) {
192 String temp = strToken;
193 int andPos = 0;
194 out:
195 do {
196 andPos = temp.toLowerCase().indexOf(" and ");
197 String 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 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 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 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 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 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 [whereCols.size()];
288 columnWhereValues = new String [whereValues.size()];
289 whereCols.copyInto(columnWhereNames);
290 whereValues.copyInto(columnWhereValues);
291 }
292 }
293
294
295 else if ( upperSql.startsWith("INSERT ") ) {
297 if (upperSql.lastIndexOf(" VALUES") == -1) {
298 throw new Exception ("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 [cols.size()];
317 cols.copyInto(columnNames);
318
319 int valuesPos = upperSql.indexOf("VALUES");
320 String 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 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 [values.size()];
337 values.copyInto(columnValues);
338 columnValues = Utils.replaceLineBrakesAndCarrReturn(
340 columnValues,
341 ((CsvConnection) statement.getConnection()).getLineBreakEscape(),
342 ((CsvConnection) statement.getConnection()).getCarriageReturnEscape()
343 );
344 }
345
346
347 else if ( upperSql.startsWith("UPDATE ") ) {
349 if (upperSql.lastIndexOf(" SET ") == -1)
350 throw new Exception ("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 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 strToken = tokenizerSet.nextToken();
369 int delimiter = strToken.indexOf("=");
370 setNames.add(strToken.substring(0, delimiter).trim());
371 String 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 [setNames.size()];
382 columnValues = new String [setValues.size()];
383 setNames.copyInto(columnNames);
384 setValues.copyInto(columnValues);
385 columnValues = Utils.replaceLineBrakesAndCarrReturn(
387 columnValues,
388 ((CsvConnection) statement.getConnection()).getLineBreakEscape(),
389 ((CsvConnection) statement.getConnection()).getCarriageReturnEscape()
390 );
391 if (wherePos != -1) {
392 String 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 strToken = tokenizerWhere.nextToken();
399 if (strToken.toLowerCase().indexOf(" and ") != -1) {
400 String temp = strToken;
401 int andPos = 0;
402 out:
403 do {
404 andPos = temp.toLowerCase().indexOf(" and ");
405 String 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 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 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 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 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 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 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 [whereCols.size()];
500 columnWhereValues = new String [whereValues.size()];
501 whereCols.copyInto(columnWhereNames);
502 whereValues.copyInto(columnWhereValues);
503 }
504 }
505
506
507 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 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 strToken = tokenizerCreate.nextToken().trim();
521 int delimiter = strToken.indexOf(" ");
522 String typeOfColumn = "";
524 StringTokenizer stSpace = new StringTokenizer(strToken, " ");
525 while (stSpace.hasMoreTokens()) {
526 String nextStSpace = stSpace.nextToken();
527 if (nextStSpace.equalsIgnoreCase(CsvDriver.BINARY_TYPE))
528 typeOfColumn = "-BINARY";
529 }
530 if (strToken.toUpperCase().indexOf("CONSTRAINT") == -1) {
531 String columnName = strToken.substring(0, delimiter).trim();
532 setColumnNames.add(columnName + typeOfColumn);
533 }
534 }
535 columnNames = new String [setColumnNames.size()];
536 setColumnNames.copyInto(columnNames);
537
538 } else {
539 throw new Exception ("Malformed SQL. Wrong SQL statement.");
540 }
541
542 }
543
544
545
549 public void setBinaryStreamList(ArrayList list) {
550 this.binaryStreamObjectList = list;
551 }
552
553 }
554 |
Popular Tags |
Java API By Example, From Geeks To Geeks.