JDK20 + SpringBoot 3.1.0 + JdbcTemplate 使用

悠悠 2023-10-15 21:31 2阅读 0赞

### JDK20 + SpringBoot 3.1.0 + JdbcTemplate 使用 ###

*  一.测试数据库 Postgres
 *  二.SpringBoot项目
 *   *  1.Pom 依赖
     *  2.配置文件
     *  3.启动类
     *  4.数据源配置类
     *  5.实体对象类包装类
     *  6.测试用实体对象
     *   *  1.基类
         *  2.扩展类
     *  7.测试类
 *  三.SpringBoot 封装的工具类演示

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

# 一.测试数据库 Postgres #

![在这里插入图片描述][34c9f683c5934b0e9841d7ee479308d8.png]

-- 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
    );

# 二.SpringBoot项目 #

## 1.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>JdbcTemplateDemo</artifactId>
        <version>1.0-SNAPSHOT</version>
        <build>
            <plugins>
                <plugin>
                    <groupId>org.apache.maven.plugins</groupId>
                    <artifactId>maven-compiler-plugin</artifactId>
                    <configuration>
                        <source>18</source>
                        <target>18</target>
                    </configuration>
                </plugin>
            </plugins>
        </build>
    
        <properties>
            <maven.compiler.source>20</maven.compiler.source>
            <maven.compiler.target>20</maven.compiler.target>
            <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
            <spring-boot.version>3.1.0</spring-boot.version>
        </properties>
    
        
        <repositories>
            <repository>
                <id>aliyun</id>
                <url>https://maven.aliyun.com/repository/central/</url>
                <releases>
                    <enabled>true</enabled>
                </releases>
                <snapshots>
                    <enabled>true</enabled>
                </snapshots>
            </repository>
        </repositories>
    
        <dependencies>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
                <version>${spring-boot.version}</version>
            </dependency>
    
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <version>1.18.26</version>
            </dependency>
    
            
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid-spring-boot-starter</artifactId>
                <version>1.2.16</version>
                <exclusions>
                    <exclusion>
                        <groupId>org.springframework.boot</groupId>
                        <artifactId>spring-boot-autoconfigure</artifactId>
                    </exclusion>
                    <exclusion>
                        <groupId>org.slf4j</groupId>
                        <artifactId>slf4j-api</artifactId>
                    </exclusion>
                </exclusions>
            </dependency>
            
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>3.0.2</version>
                <exclusions>
                    <exclusion>
                        <groupId>org.springframework.boot</groupId>
                        <artifactId>spring-boot-autoconfigure</artifactId>
                    </exclusion>
                    <exclusion>
                        <groupId>org.slf4j</groupId>
                        <artifactId>slf4j-api</artifactId>
                    </exclusion>
                </exclusions>
            </dependency>
            
            <dependency>
                <groupId>org.postgresql</groupId>
                <artifactId>postgresql</artifactId>
                <version>42.6.0</version>
            </dependency>
        </dependencies>
    </project>

## 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

## 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);
        }
    }

## 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);
        }
    }

## 5.实体对象类包装类 ##

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

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();
                }
            }
    
        }
    
    }

## 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;
    
    }

### 2.扩展类 ###

package org.example.entity;
    
    /**
     * @author moon
     * @date 2023-09-12 10:48
     * @since 1.8
     */
    public class Rabbit extends BaseAnimal{
        
    }

## 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));
        }
    }

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

![在这里插入图片描述][2dabff8097c2457c9acb138899896e74.png]

# 三.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);
        }
    }

![在这里插入图片描述][8093a64010d246d4a4af18a6533ef2a4.png]

[34c9f683c5934b0e9841d7ee479308d8.png]: https://img-blog.csdnimg.cn/34c9f683c5934b0e9841d7ee479308d8.png
[2dabff8097c2457c9acb138899896e74.png]: https://img-blog.csdnimg.cn/2dabff8097c2457c9acb138899896e74.png
[8093a64010d246d4a4af18a6533ef2a4.png]: https://img-blog.csdnimg.cn/8093a64010d246d4a4af18a6533ef2a4.png