Detailed DB2 paging query and Java implementation

DB2

• Startnum: Start number

• Endnum: End Number

  SQL statement ?

SELECT * FROM (SELECT B.*, ROWNUMBER() OVER() AS TN FROM (SELECT * FROM 表名) AS B) AS A WHERE A.TN BETWEEN startNum AND endNum; 

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8

As shown above, this is a paged query statement for DB2.

Mapper

<?xml version= "1.0" encoding= "UTF-8"?><! DOCTYPE Mapper Public "-//mybatis.org//dtd mapper 3.0//en" "Http://mybatis.org/dtd/mybatis-3-mapper.dtd" ><MapperNamespace="Com.hit.store.dao.StoreEntityDao" ><ResultmapId="Baseresultmap"Type="Storeentity" ><Idcolumn="ID"property="id"Jdbctype="BIGINT"/><Resultcolumn="Create_time"property="Createtime"Jdbctype="TIMESTAMP"/><Resultcolumn="OWNER"property="Owner"Jdbctype="VARCHAR"/><Resultcolumn="DESCRIPTION"property= "description"  Jdbctype= "VARCHAR"/> </resultMap > <select id=  "Query4encrypt" parametertype= " Map "resultmap=" Baseresultmap "> <!---the end of the SQL statement in the mapping file should not be appended with a semicolon to prevent parsing errors---> select * FROM (select b.*, ROWNUMBER () over () as TN from (SELECT * from Tbl_store) as B) as A WHERE a.tn between #{startnum} and #{endnum} </ select></MAPPER>   

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21st
• 22
• 23

Java

/** * DAO Layer Code */@Repository ("Storeentitydao")PublicInterfaceStoreentitydao {list<storeentity> query4encrypt (map<string, object> parammap);}/** * Service Layer Interface Code */PublicInterfaceStoreentityservice {PublicvoidQuery4encrypt ();}/** * Service Layer Implementation Code */@Service ("Storeentityservice")PublicInterfaceStoreentityserviceimplImplementsStoreentityservice {@OverridePublicvoidQuery4encrypt () {Boolean flag =True Long sta Baccarat Tips rtnum =0L; Long Endnum =0L; map<string, object> parammap =New hashmap<string, object> ();while (flag) {Endnum = Startnum + 100; Parammap.put (" Startnum ", startnum); Parammap.put ( "Endnum", endnum); list<storeentity> storeentitylist = Storeentitydao.query4encrypt (Parammap); if (storeentitylist! = null && storeentitylist.size () > 0) {//traverse encrypted data for (StoreEntity storeentity:storeentitylist) {//Encryption and Persistence processing}} if ( Storeentitylist! = null && storeentitylist.size () >= 100) {startnum = endnum++;} else {flag = false;}}}      

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21st
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45

At this point, we simulated the database mapping Mapper.xml file, Dao layer, and Service layer, and wrote a paged query SQL statement in Mapper.xml. In particular, in the Service implementation layer, we implement specific paging query operations, and in which the data is processed in batches.

Read the full text

Detailed DB2 paging query and Java implementation