Advertisement
Guest User

Untitled

a guest
Sep 21st, 2017
73
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Java 2.31 KB | None | 0 0
  1. public List<Event> findSearchedEventsByStringList(List<String> preparedWordList) {
  2.         try {
  3.             String query = "SELECT * FROM (SELECT e.id AS id, 0 ";
  4.             //@todo search in linked categories
  5.  
  6.             int preparedWordListSize = preparedWordList.size();
  7.             String searchBoundedString = "";
  8.             for (String searchString : preparedWordList) {
  9.                 query += "+ CASE WHEN LOWER(e.title) LIKE LOWER( '%" + searchString + "%' ) THEN 2 ELSE 0 END "
  10.                         + "+ CASE WHEN LOWER(e.description) LIKE LOWER( '%" + searchString + "%' ) THEN 1 ELSE 0 END ";
  11.                 searchBoundedString += searchString;
  12.                 if ((preparedWordListSize - 1) != preparedWordList.indexOf(searchString)) {
  13.                     searchBoundedString += " ";
  14.                 }
  15.             }
  16.  
  17.             if (preparedWordListSize > 1) {
  18.                 query += " + CASE WHEN LOWER(e.title) LIKE LOWER( '%" + searchBoundedString + "%' ) THEN "
  19.                         + preparedWordListSize * 2 + " ELSE 0 END "
  20.                         + "+ CASE WHEN LOWER(e.description) LIKE LOWER( '%" + searchBoundedString + "%' ) THEN "
  21.                         + preparedWordListSize + " ELSE 0 END ";
  22.             }
  23.            
  24.             query += " + CASE WHEN ec_count.event_category_count IS NOT NULL THEN ec_count.event_category_count ELSE 0 END ";
  25.  
  26.             query +="AS PRIORITY "
  27.                   + "FROM Event e LEFT OUTER JOIN ("
  28.                     + "SELECT COUNT(eec.events_id) as event_category_count , eec.events_id AS event_id "
  29.                     + "FROM event_eventcategory eec, eventcategory ec "
  30.                     + "WHERE eec.eventcategories_id = ec.id AND (1=0 ";
  31.                     for (String searchString : preparedWordList){
  32.                       query +=" OR LOWER(ec.title) LIKE LOWER( '%" + searchString + "%' )";
  33.                     }
  34.             query +=") GROUP BY eec.events_id ";
  35.             query +=") ec_count ON ec_count.event_id = e.id) ep, Event e2 "
  36.                     + "WHERE ep.PRIORITY > 0 AND ep.id=e2.id "
  37.                     + "ORDER BY ep.PRIORITY DESC ";
  38.  
  39.  
  40.             return em.createNativeQuery(query, Event.class).getResultList();
  41.         } catch (EJBException e) {
  42.             return new ArrayList<Event>();
  43.         }
  44.     }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement