Author: fdesbois Date: 2010-01-06 09:54:51 +0100 (Wed, 06 Jan 2010) New Revision: 1745 Modified: trunk/topia-persistence/src/main/java/org/nuiton/topia/framework/TopiaQuery.java trunk/topia-persistence/src/main/java/org/nuiton/topia/persistence/TopiaDAOImpl.java Log: - WARN : change TopiaQuery method addSelect by setSelect - Add methods to easily have multiple values for one parameter (IN clause) - Add distinct 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-01-05 09:49:58 UTC (rev 1744) +++ trunk/topia-persistence/src/main/java/org/nuiton/topia/framework/TopiaQuery.java 2010-01-06 08:54:51 UTC (rev 1745) @@ -197,6 +197,8 @@ /** Select part of the query **/ protected String select; + protected boolean distinct; + /** From part of the query **/ protected String from; @@ -220,6 +222,8 @@ protected Class<E> mainEntityClass; + protected String mainAlias; + protected TopiaDAO<E> dao; /** @@ -287,6 +291,7 @@ */ protected TopiaQuery(Class<E> entityClass, String alias) { this(entityClass); + this.mainAlias = alias; this.from += " " + alias; } @@ -329,8 +334,13 @@ */ public String fullQuery() { String result = ""; + String selectKeyWord = "SELECT "; + selectKeyWord += distinct ? " DISTINCT " : ""; if (select != null) { - result = select; + result = selectKeyWord + select; + // Set default select if there is more than one table in from part and main alias is defined + } else if (from.contains(",") && !StringUtils.isEmpty(mainAlias)) { + result = selectKeyWord + mainAlias; } result += from; if (where != null) { @@ -400,6 +410,9 @@ * @return the TopiaQuery */ public TopiaQuery<E> add(String where) { + if (StringUtils.isEmpty(where)) { + return this; + } if (this.where == null) { this.where = " WHERE "; } else { @@ -426,13 +439,18 @@ * @return the TopiaQuery */ public TopiaQuery<E> add(String paramName, Op constraint, Object paramValue) { + String valueName = getValueName(paramName); + parentheses = false; + return add(paramName + " " + constraint + " :" + valueName).addParam(valueName, paramValue); + } + + protected String getValueName(String paramName) { int dot = paramName.lastIndexOf("."); String valueName = paramName; if (dot != -1) { valueName = paramName.substring(dot+1); } - parentheses = false; - return add(paramName + " " + constraint + " :" + valueName).addParam(valueName, paramValue); + return valueName; } /** @@ -460,6 +478,56 @@ } /** + * Add an element to the query with a list of different possible values. + * + * @param paramName name of the parameter in the query + * @param values different values for this parameter + * @return the TopiaQuery + * @see #add(java.lang.String, java.util.Collection, boolean) + */ + public TopiaQuery<E> add(String paramName, Collection<Object> values) { + return add(paramName, values, false); + } + + /** + * Add an element to the query with a list of different values. The IN key word will be used to + * set the different values. An other constraint on nullity can be set with isNull argument. + * The element can have one of the value from the collection or can be null if the isNull argument is true. + * + * @param paramName name of the parameter in the query + * @param values different values for this parameter + * @param isNull use it to test the nullity of parameter + * @return + * @see #add(java.lang.String) + */ + public TopiaQuery<E> add(String paramName, Collection<Object> values, boolean isNull) { + String queryIn = ""; + if (!values.isEmpty()) { +// String valueName = getValueName(paramName); +// queryIn += paramName + " IN (:" + valueName + ")"; +// addParam(valueName, values); + + queryIn += paramName + " IN ("; + int count = 1; + for (Object value : values) { + String valueName = getValueName(paramName) + count; + if (count != 1) { + queryIn += ", "; + } + queryIn += ":" + valueName; + addParam(valueName, value); + count++; + } + queryIn += ")"; + } + if (isNull) { + queryIn += !values.isEmpty() ? " OR " : ""; + queryIn += paramName + " IS NULL"; + } + return add(queryIn); + } + + /** * Add a map of properties to the where clause of the query. Each property will be added to the query with * Op.EQ operation, the key in the map is the property name, and the value is the value of the parameter in the * query. @@ -486,22 +554,33 @@ } /** - * Add an element to the select in the query. Used to add some parameters for the return of query. + * Set the select in the query. Depends on the result wanted in execute methods. * * @param select element to add * @return the TopiaQuery */ - public TopiaQuery<E> addSelect(String select) { - if (this.select == null) { - this.select = "SELECT "; - } else { - this.select += ", "; - } - this.select += select; + public TopiaQuery<E> setSelect(String select) { +// if (this.select == null) { +// this.select = "SELECT "; +// } else { +// this.select += ", "; +// } +// this.select += select; + this.select = select; return this; } /** + * Add the distinct key word in the query. The result will not have multiple same values. + * + * @return the TopiaQuery + */ + public TopiaQuery<E> addDistinct() { + this.distinct = true; + return this; + } + + /** * Add an element to the order in the query. Used to add some parameters to order by. * * @param order element to add @@ -801,7 +880,7 @@ public Object executeToObject(TopiaContext transaction, String select) throws TopiaException { String oldValue = this.select; if (!StringUtils.isEmpty(select)) { - this.select = "SELECT " + select; + setSelect(select); } Object result = null; setMaxResults(1); Modified: trunk/topia-persistence/src/main/java/org/nuiton/topia/persistence/TopiaDAOImpl.java =================================================================== --- trunk/topia-persistence/src/main/java/org/nuiton/topia/persistence/TopiaDAOImpl.java 2010-01-05 09:49:58 UTC (rev 1744) +++ trunk/topia-persistence/src/main/java/org/nuiton/topia/persistence/TopiaDAOImpl.java 2010-01-06 08:54:51 UTC (rev 1745) @@ -711,7 +711,7 @@ public List<String> findAllIds() throws TopiaException { //List<String> find = context.find("select src.topiaId from " + entityClass.getSimpleName() + "Impl src"); //return find; - return createQuery().addSelect(TopiaEntity.TOPIA_ID).execute(context); + return createQuery().setSelect(TopiaEntity.TOPIA_ID).execute(context); } @Override