rendered paste body public List search(SearchContext searchContext) throws PersistenceException { logger.debug("search - start"); try { if (!searchContext.containsKey(SearchContext.PRINCIPAL_DIRECTORY_ID)) { throw new PersistenceException("Must supply a directory ID when searching principals."); } // hibernate configuration Configuration configuration = HibernateHelper.getConfiguration(); // load up our table relationships Table principalTable = configuration.getClassMapping(RemotePrincipal.class.getName()).getTable(); Table principalAttributesTable = configuration.getCollectionMapping(RemotePrincipal.class.getName() + ".attributes").getCollectionTable(); Table attributeTable = configuration.getClassMapping(AttributeValues.class.getName()).getTable(); Table attributeValuesTable = configuration.getCollectionMapping(AttributeValues.class.getName() + ".values").getCollectionTable(); // get the database dialect Dialect dialect = Dialect.getDialect(); // basic select stmt final String selectBasics = "select " + "p." + principalTable.getColumn(1).getQuotedName(dialect) + " as {p.directoryID}, " + "p." + principalTable.getColumn(2).getQuotedName(dialect) + " as {p.name}, " + "p." + principalTable.getColumn(3).getQuotedName(dialect) + " as {p.active}, " + "p." + principalTable.getColumn(4).getQuotedName(dialect) + " as {p.conception}, " + "p." + principalTable.getColumn(5).getQuotedName(dialect) + " as {p.lastModified} " + "from " + principalTable.getQuotedName(dialect) + " p"; // basic where stmt final String whereBasic = " where p." + principalTable.getColumn(1).getQuotedName(dialect) + " = ?"; // basic sorting restriction final String sortingRestriction = " order by p." + principalTable.getColumn(2).getQuotedName(dialect) + " asc" + ", p." + principalTable.getColumn(3).getQuotedName(dialect) + " asc"; StringBuffer sql = new StringBuffer(); // add the basic sql stmt sql.append(selectBasics); // add from clause for email if (searchContext.containsKey(SearchContext.PRINCIPAL_EMAIL)) { final String emailWhereClauseRestriction = " ," + principalAttributesTable.getQuotedName(Dialect.getDialect()) + " pa, " + attributeTable.getQuotedName(Dialect.getDialect()) + " a, " + attributeValuesTable.getQuotedName(Dialect.getDialect()) + " av"; sql.append(emailWhereClauseRestriction); } sql.append(whereBasic); // build in the sql restrictions, the order matters because of the parameter setting used later if (searchContext.containsKey(SearchContext.PRINCIPAL_NAME)) { final String nameRestriction = " and lower(p." + principalTable.getColumn(2).getQuotedName(Dialect.getDialect()) + ") like lower(?)"; sql.append(nameRestriction); } if (searchContext.containsKey(SearchContext.PRINCIPAL_ACTIVE)) { final String activeRestriction = " and p." + principalTable.getColumn(3).getQuotedName(Dialect.getDialect()) + " = ?"; sql.append(activeRestriction); } // search by sub-attribute email if (searchContext.containsKey(SearchContext.PRINCIPAL_EMAIL)) { final String emailRestriction = " and p." + principalTable.getColumn(1).getQuotedName(Dialect.getDialect()) + " = pa." + principalAttributesTable.getColumn(1).getQuotedName(Dialect.getDialect()) + " and p." + principalTable.getColumn(2).getQuotedName(Dialect.getDialect()) + " = pa." + principalAttributesTable.getColumn(2).getQuotedName(Dialect.getDialect()) + " and pa." + principalAttributesTable.getColumn(3).getQuotedName(Dialect.getDialect()) + " = a." + attributeTable.getColumn(1).getQuotedName(Dialect.getDialect()) + " and a." + attributeTable.getColumn(1).getQuotedName(Dialect.getDialect()) + " = av." + attributeValuesTable.getColumn(1).getQuotedName(Dialect.getDialect()) + " and av." + attributeValuesTable.getColumn(2).getQuotedName(Dialect.getDialect()) + " like ?"; sql.append(emailRestriction); } // add sorting restriction sql.append(sortingRestriction); // make the query object SQLQuery query = HibernateHelper.getSession().createSQLQuery(sql.toString()); query.addEntity("p", RemotePrincipal.class); // bind the parameters query.setLong(0, ((Long) searchContext.get(SearchContext.PRINCIPAL_DIRECTORY_ID)).longValue()); int parameterCount = 1; if (searchContext.containsKey(SearchContext.PRINCIPAL_NAME)) { query.setString(parameterCount, "%" + searchContext.get(SearchContext.PRINCIPAL_NAME) + "%"); parameterCount++; } if (searchContext.containsKey(SearchContext.PRINCIPAL_ACTIVE)) { query.setBoolean(parameterCount, ((Boolean) searchContext.get(SearchContext.PRINCIPAL_ACTIVE)).booleanValue()); parameterCount++; } if (searchContext.containsKey(SearchContext.PRINCIPAL_EMAIL)) { query.setString(parameterCount, "%" + searchContext.get(SearchContext.PRINCIPAL_EMAIL) + "%"); } // set start index if (searchContext.containsKey(SearchContext.SEARCH_INDEX_START)) { query.setFirstResult(((Long) searchContext.get(SearchContext.SEARCH_INDEX_START)).intValue()); } // set max results if (searchContext.containsKey(SearchContext.SEARCH_MAX_RESULTS)) { query.setMaxResults(((Long) searchContext.get(SearchContext.SEARCH_MAX_RESULTS)).intValue()); } return query.list(); } finally { logger.debug("search - end"); } }