Spring MVC配置druid 数据源

客官°小女子只卖身不卖艺 2021-09-28 15:02 1060阅读 0赞

# Spring MVC 配置 druid 数据源实例 #

### 文章目录 ###

*  Spring MVC 配置 druid 数据源实例
 *  前言
 *  druid介入步骤
 *   *  1、引入druid 相关依赖
     *  3、配置web.xml
     *  4、配置spring 配置文件
     *   *  方式1 、数据库配置保存在spring配置xml文件
         *  方式二 、数据库配置保存在properties
     *  测试

# 前言 #

前提先使用maven进行开发，搭建好spring mvc框架demo框架。

# druid介入步骤 #

## 1、引入druid 相关依赖 ##

<dependency>
    			<groupId>org.mybatis</groupId>
    			<artifactId>mybatis-spring</artifactId>
    			<version>1.3.1</version>
    		</dependency>
    
    		
    		 <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.1.12</version>
            </dependency>
            
    		
    		<dependency>
    			<groupId>mysql</groupId>
    			<artifactId>mysql-connector-java</artifactId>
    			<version>5.1.21</version>
    		</dependency>

## 3、配置web.xml ##

配置druid的控制台servlet和http请求拦截器（方便监控请求情况，不需要刻意注释掉filter和servlet，不推荐）  
可以在druid控制台servlet下设置initparam 配置访问账号和密码（强烈推荐）

配置druid控制（带访问账号、密码）、spring mvc拦截器

<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>/assets/*,*.css,*.js,*.gif,*.jpg,*.png,*.ico,*.eot,*.svg,*.ttf,*.woff,*.jsp,*.tpl,/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>admin</param-value>
    		</init-param>
    		<init-param>
    			
    			<param-name>loginPassword</param-name>
    			<param-value>admin</param-value>
    		</init-param>
    	</servlet>
    	<servlet-mapping>
    		<servlet-name>druidStatView</servlet-name>
    		<url-pattern>/druid/*</url-pattern>
    	</servlet-mapping>

## 4、配置spring 配置文件 ##

web.xml 中的spring 配置文件

<context-param>
    		<param-name>contextConfigLocation</param-name>
    		<param-value>classpath:applicationContext.xml</param-value>
    	</context-param>

### 方式1 、数据库配置保存在spring配置xml文件 ###

<bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
            <property name="location" value="classpath:jdbc.properties"/>
        </bean>
        
        <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
        <property name="driverClassName" value="driver"/>
        
        <property name="url" value="xxx"/>
        <property name="username" value="username"/>
        <property name="password" value="$password"/>
        
        <property name="initialSize" value="1"/>
        <property name="minIdle" value="1"/>
        <property name="maxActive" value="20"/>
        
        <property name="maxWait" value="60000"/>
        
        <property name="timeBetweenEvictionRunsMillis" value="60000"/>
        
        <property name="minEvictableIdleTimeMillis" value="300000"/>
        <property name="validationQuery" value="SELECT 'x'"/>
        <property name="testWhileIdle" value="true"/>
        <property name="testOnBorrow" value="false"/>
        <property name="testOnReturn" value="false"/>
        
        <property name="poolPreparedStatements" value="false"/>
        <property name="maxPoolPreparedStatementPerConnectionSize" value="20"/>
        
        <property name="filters" value="stat"/>
        </bean>

### 方式二 、数据库配置保存在properties ###

为什么使用properties保存数据库连接信息，因为我们要对测试环境、生产数据库配置中敏感信息进行加密，使用密文形式保存，避免信息泄露。

dbconfig.properties配置文件 - 保存数据库连接配置。

url=jdbc:mysql://xxx:3306/db?useUnicode=true&characterEncoding=utf-8;autoReconnect=true
    username=WhI1rcM89+Q=
    password=95UiUGyb6no=
    driverClassName=com.mysql.jdbc.Driver
    filters=stat
    maxActive=20
    initialSize=1
    maxWait=60000
    minIdle=10
    maxIdle=15
    timeBetweenEvictionRunsMillis=60000
    minEvictableIdleTimeMillis=300000
    validationQuery=SELECT 'x'
    testWhileIdle=true
    testOnBorrow=false
    testOnReturn=false
    maxOpenPreparedStatements=20
    removeAbandoned=true
    removeAbandonedTimeout=1800
    logAbandoned=true

配置spring 配置文件applicationContext.xml

备注： 我这里使用多环境配置和自定义PropertyPlaceholderConfigurer 进行配置文件解析 ，方便应用实现多环境配置切换和密文信息解密。  
具体查看：[SpringMVC项目数据库用户名密码加密解密][SpringMVC]

<?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" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:task="http://www.springframework.org/schema/task" xsi:schemaLocation="http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.3.xsd http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task-3.2.xsd ">
    
    	<context:component-scan base-package="com.csii">
    		
    		<context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller" />
    	</context:component-scan>
    
    	
    
    	<bean class="com.csii.util.PropertiesUtil">
    
             <property name="locations">
                 <list>
    				 <value>classpath:app-${spring.profiles.active}.properties</value>
    				 <value>classpath:dbconfig-${spring.profiles.active}.properties</value>
                 </list>
             </property>
    
             <property name="decryptProperties">
                 <array>
    
    				 <value>username</value>
    				 <value>password</value>
                 </array>
             </property>
         </bean>
    
        
    
    
    
    
    	<bean id="pooledDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">  
    	    
    	    <property name="url" value="${url}" />
    	    <property name="username" value="${username}" />
    	    <property name="password" value="${password}" />
    		<property name="driverClassName" value="${driverClassName}" />
    		<property name="filters" value="${filters}" />
    		
    		<property name="maxActive" value="${maxActive}" />
    		
    		<property name="initialSize" value="${initialSize}" />
    		
    		<property name="minIdle" value="${minIdle}" />
    		
    		<property name="maxWait" value="${maxWait}" />
    		
    		<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}" />
    		
    		<property name="removeAbandoned" value="${removeAbandoned}" />
    		
    		<property name="removeAbandonedTimeout" value="${removeAbandonedTimeout}" />
    		
    		<property name="logAbandoned" value="${logAbandoned}" />  
    	</bean>
    
    	
    	<bean id="druid-stat-interceptor" class="com.alibaba.druid.support.spring.stat.DruidStatInterceptor" />
    
    	<bean id="druid-stat-pointcut" class="org.springframework.aop.support.JdkRegexpMethodPointcut" scope="prototype">
    		<property name="patterns">
    			<list>
    				<value>spring.druid.service.*</value>
    			</list>
    		</property>
    	</bean>
    	
    	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    		
    		<property name="configLocation" value="classpath:mybatis-config.xml"></property>
    		<property name="dataSource" ref="pooledDataSource"></property>
    		
    		<property name="mapperLocations" value="classpath:mapper/*.xml"></property>
    	</bean>
    
    	
    	<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    		
    		<property name="basePackage" value="com.csii.dao"></property>
    	</bean>
    
    	
    	<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
    		<constructor-arg name="sqlSessionFactory" ref="sqlSessionFactory"></constructor-arg>
    		<constructor-arg name="executorType" value="BATCH"></constructor-arg>
    	</bean>
    	
    
    	
    	<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    		
    		<property name="dataSource" ref="pooledDataSource"></property>
    	</bean>
    	
    	<aop:config>
    		
    		<aop:pointcut expression="execution(* com.csii.service..*(..))" id="txPoint" />
    		
    		<aop:advisor advice-ref="txAdvice" pointcut-ref="txPoint" />
    		<aop:advisor advice-ref="druid-stat-interceptor" pointcut-ref="druid-stat-pointcut" />
    	</aop:config>
    
    	
    	<tx:advice id="txAdvice" transaction-manager="transactionManager">
    		<tx:attributes>
    			
    			<tx:method name="*" />
    		</tx:attributes>
    	</tx:advice>
    	
    	<task:annotation-driven/>
    
    </beans>

## 测试 ##

启动spring mvc 应用后，可以通过页面访问druid控制台  
例如：[http://localhost:8080/druid][http_localhost_8080_druid]

通过请求后台接口访问DAO层查询数据，可以在控制台监控到相应sql。

[SpringMVC]: https://blog.csdn.net/yunfeng482/article/details/90239119
[http_localhost_8080_druid]: http://localhost:8080/druid