Oracle一次获取多个序列值

SQL 语句一次获取多个序列值

获取序列中的多个值

创建序列

CREATE SEQUENCE test_user_seq;

获取一个序列值

SELECT test_user_seq.nextval FROM dual;

有一个比较传统的方法构造多行数据，就是使用union all

SELECT 1 FROM DUAL
UNION ALL
SELECT 1 FROM DUAL

生成多行数据的方法有了，那么一次生成多个序列的方法也有了

SELECT test_user_seq.nextval 
FROM (
SELECT 1 FROM DUAL
UNION ALL
SELECT 1 FROM DUAL)

综上所述，一次获取多个序列值需要生成多行数据

connect by level 生成多行数据

union all 生成多行数据这个方法，性能比较差，当需要的序列比较多的时候，SQL 比较长，数据库解析SQL 耗时也多，而且还需要拼接sql, 使用
connect by level 生成多行数据就比较方便

select level from dual connect by level <= 5;

一次生成了5行数据，那么就可以用这个方式，获取5个序列值

SELECT test_user_seq.nextval FROM (
select level from dual connect by level <= 5
);

需要生成几个序列值，修改一下 level 的数量就可以，非常简洁

JDBC 一次获取多个序列值

安装 驱动jar 包到本地仓库

1. 查看Oracle 安装目录的驱动路径
   
2. maven 命令安装驱动jar到本地仓库

mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc8 -Dversion=19.0.0.0.0 -Dpackaging=jar -Dfile=H:\Oracle\19c\jdbc\lib\ojdbc8.jar

3. 获取多个序列值

import org.junit.Test;
import java.sql.*;
import java.util.Properties;

public class OracleJdbc {

    private static final String driver = "oracle.jdbc.driver.OracleDriver";

    private static final String url = "jdbc:oracle:thin:@localhost:1521/TEST";

    private static final String userName = "TEST_USER";

    private static final String password = "TEST_USER";

    static {
        // 加载驱动
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public Connection createConnection(String url, String userName, String password) {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url, userName, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    @Test
    public void testConnection() {
        Connection connection = createConnection(url, userName, password);
        try {
            Properties properties = connection.getClientInfo();
            System.out.println(properties);
            System.out.println(connection.getSchema());
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Test
    public void testTestBatchSequence() {
        try (Connection connection = createConnection(url, userName, password)) {
            String sql = "SELECT test_user_seq.nextval FROM (select level from dual connect by level <= ?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 5);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                System.out.println(resultSet.getInt(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

4. 运行 testTestBatchSequence，结果如下

48
49
50
51
52

MyBatis 一次获取多个序列值

1. 配置文件 mybatis-config-oracle.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!--

       Copyright 2009-2017 the original author or authors.

       Licensed under the Apache License, Version 2.0 (the "License");
       you may not use this file except in compliance with the License.
       You may obtain a copy of the License at

          http://www.apache.org/licenses/LICENSE-2.0

       Unless required by applicable law or agreed to in writing, software
       distributed under the License is distributed on an "AS IS" BASIS,
       WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
       See the License for the specific language governing permissions and
       limitations under the License.

-->
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>

    <!-- autoMappingBehavior should be set in each test case -->
    <properties resource="templates/oracle-db.properties"/>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC">
                <property name="" value=""/>
            </transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${driverClassName}"/>
                <property name="url" value="${jdbcUrl}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mapper/OracleBatchSeqMapper.xml"/>
    </mappers>

</configuration>

2. Mapper 配置文件 OracleBatchSeqMapper.xml

<?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.scd.mapper.OracleBatchSeqMapper">
    <select id="getBatchSeqId" resultType="java.lang.Long">
        SELECT test_user_seq.nextval FROM (select level from dual connect by level <![CDATA[ <= ]]> #{param})
    </select>
</mapper>

3. 数据库配置文件 oracle-db.properties

driverClassName=oracle.jdbc.driver.OracleDriver
jdbcUrl=jdbc:oracle:thin:@localhost:1521/TEST
username=TEST_USER
password=TEST_USER

4. 测试代码

import com.scd.mapper.OracleBatchSeqMapper;
import com.scd.mapper.TestUserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;

import java.io.Reader;
import java.util.List;

public class BatchSeqIdTest {

    private SqlSessionFactory sqlSessionFactory;

    @Before
    public void setUp() throws Exception {
        String resource = "templates/mybatis-config-oracle.xml";
        Reader reader = Resources.getResourceAsReader(resource);
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
    }

    @Test
    public void testBatch() {
        try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
            OracleBatchSeqMapper oracleBatchSeqMapper = sqlSession.getMapper(OracleBatchSeqMapper.class);
            List<Long> idList = oracleBatchSeqMapper.getBatchSeqId(5);
            System.out.println(idList);
            Assert.assertEquals(5, idList.size());
        }
    }
}

5. 执行 testBatch 测试方法，运行结果如下

19:54:46.840 [main] DEBUG org.apache.ibatis.logging.LogFactory - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
19:54:46.867 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
19:54:46.867 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
19:54:46.867 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
19:54:46.867 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
19:54:46.969 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Opening JDBC Connection
19:54:47.971 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - Created connection 1766505436.
19:54:47.971 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [oracle.jdbc.driver.T4CConnection@694abbdc]
19:54:47.975 [main] DEBUG com.scd.mapper.OracleBatchSeqMapper.getBatchSeqId - ==>  Preparing: SELECT test_user_seq.nextval FROM (select level from dual connect by level <= ?)
19:54:48.120 [main] DEBUG com.scd.mapper.OracleBatchSeqMapper.getBatchSeqId - ==> Parameters: 5(Integer)
19:54:48.209 [main] DEBUG com.scd.mapper.OracleBatchSeqMapper.getBatchSeqId - <==      Total: 5
[53, 54, 55, 56, 57]
19:54:48.210 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection [oracle.jdbc.driver.T4CConnection@694abbdc]
19:54:48.210 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Closing JDBC Connection [oracle.jdbc.driver.T4CConnection@694abbdc]
19:54:48.210 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - Returned connection 1766505436 to pool.