Druid连接池使用log4j监控sql

一、pom.xml

<properties>
		<maven.compiler.source>1.8</maven.compiler.source>
		<maven.compiler.target>1.8</maven.compiler.target>
		<maven.compiler.compilerVersion>1.8</maven.compiler.compilerVersion>
	</properties>

	<!-- 继承spring-boot父类，主要是用于管理版本号,每次集成是只需要加starter -->
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.6.RELEASE</version>
	</parent>
	
	<!-- spring-boot继承springmvc -->
	<dependencies>
		<!-- 集成springmvc -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<!-- 集成redis -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-redis</artifactId>
		</dependency>

		<!-- 集成数据库默认c3p0 -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

		<!-- 引入mysql驱动 -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>

		<!-- 集成druid连接池 -->
		<!-- <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> 
			<version>1.1.10</version> </dependency> -->

		<!-- <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> 
			<version>1.2.17</version> 0/dependency> -->

		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid-spring-boot-starter</artifactId>
			<version>1.1.10</version>
		</dependency>
		
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
			<optional>true</optional>
		</dependency>

		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.1.1</version>
		</dependency>
		
	</dependencies>

二、application.properties

属性可在:https://spring.io/projects/spring-boot#learn 里找。

spring.redis.host=192.168.0.198
spring.redis.password=123456
spring.datasource.url=jdbc:mysql://localhost/unit02
spring.datasource.username=root
spring.datasource.password=ps123456
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.stat-view-servlet.login-username=xf
spring.datasource.druid.stat-view-servlet.login-password=xm
spring.datasource.druid.filter.stat.slow-sql-millis=2000

三、程序入口

package cn.ps;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.web.bind.annotation.RestController;

@RestController
@SpringBootApplication
public class Example {
    public static void main(String[] args) throws Exception {
        SpringApplication.run(Example.class, args);
    }
}

四、测试Sql

package cn.ps.contorller;

import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class JdbcController {
	
	@Autowired
	private JdbcTemplate jtl;
	
	@GetMapping("/jdbc")
	public List<Map<String, Object>> jdbc() {
		List<Map<String, Object>> query = jtl.query("select * from user_t", new ColumnMapRowMapper());
		return query;
	}
	
	@GetMapping("/jdbc1")
	public List<Map<String, Object>> jdbc1() {
		List<Map<String, Object>> query = jtl.query("select * from emp", new ColumnMapRowMapper());
		return query;
	}
	
}

五、登录查看

1. 输入：IP地址:端口号/druid/login.html

2. 填写账号密码，没设置的直接进
   

3. 访问：
   http://192.168.0.198:8080/jdbc
   http://192.168.0.198:8080/jdbc1

4. 结果：
   

六、手动配置log4j

package cn.ps.conf;

import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;

 /**
  * 可以理解为 这个类就是一个spring的配置文件
 * 	<bean id="dataSource" (就是方法名) class="com.alibaba.druid.pool.DruidDataSource"></bean>
  * @author Administrator
  *
  */
@Configuration
public class ConfBean {
	
	@Value("${spring.datasource.driver-class-name}")
	private String driverClass;
	
	@Value("${spring.datasource.url}")
	private String url;
	
	@Value("${spring.datasource.username}")
	private String username;
	
	@Value("${spring.datasource.password}")
	private String password;
	
	@Bean
	public 	DataSource dataSource() {
		DruidDataSource dds = null;
		try {
			dds = new DruidDataSource();
			dds.setDriverClassName(driverClass);
			dds.setUrl(url);
			dds.setUsername(username);
			dds.setPassword(password);
			dds.setFilters("stat, wall, log4j");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dds;
	}
	
 	@Bean
    public ServletRegistrationBean statViewServlet(){
        //创建servlet注册实体
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(),"/druid/*");
        //设置ip白名单
        servletRegistrationBean.addInitParameter("allow","127.0.0.1");
        //设置ip黑名单，如果allow与deny共同存在时,deny优先于allow
        servletRegistrationBean.addInitParameter("deny","192.168.0.19");
        //设置控制台管理用户
        servletRegistrationBean.addInitParameter("loginUsername","druid");
        servletRegistrationBean.addInitParameter("loginPassword","123456");
        //是否可以重置数据
        servletRegistrationBean.addInitParameter("resetEnable","false");
        return servletRegistrationBean;
    }
 	
    @Bean
    public FilterRegistrationBean statFilter(){
        //创建过滤器
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
        //设置过滤器过滤路径
        filterRegistrationBean.addUrlPatterns("/*");
        //忽略过滤的形式
        filterRegistrationBean.addInitParameter("exclusions","*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }
}

七、redis操作

package cn.ps.contorller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class RedisContorller {
	
	@Autowired
	private StringRedisTemplate srt;
	
	@GetMapping("/redis")
	public String redis() {
		srt.boundValueOps("re").set("redis");
		return srt.boundValueOps("re").get();
	}
}