Query pages with JPA

Asked

Viewed 317 times

2

I am changing the code of a query, because it will return many data when the database is well populated. I’m paging the query. The problem is that when they implemented it, they did it this way:

Takes a string, and all possible combinations are made with it, for example the phrase: "test 2 test 3 test 4", generates about 15 strings and it performs 15 selects. A query I know how to paginate, using

query.setFirstResult(inicioPagina);

query.setMaxResults(tamanhoDaPagina);

the problem arises when there are several queries.

------------------Solution------------------------------------------- I was able to solve the Aki :D problem

then the problem arose when I made the registration of more than 50mb in the database, and began to give some problems of memory on the server, the solution I created was to restore the Whole Object queries, return only the ID, and at the end I select only the page IDS and consult only the page

java sql jsf hibernate

  • Why does Voce make 15 selects? Can’t be a select, with 15 conditions in Where?

  • So the problem that selects are strings with the condition Like "%string%". I tried to query and by what I studied , is not possible seems

  • I don’t know which database you’re using, but it’s perfectly possible to do "and (field like '%string%' or field like '%string2%')", although I think a query like this would cause performance problems. In these cases, it is best to use some "full text" search solution such as Apache Lucene.

1 answer

1

An important issue is: there is a priority level between phrase combinations? For example: results that are exactly like the original string should come first.

If there is no prioritization of the similarity to the original sentence, for example, if the results are ordered by some other value such as a date or number, then instead of performing the N selects you could structure a query dynamics with the N clauses to perform only one query. That way the limitation of results would work smoothly.

Another alternative that would allow prioritizing certain combinations, is to use a query native with a N selects united with UNION.

Example:

SELECT ... FROM TABELA WHERE CAMPO LIKE '%frase 1%'
UNION
SELECT ... FROM TABELA WHERE CAMPO LIKE '%frase 2%'
UNION
SELECT ... FROM TABELA WHERE CAMPO LIKE '%frase 3%'
(...)

So we would first have the results of "phrase 1", after "phrase 2", etc.

Browser other questions tagged java sql jsf hibernate

You are not signed in. Login or sign up in order to post.