Author: fdesbois Date: 2010-05-25 18:48:59 +0200 (Tue, 25 May 2010) New Revision: 1971 Url: http://nuiton.org/repositories/revision/topia/1971 Log: Evo #634 and Ano #635 : problem with addParams method, need new method for subquery case : addSubQuery(String queryPart, TopiaQuery subquery, boolean select); Modified: trunk/topia-persistence/src/main/java/org/nuiton/topia/framework/TopiaQuery.java trunk/topia-persistence/src/test/java/org/nuiton/topia/framework/TopiaQueryTest.java Modified: trunk/topia-persistence/src/main/java/org/nuiton/topia/framework/TopiaQuery.java =================================================================== --- trunk/topia-persistence/src/main/java/org/nuiton/topia/framework/TopiaQuery.java 2010-05-24 12:32:28 UTC (rev 1970) +++ trunk/topia-persistence/src/main/java/org/nuiton/topia/framework/TopiaQuery.java 2010-05-25 16:48:59 UTC (rev 1971) @@ -26,6 +26,8 @@ package org.nuiton.topia.framework; import org.apache.commons.beanutils.PropertyUtils; +import org.apache.commons.collections.CollectionUtils; +import org.apache.commons.lang.ObjectUtils; import org.apache.commons.lang.RandomStringUtils; import org.apache.commons.lang.StringUtils; import org.apache.commons.logging.Log; @@ -481,6 +483,77 @@ return this; } + /** + * Method used to add a subquery in an existing query. The params will be + * automatically checked and copied from the subquery to the current one. + * This method is used to inject {@code subquery} in SELECT or WHERE parts + * of the query. The flag {@code select} is used to tell the method that the + * element will be added to the select (if false, the element will be added + * to the where). The {@code queryPart} is the element in the query to bind + * with the {@code subquery}. The ? character is used to inject the subquery + * into the {@code queryPart}. Ex : + * <pre> + * // Add a SUM(subquery) into the SELECT of the query + * query.addSubQuery("SUM(?)", subquery, true); + * // Add a SUB_ELMT = (subquery) into the WHERE of the query + * query.addSubQuery("SUB_ELMT = (?)", subquery, false); + * </pre> + * + * @param queryPart part of the query where subquery need to be injected + * @param subquery existing topiaQuery as subquery + * @param select If true, the queryPart will be added to the SELECT + * statement, if false the default query statement is the + * WHERE + * @return the TopiaQuery + * @see TopiaQuery#getValueName(String) + * @since 2.4 + */ + public TopiaQuery addSubQuery(String queryPart, + TopiaQuery subquery, + boolean select) { + + List<Object> subqueryParams = subquery.getParams(); + String subqueryString = subquery.fullQuery(); + + // If no params is still defined, use those from subquery. + if (CollectionUtils.isEmpty(params)) { + addParams(subqueryParams); + } else { + for (int i = 0; i < subqueryParams.size(); i += 2) { + + String paramName = (String) subqueryParams.get(i); + Object paramValue = subqueryParams.get(i + 1); + + // Check existence of paramName + int index = params.indexOf(paramName); + + // If already defined + if (index != -1) { + Object existingValue = params.get(index + 1); + + // Only change paramName in not equals case + if (!ObjectUtils.equals(existingValue, paramValue)) { + String newParamName = getValueName(paramName); + // Replace old paramName in subquery + subqueryString = + subqueryString.replace(":" + paramName, + ":" + newParamName); + + // Add the param to the current query + addParam(newParamName, paramValue); + } + } + } + } + + // Replace ? injection by the subquery + String result = queryPart.replace("?", subqueryString); + if (select) { + return addSelect(result); + } + return add(result); + } + public List<Object> getParams() { if (params == null) { params = new ArrayList<Object>(); @@ -822,6 +895,7 @@ /** * Helper method for array type. Each value will be separated by a comma. + * TODO-fdesbois-2010-05-25 : replace this algo by StringUtil.join() * * @param array of String * @return a String with values of the array separated by a comma Modified: trunk/topia-persistence/src/test/java/org/nuiton/topia/framework/TopiaQueryTest.java =================================================================== --- trunk/topia-persistence/src/test/java/org/nuiton/topia/framework/TopiaQueryTest.java 2010-05-24 12:32:28 UTC (rev 1970) +++ trunk/topia-persistence/src/test/java/org/nuiton/topia/framework/TopiaQueryTest.java 2010-05-25 16:48:59 UTC (rev 1971) @@ -25,6 +25,8 @@ package org.nuiton.topia.framework; +import org.apache.commons.logging.Log; +import org.apache.commons.logging.LogFactory; import org.junit.After; import org.junit.AfterClass; import org.junit.Assert; @@ -39,25 +41,8 @@ */ public class TopiaQueryTest { - public TopiaQueryTest() { - } + private static final Log log = LogFactory.getLog(TopiaQueryTest.class); - @BeforeClass - public static void setUpClass() throws Exception { - } - - @AfterClass - public static void tearDownClass() throws Exception { - } - - @Before - public void setUp() { - } - - @After - public void tearDown() { - } - @Test public void testAdd() { @@ -97,4 +82,54 @@ query.fullQuery()); } + @Test + public void testAddSubQuery() { + + // Test 1 : Subquery in select part with two params with different values + TopiaQuery query = new TopiaQuery(QueriedEntity.class). + add(QueriedEntity.TEST_ADD, "value1"); + // Exist 2 params + Assert.assertEquals(2, query.getParams().size()); + + TopiaQuery subquery = new TopiaQuery(QueriedEntity.class). + add(QueriedEntity.TEST_ADD, "value2"); + + query.addSubQuery("SUM(?)", subquery, true); + log.debug(query); + // Add other params from subquery + Assert.assertEquals(4, query.getParams().size()); + + + // Test 2 : Subquery in select part with two params with different values + // one of both is null + query = new TopiaQuery(QueriedEntity.class). + add(QueriedEntity.TEST_ADD, TopiaQuery.Op.EQ, null); + // Exist 0 param (null value) + Assert.assertEquals(0, query.getParams().size()); + + subquery = new TopiaQuery(QueriedEntity.class). + add(QueriedEntity.TEST_ADD, "value1"); + + query.addSubQuery("SUM(?)", subquery, true); + log.debug(query); + // Add 2 params from subquery + Assert.assertEquals(2, query.getParams().size()); + + + // Test 3 : Subquery in where part with two params with same value + query = new TopiaQuery(QueriedEntity.class, "Q1"). + add(QueriedEntity.TEST_ADD, "value1"); + // Exist 2 params + Assert.assertEquals(2, query.getParams().size()); + + subquery = new TopiaQuery(QueriedEntity.class, "Q2"). + add(QueriedEntity.TEST_ADD, "value1"); + + query.addSubQuery("Q1 = (?)", subquery, false); + log.debug(query); + // Still 2 params + Assert.assertEquals(2, query.getParams().size()); + + } + }