Java应用程序开发中,常用的连接池有DBCP、C3P0、Proxool等,而Druid是阿里开源的,springboot默认提供的数据源为org.apache.tomcat.jdbc.pool.DataSource,这里我们使用阿里提供的Druid数据源
pom中添加依赖
<!-- 添加druid数据源 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
application.yml配置修改
# Tomcat
server:
tomcat:
uri-encoding: UTF-8
max-threads: 1000
min-spare-threads: 30
port: 8080
servlet:context-path: /
spring:
datasource:
name: druidDataSource
type: com.alibaba.druid.pool.DruidDataSource
druid:
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/kitty?useUnicode=true&characterEncoding=utf-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
username: root
password: root
filter: stat,wall,log4j,config
max-active: 100
initial-size: 1
max-wait: 60000
min-idle: 1
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
validation-query: select 'x'
test-while-idle: true
test-on-borrow: false
test-on-return: false
pool-prepared-statements: true
max-open-prepared-statements: 50
max-pool-prepared-statement-per-connection-size: 20
# pagehelper
pagehelper:
helperDialect: mysql
reasonable: true
supportMethodsArguments: true
params: count=countSql
添加DruidDataSourceProperties
package com.example.springboot.config;
import org.springframework.boot.context.properties.ConfigurationProperties;
@ConfigurationProperties(prefix = "spring.datasource.druid")
public class DruidDataSourceProperties {
// jdbc
private String driverClassName;
private String url;
private String username;
private String password;
// jdbc connection pool
private int initialSize;
private int minIdle;
private int maxActive = 100;
private long 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;
// filter
private String filters;
public int getInitialSize() {
return initialSize;
}
public void setInitialSize(int initialSize) {
this.initialSize = initialSize;
}
public int getMinIdle() {
return minIdle;
}
public void setMinIdle(int minIdle) {
this.minIdle = minIdle;
}
public int getMaxActive() {
return maxActive;
}
public void setMaxActive(int maxActive) {
this.maxActive = maxActive;
}
public long getMaxWait() {
return maxWait;
}
public void setMaxWait(long 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;
}
public String getDriverClassName() {
return driverClassName;
}
public void setDriverClassName(String driverClassName) {
this.driverClassName = driverClassName;
}
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;
}
}
添加DruidConfig
package com.example.springboot.config;
import java.sql.SQLException;
import javax.servlet.Filter;
import javax.servlet.Servlet;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
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 com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
@Configuration
@EnableConfigurationProperties({DruidDataSourceProperties.class})
public class DruidConfig {
@Autowired
private DruidDataSourceProperties properties;
@Bean
@ConditionalOnMissingBean
public DataSource druidDataSource() {
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setDriverClassName(properties.getDriverClassName());
druidDataSource.setUrl(properties.getUrl());
druidDataSource.setUsername(properties.getUsername());
druidDataSource.setPassword(properties.getPassword());
druidDataSource.setInitialSize(properties.getInitialSize());
druidDataSource.setMinIdle(properties.getMinIdle());
druidDataSource.setMaxActive(properties.getMaxActive());
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());
try {
druidDataSource.setFilters(properties.getFilters());
druidDataSource.init();
} catch (SQLException e) {
e.printStackTrace();
}
return druidDataSource;
}
/**
* 注册Servlet信息, 配置监控视图
*
* @return
*/
@Bean
@ConditionalOnMissingBean
public ServletRegistrationBean<Servlet> druidServlet() {
ServletRegistrationBean<Servlet> servletRegistrationBean = new ServletRegistrationBean<Servlet>(new StatViewServlet(), "/druid/*");
//白名单:
servletRegistrationBean.addInitParameter("allow","192.168.1.195");
//IP黑名单 (存在共同时,deny优先于allow) : 如果满足deny的话提示:Sorry, you are not permitted to view this page.
servletRegistrationBean.addInitParameter("deny","192.168.1.119");
//登录查看信息的账号密码, 用于登录Druid监控后台
servletRegistrationBean.addInitParameter("loginUsername", "admin");
servletRegistrationBean.addInitParameter("loginPassword", "admin");
//是否能够重置数据.
servletRegistrationBean.addInitParameter("resetEnable", "true");
return servletRegistrationBean;
}
/**
* 注册Filter信息, 监控拦截器
*
* @return
*/
@Bean
@ConditionalOnMissingBean
public FilterRegistrationBean<Filter> filterRegistrationBean() {
FilterRegistrationBean<Filter> filterRegistrationBean = new FilterRegistrationBean<Filter>();
filterRegistrationBean.setFilter(new WebStatFilter());
filterRegistrationBean.addUrlPatterns("/*");
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
return filterRegistrationBean;
}
}
配置监控拦截器DruidStatFilter
package com.example.springboot.config;
import javax.servlet.annotation.WebFilter;
import javax.servlet.annotation.WebInitParam;
import com.alibaba.druid.support.http.WebStatFilter;
/**
* 配置监控拦截器, druid监控拦截器
*/
@WebFilter(filterName="druidWebStatFilter",
urlPatterns="/*",
initParams={
@WebInitParam(name="exclusions",value="*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*"), // 忽略资源
})
public class DruidStatFilter extends WebStatFilter {
}
配置Druid监控视图DruidStatViewServlet
package com.example.springboot.config;
import javax.servlet.annotation.WebInitParam;
import javax.servlet.annotation.WebServlet;
import com.alibaba.druid.support.http.StatViewServlet;
/**
* druid监控视图配置
*/
@WebServlet(urlPatterns = "/druid/*", initParams={
@WebInitParam(name="allow",value="192.168.6.195"), // IP白名单 (没有配置或者为空,则允许所有访问)
@WebInitParam(name="deny",value="192.168.6.73"), // IP黑名单 (存在共同时,deny优先于allow)
@WebInitParam(name="loginUsername",value="admin"), // 用户名
@WebInitParam(name="loginPassword",value="admin"), // 密码
@WebInitParam(name="resetEnable",value="true") // 禁用HTML页面上的“Reset All”功能
})
public class DruidStatViewServlet extends StatViewServlet {
private static final long serialVersionUID = 7359758657306626394L;
}
对结果进行封装,添加HttpStatus、HttpResult
package com.example.springboot.config;
/**
* Constants enumerating the HTTP status codes.
* All status codes defined in RFC1945 (HTTP/1.0), RFC2616 (HTTP/1.1), and
* RFC2518 (WebDAV) are listed.
*
* @see StatusLine
*
* @since 4.0
*/
public interface HttpStatus {
// --- 1xx Informational ---
/** {@code 100 Continue} (HTTP/1.1 - RFC 2616) */
public static final int SC_CONTINUE = 100;
/** {@code 101 Switching Protocols} (HTTP/1.1 - RFC 2616)*/
public static final int SC_SWITCHING_PROTOCOLS = 101;
/** {@code 102 Processing} (WebDAV - RFC 2518) */
public static final int SC_PROCESSING = 102;
// --- 2xx Success ---
/** {@code 200 OK} (HTTP/1.0 - RFC 1945) */
public static final int SC_OK = 200;
/** {@code 201 Created} (HTTP/1.0 - RFC 1945) */
public static final int SC_CREATED = 201;
/** {@code 202 Accepted} (HTTP/1.0 - RFC 1945) */
public static final int SC_ACCEPTED = 202;
/** {@code 203 Non Authoritative Information} (HTTP/1.1 - RFC 2616) */
public static final int SC_NON_AUTHORITATIVE_INFORMATION = 203;
/** {@code 204 No Content} (HTTP/1.0 - RFC 1945) */
public static final int SC_NO_CONTENT = 204;
/** {@code 205 Reset Content} (HTTP/1.1 - RFC 2616) */
public static final int SC_RESET_CONTENT = 205;
/** {@code 206 Partial Content} (HTTP/1.1 - RFC 2616) */
public static final int SC_PARTIAL_CONTENT = 206;
/**
* {@code 207 Multi-Status} (WebDAV - RFC 2518)
* or
* {@code 207 Partial Update OK} (HTTP/1.1 - draft-ietf-http-v11-spec-rev-01?)
*/
public static final int SC_MULTI_STATUS = 207;
// --- 3xx Redirection ---
/** {@code 300 Mutliple Choices} (HTTP/1.1 - RFC 2616) */
public static final int SC_MULTIPLE_CHOICES = 300;
/** {@code 301 Moved Permanently} (HTTP/1.0 - RFC 1945) */
public static final int SC_MOVED_PERMANENTLY = 301;
/** {@code 302 Moved Temporarily} (Sometimes {@code Found}) (HTTP/1.0 - RFC 1945) */
public static final int SC_MOVED_TEMPORARILY = 302;
/** {@code 303 See Other} (HTTP/1.1 - RFC 2616) */
public static final int SC_SEE_OTHER = 303;
/** {@code 304 Not Modified} (HTTP/1.0 - RFC 1945) */
public static final int SC_NOT_MODIFIED = 304;
/** {@code 305 Use Proxy} (HTTP/1.1 - RFC 2616) */
public static final int SC_USE_PROXY = 305;
/** {@code 307 Temporary Redirect} (HTTP/1.1 - RFC 2616) */
public static final int SC_TEMPORARY_REDIRECT = 307;
// --- 4xx Client Error ---
/** {@code 400 Bad Request} (HTTP/1.1 - RFC 2616) */
public static final int SC_BAD_REQUEST = 400;
/** {@code 401 Unauthorized} (HTTP/1.0 - RFC 1945) */
public static final int SC_UNAUTHORIZED = 401;
/** {@code 402 Payment Required} (HTTP/1.1 - RFC 2616) */
public static final int SC_PAYMENT_REQUIRED = 402;
/** {@code 403 Forbidden} (HTTP/1.0 - RFC 1945) */
public static final int SC_FORBIDDEN = 403;
/** {@code 404 Not Found} (HTTP/1.0 - RFC 1945) */
public static final int SC_NOT_FOUND = 404;
/** {@code 405 Method Not Allowed} (HTTP/1.1 - RFC 2616) */
public static final int SC_METHOD_NOT_ALLOWED = 405;
/** {@code 406 Not Acceptable} (HTTP/1.1 - RFC 2616) */
public static final int SC_NOT_ACCEPTABLE = 406;
/** {@code 407 Proxy Authentication Required} (HTTP/1.1 - RFC 2616)*/
public static final int SC_PROXY_AUTHENTICATION_REQUIRED = 407;
/** {@code 408 Request Timeout} (HTTP/1.1 - RFC 2616) */
public static final int SC_REQUEST_TIMEOUT = 408;
/** {@code 409 Conflict} (HTTP/1.1 - RFC 2616) */
public static final int SC_CONFLICT = 409;
/** {@code 410 Gone} (HTTP/1.1 - RFC 2616) */
public static final int SC_GONE = 410;
/** {@code 411 Length Required} (HTTP/1.1 - RFC 2616) */
public static final int SC_LENGTH_REQUIRED = 411;
/** {@code 412 Precondition Failed} (HTTP/1.1 - RFC 2616) */
public static final int SC_PRECONDITION_FAILED = 412;
/** {@code 413 Request Entity Too Large} (HTTP/1.1 - RFC 2616) */
public static final int SC_REQUEST_TOO_LONG = 413;
/** {@code 414 Request-URI Too Long} (HTTP/1.1 - RFC 2616) */
public static final int SC_REQUEST_URI_TOO_LONG = 414;
/** {@code 415 Unsupported Media Type} (HTTP/1.1 - RFC 2616) */
public static final int SC_UNSUPPORTED_MEDIA_TYPE = 415;
/** {@code 416 Requested Range Not Satisfiable} (HTTP/1.1 - RFC 2616) */
public static final int SC_REQUESTED_RANGE_NOT_SATISFIABLE = 416;
/** {@code 417 Expectation Failed} (HTTP/1.1 - RFC 2616) */
public static final int SC_EXPECTATION_FAILED = 417;
/**
* Static constant for a 418 error.
* {@code 418 Unprocessable Entity} (WebDAV drafts?)
* or {@code 418 Reauthentication Required} (HTTP/1.1 drafts?)
*/
// not used
// public static final int SC_UNPROCESSABLE_ENTITY = 418;
/**
* Static constant for a 419 error.
* {@code 419 Insufficient Space on Resource}
* (WebDAV - draft-ietf-webdav-protocol-05?)
* or {@code 419 Proxy Reauthentication Required}
* (HTTP/1.1 drafts?)
*/
public static final int SC_INSUFFICIENT_SPACE_ON_RESOURCE = 419;
/**
* Static constant for a 420 error.
* {@code 420 Method Failure}
* (WebDAV - draft-ietf-webdav-protocol-05?)
*/
public static final int SC_METHOD_FAILURE = 420;
/** {@code 422 Unprocessable Entity} (WebDAV - RFC 2518) */
public static final int SC_UNPROCESSABLE_ENTITY = 422;
/** {@code 423 Locked} (WebDAV - RFC 2518) */
public static final int SC_LOCKED = 423;
/** {@code 424 Failed Dependency} (WebDAV - RFC 2518) */
public static final int SC_FAILED_DEPENDENCY = 424;
// --- 5xx Server Error ---
/** {@code 500 Server Error} (HTTP/1.0 - RFC 1945) */
public static final int SC_INTERNAL_SERVER_ERROR = 500;
/** {@code 501 Not Implemented} (HTTP/1.0 - RFC 1945) */
public static final int SC_NOT_IMPLEMENTED = 501;
/** {@code 502 Bad Gateway} (HTTP/1.0 - RFC 1945) */
public static final int SC_BAD_GATEWAY = 502;
/** {@code 503 Service Unavailable} (HTTP/1.0 - RFC 1945) */
public static final int SC_SERVICE_UNAVAILABLE = 503;
/** {@code 504 Gateway Timeout} (HTTP/1.1 - RFC 2616) */
public static final int SC_GATEWAY_TIMEOUT = 504;
/** {@code 505 HTTP Version Not Supported} (HTTP/1.1 - RFC 2616) */
public static final int SC_HTTP_VERSION_NOT_SUPPORTED = 505;
/** {@code 507 Insufficient Storage} (WebDAV - RFC 2518) */
public static final int SC_INSUFFICIENT_STORAGE = 507;
}
package com.example.springboot.config;
public class HttpResult {
private int code;
private String msg;
private Object data;
public static HttpResult error() {
return error(HttpStatus.SC_INTERNAL_SERVER_ERROR, "未知异常,请联系管理员");
}
public static HttpResult error(String msg) {
return error(HttpStatus.SC_INTERNAL_SERVER_ERROR, msg);
}
public static HttpResult error(int code, String msg) {
HttpResult r = new HttpResult();
r.setCode(code);
r.setMsg(msg);
return r;
}
public static HttpResult ok(String msg) {
HttpResult r = new HttpResult();
r.setMsg(msg);
return r;
}
public static HttpResult ok(Object data) {
HttpResult r = new HttpResult();
r.setData(data);
return r;
}
public static HttpResult ok() {
return new HttpResult();
}
public int getCode() {
return code;
}
public void setCode(int code) {
this.code = code;
}
public String getMsg() {
return msg;
}
public void setMsg(String msg) {
this.msg = msg;
}
public Object getData() {
return data;
}
public void setData(Object data) {
this.data = data;
}
}
service下添加CurdService通用接口
package com.example.springboot.service;
import com.example.springboot.config.PageRequest;
import com.example.springboot.config.PageResult;
import java.util.List;
/**
* 通用CURD接口
*/
public interface CurdService<T> {
/**
* 保存操作
* @param record
* @return
*/
int save(T record);
/**
* 更新操作
* @param record
* @return
*/
int update(T record);
/**
* 删除操作
* @param record
* @return
*/
int delete(T record);
/**
* 批量删除操作
* @param entities
*/
int delete(List<T> records);
/**
* 根据ID查询
* @param id
* @return
*/
T findById(Long id);
/**
* 分页查询
* 这里统一封装了分页请求和结果,避免直接引入具体框架的分页对象, 如MyBatis或JPA的分页对象
* 从而避免因为替换ORM框架而导致服务层、控制层的分页接口也需要变动的情况,替换ORM框架也不会
* 影响服务层以上的分页接口,起到了解耦的作用
* @param pageRequest 自定义,统一分页查询请求
* @return PageResult 自定义,统一分页查询结果
*/
PageResult findPage(PageRequest pageRequest);
}
service下的SysUserService继承CurdService
package com.example.springboot.service;
import com.example.springboot.config.PageRequest;
import com.example.springboot.config.PageResult;
import com.example.springboot.model.SysUser;
import java.util.List;
public interface SysUserService extends CurdService<SysUser>{
/**
* 根据用户ID查找用户
* @param userId
* @return
*/
SysUser findByUserId(Long userId);
/**
* 查找所有用户
* @return
*/
List<SysUser> findAll();
PageResult findPage(PageRequest pageRequest);
}
修改controller的返回结果
package com.example.springboot.controller;
import com.example.springboot.config.HttpResult;
import com.example.springboot.config.PageRequest;
import com.example.springboot.service.SysUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
@RestController
@RequestMapping("user")
public class SysUserController {
@Autowired
private SysUserService sysUserService;
@GetMapping(value="/findByUserId")
public HttpResult findByUserId(@RequestParam Long userId) {
return HttpResult.ok(sysUserService.findByUserId(userId));
}
@GetMapping(value="/findAll")
public HttpResult findAll() {
return HttpResult.ok(sysUserService.findAll());
}
@PostMapping(value="/findPage")
public HttpResult findPage(@RequestBody PageRequest pageQuery) {
return HttpResult.ok(sysUserService.findPage(pageQuery));
}
}
SysUserServiceImpl
package com.example.springboot.service.serviceImpl;
import com.example.springboot.config.PageRequest;
import com.example.springboot.config.PageResult;
import com.example.springboot.config.PageUtils;
import com.example.springboot.dao.SysUserMapper;
import com.example.springboot.model.SysUser;
import com.example.springboot.service.SysUserService;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class SysUserServiceImpl implements SysUserService {
@Autowired
private SysUserMapper sysUserMapper;
@Override
public int save(SysUser record) {
return sysUserMapper.insertSelective(record);
}
@Override
public int update(SysUser record) {
return sysUserMapper.updateByPrimaryKeySelective(record);
}
@Override
public int delete(SysUser record) {
return sysUserMapper.deleteByPrimaryKey(record.getId());
}
@Override
public int delete(List<SysUser> records) {
for(SysUser record:records) {
delete(record);
}
return 1;
}
@Override
public SysUser findById(Long id) {
return sysUserMapper.selectByPrimaryKey(id);
}
@Override
public SysUser findByUserId(Long userId) {
return sysUserMapper.selectByPrimaryKey(userId);
}
@Override
public List<SysUser> findAll() {
return sysUserMapper.selectAll();
}
@Override
public PageResult findPage(PageRequest pageRequest) {
return PageUtils.getPageResult(pageRequest, getPageInfo(pageRequest));
}
/**
* 调用分页插件完成分页
* @param pageRequest
* @return
*/
private PageInfo<SysUser> getPageInfo(PageRequest pageRequest) {
int pageNum = pageRequest.getPageNum();
int pageSize = pageRequest.getPageSize();
PageHelper.startPage(pageNum, pageSize);
List<SysUser> sysUsers = sysUserMapper.findPage();
return new PageInfo<SysUser>(sysUsers);
}
}