spring 简单的使用 Hikari连接池 和 jdbc连接mysql 的一个简单例子

pom.xml

<?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>jdbctemplatetest</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>5.1.9.RELEASE</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.1.9.RELEASE</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.1.9.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.33</version>
        </dependency>
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>5.0.1</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.2</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
</project>

配置文件，此处支持xml 和properties 配置

jdbc.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd">


<!--    此处使用base.properties 配置文件-->
    <context:property-placeholder location="base.properties"></context:property-placeholder>
    <bean id="HikariDataSource" class="com.jdbc.datasource.DataSource" factory-method="HikariDataSource">
        <constructor-arg name="poolname" type="java.lang.String" value="${poolname}"/>
        <constructor-arg name="driverClassName" type="java.lang.String" value="${driverClass}"/>
        <constructor-arg name="jdbcurl" type="java.lang.String" value="${jdbcUrl}"/>
        <constructor-arg name="username" type="java.lang.String" value="${user}"/>
        <constructor-arg name="password" type="java.lang.String" value="${password}"/>
        <constructor-arg name="minimumIdle" type="java.lang.Integer" value="${minPoolSize}"/>
        <constructor-arg name="maximumpoolSize" type="java.lang.Integer" value="${maxPoolSize}"/>
    </bean>
    <bean id="jdbctemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <constructor-arg name="dataSource" ref="HikariDataSource"></constructor-arg>
    </bean>
</beans>

base.properties

# sysdb data source
poolname=sysdb
driverClass=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql://127.0.0.1:3306/mytest?autoReconnect=true&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=utf-8&useSSL=false
user=root
password=123456
maxPoolSize=5
minPoolSize=1
initialPoolSize=1

DataSource.java

package com.jdbc.datasource;

import com.zaxxer.hikari.HikariDataSource;

/**
 * @authour cyf
 * 2023/6/28 18:29
 */

public class DataSource {
    /** 数据源(HikariCP) 优*/
    public static HikariDataSource HikariDataSource(final String poolname, final String driverClassName, final String jdbcurl, final String username,
                                                    final String password, final Integer minimumIdle, final Integer maximumpoolSize) {
        HikariDataSource dataSource = new HikariDataSource();
        // 连接池自定义名称,可缺省,多数据源需指定
        dataSource.setPoolName("hikari-" + poolname);
        // 可缺省,会自动填充,有意外情况
        dataSource.setDriverClassName(driverClassName);
        // 连接URL
        dataSource.setJdbcUrl(jdbcurl);
        // 认证用户
        dataSource.setUsername(username);
        // 认证密钥
        dataSource.setPassword(password);
        // 最小空闲连接数(默认10个)
        dataSource.setMinimumIdle(minimumIdle);
        // 连接池中允许的最大连接数(默认10个,推荐:((core_count * 2) + effective_spindle_count))
        dataSource.setMaximumPoolSize(maximumpoolSize);
        // 连接只读数据库时配置为true(保证安全)
        dataSource.setReadOnly(false);
        // 自动提交池中返回的连接,默认值为true,如果为false则需要手动提交事物
        dataSource.setAutoCommit(true);
        // 分配连接的最大时长(毫秒,默认30秒),超时则SQLException
        dataSource.setConnectionTimeout(30000);
        // 连接允许闲置的最长时间(毫秒,默认10分),超时则被释放
        dataSource.setIdleTimeout(600000);
        // 连接最长生命周期(毫秒,默认30分),超时则被释放(比DB超时时长少30秒,参考MySQL:wait_timeout参数[show variables like '%timeout%';])
        dataSource.setMaxLifetime(1800000);
        // 数据库连接测试语句
        dataSource.setConnectionTestQuery("SELECT 1");
        return dataSource;
    }
}

MyConfiguration.java

package com.jdbc.datasource;

import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

/**
 * @authour cyf
 * 2023/6/28 18:42
 */
@Configuration
@PropertySource("classpath:base.properties")//指定配置文件
public class MyConfiguration {

    //@Value只能单个注入
    @Value("${user}") //@PropertySource 指定配置文件后才能扫描到
    private String user;

    @Bean
    public HikariDataSource getHikariDataSource(){
        HikariDataSource source = new HikariDataSource();
        source.setUsername(user);
        source.setPassword("123456");
        source.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/mytest?autoReconnect=true&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=utf-8&useSSL=false");
        source.setDriverClassName("com.mysql.jdbc.Driver");
        return source;
    }

    @Bean
    public JdbcTemplate getJdbcTemplate(){
        return new JdbcTemplate(getHikariDataSource());
    }


}

Start.java

package com.jdbc.datasource;

import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

/**
 * @authour cyf
 * 2023/6/28 18:35
 */

public class Start {
    public static void main(String[] args) {
        //使用jdbc.xml配置
        //ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("jdbc.xml");
        //JdbcTemplate jdbctemplate = (JdbcTemplate)context.getBean("jdbctemplate");

        //使用base.properties配置
        AnnotationConfigApplicationContext context = new AnnotationConfigApplicationContext(MyConfiguration.class);
        JdbcTemplate jdbctemplate = (JdbcTemplate) context.getBean(JdbcTemplate.class);


        String sql = "insert into person(name,pass,age) values(?,?,?)";
        int result = jdbctemplate.update(sql, "zhangsan", "1111", 10);
        System.out.println(result);

    }
}