SpringBoor整合JDBC与Druid
创建maven项目导入依赖整合JDBC
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
配置数据库yml
datasource:
username: root
password: ******
#?serverTimezone=UTC解决时区的报错
url: jdbc:mysql://localhost:3306/******?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
driver-class-name: com.mysql.cj.jdbc.Driver
编写测试增删改查
//JDBC
@Autowired
private JdbcTemplate jdbcTemplate;
@Test
void JDBCTest1(){
String sql="Select * from ***";
List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
System.out.println(maps.toString());
}
@Test
void JDBCTest2(){
String sql="insert into ***() values ()";
int update = jdbcTemplate.update(sql);
System.out.println(update);
}
@Test
void JDBCTest3(){
String sql="update *** set ***=*** where ***=***;";
int update = jdbcTemplate.update(sql);
System.out.println(update);
}
@Test
void JDBCTest4(){
String sql="delete from *** where *** =***";
int update = jdbcTemplate.update(sql);
System.out.println(update);
}
整合Druid
导入依赖
<!-- druid数据源 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
<!--log4j -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
</dependency>
编写配置yml
datasource:
username: root
password: ******
#?serverTimezone=UTC解决时区的报错
url: jdbc:mysql://localhost:3306/***?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
# url: jdbc:mysql://localhost:3306/db01?useUnicode=true&characterEncoding=utf8
driver-class-name: com.mysql.cj.jdbc.Driver
#设置数据源
type: com.alibaba.druid.pool.DruidDataSource
# Druid数据源配置
druid:
#Spring Boot 默认是不注入这些属性值的,需要自己绑定
#druid 数据源专有配置
initialSize: 5
minIdle: 5
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
#配置监控统计拦截的filters,stat:监控统计、log4j:日志记录、wall:防御sql注入
filters: stat,wall,log4j
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
编写测试类
@Test
public void contextLoads() throws SQLException {
//看一下默认数据源
System.out.println(dataSource.getClass());
//获得连接
Connection connection = dataSource.getConnection();
System.out.println(connection);
DruidDataSource druidDataSource = (DruidDataSource) dataSource;
System.out.println("druidDataSource 数据源最大连接数:" + druidDataSource.getMaxActive());
System.out.println("druidDataSource 数据源初始化连接数:" + druidDataSource.getInitialSize());
//关闭连接
connection.close();
}
可以看到数据源已经更换为阿里巴巴Druid
最大连接数20 初始化连接数5
编写DruidConfig
@Configuration
public class DruidConfig {
/*
将自定义的 Druid数据源添加到容器中,不再让 Spring Boot 自动创建
绑定全局配置文件中的 druid 数据源属性到 com.alibaba.druid.pool.DruidDataSource从而让它们生效
@ConfigurationProperties(prefix = "spring.datasource"):作用就是将 全局配置文件中
前缀为 spring.datasource的属性值注入到 com.alibaba.druid.pool.DruidDataSource 的同名参数中
*/
//1 创建一个数据源
@ConfigurationProperties(prefix = "spring.datasource") //绑定配置文件的属性
@Bean //注入到spring容器中
public DataSource druid(){
return new DruidDataSource();
}
//2 配置druid的监控(配置管理后台servlet)
@Bean
public ServletRegistrationBean StatViewServlet(){
ServletRegistrationBean bean = new
ServletRegistrationBean(new StatViewServlet(),
"/druid/*");
Map<String,String>map = new HashMap<>();
map.put("loginUsername","*****"); //登陆用户名
map.put("loginPassword","*****"); //登陆密码
//(不写就是允许所有访问)
// map.put("allow",""); //允许登陆ip
// map.put("deny",""); //拒绝登陆地址
bean.setInitParameters(map); //设置初始化参数(值是一个String map集合)
return bean;
}
//2、配置一个web监控的filter
@Bean
public FilterRegistrationBean webFilter(){
FilterRegistrationBean filterRegistrationBean=new FilterRegistrationBean();
filterRegistrationBean.setFilter(new WebStatFilter());
Map<String,Object> initParams=new HashMap<>();
initParams.put("exclusions","*.js,*.css,/druid/*");
filterRegistrationBean.setInitParameters(initParams);
filterRegistrationBean.setUrlPatterns(Arrays.asList("/*"));
return filterRegistrationBean;
}
}
访问/druid/会自动跳转到/druid/login.html(登陆报空指针的看看自己的配置注解加了没)
编写jdbc测试接口
@GetMapping("/jdbc")
List<Map<String,Object>> JDBCTest(){
String sql="Select * from ****";
List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
System.out.println(maps.toString());
return maps;
}
访问接口然后去Druid监控查看
能看到我们执行的sql语句和一些信息,还可以去其他页面查看我们需要的数据
遇到的问题
访问接口后监控没反应,这时候查看数据源选项,查看filter类名是否为空
问题出现原因:因为强迫症原因,导入的druid依赖在yml中会出现无法识别报黄,添加了一层diuid来解决报黄。又因为配置文件@ConfigurationProperties(prefix = “spring.datasource”) 导入不符,出现了找不到filter类的问题
解决方法1:吧druid层删掉,修改为第二张图片的样子(报黄就凑合看吧)
解决方法2:在配置类中设置属性
@Bean //注入到spring容器中
@ConfigurationProperties(prefix = "spring.datasource") //绑定配置文件的属性
public DataSource druid(){
DruidDataSource dds = new DruidDataSource();
//此处只做示范、具体根据自己配置
dds.setInitialSize(5);
return dds;
}