We have a query that needs to be built dynamically when the user chooses search params in our GUI.

I understand how to do this by hand in SQL by appending strings. While this does work there are a lot of edge cases in dynamically formatting SQL which are tedious and error prone.

I typically prefer to use Hibernate's criteria API for something like this because I can call add as many restrictions to the criteria as I need. For example...

    // Filter based on the customer no if required
    if (StringUtils.isNotBlank(customerNo)) {
        criteria.add(Restrictions.eq("customerNo", customerNo));
    // Filter based on the name if required
    if (StringUtils.isNotBlank(locationName)) {
        criteria.add(Restrictions.ilike("name", locationName, MatchMode.ANYWHERE));

The problem is some tables do not map well to Hibernate for various reasons. In order to use the Criteria API it appears I need my tables mapped nicely to entities.

Is there a technique/library anybody (in Java) is aware of to make dynamically building SQL less error prone than simple string smashing? I have seen Slick for Scala but nothing for Java.

I have wondered if hibernate exposes some of its low-level code in the Dialects to build SQL programmatically.

  • 1
    How about jOOQ ("jOOQ generates Java code from your database and lets you build type safe SQL queries through its fluent API.")? – usethe4ce Mar 15 '16 at 23:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.