How to use Hibernate Mapping when working with a huge data table

Question

Definition of the problem:
I have a database table with a huge amount of data (more than 100,000 rows), the structure of the table is similar to

AppID  DocID  DocStatus 
1      100    0
1      101    1    
2      200    0    
2      300    1

There can be thousands of documents for an application identifier, I need to get the number of documents with status 0 and the number of documents with status 1, grouped by application identifier.

When I map this object using sleep mode, it will consume a lot of heap memory due to the large amount of table data.

How can I achieve this using a Hibernate request? OR Should I use a SQL query or stored procedure to do this?

. - JAVA/Tapestry Hibernate 3. - SQL Server 2012.

Answer 1

, , ( Java-), SQL . , Java-, , . . "2. " .

JDBC SQL , MyBatis jOOQ.

:

GROUP BY

SELECT [AppID], [DocStatus], count(*)
FROM [MyTable]
GROUP BY [AppID], [DocStatus]

SQLFiddle

SELECT [AppID],
       (SELECT count(*) FROM [MyTable] [t2]
        WHERE [t1].[AppID] = [t2].[AppID]
        AND [DocStatus] = 0) [Status_0],
       (SELECT count(*) FROM [MyTable] [t2]
        WHERE [t1].[AppID] = [t2].[AppID]
        AND [DocStatus] = 1) [Status_1]
FROM [MyTable] [t1]
GROUP BY [AppID]

SQLFiddle

SUM()

SELECT [AppID],
       SUM(IIF([DocStatus] = 0, 1, 0)) [Status_0],
       SUM(IIF([DocStatus] = 1, 1, 0)) [Status_1]
FROM [MyTable] [t1]
GROUP BY [AppID]

SQLFiddle

PIVOT

SELECT [AppID], [0], [1]
FROM (
  SELECT [AppID], [DocStatus]
  FROM [MyTable]
) [t]
PIVOT (count([DocStatus]) FOR [DocStatus] IN ([0], [1])) [pvt]

SQLFiddle