Dynamic query in MySQL

Question

I'm doing a J2EE web application with Struts2, MyBatis and a MySQL database, so what I want is to allow the user to be free to choose different parameters to perform a select to a table in the database.

For example, I have a Table called SALES, so I want that user to be able to select different parameters according to what he needs. Maybe, he wants to filter by dates and the employee name, and then he wants to filter just by customer and so on.

Should I show all the necessary data to filter on the client side with Javascript, or can I do a query to several tables after the user chooses the parameters? if that is possible, how should I write the query with parameters that could be present or not?

Answer 1

If your table contains small amount of data (and will always contain small amount) then of course you can filter in JavaScript. This is true, for example, if you always select from DB only one "page" of data (LIMIT 10,20). But in real application you want to LIMIT and FILTER the same time as user wants to see filtered results but only 10 of them at once. He doesn't want to load 10 results and then filter it to 3. So you will almost always use the server side filtering.

If you using Mybatis read about Dynamic SQL and The SQL Builder Class. You can read filter parameters from HTTP request and set values of variables to test by Mybatis Dynaimc SQL if method. Of course you first need to write that xml with if for any filter condition.

For example if you want to filter SALES by amount (>100) and name (contains "Goo") then you can pass from JS your filter options like this:

{
    "filterBy" : [
        { field: "amount", op: "gt" }
        { field: "name", op: "contains" }
    ]
}

In Java code you can read this JSON to Map (with Jackson for example) and assign values to variables for MyBatis Dynamic SQL expression.

I can't explain more as I am not expert of Mybatis.