How to search for words in multiple tables

I have made an web application with some tables. Now, I want to search in these tables. Currently I have successfully made a class that encapsulates queries for each table such as getAllPersonsByFirstName... and so on. And then the user can select from checkboxes which tables he/she wants to include in the search. If multiple tables are selected I send the string (obtained from the inputfield) to each method then merge the results into a Set collection.

However, as I see it this have some limited use because if I want to paginate the results (5,10,100 on each page) I will have trouble knowing how many rows I should limit the search to. I am using JPA so I know I can set the offset and maximum, but when having multiple tables I can't see how this would work.

So what I thought of was making one big final query that is sent to the database, made upon smaller me开发者_JAVA技巧thods but I am not sure if this is the way to go. At least the offset-maximum in JPA would work.

Probably there is better ways to do so I would apprciate some help on how to achive pagination support. (Using JPA, EJB, JSF and binding result to a datatable)

---

here is some hints http://forums.mysql.com/read.php?107,36833,36833 if database is mysql.

you can use Sphinx if you huge amount of data

---

Since you are using EclipseLink I recommend you look into their Expression framework.

It allows you to do programmatic queries in a very sane way.

ExpressionBuilder emp = new ExpressionBuilder();
Expression exp = emp.get("address").get("street").equal("Meadowlands");
Vector employees = session.readAllObjects(Employee.class,
               exp.and(emp.get("salary").greaterThan(10000)));

You can combine several Expressions into a larger expression to query many tables at once. You can also specify pagination and query limits on the query expression as it's executed.