企业网站建设与维护运营,android开发菜鸟教程,用什么自己做网站吗,一般网站服务器JDK20 SpringBoot 3.1.0 JdbcTemplate 使用 一.测试数据库 Postgres二.SpringBoot项目1.Pom 依赖2.配置文件3.启动类4.数据源配置类5.实体对象类包装类6.测试用实体对象1.基类2.扩展类 7.测试类 通过 JdbcTemplate 直接执行 SQL 语句#xff0c;结合源码动态编译即可方便实现… JDK20 SpringBoot 3.1.0 JdbcTemplate 使用 一.测试数据库 Postgres二.SpringBoot项目1.Pom 依赖2.配置文件3.启动类4.数据源配置类5.实体对象类包装类6.测试用实体对象1.基类2.扩展类 7.测试类 通过 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
);二.SpringBoot项目
1.Pom 依赖
?xml version1.0 encodingUTF-8?
project xmlnshttp://maven.apache.org/POM/4.0.0xmlns:xsihttp://www.w3.org/2001/XMLSchema-instancexsi:schemaLocationhttp://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsdmodelVersion4.0.0/modelVersiongroupIdorg.example/groupIdartifactIdJdbcTemplateDemo/artifactIdversion1.0-SNAPSHOT/versionbuildpluginsplugingroupIdorg.apache.maven.plugins/groupIdartifactIdmaven-compiler-plugin/artifactIdconfigurationsource18/sourcetarget18/target/configuration/plugin/plugins/buildpropertiesmaven.compiler.source20/maven.compiler.sourcemaven.compiler.target20/maven.compiler.targetproject.build.sourceEncodingUTF-8/project.build.sourceEncodingspring-boot.version3.1.0/spring-boot.version/properties!--配置阿里云依赖包和插件仓库--repositoriesrepositoryidaliyun/idurlhttps://maven.aliyun.com/repository/central//urlreleasesenabledtrue/enabled/releasessnapshotsenabledtrue/enabled/snapshots/repository/repositoriesdependenciesdependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-web/artifactIdversion${spring-boot.version}/version/dependencydependencygroupIdorg.projectlombok/groupIdartifactIdlombok/artifactIdversion1.18.26/version/dependency!--druid--dependencygroupIdcom.alibaba/groupIdartifactIddruid-spring-boot-starter/artifactIdversion1.2.16/versionexclusionsexclusiongroupIdorg.springframework.boot/groupIdartifactIdspring-boot-autoconfigure/artifactId/exclusionexclusiongroupIdorg.slf4j/groupIdartifactIdslf4j-api/artifactId/exclusion/exclusions/dependency!-- mybatis--dependencygroupIdorg.mybatis.spring.boot/groupIdartifactIdmybatis-spring-boot-starter/artifactIdversion3.0.2/versionexclusionsexclusiongroupIdorg.springframework.boot/groupIdartifactIdspring-boot-autoconfigure/artifactId/exclusionexclusiongroupIdorg.slf4j/groupIdartifactIdslf4j-api/artifactId/exclusion/exclusions/dependency!-- pgsql --dependencygroupIdorg.postgresql/groupIdartifactIdpostgresql/artifactIdversion42.6.0/version/dependency/dependencies
/project2.配置文件
server:port: 8081spring:datasource:postgres:readTimeout: 259200000 # 3 * 24 * 60 * 60 * 1000druid:username: postgrespassword: 123456url: jdbc:postgresql://127.0.0.1:5432/wiki_animal_dbdriverClassName: org.postgresql.Drivertype: com.alibaba.druid.pool.DruidDataSource# 下面为连接池的补充设置应用到上面所有数据源中# 初始化大小最小最大initial-size: 5min-idle: 5max-active: 20# 配置获取连接等待超时的时间max-wait: 60000# 配置间隔多久才进行一次检测检测需要关闭的空闲连接单位是毫秒time-between-eviction-runs-millis: 60000# 配置一个连接在池中最小生存的时间单位是毫秒min-evictable-idle-time-millis: 300000validation-query: select version()test-while-idle: truetest-on-borrow: falsetest-on-return: false# 打开PSCache并且指定每个连接上PSCache的大小pool-prepared-statements: true# 配置监控统计拦截的filters去掉后监控界面sql无法统计wall用于防火墙max-pool-prepared-statement-per-connection-size: 20filters: stat,walluse-global-data-source-stat: true# 通过connectProperties属性来打开mergeSql功能慢SQL记录connect-properties: druid.stat.mergeSqltrue;druid.stat.slowSqlMillis5000
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*/PrimaryBean(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 MapString,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 MultiColumnRowMapperT implements RowMapperT {/*** 日志*/protected final Log logger LogFactory.getLog(this.getClass());/*** 转换类型*/Nullableprivate ClassT requiredType;/*** 缓存类属性*/Nullableprivate MapString, PropertyDescriptor mappedFields;Nullableprivate SetString 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(ClassT 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*/Nullablepublic T mapRow(ResultSet rs, int rowNumber) throws SQLException {ResultSetMetaData rsmd rs.getMetaData();int columnCount rsmd.getColumnCount();T mappedObject BeanUtils.instantiateClass(requiredType);SetString 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;}}Nullableprotected 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;Autowiredprivate 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(){ListObject[] 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(){ListObject[] 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(){ListObject[] 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 MapString, 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 ListMapString, 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 ListRabbit 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
