SpringBoot +MyBatis +Oracle + Druid 实现数据库连接池功能

冷不防 2023-10-10 11:44 53阅读 0赞

SpringBoot 项目之pom.xml 文件依赖

<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>com.zzg</groupId>
    	<artifactId>migrate</artifactId>
    	<version>0.0.1-SNAPSHOT</version>
    
    	<parent>
    		<groupId>org.springframework.boot</groupId>
    		<artifactId>spring-boot-starter-parent</artifactId>
    		<version>1.5.18.RELEASE</version>
    	</parent>
    
    	<properties>
    		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    		<encoding>UTF-8</encoding>
    		<java.version>1.8</java.version>
    		<commons-lang.version>2.6</commons-lang.version>
    		<commons-codec.version>1.10</commons-codec.version>
    		<commons-lang3.version>3.9</commons-lang3.version>
    		<commons-net.version>3.6</commons-net.version>
    		<commons-io.version>2.6</commons-io.version>
    		<commons-collections.version>3.2.1</commons-collections.version>
    		<commons-text.version>1.8</commons-text.version>
    		<oracle.version>1.0</oracle.version>
    		<mybatis-spring-boot-starter.version>1.3.2</mybatis-spring-boot-starter.version>
    		<com.alibaba.druid.version>1.1.10</com.alibaba.druid.version>
    	</properties>
    
    	<dependencies>
    		
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-web</artifactId>
    		</dependency>
    		
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-test</artifactId>
    			<scope>test</scope>
    		</dependency>
    		
    		
    		<dependency>
    			<groupId>org.apache.commons</groupId>
    			<artifactId>commons-lang3</artifactId>
    			<version>${commons-lang3.version}</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>commons-codec</groupId>
    			<artifactId>commons-codec</artifactId>
    			<version>${commons-codec.version}</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>commons-net</groupId>
    			<artifactId>commons-net</artifactId>
    			<version>${commons-net.version}</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>commons-io</groupId>
    			<artifactId>commons-io</artifactId>
    			<version>${commons-io.version}</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>commons-collections</groupId>
    			<artifactId>commons-collections</artifactId>
    			<version>${commons-collections.version}</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>org.apache.commons</groupId>
    			<artifactId>commons-text</artifactId>
    			<version>${commons-text.version}</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>org.mybatis.spring.boot</groupId>
    			<artifactId>mybatis-spring-boot-starter</artifactId>
    			<version>${mybatis-spring-boot-starter.version}</version>
    		</dependency>
    		
    		<dependency>
    　			<groupId>com.oracle</groupId>
      			<artifactId>ojdbc6</artifactId>
      			<version>${oracle.version}</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>com.alibaba</groupId>
    			<artifactId>druid-spring-boot-starter</artifactId>
    			<version>${com.alibaba.druid.version}</version>
    		</dependency>
    		
             <dependency>
                <groupId>com.github.pagehelper</groupId>
                <artifactId>pagehelper-spring-boot-starter</artifactId>
                <version>1.2.5</version>
            </dependency>
            
            <dependency> 
    		   <groupId>javax.servlet</groupId> 
    		   <artifactId>servlet-api</artifactId> 
    		   <version>2.5</version> 
    		   <scope>provided</scope> 
    		</dependency> 
    	</dependencies>
    
    </project>

SpringBoot项目整体结构图:

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V6aGl3ZW5nYW5n_size_16_color_FFFFFF_t_70][]

实现AliBaBa Druid 数据库连接池详细步骤

步骤一：pom.xml 文件添加如下配置:

<dependency>
    			<groupId>com.alibaba</groupId>
    			<artifactId>druid-spring-boot-starter</artifactId>
    			<version>${com.alibaba.druid.version}</version>
    		</dependency>

步骤二：在application.properties 配置文件中，配置Druid 数据库连接池相关配置，我的配置如下:

# 数据库指定第三方数据库连接池
    spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
    
    # Druid 配置
    # 初始化时建立物理连接的个数
    spring.datasource.druid.initial-size=5
    # 最大连接池数量
    spring.datasource.druid.max-active=30
    # 最小连接池数量
    spring.datasource.druid.min-idle=5
    # 获取连接时最大等待时间，单位毫秒
    spring.datasource.druid.max-wait=60000
    # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
    spring.datasource.druid.time-between-eviction-runs-millis=60000
    # 连接保持空闲而不被驱逐的最小时间
    spring.datasource.druid.min-evictable-idle-time-millis=300000
    # 用来检测连接是否有效的sql，要求是一个查询语句
    spring.datasource.druid.validation-query=SELECT 1 FROM DUAL
    # 建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
    spring.datasource.druid.test-while-idle=true
    # 申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
    spring.datasource.druid.test-on-borrow=false
    # 归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
    spring.datasource.druid.test-on-return=false
    # 是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭。
    spring.datasource.druid.pool-prepared-statements=true
    # 要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。
    spring.datasource.druid.max-pool-prepared-statement-per-connection-size=50
    # 配置监控统计拦截的filters，去掉后监控界面sql无法统计
    #spring.datasource.druid.filters=stat,wall
    # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
    spring.datasource.druid.connection-properties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
    # 合并多个DruidDataSource的监控数据
    spring.datasource.druid.use-global-data-source-stat=true
    # 配置sql 注入方式
    spring.datasource.druid.filters=stat,log4j

说明：重点注意spring.datasource.type=com.alibaba.druid.pool.DruidDataSource 指定第三方数据库连接池，否则使用SpringBoot 默认数据库连接池Hick\*\*

步骤三：配置AliBaBa Druid 后台管理界面访问，通过定义Druid 配置对象实现，实现如下截图:

package com.zzg.configuration;
    
    import java.util.HashMap;
    import java.util.Map;
    
    import org.springframework.boot.web.servlet.FilterRegistrationBean;
    import org.springframework.boot.web.servlet.ServletRegistrationBean;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    
    import com.alibaba.druid.support.http.StatViewServlet;
    import com.alibaba.druid.support.http.WebStatFilter;
    /**
     * 配置Druid 管理平台
     * @author zzg
     *
     */
    @Configuration
    public class DruidConfig {
    	@Bean
        public ServletRegistrationBean druidServletRegistrationBean() {
            ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean();
            servletRegistrationBean.setServlet(new StatViewServlet());
            servletRegistrationBean.addUrlMappings("/druid/*");
            servletRegistrationBean.addInitParameter("allow", "");
            servletRegistrationBean.addInitParameter("deny", "");
            servletRegistrationBean.addInitParameter("loginUsername", "admin");
            servletRegistrationBean.addInitParameter("loginPassword", "admin");
            return servletRegistrationBean;
        }
    
        /**
         * 注册DruidFilter拦截
         *
         * @return
         */
        @Bean
        public FilterRegistrationBean duridFilterRegistrationBean() {
            FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
            filterRegistrationBean.setFilter(new WebStatFilter());
            Map<String, String> initParams = new HashMap<String, String>();
            //设置忽略请求
            initParams.put("exclusions", "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*");
            filterRegistrationBean.setInitParameters(initParams);
            filterRegistrationBean.addUrlPatterns("/*");
            return filterRegistrationBean;
        }
    
    }

效果截图:

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V6aGl3ZW5nYW5n_size_16_color_FFFFFF_t_70 1][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V6aGl3ZW5nYW5n_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20201105174933383.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V6aGl3ZW5nYW5n,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V6aGl3ZW5nYW5n_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20201105180032855.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V6aGl3ZW5nYW5n,size_16,color_FFFFFF,t_70