Rewrite Hibernate Criteria with IN clause so can reuse same PreparedStatement with different number of IN clauses

Question

I use the following Hibernate query alot when retrieving multiple records by their primary key

       Criteria c = session
                .createCriteria(Song.class)
                .setLockMode(LockMode.NONE)
                .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
                .add(Restrictions.in("recNo", ids));
        List<Song> songs = c.list();

The problem is the number of ids can vary from 1 - 50, and every different number of ids requires a different PreparedStatement. That, combined with the fact that any particular prepared statement is tied to a particular database pool connection means that the opportunity to reuse a PreparedStatement is quite low.

Is there way I can rewrite this so that the same statement can be used with different number of in values, I think I read somewhere it could be done by using ANY instead but cannot find the reference.

Possible duplicate of [PreparedStatement IN clause alternatives?](https://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives) — Simon Martinelli, Jun 26 '19 at 12:28
@SimonMartinelli useful link but no mentin of hibernate in that question or answers so not duplicate — Paul Taylor, Jun 26 '19 at 12:36
It doesn't matter if you are using Hibernate or not. Prepared statements are SQL statements. And Hibernate generates these. So you have to look at the solution and write a Hibernate query that generates these SQL statements — Simon Martinelli, Jun 26 '19 at 12:37
@SimonMartinelli That is the question Im asking, how to create a Hibernate query, and that is not answerd in the linked question. — Paul Taylor, Jun 26 '19 at 12:41

score 2 · Answer 1 · answered Jun 26 '19 at 12:51

2

This is called "in clause parameter padding" and can be activated with a hibernate property:

<property
    name="hibernate.query.in_clause_parameter_padding"
    value="true"
</property>

Read more about this topic here: https://vladmihalcea.com/improve-statement-caching-efficiency-in-clause-parameter-padding/

answered Jun 26 '19 at 12:51

Simon Martinelli

34,053
5
48
82

thankyou that is a good answer, unfortunately I'm currenlty on Hibernate 4 so it not available but I do want to update to Hibernate 5. However is there also possible solution using ANY somehow do I can do in Hibernate 4. – Paul Taylor Jun 26 '19 at 12:54
You will have to provide a variety of statements 1-50 in clauses – Simon Martinelli Jun 26 '19 at 13:06

score 0 · Answer 2 · answered Oct 28 '19 at 15:12

With some help I ended up getting a usual SQL connection from Hibernate, and then using standard SQL with ANY instead of IN. As far as I know using ANY means we only need a single prepared statement per connection so is better then using padded IN's. But because just using SQL not much use if you need to modify the data returned

 public static List<SongDiff> getReadOnlySongDiffs(List<Integer> ids)
    {
        Connection connection = null;
        try
        {
            connection = HibernateUtil.getSqlSession();
            String SONGDIFFSELECT = "select * from SongDiff where recNo = ANY(?)";
            PreparedStatement ps = connection.prepareStatement(SONGDIFFSELECT);

            ps.setObject(1,  ids.toArray(new Integer[ids.size()]));
            ResultSet rs = ps.executeQuery();
            List<SongDiff> songDiffs = new ArrayList<>(ids.size());
            while(rs.next())
            {
                SongDiff sd = new SongDiff();
                sd.setRecNo(rs.getInt("recNo"));
                sd.setDiff(rs.getBytes("diff"));
                songDiffs.add(sd);
            }
            return songDiffs;
        }
        catch (Exception e)
        {
            MainWindow.logger.log(Level.SEVERE, "Failed to get SongDiffsFromDb:" + e.getMessage(), e);
            throw new RuntimeException(e);
        }
        finally
        {
            SessionUtil.close(connection);
        }
    }

 public static Connection getSqlSession() throws SQLException {
        if (factory == null || factory.isClosed()) {
            createFactory();
        }

        return ((C3P0ConnectionProvider)factory.getSessionFactoryOptions().getServiceRegistry().getService(C3P0ConnectionProvider.class)).getConnection();
    }

score 0 · Answer 3 · answered Apr 22 '21 at 09:01

If you're still on an old version of Hibernate as suggested in the comments to Simon's answer here, as a workaround, you could use jOOQ's ParsingConnection to transform your SQL by applying the IN list padding feature transparently behind the scenes. You can just wrap your DataSource like this:

// Input DataSource ds1:
DSLContext ctx = DSL.using(ds1, dialect);
ctx.settings().setInListPadding(true);

// Use this DataSource for your code, instead:
DataSource ds2 = ctx.parsingDataSource();

I've written up a blog post to explain this more in detail here.

(Disclaimer: I work for the company behind jOOQ)

Rewrite Hibernate Criteria with IN clause so can reuse same PreparedStatement with different number of IN clauses

3 Answers3