Java > Open Source Codes > org > dbforms > event > datalist > dao > DataSourceJDBC


1 /*
2  * $Header: /cvsroot/jdbforms/dbforms/src/org/dbforms/event/datalist/dao/DataSourceJDBC.java,v 1.59 2005/02/19 21:26:30 hkollmann Exp $
3  * $Revision: 1.59 $
4  * $Date: 2005/02/19 21:26:30 $
5  *
6  * DbForms - a Rapid Application Development Framework
7  * Copyright (C) 2001 Joachim Peer <joepeer@excite.com>
8  *
9  * This library is free software; you can redistribute it and/or
10  * modify it under the terms of the GNU Lesser General Public
11  * License as published by the Free Software Foundation; either
12  * version 2.1 of the License, or (at your option) any later version.
13  *
14  * This library is distributed in the hope that it will be useful,
15  * but WITHOUT ANY WARRANTY; without even the implied warranty of
16  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
17  * Lesser General Public License for more details.
18  *
19  * You should have received a copy of the GNU Lesser General Public
20  * License along with this library; if not, write to the Free Software
21  * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
22  */
23
24 package org.dbforms.event.datalist.dao;
25
26 import org.apache.commons.logging.Log;
27 import org.apache.commons.logging.LogFactory;
28
29 import org.dbforms.config.Constants;
30 import org.dbforms.config.DbEventInterceptorData;
31 import org.dbforms.config.DbFormsConfigRegistry;
32 import org.dbforms.config.Field;
33 import org.dbforms.config.FieldTypes;
34 import org.dbforms.config.FieldValue;
35 import org.dbforms.config.FieldValues;
36 import org.dbforms.config.JDBCDataHelper;
37 import org.dbforms.config.ResultSetVector;
38
39 import org.dbforms.util.FileHolder;
40 import org.dbforms.util.UniqueIDGenerator;
41 import org.dbforms.util.Util;
42
43 import java.sql.Connection ;
44 import java.sql.PreparedStatement ;
45 import java.sql.ResultSet ;
46 import java.sql.ResultSetMetaData ;
47 import java.sql.SQLException ;
48 import java.sql.Statement ;
49
50 import java.util.ArrayList ;
51 import java.util.HashMap ;
52 import java.util.Iterator ;
53 import java.util.List ;
54 import java.util.Map ;
55 import java.util.Vector ;
56
57
58
59 /**
60  * Special implementation of DataSource. This is the default class and deals
61  * with JDBC Connections.
62  *
63  * @author hkk
64  */
65 public class DataSourceJDBC extends DataSource {
66    private static Log logCat = LogFactory.getLog(DataSourceJDBC.class);
67    private Connection connection;
68    private List data;
69    private Map keys;
70    private ResultSet rs;
71    private Statement stmt;
72    private String connectionName;
73    private String query;
74    private String sqlFilter;
75    private String tableList;
76    private String whereClause;
77    private FieldValue[] filterConstraint;
78    private FieldValue[] orderConstraint;
79    private FieldValue[] sqlFilterParams;
80    private boolean calcRowCount = false;
81    private boolean fetchedAll = false;
82    private int colCount;
83    private int rowCount = 0;
84
85    /**
86     * Creates a new DataSourceJDBC object.
87     */
88    public DataSourceJDBC() {
89       data = new ArrayList ();
90       keys = new HashMap ();
91    }
92
93    /**
94     * DOCUMENT ME!
95     *
96     * @param calcRowCount The calcRowCount to set.
97     */
98    public void setCalcRowCount(boolean calcRowCount) {
99       this.calcRowCount = calcRowCount;
100    }
101
102
103    /**
104     * DOCUMENT ME!
105     *
106     * @return Returns the calcRowCount.
107     */
108    public boolean isCalcRowCount() {
109       return calcRowCount;
110    }
111
112
113    /**
114     * Set the tableList and whererClause attributes used to build the SQL
115     * Select condition.
116     *
117     * @param tableList the table list string
118     * @param whereClause the SQL where clause string
119     */
120    public void setSelect(String tableList,
121                          String whereClause) {
122       this.tableList = tableList;
123       this.whereClause = whereClause;
124    }
125
126
127    /**
128     * Set the filterConstraint and orderConstraint used to build the SQL Select
129     * condition.
130     *
131     * @param filterConstraint FieldValue array used to build a cumulation of
132     * rules for filtering fields.
133     * @param orderConstraint FieldValue array used to build a cumulation of
134     * rules for ordering (sorting) and restricting fields.
135     * @param sqlFilter sql condition to add to where clause
136     * @param sqlFilterParams list of FieldValues to fill the sqlFilter with
137     */
138    public void setSelect(FieldValue[] filterConstraint,
139                          FieldValue[] orderConstraint,
140                          String sqlFilter,
141                          FieldValue[] sqlFilterParams) {
142       this.filterConstraint = filterConstraint;
143       this.orderConstraint = orderConstraint;
144       this.sqlFilter = sqlFilter;
145       this.sqlFilterParams = sqlFilterParams;
146    }
147
148
149    /**
150     * performs a delete in the DataSource
151     *
152     * @param interceptorData DOCUMENT ME!
153     * @param keyValuesStr keyValueStr to the row to update <br>
154     * key format: FieldID ":" Length ":" Value <br>
155     * example: if key id = 121 and field id=2 then keyValueStr contains
156     * "2:3:121" <br>
157     * If the key consists of more than one fields, the key values are
158     * seperated through "-" <br>
159     * example: value of field 1=12, value of field 3=1992, then we'll
160     * get "1:2:12-3:4:1992"
161     *
162     * @throws SQLException
163     */
164    public void doDelete(DbEventInterceptorData interceptorData,
165                         String keyValuesStr)
166                  throws SQLException {
167       FieldValues fieldValues = null;
168
169       // get current blob files from database
170 if (getTable()
171                    .containsDiskblob()) {
172          ResultSet diskblobs = null;
173          StringBuffer queryBuf = new StringBuffer ();
174          queryBuf.append(getTable().getDisblobSelectStatement());
175          queryBuf.append(" WHERE ");
176          queryBuf.append(getTable().getWhereClauseForKeyFields());
177
178          PreparedStatement diskblobsPs = interceptorData.getConnection()
179                                                         .prepareStatement(queryBuf
180                                                                           .toString());
181
182          try {
183             getTable()
184                .populateWhereClauseWithKeyFields(keyValuesStr, diskblobsPs, 1);
185
186             diskblobs = diskblobsPs.executeQuery();
187
188             try {
189                ResultSetVector rsv = new ResultSetVector(getTable(),
190                                                          getTable().getDiskblobs());
191                rsv.addResultSet(interceptorData, diskblobs);
192
193                if (!ResultSetVector.isNull(rsv)) {
194                   rsv.moveFirst();
195                   fieldValues = rsv.getCurrentRowAsFieldValues();
196                }
197             } finally {
198                diskblobs.close();
199             }
200          } finally {
201             diskblobsPs.close();
202          }
203       }
204
205       // 20021031-HKK: Build in table!!
206 PreparedStatement ps = interceptorData.getConnection()
207                                             .prepareStatement(getTable().getDeleteStatement());
208
209       try {
210          // now we provide the values
211 // of the key-fields, so that the WHERE clause matches the right
212 // dataset!
213 getTable()
214             .populateWhereClauseWithKeyFields(keyValuesStr, ps, 1);
215
216          // finally execute the query
217 ps.executeUpdate();
218
219          if (fieldValues != null) {
220             deleteBlobFilesFromDisk(fieldValues);
221          }
222       } finally {
223          ps.close();
224       }
225    }
226
227
228    /**
229     * Performs an insert into the DataSource
230     *
231     * @param interceptorData DOCUMENT ME!
232     * @param fieldValues FieldValues to insert
233     *
234     * @throws SQLException
235     */
236    public void doInsert(DbEventInterceptorData interceptorData,
237                         FieldValues fieldValues)
238                  throws SQLException {
239       PreparedStatement ps = interceptorData.getConnection()
240                                             .prepareStatement(getTable().getInsertStatement(fieldValues));
241
242       try {
243          // execute the query & throws an exception if something goes wrong
244 fillWithData(ps, fieldValues);
245          ps.executeUpdate();
246       } finally {
247          ps.close();
248       }
249
250       // now handle blob files
251 saveBlobFilesToDisk(fieldValues);
252    }
253
254
255    /**
256     * Performs an update into the DataSource
257     *
258     * @param interceptorData DOCUMENT ME!
259     * @param fieldValues FieldValues to update
260     * @param keyValuesStr keyValueStr to the row to update <br>
261     * key format: FieldID ":" Length ":" Value <br>
262     * example: if key id = 121 and field id=2 then keyValueStr contains
263     * "2:3:121" <br>
264     * If the key consists of more than one fields, the key values are
265     * seperated through "-" <br>
266     * example: value of field 1=12, value of field 3=1992, then we'll
267     * get "1:2:12-3:4:1992"
268     *
269     * @throws SQLException
270     */
271    public void doUpdate(DbEventInterceptorData interceptorData,
272                         FieldValues fieldValues,
273                         String keyValuesStr)
274                  throws SQLException {
275       PreparedStatement ps = interceptorData.getConnection()
276                                             .prepareStatement(getTable().getUpdateStatement(fieldValues));
277
278       try {
279          int col = fillWithData(ps, fieldValues);
280          getTable()
281             .populateWhereClauseWithKeyFields(keyValuesStr, ps, col);
282
283          // we are now ready to execute the query
284 ps.executeUpdate();
285       } finally {
286          ps.close();
287       }
288
289       // now handle blob files
290 saveBlobFilesToDisk(fieldValues);
291    }
292
293
294    /**
295     * set the connection parameter for the DataSouce. virtual method, if you
296     * need the connection data you must override the method In this special
297     * case we need our own connection to save it in the session.
298     *
299     * @param con the JDBC Connection object
300     * @param dbConnectionName name of the used db connection. Can be used to
301     * get an own db connection, e.g. to hold it during the session (see
302     * DataSourceJDBC for example!)
303     */
304    protected void setConnection(Connection con,
305                                 String dbConnectionName) {
306       close();
307
308       // To prevent empty connection name. We always need our own connection!
309 connectionName = Util.isNull(dbConnectionName) ? "default"
310                                                      : dbConnectionName;
311    }
312
313
314    /**
315     * Get the requested row as array of objects.
316     *
317     * @param i the row number
318     *
319     * @return the requested row as array of objects
320     *
321     * @throws SQLException if any error occurs
322     */
323    protected final Object [] getRow(int i) throws SQLException {
324       Object [] result = null;
325
326       if (i >= 0) {
327          if (i < data.size()) {
328             result = (Object []) data.get(i);
329          } else {
330             if (!fetchedAll) {
331                while (rs.next()) {
332                   addRow();
333
334                   if (i < data.size()) {
335                      result = (Object []) data.get(i);
336
337                      break;
338                   }
339                }
340
341                checkResultSetEnd();
342             }
343          }
344       }
345
346       return result;
347    }
348
349
350    /**
351     * Release all the resources holded by this datasource. <br>
352     * Clean the underlying data and keys vectors, then close the JDBC
353     * resultSet, statement and connection objects.
354     */
355    protected final void close() {
356       if (data != null) {
357          data.clear();
358       }
359
360       if (keys != null) {
361          keys.clear();
362       }
363
364       closeConnection();
365
366       // reset fetched all flag. So DataSource can be reopened after close!
367 fetchedAll = false;
368    }
369
370
371    /**
372     * Find the first row of the internal data vector.
373     *
374     * @param startRow the string identifying the initial row
375     *
376     * @return the start row position
377     *
378     * @throws SQLException if any error occurs
379     */
380    protected final int findStartRow(String startRow) throws SQLException {
381       int result = 0;
382       boolean found = false;
383
384       if (startRow != null) {
385          Integer i = (Integer ) keys.get(startRow);
386
387          if (i != null) {
388             result = i.intValue();
389             found = true;
390          }
391
392          if (!found && !fetchedAll) {
393             while (rs.next()) {
394                String key = addRow();
395
396                if (startRow.equals(key)) {
397                   result = data.size() - 1;
398
399                   break;
400                }
401             }
402
403             checkResultSetEnd();
404          }
405       }
406
407       return result;
408    }
409
410
411    /**
412     * return true if there are more records to fetch then the given record
413     * number
414     *
415     * @param i index of last fetched row.
416     *
417     * @return true if there are more records to fetch then the given record
418     * number
419     *
420     * @throws SQLException
421     */
422    protected final boolean hasMore(int i) throws SQLException {
423       return !fetchedAll || (i < size());
424    }
425
426
427    /**
428     * Open this datasource and initialize its resources.
429     *
430     * @throws SQLException if any error occurs
431     */
432    protected void open() throws SQLException {
433       if (!fetchedAll && (rs == null)) {
434          if ((connection == null) || connection.isClosed()) {
435             try {
436                this.connection = DbFormsConfigRegistry.instance()
437                                                       .lookup()
438                                                       .getConnection(connectionName);
439             } catch (Exception e) {
440                logCat.error("open", e);
441             }
442          }
443
444          if (connection == null) {
445             throw new SQLException ("no connection found!");
446          }
447
448          if (Util.isNull(whereClause)) {
449             query = getTable()
450                        .getSelectQuery(getTable().getFields(),
451                                        filterConstraint, orderConstraint,
452                                        sqlFilter, Constants.COMPARE_NONE);
453             stmt = connection.prepareStatement(query);
454
455             if (stmt == null) {
456                throw new SQLException ("no statement: " + query);
457             }
458
459             // 20040730-HKK: To workaround a bug inside mysql driver
460 // stmt.setFetchSize(Integer.MIN_VALUE);
461 rs = getTable()
462                     .getDoSelectResultSet(filterConstraint, orderConstraint,
463                                           sqlFilterParams,
464                                           Constants.COMPARE_NONE,
465                                           (PreparedStatement ) stmt);
466          } else {
467             query = getTable()
468                        .getFreeFormSelectQuery(getTable().getFields(),
469                                                whereClause, tableList);
470             stmt = connection.createStatement();
471
472             if (stmt == null) {
473                throw new SQLException ("no statement");
474             }
475
476             rs = stmt.executeQuery(query);
477          }
478
479          ResultSetMetaData rsmd = rs.getMetaData();
480          colCount = rsmd.getColumnCount();
481          
482          if (isCalcRowCount()) {
483             Field f = new Field();
484             f.setName("count(*) cnt");
485             Vector v = new Vector();
486             v.add(f);
487 // v.addAll(getTable().getFields());
488 ResultSet prs = null;
489             if (Util.isNull(whereClause)) {
490                 String pquery = getTable()
491                            .getSelectQuery(v,
492                                            filterConstraint, orderConstraint,
493                                            sqlFilter, Constants.COMPARE_NONE);
494                 PreparedStatement pstmt = connection.prepareStatement(pquery);
495
496                 if (pstmt == null) {
497                    throw new SQLException ("no statement: " + pquery);
498                 }
499
500                 prs = getTable()
501                         .getDoSelectResultSet(filterConstraint, orderConstraint,
502                                               sqlFilterParams,
503                                               Constants.COMPARE_NONE,
504                                               pstmt);
505              } else {
506                 String pquery = getTable()
507                            .getFreeFormSelectQuery(v,
508                                                    whereClause, tableList);
509                 Statement pstmt = connection.createStatement();
510
511                 if (pstmt == null) {
512                    throw new SQLException ("no statement");
513                 }
514
515                 prs = pstmt.executeQuery(pquery);
516              }
517             prs.next();
518             rowCount = prs.getInt(prs.findColumn("cnt"));
519          }
520       }
521    }
522
523
524    /**
525     * Get the size of the data vector.
526     *
527     * @return the size of the data vector
528     *
529     * @throws SQLException if any error occurs
530     */
531    protected final int size() throws SQLException {
532       // Workaround for bug in firebird driver: After reaching next the next
533 // call
534 // to next will start at the beginning of the resultset.
535 // rs.next will return true, fetching data will get an
536 // NullPointerException.
537 // Catch this error and do an break!
538 if (!fetchedAll) {
539          while (rs.next()) {
540             try {
541                addRow();
542             } catch (Exception e) {
543                logCat.error("size", e);
544
545                break;
546             }
547          }
548
549          closeConnection();
550       }
551
552       return data.size();
553    }
554
555
556    /**
557     * DOCUMENT ME!
558     *
559     * @return Returns the rowCount.
560     */
561    protected int getRowCount() {
562       return rowCount;
563    }
564
565
566    private String addRow() throws SQLException {
567       Integer j = new Integer (data.size());
568       Object [] objectRow = new Object [colCount];
569       String [] stringRow = new String [colCount];
570
571       for (int i = 0; i < colCount; i++) {
572          objectRow[i] = JDBCDataHelper.getData(rs,
573                                                getTable().getField(i).getEscaper(),
574                                                i + 1);
575          stringRow[i] = (objectRow[i] != null) ? objectRow[i].toString()
576                                                : null;
577       }
578
579       data.add(objectRow);
580
581       String key = getTable()
582                       .getKeyPositionString(stringRow);
583       keys.put(key, j);
584
585       return key;
586    }
587
588
589    private void checkResultSetEnd() throws SQLException {
590       if ((rs.getRow() != 0)) {
591          // test if next record is avaiable...
592 // rs.isLast is not allowed in all circumstances!
593 if (rs.next()) {
594             addRow();
595          }
596       }
597
598       if ((rs.getRow() == 0) /* || rs.isLast() 20031510-HKK: removed because of Oracle problems */) {
599          closeConnection();
600       }
601    }
602
603
604    private void closeConnection() {
605       fetchedAll = true;
606
607       if (rs != null) {
608          try {
609             rs.close();
610          } catch (SQLException e) {
611             logCat.info("closeConnection", e);
612          }
613
614          rs = null;
615       }
616
617       if (stmt != null) {
618          try {
619             stmt.close();
620          } catch (SQLException e) {
621             logCat.info("closeConnection", e);
622          }
623
624          stmt = null;
625       }
626
627       if (connection != null) {
628          try {
629             if (!connection.isClosed()) {
630                connection.close();
631             }
632          } catch (SQLException e) {
633             logCat.info("closeConnection", e);
634          }
635
636          connection = null;
637       }
638    }
639
640
641    //------------------------------ DAO methods
642 // ---------------------------------
643 private int fillWithData(PreparedStatement ps,
644                             FieldValues fieldValues)
645                      throws SQLException {
646       // now we provide the values;
647 // every key is the parameter name from of the form page;
648 Iterator e = fieldValues.keys();
649       int col = 1;
650
651       while (e.hasNext()) {
652          String fieldName = (String ) e.next();
653          Field curField = getTable()
654                               .getFieldByName(fieldName);
655
656          if (curField != null) {
657             FieldValue fv = fieldValues.get(fieldName);
658
659             logCat.debug("Retrieved curField:" + curField.getName() + " type:"
660                          + curField.getType());
661
662             int fieldType = curField.getType();
663             Object value = null;
664
665             if (fieldType == FieldTypes.BLOB) {
666                // in case of a BLOB we supply the FileHolder object to SqlUtils for further operations
667 if (fv.getFileHolder() == null) { // if the blob field is updated from within textarea
668 value = fv.getFieldValue();
669                } else { // if we have a file upload
670 value = fv.getFileHolder();
671                }
672             } else if (fieldType == FieldTypes.DISKBLOB) {
673                FileHolder fileHolder = fv.getFileHolder();
674
675                // encode fileName
676 String fileName = fileHolder.getFileName();
677
678                // check if we need to store it encoded or not
679 if (curField.hasEncodedSet()) {
680                   int dotIndex = fileName.lastIndexOf('.');
681                   String suffix = (dotIndex != -1)
682                                   ? fileName.substring(dotIndex)
683                                   : "";
684                   fileHolder.setFileName(UniqueIDGenerator.getUniqueID()
685                                          + suffix);
686
687                   // a diskblob gets stored to db as an ordinary string
688 // (it's only the reference!)
689 value = fileHolder.getFileName();
690                } else {
691                   // a diskblob gets stored to db as an ordinary string
692 // (it's only the reference!)
693 value = fileName;
694                }
695             } else {
696                // in case of simple db types we just supply a string
697 // representing the value of the fields
698 value = fv.getFieldValueAsObject();
699             }
700
701             logCat.info("field=" + curField.getName() + " col=" + col
702                         + " value=" + value + " type=" + fieldType);
703             JDBCDataHelper.fillWithData(ps, curField.getEscaper(), col, value,
704                                         fieldType,
705                                         getTable().getBlobHandlingStrategy());
706             col++;
707          }
708       }
709
710       return col;
711    }
712 }
713

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags