引入阿里Druid数据库连接池(maven ssm框架)

2023-01-19 16:47:46

Druid

Druid是阿里巴巴开源平台上的一个项目,整个项目由数据库连接池、插件框架和SQL解析器组成。该项目主要是为了扩展JDBC的一些限制,可以让程序员实现一些特殊的需求,比如向密钥服务请求凭证、统计SQL信息、SQL性能收集、SQL注入检查、SQL翻译等,程序员可以通过定制来实现自己需要的功能。


Maven引入jar包

        <dependency>  
                <groupId>com.alibaba</groupId>  
                <artifactId>druid</artifactId>  
                <version>1.0.9</version>  
            </dependency>

        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
            <version>1.8.8</version>
        </dependency>

配置数据库连接池

在ApplicationContext.xml文件中添加下列配置
配置数据库

<!-- 声明属性文件 -->
    <context:property-placeholder location="classpath:mysql.properties" />
    <!-- 阿里 druid 数据库连接池 -->  
    <bean id = "dataSource" class = "com.alibaba.druid.pool.DruidDataSource" destroy-method = "close" >    
       <!-- 数据库基本信息配置 -->  
       <property name = "url" value = "jdbc:mysql://${mysql.host}:${mysql.port}/${mysql.dbname}?useUnicode=true&amp;characterEncoding=UTF-8&amp;zeroDateTimeBehavior=convertToNull" />    
       <property name = "username" value = "${mysql.username}" />    
       <property name = "password" value = "${mysql.password}" />    
       <property name = "driverClassName" value = "${driverClassName}" />    
       <property name = "filters" value = "${filters}" />    
        <!-- 最大并发连接数 -->  
       <property name = "maxActive" value = "${maxActive}" />  
       <!-- 初始化连接数量 -->  
       <property name = "initialSize" value = "${initialSize}" />  
       <!-- 配置获取连接等待超时的时间 -->  
       <property name = "maxWait" value = "${maxWait}" />  
       <!-- 最小空闲连接数 -->  
       <property name = "minIdle" value = "${minIdle}" />    
       <!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->  
       <property name = "timeBetweenEvictionRunsMillis" value ="${timeBetweenEvictionRunsMillis}" />  
       <!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->  
       <property name = "minEvictableIdleTimeMillis" value ="${minEvictableIdleTimeMillis}" />    
<!--        <property name = "validationQuery" value = "${validationQuery}" />     -->
       <property name = "testWhileIdle" value = "${testWhileIdle}" />    
       <property name = "testOnBorrow" value = "${testOnBorrow}" />    
       <property name = "testOnReturn" value = "${testOnReturn}" />    
       <property name = "maxOpenPreparedStatements" value ="${maxOpenPreparedStatements}" />  
       <!-- 打开 removeAbandoned 功能 -->  
       <property name = "removeAbandoned" value = "${removeAbandoned}" />  
       <!-- 1800 秒,也就是 30 分钟 -->  
       <property name = "removeAbandonedTimeout" value ="${removeAbandonedTimeout}" />  
       <!-- 关闭 abanded 连接时输出错误日志 -->     
       <property name = "logAbandoned" value = "${logAbandoned}" /> 
       <property name="proxyFilters">
            <list>
                <ref bean="wall-filter"/>
                <ref bean="stat-filter"/>
                <ref bean="log-filter"/>
            </list>
        </property>
    </bean>  


    <!-- 慢SQL记录 -->
    <bean id="stat-filter" class="com.alibaba.druid.filter.stat.StatFilter">
        <!-- 慢sql时间设置,即执行时间大于200毫秒的都是慢sql -->
        <property name="slowSqlMillis" value="2000"/>
        <property name="logSlowSql" value="true"/>
    </bean>

    <bean id="log-filter" class="com.alibaba.druid.filter.logging.Log4jFilter">
        <property name="dataSourceLogEnabled" value="true" />
        <property name="statementExecutableSqlLogEnable" value="true" />
    </bean>

    <!-- 防御SQL攻击 -->
    <bean id="wall-filter" class="com.alibaba.druid.wall.WallFilter">  
        <property name="config" ref="wall-config" />  
        <property name="logViolation" value="true" />  
<!--         <property name="throwException" value="false" />   -->
    </bean>  

    <bean id="wall-config" class="com.alibaba.druid.wall.WallConfig">  
        <property name="deleteAllow" value="false" />  
        <property name="truncateAllow" value="false" />  
        <property name="dropTableAllow" value="false" />  
        <property name="alterTableAllow" value="false" />  
        <property name="deleteWhereNoneCheck" value="false" />  
        <property name="updateWhereNoneCheck" value="false" />  

    </bean> 



    <!-- druid  spring监控   start -->
    <bean id="druid-stat-interceptor"
          class="com.alibaba.druid.support.spring.stat.DruidStatInterceptor">
    </bean>

    <bean id="druid-stat-pointcut" class="org.springframework.aop.support.JdkRegexpMethodPointcut"
          scope="prototype">
        <property name="patterns">
            <list>
                <value>com.dekscom.db.dao.*</value>
            </list>
        </property>
    </bean>


    <!-- 创建基于类的代理 -->
    <aop:config proxy-target-class="true">
        <aop:advisor advice-ref="druid-stat-interceptor" pointcut-ref="druid-stat-pointcut" />
    </aop:config>
    <!-- druid  spring监控   end -->

mysql.properties属性文件

mysql.host=localhost
mysql.port=3306
mysql.dbname=mysqldb
mysql.username=root
mysql.password=root
driverClassName: com.mysql.jdbc.Driver  
filters: wall,stat  
maxActive: 20  
initialSize: 3  
maxWait: 5000  
minIdle: 3  
maxIdle: 15  
timeBetweenEvictionRunsMillis: 60000  
minEvictableIdleTimeMillis: 300000  
validationQuery: SELECT 'x'  
testWhileIdle: true  
testOnBorrow: false  
testOnReturn: false  
maxOpenPreparedStatements: 20  
removeAbandoned: true  
removeAbandonedTimeout: 1800  
logAbandoned: true 

在web.xml文件中添加下列配置

<!-- 连接池 启用 Web 监控统计功能    start-->  
      <filter>  
         <filter-name>DruidWebStatFilter</filter-name>  
         <filter-class>com.alibaba.druid.support.http.WebStatFilter</filter-class>  
         <init-param>  
             <param-name>exclusions</param-name>  
             <param-value>*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*</param-value>  
         </init-param>  
      </filter>  
      <filter-mapping>  
         <filter-name>DruidWebStatFilter</filter-name>  
         <url-pattern>/*</url-pattern>  
      </filter-mapping>  
      <servlet>  
        <servlet-name>DruidStatView </servlet-name>  
        <servlet-class>com.alibaba.druid.support.http.StatViewServlet</servlet-class>  
        <init-param>  
        <!-- 用户名 -->  
            <param-name>loginUsername</param-name>  
            <param-value>druid</param-value>  
        </init-param>  
        <init-param>  
        <!-- 密码 -->  
        <param-name>loginPassword</param-name>  
        <param-value>druid</param-value>  
     </init-param>
      </servlet>  
      <servlet-mapping>  
         <servlet-name>DruidStatView</servlet-name>  
         <url-pattern>/druid/*</url-pattern>  
      </servlet-mapping>  
      <!-- 连接池 启用 Web 监控统计功能    end--> 


结果
http://localhost:80/项目/druid/index.html

首页
这里写图片描述

web监控
这里写图片描述

spring监控
这里写图片描述

  • 作者:谁是独钓寒江雪
  • 原文链接:https://blog.csdn.net/u012978354/article/details/78427171
    更新时间:2023-01-19 16:47:46