Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- public List<Event> findSearchedEventsByStringList(List<String> preparedWordList) {
- try {
- String query = "SELECT * FROM (SELECT e.id AS id, 0 ";
- //@todo search in linked categories
- int preparedWordListSize = preparedWordList.size();
- String searchBoundedString = "";
- for (String searchString : preparedWordList) {
- query += "+ CASE WHEN LOWER(e.title) LIKE LOWER( '%" + searchString + "%' ) THEN 2 ELSE 0 END "
- + "+ CASE WHEN LOWER(e.description) LIKE LOWER( '%" + searchString + "%' ) THEN 1 ELSE 0 END ";
- searchBoundedString += searchString;
- if ((preparedWordListSize - 1) != preparedWordList.indexOf(searchString)) {
- searchBoundedString += " ";
- }
- }
- if (preparedWordListSize > 1) {
- query += " + CASE WHEN LOWER(e.title) LIKE LOWER( '%" + searchBoundedString + "%' ) THEN "
- + preparedWordListSize * 2 + " ELSE 0 END "
- + "+ CASE WHEN LOWER(e.description) LIKE LOWER( '%" + searchBoundedString + "%' ) THEN "
- + preparedWordListSize + " ELSE 0 END ";
- }
- query += " + CASE WHEN ec_count.event_category_count IS NOT NULL THEN ec_count.event_category_count ELSE 0 END ";
- query +="AS PRIORITY "
- + "FROM Event e LEFT OUTER JOIN ("
- + "SELECT COUNT(eec.events_id) as event_category_count , eec.events_id AS event_id "
- + "FROM event_eventcategory eec, eventcategory ec "
- + "WHERE eec.eventcategories_id = ec.id AND (1=0 ";
- for (String searchString : preparedWordList){
- query +=" OR LOWER(ec.title) LIKE LOWER( '%" + searchString + "%' )";
- }
- query +=") GROUP BY eec.events_id ";
- query +=") ec_count ON ec_count.event_id = e.id) ep, Event e2 "
- + "WHERE ep.PRIORITY > 0 AND ep.id=e2.id "
- + "ORDER BY ep.PRIORITY DESC ";
- return em.createNativeQuery(query, Event.class).getResultList();
- } catch (EJBException e) {
- return new ArrayList<Event>();
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement