Druid使用

最新推荐文章于 2024-05-26 08:32:52 发布

CGSX

最新推荐文章于 2024-05-26 08:32:52 发布

阅读量2.7k

点赞数 1

分类专栏： javaweb学习文章标签： druid springboot 数据库

小菜鸟博客出品，可学习，转载，改动，不可抄袭，商用

本文链接：https://blog.csdn.net/s1547156325/article/details/101123956

版权

javaweb学习专栏收录该内容

16 篇文章 0 订阅

订阅专栏

文章目录

Druid使用

Druid使用

简介

Druid是java中的数据库连接池，是阿里巴巴的一个开源项目。Druid还提供了实时监控功能，有数据源监控，sql监控，sql防火墙，web应用监控等。下面是springboot使用jpa连接mysql，然后使用Druid数据库连接池

pom文件依赖

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
        </dependency>

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

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

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

properties文件配置

Druid配置有很多，关于连接池的一些配置

spring.datasource.url=jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#使用druid的话，需要多配制一个属性spring.datasource.type
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

##连接池的配置信息
# 初始化大小，最小或最大
spring.datasource.druid.initial-size=5
spring.datasource.druid.min-idle=5
spring.datasource.druid.max-active=20
##配置获取连接池等待超时的时间
spring.datasource.druid.max-wait=60000
##配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.druid.time-between-eviction-runs-millis=60000
#配置一个连接池中最小生存时间，单位是毫秒
spring.datasource.druid.min-evictable-idle-time-millis=300000
spring.datasource.druid.validation-query=SELECT 1 FROM DUAL
spring.datasource.druid.test-while-idle=true
spring.datasource.druid.test-on-borrow=false
spring.datasource.druid.test-on-return=false
#打开PSCache，并且指定每个连接上PSCache的大小
spring.datasource.druid.pool-prepared-statements=true
spring.datasource.druid.max-pool-prepared-statement-per-connection-size=20
#配置监控统计拦截的filters，去掉后监控界面sql无法统计，wall用于防火墙
spring.datasource.druid.filters=stat,wall

##JPA配置
## validate 加载hibernate时，验证创建数据表结构
## create 每次加载hibernate，重新创建数据表结构，这会导致数据表数据丢失
## create-drop 加载hibernate时创建，退出时删除表结构
## update  加载hibernate自动更新数据库结构
## none   启动时不做任何操作
spring.jpa.hibernate.ddl-auto=create
## 控制台打印
spring.jpa.show-sql=true

Druid工具类

Druid还需要一些工具类才能使用
DruidConfig.java

@Configuration
public class DruidConfig {
    @Value("${spring.datasource.url}")
    private String dbUrl;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;

    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;

    @Value("${spring.datasource.druid.initial-size}")
    private  int initialSize;

    @Value("${spring.datasource.druid.min-idle}")
    private int minIdle;

    @Value("${spring.datasource.druid.max-active}")
    private int maxActive;

    @Value("${spring.datasource.druid.max-wait}")
    private int maxWait;

    @Value("${spring.datasource.druid.time-between-eviction-runs-millis}")
    private int timeBetweenEvictionRunsMillis;

    @Value("${spring.datasource.druid.min-evictable-idle-time-millis}")
    private int minRvictableIdleTimeMillis;

    @Value("${spring.datasource.druid.validation-query}")
    private String validationQuery;

    @Value("${spring.datasource.druid.test-while-idle}")
    private boolean testWhileIdle;

    @Value("${spring.datasource.druid.test-on-borrow}")
    private boolean testOnBorrow;

    @Value("${spring.datasource.druid.test-on-return}")
    private boolean testOnReturn;

    @Value("${spring.datasource.druid.pool-prepared-statements}")
    private boolean poolPreparedStatements;

    @Value("${spring.datasource.druid.max-pool-prepared-statement-per-connection-size}")
    private int maxPoolPreparedStatementPerConnectionSize;

    @Value("${spring.datasource.druid.filters}")
    private String filters;
    

    @Bean
    @Primary
    public DataSource dataSource(){
        DruidDataSource dataSource = new DruidDataSource();

        dataSource.setUrl(this.dbUrl);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setDriverClassName(driverClassName);

        dataSource.setInitialSize(initialSize);
        dataSource.setMinIdle(minIdle);
        dataSource.setMaxActive(maxActive);
        dataSource.setMaxWait(maxWait);
        dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        dataSource.setMinEvictableIdleTimeMillis(minRvictableIdleTimeMillis);
        dataSource.setValidationQuery(validationQuery);
        dataSource.setTestWhileIdle(testWhileIdle);
        dataSource.setTestOnBorrow(testOnBorrow);
        dataSource.setTestOnReturn(testOnReturn);
        dataSource.setPoolPreparedStatements(poolPreparedStatements);
        dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        try{
            dataSource.setFilters(filters);
        }catch (SQLException e){

        }
        return dataSource;
    }
}

Druid过滤器

@WebFilter(filterName="druidWebStatFilter",urlPatterns = "/*",
    initParams  = {
        @WebInitParam(name = "exclusions", value = "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*")
})//忽略资源

public class DruidFilter extends WebStatFilter {
}

DruidServlet,用于过滤管理页面ip等信息

@WebServlet(urlPatterns = "/druid/*",
    initParams = {
        @WebInitParam(name="allow",value = ""), //IP白名单(若没有配置或者为空，则允许所有访问)
            @WebInitParam(name="deny",value = ""), //IP黑名单(deny优先于allow )
            @WebInitParam(name = "loginUserName", value = "admin"),  //登录druid管理页用户名
            @WebInitParam(name="loginPassword", value = "value") //登录druid管理页密码
    })
public class DruidServlet extends StatViewServlet {
}

还有一个重要的点，需要在启动类加上注解@ServletComponentScan，否则无法扫描到DruidServlet

City实体类

@Entity
@Table(name = "city")
public class City {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer cityId;

    @Column(nullable = false)
    private String cityName;
    private String cityIntroduce;

    public City(String cityName, String cityIntroduce) {
        this.cityName = cityName;
        this.cityIntroduce = cityIntroduce;
    }

    public Integer getCityId() {
        return cityId;
    }

    public void setCityId(Integer cityId) {
        this.cityId = cityId;
    }

    public String getCityName() {
        return cityName;
    }

    public void setCityName(String cityName) {
        this.cityName = cityName;
    }

    public String getCityIntroduce() {
        return cityIntroduce;
    }

    public void setCityIntroduce(String cityIntroduce) {
        this.cityIntroduce = cityIntroduce;
    }
}

CityRepository类,继承JpaRepository类，具有简单的操作数据库函数

public interface CityRepository extends JpaRepository<City, Integer> {

}

创建CityController，并注入CityRepository


@RestController
public class CityController {
    @Autowired
    private CityRepository cityRepository;

    @GetMapping("save")
    public String saveCity(String cityName, String cityIntroduce){
        City city = new City(cityName, cityIntroduce);
        cityRepository.save(city);
        return "sucess";
    }

    @GetMapping("deleteCityById")
    public String deleteCity(int cityId){
        cityRepository.deleteById(cityId);
        return "success";
    }

    @GetMapping("updateCity")
    public String updateCity(int citId, String cityName, String cityIntroduce){
        City city = new City(cityName,cityIntroduce);
        city.setCityId(citId);
        cityRepository.save(city);
        return "success";
    }

    @GetMapping("getCityById")
    public City getCityById(Integer cityId){
        City city = cityRepository.getOne(cityId);
        return city;
    }
}

在这里插入图片描述