Using Java 8, HIbernate 4.3.11 and H2 1.4.199
I create an html report made up of many pages and I have to make a larger number of queries simply with the where clause based on an IN call based on the primary key. If the number of values in the IN clause vary then a different prepared query needs to be prepared, existing ones cannot be reused. So I am trying to reduce the need for so many different prepared statements.
This is a solution in Hibernate 5 called hibernate.query.in_clause_parameter_padding as described at https://vladmihalcea.com/improve-statement-caching-efficiency-in-clause-parameter-padding/
However I cannot move to Hibernate 5, and it seems this solution has been superceded by use of ANY clause or ARRAYS anyway. But I have not yet been able to get this working
getSongDiffs1() is my initial Hibernate based Criteria query, getSongDiffs2() is query rewritten to use native sql works but has no performance advantage, I then tried using ANY for getSongDiffs2() and ARRAYS with getSongDiffs() but these gave errors when preparing statements
Although I am trying to use native SQL because generated from Hibernate session I am working with org.hibernate.SQLQuery rather than Connection so couldnt fully follow solution labelled Postgres solution at given PreparedStatement IN clause alternatives?
public static List<SongDiff> getSongDiffs1(StatelessSession session, List<Integer> ids)
{
try
{
Criteria c = session
.createCriteria(SongDiff.class)
.add(Restrictions.in("recNo", ids));
List<SongDiff> songDiffs = c.list();
return songDiffs;
}
catch (Exception e)
{
MainWindow.logger.log(Level.SEVERE, "Failed to get SongDiffsFromDb:" + e.getMessage(), e);
throw new RuntimeException(e);
}
}
public static List<SongDiff> getSongDiffs2(StatelessSession session, List<Integer> ids)
{
try
{
SQLQuery q = session.createSQLQuery("select * from SongDiff where recNo in :recNos");
q.addEntity(SongDiff.class);
q.setParameterList("recNos", ids);
List<SongDiff> songDiffs = q.list();
return songDiffs;
}
catch (Exception e)
{
MainWindow.logger.log(Level.SEVERE, "Failed to get SongDiffsFromDb:" + e.getMessage(), e);
throw new RuntimeException(e);
}
}
public static List<SongDiff> getSongDiffs3(StatelessSession session, List<Integer> ids)
{
try
{
SQLQuery q = session.createSQLQuery("select * from SongDiff where recNo = ANY(:recNos)");
q.addEntity(SongDiff.class);
q.setParameterList("recNos", ids);
List<SongDiff> songDiffs = q.list();
return songDiffs;
}
catch (Exception e)
{
MainWindow.logger.log(Level.SEVERE, "Failed to get SongDiffsFromDb:" + e.getMessage(), e);
throw new RuntimeException(e);
}
}
public static List<SongDiff> getSongDiffs(StatelessSession session, List<Integer> ids)
{
try
{
SQLQuery q = session.createSQLQuery("select * from SongDiff where recNo in :recNos)");
q.addEntity(SongDiff.class);
q.setParameter("recNos", ids.toArray());
List<SongDiff> songDiffs = q.list();
return songDiffs;
}
catch (Exception e)
{
MainWindow.logger.log(Level.SEVERE, "Failed to get SongDiffsFromDb:" + e.getMessage(), e);
throw new RuntimeException(e);
}
}
Gives error
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "SELECT * FROM SONGDIFF WHERE RECNO IN ?[*]) "; expected "("; SQL statement:
Based on answer and trial and error below I wrote this
public static List<SongDiff> getSongDiffs(StatelessSession session, List<Integer> ids)
{
try
{
StringBuilder sb = new StringBuilder();
for(Integer next:ids)
{
sb.append(next +",");
}
sb.setLength(sb.length() -1);
SQLQuery q = session.createSQLQuery("select * from SongDiff where recNo IN (SELECT * FROM UNNEST("+sb.toString()+"))");
q.addEntity(SongDiff.class);
List<SongDiff> songDiffs = q.list();
return songDiffs;
}
catch (Exception e)
{
MainWindow.logger.log(Level.SEVERE, "Failed to get SongDiffsFromDb:" + e.getMessage(), e);
throw new RuntimeException(e);
}
}
this runs without error, but I because not using parameterized the Hibernate debugging shows no ?s , i.e I get
08/10/2019 16.49.40:BST:DEBUG: select * from SongDiff where recNo IN (SELECT * FROM UNNEST(23,24,25,26,27,28,29,30,31,32,33,34,35))
08/10/2019 16.49.40:BST:DEBUG: select * from SongDiff where recNo IN (SELECT * FROM UNNEST(32,33,34,35,23,24,25,26,27,28,29,30,31))
So I assume this means they are seperate prepared statements which negates the point of this, so is it possible to get this working with setParamter()
Using the latest advise both ANY and UNNEST variations appear to send valid SQL
public static List<SongDiff> getSongDiffs(StatelessSession session, List<Integer> ids)
{
try
{
StringBuilder sb = new StringBuilder();
for(Integer next:ids)
{
sb.append(next +",");
}
sb.setLength(sb.length() -1);
SQLQuery q = session.createSQLQuery("select * from SongDiff where recNo = ANY(:recNos)");
q.addEntity(SongDiff.class);
q.setParameter("recNos", ids.toArray(new Integer[10]));
List<SongDiff> songDiffs = q.list();
return songDiffs;
}
catch (Exception e)
{
MainWindow.logger.log(Level.SEVERE, "Failed to get SongDiffsFromDb:" + e.getMessage(), e);
throw new RuntimeException(e);
}
}
but now there is a problem extracting the ResultSet
09/10/2019 08.02.14:BST:SongChangesCache:getSongDiffs:SEVERE: Failed to get SongDiffsFromDb:could not extract ResultSet
org.hibernate.exception.DataException: could not extract ResultSet
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:69)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:91)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2066)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1863)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839)
at org.hibernate.loader.Loader.doQuery(Loader.java:910)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355)
at org.hibernate.loader.Loader.doList(Loader.java:2554)
at org.hibernate.loader.Loader.doList(Loader.java:2540)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370)
at org.hibernate.loader.Loader.list(Loader.java:2365)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:353)
at org.hibernate.internal.StatelessSessionImpl.listCustomQuery(StatelessSessionImpl.java:741)
at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:311)
at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:141)
at com.jthink.songkong.db.SongChangesCache.getSongDiffs(SongChangesCache.java:407)
at com.jthink.songkong.reports.utils.HtmlAlbumArtistAlbumGridCreator.createAlbumFilesHtmlPage(HtmlAlbumArtistAlbumGridCreator.java:350)
at com.jthink.songkong.reports.utils.HtmlAlbumArtistAlbumGridCreator.createAlbumsHtmlPage(HtmlAlbumArtistAlbumGridCreator.java:282)
at com.jthink.songkong.reports.utils.HtmlAlbumArtistAlbumGridCreator.createArtistsHtmlPage(HtmlAlbumArtistAlbumGridCreator.java:198)
at com.jthink.songkong.reports.utils.HtmlAlbumArtistAlbumGridCreator.<init>(HtmlAlbumArtistAlbumGridCreator.java:75)
at com.jthink.songkong.reports.AbstractByMetadataSection.outputSection(AbstractByMetadataSection.java:306)
at com.jthink.songkong.reports.fixsongsreport.ByArtistAlbumSection.call(ByArtistAlbumSection.java:34)
at com.jthink.songkong.reports.fixsongsreport.ByArtistAlbumSection.call(ByArtistAlbumSection.java:19)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.h2.jdbc.JdbcSQLDataException: Data conversion error converting "X'aced0005757200145b4c6a6176612e6c616e672e496e74656765723bfe97ada00183e21b02000078700000000d737200116a6176612e6c616e672e496e746567657212e2a0a4f781873802000149000576616c7565787200106a6176612e6c616e672e4e756d62657286ac951d0b94e08b0200007870000000177371007e0002000000187371007e0002000000197371007e00020000001a7371007e00020000001b7371007e00020000001c7371007e00020000001d7371007e00020000001e7371007e00020000001f7371007e0002000000207371007e0002000000217371007e0002000000227371007e000200000023' (SONGDIFF: ""RECNO"" INTEGER NOT NULL)"; SQL statement:
select * from SongDiff where recNo = ANY(?) [22018-199]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:455)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:427)
at org.h2.message.DbException.get(DbException.java:194)
at org.h2.table.Column.convert(Column.java:204)
at org.h2.table.Column.convert(Column.java:185)
at org.h2.index.IndexCondition.getCurrentValueList(IndexCondition.java:152)
at org.h2.index.IndexCursor.prepare(IndexCursor.java:96)
at org.h2.index.IndexCursor.find(IndexCursor.java:154)
at org.h2.table.TableFilter.next(TableFilter.java:475)
at org.h2.command.dml.Select$LazyResultQueryFlat.fetchNextRow(Select.java:1882)
at org.h2.result.LazyResult.hasNext(LazyResult.java:101)
at org.h2.result.LazyResult.next(LazyResult.java:60)
at org.h2.command.dml.Select.queryFlat(Select.java:742)
at org.h2.command.dml.Select.queryWithoutCache(Select.java:884)
at org.h2.command.dml.Query.queryWithoutCacheLazyCheck(Query.java:151)
at org.h2.command.dml.Query.query(Query.java:435)
at org.h2.command.dml.Query.query(Query.java:397)
at org.h2.command.CommandContainer.query(CommandContainer.java:145)
at org.h2.command.Command.executeQuery(Command.java:202)
at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:115)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:116)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82)
... 25 more
So I now have a way to use a native Connection object so can try a standard JDBC way
public static List<SongDiff> getSongDiffs(StatelessSession session, List<Integer> ids)
{
try
{
Connection connection = HibernateUtil.getSqlSession();
PreparedStatement ps = connection.prepareStatement("select * from SongDiff where recNo = ANY(?)");
ps.setArray(1, connection.createArrayOf("Integer", ids.toArray(new Integer[10])));
ResultSet rs = ps.executeQuery();
while(rs.next())
{
MainWindow.logger.severe(rs.getString("recNo"));
}
return null;
}
catch (Exception e)
{
MainWindow.logger.log(Level.SEVERE, "Failed to get SongDiffsFromDb:" + e.getMessage(), e);
throw new RuntimeException(e);
}
}
but this fails with
java.lang.AbstractMethodError: com.mchange.v2.c3p0.impl.NewProxyConnection.createArrayOf(Ljava/lang/String;[Ljava/lang/Object;)Ljava/sql/Array;
Not sure if this simply means c3p0 doesnt support this (but h2 does). However regardless I cannot update c3p0 to latest version but is there a different way to create an array that can be passed to the setArray() method ?
Solution Update By using this method to retrieve a jdbc connection and the advice in Evenijs answer I now have the following working method.
public static List<SongDiff> getSongDiffs(List<Integer> ids)
{
Connection connection = null;
try
{
connection = HibernateUtil.getSqlSession();
PreparedStatement ps = connection.prepareStatement("select * from SongDiff where recNo = ANY(?)");
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);
}
}
