基於Java 利用Mybatis實現oracle批量插入及分頁查詢
1、單條數據insert
<!--簡單SQL--> insert into userinfo (USERID, USERNAME, AGE) values(1001,'小明',20); <!--Mybatis寫法1,有序列,主鍵是自增ID,主鍵是序列--> <insert id="insert" parameterType="com.zznode.modules.bean.UserInfo"> <selectKey resultType="java.lang.Integer" order="BEFORE" keyProperty="userid"> SELECT userinfo_userid_seq.nextval as userid from dual </selectKey> insert into EPG_ALARM_INFO (USERID, USERNAME, AGE) values (#{userid}, #{username}, #{age}) </insert> <!--Mybatis寫法2,無序列,主鍵是uuid,字符串--> <insert id="insert" parameterType="com.zznode.modules.bean.UserInfo"> insert into EPG_ALARM_INFO (USERID, USERNAME, AGE, TIME) values (#{userid}, #{username}, #{age}, sysdate) </insert>
2、批量數據批量insert
insert all into 的方式返回值由最後的select 決定:
<!--簡單SQL, 方法1--> INSERT ALL INTO userinfo (USERID, USERNAME, AGE) values(1001,'小明',20) INTO userinfo (USERID, USERNAME, AGE) values(1002,'小紅',18) INTO userinfo (USERID, USERNAME, AGE) values(1003,'張三',23) select 3 from dual; <!--簡單SQL, 方法2--> begin insert into userinfo (USERID, USERNAME, AGE) values(1001,'小明',20); insert into userinfo (USERID, USERNAME, AGE) values(1001,'小紅',18); insert into userinfo (USERID, USERNAME, AGE) values(1001,'張三',23); end; <!--簡單SQL, 方法3--> insert into userinfo (USERID, USERNAME, AGE) select 1001, '小明', 20 from dual union all select 1002, '小紅', 18 from dual union all select 1003, '張三', 23 from dual
<!--Mybatis寫法1,無序列--> <insert id="insertBatch" parameterType="java.util.List"> INSERT ALL <foreach collection="list" index="index" item="item"> INTO userinfo (USERID, USERNAME, AGE) VALUES (#{item.userid}, #{item.username}, #{item.age}) </foreach> select list.size from dual </insert> <!--Mybatis寫法2,無序列--> <insert id="insertBatch"> insert into EPG_ALARM_INFO (USERID, USERNAME, AGE) <foreach collection="list" item="item" index="index" separator="union all"> <!-- <foreach collection="list" item="item" index="index" separator="union all" open="(" close=")"> --> <!-- (select #{item.userid}, #{item.username}, #{item.age} from dual) --> <!-- 上面帶括號,下面不帶括號,都可以,少量數據不帶括號效率高 --> select #{item.userid}, #{item.username}, #{item.age} from dual </foreach> </insert> <!--Mybatis寫法3,有序列--> <insert id="insertBatch"> insert into EPG_ALARM_INFO (USERID, USERNAME, AGE) SELECT userinfo_userid_seq.nextval, m.* FROM ( <foreach collection="list" item="item" index="index" separator="union all"> select #{item.username}, #{item.age} from dual </foreach> ) m </insert>
3、創建序列
- minvalue n (/nominvalue):最小值為n
- maxvalue n (/nomaxvalue):最大值為n
- start with n:從n開始計數
- increment by n:每次增加n
- cache n (/nocache):緩存n個sequence值 / 不緩存,如果緩存,則會有跳號的危險
- noorder (/order):不保證序列號按順序生成請求
- cycle n (/nocycle):如果到達最大值n後,再次從start with n開始
- currval:序列的當前值,新序列必須使用一次nextval 才能獲取到值,否則會報錯
- nextval:表示序列的下一個值。新序列首次使用時獲取的是該序列的初始值,從第二次使用時開始按照設置的步進遞增
刪除序列語法: drop sequence seq_表名
<!-- create sequence 序列名 increment by 1 --每次增加幾個,我這裡是每次增加1 start with 1 --從1開始計數 nomaxvalue --不設置最大值 nocycle --一直累加,不循環 nocache; --不建緩沖區 在插入語句中調用:序列名.nextval 生成自增主鍵。 --> <!--創建序列--> create sequence SEQ_USERINFO minvalue 1 maxvalue 9999999999 start with 1 increment by 1 nocache; <!--刪除序列--> drop sequence SEQ_USERINFO
4、oracle分頁查詢
前端與後端交互,分頁查詢
service業務實現:
public List<TBadUserW> queryPageBadUserInfo(TbadUserQuery queryModel) { log.info("分頁查詢請求參數,{}", JSON.toJSONString(queryModel)); int pageNum = queryModel.getPageNum(); // 開始頁 int pageSize = queryModel.getPageSize(); // 每頁數量 queryModel.setStart((pageNum - 1) * pageSize); // 開始行數 (+1後) queryModel.setEnd(pageNum * pageSize); // 結束行數 List<TBadUserW> beans = badUserWDao.queryPageBadUserInfo(queryModel); log.info("最終查詢數量:", beans.size()); return beans; }
mapper.xml文件:
<select id="queryPageInfo" parameterType="com.zznode.test.bean.TbadUserQuery" resultMap="BaseResultMap" > SELECT tt.* FROM ( <!--前端分頁需要 total總記錄--> SELECT t.*, ROWNUM rown, COUNT (*) OVER () total FROM ( select <include refid="Base_Column_List"/> from T_BAD_USER_W <where> <if test="city != null and city !=''"> and city = #{city} </if> <if test="county != null and county != ''"> and county = #{county} </if> <if test="startTime != null and startTime !=''"> and loadtime >= to_date(#{startTime} , 'yyyy-mm-dd hh24:mi:ss') </if> <if test="endTime != null and endTime !=''"> and loadtime <![CDATA[<=]]> to_date(#{endTime} , 'yyyy-mm-dd hh24:mi:ss') </if> </where> )t )tt where tt.rown > #{start} and tt.rown <![CDATA[<=]]> #{end} </select>
後端海量數據導出,批量查詢
service業務實現:
public List<TBadUserW> queryPageBadUserInfo(TbadUserQuery queryModel) { log.info("分頁查詢請求參數,{}", JSON.toJSONString(queryModel)); List<TBadUserW> result = new ArrayList<>(); int pageNum = queryModel.getPageNum(); // 開始頁 int pageSize = queryModel.getPageSize(); // 每頁數量(可以每頁設置為200/500/1000),每次查詢的條數 boolean searchAll = true; while (searchAll){ queryModel.setStart((pageNum - 1) * pageSize); // 開始行數 (+1後) queryModel.setEnd(pageNum * pageSize); // 結束行數 List<TBadUserW> beans = badUserWDao.queryPageBadUserInfo(queryModel); if (null == beans || beans.size() < pageSize) { searchAll = false; } if (CollectionUtils.isNotEmpty(beans)) { result.addAll(beans); } pageNum++; } log.info("最終查詢數量:", result.size()); return result; }
mapper.xml文件編寫
<!--這種寫法是比較高效的分批查詢方法,分批不需要查詢total總量,不支持total--> <select id="queryPageInfo" parameterType="com.zznode.test.bean.TbadUserQuery" resultMap="BaseResultMap" > SELECT tt.* FROM ( SELECT t.*, ROWNUM rown FROM ( select <include refid="Base_Column_List"/> from T_BAD_USER_W <where> <if test="city != null and city !=''"> and city = #{city} </if> <if test="county != null and county != ''"> and county = #{county} </if> <if test="startTime != null and startTime !=''"> and loadtime >= to_date(#{startTime} , 'yyyy-mm-dd hh24:mi:ss') </if> <if test="endTime != null and endTime !=''"> and loadtime <![CDATA[<=]]> to_date(#{endTime} , 'yyyy-mm-dd hh24:mi:ss') </if> </where> )t where ROWNUM <![CDATA[<=]]> #{end} )tt where tt.rown > #{start} </select>
到此這篇關於基於Java 利用Mybatis實現oracle批量插入及分頁查詢的文章就介紹到這瞭,更多相關Mybatis實現oracle批量插入 內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
推薦閱讀:
- MySQL和Oracle批量插入SQL的通用寫法示例
- MyBatis批量插入大量數據(1w以上)
- Mybatis詳解動態SQL以及單表多表查詢的應用
- mybatis使用oracle進行添加數據的方法
- Mybatis動態SQL foreach批量操作方法