|
1
19
20
21 package org.relique.jdbc.csv;
22
23 import java.io.File  ;
24 import java.io.InputStream ;
25 import java.io.Reader ;
26 import java.math.BigDecimal ;
27 import java.net.URL ;
28 import java.sql.Array ;
29 import java.sql.Blob ;
30 import java.sql.Clob ;
31 import java.sql.Connection ;
32 import java.sql.Date ;
33 import java.sql.DriverManager ;
34 import java.sql.ParameterMetaData ;
35 import java.sql.PreparedStatement ;
36 import java.sql.Ref ;
37 import java.sql.ResultSet ;
38 import java.sql.ResultSetMetaData ;
39 import java.sql.SQLException ;
40 import java.sql.SQLWarning ;
41 import java.sql.Time ;
42 import java.sql.Timestamp ;
43 import java.util.ArrayList ;
44 import java.util.Calendar ;
45 import java.util.Enumeration ;
46 import java.util.Vector ;
47
48
53 public class CsvPreparedStatement implements PreparedStatement {
54
55 private CsvConnection connection;
56 private Vector resultSets = new Vector ();
57 private String sqlForPrepare = "";
58 private String sqlPrepared = "";
59 private int paramCount = 0;
60 private ArrayList parameters = new ArrayList ();
61 private ArrayList binaryStreamParameters = new ArrayList ();
62 private CsvWriter writeCsv;
63
64 public static String PREPARE_SEPARATOR = "~@#NEXTPARAMETER#@~";
65 private String sql;
66
67 public CsvPreparedStatement(CsvConnection connection, String preparedSql) {
68 DriverManager.println("CsvJdbc - CsvStatement() - connection=" + connection);
69 this.sqlForPrepare = preparedSql;
70 this.sqlPrepared = preparedSql;
71 this.paramCount = countParameters(preparedSql);
72 for ( int i = 0; i < paramCount; i++ )
73 parameters.add(null);
74 this.connection = connection;
75 try {
76 if(!connection.getAutoCommit())
77 writeCsv=new CsvWriter(
78 null,
79 connection.getSeperator(),
80 connection.getExtension(),
81 connection.getMaxFileSize(),
82 connection.getCharset(),
83 connection.getUseQuotes(),
84 connection.getUseQuotesEscape()
85 );
86 }
87 catch (Exception ex) {
88 ex.printStackTrace();
89 }
90 }
91
92 private int countParameters(String sql) {
93 int count = 0;
94 int index = sql.indexOf(PREPARE_SEPARATOR);
95 while( index != -1 ) {
96 count++;
97 sql = sql.substring(index+1);
98 index = sql.indexOf(PREPARE_SEPARATOR);
99 }
100 return count;
101 }
102
103 private boolean prepareSql() throws SQLException {
104 boolean retVal = true;
105 for (int i = 0; i < parameters.size(); i++) {
106 int index = sqlPrepared.indexOf(PREPARE_SEPARATOR);
107 String val;
108 if (index != -1) {
109 if( parameters.get(i) == null )
110 val = "null";
111 else
112 val = parameters.get(i).toString();
113 sqlPrepared = sqlPrepared.substring(0, index) +
114 val +
115 sqlPrepared.substring(index + PREPARE_SEPARATOR.length());
116 }
117 }
118 if (sqlPrepared.indexOf(PREPARE_SEPARATOR) != -1)
119 throw new SQLException (
120 "All ? in prepared query has to be replaced with values.");
121 else if (parameters.size() < this.paramCount)
122 throw new SQLException (
123 "Number of setted parameters is less than number of parameters in statement.");
124 else if (parameters.size() > this.paramCount)
125 throw new SQLException (
126 "Number of setted parameters is greater than number of parameters in statement.");
127
128 return retVal;
129 }
130
131
138 public void setMaxFieldSize(int p0) throws SQLException
139 {
140 throw new SQLException ("Not Supported !");
141 }
142
143
144
151 public void setMaxRows(int p0) throws SQLException
152 {
153 throw new SQLException ("Not Supported !");
154 }
155
156
157
164 public void setEscapeProcessing(boolean p0) throws SQLException
165 {
166 throw new SQLException ("Not Supported !");
167 }
168
169
170
177 public void setQueryTimeout(int p0) throws SQLException
178 {
179 throw new SQLException ("Not Supported !");
180 }
181
182
183
190 public void setCursorName(String p0) throws SQLException
191 {
192 throw new SQLException ("Not Supported !");
193 }
194
195
196
203 public void setFetchDirection(int p0) throws SQLException
204 {
205 throw new SQLException ("Not Supported !");
206 }
207
208
209
216 public void setFetchSize(int p0) throws SQLException
217 {
218 throw new SQLException ("Not Supported !");
219 }
220
221
222
229 public int getMaxFieldSize() throws SQLException
230 {
231 throw new SQLException ("Not Supported !");
232 }
233
234
235
242 public int getMaxRows() throws SQLException
243 {
244 throw new SQLException ("Not Supported !");
245 }
246
247
248
255 public int getQueryTimeout() throws SQLException
256 {
257 throw new SQLException ("Not Supported !");
258 }
259
260
261
268 public SQLWarning getWarnings() throws SQLException
269 {
270 throw new SQLException ("Not Supported !");
271 }
272
273
274
281 public ResultSet getResultSet() throws SQLException
282 {
283 throw new SQLException ("Not Supported !");
284 }
285
286
287
294 public int getUpdateCount() throws SQLException
295 {
296 throw new SQLException ("Not Supported !");
297 }
298
299
300
307 public boolean getMoreResults() throws SQLException
308 {
309 throw new SQLException ("Not Supported !");
310 }
311
312
313
320 public int getFetchDirection() throws SQLException
321 {
322 throw new SQLException ("Not Supported !");
323 }
324
325
326
333 public int getFetchSize() throws SQLException
334 {
335 throw new SQLException ("Not Supported !");
336 }
337
338
339
346 public int getResultSetConcurrency() throws SQLException
347 {
348 throw new SQLException ("Not Supported !");
349 }
350
351
352
359 public int getResultSetType() throws SQLException
360 {
361 throw new SQLException ("Not Supported !");
362 }
363
364
365
372 public Connection getConnection() throws SQLException
373 {
374 return connection;
375 }
376
377
378
386 public ResultSet executeQuery(String sql) throws SQLException
387 {
388 DriverManager.println("CsvJdbc - CsvStatement:executeQuery() - sql= " + sql);
389 CsvSqlParser parser = new CsvSqlParser();
390 if( binaryStreamParameters.size() != 0 )
391 parser.setBinaryStreamList( binaryStreamParameters );
392
393 this.sql = sql;
394 try
395 {
396 parser.parse(this);
397 }
398 catch (Exception e)
399 {
400 throw new SQLException ("Syntax Error. " + e.getMessage());
401 }
402
403 String fileName = connection.getPath() + parser.getTableName() + connection.getExtension();
404 File checkFile = new File (fileName);
405 if (!checkFile.exists())
406 {
407 throw new SQLException ("Cannot open data file '" + fileName + "' !");
408 }
409
410 if (!checkFile.canRead())
411 {
412 throw new SQLException ("Data file '" + fileName + "' not readable !");
413 }
414 CsvReader reader;
415 try
416 {
417 reader = new CsvReader(fileName,
418 connection.getSeperator(),
419 connection.isSuppressHeaders(),
420 connection.getCharset(),
421 connection.getExtension(),
422 connection.getLineBreakEscape(),
423 connection.getCarriageReturnEscape()
425 );
426 String [] xxx = parser.getColumnNames();
427 String [] yyy = reader.getColumnNames();
428 boolean isOK = true;
429 for(int i=0; i< xxx.length; i++) {
430 if(!xxx[i].endsWith("*")) {
431 out:
432 for(int j=0; j< yyy.length; j++) {
433 if(xxx[i].equalsIgnoreCase( yyy[j] )) {
434 isOK=true;
435 break out;
436 }
437 else
438 isOK=false;
439 }
440 if(!isOK)
441 throw new SQLException ("Column '" + xxx[i] + "' not found.");
442 }
443 }
444 }
445 catch (Exception e)
446 {
447 throw new SQLException ("Error reading data file. Message was: " + e);
448 }
449 CsvResultSet resultSet = new CsvResultSet(this, reader,
450 parser.getTableName(), parser.getColumnNames(), parser.getWhereColumnNames(),
451 parser.getWhereColumnValues(), reader.getColumnTypes());
452 resultSets.add(resultSet);
453 return resultSet;
454 }
455
456
457
458
466 public int executeUpdate(String sql) throws SQLException
467 {
468 int updated=0;
469 DriverManager.println("CsvJdbc - CsvStatement:executeUpdate() - sql= " + sql);
470 CsvSqlParser parser = new CsvSqlParser();
471 if( binaryStreamParameters.size() != 0 )
472 parser.setBinaryStreamList( binaryStreamParameters );
473 this.sql = sql;
474 try
475 {
476 parser.parse(this);
477 }
478 catch (Exception e)
479 {
480 throw new SQLException ("Syntax Error. " + e.getMessage());
481 }
482 if(parser.sqlType.equals(parser.SELECT))
483 throw new SQLException ("Not supported SELECT statement - use executeQuery method");
484 else if (parser.sqlType.equals(parser.CREATE_TABLE)) {
485 throw new SQLException ("Not supported CREATE TABLE statement - use execute method");
486 }
487 else if (parser.sqlType.equals(parser.INSERT)) {
488 String fileName = connection.getPath() + parser.getTableName() + connection.getExtension();
489 File checkFile = new File (fileName);
490
491 if (!checkFile.exists())
492 {
493 throw new SQLException ("Cannot open data file '" + fileName + "' !");
494 }
495
496 if (!checkFile.canWrite())
497 {
498 throw new SQLException ("Data file '" + fileName + "' is read only !");
499 }
500 try
502 {
503 if(connection.getAutoCommit())
504 writeCsv = new CsvWriter(
505 fileName,
506 connection.getSeperator(),
507 connection.getExtension(),
508 connection.getMaxFileSize(),
509 connection.getCharset(),
510 connection.getUseQuotes(),
511 connection.getUseQuotesEscape()
512 );
513 else {
514 writeCsv.setFileName(fileName);
515 writeCsv.fillTableColumnNames();
516 }
517
518 String [] xxx = parser.getColumnNames();
519 String [] yyy = writeCsv.getColumnNames();
520 boolean isOK = true;
521 for(int i=0; i< xxx.length; i++) {
522 if(!xxx[i].endsWith("*")) {
523 out:
524 for(int j=0; j< yyy.length; j++) {
525 if(xxx[i].equalsIgnoreCase( yyy[j] )) {
526 isOK=true;
527 break out;
528 }
529 else
530 isOK=false;
531 }
532 if(!isOK)
533 throw new SQLException ("Column '" + xxx[i] + "' not found.");
534 }
535 }
536 writeCsv.newLine(parser.columnNames, parser.columnValues);
537
538 }
539 catch (Exception e)
540 {
541 e.printStackTrace();
542 throw new SQLException ("Error reading data file. Message was: " + e);
543 }
544
545 }
546 else if (parser.sqlType.equals(parser.UPDATE)) {
547
548 String fileName = connection.getPath() + parser.getTableName() + connection.getExtension();
549 File checkFile = new File (fileName);
550
551 if (!checkFile.exists())
552 {
553 throw new SQLException ("Cannot open data file '" + fileName + "' !");
554 }
555
556 if (!checkFile.canWrite())
557 {
558 throw new SQLException ("Data file '" + fileName + "' is read only !");
559 }
560 try
562 {
563 if(connection.getAutoCommit())
564 writeCsv = new CsvWriter(
565 fileName,
566 connection.getSeperator(),
567 connection.getExtension(),
568 connection.getMaxFileSize(),
569 connection.getCharset(),
570 connection.getUseQuotes(),
571 connection.getUseQuotesEscape()
572 );
573 else{
574 writeCsv.setFileName(fileName);
575 writeCsv.fillTableColumnNames();
576 }
577
578 String [] xxx = parser.getColumnNames();
579 String [] yyy = writeCsv.getColumnNames();
580 boolean isOK = true;
581 for(int i=0; i< xxx.length; i++) {
582 if(!xxx[i].endsWith("*")) {
583 out:
584 for(int j=0; j< yyy.length; j++) {
585 if(xxx[i].equalsIgnoreCase( yyy[j] )) {
586 isOK=true;
587 break out;
588 }
589 else
590 isOK=false;
591 }
592 if(!isOK)
593 throw new SQLException ("Column '" + xxx[i] + "' not found.");
594 }
595 }
596 if(!writeCsv.updateFields(parser.columnNames, parser.columnValues, parser.columnWhereNames, parser.columnWhereValues))
597 updated = -1;
598 }
599 catch (Exception e)
600 {
601 throw new SQLException ("Error reading data file. Message was: " + e);
602 }
603
604 }
605 return updated;
606
607 }
608
609
610
628 public void close() throws SQLException {
629 for(Enumeration i = resultSets.elements(); i.hasMoreElements(); ) {
631 CsvResultSet resultSet = (CsvResultSet) i.nextElement();
632 resultSet.close();
633 }
634 try {
635 if(this.writeCsv != null)
636 this.writeCsv.close();
637 }
638 catch(Exception e) {
639 e.printStackTrace();
640 throw new SQLException (e.getMessage());
641 }
642 }
643
644
650 public void cancel() throws SQLException
651 {
652 throw new SQLException ("Not Supported !");
653 }
654
655
656
662 public void clearWarnings() throws SQLException
663 {
664 throw new SQLException ("Not Supported !");
665 }
666
667
668
676 public boolean execute(String sql) throws SQLException
677 {
678 CsvSqlParser parser = new CsvSqlParser();
679 if( binaryStreamParameters.size() != 0 )
680 parser.setBinaryStreamList( binaryStreamParameters );
681 this.sql = sql;
682 try
683 {
684 parser.parse(this);
685 }
686 catch (Exception e)
687 {
688 throw new SQLException ("Syntax Error. " + e.getMessage());
689 }
690 if(parser.sqlType.equals(parser.SELECT))
691 throw new SQLException ("Not supported SELECT statement - use executeQuery method");
692 else if (parser.sqlType.equals(parser.INSERT))
693 executeUpdate(sql);
694 else if (parser.sqlType.equals(parser.CREATE_TABLE)) {
695 String fileName = connection.getPath() + parser.getTableName() + connection.getExtension();
696 File checkFile = new File (fileName);
697
698 if (checkFile.exists())
699 {
700 throw new SQLException ("Data file '" + fileName + "'already exists !");
701 }
702
703 try
705 {
706 if(connection.getAutoCommit())
707 writeCsv = new CsvWriter(
708 fileName,
709 connection.getSeperator(),
710 connection.getExtension(),
711 connection.getMaxFileSize(),
712 connection.getCharset(),
713 connection.getUseQuotes(),
714 connection.getUseQuotesEscape()
715 );
716 else{
717 writeCsv.setFileName(fileName);
718 writeCsv.fillTableColumnNames();
719 }
720
721 writeCsv.createTable(parser.columnNames, fileName);
722 }
723 catch (Exception e)
724 {
725 throw new SQLException ("Error reading data file. Message was: " + e);
726 }
727 }
728 return true;
729
730 }
731
732
738 public void setString(int parameterIndex, String value) throws SQLException {
739 if( value == null ) {
740 CsvDriver.log("value = null object");
741 parameters.set(parameterIndex - 1, value);
742 } else {
743 value = Utils.replaceAll(value, "'", CsvSqlParser.QUOTE_ESCAPE);
744 CsvDriver.log("value = " + value);
745 parameters.set(parameterIndex - 1, "'" + value + "'");
746 }
747 }
748
749
750
756 public void setBytes(int parameterIndex, byte[] value) throws SQLException {
757 binaryStreamParameters.add(Utils.bytesToHexString(value));
758 String paramName = CsvSqlParser.BINARY_STREAM_OBJECT+""+binaryStreamParameters.size();
759 parameters.set(parameterIndex-1, paramName);
760 }
761
762
769 public void addBatch(String p0) throws SQLException
770 {
771 throw new SQLException ("Not Supported !");
772 }
773
774
775
781 public void clearBatch() throws SQLException
782 {
783 throw new SQLException ("Not Supported !");
784 }
785
786
787
794 public int[] executeBatch() throws SQLException
795 {
796 throw new SQLException ("Not Supported !");
797 }
798
799
803 public boolean getMoreResults(int current) throws SQLException {
804 throw new UnsupportedOperationException ("Statement.getMoreResults(int) unsupported");
805 }
806
807 public ResultSet getGeneratedKeys() throws SQLException {
808 throw new UnsupportedOperationException ("Statement.getGeneratedKeys() unsupported");
809 }
810
811 public int executeUpdate(String sql, int autoGeneratedKeys) throws SQLException {
812 throw new UnsupportedOperationException ("Statement.executeUpdate(String,int) unsupported");
813 }
814
815 public int executeUpdate(String sql, int[] columnIndexes) throws SQLException {
816 throw new UnsupportedOperationException ("Statement.executeUpdate(String,int[]) unsupported");
817 }
818
819 public int executeUpdate(String sql, String [] columnNames) throws SQLException {
820 throw new UnsupportedOperationException ("Statement.executeUpdate(String,String[]) unsupported");
821 }
822
823 public boolean execute(String sql, int autoGeneratedKeys) throws SQLException {
824 throw new UnsupportedOperationException ("Statement.execute(String,int) unsupported");
825 }
826
827 public boolean execute(String sql, int[] columnIndexes) throws SQLException {
828 throw new UnsupportedOperationException ("Statement.execute(String,int[]) unsupported");
829 }
830
831 public boolean execute(String sql, String [] columnNames) throws SQLException {
832 throw new UnsupportedOperationException ("Statement.execute(String,String[]) unsupported");
833 }
834
835 public int getResultSetHoldability() throws SQLException {
836 throw new UnsupportedOperationException ("Statement.getResultSetHoldability() unsupported");
837 }
838
839
840
841 public ResultSet executeQuery() throws SQLException {
842 if( !prepareSql())
843 throw new SQLException ("Error with prepared statement !");
844 return executeQuery(this.sqlPrepared);
845
846 }
847 public int executeUpdate() throws SQLException {
848 if( !prepareSql())
849 throw new SQLException ("Error with prepared statement !");
850 executeUpdate(this.sqlPrepared);
851 return 0;
852 }
853
854
855
856
857
858 public void setNull(int parameterIndex, int sqlType) throws SQLException {
859 this.setString(parameterIndex, null );
860 }
861
862 public void setBoolean(int parameterIndex, boolean value) throws SQLException {
863 this.setString(parameterIndex, String.valueOf(value) );
864 }
865
866 public void setByte(int parameterIndex, byte value) throws SQLException {
867 this.setString(parameterIndex, String.valueOf(value) );
868 }
869
870 public void setShort(int parameterIndex, short value) throws SQLException {
871 this.setString(parameterIndex, String.valueOf(value) );
872 }
873
874 public void setInt(int parameterIndex, int value) throws SQLException {
875 this.setString(parameterIndex, String.valueOf(value) );
876 }
877
878 public void setLong(int parameterIndex, long value) throws SQLException {
879 this.setString(parameterIndex, String.valueOf(value) );
880 }
881
882 public void setFloat(int parameterIndex, float value) throws SQLException {
883 this.setString(parameterIndex, String.valueOf(value) );
884 }
885
886 public void setDouble(int parameterIndex, double value) throws SQLException {
887 this.setString(parameterIndex, String.valueOf(value) );
888 }
889
890 public void setBigDecimal(int parameterIndex, BigDecimal value) throws SQLException {
891 if(value == null) {
892 this.setString( parameterIndex, value.toString() );
893 } else {
894 this.setString( parameterIndex, "" );
895 }
896 }
897
898 public void setDate(int parameterIndex, Date value) throws SQLException {
899 if(value == null) {
900 this.setString( parameterIndex, value.toString() );
901 } else {
902 this.setString( parameterIndex, "" );
903 }
904 }
905
906 public void setTime(int parameterIndex, Time value) throws SQLException {
907 if(value == null) {
908 this.setString( parameterIndex, value.toString() );
909 } else {
910 this.setString( parameterIndex, "" );
911 }
912 }
913
914 public void setTimestamp(int parameterIndex, Timestamp value) throws SQLException {
915 if(value == null) {
916 this.setString( parameterIndex, value.toString() );
917 } else {
918 this.setString( parameterIndex, "" );
919 }
920 }
921
922 public void setAsciiStream(int parameterIndex, InputStream x, int length) throws SQLException {
923
924 throw new java.lang.UnsupportedOperationException ("Method setAsciiStream() not yet implemented.");
925 }
926 public void setUnicodeStream(int parameterIndex, InputStream x, int length) throws SQLException {
927
928 throw new java.lang.UnsupportedOperationException ("Method setUnicodeStream() not yet implemented.");
929 }
930
931 public void clearParameters() throws SQLException {
932 this.sqlPrepared = this.sqlForPrepare;
933 for(int i = 0; i < parameters.size(); i++)
934 parameters.set(i, null);
935 }
936 public void setObject(int parameterIndex, Object x, int targetSqlType, int scale) throws SQLException {
937
938 throw new java.lang.UnsupportedOperationException ("Method setObject() not yet implemented.");
939 }
940 public void setObject(int parameterIndex, Object x, int targetSqlType) throws SQLException {
941
942 throw new java.lang.UnsupportedOperationException ("Method setObject() not yet implemented.");
943 }
944 public void setObject(int parameterIndex, Object x) throws SQLException {
945 if( x == null )
946 setString(parameterIndex, null);
947 else
948 setString( parameterIndex, x.toString() );
949 }
950 public boolean execute() throws SQLException {
951
952 throw new java.lang.UnsupportedOperationException ("Method execute() not yet implemented.");
953 }
954 public void addBatch() throws SQLException {
955
956 throw new java.lang.UnsupportedOperationException ("Method addBatch() not yet implemented.");
957 }
958 public void setCharacterStream(int parameterIndex, Reader reader, int length) throws SQLException {
959
960 throw new java.lang.UnsupportedOperationException ("Method setCharacterStream() not yet implemented.");
961 }
962 public void setRef(int i, Ref x) throws SQLException {
963
964 throw new java.lang.UnsupportedOperationException ("Method setRef() not yet implemented.");
965 }
966
967 public void setClob(int i, Clob x) throws SQLException {
968
969 throw new java.lang.UnsupportedOperationException ("Method setClob() not yet implemented.");
970 }
971 public void setArray(int i, Array x) throws SQLException {
972
973 throw new java.lang.UnsupportedOperationException ("Method setArray() not yet implemented.");
974 }
975 public ResultSetMetaData getMetaData() throws SQLException {
976
977 throw new java.lang.UnsupportedOperationException ("Method getMetaData() not yet implemented.");
978 }
979 public void setDate(int parameterIndex, Date x, Calendar cal) throws SQLException {
980
981 throw new java.lang.UnsupportedOperationException ("Method setDate() not yet implemented.");
982 }
983 public void setTime(int parameterIndex, Time x, Calendar cal) throws SQLException {
984
985 throw new java.lang.UnsupportedOperationException ("Method setTime() not yet implemented.");
986 }
987 public void setTimestamp(int parameterIndex, Timestamp x, Calendar cal) throws SQLException {
988
989 throw new java.lang.UnsupportedOperationException ("Method setTimestamp() not yet implemented.");
990 }
991 public void setNull(int paramIndex, int sqlType, String typeName) throws SQLException {
992 this.setString(paramIndex, null );
993 }
994
995 public void setURL(int parameterIndex, URL x) throws SQLException {
996
997 throw new java.lang.UnsupportedOperationException (
998 "Method setURL() not yet implemented.");
999 }
1000
1001 public ParameterMetaData getParameterMetaData() throws SQLException {
1002
1003 throw new java.lang.UnsupportedOperationException (
1004 "Method getParameterMetaData() not yet implemented.");
1005 }
1006
1007 public void setBinaryStream(int parameterIndex, InputStream value, int length) throws
1008 SQLException {
1009 try {
1010 String hex = Utils.streamToHexString(value);
1011 this.setBytes(parameterIndex,Utils.hexStringToBytes(hex));
1012 } catch (Exception e) {
1013 throw new SQLException ("Error in setBinaryStream(): "+e.getMessage());
1014 }
1015 }
1016
1017 public void setBlob(int parameterIndex, Blob value) throws SQLException {
1018
1019 throw new java.lang.UnsupportedOperationException (
1020 "Method setBlob() not yet implemented.");
1021 }
1022
1023 public String getSqlStatement() {
1024 return sql;
1025 }
1026
1027} |
Popular Tags |
Java API By Example, From Geeks To Geeks.