Dynamic queries in Hibernate

Question

In my project I'm using Hibernate and write now I'm struggling with one issue. In the project which I'm working on, I need to create something like dynamic queries. Let me present what is going on...

I have 8 different queries. The SELECT, FROM and first WHERE clause is the same for each query:

SELECT col1, col2, ..., colX FROM table WHERE col1 = :param1;

The point is that based on one parameter the rest of the query is different in each case. Cases below:

Simple query - just the base for the other queries.
One more WHERE condition: AND col2 = :param2.
The same like in previous one, but condition on different column: AND col3 = :param2.
More complex - JOIN clause, in the WHERE clause second condition again on different column, but also here we have UNION with exactly the same query like in point 3.
The same like query no. 4, but with different column in WHERE clause before UNION statement.
Same rule as in query 5.
Same rule as in query 5.
Here only JOIN and in WHERE clause are 3 conditions instead of 2.

I thought that's it, but I just remind that for all of these queries, we could apply dynamically extra WHERE conditions, but they need to appear in both SELECTs if there is a JOIN.

Does it even possible? Right now I solve the problem by passing correct columns names to the method with the StringBuilder and the if ... else statements, which takes care of UNION, JOIN and WHERE conditions.

I'm not proud of it, I don't like it, but I don't know how to resolve this problem better...

What's the extra parameter, on which the rest of the WHERE clauses depend? How do you handle it's value and can you show a code sample of what you did exactly, I think it will be more interesting to see it in the code. — g00dy
– g00dy, Commented Oct 31, 2017 at 9:25
One solution that I have in mind is to create a view, which will return different parts of it, based on the external parameter: create or replace view view_over_table As select 'external_param_value1' as ext_value, col1, col2 from table where condition1 union select 'external_param_value2' as ext_value, col1, col2 from table where condition1 and condition2;. Then you would do: SELECT * FROM view_over_table where ext_value = :external_param_that_determines_the_logic; - Would that do ? By doing this you could hide the complexity behind the view ... which seems ok to me. — g00dy
– g00dy, Commented Oct 31, 2017 at 9:29
It's just some value and depends on it, the correct query (one of these eight) should be created. The view is not correct, because I think that you didn't noticed that some queries could have JOIN or UNION or even both at the same time. — Lui
– Lui, Commented Oct 31, 2017 at 10:33
It's not a problem to create a view, which has different queries in it, combined by unions let's say, as long as they have the same columns / datatypes in their select statements. So the benefit of the view is that you hide the complexity behind it and you can easily get the proper query out of it, by a single parameter. The sample view I provided is indeed ... a sample one, but on top of it you can even have another view, which you select only '' as ext_value, col1 and col2 out of. See that's why I asked about the parameter that decides what is getting selected and the 8 queries also . — g00dy
– g00dy, Commented Oct 31, 2017 at 10:52
It's just a Java parameter. It's the parameter related with some kind of level. And depending on that level we decide how to create a query (if we need to use JOIN and UNION) and which columns should be in the WHERE condition. — Lui
– Lui, Commented Nov 7, 2017 at 11:38

Vitalii Muzalevskyi · Accepted Answer · 2017-10-30 21:26:40Z

1

I think Criteria API of Hibernate should help you with this. Some examples: http://www.baeldung.com/hibernate-criteria-queries

answered Oct 30, 2017 at 21:26

Vitalii Muzalevskyi

6728 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamic queries in Hibernate

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related