說明
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
輸入賬戶:admin 密碼:123456 這是在servlet裡設定的,可修改與資料庫管理者登入那個可以不一緻
結果:
保持伺服器運作再開一個頁面輸入http://127.0.0.1:8080/MavenSpringBoot/accService
結果:
再次檢視druid:
附:常見問題處理:https://github.com/alibaba/druid/wiki/%E5%B8%B8%E8%A7%81%E9%97%AE%E9%A2%98