Java > Open Source Codes > org > opensubsystems > core > util > DatabaseUtils


1 /*
2  * Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenSubsystems
5  *
6  * $Id: DatabaseUtils.java,v 1.23 2007/01/07 06:14:01 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.util;
23
24 import java.sql.Connection ;
25 import java.sql.PreparedStatement ;
26 import java.sql.ResultSet ;
27 import java.sql.SQLException ;
28 import java.sql.Statement ;
29 import java.util.ArrayList ;
30 import java.util.Collection ;
31 import java.util.HashMap ;
32 import java.util.HashSet ;
33 import java.util.Iterator ;
34 import java.util.List ;
35 import java.util.Map ;
36 import java.util.Set ;
37 import java.util.logging.Level ;
38 import java.util.logging.Logger ;
39
40 import org.opensubsystems.core.data.DataObject;
41 import org.opensubsystems.core.error.OSSDatabaseAccessException;
42 import org.opensubsystems.core.error.OSSException;
43 import org.opensubsystems.core.persist.db.DatabaseConnectionFactoryImpl;
44 import org.opensubsystems.core.persist.db.DatabaseFactory;
45 import org.opensubsystems.core.persist.db.DatabaseImpl;
46 import org.opensubsystems.core.persist.db.DatabaseTransactionFactoryImpl;
47
48 /**
49  * Set of common utility methods related to database access.
50  *
51  * @version $Id: DatabaseUtils.java,v 1.23 2007/01/07 06:14:01 bastafidli Exp $
52  * @author Miro Halas
53  * @code.reviewer Miro Halas
54  * @code.reviewed 1.21 2006/01/17 22:45:35 jlegeny
55  */
56 public final class DatabaseUtils
57 {
58    // Cached values ////////////////////////////////////////////////////////////
59
60    /**
61     * Logger for this class
62     */
63    private static Logger s_logger = Log.getInstance(DatabaseUtils.class);
64    
65    /**
66     * Hashed tables-columns existance information for dependency checking.
67     */
68    protected static Map s_mpDependencyCache = new HashMap ();
69
70    // Constructors /////////////////////////////////////////////////////////////
71
72    /**
73     * Private constructor since this class cannot be instantiated
74     */
75    private DatabaseUtils(
76    )
77    {
78       // Do nothing
79 }
80    
81    // Public methods ///////////////////////////////////////////////////////////
82
83    /**
84     * Execute given statement and close the statement after it is done.
85     *
86     * @param jdbcstatement - statement to execute
87     * @return int - number of affected rows
88     * @throws SQLException - only if the update fails, not in any other case
89     */
90    public static int executeUpdateAndClose(
91       PreparedStatement jdbcstatement
92    ) throws SQLException
93    {
94       int iUpdateCount = 0;
95       
96       try
97       {
98          iUpdateCount = jdbcstatement.executeUpdate();
99       }
100       finally
101       {
102          closeStatement(jdbcstatement);
103       }
104       
105       return iUpdateCount;
106    }
107
108    /**
109     * Gracefully close result set so that no error is generated.
110     * This method NEVER throws any exception therefore it is safe to call it
111     * in finally before returning connection.
112     *
113     * @param results - result set to close, if null it is ignored
114     */
115    public static void closeResultSet(
116       ResultSet results
117    )
118    {
119       try
120       {
121          if (results != null)
122          {
123             results.close();
124          }
125       }
126       catch (SQLException sqleExc)
127       {
128          // Ignore this
129 s_logger.log(Level.WARNING,
130                       "Failed to close the database result set",
131                       sqleExc);
132       }
133    }
134
135    /**
136     * Gracefully close result set and statement so that no error is generated.
137     * This method NEVER throws any exception therefore it is safe to call it
138     * in finally before returning connection.
139     *
140     * @param results - result set to close, if null it is ignored
141     * @param jdbcstatement - jdbc statement to close, if null it is ignored
142     */
143    public static void closeResultSetAndStatement(
144       ResultSet results,
145       Statement jdbcstatement
146    )
147    {
148       try
149       {
150          closeResultSet(results);
151       }
152       finally
153       {
154          closeStatement(jdbcstatement);
155       }
156    }
157
158    /**
159     * Gracefully close statement so that no error is generated.
160     * This method NEVER throws any exception therefore it is safe to call it
161     * in finally before returning connection.
162     *
163     * @param jdbcstatement - jdbc statement to close, if null it is ignored
164     */
165    public static void closeStatement(
166       Statement jdbcstatement
167    )
168    {
169       if (jdbcstatement != null)
170       {
171          try
172          {
173             jdbcstatement.close();
174          }
175          catch (SQLException sqleExc)
176          {
177             s_logger.log(Level.WARNING,
178                          "Failed to close the database statement",
179                          sqleExc);
180          }
181       }
182    }
183    
184    /**
185     * Execute statement and load at most one data object from the result set and
186     * if the result set contains more than one item announce error.
187     *
188     * Note: Since the caller constructed the prepared statement, it is responsible
189     * for closing it.
190     *
191     * @param factory - factory used to load data
192     * @param pstmQuery - query to execute
193     * @param strErrorMessage - error message to announce if there is more than
194     * one item
195     * @return DataObject - loaded data object or null if the result set was empty
196     * @throws SQLException - an error has occured
197     * @throws OSSException - an error has occured
198     */
199    public static DataObject loadAtMostOneData(
200       DatabaseFactory factory,
201       PreparedStatement pstmQuery,
202       String strErrorMessage
203    ) throws SQLException ,
204             OSSException
205    {
206       DataObject data = null;
207       ResultSet rsQueryResults = null;
208       
209       try
210       {
211          rsQueryResults = pstmQuery.executeQuery();
212          while (rsQueryResults.next())
213          {
214             if (GlobalConstants.ERROR_CHECKING)
215             {
216                assert data == null : strErrorMessage;
217             }
218    
219             data = factory.load(rsQueryResults, 1);
220          }
221       }
222       finally
223       {
224          closeResultSet(rsQueryResults);
225       }
226       
227       return data;
228    }
229    
230    /**
231     * Execute statement and load at most one data object from the result set and
232     * if the result set contains more than one item announce error.
233     *
234     * Note: Since the caller constructed the prepared statement, it is responsible
235     * for closing it.
236     *
237     * @param pstmQuery - query to execute
238     * @param iDefault - default value to return if the result set doesn't contain
239     * any value
240     * @param strErrorMessage - error message to announce if there is more than
241     * one item
242     * @return int - loaded int or null if the result set was empty
243     * @throws SQLException - an error has occured
244     * @throws OSSException - an error has occured
245     */
246    public static int loadAtMostOneInt(
247       PreparedStatement pstmQuery,
248       int iDefault,
249       String strErrorMessage
250    ) throws SQLException ,
251             OSSException
252    {
253       int iData = iDefault;
254       ResultSet rsQueryResults = null;
255       
256       try
257       {
258          rsQueryResults = pstmQuery.executeQuery();
259          if (rsQueryResults.next())
260          {
261             iData = rsQueryResults.getInt(1);
262             if (GlobalConstants.ERROR_CHECKING)
263             {
264                assert (!rsQueryResults.next()) : strErrorMessage;
265             }
266          }
267       }
268       finally
269       {
270          closeResultSet(rsQueryResults);
271       }
272       
273       return iData;
274    }
275
276    /**
277     * Execute statement and load at most one data object from the result set and
278     * if the result set contains more than one item announce error.
279     *
280     * Note: Since the caller constructed the prepared statement, it is responsible
281     * for closing it.
282     *
283     * @param pstmQuery - query to execute
284     * @param strDefault - default value to return if the result set doesn't contain
285     * any value
286     * @param strErrorMessage - error message to announce if there is more than
287     * one item
288     * @return String - loaded string or null if the result set was empty
289     * @throws SQLException - an error has occured
290     * @throws OSSException - an error has occured
291     */
292    public static String loadAtMostOneString(
293       PreparedStatement pstmQuery,
294       String strDefault,
295       String strErrorMessage
296    ) throws SQLException ,
297             OSSException
298    {
299       String strData = strDefault;
300       ResultSet rsQueryResults = null;
301       
302       try
303       {
304          rsQueryResults = pstmQuery.executeQuery();
305          if (rsQueryResults.next())
306          {
307             strData = rsQueryResults.getString(1);
308             if (GlobalConstants.ERROR_CHECKING)
309             {
310                assert (!rsQueryResults.next()) : strErrorMessage;
311             }
312          }
313       }
314       finally
315       {
316          closeResultSet(rsQueryResults);
317       }
318       
319       return strData;
320    }
321
322    /**
323     * Execute statement and load multiple data objects from the result set. If
324     * there are no items null will be returned.
325     *
326     * Note: Since the caller constructed the prepared statement, it is responsible
327     * for closing it.
328     *
329     * @param factory - factory used to load data
330     * @param pstmQuery - query to execute
331     * @return List - list of loaded data objects or null if there were no results
332     * @throws SQLException - an error has occured
333     * @throws OSSException - an error has occured
334     */
335    public static List loadMultipleData(
336       DatabaseFactory factory,
337       PreparedStatement pstmQuery
338    ) throws SQLException ,
339             OSSException
340    {
341       ResultSet rsQueryResults = null;
342       List lstData = null;
343       
344       try
345       {
346          int iCount;
347          
348          rsQueryResults = pstmQuery.executeQuery();
349          iCount = estimateResultSetSize(rsQueryResults);
350          if (iCount != 0)
351          {
352             if (iCount > 0)
353             {
354                // Use ArrayList since it is fast, lightweight and we know the count
355 lstData = new ArrayList (iCount);
356                // We have to call next here because we have to call next below
357 // so that we know if to allocate data
358 rsQueryResults.next();
359             }
360             else
361             {
362                // If there is not absolute positioning supported we don't know
363 // at this moment if there are some data in the resultset. If
364 // there are not, don't create array list 'lstData'.
365 // We cannot user first() and beforeFirst() since we don know
366 // id absolute positioning is supported so lets
367 // call next and add call to next above and convert the loop
368 // below to do while
369 if (rsQueryResults.next())
370                {
371                   // Here we do not know the count so we do not know how many
372 // items to load, for now use ArrayList even though it may be
373 // slow if it has to expand a lot
374 lstData = new ArrayList ();
375                }
376             }
377             if (lstData != null)
378             {
379                do
380                {
381                   lstData.add(factory.load(rsQueryResults, 1));
382                }
383                while (rsQueryResults.next());
384             }
385          }
386       }
387       finally
388       {
389          closeResultSet(rsQueryResults);
390       }
391        
392       return lstData;
393    }
394    
395    /**
396     * Execute statement and load multiple data objects from the result set. If
397     * there are no items null will be returned.
398     *
399     * Note: Since the caller constructed the prepared statement, it is responsible
400     * for closing it.
401     *
402     * @param factory - factory used to load data
403     * @param pstmQuery - query to execute
404     * @param bLoadSpecific - flag signaling if there will be loaded specific data
405     * false ... all columns will be loaded from resultset
406     * and set up whole object to the HashSet
407     * true ... 1st column will be loaded from resultset
408     * and set up only particular data to the HashSet
409     * @return Set - set of loaded data objects or null if there were no results
410     * @throws SQLException - an error has occured
411     * @throws OSSException - an error has occured
412     */
413    public static Set loadMultipleDataToSet(
414       DatabaseFactory factory,
415       PreparedStatement pstmQuery,
416       boolean bLoadSpecific
417    ) throws SQLException ,
418             OSSException
419    {
420       ResultSet rsQueryResults = null;
421       Set returnSet = null;
422       
423       try
424       {
425          int iCount;
426          
427          rsQueryResults = pstmQuery.executeQuery();
428          iCount = estimateResultSetSize(rsQueryResults);
429          if (iCount != 0)
430          {
431             if (iCount > 0)
432             {
433                // Use HashSet since it is fast, lightweight and we know the count
434 returnSet = new HashSet (iCount);
435                // We have to call next here because we have to call next below
436 // so that we know if to allocate data
437 rsQueryResults.next();
438             }
439             else
440             {
441                // If there is not absolute positioning supported we don't know
442 // at this moment if there are some data in the resultset. If
443 // there are not, don't create array list 'lstData'.
444 // We cannot user first() and beforeFirst() since we don know
445 // id absolute positioning is supported so lets
446 // call next and add call to next above and convert the loop
447 // below to do while
448 if (rsQueryResults.next())
449                {
450                   // Here we do not know the count so we do not know how many
451 // items to load, for now use HashSet even though it may be
452 // slow if it has to expand a lot
453 returnSet = new HashSet ();
454                }
455             }
456             if (returnSet != null)
457             {
458                do
459                {
460                   if (!bLoadSpecific)
461                   {
462                      // load all columns from resultset and set up whole object
463 // to the HashSet
464 returnSet.add(factory.load(rsQueryResults, 1));
465                   }
466                   else
467                   {
468                      // load just one specific column from resultset
469 returnSet.add(rsQueryResults.getString(1));
470                   }
471                }
472                while (rsQueryResults.next());
473             }
474          }
475       }
476       finally
477       {
478          closeResultSet(rsQueryResults);
479       }
480        
481       return returnSet;
482    }
483
484    /**
485     * Execute statement and load multiple strings from the result set. If there
486     * are no results null will be returned.
487     *
488     * Note: Since the caller constructed the prepared statement, it is responsible
489     * for closing it.
490     *
491     * @param pstmQuery - query to execute
492     * @return List - list of loaded strings or null if there were no results
493     * @throws SQLException - an error has occured
494     * @throws OSSException - an error has occured
495     */
496    public static List loadMultipleStrings(
497       PreparedStatement pstmQuery
498    ) throws SQLException ,
499             OSSException
500    {
501       ResultSet rsQueryResults = null;
502       List lstData = null;
503       
504       try
505       {
506          int iCount;
507          
508          rsQueryResults = pstmQuery.executeQuery();
509          iCount = estimateResultSetSize(rsQueryResults);
510          if (iCount != 0)
511          {
512             if (iCount > 0)
513             {
514                // Use ArrayList since it is fast, lightweight and we know the count
515 lstData = new ArrayList (iCount);
516                // We have to call next here because we have to call next below
517 // so that we know if to allocate data
518 rsQueryResults.next();
519             }
520             else
521             {
522                // If there is not absolute positioning supported we don't know
523 // at this moment if there are some data in the resultset. If
524 // there are not, don't create array list 'lstData'.
525 // We cannot user first() and beforeFirst() since we don know
526 // id absolute positioning is supported so lets
527 // call next and add call to next above and convert the loop
528 // below to do while
529 if (rsQueryResults.next())
530                {
531                   // Here we do not know the count so we do not know how many
532 // items to load, for now use ArrayList even though it may be
533 // slow if it has to expand a lot
534 lstData = new ArrayList ();
535                }
536             }
537             if (lstData != null)
538             {
539                do
540                {
541                   lstData.add(rsQueryResults.getString(1));
542                }
543                while (rsQueryResults.next());
544             }
545          }
546       }
547       finally
548       {
549          closeResultSet(rsQueryResults);
550       }
551        
552       return lstData;
553    }
554    
555    /**
556     * Execute statement and load multiple int's from the result set. If there
557     * are no results null will be returned.
558     *
559     * Note: Since the caller constructed the prepared statement, it is responsible
560     * for closing it.
561     *
562     * @param pstmQuery - query to execute
563     * @return int[] - list of loaded int's or null if there were no results
564     * @throws SQLException - an error has occured
565     * @throws OSSException - an error has occured
566     */
567    public static int[] loadMultipleIntsAsArray(
568       PreparedStatement pstmQuery
569    ) throws SQLException ,
570             OSSException
571    {
572       ResultSet rsQueryResults = null;
573       int[] arrData = null;
574       
575       try
576       {
577          int iCount;
578          
579          rsQueryResults = pstmQuery.executeQuery();
580          iCount = estimateResultSetSize(rsQueryResults);
581          if (iCount != 0)
582          {
583             int iActualCount = 0;
584             if (iCount > 0)
585             {
586                // Use ArrayList since it is fast, lightweight and we know the count
587 arrData = new int[iCount];
588                while (rsQueryResults.next())
589                {
590                   arrData[iActualCount++] = rsQueryResults.getInt(1);
591                }
592             }
593             else
594             {
595                // Here we do not know the count so we do not know how many items
596 // to load, for now use ArrayList even though it may be slow
597 // if it has to expand a lot
598 if (rsQueryResults.next())
599                {
600                   List lstData = new ArrayList ();
601                   do
602                   {
603                      lstData.add(new Integer (rsQueryResults.getInt(1)));
604                   }
605                   while (rsQueryResults.next());
606                   arrData = new int[lstData.size()];
607                   for (Iterator items = lstData.iterator(); items.hasNext();)
608                   {
609                      arrData[iActualCount++] = ((Integer )items.next()).intValue();
610                   }
611                }
612             }
613          }
614       }
615       finally
616       {
617          closeResultSet(rsQueryResults);
618       }
619        
620       return arrData;
621    }
622
623    /**
624     * Execute statement and load multiple TwoIntStruct objects from the result set.
625     * If there are no results null will be returned.
626     *
627     * Note: Since the caller constructed the prepared statement, it is responsible
628     * for closing it.
629     *
630     * @param pstmQuery - query to execute
631     * @return TwoIntStruct[] - list of loaded int's or null if there were no results
632     * @throws SQLException - an error has occured
633     * @throws OSSException - an error has occured
634     */
635    public static TwoIntStruct[] loadMultipleTwoIntStruct(
636       PreparedStatement pstmQuery
637    ) throws SQLException ,
638             OSSException
639    {
640       ResultSet rsQueryResults = null;
641       TwoIntStruct[] arrData = null;
642       
643       try
644       {
645          int iCount;
646          
647          rsQueryResults = pstmQuery.executeQuery();
648          iCount = estimateResultSetSize(rsQueryResults);
649          if (iCount != 0)
650          {
651             int iActualCount = 0;
652             int iItem1;
653             int iItem2;
654             if (iCount > 0)
655             {
656                arrData = new TwoIntStruct[iCount];
657                while (rsQueryResults.next())
658                {
659                   iItem1 = rsQueryResults.getInt(1);
660                   iItem2 = rsQueryResults.getInt(2);
661    
662                   arrData[iActualCount++] = new TwoIntStruct(iItem1, iItem2);
663                }
664             }
665             else
666             {
667                // Here we do not know the count so we do not know how many items
668 // to load, for now use ArrayList even though it may be slow
669 // if it has to expand a lot
670 if (rsQueryResults.next())
671                {
672                   List lstData = new ArrayList ();
673                   do
674                   {
675                      lstData.add(new TwoIntStruct(rsQueryResults.getInt(1),
676                                                   rsQueryResults.getInt(2)));
677                   }
678                   while (rsQueryResults.next());
679                   arrData = new TwoIntStruct[lstData.size()];
680                   for (Iterator items = lstData.iterator(); items.hasNext();)
681                   {
682                      arrData[iActualCount++] = (TwoIntStruct)items.next();
683                   }
684                }
685             }
686          }
687       }
688       finally
689       {
690          closeResultSet(rsQueryResults);
691       }
692        
693       return arrData;
694    }
695
696    /**
697     * This method is for relation checking and for hashing table-column existance
698     * for optimalization. Basically what it can do for you is if you pass it a
699     * set of tables and column names which can refer to your table it will check
700     * if the specified id (your id) is in any of those tables (and therefore if
701     * there is anyone refering to you). If some of the tables doesn't exist, this
702     * method will remember it and it won't check the table anymore.
703     *
704     * This can be used to break dependency on database level when you can configure
705     * in configuration file tables/columns which refer to some object and then
706     * the object can load this information from configuration file and check
707     * if there are any data refering to it. If those tables do not exist (e.g.
708     * because components containing those tables are not deployed than this method
709     * will correctly handle it)
710     *
711     * @param arTableColumn - array of {table name, column name} arrays to check
712     * relation to
713     * @param iId - id of the object to check relations
714     * @return boolean - true if relations exist
715     * @throws OSSException - error during check
716     */
717    public static boolean hasRelations(
718       String [][] arTableColumn,
719       int iId
720    ) throws OSSException
721    {
722       boolean bReturn = false;
723       Connection cntConnection = null;
724       PreparedStatement pstmQuery = null;
725       ResultSet rsResult = null;
726       StringBuffer sbQuery = null;
727       boolean bQueryInitialized = false;
728       boolean bExist;
729       
730       if ((arTableColumn != null) && (arTableColumn.length > 0))
731       {
732          try
733          {
734             // Request autocommit true since we are just reading data from the database
735 cntConnection = DatabaseConnectionFactoryImpl.getInstance().requestConnection(true);
736    
737             for (int iCount = 0; iCount < arTableColumn.length; iCount++)
738             {
739                // TODO: Performace: Optimize this with StringBuffers and preparedStatement
740 if (s_mpDependencyCache.get(arTableColumn[iCount][0]
741                                    + "|" + arTableColumn[iCount][1]) == null)
742                {
743                   try
744                   {
745                      pstmQuery = cntConnection.prepareStatement(
746                         "select " +
747                         arTableColumn[iCount][1] +
748                         " from " +
749                         arTableColumn[iCount][0]);
750                      rsResult = pstmQuery.executeQuery();
751                      
752                      s_mpDependencyCache.put(
753                            arTableColumn[iCount][0] + "|" + arTableColumn[iCount][1],
754                            "exist");
755                      bExist = true;
756                      
757                   }
758                   catch (SQLException sqleExc)
759                   {
760                      s_mpDependencyCache.put(
761                            arTableColumn[iCount][0] + "|" + arTableColumn[iCount][1],
762                            "not in DB");
763                      bExist = false;
764                   }
765                   finally
766                   {
767                      DatabaseUtils.closeResultSetAndStatement(rsResult, pstmQuery);
768                   }
769                }
770                else
771                {
772                   bExist = s_mpDependencyCache.get(
773                         arTableColumn[iCount][0] + "|" + arTableColumn[iCount][1]
774                      ).equals("exist");
775                }
776                
777                if (bExist)
778                {
779                   if (bQueryInitialized)
780                   {
781                      sbQuery.append(" union ");
782                   }
783                   else
784                   {
785                      sbQuery = new StringBuffer ();
786                   }
787                   
788                   sbQuery.append("select ");
789                   sbQuery.append(arTableColumn[iCount][1]);
790                   sbQuery.append(" from ");
791                   sbQuery.append(arTableColumn[iCount][0]);
792                   sbQuery.append(" where ");
793                   sbQuery.append(arTableColumn[iCount][1]);
794                   sbQuery.append(" = ");
795                   sbQuery.append(iId);
796
797                   bQueryInitialized = true;
798                }
799             }
800             
801             if (sbQuery != null && sbQuery.length() > 0)
802             {
803                try
804                {
805                   pstmQuery = cntConnection.prepareStatement(sbQuery.toString());
806                   rsResult = pstmQuery.executeQuery();
807                   
808                   if (rsResult.next())
809                   {
810                      bReturn = true;
811                   }
812                }
813                catch (SQLException sqleExc)
814                {
815                   throw new OSSDatabaseAccessException(
816                         "Cannot get connection to the database.",
817                         sqleExc);
818                }
819                finally
820                {
821                   DatabaseUtils.closeResultSetAndStatement(rsResult, pstmQuery);
822                }
823             }
824          }
825          finally
826          {
827             DatabaseConnectionFactoryImpl.getInstance().returnConnection(cntConnection);
828          }
829       }
830       
831       return bReturn;
832    }
833
834    /**
835     * Rollback transaction and ingore any exception if it occurs.
836     * The rollback will be performed only if this is a single operation which
837     * is not managed as part of multiconnection transaction.
838     *
839     * @param cntConnection - connection to rollback if necessary
840     */
841    public static void rollbackAndIgnoreException(
842       Connection cntConnection
843    )
844    {
845       try
846       {
847          // At this point we don't know if this is just a single operation
848 // and we need to commit or if it is a part of bigger transaction
849 // and the commit is not desired until all operations proceed.
850 // Therefore let the DatabaseTransactionFactory resolve it
851 DatabaseTransactionFactoryImpl.getInstance().rollbackTransaction(cntConnection);
852       }
853       catch (SQLException sqleExc)
854       {
855          // Ignore this
856 s_logger.log(Level.WARNING,
857                       "Failed to rollback current transaction",
858                       sqleExc);
859       }
860       catch (OSSException osseExc)
861       {
862          // Ignore this
863 s_logger.log(Level.WARNING,
864                       "Failed to rollback current transaction",
865                       osseExc);
866       }
867    }
868    
869    /**
870     * Merge new columns to the list of existing columns safely so if they already
871     * exist in the original list, they won't be added.
872     *
873     * @param arrOriginalColumns - list of original columns
874     * @param arrExtraColumns - new columns to add to the list
875     * @return int[] - if the list already contained the columns, it will return
876     * the same list otherwise it will return new list
877     * with extra columns
878     */
879    public static int[] mergeColumnsSafely(
880       int[] arrOriginalColumns,
881       int[] arrExtraColumns
882    )
883    {
884       int[] arrReturn = arrOriginalColumns;
885       List lstAddedColumns = new ArrayList ();
886       int iIndex;
887       int iIndex1;
888       Integer iColumn;
889
890       boolean foundFlag = false;
891       // Try to find if the columns already exist in the list
892 for (iIndex = 0; iIndex < arrExtraColumns.length; iIndex++)
893       {
894          foundFlag = false;
895          for (iIndex1 = 0; iIndex1 < arrOriginalColumns.length; iIndex1++)
896          {
897             if (arrExtraColumns[iIndex] == arrOriginalColumns[iIndex1])
898             {
899                // Column is already there
900 foundFlag = true;
901                break;
902             }
903          }
904          if (!foundFlag)
905          {
906             // new column value not found
907 lstAddedColumns.add(new Integer (arrExtraColumns[iIndex]));
908          }
909       }
910
911       if (lstAddedColumns.size() > 0)
912       {
913          // There are not some columns yet, copy the original elements
914 arrReturn = new int[arrOriginalColumns.length + lstAddedColumns.size()];
915          System.arraycopy(arrOriginalColumns, 0, arrReturn, 0, arrOriginalColumns.length);
916
917          // Add new columns to the original
918 for (iIndex = 0; iIndex < lstAddedColumns.size(); iIndex++)
919          {
920             iColumn = (Integer ) lstAddedColumns.get(iIndex);
921             arrReturn[arrOriginalColumns.length + iIndex] = iColumn.intValue();
922          }
923       }
924
925       return arrReturn;
926    }
927
928 // TODO: For Miro: Remove this after all code is ported and nothing is using it?
929 // /**
930 // * method to create where part of SQL clause for 3 Strings
931 // *
932 // * @param buffer - String buffer for append where clause
933 // * @param str1 - String 1 (Join wheer part)
934 // * @param str2 - String 2 (List where part)
935 // * @param str3 - String 3 (Filter where part)
936 // * @param str4 - String 4 (Security where part)
937 // */
938 // public static void getWhereClause(
939 // StringBuffer buffer,
940 // String str1,
941 // String str2,
942 // String str3,
943 // String str4
944 // )
945 // {
946 // if (str1.length() > 0)
947 // {
948 // buffer.append(" where ");
949 // buffer.append(str1);
950 // if (str2.length() > 0)
951 // {
952 // buffer.append(" and ");
953 // buffer.append(str2);
954 // if (str3.length() > 0)
955 // {
956 // buffer.append(" and ");
957 // buffer.append(str3);
958 // if (str4.length() > 0)
959 // {
960 // buffer.append(" and ");
961 // buffer.append(str4);
962 // }
963 // }
964 // else
965 // {
966 // if (str4.length() > 0)
967 // {
968 // buffer.append(" and ");
969 // buffer.append(str4);
970 // }
971 // }
972 // }
973 // else
974 // {
975 // if (str3.length() > 0)
976 // {
977 // buffer.append(" and ");
978 // buffer.append(str3);
979 // if (str4.length() > 0)
980 // {
981 // buffer.append(" and ");
982 // buffer.append(str4);
983 // }
984 // }
985 // else
986 // {
987 // if (str4.length() > 0)
988 // {
989 // buffer.append(" and ");
990 // buffer.append(str4);
991 // }
992 // }
993 // }
994 // }
995 // else
996 // {
997 // if (str2.length() > 0)
998 // {
999 // buffer.append(" where ");
1000// buffer.append(str2);
1001// if (str3.length() > 0)
1002// {
1003// buffer.append(" and ");
1004// buffer.append(str3);
1005// if (str4.length() > 0)
1006// {
1007// buffer.append(" and ");
1008// buffer.append(str4);
1009// }
1010// }
1011// else
1012// {
1013// if (str4.length() > 0)
1014// {
1015// buffer.append(" and ");
1016// buffer.append(str4);
1017// if (str4.length() > 0)
1018// {
1019// buffer.append(" and ");
1020// buffer.append(str4);
1021// }
1022// }
1023// else
1024// {
1025// if (str4.length() > 0)
1026// {
1027// buffer.append(" and ");
1028// buffer.append(str4);
1029// }
1030// }
1031// }
1032// }
1033// else
1034// {
1035// if (str3.length() > 0)
1036// {
1037// buffer.append(" where ");
1038// buffer.append(str3);
1039// if (str4.length() > 0)
1040// {
1041// buffer.append(" and ");
1042// buffer.append(str4);
1043// }
1044// }
1045// else
1046// {
1047// if (str4.length() > 0)
1048// {
1049// buffer.append(" where ");
1050// buffer.append(str4);
1051// }
1052// }
1053// }
1054// }
1055// }
1056//
1057
1058   /**
1059    * Because there is limitation for sql statement length (in sap db is default
1060    * 64 kb) and in() expression can contain lot of members, this function will
1061    * construct list of strings with maximum number of allowed members
1062    * (representing ids) each. Strings do not contain ().
1063    *
1064    * @param idList - collection with ids (as Objects)
1065    * @param bQuote - flag signaling if there will be used quotes when
1066    * string items will be served
1067    * - true = there will be used quotes; false = otherwise
1068    * @return List - list of strings with safe length
1069    */
1070   public static List getInListWithSafeLength(
1071      Collection idList,
1072      boolean bQuote
1073   )
1074   {
1075      List lstRetList = new ArrayList (idList.size() / DatabaseImpl.MAX_SAFE_LENGTH + 1);
1076      int count = 0;
1077      Iterator inputIterator = idList.iterator();
1078      String currData = null;
1079      StringBuffer dataString = new StringBuffer ();
1080      
1081      while (inputIterator.hasNext())
1082      {
1083         // Get next data
1084 currData = ((Object )inputIterator.next()).toString();
1085         if (count == 0)
1086         {
1087            // Clear buffer
1088 dataString.delete(0, dataString.length());
1089         }
1090         else
1091         {
1092            // Add comma before next item
1093 dataString.append(",");
1094         }
1095         
1096         if (bQuote)
1097         {
1098            // Append left quote if it is allowed
1099 dataString.append("'");
1100         }
1101
1102         // Append item to buffer
1103 dataString.append(currData);
1104
1105         if (bQuote)
1106         {
1107            // Append right quote if it is allowed
1108 dataString.append("'");
1109         }
1110
1111         count++;
1112         
1113         if (count == DatabaseImpl.MAX_SAFE_LENGTH)
1114         {
1115            // Put buffer with 1000 items to output list
1116 lstRetList.add(dataString.toString());
1117            // Reset count
1118 count = 0;
1119         }
1120      }
1121      if (count > 0)
1122      {
1123         // Add rest (if any) to output list
1124 lstRetList.add(dataString.toString());
1125      }
1126      
1127      return lstRetList;
1128   }
1129   
1130   // Helper methods ///////////////////////////////////////////////////////////
1131
1132   /**
1133    * Estimate the size of the result set so that data structures can be
1134    * allocated efficiently.
1135    *
1136    * @param rsQueryResults - resultset of size of which to estimate
1137    * @return int - if the size can be estimated it returns the size, otherwise
1138    * it will return -1
1139    * @throws SQLException - an error has occured
1140    * @throws OSSException - an error has occured
1141    */
1142   protected static int estimateResultSetSize(
1143      ResultSet rsQueryResults
1144   ) throws SQLException ,
1145            OSSException
1146   {
1147      // Initialize to negative so that we can recognize when we actually
1148 // know the count and when we do not know it
1149 int iCount = -1;
1150      
1151      // If the database supports absolute positioning and it is efficient
1152 // (so that by general we can't use it to determine size of results)
1153 // instead of executing the query with count() we can determine the count
1154 // to better allocate memory
1155 if ((DatabaseImpl.getInstance().hasAbsolutePositioningSupport())
1156         && (!DatabaseImpl.getInstance().preferCountToLast()))
1157      {
1158         // It seems like absolute positioning is efficient so use it to
1159 // determine since of result set so it can allocate optimal amount
1160 // of memory
1161 if (rsQueryResults.last())
1162         {
1163            // The last row number will tell us the row count (since it is 1 based)
1164 iCount = rsQueryResults.getRow();
1165            rsQueryResults.beforeFirst();
1166         }
1167         else
1168         {
1169            // It is empty
1170 iCount = 0;
1171         }
1172      }
1173      
1174      return iCount;
1175   }
1176}
1177

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags