Spring JdbcTemplate使用临时表+事务会话管理实现数据新增、查询及自动清除功能

需求描述：

由于某些情况下当查询过滤参数过大时，执行sql由于参数过大而报错，此时需要使用临时表的方式，即当参数超过某个阀值（如 1000，可调整）新增一张临时表，将原表与该临时表进行inner join 达到条件筛选过滤的目的（当然，除了这种方式，还可以考虑将参数进行切片后分批次查询组装）。

实操步骤：

1，建表语句

CREATE TABLE T3 (
	STRING_VALUE VARCHAR(512),
	DATA_TYPE VARCHAR(128),
	STRING_VALUE1 VARCHAR(512),
	STRING_VALUE2 VARCHAR(1024)
);

CREATE GLOBAL TEMPORARY TABLE T3_DATA_TEMP (
	STRING_VALUE VARCHAR(512),
	DATA_TYPE VARCHAR(128),
	STRING_VALUE1 VARCHAR(512),
	STRING_VALUE2 VARCHAR(1024)
);

2，引入需要的pom依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>testMybatis</artifactId>
    <version>1.0-SNAPSHOT</version>
    <!-- 父项目信息 -->
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.8</version>
        <relativePath/>
    </parent>
    <properties>
        <maven.compiler.source>15</maven.compiler.source>
        <maven.compiler.target>15</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>
        <!-- 高斯DB驱动 -->
        <dependency>
            <groupId>com.huawei.gauss</groupId>
            <artifactId>com.huawei.gauss.jdbc.ZenithDriver</artifactId>
            <version>1.2.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>


</project>

3，编写application.properties文件

spring.datasource.url=jdbc:zenith:@xxx:xxxx
spring.datasource.username=xxx
spring.datasource.password=xxx
spring.datasource.driver-class-name=com.huawei.gauss.jdbc.inner.GaussDriver
mybatis.mapper-locations=classpath:mapper/*.xml

org.apache.springframework.jdbc.core.JdbcTemplate = debug

4，编写 dao 层接口代码

package com.example.dao;

import com.example.entity.DataTemp;

import java.util.List;

public interface DataTempDao {

    void batchInsert(String dataType, List<?> values);

    void batchInsert(List<DataTemp> records);

    List<String> queryForList();

    void batchInsertmy_session_data(List<DataTemp> records);

    void createTempTable();

    List<String> queryValueList();

    List<String> queryValueListT3();

    void batchInsertT3(List<DataTemp> records);
}

5，dao 层实现

package com.example.dao.impl;

import com.example.dao.DataTempDao;
import com.example.entity.DataTemp;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;

import java.util.List;
import java.util.stream.Collectors;

@Repository
public class DataTempDaoImpl implements DataTempDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void batchInsert(String dataType, List<?> values) {
        if (CollectionUtils.isEmpty(values)) {
            return;
        }
        batchInsert(values.stream().map(value -> new DataTemp(dataType, value.toString())).collect(Collectors.toList()));
    }

    @Override
    public void batchInsert(List<DataTemp> records) {
        String sql = "insert into t_comm_data_temp(data_type, string_value, string_value1, string_value2) values(?, ?, ?, ?)";
        jdbcTemplate.batchUpdate(sql, records, records.size(), (ps, record) -> {
            ps.setString(1, record.getDataType());
            ps.setString(2, record.getStringValue());
            ps.setString(3, record.getStringValue1());
            ps.setString(4, record.getStringValue2());
        });
    }

    @Override
    public void batchInsertT3(List<DataTemp> records) {
//        String sql = "insert into t3(data_type, string_value, string_value1, string_value2) values(?, ?, ?, ?)";
        String sql = "insert into t3_data_temp(data_type, string_value, string_value1, string_value2) values(?, ?, ?, ?)";
        jdbcTemplate.batchUpdate(sql, records, records.size(), (ps, record) -> {
            ps.setString(1, record.getDataType());
            ps.setString(2, record.getStringValue());
            ps.setString(3, record.getStringValue1());
            ps.setString(4, record.getStringValue2());
        });
    }

    @Override
    public List<String> queryForList() {
        String sql = "select string_value from t_comm_data_temp";
        return jdbcTemplate.queryForList(sql, String.class);
    }

    @Override
    public List<String> queryValueListT3() {
//        String sql = "select string_value from t3";
        String sql = "select string_value from t3_data_temp";
        return jdbcTemplate.queryForList(sql, String.class);
    }

    @Override
    public void batchInsertmy_session_data(List<DataTemp> records) {
        String sql = "insert into `#my_session_data`(data_type, string_value, string_value1, string_value2) values(?, ?, ?, ?)";
        jdbcTemplate.batchUpdate(sql, records, records.size(), (ps, record) -> {
            ps.setString(1, record.getDataType());
            ps.setString(2, record.getStringValue());
            ps.setString(3, record.getStringValue1());
            ps.setString(4, record.getStringValue2());
        });
    }

    @Override
    public void createTempTable() {
        String sql = "CREATE TEMPORARY TABLE `#my_session_data` (  \n" +
                "    STRING_VALUE VARCHAR(512),\n" +
                "    DATA_TYPE VARCHAR(128),\n" +
                "    STRING_VALUE1 VARCHAR(512),\n" +
                "    STRING_VALUE2 VARCHAR(1024)\n" +
                ")";
        jdbcTemplate.execute(sql);
    }

    @Override
    public List<String> queryValueList() {
        String sql1 = "select string_value from `#my_session_data`";
        return jdbcTemplate.queryForList(sql1, String.class);
    }
}

6，启动类

package com.example;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.example.mapper")
public class DemoApplication {


    public static void main(String[] args) {

        SpringApplication.run(DemoApplication.class, args);
    }

}

7，编写单元测试类（涵盖业务流程）


import com.example.DemoApplication;
import com.example.dao.DataTempDao;
import com.example.dao.T1Dao;
import com.example.entity.DataTemp;
import com.example.entity.T1;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@RunWith(SpringRunner.class)
@SpringBootTest(classes = DemoApplication.class)
@Slf4j
public class SpringTest {

    @Autowired
    DataTempDao dataTempDao;
    @Autowired
    JdbcTemplate jdbcTemplate;
    @Autowired
    T1Dao t1Dao;

    @Test
    public void test1(){
        List<DataTemp> list = new ArrayList<>(10);
        DataTemp dataTemp = new DataTemp();
        dataTemp.setDataType("test");
        dataTemp.setStringValue("111");
        dataTemp.setStringValue1("1");
        dataTemp.setStringValue2("2");
        list.add(dataTemp);
        dataTempDao.batchInsert(list);
        //查询
        List<String> dataTemps = dataTempDao.queryForList();
        log.info("查询有:{}条数据！",dataTemps.size());
    }

    /**
     * 测试查询
     */
    @Test
    public void test2(){
        //查询
        String sql = "select * from T1";
        List<Map<String, Object>> query = jdbcTemplate.query(sql, new ColumnMapRowMapper());
        query.stream().forEach(v->{
            v.entrySet().stream().forEach(w->{
                log.info("key:{},value:{}",w.getKey(),w.getValue());
            });
        });
    }

    /**
     * 测试单条新增
     */
    @Test
    public void test3(){
        T1 t1 = new T1();
//        t1.setId(1L);
        t1.setData(new Timestamp(System.currentTimeMillis()));
        t1.setA("A");
        t1.setB("B");
        t1Dao.save(t1);
    }

    /**
     * 测试批量新增
     */
    @Test
    public void test4(){
        List<T1> list = new ArrayList<>(4);
        for (int i = 0; i < 4; i++) {
            T1 t1 = new T1();
            t1.setId(Long.valueOf(i));
            t1.setData(new Timestamp(System.currentTimeMillis()));
            t1.setA("A"+i);
            t1.setB("B"+i);
            list.add(t1);
        }
        t1Dao.batchInsert(list);
    }

    /**
     * 临时表测试
     * 同一个 session下的表才能查询到，即 每次创建完表执行操作后，session结束时会自动把数据及表清除掉，下次再执行时重新开始
     */
    @Test
    public void test5(){
        dataTempDao.createTempTable();
        log.info("建表完成！");

        List<DataTemp> list = new ArrayList<>(10);
        DataTemp dataTemp = new DataTemp();
        dataTemp.setDataType("test");
        dataTemp.setStringValue("111");
        dataTemp.setStringValue1("1");
        dataTemp.setStringValue2("2");
        list.add(dataTemp);
        dataTempDao.batchInsertmy_session_data(list);
        //查询
        List<String> dataTemps = dataTempDao.queryValueList();
        log.info("总共有:{} 条数据！",dataTemps.size());
    }

    @Test
    @Transactional
    public void test6(){
        List<DataTemp> list = new ArrayList<>(10);
        DataTemp dataTemp = new DataTemp();
        dataTemp.setDataType("test");
        dataTemp.setStringValue("111");
        dataTemp.setStringValue1("1");
        dataTemp.setStringValue2("2");
        list.add(dataTemp);
        dataTempDao.batchInsertT3(list);
        //查询
        List<String> dataTemps = dataTempDao.queryValueListT3();
        log.info("查询有:{}条数据！",dataTemps.size());
    }

}

注意：要想实现插入数据与查询在同一个会话中实现，这里采用最简单的实现方式：事务来实现，即方法上加上注解 @Transactional 即可

8，整个项目代码结构