JDK20 + SpringBoot 3.1.0 + JdbcTemplate usage
- 1. Test database Postgres
- 2. SpringBoot project
-
- 1.Pom dependency
- 2. Configuration file
- 3. Startup class
- 4. Data source configuration class
- 5. Entity object class packaging class
- 6. Entity objects for testing
-
- 1.Base class
- 2.Extension class
- 7.Test class
- 3. Demonstration of tool classes encapsulated by SpringBoot
Directly executing SQL statements through JdbcTemplate, combined with dynamic compilation of source code, can easily achieve the effect of dynamically modifying code logic.
1. Test database 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 );
2. SpringBoot project
1.Pom dependencies
<?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>
<!--Configure Alibaba Cloud dependency packages and plug-in repositories-->
<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>
<!--druid-->
<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>
<!-- mybatis-->
<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>
<!-- pgsql -->
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.6.0</version>
</dependency>
</dependencies>
</project>
2. Configuration file
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
#The following are supplementary settings for the connection pool, which apply to all data sources above.
#Initialize size, minimum, maximum
initial-size: 5
min-idle: 5
max-active: 20
#Configure the timeout to wait for the connection to be obtained
max-wait: 60000
# Configure how often to detect idle connections that need to be closed. The unit is milliseconds.
time-between-eviction-runs-millis: 60000
# Configure the minimum survival time of a connection in the pool, the unit is milliseconds
min-evictable-idle-time-millis: 300000
validation-query: select version()
test-while-idle: true
test-on-borrow: false
test-on-return: false
# Turn on PSCache and specify the size of PSCache on each connection
pool-prepared-statements: true
# Configure the filters for monitoring and statistics interception. After removing them, the monitoring interface SQL cannot collect statistics. The wall is used for firewalls.
max-pool-prepared-statement-per-connection-size: 20
filters: stat,wall
use-global-data-source-stat: true
# Turn on the mergeSql function through the connectProperties attribute; slow SQL recording
connect-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
3. Startup class
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. Data source configuration class
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 timeout tentatively 3D may cause a large number of socket dead links
*/
@Value("${spring.datasource.postgres.readTimeout}")
private int readTimeout;
@Bean(name = "druidProperties")
@ConfigurationProperties(prefix = "spring.datasource")
public Properties druidProperties(){<!-- -->
return new Properties();
}
/**
* @description: data source
* @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. Entity object class packaging class
is used to configure the entity object class to facilitate parsing the return value of JdbcTemplate query
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);
/**
* Get the class packaging object
* @paramclazz
* @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
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> {<!-- -->
/**
* log
*/
protected final Log logger = LogFactory.getLog(this.getClass());
/**
* Conversion type
*/
@Nullable
private Class<T> requiredType;
/**
* Cache class attributes
*/
@Nullable
private Map<String, PropertyDescriptor> mappedFields;
@Nullable
private Set<String> mappedProperties;
private boolean primitivesDefaultedForNullValue = true;
/**
* Whether the verification attributes are consistent
*/
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;
}
/**
* Construct and parse target class attribute information
* @param requiredType
*/
public MultiColumnRowMapper(Class<T> requiredType) {<!-- -->
this.requiredType = requiredType;
init();
}
/**
* Parse attributes
*/
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());
}
}
}
/**
* Convert the returned information into the specified class object
* @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 & amp; & amp; 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);
}
}
}
//Verify attribute consistency
if (populatedProperties != null & amp; & amp; !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. Entity objects for testing
1. Base class
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.Extension class
package org.example.entity;
/**
* @author moon
* @date 2023-09-12 10:48
* @since 1.8
*/
public class Rabbit extends BaseAnimal{<!-- -->
}
7. Test class
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;
/**
* Insert via ? parameter placeholder
*/
@GetMapping("/insert")
public void insert(){<!-- -->
postgresTemplate.update("INSERT INTO PUBLIC.TB_RABBIT_BASIC (ID, ANIMAL_NAME, COUNTRY) VALUES (?,?,?)",18,"海棠Rabbit","France");
}
/**
* Batch insert
*/
@GetMapping("/batchInsert")
public void batchInsert(){<!-- -->
List<Object[]> list = new ArrayList<>(3);
list.add(new Object[]{<!-- -->19,"Begonia Rabbit","France"});
list.add(new Object[]{<!-- -->20,"Himalayan Rabbit","China"});
list.add(new Object[]{<!-- -->30,"Hare","Belgium"});
postgresTemplate.batchUpdate("INSERT INTO PUBLIC.TB_RABBIT_BASIC (ID, ANIMAL_NAME, COUNTRY) VALUES (?,?,?)",list);
}
/**
* renew
*/
@GetMapping("/update")
public void update(){<!-- -->
postgresTemplate.update("UPDATE PUBLIC.TB_RABBIT_BASIC SET COUNTRY = ? WHERE ID = ?","France + ",19);
}
/**
* renew
*/
@GetMapping("/batchUpdate")
public void batchUpdate(){<!-- -->
List<Object[]> list = new ArrayList<>(3);
list.add(new Object[]{<!-- -->"France + + + ",19});
list.add(new Object[]{<!-- -->"China + + + ",20});
list.add(new Object[]{<!-- -->"Belgium + + + ",30});
postgresTemplate.batchUpdate("UPDATE PUBLIC.TB_RABBIT_BASIC SET COUNTRY = ? WHERE ID = ?",list);
}
/**
* delete
*/
@GetMapping("/delete")
public void delete(){<!-- -->
postgresTemplate.update("DELETE FROM PUBLIC.TB_RABBIT_BASIC WHERE ID = ?",19);
}
/**
* batch deletion
*/
@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;
}
/**
* Query select *
* @return
*/
@GetMapping("/queryForMap")
public Map<String, Object> queryForMap(){<!-- -->
return postgresTemplate.queryForMap("SELECT * FROM PUBLIC.TB_RABBIT_BASIC WHERE ID = ?",18);
}
/**
* Query 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 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 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 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 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));
}
}
Take batch query as an example: http://127.0.0.1:8081/animal/queryForListClass
3. SpringBoot encapsulated tool class demonstration
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);
}
}
