KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > opensubsystems > patterns > listdata > persist > db > PostgreSQLListDatabaseUtils


1 /*
2  * Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenSubsystems
5  *
6  * $Id: PostgreSQLListDatabaseUtils.java,v 1.15 2007/01/07 06:14:23 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.patterns.listdata.persist.db;
23
24 import java.util.List JavaDoc;
25
26 import org.opensubsystems.core.error.OSSException;
27 import org.opensubsystems.core.persist.db.DatabaseImpl;
28 import org.opensubsystems.patterns.listdata.data.ListOptions;
29
30 /**
31  * This class is used for common list retrieval and manipulation routines
32  * specific for PostgreSQL.
33  *
34  * @version $Id: PostgreSQLListDatabaseUtils.java,v 1.15 2007/01/07 06:14:23 bastafidli Exp $
35  * @author Julo Legeny
36  * @code.reviewer Miro Halas
37  * @code.reviewed 1.12 2006/03/14 23:56:56 jlegeny
38  */

39 public class PostgreSQLListDatabaseUtils extends ListDatabaseUtils
40 {
41    // Constructors /////////////////////////////////////////////////////////////
42

43    /**
44     * Constructor
45     */

46    public PostgreSQLListDatabaseUtils()
47    {
48       super();
49    }
50
51    // Public methods ///////////////////////////////////////////////////////////
52

53    /**
54     * {@inheritDoc}
55     */

56    public List JavaDoc getObjectList(
57       ListOptions options,
58       String JavaDoc query,
59       ListDatabaseFactory factory,
60       ListDatabaseSchema schema
61    ) throws OSSException
62    {
63       return super.getObjectList(options, query, factory, schema);
64             
65       // There can be used also CURSOR for retrieving particular data
66
// from the query SELECT ... LIMIT X OFFSET Y. There has to be
67
// called stored procedure that returns cursor.
68
// Example of the procedure:
69
// CREATE OR REPLACE FUNCTION list_data(text)
70
// RETURNS refcursor AS
71
// DECLARE command alias for $1;
72
// c_cur refcursor;
73
// BEGIN
74
// open c_cur for execute command;
75
// return c_cur;
76
// END;
77
// LANGUAGE 'plpgsql' VOLATILE;
78
// Parameter into the procedure will be command that has to be executed.
79

80       // But performance is almost the same for using cursors or without it.
81
// So we will not use cursors.
82
/*
83       List lstObjects = null;
84       Connection cntConnection = null;
85       ResultSet rsQueryResults = null;
86       PreparedStatement pstmQuery = null;
87       MyTimer timer = new MyTimer();
88       CallableStatement proc = null;
89       try
90       {
91          // Try to load information from the database
92          cntConnection = DatabaseConnectionFactoryImpl.getInstance().requestConnection();
93       
94          int iCount = 0;
95
96          if ((!DatabaseImpl.getInstance().hasAbsolutePositioningSupport())
97              || (DatabaseImpl.getInstance().preferCountToLast()))
98          {
99             // --------------------------------------------------------------
100             // 1. If the count() is preferred to last() to determine how
101             // many results are there available or if the last() is not supported
102             // then execute count separately
103             // --------------------------------------------------------------
104             
105             // get number of all items retrieved by count(*)
106             // timer.reset();
107             iCount = getSelectCount(cntConnection, query, schema);
108             // s_logger.info("Duration for executing count statement for page " +
109             // options.getActualPage() + " (at position " +
110             // options.getBeginPosition() + ") = " + timer.toString());
111    
112             // set option positioning, at this position we know the real number of all records
113             setOptionPositioning(iCount, options);
114             
115             // allocate object list
116             lstObjects = allocateObjectList(options, iCount);
117    
118             // default implementation doesn't do anything
119             // if there is supported LIMIT, process changes on original query and use them
120             query = preprocessSelectQuery(query, options, schema);
121
122             // declare cursor
123             //timer.reset();
124             pstmQuery = cntConnection.prepareStatement(query);
125             if (schema.isInDomain())
126             {
127                pstmQuery.setInt(1, options.getDomainId());
128             }
129             // s_logger.info("Duration for prepare statement = " + timer.toString());
130
131             // Turn transactions off.
132             cntConnection.setAutoCommit(false);
133             // Procedure call.
134             proc = cntConnection.prepareCall("{ ? = call list_users(?) }");
135             proc.registerOutParameter(1, Types.OTHER);
136             proc.setString(2, query);
137             timer.reset();
138             proc.execute();
139             rsQueryResults = (ResultSet) proc.getObject(1);
140             // s_logger.info("Duration for executing final query from procedure = " +
141             // timer.toString());
142
143             if (lstObjects != null)
144             {
145                // If query with list range support will be used then the result
146                // is the first record in the result set
147                // timer.reset();
148                if (DatabaseImpl.getInstance().hasSelectListRangeSupport())
149                {
150                   rsQueryResults.next();
151                }
152                else
153                {
154                   rsQueryResults.absolute(options.getBeginPosition());
155                }
156                // s_logger.info("Duration for absolute/first() for load data = "
157                // + timer.toString());
158             }
159             cntConnection.setAutoCommit(true);
160          }
161          else
162          {
163             // --------------------------------------------------------------
164             // 2. If the last() is preferred to count() so determine how many
165             // items are available by executing the query and checking how
166             // many items it returned
167             // --------------------------------------------------------------
168    
169             // s_logger.info("Preparing query " + query);
170             // timer.reset();
171             pstmQuery = cntConnection.prepareStatement(
172                query,
173                DatabaseImpl.getInstance().getSelectListResultSetType(),
174                DatabaseImpl.getInstance().getSelectListResultSetConcurrency()
175             );
176             if (schema.isInDomain())
177             {
178                pstmQuery.setInt(1, options.getDomainId());
179             }
180             // s_logger.info("Duration for prepare statement = " + timer.toString());
181
182             // timer.reset();
183             //rsQueryResults = pstmQuery.executeQuery();
184             // s_logger.info("Duration for executing query = " + timer.toString());
185    
186             // We should use last() to determine total number of items
187             // timer.reset();
188             iCount = getTotalRecords(rsQueryResults, options);
189             // s_logger.info("Duration for last() = " + timer.toString());
190
191             // set option positioning, at this position we know the real number of all records
192             setOptionPositioning(iCount, options);
193             
194             // allocate object list
195             lstObjects = allocateObjectList(options, iCount);
196
197             if (lstObjects != null)
198             {
199                // timer.reset();
200                rsQueryResults.absolute(options.getBeginPosition());
201                // s_logger.info("Duration for absolute() for load data = " + timer.toString());
202             }
203          }
204
205          // =====================================================================
206          
207          // At this point we have already constructed and executed query,
208          // allocated object list and set up options. Now load particular
209          // data from the result set.
210          if (lstObjects != null)
211          {
212             if (GlobalConstants.ERROR_CHECKING)
213             {
214                assert iCount > 0
215                       : "List should be null for empty result";
216             }
217             
218             int iHelpCounter = 0;
219             // Cache this so we don't have to call it always inside of loop
220             int[] columnCodes = options.getRetrieveColumnCodes();
221
222             // not to call rsQueryResults.next() for first loop
223             // timer.reset();
224             while ((iHelpCounter == 0 || rsQueryResults.next())
225                && ((iHelpCounter < options.getPageSize())
226                || (options.getPageSize() == ListOptions.PAGE_SIZE_ALL)))
227             {
228                iHelpCounter++;
229                lstObjects.add(
230                factory.load(rsQueryResults, columnCodes, 1));
231             };
232             // s_logger.info("Duration for load data = " + timer.toString());
233          }
234          proc.close();
235
236          // =====================================================================
237
238          // Call method that check if can be processed list keepeng selected and
239          // if yes, do it.
240          lstObjects = getObjectListKeepSelected (factory, cntConnection, query,
241                                                  options, lstObjects, schema);
242       }
243       catch (SQLException sqleExc)
244       {
245          throw new OSSDatabaseAccessException(
246                       "Failed to retrieve specified list of data objects" +
247                       " from the database.", sqleExc);
248       }
249       finally
250       {
251          DatabaseUtils.closeResultSetAndStatement(rsQueryResults, pstmQuery);
252          DatabaseConnectionFactoryImpl.getInstance().returnConnection(cntConnection);
253       }
254    
255       return lstObjects;
256 */

257    }
258
259    /**
260     * {@inheritDoc}
261     */

262    protected String JavaDoc preprocessSelectQuery(
263       String JavaDoc inputQuery,
264       ListOptions options,
265       ListDatabaseSchema schema
266    ) throws OSSException
267    {
268       // if there is not supported LIMIT, process changes on original query and use them
269
if (DatabaseImpl.getInstance().hasSelectListRangeSupport())
270       {
271          // For PostgreSQL there will be added 'LIMIT X OFFSET Y' clause
272
// at the end of the input query.
273
StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
274          
275          buffer.append(inputQuery);
276          buffer.append(" limit ");
277          buffer.append(options.getPageSize());
278          buffer.append(" offset ");
279          buffer.append(options.getBeginPosition() - 1);
280    
281          return buffer.toString();
282       }
283       else
284       {
285          // if there is not supported LIMIT, just call super
286
return super.preprocessSelectQuery(inputQuery, options, schema);
287       }
288    }
289 }
290
Popular Tags