AW: OrderBy clause in DefaultQuery

"Vidakovic Aleksandar" <[email protected]> Sun, 4 Apr 2004 19:29:20 +0200

Newsgroups	gmane.comp.java.keel.user
Message-ID	<[email protected]>

I think I solved the problems with connection pool. As I don't have no outgoing cvs at the moment I send you my version of the query service as a patch. 

I also had problems with the order by clause. Solved it for my needs, but did not revise it if it's suitable in general...

Cheers,

Aleks

-----Ursprüngliche Nachricht-----
Von: Michael Nash JGlobal.com [mailto:[email protected]]
Gesendet: Sonntag, 4. April 2004 17:10
An: user-6VIttnCrOeJXNEnpj1eHPNi2O/[email protected]
Betreff: Re: [Keel User] OrderBy clause in DefaultQuery


Nathan:

I don't think the query service should *insist* on an Order by, so I'd have to say it's a bug - I suppose it's a matter of perspective, but I'd call it a bug :-)

Ideally, I'd think that query should allow you to have whatever SQL you want to have, for maximum flexibility.

Mike

On Sun,  4 Apr 2004 00:13:56 -0800
Nathan Kontny <Nathan.Kontny-UgwZ4owrJFB8UrSeD/[email protected]> wrote:

> The QueryService is forcing me to have an OrderBy clause.  Is this a bug? 
> 
> The bindParameters method has: 
> 
> if ((myStatement.toString().toUpperCase().indexOf("ORDER BY "))
> 			== -1) {
> 			log.info("sindex not found, so append");
> 			myStatement.append("\n ORDER BY $orderByClause ");
> 
> _______________________________________________
> User mailing list
> [email protected]
> http://lists.keelframework.org/listinfo.cgi/user-keelframework.org


-- 
Michael Nash

JGlobal Ltd 
Next-Generation Web Application Development and Open Source Support
http://www.jglobal.com

Bahamas Commerce and Trade
Offshore eCommerce Hosting and Business Services
http://www.bahamascommerce.com

_______________________________________________
User mailing list
[email protected]
http://lists.keelframework.org/listinfo.cgi/user-keelframework.org

patch.txt (text/plain, 8.2 KB)

Index: DefaultQuery.java
===================================================================
RCS file: /cvsroot/keel/svc-query-jdbc/src/java/org/keel/services/query/jdbc/DefaultQuery.java,v
retrieving revision 1.4
diff -u -r1.4 DefaultQuery.java
--- DefaultQuery.java	10 Mar 2004 03:37:28 -0000	1.4
+++ DefaultQuery.java	4 Apr 2004 16:43:46 -0000
@@ -28,6 +28,7 @@
 import org.apache.avalon.excalibur.datasource.ids.IdGenerator;
 import org.apache.avalon.excalibur.pool.Poolable;
 import org.apache.avalon.excalibur.pool.Recyclable;
+import org.apache.avalon.framework.activity.Disposable;
 import org.apache.avalon.framework.activity.Initializable;
 import org.apache.avalon.framework.configuration.Configurable;
 import org.apache.avalon.framework.configuration.Configuration;
@@ -57,7 +58,7 @@
  */
 public class DefaultQuery
 	extends AbstractKeelServiceable
-	implements Query, Poolable, Recyclable, LogEnabled, Configurable, Initializable {
+	implements Query, Poolable, Recyclable, LogEnabled, Configurable, Initializable, Disposable {
 
 	private static Logger log = null;
     
@@ -130,44 +131,53 @@
 		// ===================================================				
 		try {
 			myConnection = dataSource.getConnection();
+			System.out.println("[CONNECTION] instantiated: " + myConnection.toString() );
 		} catch (SQLException e) {
 						throw new QueryException(
 							e,
 							"Obtaining database connection failed");
 		}
 		
-		// ===================================================
-		// We are building a tabular report which analyzes the objects
-		// named in ObjectKeys.  So if ObjectKeys collection is not empty we
-		// store ObjectKeys info the id cache table called queryJoin. myQueryId identifies the cache.
-		// Warning: If ObjectKeys is empty the method polymorphism
-		// getQueryResults with no parameter should be used instead
-		// ===================================================
-		if (objectKeys != null) {
-			myQueryId = cacheObjectKeys(objectKeys, myQueryId, myConnection);
-		} //end if
-		else
-		{
-			log.warn("\n\n\n getQueryResults(objectKeys) has been called" +
-				" with objectKeys == null, but getQueryResults() without parameters should be used");
-		}
-
-
-		// ===================================================
-		// now run the query and get tabular report
-		// ===================================================
-		queryResult = runQuery(myConnection, myQueryId);
-		
-		// ===================================================
-		// Eliminate the object Keys cache: no longer needed
-		// ===================================================		
-		deCacheObjectKeys(myQueryId, myConnection);
-		
-// modified by aleks
+//		 modified by aleks
 		try {
-			myConnection.close();
-		} catch (SQLException se) {
-			throw new QueryException(se);
+			// ===================================================
+			// We are building a tabular report which analyzes the objects
+			// named in ObjectKeys.  So if ObjectKeys collection is not empty we
+			// store ObjectKeys info the id cache table called queryJoin. myQueryId identifies the cache.
+			// Warning: If ObjectKeys is empty the method polymorphism
+			// getQueryResults with no parameter should be used instead
+			// ===================================================
+			if (objectKeys != null) {
+				myQueryId = cacheObjectKeys(objectKeys, myQueryId, myConnection);
+			} //end if
+			else
+			{
+				log.warn("\n\n\n getQueryResults(objectKeys) has been called" +
+					" with objectKeys == null, but getQueryResults() without parameters should be used");
+			}
+	
+	
+			// ===================================================
+			// now run the query and get tabular report
+			// ===================================================
+			queryResult = runQuery(myConnection, myQueryId);
+			
+			// ===================================================
+			// Eliminate the object Keys cache: no longer needed
+			// ===================================================		
+			deCacheObjectKeys(myQueryId, myConnection);
+		
+		} catch (QueryException qe) {
+			throw new QueryException(qe);
+		}
+		finally
+		{
+			try {
+				myConnection.close();
+				myConnection = null;
+			} catch (SQLException e1) {
+				throw new QueryException(e1);
+			}
 		}
 // modified by aleks
 		return queryResult;	
@@ -200,6 +210,7 @@
 			// ===================================================				
 			try {
 				myConnection = dataSource.getConnection();
+				System.out.println("[CONNECTION2] instantiated: " + myConnection.toString() );
 			} catch (SQLException e) {
 							throw new QueryException(
 								e,
@@ -210,19 +221,23 @@
 			// now run the query and get tabular report
 			// There is no queryId, so we pass 0 as a dummy parameter
 			// ===================================================
-			queryResult = runQuery(myConnection, 0 );
-		
-
-		
-// modified by aleks
+//			 modified by aleks
 			try {
-				myConnection.close();
-			} catch (SQLException se) {
-				throw new QueryException(se);
+				queryResult = runQuery(myConnection, 0 );
+			} catch (QueryException qe) {
+				throw new QueryException(qe);
+			}
+			finally
+			{
+				try {
+					myConnection.close();
+					myConnection = null;
+				} catch (SQLException e1) {
+					throw new QueryException(e1);
+				}
 			}
 // modified by aleks
 			return queryResult;	
-		
 	}
 
 	/**
@@ -317,6 +332,7 @@
 					{
 						try {
 							aStatement.close();
+							aStatement = null;
 						} catch (SQLException se) {
 							throw new QueryException(se);
 						}
@@ -360,6 +376,7 @@
 		{
 			try {
 				cleanupStatement.close();
+				cleanupStatement = null;
 			} catch (SQLException se) {
 				throw new QueryException(se);
 			}
@@ -393,7 +410,7 @@
 		log.debug(
 			"Query statement after, join id,  param subsitution and append ORDER by clause:"
 				+ myStatement);
-		List tempResult = executeQuery(myStatement);
+		List tempResult = executeQuery(myConnection, myStatement);
 		
 
 		// ===================================================
@@ -429,11 +446,13 @@
 		log.info(
 			"Does Config Query have ORDER by clause, not if this index is -1:"
 				+ (myStatement.toString().toUpperCase().indexOf("ORDER BY ")));
+// modified by aleks
 		if ((myStatement.toString().toUpperCase().indexOf("ORDER BY "))
-			== -1) {
+			== -1 && myQueryId>0) {
 			log.info("sindex not found, so append");
 			myStatement.append("\n ORDER BY $orderByClause ");
 		}
+// modified by aleks
 		try {
 			String oneCriteriaCode = null;
 			for (int i = 0; i < criteria.length; i++) {
@@ -474,8 +493,8 @@
 	 * @return Set queryResultRows containing Maps, each representing a row.
 	 * @throws QueryException Thrown in the case of system failure.
 	 */
-	private List executeQuery(SuperString myStatement) throws QueryException {
-		Connection myConnection = null;
+	private List executeQuery(Connection myConnection, SuperString myStatement) throws QueryException {
+		//Connection myConnection = null;
 		Statement sqlStatement = null;
 		ResultSet myResultSet = null;
 		List queryResultRows = new ArrayList();
@@ -497,7 +516,7 @@
 			// ================================
 			// Execute the sql query
 			// ================================
-			myConnection = dataSource.getConnection();
+			//myConnection = dataSource.getConnection();
 			log.debug("Running statement '" + myStatement.toString() + "'");
 			sqlStatement = myConnection.createStatement();
 			myResultSet =
@@ -528,9 +547,20 @@
 			throw new QueryException(de);
 		} finally {
 			try {
-				myResultSet.close();
-				sqlStatement.close();
-				myConnection.close();
+				if(myResultSet!=null)
+				{
+					myResultSet.close();
+					myResultSet = null;
+				}
+				if(sqlStatement!=null)
+				{
+					sqlStatement.close();
+					sqlStatement = null;
+				}
+//				if(myConnection!=null)
+//				{
+//					myConnection.close();
+//				}
 			} catch (SQLException se) {
 				throw new QueryException(se);
 			}
@@ -583,4 +613,10 @@
 		releaseServices();
 	}
 
+	/* (non-Javadoc)
+	 * @see org.apache.avalon.framework.activity.Disposable#dispose()
+	 */
+	public void dispose() {
+        releaseServices();
+	}
 }