Spring boot整合mysql和druid

落日映苍穹つ 2022-04-14 03:51 204阅读 0赞

# Spring boot整合mysql和druid #

项目源码github:[https://github.com/superRabbitMan/spring-boot-mysql][https_github.com_superRabbitMan_spring-boot-mysql]

## 集成mysql数据库 ##

### 引入依赖 ###

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
    </dependency>
    
    <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>

### 添加数据库配置 ###

Spring boot项目的resources目录下的applicataion.properties

# datasource
    spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8
    spring.datasource.username=root
    spring.datasource.password=root
    spring.datasource.driver-class-name=com.mysql.jdbc.Driver

### 设计数据库表和实体 ###

create table user(
    id bigint primary key auto_increment,
    name varchar(10) not null ,
    age int default 0
    );
    
    insert into user (name, age) VALUES ('jack', 12);

public class User {
    
    private int id;//id
    
    private String name;//姓名
    
    private int age;//年龄
    
    //get set省略
    
    @Override
    public String toString() {
    return "User{"+
    "id="+ id +
    ", name='"+ name + '\'' +
    ", age="+ age +
    '}';
        }
    }

### 集成测试 ###

在test文件夹下的SpringBootApplication.java中编写测试类

@RunWith(SpringRunner.class)
    @SpringBootTest
    public class SpringBootMysqlApplicationTests {
    
    @Resource
    private JdbcTemplate jdbcTemplate;
    
    @Test
    public void contextLoads() {
          String sql = "select id, name, age from user";
          List<User> userList = jdbcTemplate.query(sql, new RowMapper<User>() {
    @Override
    public User mapRow(ResultSet resultSet, int i) throws SQLException {
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setName(resultSet.getString("name"));
                user.setAge(resultSet.getInt("age"));
    return user;
             }
          });
          System.out.println("----------查询结果如下：----------");
    //下面使用JDK8的新特性，如果没有安装JDK8的可以修改为普通的for循环
    userList.stream().forEach(user -> {System.out.println(user.toString());});
       }
    
    }

## 集成Druid ##

Druid官网：[https://github.com/alibaba/druid][https_github.com_alibaba_druid]

### 引入依赖 ###

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.10</version>
    </dependency>

### 添加配置 ###

# druid,更多属性可以看官网
    spring.datasource.druid.initial-size=5
    spring.datasource.druid.max-active=20
    spring.datasource.druid.min-idle=5
    spring.datasource.druid.max-wait=60000
    spring.datasource.druid.filter.config.enabled=true
    # 配置StatFilter
    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
    #
    # 配置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

### 开启Druid功能 ###

@Configuration
    public class DruidConfiguration {
    
    @Bean
    public ServletRegistrationBean statViewServle() {
            ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
    //IP白名单
    //servletRegistrationBean.addInitParameter("allow", "127.0.0.1, 127.0.0.1");
            //IP黑名单
    //servletRegistrationBean.addInitParameter("deny", "127.0.0.1, 127.0.0.1");
    servletRegistrationBean.addInitParameter("loginUsername", "druid");
            servletRegistrationBean.addInitParameter("loginPassword", "12345678");
    //是否允许重置数据
    servletRegistrationBean.addInitParameter("resetEnable", "false");
    return servletRegistrationBean;
        }
    
    @Bean
    public FilterRegistrationBean druidStatFilter() {
            FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
    //添加过滤规则
    filterRegistrationBean.addUrlPatterns("/*");
    //忽略过滤的格式
    filterRegistrationBean.addInitParameter("exclusions", "*.js, *.jpg, *.png, *.css, /druid/*");
    return filterRegistrationBean;
        }
    
    }

### 浏览效果 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzMyMzY2MzI5_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzMyMzY2MzI5_size_16_color_FFFFFF_t_70 1][]

[https_github.com_superRabbitMan_spring-boot-mysql]: https://github.com/superRabbitMan/spring-boot-mysql
[https_github.com_alibaba_druid]: https://github.com/alibaba/druid
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzMyMzY2MzI5_size_16_color_FFFFFF_t_70]: /images/20220414/51b12a2877a34ea584e59237bd26851b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzMyMzY2MzI5_size_16_color_FFFFFF_t_70 1]: /images/20220414/c477b97476f546039655001abaf6008d.png