天天看點

Druid在SpringBoot下的使用

說明

Druid是Java語言中最好的資料庫連接配接池。Druid能夠提供強大的監控和擴充功能。DruidDataSource支援的資料庫:

理論上說,支援所有有jdbc驅動的資料庫。實際測試過的有

資料庫 支援狀态
mysql 支援,大規模使用
oracle 支援,大規模使用
sqlserver 支援
postgres 支援
db2 支援
h2 支援
derby 支援
sqlite 支援
sybase 支援

更新:最新druid-spring-boot-starter的使用戳-->druid-spring-boot-starter:1.1.10

Druid可以做什麼?

        可以監控資料庫通路性能,Druid内置提供了一個功能強大的StatFilter插件,能夠詳細統計SQL的執行性能,這對于線上分析資料庫通路性能有幫助。

           替換DBCP和C3P0。Druid提供了一個高效、功能強大、可擴充性好的資料庫連接配接池。

        資料庫密碼加密。直接把資料庫密碼寫在配置檔案中,這是不好的行為,容易導緻安全問題。DruidDruiver和DruidDataSource都支援PasswordCallback。

        SQL執行日志,Druid提供了不同的LogFilter,能夠支援Common-Logging、Log4j和JdkLog,你可以按需要選擇相應的LogFilter,監控你應用的資料庫通路情況。

        擴充JDBC,如果你要對JDBC層有程式設計的需求,可以通過Druid提供的Filter-Chain機制,很友善編寫JDBC層的擴充插件

步驟

1 更新pom.xml

<!-- 添加SQLServer驅動-->
		<dependency>
			<groupId>com.microsoft.sqlserver</groupId>
			<artifactId>sqljdbc4</artifactId>
			<version>4.2</version>
		</dependency>

		<!-- 配置阿裡druid連接配接池,通過改變spring.datasource.type設定資料源 -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.0.29</version>
		</dependency>
           

說明:sqljdbc4不能直接通過Maven下載下傳,需要自己去官網下載下傳了之後即将jar包放到repository\com\microsoft\sqlserver\sqljdbc4\4.2目錄下,這個問題可以通過檢視日志和百度找到位置。

2 更新application.properties或者是application.yml檔案

application.properties:

#配置上下文,根據自己項目設定
server.contextPath=/MavenSpringBoot
#配置tomcat插件啟動端口号
#server.port=8081

#中文及編碼配置
#server.tomcat.uri-encoding=UTF-8
#spring.http.encoding.charset=UTF-8
#spring.http.encoding.enabled=true
#spring.http.encoding.force=true
#spring.messages.encoding=UTF-8

#配置DataSource,使用druid
#需要注意的是:spring.datasource.type屬性,舊的spring boot版本是不能識别的。
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.url=jdbc:sqlserver://localhost:1433; DatabaseName=test
spring.datasource.username=test
spring.datasource.password=123456

# 連接配接池的配置資訊
# 初始化大小,最小,最大
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=20
# 配置擷取連接配接等待逾時的時間
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
# 打開PSCache,并且指定每個連接配接上PSCache的大小
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
# 配置監控統計攔截的filters,去掉後監控界面sql無法統計,'wall'用于防火牆
spring.datasource.filters=stat,wall,log4j
# 通過connectProperties屬性來打開mergeSql功能;慢SQL記錄
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
           

application.yml:

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
    url: jdbc:sqlserver://localhost:1433; DatabaseName=test
    username: test
    password: 123456
    # 連接配接池的配置資訊
    # 初始化大小,最小,最大
    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,log4j
    # 通過connectProperties屬性來打開mergeSql功能;慢SQL記錄
    connectionProperties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
           

3 配置Druid

由于Druid暫時不在Spring Boot中的直接支援,故需要進行配置資訊的定制:

1)config:

package test.config;

import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import com.alibaba.druid.pool.DruidDataSource;

@Configuration
public class DruidDBConfig {
	private Logger logger = Logger.getLogger(this.getClass());	//log4j日志
	
    @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.initialSize}")
    private int initialSize;
    
    @Value("${spring.datasource.minIdle}")
    private int minIdle;
    
    @Value("${spring.datasource.maxActive}")
    private int maxActive;
    
    @Value("${spring.datasource.maxWait}")
    private int maxWait;
    
    @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
    private int timeBetweenEvictionRunsMillis;
    
    @Value("${spring.datasource.minEvictableIdleTimeMillis}")
    private int minEvictableIdleTimeMillis;
    
    @Value("${spring.datasource.validationQuery}")
    private String validationQuery;
    
    @Value("${spring.datasource.testWhileIdle}")
    private boolean testWhileIdle;
    
    @Value("${spring.datasource.testOnBorrow}")
    private boolean testOnBorrow;
    
    @Value("${spring.datasource.testOnReturn}")
    private boolean testOnReturn;
    
    @Value("${spring.datasource.poolPreparedStatements}")
    private boolean poolPreparedStatements;
    
    @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
    private int maxPoolPreparedStatementPerConnectionSize;
    
    @Value("${spring.datasource.filters}")
    private String filters;
    
    @Value("{spring.datasource.connectionProperties}")
    private String connectionProperties;
    
    @Bean     //聲明其為Bean執行個體
    @Primary  //在同樣的DataSource中,首先使用被标注的DataSource
    public DataSource dataSource(){
    	DruidDataSource datasource = new DruidDataSource();
    	
    	datasource.setUrl(this.dbUrl);
    	datasource.setUsername(username);
    	datasource.setPassword(password);
    	datasource.setDriverClassName(driverClassName);
    	
    	//configuration
    	datasource.setInitialSize(initialSize);
    	datasource.setMinIdle(minIdle);
    	datasource.setMaxActive(maxActive);
    	datasource.setMaxWait(maxWait);
    	datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
    	datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
    	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) {
			logger.error("druid configuration initialization filter", e);
		}
    	datasource.setConnectionProperties(connectionProperties);
    	
    	return datasource;
    }
}
           

2)filter:

package test.filter;

import javax.servlet.annotation.WebFilter;
import javax.servlet.annotation.WebInitParam;

import com.alibaba.druid.support.http.WebStatFilter;

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

}
           

3)servlet:

package test.servlet;

import javax.servlet.annotation.WebInitParam;
import javax.servlet.annotation.WebServlet;

import com.alibaba.druid.support.http.StatViewServlet;

@WebServlet(urlPatterns="/druid/*",
    initParams={
         @WebInitParam(name="allow",value="127.0.0.1,192.168.163.1"),// IP白名單(沒有配置或者為空,則允許所有通路)
         @WebInitParam(name="deny",value="192.168.1.73"),// IP黑名單 (存在共同時,deny優先于allow)
         @WebInitParam(name="loginUsername",value="admin"),// 使用者名
         @WebInitParam(name="loginPassword",value="123456"),// 密碼
         @WebInitParam(name="resetEnable",value="false")// 禁用HTML頁面上的“Reset All”功能
})
public class DruidStatViewServlet extends StatViewServlet {
	private static final long serialVersionUID = -2688872071445249539L;

}
           

啟動類:SpringApplication(注意添加@ServletComponentScan)

package test;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.web.servlet.ServletComponentScan;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

// 傳回json字元串的資料,直接可以編寫RESTFul的接口
@RestController

// @SpringBootApplication聲明讓spring boot自動給程式進行必要的配置
@SpringBootApplication

// 添加servlet元件掃描,使得Spring能夠掃描到我們編寫的servlet和filter
@ServletComponentScan
public class SpringBootApp {

	public static void main(String[] args) {
		SpringApplication.run(SpringBootApp.class, args);
	}
}
           

4 編寫使用druid的DAO層

1)interface:

package test.interf;

import java.util.List;

import test.bean.Account;

public interface IAccountDAO {
	int add(Account account);

	int update(Account account);

	int delete(int id);

	Account findAccountById(int id);

	List<Account> findAccountList();
}
           
package test.interf;

import java.util.List;

import test.bean.Account;

public interface IAccountService {

    int add(Account account);

    int update(Account account);

    int delete(int id);

    Account findAccountById(int id);

    List<Account> findAccountList();

}
           

2)impl:

package test.dao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;

import test.bean.Account;
import test.interf.IAccountDAO;

import java.util.List;

@Repository
@Configuration
@Component
public class AccountDaoImpl implements IAccountDAO {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public int add(Account account) {
        return jdbcTemplate.update("insert into account(name, money) values(?, ?)",
              account.getName(),account.getMoney());

    }

    @Override
    public int update(Account account) {
        return jdbcTemplate.update("UPDATE  account SET NAME=? ,money=? WHERE id=?",
                account.getName(),account.getMoney(),account.getId());
    }

    @Override
    public int delete(int id) {
        return jdbcTemplate.update("DELETE from TABLE account where id=?",id);
    }

    @SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
    public Account findAccountById(int id) {
        List<Account> list = jdbcTemplate.query("select * from account where id = ?", new Object[]{id}, new BeanPropertyRowMapper(Account.class));
        if(list!=null && list.size()>0){
            Account account = list.get(0);
            return account;
        }else{
            return null;
        }
    }

    @SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
    public List<Account> findAccountList() {
        List<Account> list = jdbcTemplate.query("select * from account", new Object[]{}, new BeanPropertyRowMapper(Account.class));
        if(list!=null && list.size()>0){
            return list;
        }else{
            return null;
        }
    }
}
           

3)service:

package test.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import test.bean.Account;
import test.interf.IAccountDAO;
import test.interf.IAccountService;

@Service
public class AccountService implements IAccountService {
    @Autowired
    @Qualifier("accountDaoImpl")
    private IAccountDAO accDaoImpl;
    @Override
    public int add(Account account) {
        return accDaoImpl.add(account);
    }

    @Override
    public int update(Account account) {
        return accDaoImpl.update(account);
    }

    @Override
    public int delete(int id) {
        return accDaoImpl.delete(id);
    }

    @Override
    public Account findAccountById(int id) {
        return accDaoImpl.findAccountById(id);
    }

    @Override
    public List<Account> findAccountList() {
        return accDaoImpl.findAccountList();
    }
}
           

4)controller:

package test.controller;

import org.springframework.web.bind.annotation.RestController;

import test.bean.Account;
import test.interf.IAccountService;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.web.bind.annotation.RequestMapping;

@RestController
public class HelloController {
	@Autowired
	//使用@Qualifier來提示Spring該用什麼類型的資料填充該變量IoC
	@Qualifier("accountService")
    private IAccountService accService;
    @RequestMapping("/accService")
    public  List<Account> getAccounts(){
        return accService.findAccountList();
     }
    
}
           

5 運作測試

首先需要建表:

create table account(
	id int,
	name nvarchar(10),
	money float
)
insert account values(1,'yunlingfly',1.2)
insert account values(2,'test',1.6)
           

輸入http://127.0.0.1:8080/MavenSpringBoot/druid/login.html

Druid在SpringBoot下的使用

輸入賬戶:admin    密碼:123456    這是在servlet裡設定的,可修改與資料庫管理者登入那個可以不一緻

結果:

Druid在SpringBoot下的使用

保持伺服器運作再開一個頁面輸入http://127.0.0.1:8080/MavenSpringBoot/accService

結果:

Druid在SpringBoot下的使用

再次檢視druid:

Druid在SpringBoot下的使用

附:常見問題處理:https://github.com/alibaba/druid/wiki/%E5%B8%B8%E8%A7%81%E9%97%AE%E9%A2%98

繼續閱讀