spring boot使用druid连接池

迈不过友情╰ 2023-05-31 04:58 7阅读 0赞

\#spring boot使用druid连接池  
在java开发远古时代我们对数据库的操作我们每连接一次数据库就需要创建一次连接，对数据库完成操作后就释放该连接。我们来看看过去创建数据库连接的方式

//        1.    注册驱动.
    //        告知JVM使用的是哪一个数据库的驱动
            //DriverManager.registerDriver(new Driver());
                //    把com.mysql.jdbc.Driver这个实现类直接扔到内存里
                //  扔到内存之后调用static静态代码块
                //  静态代码块中注册驱动
            Class.forName("com.mysql.jdbc.Driver");   //com.mysql.jdbc为驱动所在包
    //        2.    获得连接对象.
    //        使用JDBC中的类，完成对MySQL数据库的连接
            //jdbc:mysql://localhost:3306/数据库名
            String url="jdbc:mysql://localhost:3306/goods?characterEncoding=utf-8";
            String username="root";
            String password="123456";
            Connection conn=DriverManager.getConnection(url, username, password);
    //        3.    获得语句执行平台
    //        通过连接对象获取对SQL语句的执行者对象
            Statement sta=conn.createStatement();   //接收一定要用java里面的  导包导SQL里的
    //        4.    执行sql语句
    //        使用执行者对象，向数据库执行SQL语句
    //        获取到数据库的执行后的结果
    　　　　　//添加
            String sql="insert into sort(sname,sdesc) value('电子设备','都是骗男人的')";
            int row=sta.executeUpdate(sql);
            System.out.println(row);
            //修改
            String sql="update sort set sname='玩具' where sid='1'";
            int row=sta.executeUpdate(sql);
            System.out.println(row);
            //查询
            String sql="select * from sort";
            //删除
            String sql="delete from sort where sid='1'";
    //        5.    处理结果 (仅用于查询)
            ResultSet rs=sta.executeQuery(sql);
            while(rs.next()){              
    　　　　　　　　System.out.println(rs.getInt("sid")+"..."+rs.getString("sname")+"..."+rs.getString("sdesc"));
            }
    //        6.    释放资源.   (先开的后关)
    //        调用一堆close()方法  关闭对象
            rs.close();
            sta.close();
            conn.close();

像上面代码这样通常我们都会进行封装成工具类  
当应用对数据库访问操作较为频繁时就会不断的创建数据库连接而创建数据库连接是非常消耗资源的，进而影响到系统的响应时间。所以就业界就出现了数据库连接池。  
\#\#数据库连接池定义  
看看百度对连接池的定义

> 数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。这项技术能明显提高对数据库操作的性能。

\#\#java常用的数据库连接池  
C3P0：一个开放源代码的JDBC连接池，它在lib目录中与Hibernate一起发布,包括了实现jdbc3和jdbc2扩展规范说明的Connection 和Statement 池的DataSources 对象  
DBCP：（Database Connection Pool）是一个依赖Jakarta commons-pool对象池机制的数据库连接池，Tomcat的数据源使用的就是DBCP  
druid：druid是阿里巴巴开源平台上一个数据库连接池实现，它结合了C3P0、DBCP、PROXOOL等DB池的优点，同时加入了日志监控，可以很好的监控DB池连接和SQL的执行情况，可以说是针对监控而生的DB连接池  
这里我们演示通过druid连接数据库  
\#\#代码演示  
代码环境

> spring boot+mysql+druid+spring data jpa

\#\#\#先看一下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 https://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.1.8.RELEASE</version>
            <relativePath/> 
        </parent>
        <groupId>com.spring</groupId>
        <artifactId>spring-boot-druid</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>spring-boot-druid</name>
        <description>druid demo project for Spring Boot</description>
    
        <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-devtools</artifactId>
                <scope>runtime</scope>
                <optional>true</optional>
            </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>
    
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.1.20</version>
            </dependency>
            <dependency>
                <groupId>com.google.guava</groupId>
                <artifactId>guava</artifactId>
                <version>28.1-jre</version>
            </dependency>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
        </build>
    
    </project>

\#\#\#application.properties

##DB
    spring.jpa.properties.hibernate.hbm2ddl.auto = update
    #数据库配置
    spring.datasource.url=jdbc:mysql://url:3306/book?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
    spring.datasource.username=book
    spring.datasource.password=book
    spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
    spring.datasource.initialSize=1
    spring.datasource.maxActive=20
    spring.datasource.minIdle=1
    spring.datasource.maxWait=60000
    spring.datasource.timeBetweenEvictionRunsMillis=60000
    spring.datasource.minEvictableIdleTimeMillis=300000
    spring.datasource.validationQuery=SELECT 1 FROM dual
    spring.datasource.testWhileIdle=true
    spring.datasource.testOnBorrow=false
    spring.datasource.testOnReturn=false
    spring.datasource.poolPreparedStatements=true
    spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
    spring.datasource.filters=stat,wall

\#\#\#datasource配置

package com.spring.config.datasource;
    
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.context.annotation.Configuration;
    
    @Configuration
    @ConfigurationProperties(prefix = "spring.datasource")
    public class DruidDataSourceProperties {
    	private String url;
    
    	private String username;
    
    	private String password;
    
    	private String driverClassName;
    
    	private int initialSize;
    
    	private int maxActive;
    
    	private int minIdle;
    
    	private int maxWait;
    
    	private long timeBetweenEvictionRunsMillis;
    
    	private long minEvictableIdleTimeMillis;
    
    	private String validationQuery;
    
    	private boolean testWhileIdle;
    
    	private boolean testOnBorrow;
    
    	private boolean testOnReturn;
    
    	private boolean poolPreparedStatements;
    
    	private int maxPoolPreparedStatementPerConnectionSize;
    
    	private String filters;
    
    
    
    
    	public DruidDataSourceProperties() {
    		// TODO Auto-generated constructor stub
    	}
    
    
    
    	public DruidDataSourceProperties(String url, String username,
                                          String password, String driverClassName, int initialSize,
                                          int maxActive, int minIdle, int maxWait,
                                          long timeBetweenEvictionRunsMillis,
                                          long minEvictableIdleTimeMillis, String validationQuery,
                                          boolean testWhileIdle, boolean testOnBorrow, boolean testOnReturn,
                                          boolean poolPreparedStatements,
                                          int maxPoolPreparedStatementPerConnectionSize, String filters) {
    		this.url = url;
    		this.username = username;
    		this.password = password;
    		this.driverClassName = driverClassName;
    		this.initialSize = initialSize;
    		this.maxActive = maxActive;
    		this.minIdle = minIdle;
    		this.maxWait = maxWait;
    		this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
    		this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
    		this.validationQuery = validationQuery;
    		this.testWhileIdle = testWhileIdle;
    		this.testOnBorrow = testOnBorrow;
    		this.testOnReturn = testOnReturn;
    		this.poolPreparedStatements = poolPreparedStatements;
    		this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;
    		this.filters = filters;
    	}
    	
    	
    
    	public String getUrl() {
    		return url;
    	}
    
    	public void setUrl(String url) {
    		this.url = url;
    	}
    
    	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 String getDriverClassName() {
    		return driverClassName;
    	}
    
    	public void setDriverClassName(String driverClassName) {
    		this.driverClassName = driverClassName;
    	}
    
    	public int getInitialSize() {
    		return initialSize;
    	}
    
    	public void setInitialSize(int initialSize) {
    		this.initialSize = initialSize;
    	}
    
    	public int getMaxActive() {
    		return maxActive;
    	}
    
    	public void setMaxActive(int maxActive) {
    		this.maxActive = maxActive;
    	}
    
    	public int getMinIdle() {
    		return minIdle;
    	}
    
    	public void setMinIdle(int minIdle) {
    		this.minIdle = minIdle;
    	}
    
    	public int getMaxWait() {
    		return maxWait;
    	}
    
    	public void setMaxWait(int maxWait) {
    		this.maxWait = maxWait;
    	}
    
    	public long getTimeBetweenEvictionRunsMillis() {
    		return timeBetweenEvictionRunsMillis;
    	}
    
    	public void setTimeBetweenEvictionRunsMillis(long timeBetweenEvictionRunsMillis) {
    		this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
    	}
    
    	public long getMinEvictableIdleTimeMillis() {
    		return minEvictableIdleTimeMillis;
    	}
    
    	public void setMinEvictableIdleTimeMillis(long minEvictableIdleTimeMillis) {
    		this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
    	}
    
    	public String getValidationQuery() {
    		return validationQuery;
    	}
    
    	public void setValidationQuery(String validationQuery) {
    		this.validationQuery = validationQuery;
    	}
    
    	public boolean isTestWhileIdle() {
    		return testWhileIdle;
    	}
    
    	public void setTestWhileIdle(boolean testWhileIdle) {
    		this.testWhileIdle = testWhileIdle;
    	}
    
    	public boolean isTestOnBorrow() {
    		return testOnBorrow;
    	}
    
    	public void setTestOnBorrow(boolean testOnBorrow) {
    		this.testOnBorrow = testOnBorrow;
    	}
    
    	public boolean isTestOnReturn() {
    		return testOnReturn;
    	}
    
    	public void setTestOnReturn(boolean testOnReturn) {
    		this.testOnReturn = testOnReturn;
    	}
    
    	public boolean isPoolPreparedStatements() {
    		return poolPreparedStatements;
    	}
    
    	public void setPoolPreparedStatements(boolean poolPreparedStatements) {
    		this.poolPreparedStatements = poolPreparedStatements;
    	}
    
    	public int getMaxPoolPreparedStatementPerConnectionSize() {
    		return maxPoolPreparedStatementPerConnectionSize;
    	}
    
    	public void setMaxPoolPreparedStatementPerConnectionSize(
    			int maxPoolPreparedStatementPerConnectionSize) {
    		this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;
    	}
    
    	public String getFilters() {
    		return filters;
    	}
    
    	public void setFilters(String filters) {
    		this.filters = filters;
    	}
    }

这个是跟application.properties中的各项属性一一对应的

定义数据连接池配置

package com.spring.config.datasource;
    
    import com.alibaba.druid.pool.DruidDataSource;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    
    import javax.sql.DataSource;
    
    @Configuration
    public class DruidDataSourceConfiguration {
        @Autowired
        private DruidDataSourceProperties properties;
    
        @Bean(name = "druidDataSource", initMethod = "init", destroyMethod = "close")
        @Qualifier("druidDataSource")
        public DataSource dataSource() throws Exception {
            DruidDataSource druidDataSource = new DruidDataSource();
            druidDataSource.setUrl(properties.getUrl());
            druidDataSource.setUsername(properties.getUsername());
            druidDataSource.setPassword(properties.getPassword());
            druidDataSource.setDriverClassName(properties.getDriverClassName());
            druidDataSource.setInitialSize(properties.getInitialSize());
            druidDataSource.setMaxActive(properties.getMaxActive());
            druidDataSource.setMinIdle(properties.getMinIdle());
            druidDataSource.setMaxWait(properties.getMaxWait());
            druidDataSource.setTimeBetweenEvictionRunsMillis(properties
                    .getTimeBetweenEvictionRunsMillis());
            druidDataSource.setMinEvictableIdleTimeMillis(properties
                    .getMinEvictableIdleTimeMillis());
            druidDataSource.setValidationQuery(properties.getValidationQuery());
            druidDataSource.setTestWhileIdle(properties.isTestWhileIdle());
            druidDataSource.setTestOnBorrow(properties.isTestOnBorrow());
            druidDataSource.setTestOnReturn(properties.isTestOnReturn());
            druidDataSource.setPoolPreparedStatements(properties
                    .isPoolPreparedStatements());
            druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(properties
                    .getMaxPoolPreparedStatementPerConnectionSize());
            druidDataSource.setFilters(properties.getFilters());
    
            try {
                if (null != druidDataSource) {
                    druidDataSource.setFilters("wall,stat");
                    druidDataSource.setUseGlobalDataSourceStat(true);
    //                Properties properties = new Properties();
    //                properties.setProperty("decrypt", "true");
    //                druidDataSource.setConnectProperties(properties);
                    druidDataSource.init();
                }
            } catch (Exception e) {
                throw new RuntimeException(
                        "load datasource error, dbProperties is :", e);
            }
            return druidDataSource;
        }
    }

\#\#\#jpa配置

package com.spring.config.jpa;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
    import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Primary;
    import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
    import org.springframework.orm.jpa.JpaTransactionManager;
    import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
    import org.springframework.transaction.PlatformTransactionManager;
    import org.springframework.transaction.annotation.EnableTransactionManagement;
    
    import javax.persistence.EntityManager;
    import javax.sql.DataSource;
    import java.util.Map;
    
    /**
     * jpa基础配置
     * @author chengjian
     * @date 19-3-6
     */
    @Configuration
    @EnableTransactionManagement
    @EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactory",
            transactionManagerRef = "transactionManager",
            basePackages = {"com.spring.dao"})//指定需要扫描的dao所在包
    public class RepositoryConfig {
    
        @Autowired
        private JpaProperties jpaProperties;
    
        @Autowired
        @Qualifier("druidDataSource")
        private DataSource druidDataSource;
    
        @Bean(name = "entityManager")
        @Primary
        public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
            return entityManagerFactory(builder).getObject().createEntityManager();
        }
    
        /**
         * 指定需要扫描的实体包实现与数据库关联
         * @param builder
         * @return
         */
        @Bean(name = "entityManagerFactory")
        @Primary
        public LocalContainerEntityManagerFactoryBean entityManagerFactory(EntityManagerFactoryBuilder builder) {
            return builder
                    .dataSource(druidDataSource)
                    .properties(getVendorProperties(druidDataSource))
                    .packages("com.spring.entity")
                    .build();
        }
    
        /**
         * 通过jpaProperties指定hibernate数据库方言以及在控制台打印sql语句
         * @param dataSource
         * @return
         */
        private Map<String, String> getVendorProperties(DataSource dataSource) {
            Map<String, String> map = jpaProperties.getProperties();
            map.put("hibernate.dialect", "org.hibernate.dialect.MySQL8Dialect");
            map.put("hibernate.show_sql", "true");
            return map;
        }
    
        /**
         * 创建事务管理
         * @param builder
         * @return
         */
        @Bean(name = "transactionManager")
        @Primary
        PlatformTransactionManager transactionManager(EntityManagerFactoryBuilder builder) {
            return new JpaTransactionManager(entityManagerFactory(builder).getObject());
        }
    
    }

\#\#\#entity配置

package com.spring.entity;
    
    import javax.persistence.*;
    
    @Entity
    @Table(name = "book",schema = "book")
    public class BookEntity {
        private String id;
        private String name;
        private String author;
        private String isbn;
        private String publish;
    
        @Id
        @Column(name = "id", nullable = false, length = 50)
        public String getId() {
            return id;
        }
    
        public void setId(String id) {
            this.id = id;
        }
    
        @Basic
        @Column(name = "name", nullable = false, length = 50)
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        @Basic
        @Column(name = "author", nullable = false, length = 50)
        public String getAuthor() {
            return author;
        }
    
        public void setAuthor(String author) {
            this.author = author;
        }
    
        @Basic
        @Column(name = "isbn", length = 50)
        public String getIsbn() {
            return isbn;
        }
    
        public void setIsbn(String isbn) {
            this.isbn = isbn;
        }
    
        @Basic
        @Column(name = "publish", length = 50)
        public String getPublish() {
            return publish;
        }
    
        public void setPublish(String publish) {
            this.publish = publish;
        }
    
        @Override
        public String toString() {
            return "BookEntity{" +
                    "id='" + id + '\'' +
                    ", name='" + name + '\'' +
                    ", author='" + author + '\'' +
                    ", isbn='" + isbn + '\'' +
                    ", publish='" + publish + '\'' +
                    '}';
        }
    }

\#\#\#Dao配置

package com.spring.dao;
    
    import com.spring.entity.BookEntity;
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.stereotype.Repository;
    
    @Repository
    public interface BookDao extends JpaRepository<BookEntity,String> {
    }

\#\#\#测试类

package com.spring.service.impl;
    
    import com.spring.entity.BookEntity;
    import com.spring.service.BookService;
    import com.spring.util.UUIDUtil;
    import org.junit.Test;
    import org.junit.runner.RunWith;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.boot.test.context.SpringBootTest;
    import org.springframework.test.context.junit4.SpringRunner;
    
    import java.util.List;
    
    import static org.junit.Assert.*;
    
    @RunWith(SpringRunner.class)
    @SpringBootTest
    public class BookServiceImplTest {
    
        @Autowired
        private BookService bookService;
    
    
        @Test
        public void save() {
            BookEntity bookEntity=new BookEntity();
            bookEntity.setId(UUIDUtil.getUUID32());
            bookEntity.setName("abc");
            bookEntity.setAuthor("abc");
            bookEntity.setIsbn(UUIDUtil.getUUID32());
            bookEntity.setPublish("abc");
            try {
                bookService.save(bookEntity);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    
        @Test
        public void delete() {
        }
    
        @Test
        public void findAll() {
            try {
                List<BookEntity> list=bookService.findAll();
                System.out.println(list.size());
                for (BookEntity entity:list){
                    System.out.println(entity.toString());
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    
        @Test
        public void findByPage() {
        }
    }

可执行代码地址github  
https://github.com/jackycheng86/spring.git