Design pattern for constructing conditional SQL statement

Question

I have an issue.

I have SQL that I need to append different type of "restrictions" or even do a join. This depends on user's search criteria.

This SQL will involve different table as it can search one-to-many relationship, therefore hibernate ORM can't support my requirement.

May I know if there is a design pattern to help construct such SQL statements?

post your SQL query and add Hibernate tag to your question so we may help you with Criteria API usage — kothvandir, Sep 06 '12 at 08:20

score 0 · Answer 1 · answered Sep 05 '12 at 16:01

I exactly have a similar requirement where I have a context-free language to define the search criteria, parsed to ParseEntry objects in a ParseTree which are analogous to the Restrictions. I use a SQLQueryGeneratorVisitor to visit the parse table and generate the SQL query, similary a HibernateCriteriaGeneratorVisitor if the criteria needs to be generated for a single entity. So, I essentially used the Visitor pattern making the parse tree and the entries visitable so that different types of criteria can be generated (SQL/Hibernate or something else in future).

thanks for your reply but I am kind of confuse.. could you provide some example please? — seesee, Sep 06 '12 at 03:22

score 0 · Accepted Answer · answered Sep 06 '12 at 08:29

0

The design pattern that fits to the problem of representing a language statement is the Interpreter pattern. But before you start to code your SQL parser, take a look to ANTLR. And what is more important, ask yourself two questions:

Are the number of different SQL's justify the effort of develop a general SQL interpreter solutions instead of programming (just if-else statements) my 5-10 different queries?
Have I reviewed in detail the Hibernate reference manual?

answered Sep 06 '12 at 08:29

kothvandir

2,111
2
19
34

Have I reviewed in detail the Hibernate reference manual? -> Yes I spend 1 month on it and always supported Hibernate but sadly it just can't meet the need of large data set. Subqueries IN is very in efficient when it comes to million of data row. – seesee Sep 06 '12 at 09:44
Are the number of different SQL's justify the effort of develop a general SQL interpreter solutions instead of programming (just if-else statements) my 5-10 different queries? -> I don't want to interprete the SQL statements. I merely want to make it more "modular" when I wish to change. I hate a series of IF-ELSE statements. – seesee Sep 06 '12 at 09:46

Design pattern for constructing conditional SQL statement

2 Answers2