1

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?

Cœur
  • 37,241
  • 25
  • 195
  • 267
saurzcode
  • 827
  • 12
  • 30

2 Answers2

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.

James Wang
  • 453
  • 4
  • 5
  • My usecase is like based on users filters for data , UI should create some select expression instead of sending multiple request params.For.e.g http://docs.amazonwebservices.com/AmazonSimpleDB/latest/DeveloperGuide/SDB_API_Select.html so there will be a select expression like `select field1,field2 from mydomain where field1 ='abc' and field2='cde' orderby field1 desc limit 10,20` so an expression like this will be passed from UI to java API. I want Java API to parse that select expression to get those parameters and form a dynamic SQL string to be passed to DB. What do u suggest? – saurzcode Jan 19 '12 at 10:58
  • Do you need to form such a dynamic sql: select field1,field2 from mydomain where field1 = '@p1' and field2= '@p2' orderby field1 desc limit 10,20 that replace literal with parameter, or something else? – James Wang Jan 20 '12 at 02:11
0

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.

JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
  • I am talking about something related to [jsqlparser](http://jsqlparser.sourceforge.net/home.php) and [zql](http://zql.sourceforge.net/).Any idea of any example implementation of those ? – saurzcode Jan 18 '12 at 11:28
  • Why do you want to parse SQL? Your question says that you want to filter data. You don't need a SQL parser for this? Store the data in a database, and filter it using a SQL query. A SQL parser is useful if you want to *implement* a database. You want to *use* a database. – JB Nizet Jan 18 '12 at 11:32