JDK20 + SpringBoot 3.1.0 + JdbcTemplate 使用

通过 JdbcTemplate 直接执行 SQL 语句，结合源码动态编译即可方便实现动态修改代码逻辑的效果

一.测试数据库 Postgres

-- public.tb_rabbit_basic definition

-- Drop table

-- DROP TABLE public.tb_rabbit_basic;

CREATE TABLE public.tb_rabbit_basic (
	id int4 NULL,
	animal_name varchar NULL,
	country varchar NULL
);
1
2
3
4
5
6
7
8
9
10
11

二.SpringBoot项目

1.Pom 依赖

<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.0modelVersion>

    <groupId>org.examplegroupId>
    <artifactId>JdbcTemplateDemoartifactId>
    <version>1.0-SNAPSHOTversion>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.pluginsgroupId>
                <artifactId>maven-compiler-pluginartifactId>
                <configuration>
                    <source>18source>
                    <target>18target>
                configuration>
            plugin>
        plugins>
    build>

    <properties>
        <maven.compiler.source>20maven.compiler.source>
        <maven.compiler.target>20maven.compiler.target>
        <project.build.sourceEncoding>UTF-8project.build.sourceEncoding>
        <spring-boot.version>3.1.0spring-boot.version>
    properties>

    
    <repositories>
        <repository>
            <id>aliyunid>
            <url>https://maven.aliyun.com/repository/central/url>
            <releases>
                <enabled>trueenabled>
            releases>
            <snapshots>
                <enabled>trueenabled>
            snapshots>
        repository>
    repositories>

    <dependencies>

        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-webartifactId>
            <version>${spring-boot.version}version>
        dependency>

        <dependency>
            <groupId>org.projectlombokgroupId>
            <artifactId>lombokartifactId>
            <version>1.18.26version>
        dependency>

        
        <dependency>
            <groupId>com.alibabagroupId>
            <artifactId>druid-spring-boot-starterartifactId>
            <version>1.2.16version>
            <exclusions>
                <exclusion>
                    <groupId>org.springframework.bootgroupId>
                    <artifactId>spring-boot-autoconfigureartifactId>
                exclusion>
                <exclusion>
                    <groupId>org.slf4jgroupId>
                    <artifactId>slf4j-apiartifactId>
                exclusion>
            exclusions>
        dependency>
        
        <dependency>
            <groupId>org.mybatis.spring.bootgroupId>
            <artifactId>mybatis-spring-boot-starterartifactId>
            <version>3.0.2version>
            <exclusions>
                <exclusion>
                    <groupId>org.springframework.bootgroupId>
                    <artifactId>spring-boot-autoconfigureartifactId>
                exclusion>
                <exclusion>
                    <groupId>org.slf4jgroupId>
                    <artifactId>slf4j-apiartifactId>
                exclusion>
            exclusions>
        dependency>
        
        <dependency>
            <groupId>org.postgresqlgroupId>
            <artifactId>postgresqlartifactId>
            <version>42.6.0version>
        dependency>
    dependencies>
project>
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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97

2.配置文件

server:
  port: 8081

spring:
  datasource:
    postgres:
      readTimeout: 259200000 # 3 * 24 * 60 * 60 * 1000
    druid:
      username: postgres
      password: 123456
      url: jdbc:postgresql://127.0.0.1:5432/wiki_animal_db
      driverClassName: org.postgresql.Driver
      type: com.alibaba.druid.pool.DruidDataSource
      # 下面为连接池的补充设置，应用到上面所有数据源中
      # 初始化大小，最小，最大
      initial-size: 5
      min-idle: 5
      max-active: 20
      # 配置获取连接等待超时的时间
      max-wait: 60000
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      time-between-eviction-runs-millis: 60000
      # 配置一个连接在池中最小生存的时间，单位是毫秒
      min-evictable-idle-time-millis: 300000
      validation-query: select version()
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      # 打开PSCache，并且指定每个连接上PSCache的大小
      pool-prepared-statements: true
      #   配置监控统计拦截的filters，去掉后监控界面sql无法统计，wall用于防火墙
      max-pool-prepared-statement-per-connection-size: 20
      filters: stat,wall
      use-global-data-source-stat: true
      # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
      connect-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

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

3.启动类

package org.example;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 * @author moon
 */
@SpringBootApplication
public class JdbcApp {

    public static void main(String[] args) {
        SpringApplication.run(JdbcApp.class, args);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

4.数据源配置类

package org.example.config;

import com.alibaba.druid.pool.DruidDataSource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.SQLException;
import java.util.Properties;

/**
 * @author moon
 * @date 2023-09-12 12:00
 * @since 1.8
 */
@Slf4j
@Configuration
public class PostgresDataSource {

    /**
     * Postgres readTimeout 超时 暂定 3D 可能导致存在大量 socket 死链接
     */
    @Value("${spring.datasource.postgres.readTimeout}")
    private int readTimeout;

    @Bean(name = "druidProperties")
    @ConfigurationProperties(prefix = "spring.datasource")
    public Properties druidProperties(){
        return new Properties();
    }

    /**
     * @description: 数据源
     * @params: [properties]
     * @return: com.alibaba.druid.pool.DruidDataSource
     * @create: 2023-09-12
     */
    @Primary
    @Bean(name = "druidDataSource")
    public DruidDataSource druidDataSource(@Qualifier("druidProperties") Properties properties){
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.configFromPropety(properties);
        try {
            druidDataSource.setSocketTimeout(readTimeout);
            druidDataSource.init();
        } catch (SQLException e) {
            log.error("Postgres Datasource Init Exception:",e);
        }
        return druidDataSource;
    }

    /**
     * jdbc template
     * @param druidDataSource
     * @return
     */
    @Bean(name = "postgresTemplate")
    public JdbcTemplate postgresTemplate(@Qualifier("druidDataSource") DruidDataSource druidDataSource){
        return new JdbcTemplate(druidDataSource);
    }
}

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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

5.实体对象类包装类

用于配置实体对象类，方便解析 JdbcTemplate 查询的返回值
1

package org.example.config;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.BeanWrapperImpl;
import org.springframework.beans.NotWritablePropertyException;
import org.springframework.beans.TypeMismatchException;
import org.springframework.dao.DataRetrievalFailureException;
import org.springframework.dao.InvalidDataAccessApiUsageException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.lang.Nullable;
import org.springframework.stereotype.Component;
import org.springframework.util.ClassUtils;
import org.springframework.util.StringUtils;

import java.beans.PropertyDescriptor;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.*;
import java.util.concurrent.Semaphore;
import java.util.concurrent.TimeUnit;

/**
 * @author moon
 * @date 2023-09-11 18:08
 * @since 1.8
 */
@Slf4j
@Component
public class ColumnRowMap {

    private Map<String,MultiColumnRowMapper> map = new HashMap<>(16);

    Semaphore semaphore = new Semaphore(1);

    /**
     * 获取类包装对象
     * @param clazz
     * @return
     */
    public MultiColumnRowMapper getColumnRowMap(Class<?> clazz) {
        while (true){
            boolean acquire = false;
            try {
                acquire = semaphore.tryAcquire(3, TimeUnit.SECONDS);
                if (acquire){
                    MultiColumnRowMapper mapper = map.get(clazz.getName());
                    if (null == mapper){
                        mapper = new MultiColumnRowMapper<>(clazz);
                        map.put(clazz.getName(),mapper);
                    }
                    //返回
                    return mapper;
                }
            } catch (InterruptedException e) {
                log.error("get column row map exception:",e);
            } finally {
                if (acquire){
                    semaphore.release();
                }
            }
        }
    }

    static class MultiColumnRowMapper<T> implements RowMapper<T> {

        /**
         * 日志
         */
        protected final Log logger = LogFactory.getLog(this.getClass());

        /**
         * 转换类型
         */
        @Nullable
        private Class<T> requiredType;

        /**
         * 缓存类属性
         */
        @Nullable
        private Map<String, PropertyDescriptor> mappedFields;

        @Nullable
        private Set<String> mappedProperties;

        private boolean primitivesDefaultedForNullValue = true;

        /**
         * 是否校验属性一致
         */
        private boolean checkFullyPopulated = false;

        public void setCheckFullyPopulated(boolean checkFullyPopulated) {
            this.checkFullyPopulated = checkFullyPopulated;
        }

        public boolean isCheckFullyPopulated() {
            return this.checkFullyPopulated;
        }

        public void setPrimitivesDefaultedForNullValue(boolean primitivesDefaultedForNullValue) {
            this.primitivesDefaultedForNullValue = primitivesDefaultedForNullValue;
        }

        public boolean isPrimitivesDefaultedForNullValue() {
            return this.primitivesDefaultedForNullValue;
        }

        /**
         * 构造并解析目标类属性信息
         * @param requiredType
         */
        public MultiColumnRowMapper(Class<T> requiredType) {
            this.requiredType = requiredType;
            init();
        }

        /**
         * 解析属性
         */
        private void init(){
            PropertyDescriptor[] var2 = BeanUtils.getPropertyDescriptors(requiredType);
            int var3 = var2.length;
            this.mappedFields = new HashMap(var3);
            this.mappedProperties = new HashSet(var3);
            for(int var4 = 0; var4 < var3; ++var4) {
                PropertyDescriptor pd = var2[var4];
                if (pd.getWriteMethod() != null) {
                    String lowerCaseName = this.lowerCaseName(pd.getName());
                    this.mappedFields.put(lowerCaseName, pd);
                    String underscoreName = this.underscoreName(pd.getName());
                    if (!lowerCaseName.equals(underscoreName)) {
                        this.mappedFields.put(underscoreName, pd);
                    }
                    this.mappedProperties.add(pd.getName());
                }
            }
        }

        /**
         * 将返回信息转为指定类对象
         * @param rs
         * @param rowNumber
         * @return
         * @throws SQLException
         */
        @Nullable
        public T mapRow(ResultSet rs, int rowNumber) throws SQLException {
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            T mappedObject = BeanUtils.instantiateClass(requiredType);
            Set<String> populatedProperties = this.isCheckFullyPopulated() ? new HashSet() : null;
            BeanWrapperImpl bw = new BeanWrapperImpl();
            bw.setBeanInstance(mappedObject);
            PropertyDescriptor pd;
            for(int index = 1; index <= columnCount; ++index) {
                String column = JdbcUtils.lookupColumnName(rsmd, index);
                String field = this.lowerCaseName(StringUtils.delete(column, " "));
                pd = this.mappedFields != null ? this.mappedFields.get(field) : null;
                if (pd != null) {
                    try {
                        Object value = this.getColumnValue(rs, index, pd);
                        if (rowNumber == 0 && this.logger.isDebugEnabled()) {
                            this.logger.debug("Mapping column '" + column + "' to property '" + pd.getName() + "' of type '" + ClassUtils.getQualifiedName(pd.getPropertyType()) + "'");
                        }
                        try {
                            bw.setPropertyValue(pd.getName(), value);
                        } catch (TypeMismatchException var14) {
                            if (value != null || !this.primitivesDefaultedForNullValue) {
                                throw var14;
                            }

                            if (this.logger.isDebugEnabled()) {
                                this.logger.debug("Intercepted TypeMismatchException for row " + rowNumber + " and column '" + column + "' with null value when setting property '" + pd.getName() + "' of type '" + ClassUtils.getQualifiedName(pd.getPropertyType()) + "' on object: " + mappedObject, var14);
                            }
                        }

                        if (populatedProperties != null) {
                            populatedProperties.add(pd.getName());
                        }
                    } catch (NotWritablePropertyException var15) {
                        throw new DataRetrievalFailureException("Unable to map column '" + column + "' to property '" + pd.getName() + "'", var15);
                    }
                }
            }
            //校验属性一致性
            if (populatedProperties != null && !populatedProperties.equals(this.mappedProperties)) {
                throw new InvalidDataAccessApiUsageException("Given ResultSet does not contain all fields necessary to populate object of " + this.requiredType.getName() + ": " + this.mappedProperties);
            } else {
                return mappedObject;
            }
        }

        @Nullable
        protected Object getColumnValue(ResultSet rs, int index, PropertyDescriptor pd) throws SQLException {
            return JdbcUtils.getResultSetValue(rs, index, pd.getPropertyType());
        }

        protected String lowerCaseName(String name) {
            return name.toLowerCase(Locale.US);
        }

        protected String underscoreName(String name) {
            if (!StringUtils.hasLength(name)) {
                return "";
            } else {
                StringBuilder result = new StringBuilder();
                result.append(Character.toLowerCase(name.charAt(0)));

                for(int i = 1; i < name.length(); ++i) {
                    char c = name.charAt(i);
                    if (Character.isUpperCase(c)) {
                        result.append('_').append(Character.toLowerCase(c));
                    } else {
                        result.append(c);
                    }
                }

                return result.toString();
            }
        }

    }

}

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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231

6.测试用实体对象

1.基类

package org.example.entity;

import lombok.Data;

/**
 * @author moon
 * @date 2023-09-12 10:45
 * @since 1.8
 */
@Data
public class BaseAnimal {

    private int id;
    private String animalName;
    private String country;

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2.扩展类

package org.example.entity;

/**
 * @author moon
 * @date 2023-09-12 10:48
 * @since 1.8
 */
public class Rabbit extends BaseAnimal{
}

1
2
3
4
5
6
7
8
9
10

7.测试类

package org.example.controller;

import jakarta.annotation.Resource;
import lombok.extern.slf4j.Slf4j;
import org.example.config.ColumnRowMap;
import org.example.entity.Rabbit;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

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

/**
 * @author moon
 * @date 2023-09-12 11:52
 * @since 1.8
 */
@Slf4j
@RestController
@RequestMapping("/animal")
public class AnimalController {

    @Resource(name = "postgresTemplate")
    private JdbcTemplate postgresTemplate;

    @Autowired
    private ColumnRowMap columnRowMap;

    /**
     * 插入 通过 ? 参数占位符
     */
    @GetMapping("/insert")
    public void insert(){
        postgresTemplate.update("INSERT INTO PUBLIC.TB_RABBIT_BASIC (ID, ANIMAL_NAME, COUNTRY) VALUES (?,?,?)",18,"海棠兔","法国");
    }

    /**
     * 批量插入
     */
    @GetMapping("/batchInsert")
    public void batchInsert(){
        List<Object[]> list = new ArrayList<>(3);
        list.add(new Object[]{19,"海棠兔","法国"});
        list.add(new Object[]{20,"喜马拉雅兔","中国"});
        list.add(new Object[]{30,"野兔","比利时"});
        postgresTemplate.batchUpdate("INSERT INTO PUBLIC.TB_RABBIT_BASIC (ID, ANIMAL_NAME, COUNTRY) VALUES (?,?,?)",list);
    }

    /**
     * 更新
     */
    @GetMapping("/update")
    public void update(){
        postgresTemplate.update("UPDATE PUBLIC.TB_RABBIT_BASIC SET COUNTRY = ? WHERE ID = ?","法国+",19);
    }

    /**
     * 更新
     */
    @GetMapping("/batchUpdate")
    public void batchUpdate(){
        List<Object[]> list = new ArrayList<>(3);
        list.add(new Object[]{"法国+++",19});
        list.add(new Object[]{"中国+++",20});
        list.add(new Object[]{"比利时+++",30});
        postgresTemplate.batchUpdate("UPDATE PUBLIC.TB_RABBIT_BASIC SET COUNTRY = ? WHERE ID = ?",list);
    }

    /**
     * 删除
     */
    @GetMapping("/delete")
    public void delete(){
        postgresTemplate.update("DELETE FROM PUBLIC.TB_RABBIT_BASIC WHERE ID = ?",19);
    }

    /**
     * 批量删除
     */
    @GetMapping("/batchDelete")
    public int[] batchDelete(){
        List<Object[]> list = new ArrayList<>();
        list.add(new Object[]{19});
        list.add(new Object[]{20});
        list.add(new Object[]{30});
        int[] result = postgresTemplate.batchUpdate("DELETE FROM PUBLIC.TB_RABBIT_BASIC WHERE ID = ?",list);
        return  result;
    }

    /**
     * 查询 select *
     * @return
     */
    @GetMapping("/queryForMap")
    public Map<String, Object> queryForMap(){
        return postgresTemplate.queryForMap("SELECT * FROM PUBLIC.TB_RABBIT_BASIC WHERE ID = ?",18);
    }

    /**
     * 查询 query for row set
     * @return
     */
    @GetMapping("/queryForRowSet")
    public void queryForRowSet(){
        SqlRowSet rowSet = postgresTemplate.queryForRowSet("SELECT * FROM PUBLIC.TB_RABBIT_BASIC WHERE ID = ?",18);
        while (rowSet.next()){
            int rowId = rowSet.getRow();
            Integer ID = rowSet.getInt("ID");
            String ANIMAL_NAME = rowSet.getString("ANIMAL_NAME");
            String COUNTRY = rowSet.getString("COUNTRY");
            log.info("rowId {} id {} animalName {} country {}",rowId,ID,ANIMAL_NAME,COUNTRY);
        }
    }

    /**
     * 查询 query for object
     * @return
     */
    @GetMapping("/queryForObject")
    public Object queryForObject(){
        return postgresTemplate.queryForObject("SELECT ID AS id FROM PUBLIC.TB_RABBIT_BASIC WHERE ID = ?", Integer.class,18);
    }

    /**
     * 查询 query for object
     * @return
     */
    @GetMapping("/queryForObjectMapper")
    public Object queryForObjectMapper(){
        return postgresTemplate.queryForObject("SELECT ID AS id, ANIMAL_NAME AS animalName, COUNTRY AS country FROM PUBLIC.TB_RABBIT_BASIC WHERE ID = ?",columnRowMap.getColumnRowMap(Rabbit.class),18);
    }

    /**
     * 查询 query for object
     * @return
     */
    @GetMapping("/queryForList")
    public List<Map<String, Object>> queryForList(){
        return postgresTemplate.queryForList("SELECT ID AS id, ANIMAL_NAME AS animalName, COUNTRY AS country FROM PUBLIC.TB_RABBIT_BASIC");
    }

    /**
     * 查询 query for object class
     * @return
     */
    @GetMapping("/queryForListClass")
    public List<Rabbit> queryForListClass(){
        return postgresTemplate.query("SELECT ID AS id, ANIMAL_NAME AS animalName, COUNTRY AS country FROM PUBLIC.TB_RABBIT_BASIC",columnRowMap.getColumnRowMap(Rabbit.class));
    }
}

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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156

以批量查询为例：http://127.0.0.1:8081/animal/queryForListClass

三.SpringBoot 封装的工具类演示

import org.example.entity.Rabbit;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.BeanWrapperImpl;

/**
 * @author moon
 * @date 2023-09-13 20:38
 * @since 1.8
 */
public class Test {

    public static void main(String[] args) {
        Object t = BeanUtils.instantiateClass(Rabbit.class);
        BeanWrapperImpl bean = new BeanWrapperImpl(t);
        bean.setPropertyValue("id",1);
        System.out.println(t);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19