MySQLDataUtils


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