EclipseLink生成的SQL不包含分页 [英] EclipseLink generated SQL doesn't include pagination
问题描述
(我的环境:Windows 7 x64和Server 2008，EclipseLink 2.5.2，我已经尝试使用JTDS和MS JDBC驱动程序以及MS SQL Server Express 2008和2012进行以下操作.)
(My environment: Windows 7 x64 and Server 2008, EclipseLink 2.5.2, I've tried the following with JTDS and the MS JDBC driver, and with MS SQL Server Express 2008 and 2012.)
我正在对一个表进行分页查询，该表的行数从5到5000万行不等，其中约500万行符合过滤条件.以下是我用来一次在用户界面25行中显示此数据的分页查询.分页工作正常-该列表每页仅包含25行.但是，查询需要24秒才能返回25行，这似乎很长.
I'm doing a paginated query in a table with anywhere from 5-50 million rows, with about 5 million rows matching the filter criteria. The following is the paginated query I'm using to display this data in the UI 25 rows at a time. The pagination is working properly - the list contains just 25 rows for each page. However, the query takes 24 seconds to return 25 rows, which seems long.
我的目标是记录生成的SQL，因此我可以确切地看到JPA如何完成SQL Server 2008 vs 2012中的分页.但是，生成的SQL不包含与分页有关的内容，这使我想知道还有什么其他问题我在生成的SQL中看不到.
My goal is to log the generated SQL, so I can see exactly how JPA is accomplishing the pagination in SQL Server 2008 vs 2012. But the generated SQL doesn't include anything to do with pagination, which makes me wonder what else I'm not seeing in the generated SQL.
查询:
CriteriaBuilder cb = JPA.em().getCriteriaBuilder();
CriteriaQuery<RGHICarrierPull> cq = cb.createQuery(RGHICarrierPull.class);
Root<RGHICarrierPull> from = cq.from(RGHICarrierPull.class);
CriteriaQuery<RGHICarrierPull> select = cq.select(from);
// Add filter/sort predicates to "predicates"
...
select.where(predicates);
// Apply pagination
records.setFirstResult((page-1)*limit);
records.setMaxResults(limit);
// Get data
List<RGHICarrierPull> lst = records.getResultList();
要以编程方式记录生成的SQL:
To log the generated SQL programatically:
// Log the sql for this query
Session session = JPA.em().unwrap(JpaEntityManager.class).getActiveSession();
DatabaseQuery databaseQuery = ((EJBQueryImpl)records).getDatabaseQuery();
databaseQuery.prepareCall(session, new DatabaseRecord());
System.out.println(databaseQuery.getSQLString());
记录的SQL:
SELECT t1.SHIPTO_ZIP, t1.WHSE, t1.ANYNBR1, t1.ANYTEXT1, t1.CREATE_DATE_TIME, t1.
MOD_DATE_TIME, t1.PULL_TIME, t1.PULL_TIME_AMPM, t1.PULL_TRLR_CODE, t1.USER_ID,
1.SHIP_VIA FROM Ship_Via t0, RGHI_Carrier_Pull t1 WHERE ((t1.WHSE = 'WHSE1') AND
(t0.SHIP_VIA = t1.SHIP_VIA)) ORDER BY t0.SHIP_VIA ASC, t1.SHIPTO_ZIP ASC
显然，这不是分页查询，所以如果我直接运行此查询，它将运行一分钟以上并返回所有500万行.如果使用persistence.xml设置记录所有JPA查询，以及从MS SQL Server记录SQL，我也会得到相同的结果.
Obviously, this is not a paginated query, so if I run this query directly, it runs for over a minute and returns all 5 million rows. I get the same results if I use persistence.xml settings to log all JPA queries, and also if I log the SQL from MS SQL Server.
这是实际生成的SQL吗?我看到两种可能性:
Is this the actual generated SQL? I see two possibilities:
- 这是完整生成的SQL，但是EclipseLink正在做其他事情来完成分页.
- EclipseLink在记录分页内容之前正在记录此生成的SQL
推荐答案
EclipseLink(至少在当前版本为2.6.1之前)不支持OFFSET-FETCH语法形式的SQL Server 2012分页，也不支持较旧的SQL Server TOP语法，请查看 EclipseLink数据库支持.相反，EclipseLink在内部使用JDBC功能 Statement.setMaxRows() 实质上从返回的ResultSet中丢弃多余的行.到目前为止，还没有计划在将来的版本中提供支持.
EclipseLink (at least up to the current version 2.6.1) supports neither SQL Server 2012 pagination in the form of OFFSET-FETCH syntax nor older SQL Server TOP syntax, check out EclipseLink Database Support. Instead EclipseLink internally uses JDBC feature Statement.setMaxRows() which essentially discards excessive rows from the returning ResultSet. And there is no plans to support that in the future versions so far.
您可以尝试通过扩展SQLServerPlatform和覆盖方法printSQLSelectStatement()来手动实现，类似于 https://github.com/roman-sinyakov /eclipselink/blob/master/SQLServer2012Platform.java .
You can try to implement this manually extending the SQLServerPlatform and overriding method printSQLSelectStatement() similarly to how it is done in PostgreSQLPlatform.printSQLSelectStatement(). The working prototype is here: https://github.com/roman-sinyakov/eclipselink/blob/master/SQLServer2012Platform.java.
这篇关于EclipseLink生成的SQL不包含分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
