您好,登錄后才能下訂單哦!
博主說(shuō):有時(shí)候,我們需要對(duì)數(shù)據(jù)庫(kù)中現(xiàn)有的數(shù)據(jù)進(jìn)行大量處理操作(例如表中的某個(gè)字段需要全部更新等),如果直接使用select * from tableName很容易出現(xiàn)問(wèn)題,因此我們可以選擇分頁(yè)查詢,批量處理數(shù)據(jù)。
DB2
SQL 語(yǔ)句
SELECT * FROM ( SELECT B.*, ROWNUMBER() OVER() AS TN FROM ( SELECT * FROM 表名 ) AS B ) AS A WHERE A.TN BETWEEN startNum AND endNum;
如上所示,此即為 DB2 的分頁(yè)查詢語(yǔ)句。
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" > <mapper namespace="com.hit.store.dao.StoreEntityDao" > <resultMap id="BaseResultMap" type="StoreEntity" > <id column="ID" property="id" jdbcType="BIGINT" /> <result column="CREATE_TIME" property="createTime" jdbcType="TIMESTAMP" /> <result column="OWNER" property="owner" jdbcType="VARCHAR" /> <result column="DESCRIPTION" property="description" jdbcType="VARCHAR" /> </resultMap> <select id="query4encrypt" parameterType="Map" resultMap="BaseResultMap"> <!--- 在映射文件中 SQL 語(yǔ)句末尾不應(yīng)該加分號(hào),防止解析錯(cuò)誤 ---> 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>
Java
/** * Dao 層代碼 */ @Repository("storeEntityDao") public interface StoreEntityDao { List<StoreEntity> query4encrypt(Map<String, Object> paramMap); } /** * Service 層接口代碼 */ public interface StoreEntityService { public void query4encrypt(); } /** * Service 層實(shí)現(xiàn)代碼 */ @Service("storeEntityService") public interface StoreEntityServiceImpl implements StoreEntityService { @Override public void query4encrypt() { boolean flag = true; Long startNum = 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) { // 遍歷加密數(shù)據(jù) for (StoreEntity storeEntity : storeEntityList) { // 加密及持久化處理 } } if (storeEntityList != null && storeEntityList.size() >= 100) { startNum = endNum++; } else { flag = false; } } } }
至此,我們模擬了數(shù)據(jù)庫(kù)映射 Mapper.xml 文件、Dao 層和 Service 層,并在 Mapper.xml 中書(shū)寫(xiě)了分頁(yè)查詢 SQL 語(yǔ)句。特別地,在 Service 的實(shí)現(xiàn)層中,我們實(shí)現(xiàn)了具體的分頁(yè)查詢操作,并在其中批量處理數(shù)據(jù)。
以上就是本文的全部?jī)?nèi)容,希望對(duì)大家的學(xué)習(xí)有所幫助,也希望大家多多支持億速云。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。