SpringBoot访问数据库与druid连接池的使用

一．环境搭建

1.pom.xml添加starter和数据库驱动

<!-- 连接数据库的stater-->
<dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-jdbc</artifactId>
   <version>2.2.4.RELEASE</version>
</dependency>
<!-- 数据库驱动-->
<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>5.1.47</version>
</dependency>

2. application.properties或application.yaml中编写数据源配置文件

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/db_springboot?characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false
    password: 123456
    username: root
    driver-class-name: com.mysql.jdbc.Driver

3.建库建表

create database db_springboot;

create table person(
name varchar(15),
age int(2))ENGINE=INNODB DEFAULT CHARSET=UTF8;

insert into person  values('Hsin',20);

4.新建实体类

package com.example.demo.pojo;

/**
 * @author XiaoXin
 * @date 2020/2/20 上午12:43
 */
public class Person {
    String name;
    Integer age;

    public Person(String name, Integer age) {
        this.name = name;
        this.age = age;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "Person{" +
                "name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

二．原生jdbc测试

/**
 * @author XiaoXin
 * @date 2020/2/20 上午12:43
 */

import com.example.demo.pojo.Person;
import com.example.demo.utils.DBUtil;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


@SpringBootTest
class DemoApplicationTests {
    @Autowired
    DataSource source;
    @Test
    void testDeaultJdbc() throws SQLException {
        System.out.println("dataSource 所使用的类："+ source.getClass());
        Connection con= source.getConnection() ;
        System.out.println("dataSource　获得的连接："+ con);
        DBUtil dbUtil = new DBUtil(con);
        ResultSet rs =  dbUtil.executeQuery("select * from person",null);
        List<Person> personList = new ArrayList<>();
        Person person = null;
        while (rs.next()){
            String name = rs.getString("name");
            Integer age = rs.getInt("age");
            person =new Person(name,age);
            personList.add(person);
        }
        for(Person p:personList){
            System.out.println(p);
        }


    }

}

结果

可以看到springboot默认的数据源是class com.zaxxer.hikari.HikariDataSource．查看DataSourceConfiguration,源码如下:

abstract class DataSourceConfiguration {

	@SuppressWarnings("unchecked")
	protected static <T> T createDataSource(DataSourceProperties properties, Class<? extends DataSource> type) {
		return (T) properties.initializeDataSourceBuilder().type(type).build();
	}

	/**
	 * Tomcat Pool DataSource configuration.
	 */
	@Configuration(proxyBeanMethods = false)
	@ConditionalOnClass(org.apache.tomcat.jdbc.pool.DataSource.class)
	@ConditionalOnMissingBean(DataSource.class)
	@ConditionalOnProperty(name = "spring.datasource.type", havingValue = "org.apache.tomcat.jdbc.pool.DataSource",
			matchIfMissing = true)
	static class Tomcat {

		@Bean
		@ConfigurationProperties(prefix = "spring.datasource.tomcat")
		org.apache.tomcat.jdbc.pool.DataSource dataSource(DataSourceProperties properties) {
			org.apache.tomcat.jdbc.pool.DataSource dataSource = createDataSource(properties,
					org.apache.tomcat.jdbc.pool.DataSource.class);
			DatabaseDriver databaseDriver = DatabaseDriver.fromJdbcUrl(properties.determineUrl());
			String validationQuery = databaseDriver.getValidationQuery();
			if (validationQuery != null) {
				dataSource.setTestOnBorrow(true);
				dataSource.setValidationQuery(validationQuery);
			}
			return dataSource;
		}

	}

	/**
	 * Hikari DataSource configuration.
	 */
	@Configuration(proxyBeanMethods = false)
	@ConditionalOnClass(HikariDataSource.class)
	@ConditionalOnMissingBean(DataSource.class)
	@ConditionalOnProperty(name = "spring.datasource.type", havingValue = "com.zaxxer.hikari.HikariDataSource",
			matchIfMissing = true)
	static class Hikari {

		@Bean
		@ConfigurationProperties(prefix = "spring.datasource.hikari")
		HikariDataSource dataSource(DataSourceProperties properties) {
			HikariDataSource dataSource = createDataSource(properties, HikariDataSource.class);
			if (StringUtils.hasText(properties.getName())) {
				dataSource.setPoolName(properties.getName());
			}
			return dataSource;
		}

	}

	/**
	 * DBCP DataSource configuration.
	 */
	@Configuration(proxyBeanMethods = false)
	@ConditionalOnClass(org.apache.commons.dbcp2.BasicDataSource.class)
	@ConditionalOnMissingBean(DataSource.class)
	@ConditionalOnProperty(name = "spring.datasource.type", havingValue = "org.apache.commons.dbcp2.BasicDataSource",
			matchIfMissing = true)
	static class Dbcp2 {

		@Bean
		@ConfigurationProperties(prefix = "spring.datasource.dbcp2")
		org.apache.commons.dbcp2.BasicDataSource dataSource(DataSourceProperties properties) {
			return createDataSource(properties, org.apache.commons.dbcp2.BasicDataSource.class);
		}

	}

	/**
	 * Generic DataSource configuration.
	 */
	@Configuration(proxyBeanMethods = false)
	@ConditionalOnMissingBean(DataSource.class)
	@ConditionalOnProperty(name = "spring.datasource.type")
	static class Generic {

		@Bean
		DataSource dataSource(DataSourceProperties properties) {
			return properties.initializeDataSourceBuilder().build();
		}

	}

}

继续查看查看DataSourceProperties，该配置类定义的属性如下：

@ConfigurationProperties(prefix = "spring.datasource")
public class DataSourceProperties implements BeanClassLoaderAware, InitializingBean {

	private ClassLoader classLoader;

	/**
	 * Name of the datasource. Default to "testdb" when using an embedded database.
	 */
	private String name;

	/**
	 * Whether to generate a random datasource name.
	 */
	private boolean generateUniqueName;

	/**
	 * Fully qualified name of the connection pool implementation to use. By default, it
	 * is auto-detected from the classpath.
	 */
	private Class<? extends DataSource> type;

	/**
	 * Fully qualified name of the JDBC driver. Auto-detected based on the URL by default.
	 */
	private String driverClassName;

	/**
	 * JDBC URL of the database.
	 */
	private String url;

	/**
	 * Login username of the database.
	 */
	private String username;

	/**
	 * Login password of the database.
	 */
	private String password;

	/**
	/**
	 * Initialize a {@link DataSourceBuilder} with the state of this instance.
	 * @return a {@link DataSourceBuilder} initialized with the customizations defined on
	 * this instance
	 */
	public DataSourceBuilder<?> initializeDataSourceBuilder() {
		return DataSourceBuilder.create(getClassLoader()).type(getType()).driverClassName(determineDriverClassName())
				.url(determineUrl()).username(determineUsername()).password(determinePassword());
	}
/**
	 * Determine the name to used based on this configuration.
	 * @return the database name to use or {@code null}
	 * @since 2.0.0
	 */
	public String determineDatabaseName() {
		if (this.generateUniqueName) {
			if (this.uniqueName == null) {
				this.uniqueName = UUID.randomUUID().toString();
			}
			return this.uniqueName;
		}
		if (StringUtils.hasLength(this.name)) {
			return this.name;
		}
		if (this.embeddedDatabaseConnection != EmbeddedDatabaseConnection.NONE) {
			return "testdb";
		}
		return null;
	}

三．JdbcTemplate测试

package com.example.demo.dao.Impl;

import com.example.demo.dao.PersonDao;
import com.example.demo.pojo.Person;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author XiaoXin
 * @date 2020/2/20 上午12:49
 */
@Repository
public class PersonDaoImpl implements PersonDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<Person> queryAll() {
        String sql = "select * from person";
        List<Person> personList = new ArrayList<>();
       List<Map<String,Object>> mapList = jdbcTemplate.queryForList(sql);
       for(Map<String,Object> map:mapList){
           String name = (String) map.get("name");
           Integer age = (Integer) map.get("age");
           Person person = new Person(name,age);
           personList.add(person);
       }
        return personList;
    }
}

package com.example.demo.controller;

import com.example.demo.dao.Impl.PersonDaoImpl;
import com.example.demo.pojo.Person;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * @author XiaoXin
 * @date 2020/2/20 上午1:38
 */
@RestController
public class TestDaoController{
    @Autowired
    PersonDaoImpl personDao;
    @GetMapping("/person")
    private List<Person> getPersons(){
        List<Person> personList = personDao.queryAll();
        return personList;
    }
}

结果：

四．使用Druid连接池

１．什么是druid?

Druid是阿里巴巴数据库事业部推出的一款专门为监控而生的数据库连接池，也是Java语言中最好的数据库连接池。Druid能够提供强大的监控和扩展功能。可以在maven中央仓库下载,这里我们引入maven依赖．

２．druid怎么用？

我们要在springboot中使用druid，除了导入jdb相关的starter,还要导入druid依赖，并进行相关配置．
１．导入druid依赖

<!--druid连接池 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.21</version>
</dependency>

２切换为druid数据源．
只需要原来的数据源配置中加入type: com.alibaba.druid.pool.DruidDataSource．当然还可以加入druid自己的私有属性配置．点开druid的jar，如下

继续点开pool,查看DruidDataSource这个类，我们可以看到Druid数据源定义了非常非常多非常多的属性．就这个类好像3600多行代码吧

public class DruidDataSource extends DruidAbstractDataSource implements DruidDataSourceMBean, ManagedDataSource, Referenceable, Closeable, Cloneable, ConnectionPoolDataSource, MBeanRegistration {
    private static final Log LOG = LogFactory.getLog(DruidDataSource.class);
    private static final long serialVersionUID = 1L;
    private volatile long recycleErrorCount;
    private long connectCount;
    private long closeCount;
    private volatile long connectErrorCount;
    private long recycleCount;
    private long removeAbandonedCount;
    private long notEmptyWaitCount;
    private long notEmptySignalCount;
    private long notEmptyWaitNanos;
    private int keepAliveCheckCount;
    private int activePeak;
    private long activePeakTime;
    private int poolingPeak;
    private long poolingPeakTime;
    private volatile DruidConnectionHolder[] connections;
    private int poolingCount;
    private int activeCount;
    private volatile long discardCount;
    private int notEmptyWaitThreadCount;
    private int notEmptyWaitThreadPeak;
    //这里粘贴了一部分

继续往下翻，看到configFromPropety这个方法，熟悉的Properties.我们知道通过这个方法获取到我们文件里的数据库的相关参数．

public void configFromPropety(Properties properties) {
        String property = properties.getProperty("druid.name");
        if (property != null) {
            this.setName(property);
        }

        property = properties.getProperty("druid.url");
        if (property != null) {
            this.setUrl(property);
        }

        property = properties.getProperty("druid.username");
        if (property != null) {
            this.setUsername(property);
        }

        property = properties.getProperty("druid.password");
        if (property != null) {
            this.setPassword(property);
        }

        property = properties.getProperty("druid.stat.sql.MaxSize");
        int value;
        if (property != null && property.length() > 0) {
            try {
                value = Integer.parseInt(property);
                if (this.dataSourceStat != null) {
                    this.dataSourceStat.setMaxSqlSize(value);
                }
            } catch (NumberFormatException var19) {
                LOG.error("illegal property 'druid.stat.sql.MaxSize'", var19);
            }
        }
		．．．

    }

３．配置文件

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/db_springboot?characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false
    password: 123456
    username: root
    driver-class-name: com.mysql.jdbc.Driver
    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
    # 打开PSCache，并且指定每个连接上PSCache的大小
    poolPreparedStatements: true
    maxPoolPreparedStatementPerConnectionSize: 20
    # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    filters: stat,wall,slf4j
    # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    # 合并多个DruidDataSource的监控数据
    #useGlobalDataSourceStat: true

４．配置数据源，让druid的私有配置生效，同时启用druid的后台监管面板和sql监控．
内置监控页面是一个Servlet，Druid内置提供了一个StatViewServlet用于展示Druid的统计信息。
这个StatViewServlet的用途包括：提供监控信息展示的html页面和监控信息的JSON API．
因此要使用后台监控面板，就要配置这个StatViewServlet．另外Druid的监控统计功能是通过filter-chain扩展实现，如果你要打开监控统计功能，需要配置WebStatFilter，这个WebStatFilter用于采集web-jdbc关联监控的数据。我们只要再配置了它就可以了j监控sql的运行了．

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;

/**
 * @author XiaoXin
 * @date 2020/2/20 上午2:40
 */
@Configuration
public class DruidConfig {
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource druid(){
        return  new DruidDataSource();
    }
    /**
     * druid的强大在于有一套完整的监控配置,我们可以在这里配置一下,配置druid的后台监控需要配置
     * 一个servlet,我们可以直接使用servletRegistrationBean来配置,配置的servlet的名称
     * 是statViewServlet,
     */
    @Bean
    public ServletRegistrationBean statViewServlet() {
        ServletRegistrationBean bean
                = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        //可以在这个servlet中设置参数来定义后台的一些参数
        Map<String, String> initParms = new HashMap<>();
        //配置登录用户名
        initParms.put("loginUsername", "admin");
        //配置密码
        initParms.put("loginPassword", "123456");
        //配置访问权限,默认是所有都能访问
        initParms.put("allow", "");
        //配置拒绝访问的ip
        initParms.put("deny", "");
        bean.setInitParameters(initParms);
        return bean;
    }

    /**
     * 要使用druid的后台监控功能,还可以配置一个filter,它的名称是webStatFilter
     *
     */
    @Bean
    public FilterRegistrationBean webStatFilter() {
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());

        Map<String, String> initParms = new HashMap<>();
        //不拦截的资源
        initParms.put("exclusions", "*.js,*.css,/druid/*");
        bean.setInitParameters(initParms);
        //要拦截的请求
        bean.setUrlPatterns(Arrays.asList("/*"));
        return bean;
    }


}

WebStatFilter继承了一个抽象类并实现了Filter接口，部分代码如下：

public class WebStatFilter extends AbstractWebStatImpl implements Filter {
    private static final Log LOG = LogFactory.getLog(WebStatFilter.class);
    public static final String PARAM_NAME_PROFILE_ENABLE = "profileEnable";
    public static final String PARAM_NAME_SESSION_STAT_ENABLE = "sessionStatEnable";
    public static final String PARAM_NAME_SESSION_STAT_MAX_COUNT = "sessionStatMaxCount";
    public static final String PARAM_NAME_EXCLUSIONS = "exclusions";
    public static final String PARAM_NAME_PRINCIPAL_SESSION_NAME = "principalSessionName";
    public static final String PARAM_NAME_PRINCIPAL_COOKIE_NAME = "principalCookieName";
    public static final String PARAM_NAME_REAL_IP_HEADER = "realIpHeader";
    protected PatternMatcher pathMatcher = new ServletPathMatcher();
    private Set<String> excludesPattern;

    public WebStatFilter() {
    }

    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
      //
    }

    public void init(FilterConfig config) throws ServletException {
    //
    }
    public void destroy() {
      //
    }
}

运行结果：
访问/druid,登录进入后台管理界面查看数据源，可以看到，我们配置的属性都生效了

发起一个查询，一切正常

同时，druid监控生效．到此成功．

注意在配置druid的监控时，最好使用用sl4j，请不要使用log4j．我第一参考别的博客，使用log4j
,控制台报如下提醒．最后发现使用slf4j时一切正常，可能是自己使用的druid和springboot版本过高导致的．