[exo-jcr-commits] exo-jcr SVN: r2684 - in core/branches/2.3.x: exo.core.component.organization.jdbc/src/test/java/org/exoplatform/services/organization and 1 other directory.

Author: tolusha Date: 2010-06-23 05:42:59 -0400 (Wed, 23 Jun 2010) New Revision: 2684 Modified: core/branches/2.3.x/exo.core.component.database/src/main/java/org/exoplatform/services/database/DBObjectPageList.java core/branches/2.3.x/exo.core.component.database/src/main/java/org/exoplatform/services/database/ObjectQuery.java core/branches/2.3.x/exo.core.component.organization.jdbc/src/test/java/org/exoplatform/services/organization/TestOrganizationService.java Log: EXOJCR-805: Can not search user with keyword that contain special character Modified: core/branches/2.3.x/exo.core.component.database/src/main/java/org/exoplatform/services/database/DBObjectPageList.java =================================================================== --- core/branches/2.3.x/exo.core.component.database/src/main/java/org/exoplatform/services/database/DBObjectPageList.java 2010-06-23 08:45:17 UTC (rev 2683) +++ core/branches/2.3.x/exo.core.component.database/src/main/java/org/exoplatform/services/database/DBObjectPageList.java 2010-06-23 09:42:59 UTC (rev 2684) @@ -22,7 +22,10 @@ import org.hibernate.Query; import org.hibernate.Session; +import java.util.HashMap; import java.util.List; +import java.util.Map; +import java.util.Map.Entry; /** * @author Tuan Nguyen (tuan08(a)users.sourceforge.net) @@ -38,6 +41,8 @@ private HibernateService service_; + private Map<String, Object> binding = new HashMap<String, Object>(); + public DBObjectPageList(HibernateService service, Class objectType) throws Exception { super(20); @@ -54,10 +59,17 @@ { super(20); service_ = service; - findQuery_ = oq.getHibernateQuery(); - countQuery_ = oq.getHibernateCountQuery(); + findQuery_ = oq.getHibernateQueryWithBinding(); + countQuery_ = oq.getHibernateCountQueryWithBinding(); + binding = oq.getBindingFields(); + Session session = service_.openSession(); - List l = session.createQuery(countQuery_).list(); + + Query countQuery = session.createQuery(countQuery_); + bindFields(countQuery); + + List l = countQuery.list(); + Number count = (Number)l.get(0); setAvailablePage(count.intValue()); } @@ -74,21 +86,41 @@ setAvailablePage(count.intValue()); } + @Override @SuppressWarnings("unused") protected void populateCurrentPage(int page) throws Exception { Session session = service_.openSession(); Query query = session.createQuery(findQuery_); + bindFields(query); + int from = getFrom(); query.setFirstResult(from); query.setMaxResults(getTo() - from); currentListPage_ = query.list(); } + @Override public List getAll() throws Exception { Session session = service_.openSession(); + Query query = session.createQuery(findQuery_); + bindFields(query); + return query.list(); } + + /** + * Bind a value to a named query parameter. + * + * @param query + */ + private void bindFields(Query query) + { + for (Entry<String, Object> entry : binding.entrySet()) + { + query.setParameter(entry.getKey(), entry.getValue()); + } + } } Modified: core/branches/2.3.x/exo.core.component.database/src/main/java/org/exoplatform/services/database/ObjectQuery.java =================================================================== --- core/branches/2.3.x/exo.core.component.database/src/main/java/org/exoplatform/services/database/ObjectQuery.java 2010-06-23 08:45:17 UTC (rev 2683) +++ core/branches/2.3.x/exo.core.component.database/src/main/java/org/exoplatform/services/database/ObjectQuery.java 2010-06-23 09:42:59 UTC (rev 2684) @@ -21,7 +21,9 @@ import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; +import java.util.HashMap; import java.util.List; +import java.util.Map; /** * @author Tuan Nguyen (tuan08(a)users.sourceforge.net) @@ -89,9 +91,6 @@ public String optimizeInputString(String value) { value = value.replace('*', '%'); - value = value.replaceAll("'", "&#39;"); - value = value.replaceAll("<", "&#60;"); - value = value.replaceAll(">", "&#62;"); return value; } @@ -176,6 +175,84 @@ return b.toString(); } + /** + * + * @return + */ + public String getHibernateQueryWithBinding() + { + StringBuffer b = new StringBuffer(); + b.append("from o in class ").append(type_.getName()); + if (parameters_.size() > 0) + { + b.append(" WHERE "); + for (int i = 0; i < parameters_.size(); i++) + { + if (i > 0) + b.append(" AND "); + Parameter p = parameters_.get(i); + if (p.value_ instanceof String) + { + if (p.field_.startsWith("UPPER") || p.field_.startsWith("LOWER")) + { + b.append(p.field_).append(p.op_).append(":").append(p.field_.substring(6, p.field_.length() - 1)) + .append(i); + } + else + { + b.append(" o.").append(p.field_).append(p.op_).append(":").append(p.field_).append(i); + } + } + else if (p.value_ instanceof Date) + { + String value = ft_.format((Date)p.value_); + b.append(" o.").append(p.field_).append(p.op_).append("'").append(value).append("'"); + } + else + { + b.append(" o.").append(p.field_).append(p.op_).append(p.value_); + } + } + } + + if (orderBy_ != null) + { + b.append(orderBy_); + } + + return b.toString(); + } + + /** + * + * @return + */ + public Map<String, Object> getBindingFields() + { + Map<String, Object> binding = new HashMap<String, Object>(); + + if (parameters_.size() > 0) + { + for (int i = 0; i < parameters_.size(); i++) + { + Parameter p = parameters_.get(i); + if (p.value_ instanceof String) + { + if (p.field_.startsWith("UPPER") || p.field_.startsWith("LOWER")) + { + binding.put(p.field_.substring(6, p.field_.length() - 1) + i, p.value_); + } + else + { + binding.put(p.field_ + i, p.value_); + } + } + } + } + + return binding; + } + public String getHibernateGroupByQuery() { StringBuffer b = new StringBuffer(); @@ -281,6 +358,48 @@ return b.toString(); } + /** + * + * @return + */ + public String getHibernateCountQueryWithBinding() + { + StringBuffer b = new StringBuffer(); + b.append("SELECT COUNT(o) FROM o IN CLASS ").append(type_.getName()); + if (parameters_.size() > 0) + { + b.append(" WHERE "); + for (int i = 0; i < parameters_.size(); i++) + { + if (i > 0) + b.append(" AND "); + Parameter p = parameters_.get(i); + if (p.value_ instanceof String) + { + if (p.field_.startsWith("UPPER") || p.field_.startsWith("LOWER")) + { + b.append(p.field_).append(p.op_).append(":").append(p.field_.substring(6, p.field_.length() - 1)) + .append(i); + } + else + { + b.append(" o.").append(p.field_).append(p.op_).append(":").append(p.field_).append(i); + } + } + else if (p.value_ instanceof Date) + { + String value = ft_.format((Date)p.value_); + b.append(" o.").append(p.field_).append(p.op_).append("'").append(value).append("'"); + } + else + { + b.append(" o.").append(p.field_).append(p.op_).append(p.value_); + } + } + } + return b.toString(); + } + static class Parameter { String op_; Modified: core/branches/2.3.x/exo.core.component.organization.jdbc/src/test/java/org/exoplatform/services/organization/TestOrganizationService.java =================================================================== --- core/branches/2.3.x/exo.core.component.organization.jdbc/src/test/java/org/exoplatform/services/organization/TestOrganizationService.java 2010-06-23 08:45:17 UTC (rev 2683) +++ core/branches/2.3.x/exo.core.component.organization.jdbc/src/test/java/org/exoplatform/services/organization/TestOrganizationService.java 2010-06-23 09:42:59 UTC (rev 2684) @@ -23,6 +23,7 @@ import org.exoplatform.test.BasicTestCase; import java.util.ArrayList; +import java.util.Calendar; import java.util.List; /** @@ -483,4 +484,94 @@ return user; } + public void testSearchWithSpecialCharacter() throws Exception + { + // create user + User user = userHandler_.createUserInstance("TestName"); + user.setPassword("default"); + user.setFirstName("L'test"); + user.setLastName("default"); + user.setEmail(&quot;exo(a)exoportal.org&quot;); + userHandler_.createUser(user, true); + + // search user + Query query = new Query(); + query.setFirstName("L'test"); + PageList list = userHandler_.findUsers(query); + assertEquals(1, list.getAll().size()); + assertEquals(1, list.getPage(1).size()); + } + + /** + * Find users using query and check it count. + */ + public void testFindUsers() throws Exception + { + + Calendar calendar = Calendar.getInstance(); + calendar.set(2008, 1, 1); + + User u = userHandler_.createUserInstance("tolik"); + u.setEmail("email@test"); + u.setFirstName("first"); + u.setLastName("last"); + u.setPassword("pwd"); + userHandler_.createUser(u, true); + + try + { + Query query = new Query(); + + query.setEmail("email@test"); + assertEquals(userHandler_.findUsers(query).getAll().size(), 1); + query.setEmail(null); + + query.setUserName("*tolik*"); + assertEquals(userHandler_.findUsers(query).getAll().size(), 1); + + query.setUserName("tolik*"); + assertEquals(userHandler_.findUsers(query).getAll().size(), 1); + + query.setUserName("tolik"); + assertEquals(userHandler_.findUsers(query).getAll().size(), 1); + + query.setFirstName("First"); + query.setLastName("laSt"); + assertEquals(userHandler_.findUsers(query).getAll().size(), 1); + query.setFirstName(null); + query.setLastName(null); + + Calendar calc = Calendar.getInstance(); + calc.set(2007, 1, 1); + query.setFromLoginDate(calc.getTime()); + query.setUserName("*tolik*"); + assertEquals(userHandler_.findUsers(query).getAll().size(), 1); + + calc.set(2050, 1, 1); + query.setFromLoginDate(calc.getTime()); + assertEquals(userHandler_.findUsers(query).getAll().size(), 0); + query.setFromLoginDate(null); + + calc.set(2007, 1, 1); + query.setToLoginDate(calc.getTime()); + assertEquals(userHandler_.findUsers(query).getAll().size(), 0); + + calc.set(2050, 1, 1); + query.setToLoginDate(calc.getTime()); + assertEquals(userHandler_.findUsers(query).getAll().size(), 1); + query.setUserName(null); + query.setToLoginDate(null); + + } + catch (Exception e) + { + e.printStackTrace(); + fail("Exception should not be thrown."); + } + finally + { + userHandler_.removeUser("tolik", true); + } + } + }