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

    }
}