例子:
添加druid依赖:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>${druid-version}</version>
</dependency>
使用原生的连接池:
<!-- ========================MyBatis DataSource============================ -->
<bean id="dsMybts" class="org.apache.commons.dbcp.BasicDataSource">
<property name="url" value="jdbc:mysql://114.215.xxx.xxx:4040/tmg?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull" />
<property name="username" value="xxx" />
<property name="password" value="xxx" />
<property name="initialSize" value="10" />
<property name="maxIdle" value="20" />
<property name="maxActive" value="50" />
<property name="maxWait" value="500" />
<property name="logAbandoned" value="true" />
<property name="removeAbandoned" value="true" />
<property name="removeAbandonedTimeout" value="60" />
<property name="validationQuery" value="SELECT 1" />
<property name="testOnBorrow" value="true" />
<property name="testOnReturn" value="true" />
<property name="testWhileIdle" value="true" />
<property name="defaultAutoCommit" value="true" />
</bean>
<!-- ************************************************************************************************* -->
<!-- * MyBatis 配置 * -->
<!-- ************************************************************************************************* -->
<!-- sqlSessionFactory -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dsMybts" />
<property name="mapperLocations" value="classpath*:mybatis/mappers/**/*.xml" />
</bean>
<!-- scan for mappers and let them be autowired -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<!-- Mapper接口所在包名,Spring会自动查找其下的Mapper -->
<property name="basePackage" value="com.tmg.dao.mybatis.performance" />
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
</bean>
使用druid连接池:
<!-- ===================MyBatis DataSource===============-->
<!-- 使用druid作为数据源连接池-->
<bean id="datasource" class="com.alibaba.druid.pool.DruidDataSource"
init-method="init" destroy-method="close" >
<!-- 数据库的配置,host:主机名,port:端口,database:数据库 -->
<property name="url" value="jdbc:mysql://${mysql.host}:${mysql.port}/${mysql.database}?characterEncoding=utf8" />
<!-- 连接数据的用户名-->
<property name="username" value="${mysql.user}" />
<!-- 连接数据库的密码-->
<property name="password" value="${mysql.pwd}" />
<!-- 配置初始化大小、最小、最大 -->
<property name="initialSize" value="1" />
<property name="minIdle" value="1" />
<property name="maxActive" value="20" />
<!-- 配置获取连接等待超时的时间 -->
<property name="maxWait" value="30000" />
<!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->
<property name="timeBetweenEvictionRunsMillis" value="60000" />
<!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->
<property name="minEvictableIdleTimeMillis" value="300000" />
<property name="testWhileIdle" value="true" />
<!-- 这里建议配置为TRUE,防止取到的连接不可用 -->
<property name="testOnBorrow" value="true" />
<property name="testOnReturn" value="false" />
<!-- 打开PSCache,并且指定每个连接上PSCache的大小 -->
<property name="poolPreparedStatements" value="false" />
<!-- 验证连接有效与否的SQL,不同的数据配置不同 -->
<property name="validationQuery" value="select 1 " />
<!-- ******重要的配置:监控统计拦截的filters,日志记录 *start* ******-->
<!-- 并在filters属性中配置了log4j,ref中的bean是下面拦截器的配置 -->
<!-- proxyFilters和filters是组合关系,前者是根据bean配置,后者根据别名配置的-->
<!-- stat是statfilter的别名,log4j是logFilter的别名-->
<!-- 监控统计拦截的filters,日志记录,这里开启了stat,wall(sql翻过墙过滤监控),log4j(log4j的监控)配置 -->
<property name="proxyFilters">
<list>
<ref bean="statfilter" />
<ref bean="logFilter" />
</list>
</property>
<property name="filters" value="wall" />
<!-- *end* -->
</bean>
<!-- 慢SQL记录 *start* -->
<bean id="statfilter" class="com.alibaba.druid.filter.stat.StatFilter">
<!-- 开启合并sql -->
<property name="mergeSql" value="true" />
<!-- 开启慢查询语句,1秒 -->
<property name="slowSqlMillis" value="1000" />
<property name="logSlowSql" value="true" />
</bean>
<bean id="logFilter" class="com.alibaba.druid.filter.logging.Log4jFilter">
<!-- <property name="resultSetLogEnabled" value="false" /> -->
<!-- <property name="statementExecutableSqlLogEnable" value="true" /> -->
</bean>
<!-- 慢SQL记录 *end* -->
<!-- ========================================================-->
<!-- Session Factory -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="datasource"/>
<property name="mapperLocations" value="classpath:mapper/*mapper.xml"/>
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>
<property name="basePackage" value="cn.test.web.mapper"/>
</bean>
使用Filters进行监控统计:
上面的配置文件使用了两种类型的filter,别名配置是通过filters属性配置的,filters属性的类型是String。如:
<property name="filters" value="stat,log4j" />
如果需要通过bean的方式配置,使用proxyFilters属性。如:
<bean id="stat-filter" class="com.alibaba.druid.filter.stat.StatFilter">
<property name="slowSqlMillis" value="10000" />
<property name="logSlowSql" value="true" />
</bean>
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
init-method="init" destroy-method="close">
... ...
<property name="filters" value="log4j" />
<property name="proxyFilters">
<list>
<ref bean="stat-filter" />
</list>
</property>
</bean>
配置 wallfilter防止注入攻击<property name="filters" value="wall"/>
使用StatViewServlet展示监控页面:
<!-- 德鲁伊监控平台监控器 -->
<servlet>
<servlet-name>DruidStatView</servlet-name>
<servlet-class>com.alibaba.druid.support.http.StatViewServlet</servlet-class>
<init-param>
<!-- 允许清空统计数据 -->
<param-name>resetEnable</param-name>
<param-value>true</param-value>
</init-param>
<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.xml中配置后,可以在 http://host:port/projectName/druid/index.html 访问。
Web关联监控配置 :
在web.xml中配置
<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>
<!-- druid 0.2.7版本开始支持profile,配置profileEnable能够监控单个url调用的sql列表。-->
<init-param>
<param-name>profileEnable</param-name>
<param-value>true</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>DruidWebStatFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
结合Log4j:
# Druid
log4j.logger.druid.sql=info,A1,A2
log4j.logger.druid.sql.DataSource=info,A1,A2
log4j.logger.druid.sql.Connection=info,A1,A2
log4j.logger.druid.sql.Statement=info,A1,A2
log4j.logger.druid.sql.ResultSet=info,A1,A2
log4j.appender.A1=org.apache.log4j.DailyRollingFileAppender
log4j.appender.A1.File=../druid-logs/info_slowsql.log
log4j.appender.A1.DatePattern='.'yyyy-MM-dd
#log4j.appender.A1.File=${catalina.home}/logs/info_slowsql.log
#log4j.appender.A1.File=${WebApp.TomcatHome}/logs/info_slowsql.log
log4j.appender.A1.ImmediateFlush=true
log4j.appender.A1.Append=true
log4j.appender.A1.Threshold=info
log4j.appender.A1.layout=org.apache.log4j.PatternLayout
log4j.appender.A1.layout.ConversionPattern=[%d{yyyy-MM-dd HH:mm:ss}] [%p] [%c] - %m%n
log4j.appender.A2=org.apache.log4j.DailyRollingFileAppender
log4j.appender.A2.File=../druid-logs/warn_slowsql.log
log4j.appender.A2.DatePattern='.'yyyy-MM-dd
#log4j.appender.A2.File=${catalina.home}/logs/warn_slowsql.log
#log4j.appender.A2.File=${WebApp.TomcatHome}/logs/warn_slowsql.log
log4j.appender.A2.ImmediateFlush=true
log4j.appender.A2.Append=true
log4j.appender.A2.Threshold=warn
log4j.appender.A2.layout=org.apache.log4j.PatternLayout
log4j.appender.A2.layout.ConversionPattern=[%d{yyyy-MM-dd HH:mm:ss}] [%p] [%c] - %m%n