#datasource

#Introductions: https://github.com/alibaba/druid/wiki/DruidDataSource%E9%85%8D%E7%BD%AE%E5%B1%9E%E6%80%A7%E5%88%97%E8%A1%A8

#https://github.com/alibaba/druid/wiki/%E9%85%8D%E7%BD%AE_DruidDataSource%E5%8F%82%E8%80%83%E9%85%8D%E7%BD%AE

#初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时 default:0

spring.datasource.druid.initial-size=2

#最大连接池数量。default=8+

spring.datasource.druid.max-active=20

#最小连接池数量。maxIdle已经废弃

spring.datasource.druid.min-idle=10

#获取连接时最大等待时间，单位毫秒。配置了maxWait之后，缺省启用公平锁，并发效率会有所下降，如果需要可以通过配置useUnfairLock属性为true使用非公平锁

spring.datasource.druid.max-wait=60000

#是否缓存preparedStatement,也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭。default=false

spring.datasource.druid.pool-prepared-statements=false

#要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。在Druid中，会存在Oracle下PSCache占用内存过多的问题，可以把这个数据配置大一些，比如100.default=-1

spring.datasource.druid.max-pool-prepared-statement-per-connection-size=-1

#用来检测连接是否有效的sql，要求是一个查询语句，常用select 'x'。如果validationQuery为null,testOnBorrow,testOnBorrow,testOnReturn,testWhileIdle都不会起作用。这个可以不配置

#spring.datasource.druid.validation-query=select 'x'

#单位:秒，检测连接是否有效的超时时间。底层调用jdbc Statement对象的void. setQueryTImeout(int seconds)方法,mysql实现的不是很合理，不建议在mysql下配置此参数

#spring.datasource.druid.validation-query-timeout=60

#申请连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能。default=true

spring.datasource.druid.test-on-borrow=false

#归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。default=false

spring.datasource.druid.test-on-return=false

#建议配置为true,不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。default=false

spring.datasource.druid.test-while-idle=true

#连接池中的minIdle数据以内的连接，空闲时间超过minEvictableIdleTimeMillis，则会执行keepAlive操作。default=false

spring.datasource.druid.keep-alive=true

#配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 default=1分钟

#有两个含义：

# (1)Destroy线程会检测连接的间隔时间，如果连接空闲时间大于等于minEvictableIdleTimeMillis则关闭物理连接

# (2)testWhileIdle的判断依据，详细看testWhileIdle属性的说明

spring.datasource.druid.time-between-eviction-runs-millis=60000

#池中的连接保持空闲而不被驱逐的最小时间，单位是毫秒

spring.datasource.druid.min-evictable-idle-time-millis=300000

#合并多个DruidDataSource的监控数据

spring.datasource.druid.use-global-data-source-stat=true

#spring.datasource.druid.filters=#配置多个英文逗号分隔

# 配置StatFilter

spring.datasource.druid.filter.stat.enabled=true

spring.datasource.druid.filter.stat.db-type=mysql

spring.datasource.druid.filter.stat.log-slow-sql=true

spring.datasource.druid.filter.stat.slow-sql-millis=2000

spring.datasource.druid.filter.stat.merge-sql=true

# 配置WallFilter

spring.datasource.druid.filter.wall.enabled=true

spring.datasource.druid.filter.wall.db-type=mysql

spring.datasource.druid.filter.wall.config.delete-allow=false

spring.datasource.druid.filter.wall.config.drop-table-allow=false

spring.datasource.druid.filter.wall.config.create-table-allow=false

spring.datasource.druid.filter.wall.config.alter-table-allow=false

spring.datasource.druid.filter.wall.config.truncate-allow=false

select * from t where id = 1

select * from t where id = 2

select * from t where id = 3

select * from t where id = ?

<bean id="stat-filter" class="com.alibaba.druid.filter.stat.StatFilter">

    <property name="mergeSql" value="true" />

</bean>

spring.datasource.druid.filters=config,stat,wall,log4j 配置监控统计拦截的filters，去掉后监控界面SQL无法进行统计，’wall’用于防火墙

# WebStatFilter配置，说明请参考Druid Wiki，配置_配置WebStatFilter

spring.datasource.druid.web-stat-filter.enabled= #是否启用StatFilter默认值false

spring.datasource.druid.web-stat-filter.url-pattern=

spring.datasource.druid.web-stat-filter.exclusions=

spring.datasource.druid.web-stat-filter.session-stat-enable=

spring.datasource.druid.web-stat-filter.session-stat-max-count=

spring.datasource.druid.web-stat-filter.principal-session-name=

spring.datasource.druid.web-stat-filter.principal-cookie-name=

spring.datasource.druid.web-stat-filter.profile-enable=

# StatViewServlet配置，说明请参考Druid Wiki，配置_StatViewServlet配置

spring.datasource.druid.stat-view-servlet.enabled= #是否启用StatViewServlet（监控页面）默认值为false（考虑到安全问题默认并未启动，如需启用建议设置密码或白名单以保障安全）

spring.datasource.druid.stat-view-servlet.url-pattern=

spring.datasource.druid.stat-view-servlet.reset-enable=

spring.datasource.druid.stat-view-servlet.login-username=

spring.datasource.druid.stat-view-servlet.login-password=

spring.datasource.druid.stat-view-servlet.allow=

spring.datasource.druid.stat-view-servlet.deny=

# Spring监控配置，说明请参考Druid Github Wiki，配置_Druid和Spring关联监控配置

spring.datasource.druid.aop-patterns= # Spring监控AOP切入点，如x.y.z.service.*,配置多个英文逗号分隔

# 配置StatFilter

spring.datasource.druid.filter.stat.enabled=true

spring.datasource.druid.filter.stat.db-type=h2

spring.datasource.druid.filter.stat.log-slow-sql=true

spring.datasource.druid.filter.stat.slow-sql-millis=2000

# 配置WallFilter

spring.datasource.druid.filter.wall.enabled=true

spring.datasource.druid.filter.wall.db-type=h2

spring.datasource.druid.filter.wall.config.delete-allow=false

spring.datasource.druid.filter.wall.config.drop-table-allow=false

# 其他 Filter 配置不再演示

druid.filters.log4j=com.alibaba.druid.filter.logging.Log4jFilter

druid.filters.log4j2=com.alibaba.druid.filter.logging.Log4j2Filter

druid.filters.slf4j=com.alibaba.druid.filter.logging.Slf4jLogFilter

druid.filters.commonlogging=com.alibaba.druid.filter.logging.CommonsLogFilter

druid.filters.commonLogging=com.alibaba.druid.filter.logging.CommonsLogFilter

  <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"

      init-method="init" destroy-method="close">

      ... ...

      <property name="filters" value="stat,log4j" />

  </bean>

  public abstract class LogFilter {

      protected String          dataSourceLoggerName                 = "druid.sql.DataSource";

      protected String          connectionLoggerName                 = "druid.sql.Connection";

      protected String          statementLoggerName                  = "druid.sql.Statement";

      protected String          resultSetLoggerName                  = "druid.sql.ResultSet";

  }

<bean id="log-filter" class="com.alibaba.druid.filter.logging.Log4jFilter">

    <property name="resultSetLogEnabled" value="false" />

</bean>

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">

    ...

    <property name="proxyFilters">

        <list>

            <ref bean="log-filter"/>

        </list>

    </property>

</bean>

log4j.logger.druid.sql=warn,stdout

log4j.logger.druid.sql.DataSource=warn,stdout

log4j.logger.druid.sql.Connection=warn,stdout

log4j.logger.druid.sql.Statement=warn,stdout

log4j.logger.druid.sql.ResultSet=warn,stdout

-Ddruid.log.stmt.executableSql=true

  <bean id="log-filter" class="com.alibaba.druid.filter.logging.Log4jFilter">

        <property name="statementExecutableSqlLogEnable" value="true" />

  </bean>

<update id="updateBatch" parameterType="java.util.List">

        <foreach collection="list" item="item" index="index" open="" close="" separator=";">

                update device_bd_token

                <set>

                access_token=#{item.accessToken}

                </set>

                where device_id = #{item.deviceId}

         </foreach>

    </update>  

jdbc.url=jdbc:mysql://192.168.11.107:3306/alarm_db?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8  

<bean id="dataSourceOne" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">

        <property name="proxyFilters">

            <list>

                <ref bean="stat-filter" />

                <ref bean="wall-filter"/>

            </list>

        </property>

    </bean>  

<bean id="wall-filter" class="com.alibaba.druid.wall.WallFilter">

    <property name="config" ref="wall-config" />

</bean>  

<bean id="wall-config" class="com.alibaba.druid.wall.WallConfig">

     <property name="multiStatementAllow" value="true" />

</bean>  

    public void addRemoteAddress(String ip) {

        if (remoteAddresses == null) {

            this.remoteAddresses = ip;

            return;

        }

        if (remoteAddresses.contains(ip)) {

            return;

        }

        if (remoteAddresses.length() > 256) {

            LOG.error("session ip change too many");

            return;

        }

        remoteAddresses += ';' + ip;

    }

package com.alibaba.druid.util;

import javax.servlet.GenericServlet;

import javax.servlet.ServletContext;

import javax.servlet.http.HttpServletRequest;

public class DruidWebUtils {

    public static String getRemoteAddr(HttpServletRequest request) {

        String ip = request.getHeader("x-forwarded-for");

        if (ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {

            ip = request.getHeader("Proxy-Client-IP");

        }

        if (ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {

            ip = request.getHeader("WL-Proxy-Client-IP");

        }

        if (ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {

            ip = request.getRemoteAddr();

        }

        return ip;

    }

  <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>

<init-param>

    <param-name>exclusions</param-name>

    <param-value>*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*</param-value>

</init-param>

<init-param>

    <param-name>sessionStatMaxCount</param-name>

    <param-value>1000</param-value>

</init-param>

<init-param>

    <param-name>sessionStatEnable</param-name>

    <param-value>false</param-value>

</init-param>

<init-param>

    <param-name>principalSessionName</param-name>

    <param-value>xxx.user</param-value>

</init-param>

<init-param>

    <param-name>principalCookieName</param-name>

    <param-value>xxx.user</param-value>

</init-param>

<init-param>

    <param-name>profileEnable</param-name>

    <param-value>true</param-value>

</init-param>