reset password
Author Message
talisunep
Posts: 140
Posted 23:10 Jun 04, 2013 |

professor to implement the fts queries in my controller  since the results have 3 entities as in rank/title/description will i need to use an object to store the results in contoller to be passed to my jsp?

cysun
Posts: 2935
Posted 23:25 Jun 04, 2013 |

You can use Review objects to hold the search results.

talisunep
Posts: 140
Posted 23:30 Jun 04, 2013 |

Professor

i am getting this error
 

SEVERE: Servlet.service() for servlet [bvvo] in context with path [/bvvo] threw exception [Request processing failed; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause
org.postgresql.util.PSQLException: ERROR: syntax error at or near "@@"
  Position: 175
 
im wondering if im doing my query right in the controller

String sql = "select ts_rank ( tsv, plainto_tsquery(:searchByReview)) as ranking ," +
"id," +
"ts_headline (title,plainto_tsquery(:searchByReview))," +
"ts_headline (review_description , plainto_tsquery(:searchByReview))" +
"from reviews" +
"where tsv @@ plainto_tsquery(:searchByReview)" +
"order by ranking desc";

 List <Review> searchedReviews = entityManager.createNativeQuery(sql, Review.class).setParameter("searchByReview", searchByReview).getResultList();

 

talisunep
Posts: 140
Posted 04:49 Jun 05, 2013 |

sorry my debugged my query had some syntax errors, its fixed now...
professor i am storing the results int he review object  by order and by relevant ranking and i am able to retrive the results in the jsp (title and description) however i am not able to retrive the other two coulmn form my sql  in order to show highlighted columns as these two rows hold the highlighted tags...

ts_headline (title,plainto_tsquery(:searchByReview)),
ts_headline (review_description , plainto_tsquery(:searchByReview)) 

is there any way i can retrive these in jsp ? i saved the review object as a list (searchedReviews)

Last edited by talisunep at 04:50 Jun 05, 2013.
cysun
Posts: 2935
Posted 07:23 Jun 05, 2013 |
talisunep wrote:

sorry my debugged my query had some syntax errors, its fixed now...
professor i am storing the results int he review object  by order and by relevant ranking and i am able to retrive the results in the jsp (title and description) however i am not able to retrive the other two coulmn form my sql  in order to show highlighted columns as these two rows hold the highlighted tags...

ts_headline (title,plainto_tsquery(:searchByReview)),
ts_headline (review_description , plainto_tsquery(:searchByReview)) 

is there any way i can retrive these in jsp ? i saved the review object as a list (searchedReviews)

In the JSP showing the search results, you don't really need the original review title or content, just the title and content with the search terms highlighted, so you can do something like:

select id, ts_headline(title, ...) as title, ts_headline(content, ...) as content from ...

This way the title and content fields of the Review objects will hold the highlighted title and content instead of the original ones.