I want to understand how I can write dynamic SQL calls, which can be used to retrieve rows, based on an end user's search criteria. I am in particular wondering about how to structure the data sent from the client side to the server. I'm going to keep this question limited to a single table search.
Normally when I have sent search criteria from the client side, it has been simple predetermined data. For instance get Item by ID, or get items with timestamp larger than a given time. These parameters can easily be appended to a preparedstatement. Now however, I need to let the user select which ever search parameters he or her desire, for searching a single table There are a lot of ways to search for data. Here are a few examples that I can think of:
- number, string, date etc. equals. Specific ID's, registrations etc.
- number, string date etc in range from x to y. Finding people in an age group, or reservations for a period.
- number, string, date in lists. Finding people who is a citizen of one of the NATO countries.
In other words, for each parameter that I send, I also need to send metadata of which column the parameters belong to, and how the parameters should be used. I have so far sent a fixed set of search parameters as key value pairs, where the server side would know which data type, and which validation type it should be interpreted as. Now i need to send objects which also contains that kind of info, because the user can decide themselves how many columns to send parameters for.
So for each query, besides which table and which columns to return, I would need to know the following for each query criteria:
- Which column to check
- What type of check to perform (equals, like, between, greaterThan, In list etc.)
- The above also needs to correspond to the parameter data structure (equals has one single value, between/range should have two, while list has 1-*)
- The data type of the parameter/column (?)
- Possibly sorting criteria as well.
Now I could send this as pure JSON, and create my own interpreter server side, who can generate either SQL, JPQL or a criteriaQuery using an ORM. The last one is my likely choice. I do however have a feeling that there has to be a standard solution for sending dynamic SQL query data like this, from the client side, to the server side. Just consider for instance a dating site, where users can search for possible partners, based on a myriad of possible options. There are so many applications for this, I would be surprised if there is no standard solution for this.
