Druid介绍
Druid是一个为监控而生的数据库连接池,高效、功能强大、可扩展性好,是阿里巴巴开源平台的一个项目。Druid是Java语言中最好的数据库连接池,Druid能够提供强大的监控和扩展功能.它可以替换DBCP和C3P0连接池。
Druid配置
spring: datasource: username: root password: 123456 url: jdbc:mysql://localhost:3306/study?useUmicode=true&charactorEncoding=utf-8 driver-class-name: com.mysql.cj.jdbc.Driver type: com.alibaba.druid.pool.DruidDataSource #公司要求,springboot默认不注入这些属性值 #druid数据源专有配置 initial-size: 5 min-idle: 5 max-active: 20 max-wait: 60000 time-between-eviction-runs-millis: 60000 min-evictable-idle-time-millis: 300000 validation-query: SELECT 1 FROM DUAL test-while-idle: true test-on-borrow: false test-on-return: false pool-prepared-statements: true #配置监控统计拦截的filters stat:监控统计;log4j日志记录;wall:防御sql注入 #如果允许时报错,java.long.ClassNotFoundException:org.apache.log4j.Priority #则导入log4j依赖即可 maven地址:https://mvnrepository.com/artifact/log4j/log4j filters: stat,wall,log4j max-pool-prepared-statement-per-connection-size: 20 useGlobaDataSourceStat: true connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
Druid使用
配置后台监控功能+过滤器
package com.dong.config; import com.alibaba.druid.pool.DruidDataSource; import com.alibaba.druid.support.http.StatViewServlet; import com.alibaba.druid.support.http.WebStatFilter; import org.springframework.boot.context.properties.ConfigurationProperties; 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 javax.servlet.Filter; import javax.sql.DataSource; import java.util.HashMap; @Configuration public class DruidConfig { @Bean @ConfigurationProperties(prefix = "spring.datasource") public DataSource druidDataSource(){ return new DruidDataSource(); } //配置后台监控功能,约等web.xml //因为springboot内置了servlet容器,所以没有xml文件,替代方法ServletRegistrationBean @Bean public ServletRegistrationBean statViewServlet(){ ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*"); //后台需要有人登录 //账号密码配置 HashMap<String,String> initParameters = new HashMap<>(); //增加配置 initParameters.put("loginUsername","admin");//登录的key是固定的loginUsername loginPassword initParameters.put("loginPassword","123456"); //允许谁能访问 //空着代表谁都可以访问,写谁谁才可以访问 initParameters.put("allow",""); //禁止谁访问initParameters.put("dong","192.168.11.123"); bean.setInitParameters(initParameters);//设置初始化参数 return bean; } //filter,过滤器 @Bean public FilterRegistrationBean webStatFilter(){ FilterRegistrationBean<Filter> bean = new FilterRegistrationBean<>(); bean.setFilter(new WebStatFilter()); //可以过滤那些请求 HashMap<String, String> initParameters = new HashMap<>(); //这些不进行统计 initParameters.put("exclusions","*.js,*.css,/druid/*"); bean.setInitParameters(initParameters); return bean; } }
对数据库中数据进行增删改查
package com.dong.controller; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PathVariable; import org.springframework.web.bind.annotation.RestController; import java.util.List; import java.util.Map; @RestController public class JDBCController { @Autowired JdbcTemplate jdbcTemplate; //查询数据库的所有信息 @GetMapping("/userList") public List<Map<String,Object>> userList(){ String sql = "select * from user"; List<Map<String, Object>> list_maps = jdbcTemplate.queryForList(sql); return list_maps; } //增加用户 @GetMapping("/adduser") public String addUser(){ String sql = "insert into study.user(id,name,height) values('4','大宝','171')"; jdbcTemplate.update(sql); return "adduser-ok"; } // @GetMapping("/updateuser/{id}") public String updateUser(@PathVariable("id") int id){ String sql = "update user set name = ?,height=? where id ="+id; //封装 Object[] objects = new Object[2]; objects[0] = "小宝"; objects[1] = "172"; jdbcTemplate.update(sql,objects); return "updateuser-ok"; } // @GetMapping("/deleteuser/{id}") public String deleteUser(@PathVariable("id") int id){ String sql = "delete from user where id = ?"; jdbcTemplate.update(sql,id); return "deleteuser-ok"; } }
报错:连接数据库报错Server returns invalid timezone. Need to set ‘serverTimezone’ property
原因:MySQL驱动中默认时区是UTC,与本地时间(中国)相差八个小时,所以链接不上。可以用两种方法解决,都是解决时区问题。
解决:1. 点开最右侧 Advanced,找到 serverTimezone,在右侧value处填写 GMT,保存即可!(或填写 Asia/Shanghai)2. 也可以在url后添加:?serverTimezone=GMT3. MySQL驱动 8.0.x新版本的需要设置时区,5.1.x 版本的不需要设置时区,可以直接选择5.1.x的版本省掉时区设置(根据需要选择)