MySQLListDatabaseUtils


1   /*
2    * Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3    * 
4    * Project: OpenSubsystems
5    * 
6    * $Id: MySQLListDatabaseUtils.java,v 1.28 2007/02/01 07:23:41 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.sql.Connection  ;
25  import java.sql.ResultSet  ;
26  import java.sql.SQLException  ;
27  import java.sql.Statement  ;
28  import java.util.List  ;
29  import java.util.logging.Logger  ;
30  
31  import org.opensubsystems.core.error.OSSDatabaseAccessException;
32  import org.opensubsystems.core.error.OSSException;
33  import org.opensubsystems.core.persist.db.DatabaseConnectionFactoryImpl;
34  import org.opensubsystems.core.persist.db.DatabaseImpl;
35  import org.opensubsystems.core.util.CallContext;
36  import org.opensubsystems.core.util.DatabaseUtils;
37  import org.opensubsystems.core.util.GlobalConstants;
38  import org.opensubsystems.core.util.Log;
39  import org.opensubsystems.core.util.StringUtils;
40  import org.opensubsystems.patterns.listdata.data.DataCondition;
41  import org.opensubsystems.patterns.listdata.data.ListOptions;
42  import org.opensubsystems.patterns.listdata.data.SimpleRule;
43  
44  /**
45   * This class is used for common list retrieval and manipulation routines 
46   * specific for MySQL. 
47   * 
48   * @version $Id: MySQLListDatabaseUtils.java,v 1.28 2007/02/01 07:23:41 bastafidli Exp $
49   * @author Julo Legeny
50   * @code.reviewer Miro Halas
51   * @code.reviewed 1.22 2006/03/14 23:56:56 jlegeny
52   */
53  public class MySQLListDatabaseUtils extends ListDatabaseUtils
54  {
55     // Cached values ////////////////////////////////////////////////////////////
56  
57     /**
58      * Logger to use for this class
59      */
60     private static Logger   s_logger = Log.getInstance(MySQLListDatabaseUtils.class);
61  
62     // Constructors /////////////////////////////////////////////////////////////
63  
64     /**
65      * Constructor
66      */
67     public MySQLListDatabaseUtils()
68     {
69        super();
70     }
71  
72     // Public methods ///////////////////////////////////////////////////////////
73  
74     /**
75      * {@inheritDoc}
76      */
77     public String   getWhereClause(
78        String               strTableName,
79        int[]              arrIds,
80        SimpleRule         securityFilter,
81        ListDatabaseSchema schema
82     ) throws OSSException
83     {
84        // We need to redefine this method because if there are used access rights
85        // the query would contain subselect for this and MySQL doesn't support 
86        // subselect. We will construct extra buffer for particular subselect and 
87        // process it here and resuls (ID values) will be added instead of the 
88        // subselect query.
89        
90        Connection   cntConnection  = null;
91        ResultSet    rsQueryResults = null;
92        Statement    stmQuery       = null;
93  
94        int iIndex = 0;
95  
96        if (GlobalConstants.ERROR_CHECKING)
97        {
98           assert securityFilter != null 
99                  : "If I don't have rights for action, this shouldn't be called"; 
100       }
101       
102       StringBuffer   buffer = new StringBuffer  ();
103       StringBuffer   bufferInnerQuery = new StringBuffer  ();
104       
105       buffer.append(" where ");
106       
107       if (schema.isInDomain())
108       {
109          // We must use the table name since the calling function may do join
110          // as well
111          buffer.append(strTableName); 
112          buffer.append(".DOMAIN_ID = ? "); 
113       }
114       
115       if ((arrIds != null) && (arrIds.length > 0))
116       {
117          // We must use the table name since the calling function may do join
118          // as well
119          if (schema.isInDomain())
120          {
121             buffer.append(" and ");
122          }
123          buffer.append(strTableName); 
124          buffer.append(".ID in (");
125          buffer.append(StringUtils.parseIntArrayToString(arrIds, ","));
126          buffer.append(")");
127       }
128 
129       // All below is totally security related and only tells us what data objects
130       // use can access
131       List   securityConditions = null;
132       
133       securityConditions = securityFilter.getConditions();
134       if (securityConditions != null)
135       {
136          // There are some security restrictions so create a subcondition which
137          // will say that only those IDs may be accessed that match subquery 
138          // that describes the access rights
139          String  [] otherFromWhere;
140          
141          // We must use the table name since the calling function may do join as well
142          buffer.append(" and ");
143          buffer.append(strTableName); 
144          buffer.append(".ID in (");
145          
146          bufferInnerQuery.append("select ");
147          bufferInnerQuery.append(strTableName);
148          bufferInnerQuery.append(".ID from ");
149          bufferInnerQuery.append(strTableName);
150          // Here we finish FROM clause since, the security may depend on other 
151          // tables which must go to FROM claus
152          otherFromWhere = schema.getJoinFromWhere(securityConditions, null);
153          if (otherFromWhere != null)
154          {            
155             bufferInnerQuery.append(otherFromWhere[0]);
156          }
157          // Here is FROM done and we add WHERE joining those two tables
158          bufferInnerQuery.append(" where ");
159          if (schema.isInDomain())
160          {
161             bufferInnerQuery.append(strTableName);
162             bufferInnerQuery.append(".DOMAIN_ID=");
163             bufferInnerQuery.append(CallContext.getInstance().getCurrentDomainId());
164          }
165          // this is used only for confirmation that 
166          // inner select where clause will be not empty
167          else
168          {  
169             bufferInnerQuery.append(strTableName);
170             bufferInnerQuery.append(".ID is not null ");            
171          }
172 
173          if ((otherFromWhere != null) && (otherFromWhere[1].length() > 0))
174          {
175             bufferInnerQuery.append(" and ");
176             bufferInnerQuery.append(otherFromWhere[1]);
177          }
178          bufferInnerQuery.append(" and (");
179          
180          // Here we construct WHERE clause for the security   
181          for (int iCount = 0; iCount < securityConditions.size(); iCount++)
182          {
183             if (iCount > 0)
184             {
185                bufferInnerQuery.append(" or ");            
186             }
187             parseCondition(bufferInnerQuery, (DataCondition) securityConditions.get(iCount), 
188                            schema);
189          }            
190          bufferInnerQuery.append(")");
191          
192          // At this moment we have created inner query that will be processed first and 
193          // particular results will be added to the outer query
194          try
195          {
196             // Request autocommit true since we are just reading data from the database
197             cntConnection = DatabaseConnectionFactoryImpl.getInstance().requestConnection(true);
198             stmQuery = cntConnection.createStatement();
199             rsQueryResults = stmQuery.executeQuery(bufferInnerQuery.toString());
200             
201             bufferInnerQuery.delete(0, bufferInnerQuery.length());
202             while (rsQueryResults.next())
203             {
204                if (iIndex > 0)
205                {
206                   bufferInnerQuery.append(", ");
207                }
208                bufferInnerQuery.append(rsQueryResults.getInt(1));
209                iIndex++;
210             }
211             // if there were not results returned, use value -1 as the result from subselect
212             if (iIndex == 0)
213             {
214                bufferInnerQuery.append("-1");
215             }
216          }
217          catch (SQLException   sqleExc)
218          {
219             throw new OSSDatabaseAccessException(
220                          "Failed to retrieve specified data by subselect " +
221                          " from the database.", sqleExc);
222          }
223          finally
224          {
225             DatabaseUtils.closeResultSetAndStatement(rsQueryResults, stmQuery);
226             DatabaseConnectionFactoryImpl.getInstance().returnConnection(cntConnection);
227          }
228 
229          // append results (returned IDs separated by ',') instead of the subselect
230          buffer.append(bufferInnerQuery);
231          buffer.append(")");
232       }
233       
234       return buffer.toString();  
235    }
236 
237    /**
238     * {@inheritDoc}
239     */
240    public List   getObjectList(
241       ListOptions         options,
242       String                query,
243       ListDatabaseFactory factory,
244       ListDatabaseSchema schema        
245    ) throws OSSException
246    {
247       return super.getObjectList(options, query, factory, schema);
248 
249       // This code is commented because it can be used for SQL_CALC_FOUND_ROWS clause.
250       // but performance is worse that using COUNT(*) + preprocessed query
251 /*
252       List              lstObjects     = null;
253       Connection        cntConnection  = null;
254       ResultSet         rsQueryResults = null;
255       ResultSet         rsQueryResults1 = null;
256       PreparedStatement pstmQuery      = null;
257       PreparedStatement pstmQuery1      = null;
258       MyTimer           timer          = new MyTimer();
259 
260       try
261       {
262          // Try to load information from the database
263          cntConnection = DatabaseConnectionFactoryImpl.getInstance().requestConnection();
264       
265          int iCount = 0;
266 
267          if ((!DatabaseImpl.getInstance().hasAbsolutePositioningSupport())
268              || (DatabaseImpl.getInstance().preferCountToLast()))
269          {
270             // --------------------------------------------------------------
271             // 1. If the count() is preferred to last() to determine how
272             // many results are there available or if the last() is not supported
273             // then execute count separately
274             // --------------------------------------------------------------
275             
276             // default implementation doesn't do anything
277             // if there is supported LIMIT, process changes on original query and use them
278             query = preprocessSelectQuery(query, options);
279 
280             //timer.reset();
281             pstmQuery = cntConnection.prepareStatement(query);
282             if (schema.isInDomain())
283             {
284                pstmQuery.setInt(1, options.getDomainId());
285             }
286             // s_logger.info("Duration for prepare statement = " + timer.toString());
287 
288             timer.reset();
289             rsQueryResults = pstmQuery.executeQuery();
290 
291                         pstmQuery1 = cntConnection.prepareStatement("SELECT FOUND_ROWS()");  
292             rsQueryResults1 = pstmQuery1.executeQuery();
293             if (DatabaseImpl.getInstance().hasSelectListRangeSupport())
294             {
295                rsQueryResults1.next();
296                iCount = rsQueryResults1.getInt(1);
297             }
298             
299             // set option positioning, at this position we know the real number of all records
300             setOptionPositioning(iCount, options);
301             
302             // allocate object list
303             lstObjects = allocateObjectList(options, iCount);
304    
305             if (lstObjects != null)
306             {
307                // If query with list range support will be used then the result
308                // is the first record in the result set
309                // timer.reset();
310                if (DatabaseImpl.getInstance().hasSelectListRangeSupport())
311                {
312                   rsQueryResults.first();
313                }
314                else
315                {
316                   rsQueryResults.absolute(options.getBeginPosition());                  
317                }
318                // s_logger.info("Duration for absolute/first() for load data = " 
319                //               + timer.toString());
320             }
321          }
322          else
323          {      
324             // --------------------------------------------------------------
325             // 2. If the last() is preferred to count() so determine how many
326             // items are available by executing the query and checking how 
327             // many items it returned
328             // --------------------------------------------------------------
329    
330             // s_logger.info("Preparing query " + query);
331             // timer.reset();
332             pstmQuery = cntConnection.prepareStatement(
333                query,
334                DatabaseImpl.getInstance().getSelectListResultSetType(), 
335                DatabaseImpl.getInstance().getSelectListResultSetConcurrency()
336             );
337             if (schema.isInDomain())
338             {
339                pstmQuery.setInt(1, options.getDomainId());
340             }
341             // s_logger.info("Duration for prepare statement = " + timer.toString());
342             
343             // timer.reset();
344             rsQueryResults = pstmQuery.executeQuery();
345             // s_logger.info("Duration for executing query = " + timer.toString());
346    
347             // We should use last() to determine total number of items
348             // timer.reset();
349             iCount = getTotalRecords(rsQueryResults, options);
350             // s_logger.info("Duration for last() = " + timer.toString());
351 
352             // set option positioning, at this position we know the real number of all records
353             setOptionPositioning(iCount, options);
354             
355             // allocate object list
356             lstObjects = allocateObjectList(options, iCount);
357 
358             if (lstObjects != null)
359             {
360                // timer.reset();
361                rsQueryResults.absolute(options.getBeginPosition());                  
362                // s_logger.info("Duration for absolute() for load data = " + timer.toString());
363             }
364          }
365 
366          // =====================================================================
367          
368          // At this point we have already constructed and executed query, 
369          // allocated object list and set up options. Now load particular 
370          // data from the result set.
371          if (lstObjects != null)
372          {                     
373             if (GlobalConstants.ERROR_CHECKING)
374             {        
375                assert iCount > 0 
376                       : "List should be null for empty result";
377             }
378             
379             int   iHelpCounter = 0; 
380             // Cache this so we don't have to call it always inside of loop
381             int[] columnCodes = options.getRetrieveColumnCodes();
382 
383             // not to call rsQueryResults.next() for first loop
384             // timer.reset();
385             while ((iHelpCounter == 0 || rsQueryResults.next())
386                && ((iHelpCounter < options.getPageSize()) 
387                || (options.getPageSize() == ListOptions.PAGE_SIZE_ALL)))
388             {
389                iHelpCounter++;
390                lstObjects.add(
391                factory.load(rsQueryResults, columnCodes, 1));
392             };
393             // s_logger.info("Duration for load data = " + timer.toString());
394          }
395 
396          // =====================================================================
397 
398          // Call method that check if can be processed list keepeng selected and
399          // if yes, do it.
400          lstObjects = getObjectListKeepSelected (factory, cntConnection, query, 
401                                                  options, lstObjects, schema);
402       }
403       catch (SQLException sqleExc)
404       {
405          throw new OSSDatabaseAccessException(
406                       "Failed to retrieve specified list of data objects" +
407                       " from the database.", sqleExc);
408       }
409       finally
410       {
411          DatabaseUtils.closeResultSetAndStatement(rsQueryResults1, pstmQuery1);
412          DatabaseUtils.closeResultSetAndStatement(rsQueryResults, pstmQuery);
413          DatabaseConnectionFactoryImpl.getInstance().returnConnection(cntConnection);
414       }
415    
416       return lstObjects;
417 */
418    }
419 
420    // Helper methods ///////////////////////////////////////////////////////////
421    
422    /**
423     * {@inheritDoc}
424     */
425    protected String   preprocessSelectQuery(
426       String               inputQuery,
427       ListOptions        options,
428       ListDatabaseSchema schema
429    ) throws OSSException
430    {
431      // If LIMIT is supported, process changes on original query and use them
432      if (DatabaseImpl.getInstance().hasSelectListRangeSupport())
433      {
434          // For MySQL there will be added 'LIMIT X, Y' clause at the end of the 
435          // input query. Also there has to be added preferred index that should 
436          // be used for best performance. This index will be added only in the 
437          // case if there was used ORDER BY clause and ONLY ONE column is used 
438          // inside ORDER BY clause. In this case there will be added index 
439          // that will be constructed as index prefix and order by column name 
440          // (this index should be already created within the particular DB schema).
441    
442          StringBuffer   buffer          = new StringBuffer  ();
443          StringBuffer   bufferUsedIndex = new StringBuffer  ();
444    
445          // We need to search as case sensitive. But carefull, we cannot change 
446          // the case of the original query since some databases are case sensitive 
447          // when using columns names, etc. So we will convert original query to 
448          // lower case and we will provide search on this new query.
449          String         strQueryLowerCase = inputQuery.toLowerCase();
450    
451          String         strTableName = "";
452          String         strColumnName = "";
453          String         strFromTables = "";
454          String         strIndexName = "";
455    
456          boolean      bCanUseIndex     = false;
457          boolean      bCanUseTableName = false;
458    
459          // first find out if there is used ORDER BY clause and in that case if there 
460          // will be used just one column for sorting
461          int iSortColumnStart      = strQueryLowerCase.indexOf(" order by ");
462          int iSortColumnEnd        = strQueryLowerCase.lastIndexOf(" asc");
463          int iSortColumnEndDesc    = strQueryLowerCase.lastIndexOf(" desc");
464          int iFromWhereColumnStart = -1;
465          int iFromWhereColumnEnd   = -1;
466          int ihelpIndex            = -1;
467          int iBeginPosition        = 0;
468 
469          // set end position for ORDER BY clause
470          if (iSortColumnEndDesc > iSortColumnEnd)
471          {
472             iSortColumnEnd = iSortColumnEndDesc;
473          }
474 
475          // check if there is used ORDER BY - ASC/DESC clause within the SQL command 
476          if (iSortColumnStart != -1)
477          {
478             if (GlobalConstants.ERROR_CHECKING)
479             {
480                assert iSortColumnEnd != -1
481                       : "There should be set up value for ASC/DESC position."; 
482             }
483             
484             // At this point we know that there is used ORDER BY - ASC/DESC clause and
485             // we will parse substring from this clause. If there will be used only one
486             // column for ordering, we can add index using to the final SQL command.
487             strColumnName = inputQuery.substring(iSortColumnStart + " order by ".length(), 
488                                                    iSortColumnEnd);
489             // find out if there is just one column used in the sort 
490             // (there will be no ',' separator)
491             if (strColumnName.indexOf(",") == -1)
492             {
493                // Set start and end position of the FROM-WHERE clause. This values vill be used
494                // more times and they will be defined here just once.
495                iFromWhereColumnStart = strQueryLowerCase.indexOf(" from ");
496                iFromWhereColumnEnd = strQueryLowerCase.indexOf(" where ");
497    
498                // At this point we know that there is used only one column. Column can be defined
499                // as TABLE.COLUMN_NAME, so we need to parse table name and also column name 
500                // separately. And index that will be used will be added after table name within
501                // the FROM - WHERE clause of the original input query.
502                if (strColumnName.indexOf(".") != -1)
503                {
504                   strTableName = strColumnName.substring(0, strColumnName.indexOf("."));
505                   strColumnName = strColumnName.substring(strColumnName.indexOf(".") + 1, 
506                                                           strColumnName.length());
507                   bCanUseIndex = true;
508                   bCanUseTableName = true;
509                }
510                else
511                {
512                   // If there is not specified column within the ORDER-ASC/DESC clause
513                   // as TABLE.COLUMN_NAME but only as COLUMN_NAME, then we can add index only
514                   // in the case if there is JUST ONE table defined within the FROM - WHERE clause.
515    
516                   // Get string (all columns) within the FROM-WHERE clause. This will be used 
517                   // for finding right position of the possible used index.
518                   strFromTables = inputQuery.substring(iFromWhereColumnStart + " from ".length(),
519                                                        iFromWhereColumnEnd); 
520    
521                   if (strFromTables.indexOf(",") == -1)
522                   {
523                      // there is defined just one table so we can use 1st time retrieved column name
524                      bCanUseIndex = true;
525                      
526                      // store this table name for constructing used index
527                      strTableName = strFromTables;
528                   }
529                   else
530                   {
531                      // There are more tables within the FROM-WHERE clause and column used within
532                      // the ORDER BY - ACS/DESC clause has no defined its table, so we can't use
533                      // indexes in the final SQL query because we dont't know table the used
534                      // index has to be used for.
535                      
536                      // This situation should not became, because getColumns() method is 
537                      // responsible for this construction and if there are used more tables
538                      // within the FROM-WHERE clause, all tables used in the query must be 
539                      // specified by belonging table name.  
540                      if (GlobalConstants.ERROR_CHECKING)
541                      {
542                         assert true 
543                                : "There should be defined belonging table name" +
544                                  " to the column within the ORDER BY-ASC/DESC" +
545                                  " clause while there are used more tables " +
546                                  " within the FROM-WHERE clause."; 
547                      }
548                   }
549                }
550             }
551             else
552             {
553                // In this point we known there are more columns specified witin the 
554                // ORDER BY-ASC/DESC clause. We don't use index, we just log it here.
555                s_logger.info("There will be not used index because there are more columns " +
556                              "within the ORDER BY-ASC/DESC clause.");
557             }
558          }
559          
560          buffer.append(inputQuery);
561          // ----------------------------------------------------------------
562          // This code is commented because it can be used for SQL_CALC_FOUND_ROWS clause.
563          // But performance is worse that using COUNT(*) + preprocessed query
564          // ----------------------------------------------------------------
565          // int iStartPosition  =  strQueryLowerCase.indexOf("select ") + "select ".length();
566          // int iStartFromPosition  =  strQueryLowerCase.indexOf(" from ");
567          // ----------------------------------------------------------------
568 
569          // specify index if it will be used
570          if (bCanUseIndex)
571          {
572             // construct name of the index first
573             strIndexName = getConstructListIndexName(strTableName, strColumnName);
574             // use index only for data object that allows it
575             if (schema.isExistingIndex(strIndexName))
576             {
577                // construct USE INDEX command that will be used 
578                bufferUsedIndex.append(" use index (");
579                bufferUsedIndex.append(strIndexName);
580                bufferUsedIndex.append(") ");
581             }
582    
583             if (bCanUseTableName)
584             {
585                // If there are more tables defined within the FROM-WHERE clause, we need
586                // to place used index after particular table name the index was defined for.
587                
588                // find first occurence of the specified table name, starting with 
589                // FROM clause position.
590                ihelpIndex = buffer.indexOf(strTableName, iFromWhereColumnStart);
591                buffer.insert(ihelpIndex + strTableName.length() + 1, bufferUsedIndex);
592             }
593             else
594             {
595                // If there is defined only one table within the FROM-WHERE clause, we will
596                // simple place used index before WHERE clause.
597                buffer.insert(iFromWhereColumnStart, bufferUsedIndex);        
598             }
599          }
600 
601          // ----------------------------------------------------------------
602          // This code is commented because it can be used for SQL_CALC_FOUND_ROWS clause.
603          // But performance is worse that using COUNT(*) + preprocessed query
604          // ----------------------------------------------------------------
605          // buffer.insert(iFromWhereColumnStart, ", FOUND_ROWS() AS FR ");
606          // Add start query without first SELECT clause
607          // buffer.insert(iStartPosition, "SQL_CALC_FOUND_ROWS ");   
608          // ----------------------------------------------------------------
609          
610          buffer.append(" limit ");
611          if (options.getBeginPosition() > 0)
612          {
613             iBeginPosition = options.getBeginPosition() - 1;
614          }
615          buffer.append(iBeginPosition);
616          buffer.append(", ");
617          buffer.append(options.getPageSize());
618    
619          return buffer.toString();
620       }
621       else
622       {
623          // In case the LIMIT is not supported or we do not want to use it, 
624          // just call super
625          return super.preprocessSelectQuery(inputQuery, options, schema);
626       }
627    }
628 
629    /**
630     * {@inheritDoc}
631     */
632    protected String   preprocessSelectQueryForCreationDate(
633       String   inputQuery,
634       ListOptions options,
635       ListDatabaseSchema schema
636    ) throws OSSException
637    {
638       return ListQueryPreprocessor.preprocessSelectQueryForCreationDate(
639                                       inputQuery, options, schema);
640    }
641 }
642
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags