java我可以使用任意或数组作为Hibernate 4.3.11和H2 1.4.199中变量IN子句的替代品吗
使用Java8、Hibernate4.3.11和H21.4.199
我创建了一个由许多页面组成的html报告，我必须根据主键的IN调用使用where子句进行大量查询。如果in子句中的值的数量不同，则需要准备一个不同的已准备好的查询，不能重用现有的查询。因此，我试图减少对如此多不同的准备声明的需求
这是Hibernate 5中名为Hibernate的解决方案。查询在_子句中_参数_padding，如中所述 https://vladmihalcea.com/improve-statement-caching-efficiency-in-clause-parameter-padding/
然而，我不能移动到Hibernate 5，而且这个解决方案似乎已经被任何子句或数组的使用所取代。但我还没能让它工作
getSongDiffs1（）是我最初基于Hibernate的条件查询，getSongDiffs2（）被重写为使用原生sql进行查询，但没有性能优势，然后我尝试使用ANY forgetSongDiffs2（）和带有getSongDiffs（）的数组，但在准备语句时出现了错误
虽然我试图使用原生SQL，因为它是从Hibernate会话生成的，但我正在使用org。冬眠SQLQuery而不是连接因此无法在给定的PreparedStatement IN clause alternatives?完全遵循标记为Postgres solution的解决方案
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);
}
}
给出错误
原因：org。h2。jdbc。JdbcSQLSyntaxErrorException:SQL语句“SELECT*FROM SONGDIFF WHERE RECNO in？[*]）中的语法错误；应为“（”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 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);
}
}
这运行时没有错误，但我不知道，因为没有使用参数化的Hibernate调试会显示no？s，我明白了
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))
所以我假设这意味着它们是单独准备的语句，否定了这一点，那么有没有可能使用setParameter（）
使用最新的advice，任何和最新的变体似乎都可以发送有效的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);
}
}
但是现在提取结果集时出现了一个问题
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
所以我现在有了一种使用本机连接对象的方法，所以可以尝试一种标准的JDBC方法
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);
}
}
但这一点在某些情况下失败了
java。lang.AbstractMethodError:com。麦克亨格。v2。c3p0。impl。新建代理连接。createArrayOf（Ljava/lang/String；[Ljava/lang/Object；）Ljava/sql/Array
不确定这是否仅仅意味着c3p0不支持这一点（但h2支持）。但是无论如何，我无法将c3p0更新到最新版本，但是有没有其他方法来创建可以传递给setArray（）方法的数组
解决方案更新 通过使用this method检索jdbc连接和Evenijs答案中的建议，我现在有了以下工作方法
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);
}
}
共 (0) 个答案