Usage of SQL parsers for java for search scenarios

Question

Can anyone suggest some good implementation examples or usage scenarios where SQL parsers can be used for java.

I have an application where we need to filter data to be presented on UI based on certain parameters, sort criteria etc. I have some doubts regarding this:

1)Can this be an ideal solution for this?

2)How can UI play an role for providing query to the Java layer?

Answer 1

Do you want to construct sql query dynamically in your java app and then fetch data using this sql? Let's say you have a sql like this:

select salary from emp where dept='sales'

after users pick up some other filters from UI, such as age > 40, then sql will be like this:

select salary from emp where dept='sales' and age > 40

Of course, you maybe want to add more complicated filters or add sort clause as well.

In order to achieve this, a full SQL Parser is helpful, here is a demo that illustrate how to Deconstruct, Modify, Rebuild a SQL statement based on a Java SQL Parser.

Answer 2

I don't know what a SQL parser is supposed to be. Applications don't have to parse SQL. Thay have to execute SQL queries, and thus potentially generate SQL queries dynamically. But it's the database that parses the SQL, not the application.

The UI doesn't typically provide a query to the service layer. The UI doesn't have to know how the data is persisted and which queries to execute. It's not its responsibility. The UI should just pass Filter or Criteria objects to the service layer, which transforms the filter or criteria into a SQL query, executes the query, transforms the results into Java objects, and return those objects to the UI, which displays these objects.