java - 使用H2 SqlGrammarException hibernate setMaxResults()

Question

我正在尝试从给定特定用户和最大数量的数据库中获取一定数量的行。这是当前代码的样子：

@Override
public Collection<TestObject> findMaxByUser(User _user, int _max) { 
if (_max <= 0) {
  throw new IllegalArgumentException("max <= 0: " + _max);
}
Long rowCount = (Long)createCriteria()
    .add(Restrictions.eq("m_queryUser", _user))
    .setProjection(Projections.rowCount())
    .uniqueResult();

Collection<TestObject> results = createCriteria()
  .add(Restrictions.eq("m_queryUser", _user))
  .setFirstResult(1)
  .setMaxResults(_max)
  .list();
final int count = rowCount.intValue();
final int totalCount = count > results.size() 
    ? count : results.size();
return results;

这将导致 org.hibernate.SQLGrammarException: could not prepare statement错误。

奇怪的是，如果我将数据库实现更改为Derby，则代码可以正常工作。但是当我尝试使用 .setMaxResults()时，使用H2会发生一些愚蠢的事情。如果我使用 .setFetchSize()相反，它将毫无问题地返回所有结果，但是回到 .setMaxResults()会产生上述错误。

任何帮助/信息都很好。

导致的跟踪：

Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "SELECT THIS_.ID AS ID1_0_5_, THIS_.CREATED_ON AS CREATED2_0_5_, THIS_.UPDATED_ON AS UPDATED3_0_5_, THIS_.VERSION AS VERSION4_0_5_, THIS_.FAVORITE_QUERY AS FAVORITE6_0_5_, THIS_.NAME AS NAME5_0_5_, THIS_.USER_KEY AS USER7_0_5_, USERQUERY2_.ID AS ID1_2_0_, USERQUERY2_.CREATED_ON AS CREATED2_2_0_, USERQUERY2_.UPDATED_ON AS UPDATED3_2_0_, USERQUERY2_.VERSION AS VERSION4_2_0_, USERQUERY2_.MAX_RESULTS AS MAX5_2_0_, USERQUERY2_.PROPERTIES AS PROPERTI6_2_0_, USERQUERY2_.QUERY_STRING AS QUERY7_2_0_, USERQUERY2_.USER_KEY AS USER9_2_0_, USERQUERY2_.TARGETS AS TARGETS8_2_0_, EXTUSER3_.EXT_USER AS EXT1_4_1_, EXTUSER3_.CREATED_ON AS CREATED2_4_1_, EXTUSER3_.UPDATED_ON AS UPDATED3_4_1_, EXTUSER3_.VERSION AS VERSION4_4_1_, M_SORTS4_.USER_QUERIES AS USER1_2_7_, QUERYSORT5_.ID AS SORTS2_3_7_, QUERYSORT5_.ID AS ID1_1_2_, QUERYSORT5_.CREATED_ON AS CREATED2_1_2_, QUERYSORT5_.UPDATED_ON AS UPDATED3_1_2_, QUERYSORT5_.VERSION AS VERSION4_1_2_, QUERYSORT5_.ELEMENT_NAME AS ELEMENT5_1_2_, QUERYSORT5_.SORT_ORDER AS SORT6_1_2_, QUERYSORT5_.TYPE AS TYPE7_1_2_, QUERYSORT5_.USER_QUERY AS USER8_1_2_, USERQUERY6_.ID AS ID1_2_3_, USERQUERY6_.CREATED_ON AS CREATED2_2_3_, USERQUERY6_.UPDATED_ON AS UPDATED3_2_3_, USERQUERY6_.VERSION AS VERSION4_2_3_, USERQUERY6_.MAX_RESULTS AS MAX5_2_3_, USERQUERY6_.PROPERTIES AS PROPERTI6_2_3_, USERQUERY6_.QUERY_STRING AS QUERY7_2_3_, USERQUERY6_.USER_KEY AS USER9_2_3_, USERQUERY6_.TARGETS AS TARGETS8_2_3_, EXTUSER7_.EXT_USER AS EXT1_4_4_, EXTUSER7_.CREATED_ON AS CREATED2_4_4_, EXTUSER7_.UPDATED_ON AS UPDATED3_4_4_, EXTUSER7_.VERSION AS VERSION4_4_4_ FROM IES_TEST_SCHEMA.LES_FAVORITES THIS_ LEFT OUTER JOIN IES_TEST_SCHEMA.LES_USER_QUERIES USERQUERY2_ ON THIS_.FAVORITE_QUERY=USERQUERY2_.ID LEFT OUTER JOIN IES_TEST_SCHEMA.LES_USERS EXTUSER3_ ON USERQUERY2_.USER_KEY=EXTUSER3_.EXT_USER LEFT OUTER JOIN IES_TEST_SCHEMA.LES_USER_QUERIES_SORTS M_SORTS4_ ON USERQUERY2_.ID=M_SORTS4_.USER_QUERIES LEFT OUTER JOIN IES_TEST_SCHEMA.LES_QUERY_SORTS QUERYSORT5_ ON M_SORTS4_.SORTS=QUERYSORT5_.ID LEFT OUTER JOIN IES_TEST_SCHEMA.LES_USER_QUERIES USERQUERY6_ ON QUERYSORT5_.USER_QUERY=USERQUERY6_.ID INNER JOIN IES_TEST_SCHEMA.LES_USERS EXTUSER7_ ON THIS_.USER_KEY=EXTUSER7_.EXT_USER WHERE THIS_.USER_KEY=? OFFSET[*] 1 ROWS FETCH NEXT 2 ROWS ONLY "; SQL statement:
select this_.id as id1_0_5_, this_.created_on as created2_0_5_, this_.updated_on as updated3_0_5_, this_.version as version4_0_5_, this_.favorite_query as favorite6_0_5_, this_.name as name5_0_5_, this_.user_key as user7_0_5_, userquery2_.id as id1_2_0_, userquery2_.created_on as created2_2_0_, userquery2_.updated_on as updated3_2_0_, userquery2_.version as version4_2_0_, userquery2_.max_results as max5_2_0_, userquery2_.properties as properti6_2_0_, userquery2_.query_string as query7_2_0_, userquery2_.user_key as user9_2_0_, userquery2_.targets as targets8_2_0_, extuser3_.ext_user as ext1_4_1_, extuser3_.created_on as created2_4_1_, extuser3_.updated_on as updated3_4_1_, extuser3_.version as version4_4_1_, m_sorts4_.user_queries as user1_2_7_, querysort5_.id as sorts2_3_7_, querysort5_.id as id1_1_2_, querysort5_.created_on as created2_1_2_, querysort5_.updated_on as updated3_1_2_, querysort5_.version as version4_1_2_, querysort5_.element_name as element5_1_2_, querysort5_.sort_order as sort6_1_2_, querysort5_.type as type7_1_2_, querysort5_.user_query as user8_1_2_, userquery6_.id as id1_2_3_, userquery6_.created_on as created2_2_3_, userquery6_.updated_on as updated3_2_3_, userquery6_.version as version4_2_3_, userquery6_.max_results as max5_2_3_, userquery6_.properties as properti6_2_3_, userquery6_.query_string as query7_2_3_, userquery6_.user_key as user9_2_3_, userquery6_.targets as targets8_2_3_, extuser7_.ext_user as ext1_4_4_, extuser7_.created_on as created2_4_4_, extuser7_.updated_on as updated3_4_4_, extuser7_.version as version4_4_4_ from IES_TEST_SCHEMA.les_favorites this_ left outer join IES_TEST_SCHEMA.les_user_queries userquery2_ on this_.favorite_query=userquery2_.id left outer join IES_TEST_SCHEMA.les_users extuser3_ on userquery2_.user_key=extuser3_.ext_user left outer join IES_TEST_SCHEMA.les_user_queries_sorts m_sorts4_ on userquery2_.id=m_sorts4_.user_queries left outer join IES_TEST_SCHEMA.les_query_sorts querysort5_ on m_sorts4_.sorts=querysort5_.id left outer join IES_TEST_SCHEMA.les_user_queries userquery6_ on querysort5_.user_query=userquery6_.id inner join IES_TEST_SCHEMA.les_users extuser7_ on this_.user_key=extuser7_.ext_user where this_.user_key=? offset 1 rows fetch next 2 rows only [42000-171]

Answer 1

听起来您没有使用正确的方言...

仔细检查它的设置。

hibernate.dialect=org.hibernate.dialect.H2Dialect