IT技术社区_专业开发者平台_最新编程教程与解决方案 -沐辰社区

Spring Boot整合Druid案例

wen java案例 3

本文目录导读：

Spring Boot整合Druid案例

  1. 项目结构
  2. pom.xml依赖
  3. application.yml配置
  4. Druid配置类
  5. 实体类
  6. Mapper接口
  7. Mapper XML
  8. Service接口和实现
  9. Controller
  10. 启动类
  11. 数据库初始化SQL
  12. 测试用例
  13. 使用说明

我来提供一个完整的Spring Boot整合Druid连接池的案例。

项目结构

spring-boot-druid-demo/
├── pom.xml
├── src/main/java/com/example/druid/
│   ├── DruidApplication.java
│   ├── config/
│   │   └── DruidConfig.java
│   ├── controller/
│   │   └── UserController.java
│   ├── entity/
│   │   └── User.java
│   ├── mapper/
│   │   └── UserMapper.java
│   └── service/
│       ├── UserService.java
│       └── impl/
│           └── UserServiceImpl.java
└── src/main/resources/
    ├── application.yml
    └── mapper/
        └── UserMapper.xml

pom.xml依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 
         http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.14</version>
    </parent>
    <groupId>com.example</groupId>
    <artifactId>spring-boot-druid-demo</artifactId>
    <version>1.0.0</version>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <!-- Spring Boot Web -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- MyBatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.3.1</version>
        </dependency>
        <!-- Druid 连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.20</version>
        </dependency>
        <!-- MySQL 驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.33</version>
        </dependency>
        <!-- Lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </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>

application.yml配置

server:
  port: 8080
spring:
  datasource:
    # 使用Druid连接池
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test_db?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    username: root
    password: 123456
    druid:
      # 初始连接数
      initial-size: 5
      # 最小连接池数量
      min-idle: 5
      # 最大连接池数量
      max-active: 20
      # 获取连接时的最大等待时间（毫秒）
      max-wait: 60000
      # 检测连接是否有效的SQL
      validation-query: SELECT 1
      # 申请连接时执行validationQuery检测连接是否有效
      test-while-idle: true
      # 申请连接时检查连接是否有效（影响性能）
      test-on-borrow: false
      # 归还连接时检查连接是否有效（影响性能）
      test-on-return: false
      # 是否缓存preparedStatement（Oracle建议开启）
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 20
      # 配置监控统计拦截的filters
      filters: stat,wall,slf4j
      # 合并多个DruidDataSource的监控数据
      use-global-data-source-stat: true
      # 连接池监控配置
      web-stat-filter:
        enabled: true
        url-pattern: "/*"
        exclusions: "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*"
        session-stat-enable: true
        session-stat-max-count: 100
      # 监控页面配置
      stat-view-servlet:
        enabled: true
        url-pattern: "/druid/*"
        login-username: admin
        login-password: 123456
        reset-enable: false
        # IP白名单
        allow: 127.0.0.1
        # IP黑名单
        deny: ""
mybatis:
  # mapper xml文件位置
  mapper-locations: classpath:mapper/*.xml
  # 实体类包路径
  type-aliases-package: com.example.druid.entity
  configuration:
    # 下划线转驼峰
    map-underscore-to-camel-case: true
    # 打印SQL日志
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
logging:
  level:
    com.example.druid.mapper: debug

Druid配置类

package com.example.druid.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.sql.DataSource;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
@Configuration
public class DruidConfig {
    /**
     * 绑定数据源配置
     */
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource druidDataSource() {
        return new DruidDataSource();
    }
    /**
     * 配置Druid监控页面
     */
    @Bean
    public ServletRegistrationBean statViewServlet() {
        ServletRegistrationBean bean = new ServletRegistrationBean(
                new StatViewServlet(), "/druid/*");
        // 配置监控页面的登录账号密码
        Map<String, String> initParameters = new HashMap<>();
        initParameters.put("loginUsername", "admin");
        initParameters.put("loginPassword", "123456");
        // 允许访问的IP，空表示所有IP
        initParameters.put("allow", "");
        // 禁止访问的IP
        initParameters.put("deny", "");
        // 是否能够重置数据
        initParameters.put("resetEnable", "false");
        bean.setInitParameters(initParameters);
        return bean;
    }
    /**
     * 配置Druid监控过滤器
     */
    @Bean
    public FilterRegistrationBean webStatFilter() {
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());
        Map<String, String> initParameters = new HashMap<>();
        // 不统计的请求
        initParameters.put("exclusions", "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*");
        bean.setInitParameters(initParameters);
        // 拦截所有请求
        bean.setUrlPatterns(Arrays.asList("/*"));
        return bean;
    }
}

实体类

package com.example.druid.entity;
import lombok.Data;
import java.util.Date;
@Data
public class User {
    private Integer id;
    private String username;
    private String password;
    private String email;
    private Integer age;
    private Date createTime;
}

Mapper接口

package com.example.druid.mapper;
import com.example.druid.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import java.util.List;
@Mapper
public interface UserMapper {
    int insert(User user);
    int deleteById(@Param("id") Integer id);
    int update(User user);
    User selectById(@Param("id") Integer id);
    List<User> selectAll();
    List<User> selectByPage(@Param("offset") int offset, @Param("limit") int limit);
}

Mapper XML

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.druid.mapper.UserMapper">
    <resultMap id="BaseResultMap" type="com.example.druid.entity.User">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="username" property="username" jdbcType="VARCHAR"/>
        <result column="password" property="password" jdbcType="VARCHAR"/>
        <result column="email" property="email" jdbcType="VARCHAR"/>
        <result column="age" property="age" jdbcType="INTEGER"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
    </resultMap>
    <sql id="Base_Column_List">
        id, username, password, email, age, create_time
    </sql>
    <insert id="insert" parameterType="com.example.druid.entity.User" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO user (username, password, email, age, create_time)
        VALUES (#{username}, #{password}, #{email}, #{age}, NOW())
    </insert>
    <delete id="deleteById" parameterType="java.lang.Integer">
        DELETE FROM user WHERE id = #{id}
    </delete>
    <update id="update" parameterType="com.example.druid.entity.User">
        UPDATE user
        <set>
            <if test="username != null">username = #{username},</if>
            <if test="password != null">password = #{password},</if>
            <if test="email != null">email = #{email},</if>
            <if test="age != null">age = #{age},</if>
        </set>
        WHERE id = #{id}
    </update>
    <select id="selectById" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List"/>
        FROM user
        WHERE id = #{id}
    </select>
    <select id="selectAll" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List"/>
        FROM user
        ORDER BY id DESC
    </select>
    <select id="selectByPage" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List"/>
        FROM user
        ORDER BY id DESC
        LIMIT #{offset}, #{limit}
    </select>
</mapper>

Service接口和实现

// UserService.java
package com.example.druid.service;
import com.example.druid.entity.User;
import java.util.List;
public interface UserService {
    int add(User user);
    int delete(Integer id);
    int update(User user);
    User getById(Integer id);
    List<User> getAll();
    List<User> getByPage(int pageNum, int pageSize);
}
// UserServiceImpl.java
package com.example.druid.service.impl;
import com.example.druid.entity.User;
import com.example.druid.mapper.UserMapper;
import com.example.druid.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
@Service
@Transactional(rollbackFor = Exception.class)
public class UserServiceImpl implements UserService {
    @Autowired
    private UserMapper userMapper;
    @Override
    public int add(User user) {
        return userMapper.insert(user);
    }
    @Override
    public int delete(Integer id) {
        return userMapper.deleteById(id);
    }
    @Override
    public int update(User user) {
        return userMapper.update(user);
    }
    @Override
    public User getById(Integer id) {
        return userMapper.selectById(id);
    }
    @Override
    public List<User> getAll() {
        return userMapper.selectAll();
    }
    @Override
    public List<User> getByPage(int pageNum, int pageSize) {
        int offset = (pageNum - 1) * pageSize;
        return userMapper.selectByPage(offset, pageSize);
    }
}

Controller

package com.example.druid.controller;
import com.example.druid.entity.User;
import com.example.druid.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@RestController
@RequestMapping("/api/user")
public class UserController {
    @Autowired
    private UserService userService;
    /**
     * 添加用户
     */
    @PostMapping("/add")
    public Map<String, Object> add(@RequestBody User user) {
        Map<String, Object> result = new HashMap<>();
        try {
            userService.add(user);
            result.put("code", 200);
            result.put("message", "添加成功");
            result.put("data", user);
        } catch (Exception e) {
            result.put("code", 500);
            result.put("message", "添加失败: " + e.getMessage());
        }
        return result;
    }
    /**
     * 删除用户
     */
    @DeleteMapping("/delete/{id}")
    public Map<String, Object> delete(@PathVariable Integer id) {
        Map<String, Object> result = new HashMap<>();
        try {
            userService.delete(id);
            result.put("code", 200);
            result.put("message", "删除成功");
        } catch (Exception e) {
            result.put("code", 500);
            result.put("message", "删除失败: " + e.getMessage());
        }
        return result;
    }
    /**
     * 更新用户
     */
    @PutMapping("/update")
    public Map<String, Object> update(@RequestBody User user) {
        Map<String, Object> result = new HashMap<>();
        try {
            userService.update(user);
            result.put("code", 200);
            result.put("message", "更新成功");
        } catch (Exception e) {
            result.put("code", 500);
            result.put("message", "更新失败: " + e.getMessage());
        }
        return result;
    }
    /**
     * 查询用户
     */
    @GetMapping("/get/{id}")
    public Map<String, Object> getById(@PathVariable Integer id) {
        Map<String, Object> result = new HashMap<>();
        try {
            User user = userService.getById(id);
            result.put("code", 200);
            result.put("message", "查询成功");
            result.put("data", user);
        } catch (Exception e) {
            result.put("code", 500);
            result.put("message", "查询失败: " + e.getMessage());
        }
        return result;
    }
    /**
     * 查询所有用户
     */
    @GetMapping("/list")
    public Map<String, Object> list() {
        Map<String, Object> result = new HashMap<>();
        try {
            List<User> users = userService.getAll();
            result.put("code", 200);
            result.put("message", "查询成功");
            result.put("data", users);
            result.put("total", users.size());
        } catch (Exception e) {
            result.put("code", 500);
            result.put("message", "查询失败: " + e.getMessage());
        }
        return result;
    }
    /**
     * 分页查询
     */
    @GetMapping("/page")
    public Map<String, Object> page(@RequestParam(defaultValue = "1") int pageNum,
                                    @RequestParam(defaultValue = "10") int pageSize) {
        Map<String, Object> result = new HashMap<>();
        try {
            List<User> users = userService.getByPage(pageNum, pageSize);
            result.put("code", 200);
            result.put("message", "查询成功");
            result.put("data", users);
            result.put("pageNum", pageNum);
            result.put("pageSize", pageSize);
        } catch (Exception e) {
            result.put("code", 500);
            result.put("message", "查询失败: " + e.getMessage());
        }
        return result;
    }
}

启动类

package com.example.druid;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class DruidApplication {
    public static void main(String[] args) {
        SpringApplication.run(DruidApplication.class, args);
        System.out.println("应用启动成功！");
        System.out.println("Druid监控页面: http://localhost:8080/druid");
    }
}

数据库初始化SQL

-- 创建数据库
CREATE DATABASE IF NOT EXISTS test_db DEFAULT CHARACTER SET utf8mb4;
USE test_db;
-- 创建用户表
CREATE TABLE IF NOT EXISTS `user` (
    `id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    `username` VARCHAR(50) NOT NULL COMMENT '用户名',
    `password` VARCHAR(100) NOT NULL COMMENT '密码',
    `email` VARCHAR(100) COMMENT '邮箱',
    `age` INT COMMENT '年龄',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
-- 插入测试数据
INSERT INTO `user` (`username`, `password`, `email`, `age`) VALUES
('张三', '123456', 'zhangsan@test.com', 25),
('李四', '123456', 'lisi@test.com', 30),
('王五', '123456', 'wangwu@test.com', 28),
('赵六', '123456', 'zhaoliu@test.com', 35),
('孙七', '123456', 'sunqi@test.com', 22);

测试用例

package com.example.druid;
import com.example.druid.entity.User;
import com.example.druid.mapper.UserMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
@SpringBootTest
public class DruidApplicationTests {
    @Autowired
    private UserMapper userMapper;
    @Test
    public void testInsert() {
        User user = new User();
        user.setUsername("测试用户");
        user.setPassword("123456");
        user.setEmail("test@test.com");
        user.setAge(25);
        int result = userMapper.insert(user);
        System.out.println("插入结果: " + result);
    }
    @Test
    public void testSelectAll() {
        List<User> users = userMapper.selectAll();
        System.out.println("用户数量: " + users.size());
        users.forEach(System.out::println);
    }
    @Test
    public void testSelectById() {
        User user = userMapper.selectById(1);
        System.out.println("用户信息: " + user);
    }
}

使用说明

访问Druid监控页面

启动应用后访问：http://localhost:8080/druid

  • 用户名：admin
  • 密码：123456

功能特点

  1. 连接池管理：

    • 自动管理数据库连接
    • 可配置初始连接数、最大连接数等

  2. 监控功能：

    • SQL监控
    • 慢查询监控
    • 活跃连接监控
    • 数据源状态监控

  3. 安全过滤：

    • SQL注入防护
    • 防SQL防火墙

API测试

# 添加用户
curl -X POST http://localhost:8080/api/user/add \
  -H "Content-Type: application/json" \
  -d '{"username":"测试用户","password":"123456","email":"test@test.com","age":25}'
# 查询全部用户
curl http://localhost:8080/api/user/list
# 查询单个用户
curl http://localhost:8080/api/user/get/1
# 分页查询
curl "http://localhost:8080/api/user/page?pageNum=1&pageSize=10"
# 更新用户
curl -X PUT http://localhost:8080/api/user/update \
  -H "Content-Type: application/json" \
  -d '{"id":1,"age":26}'
# 删除用户
curl -X DELETE http://localhost:8080/api/user/delete/1

这个案例涵盖了Spring Boot整合Druid的完整功能，包括基础配置、监控页面、SQL防火墙等特性,你可以根据需要调整配置参数。

上一篇Spring Boot整合MyBatis-Plus案例

下一篇Spring Boot整合Swagger案例

相关文章

抱歉，评论功能暂时关闭!