Java > Open Source Codes > org > webdocwf > util > i18njdbc > I18nPreparedStatement


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
21 package org.webdocwf.util.i18njdbc;
22
23 import java.io.File ;
24 import java.io.FileInputStream ;
25 import java.io.InputStream ;
26 import java.io.Reader ;
27 import java.math.BigDecimal ;
28 import java.net.URL ;
29 import java.sql.Array ;
30 import java.sql.Blob ;
31 import java.sql.Clob ;
32 import java.sql.Connection ;
33 import java.sql.Date ;
34 import java.sql.DriverManager ;
35 import java.sql.ParameterMetaData ;
36 import java.sql.PreparedStatement ;
37 import java.sql.Ref ;
38 import java.sql.ResultSet ;
39 import java.sql.ResultSetMetaData ;
40 import java.sql.SQLException ;
41 import java.sql.SQLWarning ;
42 import java.sql.Time ;
43 import java.sql.Timestamp ;
44 import java.util.ArrayList ;
45 import java.util.Calendar ;
46 import java.util.Enumeration ;
47 import java.util.Vector ;
48
49 /**
50  * This class implements the PreparedStatement interface for the I18nJdbc driver.
51  *
52  * @author Zoran Milakovic
53  * @author Zeljko Kovacevic
54  */
55 public class I18nPreparedStatement implements PreparedStatement {
56
57   private I18nConnection connection;
58   private Vector resultSets = new Vector ();
59   private String sqlForPrepare = "";
60   private String sqlPrepared = "";
61   private int paramCount = 0;
62   private ArrayList parameters = new ArrayList ();
63   private ArrayList binaryStreamParameters = new ArrayList ();
64 /** Name used for prepared statement parameters */
65   public static String PREPARE_SEPARATOR = "~@#NEXTPARAMETER#@~";
66   private String sql;
67
68   public I18nPreparedStatement(I18nConnection connection, String preparedSql) {
69     DriverManager.println("I18nJdbc - I18nStatement() - connection=" + connection);
70     this.sqlForPrepare = preparedSql;
71     this.sqlPrepared = preparedSql;
72     this.paramCount = countParameters(preparedSql);
73     for ( int i = 0; i < paramCount; i++ )
74       parameters.add(null);
75     this.connection = connection;
76   }
77
78   private int countParameters(String sql) {
79     int count = 0;
80     int index = sql.indexOf(PREPARE_SEPARATOR);
81     while( index != -1 ) {
82       count++;
83       sql = sql.substring(index+1);
84       index = sql.indexOf(PREPARE_SEPARATOR);
85     }
86     return count;
87   }
88
89   private boolean prepareSql() throws SQLException {
90     boolean retVal = true;
91     for (int i = 0; i < parameters.size(); i++) {
92       int index = sqlPrepared.indexOf(PREPARE_SEPARATOR);
93       String val;
94       if (index != -1) {
95         if( parameters.get(i) == null )
96           val = "null";
97         else
98           val = parameters.get(i).toString();
99         sqlPrepared = sqlPrepared.substring(0, index) +
100             val +
101             sqlPrepared.substring(index + PREPARE_SEPARATOR.length());
102       }
103     }
104     if (sqlPrepared.indexOf(PREPARE_SEPARATOR) != -1)
105       throw new SQLException (
106           "All ? in prepared query has to be replaced with values.");
107     else if (parameters.size() < this.paramCount)
108       throw new SQLException (
109           "Number of setted parameters is less than number of parameters in statement.");
110     else if (parameters.size() > this.paramCount)
111       throw new SQLException (
112           "Number of setted parameters is greater than number of parameters in statement.");
113     return retVal;
114   }
115
116   /**
117      *Sets the maxFieldSize attribute of the I18nStatement object
118      *
119      * @param p0 The new maxFieldSize value
120      * @exception SQLException Description of Exception
121      * @since
122      */
123     public void setMaxFieldSize(int p0) throws SQLException
124     {
125         throw new SQLException ("Not Supported !");
126     }
127
128
129     /**
130      *Sets the maxRows attribute of the I18nStatement object
131      *
132      * @param p0 The new maxRows value
133      * @exception SQLException Description of Exception
134      * @since
135      */
136     public void setMaxRows(int p0) throws SQLException
137     {
138         throw new SQLException ("Not Supported !");
139     }
140
141
142     /**
143      *Sets the escapeProcessing attribute of the I18nStatement object
144      *
145      * @param p0 The new escapeProcessing value
146      * @exception SQLException Description of Exception
147      * @since
148      */
149     public void setEscapeProcessing(boolean p0) throws SQLException
150     {
151         throw new SQLException ("Not Supported !");
152     }
153
154
155     /**
156      *Sets the queryTimeout attribute of the I18nStatement object
157      *
158      * @param p0 The new queryTimeout value
159      * @exception SQLException Description of Exception
160      * @since
161      */
162     public void setQueryTimeout(int p0) throws SQLException
163     {
164         throw new SQLException ("Not Supported !");
165     }
166
167
168     /**
169      *Sets the cursorName attribute of the I18nStatement object
170      *
171      * @param p0 The new cursorName value
172      * @exception SQLException Description of Exception
173      * @since
174      */
175     public void setCursorName(String p0) throws SQLException
176     {
177         throw new SQLException ("Not Supported !");
178     }
179
180
181     /**
182      *Sets the fetchDirection attribute of the I18nStatement object
183      *
184      * @param p0 The new fetchDirection value
185      * @exception SQLException Description of Exception
186      * @since
187      */
188     public void setFetchDirection(int p0) throws SQLException
189     {
190         throw new SQLException ("Not Supported !");
191     }
192
193
194     /**
195      *Sets the fetchSize attribute of the I18nStatement object
196      *
197      * @param p0 The new fetchSize value
198      * @exception SQLException Description of Exception
199      * @since
200      */
201     public void setFetchSize(int p0) throws SQLException
202     {
203         throw new SQLException ("Not Supported !");
204     }
205
206
207     /**
208      *Gets the maxFieldSize attribute of the I18nStatement object
209      *
210      * @return The maxFieldSize value
211      * @exception SQLException Description of Exception
212      * @since
213      */
214     public int getMaxFieldSize() throws SQLException
215     {
216         throw new SQLException ("Not Supported !");
217     }
218
219
220     /**
221      *Gets the maxRows attribute of the I18nStatement object
222      *
223      * @return The maxRows value
224      * @exception SQLException Description of Exception
225      * @since
226      */
227     public int getMaxRows() throws SQLException
228     {
229         throw new SQLException ("Not Supported !");
230     }
231
232
233     /**
234      *Gets the queryTimeout attribute of the I18nStatement object
235      *
236      * @return The queryTimeout value
237      * @exception SQLException Description of Exception
238      * @since
239      */
240     public int getQueryTimeout() throws SQLException
241     {
242         throw new SQLException ("Not Supported !");
243     }
244
245
246     /**
247      *Gets the warnings attribute of the I18nStatement object
248      *
249      * @return The warnings value
250      * @exception SQLException Description of Exception
251      * @since
252      */
253     public SQLWarning getWarnings() throws SQLException
254     {
255         throw new SQLException ("Not Supported !");
256     }
257
258
259     /**
260      *Gets the resultSet attribute of the I18nStatement object
261      *
262      * @return The resultSet value
263      * @exception SQLException Description of Exception
264      * @since
265      */
266     public ResultSet getResultSet() throws SQLException
267     {
268         throw new SQLException ("Not Supported !");
269     }
270
271
272     /**
273      *Gets the updateCount attribute of the I18nStatement object
274      *
275      * @return The updateCount value
276      * @exception SQLException Description of Exception
277      * @since
278      */
279     public int getUpdateCount() throws SQLException
280     {
281         throw new SQLException ("Not Supported !");
282     }
283
284
285     /**
286      *Gets the moreResults attribute of the I18nStatement object
287      *
288      * @return The moreResults value
289      * @exception SQLException Description of Exception
290      * @since
291      */
292     public boolean getMoreResults() throws SQLException
293     {
294         throw new SQLException ("Not Supported !");
295     }
296
297
298     /**
299      *Gets the fetchDirection attribute of the I18nStatement object
300      *
301      * @return The fetchDirection value
302      * @exception SQLException Description of Exception
303      * @since
304      */
305     public int getFetchDirection() throws SQLException
306     {
307         throw new SQLException ("Not Supported !");
308     }
309
310
311     /**
312      *Gets the fetchSize attribute of the I18nStatement object
313      *
314      * @return The fetchSize value
315      * @exception SQLException Description of Exception
316      * @since
317      */
318     public int getFetchSize() throws SQLException
319     {
320         throw new SQLException ("Not Supported !");
321     }
322
323
324     /**
325      *Gets the resultSetConcurrency attribute of the I18nStatement object
326      *
327      * @return The resultSetConcurrency value
328      * @exception SQLException Description of Exception
329      * @since
330      */
331     public int getResultSetConcurrency() throws SQLException
332     {
333         throw new SQLException ("Not Supported !");
334     }
335
336
337     /**
338      *Gets the resultSetType attribute of the I18nStatement object
339      *
340      * @return The resultSetType value
341      * @exception SQLException Description of Exception
342      * @since
343      */
344     public int getResultSetType() throws SQLException
345     {
346         throw new SQLException ("Not Supported !");
347     }
348
349
350     /**
351      *Gets the connection attribute of the I18nStatement object
352      *
353      * @return The connection value
354      * @exception SQLException Description of Exception
355      * @since
356      */
357     public Connection getConnection() throws SQLException
358     {
359         return connection;
360     }
361
362
363     /**
364      *Description of the Method
365      *
366      * @param sql Description of Parameter
367      * @return Description of the Returned Value
368      * @exception SQLException Description of Exception
369      * @since
370      */
371     public ResultSet executeQuery(String sql) throws SQLException
372     {
373         DriverManager.println("I18nJdbc - I18nStatement:executeQuery() - sql= " + sql);
374         I18nSqlParser parser = new I18nSqlParser();
375         this.sql = sql;
376         try
377             {
378                 parser.parse(this);
379             }
380         catch (Exception e)
381             {
382                 throw new SQLException ("Syntax Error. " + e.getMessage());
383             }
384
385     String fileName = connection.getPath() + parser.getTableName() + connection.getExtension();
386     File checkFile = new File (fileName);
387     if (!checkFile.exists())
388     {
389       throw new SQLException ("Cannot open data file '" + fileName + "' !");
390     }
391
392     if (!checkFile.canRead())
393     {
394       throw new SQLException ("Data file '" + fileName + "' not readable !");
395     }
396    
397     try
398     {
399
400                 String [] xxx = parser.getColumnNames();
401               String [] yyy = connection.getColumnNames();
402               boolean isOK = true;
403               for(int i=0; i< xxx.length; i++) {
404               if(!xxx[i].endsWith("*")) {
405                out:
406               for(int j=0; j< yyy.length; j++) {
407                  if(xxx[i].equalsIgnoreCase(yyy[j])) {
408                     isOK=true;
409                     break out;
410                   }
411                   else
412                     isOK=false;
413                 }
414                if(!isOK)
415                 throw new SQLException ("Column '" + xxx[i] + "' not found.");
416               }
417               }
418     }
419     catch (Exception e)
420     {
421         if( I18nDriver.DEBUG )
422             e.printStackTrace();
423       throw new SQLException ("Error reading data file. Message was: " + e);
424     }
425     
426     this.connection.setCurrentTableName(parser.getTableName());
427     
428     //load properties file
429 try {
430         if(connection.getAutoCommit()) {
431             connection.getProperties().load(new FileInputStream (checkFile));
432         }
433     } catch (Exception e) {
434         throw new SQLException ("Error while loading properties");
435     }
436
437         I18nResultSet resultSet = new I18nResultSet(this,
438                     parser.getTableName(), parser.getColumnNames(), parser.getWhereColumnNames(),
439                     parser.getWhereColumnValues());
440     resultSets.add(resultSet);
441     return resultSet;
442   }
443
444
445
446     /**
447      *Description of the Method
448      *
449      * @param sql Description of Parameter
450      * @return Description of the Returned Value
451      * @exception SQLException Description of Exception
452      * @since
453      */
454     public int executeUpdate(String sql) throws SQLException
455    {
456    int updated=0;
457    DriverManager.println("I18nJdbc - I18nStatement:executeUpdate() - sql= " + sql);
458    I18nSqlParser parser = new I18nSqlParser();
459    this.sql = sql;
460    try
461    {
462      parser.parse(this);
463    }
464    catch (Exception e)
465    {
466      throw new SQLException ("Syntax Error. " + e.getMessage());
467    }
468      if(parser.sqlType.equals(I18nSqlParser.SELECT))
469            throw new SQLException ("Not supported SELECT statement - use executeQuery method");
470      else if (parser.sqlType.equals(I18nSqlParser.CREATE_TABLE)) {
471                throw new SQLException ("Not supported CREATE TABLE statement - use execute method");
472      }
473      else if (parser.sqlType.equals(I18nSqlParser.INSERT)) {
474           String fileName = connection.getPath() + parser.getTableName() + connection.getExtension();
475            File checkFile = new File (fileName);
476
477            if (!checkFile.exists())
478            {
479            throw new SQLException ("Cannot open data file '" + fileName + "' !");
480            }
481
482            if (!checkFile.canWrite())
483            {
484            throw new SQLException ("Data file '" + fileName + "' is read only !");
485            }
486              try
487              {
488                String [] xxx = parser.getColumnNames();
489                                String [] yyy = connection.getColumnNames();
490                boolean isOK = true;
491                for(int i=0; i< xxx.length; i++) {
492                  if(!xxx[i].endsWith("*")) {
493                  out:
494                  for(int j=0; j< yyy.length; j++) {
495                    if(xxx[i].equalsIgnoreCase(yyy[j])) {
496                      isOK=true;
497                      break out;
498                    }
499                  else
500                    isOK=false;
501                  }
502                if(!isOK)
503                  throw new SQLException ("Column '" + xxx[i] + "' not found.");
504                }
505              }
506                 try {
507                            String [] colValues = parser.columnValues;
508                            String [] colNames = parser.columnNames;
509                            String keyColumn=connection.getNameColumn();
510                            String key;
511                            String value;
512                              if (colNames.length!=colValues.length){
513                                         throw new Exception ("Number of columns and number of values are different!");
514                              }
515                            if (colNames[0].equalsIgnoreCase(keyColumn)){
516                                key = colValues[0];
517                                value = colValues[1];
518                            }else{
519                              key = colValues[1];
520                              value = colValues[0];
521                            }
522                            
523                            connection.setCurrentTableName(parser.getTableName());
524                            
525                            if(connection.getAutoCommit()) {
526                                connection.getProperties().load(new FileInputStream (checkFile));
527                            }
528                            if (connection.getProperties().containsKey(key)==true){
529                                    throw new Exception ("Key already exist in the property file. Key must be unique!");
530                            }else{
531                                connection.getProperties().put(key,value);
532                                if(connection.getAutoCommit()) {
533                                    connection.getProperties().store(checkFile);
534                                }
535                                  updated++;
536                            }
537                             
538                        } catch (Exception e) {
539                           throw new SQLException ("Error writing data to property file."+e.getMessage());
540                        }
541            }
542            catch (Exception e)
543            {
544            throw new SQLException ("Error writing data file. Message was: " + e);
545            }
546
547      }
548     else if (parser.sqlType.equals(I18nSqlParser.UPDATE)) {
549
550                String fileName = connection.getPath() + parser.getTableName() + connection.getExtension();
551            File checkFile = new File (fileName);
552
553            if (!checkFile.exists())
554            {
555            throw new SQLException ("Cannot open data file '" + fileName + "' !");
556            }
557
558            if (!checkFile.canWrite())
559            {
560            throw new SQLException ("Data file '" + fileName + "' is read only !");
561            }
562
563            try
564            {
565                String [] xxx = parser.getColumnNames();
566                String [] yyy = connection.getColumnNames();
567                boolean isOK = true;
568                for(int i=0; i< xxx.length; i++) {
569                  if(!xxx[i].endsWith("*")) {
570                    out:
571                  for(int j=0; j< yyy.length; j++) {
572                    if(xxx[i].equalsIgnoreCase(yyy[j])) {
573                      isOK=true;
574                      break out;
575                    }
576                  else
577                    isOK=false;
578                }
579                if(!isOK)
580                  throw new SQLException ("Column '" + xxx[i] + "' not found.");
581                  }
582                }
583                
584                        try {
585                               String [] colValues = parser.columnValues;
586                               String [] colNames = parser.columnNames;
587                               String [] colWhereNames = parser.columnWhereNames;
588                               String [] colWhereValues = parser.columnWhereValues;
589                               String keyColumn=connection.getNameColumn();
590                                 String valueColumn=connection.getValueColumn();
591                               String key = "";
592                               String value = "";
593                               boolean paramsOK = true;
594                               //pick up values which will be updated.This block is used if firs column is
595 // key column
596 if (colNames[0].equalsIgnoreCase(keyColumn)){
597                                       if (colValues.length==1){
598                                        key = colValues[0];
599                                       }else if (colValues.length==2){
600                                            key = colValues[0];
601                                            value = colValues[1];
602                                       }
603                                //pick up values which will be updated.This block is used if first column is
604 //value column
605 }else if (colNames[0].equalsIgnoreCase(valueColumn)){
606                                            if (colValues.length==1){
607                                                value = colValues[0];
608                                            }else if (colValues.length==2){
609                                                value = colValues[0];
610                                                key = colValues[1];
611                                            }
612                                   }
613                                  
614                                   connection.setCurrentTableName(parser.getTableName());
615                                   
616                                   if(connection.getAutoCommit()) {
617                                       connection.getProperties().load(new FileInputStream (checkFile));
618                                   }
619                                     if (!(colWhereNames[0].equalsIgnoreCase(keyColumn) || colWhereNames[0].equalsIgnoreCase(valueColumn))){
620                                             throw new SQLException ("Error in sql statement. Wrong column name!");
621                                     }
622                                    if ((!key.equalsIgnoreCase("")) && (!value.equalsIgnoreCase(""))){
623                                        Enumeration en = connection.getProperties().keys();
624                                            while(en.hasMoreElements()){
625                                                    String oldKey = en.nextElement().toString();
626                                                    String oldValue = connection.getProperties().getProperty(oldKey);
627                                                    //update key and value if this key is equal with key from where claus and also column name from where claus is equal with key column
628 //or update key and value if this value is equal with value from where claus and also column name from where claus is equal with value column
629 if ((colWhereValues[0].equalsIgnoreCase(oldKey) && keyColumn.equals(colWhereNames[0]))
630                                                            ||(colWhereValues[0].equalsIgnoreCase(oldValue) && valueColumn.equals(colWhereNames[0]))){
631                                                        connection.getProperties().remove(oldKey);
632                                                        connection.getProperties().put(key, value);
633                                                        updated++;
634                                                }
635                                            }
636                                         
637                                        }else if (!value.equalsIgnoreCase("")){
638                                 
639                                            Enumeration en = connection.getProperties().keys();
640                                                while(en.hasMoreElements()){
641                                                    String oldKey = en.nextElement().toString();
642                                                    String oldValue = connection.getProperties().getProperty(oldKey);
643                                                    //update value if this key is equal with key from where claus and also column name from where claus is equal with key column
644 //or update value if this value is equal with value from where claus and also column name from where claus is equal with value column
645 if ((colWhereValues[0].equalsIgnoreCase(oldKey) && keyColumn.equals(colWhereNames[0]))
646                                                             ||(colWhereValues[0].equalsIgnoreCase(oldValue) && valueColumn.equals(colWhereNames[0]))){
647                                                            connection.getProperties().put(oldKey,value);
648                                                            updated++;
649                                                        }
650                                                }
651                                    }else if (!key.equalsIgnoreCase("")){
652                                         
653                                                Enumeration en = connection.getProperties().keys();
654                                                while(en.hasMoreElements()){
655                                                    String oldKey = en.nextElement().toString();
656                                                    String oldValue = connection.getProperties().getProperty(oldKey);
657                                                    //update key if this key is equal with key from where claus and also column name from where claus is equal with key column
658 //or update key if this value is equal with value from where claus and also column name from where claus is equal with value column
659 if ((colWhereValues[0].equalsIgnoreCase(oldKey) && keyColumn.equals(colWhereNames[0]))
660                                                            &nbs