How to use Mybatis to implement Oracle batch insertion and paging query based on Java

1. Single data insert

<!--简单SQL-->
insert into userinfo (USERID, USERNAME, AGE) values(1001,&#39;小明&#39;,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. Batch data batch insert

The return value of insert all into is determined by the final select:

<!--简单SQL, 方法1-->
INSERT ALL 
INTO userinfo (USERID, USERNAME, AGE) values(1001,&#39;小明&#39;,20)
INTO userinfo (USERID, USERNAME, AGE) values(1002,&#39;小红&#39;,18)
INTO userinfo (USERID, USERNAME, AGE) values(1003,&#39;张三&#39;,23)
select 3 from dual;
<!--简单SQL, 方法2-->
begin
    insert into userinfo (USERID, USERNAME, AGE) values(1001,&#39;小明&#39;,20);
    insert into userinfo (USERID, USERNAME, AGE) values(1001,&#39;小红&#39;,18);
    insert into userinfo (USERID, USERNAME, AGE) values(1001,&#39;张三&#39;,23);
end;
<!--简单SQL, 方法3-->
insert into userinfo (USERID, USERNAME, AGE) 
select 1001, &#39;小明&#39;, 20 from dual union all
select 1002, &#39;小红&#39;, 18 from dual union all
select 1003, &#39;张三&#39;, 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. Create a sequence

• minvalue n (/nominvalue): the minimum value is n

• maxvalue n (/nomaxvalue): the maximum The value is n

• start with n: start counting from n

• increment by n: increase n

# each time
• ##cache n (/nocache): Cache n sequence values ​​/ Do not cache. If cached, there will be a risk of number hopping

• noorder (/order): No Ensure that the sequence numbers are generated in order

• cycle n (/nocycle): If the maximum value n is reached, start again from start with n

• currval: The current value of the sequence. A new sequence must use nextval once to obtain the value, otherwise an error will be reported.

• nextval: Indicates the next value of the sequence. When a new sequence is used for the first time, the initial value of the sequence is obtained. From the second time it is used, it increases according to the set step

Delete sequence syntax: drop sequence seq_table name

<!--
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 paging query

Front-end and back-end interaction, paging query

service business implementation:

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 file:

<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 !=&#39;&#39;">
                    and city = #{city}
                </if>
                <if test="county != null and county != &#39;&#39;">
                    and county = #{county}
                </if>
                <if test="startTime != null and startTime !=&#39;&#39;">
                    and loadtime >= to_date(#{startTime} , &#39;yyyy-mm-dd hh34:mi:ss&#39;)
                </if>
                <if test="endTime != null and endTime !=&#39;&#39;">
                    and loadtime <![CDATA[<=]]> to_date(#{endTime} , &#39;yyyy-mm-dd hh34:mi:ss&#39;)
                </if>
            </where>
        )t
    )tt
    where tt.rown > #{start} and tt.rown <![CDATA[<=]]> #{end}
</select>

Backend massive data export, batch query

service business implementation:

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 file writing

<!--这种写法是比较高效的分批查询方法，分批不需要查询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 !=&#39;&#39;">
                    and city = #{city}
                </if>
                <if test="county != null and county != &#39;&#39;">
                    and county = #{county}
                </if>
                <if test="startTime != null and startTime !=&#39;&#39;">
                    and loadtime >= to_date(#{startTime} , &#39;yyyy-mm-dd hh34:mi:ss&#39;)
                </if>
                <if test="endTime != null and endTime !=&#39;&#39;">
                    and loadtime <![CDATA[<=]]> to_date(#{endTime} , &#39;yyyy-mm-dd hh34:mi:ss&#39;)
                </if>
            </where>
        )t where ROWNUM <![CDATA[<=]]> #{end}
    )tt
    where tt.rown > #{start}
</select>

The above is the detailed content of How to use Mybatis to implement Oracle batch insertion and paging query based on Java. For more information, please follow other related articles on the PHP Chinese website!