Java-使用sqlSessionTemplate实现批量更新-模拟mybatis 动态sql

环境准备（非核心方法）

创建表

创建表的sql(下表是基于Oracle创建的)

CREATE TABLE "SYSTEM"."STUDENT" (
    "ID"       NUMBER(10, 0),
    "NAME"     VARCHAR2(20 BYTE),
    "ADDRES"   CLOB,
    PRIMARY KEY ( "ID" )
        USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
            STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
            DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT )
        TABLESPACE "SYSTEM"
    ENABLE
)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
    STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
    DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT )
TABLESPACE "SYSTEM"
    LOB ( "ADDRES" ) STORE AS BASICFILE (
        TABLESPACE "SYSTEM"
        ENABLE STORAGE IN ROW
        CHUNK 8192
        RETENTION
        NOCACHE LOGGING
        STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
        DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT )
    );
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

表结构-具体使用什么数据库都行以自己的项目为准

创建集成mybatis的springboot 项目（数据库是基于Oracle的）

pom

    4.0.0
    
        org.springframework.boot
        spring-boot-starter-parent
        2.5.0
         
    
    com.example
    demo
    0.0.1-SNAPSHOT
    mybatis-demo
    测试mybaits
    
        1.8
    
    
        
            org.springframework.boot
            spring-boot-starter-web
        
        
            org.springframework.boot
            spring-boot-starter-test
            test
        
        
        
            org.mybatis.spring.boot
            mybatis-spring-boot-starter
            2.2.0
        
        
        
            com.oracle.database.jdbc
            ojdbc8
            12.2.0.1
        
    




1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

application

spring.datasource.url=jdbc:oracle:thin:@localhost:1521:xe
spring.datasource.username=system
spring.datasource.password=oracle
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

1
2
3
4
5

制造假数据

代码

student表操作类

@Service
public class studentDaoTest {


    @Autowired
    private SqlSessionTemplate sqlSessionTemplate;

  

    public void inster(){
        Connection connection = null;
        SqlSessionFactory sqlSessionFactory = sqlSessionTemplate.getSqlSessionFactory();
        SqlSession sqlSession = sqlSessionFactory.openSession();
        connection=sqlSession.getConnection();
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO student (id, name, addres) VALUES (?, ?, ?)");

        try (PreparedStatement statement = connection.prepareStatement(sql.toString())) {
            for (int i = 1; i <= 100; i++) {
                statement.setInt(1, i);
                statement.setString(2, "Name " + i);
                statement.setString(3, "Addres " + i);
                statement.addBatch();
            }

            statement.executeBatch();
        }catch (Exception e){
            System.out.println(e.getMessage());
        }
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

测试类

@SpringBootTest
class MybatisDemoApplicationTests {


    @Autowired
    private studentDaoTest studentDaoTest;

    @Test
    void contextLoads() {
       studentDaoTest.inster();
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13

结果

批量更新sql拼接(核心方法)

更新方法

 public String testSQlAppendBatch(List> prms){


        Connection connection = null;
        //具体的拼接参数（按顺序添加到此集合里）
        List