Hibernate returns Method threw 'org.hibernate.exception.SQLGrammarException' exception. Could not extract ResultSet

Question

I have a piece of code

public List<Fruit> getFruits(final Set<Integer> ids) {
    final Criteria criteria = super.criteria().add(Restrictions.in("id", ids));
    final List<Fruit> fruits = this.list(criteria);  // throws SQLGrammarException
    fruits.forEach(this::initializeFruit);
    return fruits;
}

When empty set provided, it will throw exception in this.list(criteria), 'org.hibernate.exception.SQLGrammarException' exception. Could not extract ResultSet

Any suggestions how can I prevent?

Thanks!

Smutje · Accepted Answer · 2020-05-22 09:26:13Z

1

Basically this is how JPA is designed

There must be at least one element in the comma separated list that defines the set of values for the IN expression.

And the easiest way how to handle the erroneous situation you mentioned on having an empty list is to skip querying the database altogether because the condition is not supposed to match any entry anyway:

public List<Fruit> getFruits(final Set<Integer> ids) {
    final List<Fruit> fruits;

    if (ids.isEmpty()) {
        fruits = Collections.emptyList();
    } else {
        final Criteria criteria = super.criteria().add(Restrictions.in("id", ids));
        fruits = this.list(criteria);
        fruits.forEach(this::initializeFruit);
    }

    return fruits;
}

answered May 22, 2020 at 9:26

Smutje

18.2k4 gold badges26 silver badges42 bronze badges

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

4 Comments

M. Deinum Over a year ago

It isn't a JPA error but a SQL error, most SQL engines don't support an empty IN clause. Also isn't the condition is not supposed to match any entry an assumption? Maybe the OP wanted to return everything without limit.

Smutje Over a year ago

Of course it's a JPA thing, JPA could as well specify that its implementations have to follow the same fall-back as I did but they chose to design their API the way that they expect a non-empty set and let their consumers handle potential errors. JPA could have also specified that passing in an empty set results in e. g. an Exception but they didn't.

M. Deinum Over a year ago

And what would be a sensible default? Return nothing? Exclude the in? Put an empty null? What might be logical for you might not be for someone else who might expect the other way around. JPA only translate to SQL, sql does't allow an empty IN clause hence the SQL breaks. Just as it would if you would create an empty IN clause when writing plain SQL. But alas, that it probably a different discussion/topic all together.

Smutje Over a year ago

I know what my sensible default would be as I only include an "in" restriction when I want to restrict the results. Putting in a hard-coded restriction where I expect no restriction on certain conditions is exactly the obfuscated way people write code that takes up every readers time understanding all the unnecessary complexity instead of writing code that is easily readable by being concise and clear.

Collectives™ on Stack Overflow

Hibernate returns Method threw 'org.hibernate.exception.SQLGrammarException' exception. Could not extract ResultSet

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related