HsqlDBDataUtils


1   /*
2    * Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3    * 
4    * Project: OpenSubsystems
5    * 
6    * $Id: HsqlDBDataUtils.java,v 1.4 2007/01/07 06:15:15 bastafidli Exp $
7    * 
8    * This program is free software; you can redistribute it and/or modify
9    * it under the terms of the GNU General Public License as published by
10   * the Free Software Foundation; version 2 of the License. 
11   * 
12   * This program is distributed in the hope that it will be useful,
13   * but WITHOUT ANY WARRANTY; without even the implied warranty of
14   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
15   * GNU General Public License for more details.
16   * 
17   * You should have received a copy of the GNU General Public License
18   * along with this program; if not, write to the Free Software
19   * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA 
20   */
21  
22  package org.opensubsystems.core.persist.db.hsqldb;
23  
24  import java.sql.CallableStatement  ;
25  import java.sql.Connection  ;
26  import java.sql.PreparedStatement  ;
27  import java.sql.ResultSet  ;
28  import java.sql.SQLException  ;
29  
30  import org.opensubsystems.core.data.BasicDataObject;
31  import org.opensubsystems.core.data.DataObject;
32  import org.opensubsystems.core.data.ModifiableDataObject;
33  import org.opensubsystems.core.error.OSSDataCreateException;
34  import org.opensubsystems.core.error.OSSDataSaveException;
35  import org.opensubsystems.core.error.OSSDatabaseAccessException;
36  import org.opensubsystems.core.error.OSSException;
37  import org.opensubsystems.core.error.OSSInconsistentDataException;
38  import org.opensubsystems.core.persist.db.DatabaseDataUtils;
39  import org.opensubsystems.core.util.DatabaseUtils;
40  
41  /**
42   * This class collects code fragments which are reusable for managing data
43   * in HSQLDB.
44   * 
45   * @version $Id: HsqlDBDataUtils.java,v 1.4 2007/01/07 06:15:15 bastafidli Exp $
46   * @author Miro Halas
47   * @code.reviewer Miro Halas
48   * @code.reviewed 1.2 2006/02/11 00:26:07 jlegeny
49   */
50  public final class HsqlDBDataUtils
51  {
52     /**
53      * Class used to encapsulate and hold cached statements between method calls.
54      *  
55      * @version $Id: HsqlDBDataUtils.java,v 1.4 2007/01/07 06:15:15 bastafidli Exp $
56      * @author Miro Halas
57      */
58     public static class CachedInsertStatements
59     {
60        /**
61         * Are the statements generated for a domain.
62         */
63        private boolean m_bIsInDomain;
64        
65        /**
66         * Cached call statement.
67         */
68        private CallableStatement   m_call; 
69  
70        /**
71         * Cached select statement.
72         */
73        private PreparedStatement   m_select; 
74  
75        /**
76         * @param bIsInDomain - are the statements generated for a domain
77         * @param call - new call statement to cache
78         * @param select - new select statement to cache
79         */
80        public CachedInsertStatements(
81           boolean           bIsInDomain,
82           CallableStatement   call, 
83           PreparedStatement   select
84        )
85        {
86           super();
87           
88           m_bIsInDomain = bIsInDomain;
89           m_call = call;
90           m_select = select;
91        }
92  
93        /**
94         * @return CallableStatement
95         */
96        private CallableStatement   getCall()
97        {
98           return m_call;
99        }
100 
101       /**
102        * @return PreparedStatement
103        */
104       private PreparedStatement   getSelect()
105       {
106          return m_select;
107       }
108       
109       /**
110        * @return boolean
111        */
112       public boolean isInDomain()
113       {
114          return m_bIsInDomain;
115       }
116 
117    }
118    
119    /**
120     * Class used to encapsulate and hold cached statements between method calls.
121     *  
122     * @version $Id: HsqlDBDataUtils.java,v 1.4 2007/01/07 06:15:15 bastafidli Exp $
123     * @author Miro Halas
124     */
125    public static class CachedUpdateStatements
126    {
127       /**
128        * Are the statements generated for a domain.
129        */
130       private boolean m_bIsInDomain;
131       
132       /**
133        * Cached select statement.
134        */
135       private PreparedStatement   m_select; 
136    
137       /**
138        * @param bIsInDomain - are the statements generated for a domain
139        * @param select - new select statement to cache
140        */
141       public CachedUpdateStatements(
142          boolean           bIsInDomain,
143          PreparedStatement   select
144       )
145       {
146          super();
147          
148          m_bIsInDomain = bIsInDomain;
149          m_select = select;
150       }
151    
152       /**
153        * @return PreparedStatement
154        */
155       public PreparedStatement   getSelect()
156       {
157          return m_select;
158       }
159       
160       /**
161        * @return boolean
162        */
163       public boolean isInDomain()
164       {
165          return m_bIsInDomain;
166       }
167    
168    }
169 
170    // Constructors /////////////////////////////////////////////////////////////
171    
172    /** 
173     * Private constructor since this class cannot be instantiated
174     */
175    private HsqlDBDataUtils(
176    )
177    {
178       // Do nothing
179    }
180    
181    // Public methods ///////////////////////////////////////////////////////////
182 
183    /**
184     * Insert the data, fetch from the database id and generated creation and 
185     * modification timestamps for the newly created data object.
186     * 
187     * Note: Since the caller created the prepared statement, the caller is
188     * responsible for its closing.
189     *  
190     * @param dbConnection - connection to use to access the database
191     * @param insertStatement - statement used to insert the data
192     * @param bIsInDomain - are the data objects maintained in domains
193     * @param strTableName - name of the table
194     * @param data - data object to update
195     * @throws SQLException - an error has occured
196     * @throws OSSException - an error has occured
197     */
198    public static void insertAndFetchGeneratedValues(
199       Connection          dbConnection,
200       PreparedStatement   insertStatement,
201       boolean           bIsInDomain,
202       String              strTableName,
203       BasicDataObject   data
204    ) throws SQLException  ,
205             OSSException
206    {
207       CachedInsertStatements cache = null;
208       
209       try
210       {
211          cache = cacheStatementsForInsert(
212                     dbConnection, bIsInDomain, strTableName,
213                     data instanceof ModifiableDataObject);
214          insertAndFetchGeneratedValues(insertStatement, cache, data);
215       }
216       finally
217       {
218          closeStatements(cache);
219       }
220    }
221    
222    /**
223     * Insert the data, fetch from the database id and generated creation and 
224     * modification timestamps for the newly created data object.
225     * 
226     * Note: Since the caller created the prepared statement, the caller is
227     * responsible for its closing.
228     *  
229     * @param insertStatement - statement used to insert the data
230     * @param cache - cached jdbc statements
231     * @param data - data object to update
232     * @throws OSSException - an error accessing database
233     * @throws SQLException - an error while inserting data
234     */
235    public static void insertAndFetchGeneratedValues(
236       PreparedStatement        insertStatement,
237       CachedInsertStatements cache,
238       BasicDataObject        data
239    ) throws OSSException, 
240             SQLException  
241    {
242       ResultSet   rsResults = null;
243       int       iGeneratedKey = DataObject.NEW_ID;
244 
245       insertStatement.executeUpdate();
246       
247       try
248       {
249          // I prefer to do it step by step so that we don't have to keep 2 statements
250          // and result sets opened
251          try
252          {
253             rsResults = cache.getCall().executeQuery();
254             if (rsResults.next())
255             {
256                iGeneratedKey = rsResults.getInt(1);
257             }
258             else
259             {
260                throw new OSSDataCreateException(
261                             "Cannot read the generated ID from the database.");
262             }
263          }
264          finally
265          {      
266             DatabaseUtils.closeResultSet(rsResults);
267          }   
268          
269          if (iGeneratedKey != DataObject.NEW_ID)
270          {   
271             PreparedStatement   selectStatement = null;
272    
273             try
274             {
275                
276                selectStatement = cache.getSelect();
277                selectStatement.clearParameters();
278                selectStatement.setInt(1, iGeneratedKey);
279                if (cache.isInDomain())
280                {   
281                   selectStatement.setInt(2, data.getDomainId());
282                }
283                rsResults = selectStatement.executeQuery();
284                if (rsResults.next())
285                { 
286                   data.setId(iGeneratedKey);
287                   data.setCreationTimestamp(rsResults.getTimestamp(1));
288                   if (data instanceof ModifiableDataObject)
289                   {   
290                      ((ModifiableDataObject)data).setModificationTimestamp(
291                                                      rsResults.getTimestamp(2));
292                   }
293                }
294                else
295                {
296                   throw new OSSDataCreateException(
297                                "Cannot read the generated creation and modification " +
298                                "time from the database.");
299                }
300             }
301             finally
302             {      
303                DatabaseUtils.closeResultSet(rsResults);
304             }
305          }
306       }
307       catch (SQLException   eExc)
308       {
309          throw new OSSDataCreateException(
310                      "Cannot read the generated creation and modification time" +
311                      " from the database.", eExc);
312       }
313    }
314 
315    /**
316     * Cache the statements required by subsequent calls to this class.
317     * You must call closeGeneratedValuesStatements in finally
318     * to properly free resources.
319     * 
320     * @param dbConnection - connection to use to access the datavase
321     * @param bIsInDomain - are the data objects maintained in domains
322     * @param strTableName - name of the table
323     * @param bModifiable - is the data object modifiable
324     * @return CachedInsertStatements - cached jdbc statements
325     * @throws OSSException - an error accessing the database
326     */
327    public static CachedInsertStatements cacheStatementsForInsert(
328       Connection   dbConnection,
329       boolean    bIsInDomain,
330       String       strTableName,
331       boolean    bModifiable
332    ) throws OSSException
333    {
334       StringBuffer       sbQuery = new StringBuffer  ();
335       CachedInsertStatements cache;
336       
337       sbQuery.append("select CREATION_DATE");
338       if (bModifiable)
339       {   
340          sbQuery.append(", MODIFICATION_DATE");
341       }
342       sbQuery.append(" from ");
343       sbQuery.append(strTableName);
344       sbQuery.append(" where ID = ?"); 
345       if (bIsInDomain)
346       {   
347          sbQuery.append(" and DOMAIN_ID = ?");
348       }
349 
350       try
351       {
352          cache =  new CachedInsertStatements(
353                          bIsInDomain,
354                          dbConnection.prepareCall("call identity()"),
355                          dbConnection.prepareStatement(sbQuery.toString()));
356       }
357       catch (SQLException   eExc)
358       {
359          throw new OSSDatabaseAccessException(
360                "Cannot create jdbc statements to access the database.", 
361                eExc);
362       }
363          
364       return cache;
365    }
366 
367    /**
368     * Release the statements cached by cacheStatementsForXXX.
369     * 
370     * @param cache - cache to release
371     */
372    public static void closeStatements(
373       CachedInsertStatements cache
374    )
375    {
376       if (cache != null)
377       {   
378          DatabaseUtils.closeStatement(cache.getCall());
379          DatabaseUtils.closeStatement(cache.getSelect());
380       }
381    }
382 
383    /**
384     * Update the data, check for  errors and fetch from the database generated 
385     * modification timestamps for the updated data object.
386     * 
387     * Note: Since the caller created the prepared statement, the caller is
388     * responsible for its closing. 
389     * 
390     * @param strDataName - name of the data object
391     * @param dbConnection - connection to use to access the datavase
392     * @param updateStatement - statement to update data in the database
393     * @param bIsInDomain - are the data objects maintained in domains
394     * @param strTableName - name of the table
395     * @param data - data object to update
396     * @throws SQLException - an error has occured
397     * @throws OSSException - an error has occured
398     */
399    public static void updatedAndFetchGeneratedValues(
400       String                 strDataName,
401       Connection             dbConnection,
402       PreparedStatement      updateStatement,
403       boolean              bIsInDomain,
404       String                 strTableName,
405       ModifiableDataObject data
406    ) throws SQLException  ,
407             OSSException
408    {
409       CachedUpdateStatements cache = null;
410       
411       try
412       {
413          int iUpdateCount;
414          
415          iUpdateCount = updateStatement.executeUpdate();
416          if (iUpdateCount == 0)
417          {
418             DatabaseDataUtils.checkUpdateError(dbConnection, strDataName, 
419                                strTableName, data.getId(), data.getModificationTimestamp());
420          }
421          else if (iUpdateCount > 1)
422          {
423             throw new OSSInconsistentDataException(
424                          "Inconsistent database contains multiple ("
425                          + iUpdateCount + ") records with the same ID"
426                          + " and modified at the same time");
427          }
428    
429          cache = HsqlDBDataUtils.cacheStatementsForUpdate(dbConnection, bIsInDomain, 
430                                                    strTableName);
431          HsqlDBDataUtils.fetchModifiedTimestamps(cache, data);
432       }
433       finally
434       {
435          HsqlDBDataUtils.closeStatements(cache);
436       }
437    }
438 
439    /**
440     * Check errors and fetch from the database generated modification timestamps 
441     * for the updated data object.
442     * 
443     * @param cache - cached jdbc statements
444     * @param data - data object to update
445     * @throws OSSException - an error has occured
446     */
447    public static void fetchModifiedTimestamps(
448       CachedUpdateStatements cache,
449       ModifiableDataObject  data
450    ) throws OSSException
451    {
452       ResultSet           rsResults = null;
453       PreparedStatement   selectStatement = null;
454    
455       try
456       {
457          selectStatement = cache.getSelect();
458          selectStatement.setInt(1, data.getId());
459          if (cache.isInDomain())
460          {   
461             selectStatement.setInt(2, data.getDomainId());
462          }
463          rsResults = selectStatement.executeQuery();
464          if (rsResults.next())
465          { 
466             data.setModificationTimestamp(rsResults.getTimestamp(1));
467          }
468          else
469          {
470             throw new OSSDataSaveException("Cannot read the generated modification " +
471                                           "time from the database.");
472    
473          }
474       }
475       catch (SQLException   eExc)
476       {
477          throw new OSSDataSaveException(
478                "Cannot read the generated modification time" +
479                " from the database.", 
480                eExc);
481       }
482       finally
483       {      
484          DatabaseUtils.closeResultSet(rsResults);
485       }
486    }
487 
488    /**
489     * Cache the statements required by subsequent calls to this class.
490     * You must call closeGeneratedValuesStatements in finally
491     * to properly free resources.
492     * 
493     * @param dbConnection - connection to the database to use
494     * @param bIsInDomain - are the data objects maintained in domains
495     * @param strTableName - name of the table
496     * @return CachedInsertStatements
497     * @throws OSSException - an error accessing the database
498     */
499    public static CachedUpdateStatements cacheStatementsForUpdate(
500       Connection   dbConnection,
501       boolean    bIsInDomain,
502       String       strTableName
503    ) throws OSSException
504    {
505       StringBuffer       sbQuery = new StringBuffer  ();
506       CachedUpdateStatements cache;
507       
508       sbQuery.append("select MODIFICATION_DATE from ");
509       sbQuery.append(strTableName);
510       sbQuery.append(" where ID = ?"); 
511       if (bIsInDomain)
512       {   
513          sbQuery.append(" and DOMAIN_ID = ?");
514       }
515    
516       try
517       {
518          cache =  new CachedUpdateStatements(
519                          bIsInDomain,
520                          dbConnection.prepareStatement(sbQuery.toString()));
521       }
522       catch (SQLException   eExc)
523       {
524          throw new OSSDatabaseAccessException(
525                "Cannot create jdbc statements to access the database.", 
526                eExc);
527       }
528          
529       return cache;
530    }
531 
532    /**
533     * Release the statements cached by cacheStatementsForXXX.
534     * 
535     * @param cache - cached jdbc statements
536     */
537    public static void closeStatements(
538       CachedUpdateStatements cache
539    )
540    {
541       if (cache != null)
542       {   
543          DatabaseUtils.closeStatement(cache.getSelect());
544       }
545    }
546 }
547
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags