SpringBoot（5）数据库操作_JDBC和整合Druid

SpringBoot使用JDBC

1. 使用yml文件设置数据库信息

spring:
  datasource:
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://192.168.253.138:3306/jdbc?useUnicode=true&characterEncoding=utf8

2. 测试数据库连接状态

    @Autowired
    DataSource dataSource;

    @Test
    void contextLoads() throws SQLException {
        System.out.println(dataSource.getClass());

        Connection connection = dataSource.getConnection();
        System.out.println(connection);
        connection.close();
    }

运行结果：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5JykjnwE-1604317735576)(https://note.youdao.com/yws/api/personal/file/445D5E0D47CD4039AB37ABAE380E1A91?method=getImage&version=1740&cstk=2GVXnAFz)]
3. springBoot启动自动添加表
在配置文件中加上

spring.datasource.initialization-mode=always

在resource中添加department.sql
在配置文件中设置sql文件路径

spring:
  datasource:
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://192.168.253.138:3306/jdbc?useUnicode=true&characterEncoding=utf8
    schema:
      - classpath:department.sql
==注意：==  
==sql文件可以使用shema-all.sql名称就可以不需要配置路径自动执行== 

springBoot启动后就会执行department.sql
4. 在Controller中执行语句

    @Autowired
    JdbcTemplate jdbcTemplate;

    @ResponseBody
    @GetMapping("/query")
    public Map<String,Object> map(){
        List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from department");
        return list.get(0);
    }

发送请求：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aK5fjTop-1604317735577)(https://note.youdao.com/yws/api/personal/file/15DD97CCB4C74B1F948AB636742CBBD0?method=getImage&version=1779&cstk=6rWt7iwX)]

整合Druid和配置数据源监控

1. 导入Druid依赖

    <!--引入druid数据源-->
    <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.1.10</version>
    </dependency>

2. 修改application.yml配置文件

spring:
  datasource:
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://192.168.253.138:3306/jdbc?useUnicode=true&characterEncoding=utf8
    type: com.alibaba.druid.pool.DruidDataSource

    #   数据源其他配置
    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，去掉后监控界面sql无法统计，'wall'用于防火墙
    filters: stat,wall,log4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

3. 新建配置类DruidConfig

@Configuration
public class DruidConfig {

    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druid(){
        return new DruidDataSource();
    }

    //配置Druid的监控
    //1. 配置一个管理后台的servlet
    @Bean
    public ServletRegistrationBean StatViewServlet(){
        ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
        Map<String,String> inParams = new HashMap<>();

        inParams.put("loginUsername","admin");
        inParams.put("loginPassword","123456");
        inParams.put("allow","");//默认就是允许所有访问
        inParams.put("deny","172.21.58.225");//拒绝访问

        bean.setInitParameters(inParams);
        return bean;
    }

    //2. 配置一个filter
    @Bean
    public FilterRegistrationBean WebStatFilter(){
        FilterRegistrationBean<Filter> bean = new FilterRegistrationBean<>();
        bean.setFilter(new WebStatFilter());

        Map<String,String> inParams = new HashMap<>();
        inParams.put("exclusions","*.js,*.css,/druid/*");
        bean.setInitParameters(inParams);

        bean.setUrlPatterns(Arrays.asList("/*"));

        return bean;
    }

}

4. 发送http://localhost:8080/druid请求

出现登录界面，使用设置的用户名和密码登陆来到主页
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GdZts3tC-1604317735578)(https://note.youdao.com/yws/api/personal/file/A84B60B8F104493692BCDBE541CE6C79?method=getImage&version=1803&cstk=6rWt7iwX)]