-- 使用mybatis时，下面的做法当一次插入数量太多时会报错（ORA-00913: too many values），这时需要用batch insert
-- 1 Use a SELECT Query
INSERT INTO Products (ProductId, ProductName, Price) 
    WITH p AS ( 
        SELECT 1, 'Left Handed Screwdriver', 10.50 FROM dual UNION ALL 
        SELECT 2, 'Right Handed Screwdriver', 22.75 FROM dual UNION ALL 
        SELECT 3, 'Bottomless Coffee Cup (4 pack)', 15.00 FROM dual UNION ALL 
        SELECT 4, 'Urban Dictionary Version 2.3', 75 FROM dual UNION ALL
        SELECT 5, 'Beer Water', 15 FROM dual
  )
SELECT * FROM p;
-- 2 Use INSERT ALL
INSERT ALL 
    INTO Products ( ProductId, ProductName, Price ) VALUES ( 1, 'Left Handed Screwdriver', 10.50 )
    INTO Products ( ProductId, ProductName, Price ) VALUES ( 2, 'Right Handed Screwdriver', 22.75 )
    INTO Products ( ProductId, ProductName, Price ) VALUES ( 3, 'Bottomless Coffee Cup (4 pack)', 15.00 )
    INTO Products ( ProductId, ProductName, Price ) VALUES ( 4, 'Urban Dictionary Version 2.3', 75 )
    INTO Products ( ProductId, ProductName, Price ) VALUES ( 5, 'Beer Water', 15 )
SELECT 1 FROM dual;

    @Test
    @Transactional(readOnly = true)
    void testBatch() {
        int batchSize = 10;
        // openSession默认关闭自动提交，不需再加false参数
        try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
            CountriesMapper cmapper = sqlSession.getMapper(CountriesMapper.class);
            IntStream.range(1, 55).forEach(item -> {
                cmapper.insert(new Countries("" + item, "230211-" + item + "-name",
                        (short) (RandomUtils.nextInt() % 4 + 1)));
                if (item % batchSize == 0) {
                    sqlSession.flushStatements();
                }
            });
            sqlSession.flushStatements();
            sqlSession.commit();
        }

        assertTrue(true);
    }