Spring Boot 技术探索

Spring Boot makes it easy to create stand-alone, production-grade Spring based Applications that you can "just run".

24、Spring Boot之使用Druid监控数据源

平台环境:

名称

版本号

Mac OS X

10.15

JDK

1.8.0_201

Apache Maven

3.6.0

IntelliJ IDEA

2019.1 (Ultimate Edition)

Spring Boot

2.1.9.RELEASE

MySQL

8.0.13 库名test1

 

  什么是Druid Spring Boot Starter?

  Druid Spring Boot Starter 用于帮助你在Spring Boot项目中轻松集成Druid数据库连接池和监控。

 

Spring Data JPA中增加Druid监控

pom.xml中增加依赖

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-starter -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.10</version>
</dependency>

 

配置文件application.properties增加配置项

# Spring Config
spring.datasource.url=jdbc:mysql://localhost:3306/test1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.username=root
spring.datasource.password=12345678
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver


# Spring Data JPA Config
spring.jpa.properties.hibernate.hbm2ddl.auto=update
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.show-sql=true
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl


# 初始化大小、最小、最大连接数
spring.datasource.druid.initial-size=3
spring.datasource.druid.min-idle=3
spring.datasource.druid.max-active=10


# 配置获取连接等待超时的时间
spring.datasource.druid.max-wait=60000


# 监控后台账号和密码
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=admin


# 配置 StatFilter
spring.datasource.druid.filter.stat.log-slow-sql=true
spring.datasource.druid.filter.stat.slow-sql-millis=2000

 

新建Entity类User

package com.example.demo.dao.entity;


import java.io.Serializable;
import java.util.Date;
import javax.persistence.*;


@Entity
public class User implements Serializable
{
    private static final long serialVersionUID = 1L;


    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;


    @Column(nullable = false, unique = true)
    private String userName;


    @Column(nullable = false)
    private String passWord;


    @Column
    private Date regTime;


    @Column
    private String remark;


    @Column
    private int sex;


    public Long getId()
    {
        return id;
    }


    public void setId(Long id)
    {
        this.id = id;
    }


    public String getUserName()
    {
        return userName;
    }


    public void setUserName(String userName)
    {
        this.userName = userName;
    }


    public String getPassWord()
    {
        return passWord;
    }


    public void setPassWord(String passWord)
    {
        this.passWord = passWord;
    }


    public Date getRegTime()
    {
        return regTime;
    }


    public void setRegTime(Date regTime)
    {
        this.regTime = regTime;
    }


    public String getRemark()
    {
        return remark;
    }


    public void setRemark(String remark)
    {
        this.remark = remark;
    }


    public int isSex()
    {
        return sex;
    }


    public void setSex(int sex)
    {
        this.sex = sex;
    }
}

 

新建UserRepository类

package com.example.demo.dao;


import com.example.demo.dao.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;


public interface UserRepository extends JpaRepository<User, Long>
{
}

 

新建控制类UserController

package com.example.demo;


import com.example.demo.dao.UserRepository;
import com.example.demo.dao.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;


import java.util.List;


@RestController
public class UserController {


    @Autowired
    private UserRepository userRepository;


    @RequestMapping("/getUsers")
    public List<User> getUsers()
    {
        List<User> users=userRepository.findAll();
        return users;
    }
}

 

启动项目,访问http://localhost:8080/druid/login.html

输入用户名、密码,都是admin

找到SQL监控,目前没有拦截到任何SQL执行。

 

接着访问http://localhost:8080/getUsers

再看SQL监控页面。页面自动刷新出拦截到的SQL

 

源码文件:springBootDemo24


 

MyBatis中增加Druid监控

pom.xml中增加依赖

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.1.0</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.1.10</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

 

配置文件application.properties增加配置项

# Spring Config
spring.datasource.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.url = jdbc:mysql://localhost:3306/test1?serverTimezone=Asia/Shanghai&characterEncoding=utf-8
spring.datasource.username = root
spring.datasource.password = 12345678




# Druid配置
# 初始化大小,最小,最大链接数
spring.datasource.druid.initial-size=3
spring.datasource.druid.min-idle=3
spring.datasource.druid.max-active=10


# 配置获取连接等待超时的时间
spring.datasource.druid.max-wait=60000


#  StatViewServlet 配置
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=admin


# 配置StatFilter
spring.datasource.druid.filter.stat.log-slow-sql=true
spring.datasource.druid.filter.stat.slow-sql-millis=2000




# MyBatis Config
logging.level.com.example.MyBatisDemo.mapper=debug
#开启驼峰命名转换
#mybatis.configuration.map-underscore-to-camel-case=true
#开启null值返回
#mybatis.configuration.callSettersOnNulls=true

 

新建实体类EmployeeEntity

package com.example.demo.dao.entity;


import java.io.Serializable;
import java.util.Date;


public class EmployeeEntity implements Serializable
{
    private static final long serialVersionUID = 1L;
    private Long id;
    private String userName;
    private String password;
    private Date loginTime;
    private String remark;


    public Long getId()
    {
        return id;
    }


    public void setId(Long id)
    {
        this.id = id;
    }


    public String getUserName()
    {
        return userName;
    }


    public void setUserName(String userName)
    {
        this.userName = userName;
    }


    public String getPassword()
    {
        return password;
    }


    public void setPassword(String password)
    {
        this.password = password;
    }


    public Date getLoginTime()
    {
        return loginTime;
    }


    public void setLoginTime(Date loginTime)
    {
        this.loginTime = loginTime;
    }


    public String getRemark()
    {
        return remark;
    }


    public void setRemark(String remark)
    {
        this.remark = remark;
    }
}

 

新建映射类EmployeeMapper

package com.example.demo.dao.mapper;


import java.util.LinkedHashMap;
import java.util.List;


import com.example.demo.dao.entity.EmployeeEntity;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;


public interface EmployeeMapper
{
    // #{}中的名称要和Entity类中的属性名一致
    @Insert("INSERT INTO Employee(user_name, pass_word, login_time, remark) VALUES(#{userName}, #{password}, #{loginTime}, #{remark})")
    void insert(EmployeeEntity employeeEntity);


    // 由于数据库字段名和Entity中的属性不完全一致,因此这里需要编写映射字段。
    @Select("SELECT * FROM Employee WHERE user_name = '#{userName}'")
    @Results(id = "EmployeeResults", value = {@Result(column = "user_name", property = "userName"), @Result(column = "pass_word", property = "password"), @Result(column = "login_time", property = "loginTime")})
    // remark的映射就不用写了,因为数据库列名称和Entity类字段完全一样。
    EmployeeEntity getEmployeeByUserName(String userName);


    // 直接使用定义好的@Results
    @Select("SELECT * FROM Employee")
    @ResultMap("EmployeeResults")
    List<EmployeeEntity> getAll();


    // 不用Entity类接收返回值(省略写@Results的麻烦)
    @Select("SELECT * FROM Employee")
    List<LinkedHashMap<String, Object>> getAll_NoEntity();


    // 注意这里用$号(相当于拼接字符串)
    @Update("UPDATE Employee SET remark='${remark}' WHERE user_name = '${userName}'")
    void update(@Param("userName") String userName, @Param("remark") String remark);


    // 注意这里用#号(相当于?号传参)
    @Delete("DELETE FROM Employee WHERE ID=#{id}")
    void delete(Long id);


    @Delete("DELETE FROM Employee WHERE user_name = '${userName}'")
    void deleteByUserName(@Param("userName") String userName);
}

 

新建配置类AppConfig

package com.example.demo.config;


import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Configuration;


@Configuration
@MapperScan("com.example.demo.dao.mapper")// 这个也可以直接写到Spring Boot的启动类上
public class AppConfig
{
}

 

新建EmployeeController

package com.example.demo.controller;


import com.example.demo.dao.entity.EmployeeEntity;
import com.example.demo.dao.mapper.EmployeeMapper;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;


import javax.annotation.Resource;
import java.util.List;


@RestController
public class EmployeeController
{
    @Resource
    private EmployeeMapper employeeMapper;


    @RequestMapping("/getEmployees")
    public void getUsers()
    {
        List<EmployeeEntity> employeeEntitys = employeeMapper.getAll();
        for (EmployeeEntity employeeEntity : employeeEntitys)
        {
            System.out.println(employeeEntity.getUserName());
            System.out.println(employeeEntity.getPassword());
            System.out.println(employeeEntity.getLoginTime());
            System.out.println(employeeEntity.getRemark());
            System.out.println("--------------");
        }
    }
}

 

启动项目,访问http://localhost:8080/druid/login.html

输入用户名、密码,都是admin

找到SQL监控,目前没有拦截到任何SQL执行。

 

接着访问http://localhost:8080/getEmployees

再看SQL监控页面。页面自动刷新出拦截到的SQL

 

源码文件:springBootDemo24B


 

BUG:

开始使用的Druid是1.1.20版本的,结果访问显示404页面。

<!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-starter -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.20</version>
</dependency>

 

换成1.1.10的就好了。

<!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-starter -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.10</version>
</dependency>

 

 

 

Bootstrap Thumbnail Second
MySQL

MySQL is the world's most popular open source database.

GO

Bootstrap Thumbnail Third
算法基础

本书介绍了什么是计算机算法,如何描述它们,以及如何来评估它们。

GO