druid monitor 监控部署(SQL监控 Spring监控)

• 导入依赖

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

•    数据源配置

<bean id="druidDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
....(省略数据库基本信息配置)   
 <property name="proxyFilters">
            <list>
                <ref bean="stat-filter"/>
                <ref bean="log-filter"/>
            </list>
    </property>
 </bean>

<!-- druid spring monitor setting -->
    <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>包路径.*</value>
            </list>
        </property>
    </bean>

    <aop:config proxy-target-class="true">
        <aop:advisor advice-ref="druid-stat-interceptor" pointcut-ref="druid-stat-pointcut"/> <!--druid-stat-pointcut会显示报错,不影响运行-->
    </aop:config>
    <!-- 慢SQL记录 -->
    <bean id="stat-filter" class="com.alibaba.druid.filter.stat.StatFilter">
        <!-- 慢sql时间设置,即执行时间大于200毫秒的都是慢sql -->
        <property name="slowSqlMillis" value="200"/>
        <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>

   

• web.xml配置(用户名账户密码自己设置)

 <!-- 配置druid sql监控 -->
    <filter>
        <filter-name>DruidWebStatFilter</filter-name>
        <filter-class>com.alibaba.druid.support.http.WebStatFilter</filter-class>
        <init-param>
            <!-- 经常需要排除一些不必要的url，比如.js,/jslib/等等。配置在init-param中 -->
            <param-name>exclusions</param-name>
            <param-value>*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*</param-value>
        </init-param>
        <!-- 缺省sessionStatMaxCount是1000个。你可以按需要进行配置 -->
        <init-param>
            <param-name>sessionStatMaxCount</param-name>
            <param-value>1000</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>
        <init-param>
            <param-name>principalSessionName</param-name>
            <param-value>users.username</param-value>
        </init-param>
        <!-- 你可以关闭session统计功能-->
        <!--<init-param>
          <param-name>sessionStatEnable</param-name>
          <param-value>true</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>resetEnable</param-name>
            <param-value>true</param-value>
        </init-param>
        <init-param>
            <!-- 用户名 -->
            <param-name>loginUsername</param-name>
            <param-value>account</param-value>
        </init-param>
        <init-param>
            <!-- 密码 -->
            <param-name>loginPassword</param-name>
            <param-value>account</param-value>
        </init-param>
    </servlet>
    <servlet-mapping>
        <servlet-name>DruidStatView</servlet-name>
        <url-pattern>/druid/*</url-pattern>
    </servlet-mapping>

• log4j.properties输出配置

#定义根日志级别和输出端(定义了两个输出端) 
log4j.rootLogger=ERROR,CONSOLE,DAILY_ROLLING_FILE,druid

#定义第一个输出端，输出到控制台  
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.Threshold=debug
log4j.appender.CONSOLE.Target=System.out
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern=[%-5p] %d{yyyy-MM-dd HH:mm:ss,SSS} [%c{1}:%L] %m%n

#定义第二个输出端，输出到文件  
log4j.appender.DAILY_ROLLING_FILE=org.apache.log4j.DailyRollingFileAppender
log4j.appender.DAILY_ROLLING_FILE.Append=true
log4j.appender.DAILY_ROLLING_FILE.Threshold=debug
log4j.appender.DAILY_ROLLING_FILE.Encoding=UTF-8
#${***.log.dir}环境变量 jvm tomcat可配置 (***自己配置)
log4j.appender.DAILY_ROLLING_FILE.File=${***.log.dir}/account-log.txt
log4j.appender.DAILY_ROLLING_FILE.DatePattern='.'yyyy-MM-dd
log4j.appender.DAILY_ROLLING_FILE.layout=org.apache.log4j.PatternLayout
log4j.appender.DAILY_ROLLING_FILE.layout.ConversionPattern=[%-5p] %d{yyyy-MM-dd HH:mm:ss,SSS} [%c{1}]:%L %m%n

# Druid
log4j.logger.druid.sql=WARN,druid
log4j.logger.druid.sql.DataSource=WARN,druid
log4j.logger.druid.sql.Connection=WARN,druid
log4j.logger.druid.sql.Statement=WARN,druid

log4j.appender.druid=org.apache.log4j.DailyRollingFileAppender
log4j.appender.druid.layout=org.apache.log4j.PatternLayout
log4j.appender.druid.layout.ConversionPattern= [%-5p] %d{yyyy-MM-dd HH:mm:ss,SSS} [%c{1}]:%L %m%n
log4j.appender.druid.datePattern='.'yyyy-MM-dd
log4j.appender.druid.Threshold = WARN
log4j.appender.druid.append=true
log4j.appender.druid.File=${***.log.dir}/druid-account-slow-sql.log

• 配置好以后启动项目

1. 访问方式  http://ip:port/项目名/druid/index.html
2. 输入用户名密码(account/account)就可以进入sql监控页面(如下图)3.切换进入spring监控页面