pom文件
<?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 http://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.3.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>demo</name>
<description>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</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!--mybatis -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.1</version>
</dependency>
<!--druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.28</version>
</dependency>
<!--web -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
druid配置文件
package com.example.demo.config;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
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 org.springframework.context.annotation.Primary;
import javax.sql.DataSource;
import java.sql.SQLException;
/**
* Created by PrimaryKey on 17/2/4.
*/
@SuppressWarnings("AlibabaRemoveCommentedCode")
@Configuration
public class DruidDBConfig {
private Logger logger = LoggerFactory.getLogger(DruidDBConfig.class);
@Value("${spring.datasource.url}")
private String dbUrl;
@Value("${spring.datasource.username}")
private String username;
@Value("${spring.datasource.password}")
private String password;
@Value("${spring.datasource.driverClassName}")
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(initMethod = "init", destroyMethod = "close") //声明其为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;
}
@Bean
public ServletRegistrationBean druidServlet() {
//新建servlet
ServletRegistrationBean reg = new ServletRegistrationBean();
//Druid内置提供了一个StatViewServlet用于展示Druid的统计信息
reg.setServlet(new StatViewServlet());
reg.addUrlMappings("/druid/*");
reg.addInitParameter("allow", ""); //白名单
reg.addInitParameter("deny", "127.0.0.1"); //白名单
return reg;
}
@Bean
public FilterRegistrationBean filterRegistrationBean() {
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
filterRegistrationBean.setFilter(new WebStatFilter());
filterRegistrationBean.addUrlPatterns("/*");
//排除过滤的路径
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
//druid 0.2.7版本开始支持profile,配置profileEnable能够监控单个url调用的sql列表。
filterRegistrationBean.addInitParameter("profileEnable", "true");
//principalCookieName配置。如果你的user信息保存在cookie中,你可以配置principalCookieName,使得druid知道当前的user是谁
filterRegistrationBean.addInitParameter("principalCookieName","USER_COOKIE");
//principalSessionName配置。你可以配置principalSessionName,使得druid能够知道当前的session的用户是谁
filterRegistrationBean.addInitParameter("principalSessionName","USER_SESSION");
//拦截所有请求
filterRegistrationBean.addInitParameter("DruidWebStatFilter","/*");
return filterRegistrationBean;
}
}
接口userDao
package com.example.demo.dao;
import com.example.demo.domain.UserDO;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
import java.util.Map;
/**
*
* @author chglee
* @email 1992lcg@163.com
* @date 2017-10-03 09:45:11
*/
@Mapper
public interface UserDao {
UserDO get(Long userId);
List<UserDO> list(Map<String, Object> map);
int count(Map<String, Object> map);
int save(UserDO user);
int update(UserDO user);
int remove(Long userId);
int batchRemove(Long[] userIds);
Long[] listAllDept();
}
mapper文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.dao.UserDao">
<select id="get" resultType="com.example.demo.domain.UserDO">
select `user_id`,`username`,`name`,`password`,`dept_id`,`email`,`mobile`,`status`,`user_id_create`,`gmt_create`,`gmt_modified`,`sex`,`birth`,`pic_id`,`live_address`,`hobby`,`province`,`city`,`district` from sys_user where user_id = #{value}
</select>
<select id="list" resultType="com.example.demo.domain.UserDO">
<choose>
<when test="username!=null">
<bind name="likeUsername" value="'%' + username + '%'"/>
</when>
<otherwise>
<bind name="likeUsername" value="'%%'"/>
</otherwise>
</choose>
<choose>
<when test="name!=null">
<bind name="likeName" value="'%' + name + '%'"/>
</when>
<otherwise>
<bind name="likeName" value="'%%'"/>
</otherwise>
</choose>
<choose>
<when test="email!=null">
<bind name="likeEmail" value="'%' + email + '%'"/>
</when>
<otherwise>
<bind name="likeEmail" value="'%%'"/>
</otherwise>
</choose>
<choose>
<when test="status!=null">
<bind name="likeStatus" value="'%' + status + '%'"/>
</when>
<otherwise>
<bind name="likeStatus" value="'%%'"/>
</otherwise>
</choose>
select `user_id`,`username`,`name`,`password`,`dept_id`,`email`,`mobile`,`status`,`user_id_create`,`gmt_create`,`gmt_modified`,`sex`,`birth`,`pic_id`,`live_address`,`hobby`,`province`,`city`,`district` from sys_user
<where>
<if test="userId != null and userId != ''"> and user_id = #{userId} </if>
<if test="username != null and username != ''"> and username like #{likeUsername} </if>
<if test="name != null and name != ''"> and name like #{likeName} </if>
<if test="password != null and password != ''"> and password = #{password} </if>
<if test="deptId != null and deptId != ''"> and dept_id = #{deptId} </if>
<if test="email != null and email != ''"> and email like #{likeEmail} </if>
<if test="mobile != null and mobile != ''"> and mobile = #{mobile} </if>
<if test="status != null and status != ''"> and status like #{likeStatus} </if>
<if test="userIdCreate != null and userIdCreate != ''"> and user_id_create = #{userIdCreate} </if>
<if test="gmtCreate != null and gmtCreate != ''"> and gmt_create = #{gmtCreate} </if>
<if test="gmtModified != null and gmtModified != ''"> and gmt_modified = #{gmtModified} </if>
<if test="sex != null and sex != ''"> and sex = #{sex} </if>
<if test="birth != null and birth != ''"> and birth = #{birth} </if>
<if test="picId != null and picId != ''"> and pic_id = #{picId} </if>
<if test="liveAddress != null and liveAddress != ''"> and live_address = #{liveAddress} </if>
<if test="hobby != null and hobby != ''"> and hobby = #{hobby} </if>
<if test="province != null and province != ''"> and province = #{province} </if>
<if test="city != null and city != ''"> and city = #{city} </if>
<if test="district != null and district != ''"> and district = #{district} </if>
<if test="mulSsdw != null"> and dept_id in
<foreach collection="mulSsdw" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
</where>
<choose>
<when test="sort != null and sort.trim() != ''">
order by ${sort} ${order}
</when>
<otherwise>
order by user_id desc
</otherwise>
</choose>
<if test="offset != null and limit != null">
limit #{offset}, #{limit}
</if>
</select>
<select id="count" resultType="int">
<choose>
<when test="username!=null">
<bind name="likeUsername" value="'%' + username + '%'"/>
</when>
<otherwise>
<bind name="likeUsername" value="'%%'"/>
</otherwise>
</choose>
<choose>
<when test="name!=null">
<bind name="likeName" value="'%' + name + '%'"/>
</when>
<otherwise>
<bind name="likeName" value="'%%'"/>
</otherwise>
</choose>
<choose>
<when test="email!=null">
<bind name="likeEmail" value="'%' + email + '%'"/>
</when>
<otherwise>
<bind name="likeEmail" value="'%%'"/>
</otherwise>
</choose>
<choose>
<when test="status!=null">
<bind name="likeStatus" value="'%' + status + '%'"/>
</when>
<otherwise>
<bind name="likeStatus" value="'%%'"/>
</otherwise>
</choose>
select count(*) from sys_user
<where>
<if test="userId != null and userId != ''"> and user_id = #{userId} </if>
<if test="username != null and username != ''"> and username like #{likeUsername} </if>
<if test="name != null and name != ''"> and name like #{likeName} </if>
<if test="password != null and password != ''"> and password = #{password} </if>
<if test="deptId != null and deptId != ''"> and dept_id = #{deptId} </if>
<if test="email != null and email != ''"> and email like #{likeEmail} </if>
<if test="mobile != null and mobile != ''"> and mobile = #{mobile} </if>
<if test="status != null and status != ''"> and status like #{likeStatus} </if>
<if test="userIdCreate != null and userIdCreate != ''"> and user_id_create = #{userIdCreate} </if>
<if test="gmtCreate != null and gmtCreate != ''"> and gmt_create = #{gmtCreate} </if>
<if test="gmtModified != null and gmtModified != ''"> and gmt_modified = #{gmtModified} </if>
<if test="sex != null and sex != ''"> and sex = #{sex} </if>
<if test="birth != null and birth != ''"> and birth = #{birth} </if>
<if test="picId != null and picId != ''"> and pic_id = #{picId} </if>
<if test="liveAddress != null and liveAddress != ''"> and live_address = #{liveAddress} </if>
<if test="hobby != null and hobby != ''"> and hobby = #{hobby} </if>
<if test="province != null and province != ''"> and province = #{province} </if>
<if test="city != null and city != ''"> and city = #{city} </if>
<if test="district != null and district != ''"> and district = #{district} </if>
<if test="mulSsdw != null"> and dept_id in
<foreach collection="mulSsdw" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
</where>
</select>
<insert id="save" parameterType="com.example.demo.domain.UserDO" useGeneratedKeys="true" keyProperty="userId">
insert into sys_user
(
`username`,
`name`,
`password`,
`dept_id`,
`email`,
`mobile`,
`status`,
`user_id_create`,
`gmt_create`,
`gmt_modified`,
`sex`,
`birth`,
`pic_id`,
`live_address`,
`hobby`,
`province`,
`city`,
`district`
)
values
(
#{username},
#{name},
#{password},
#{deptId},
#{email},
#{mobile},
#{status},
#{userIdCreate},
#{gmtCreate},
#{gmtModified},
#{sex},
#{birth},
#{picId},
#{liveAddress},
#{hobby},
#{province},
#{city},
#{district}
)
</insert>
<update id="update" parameterType="com.example.demo.domain.UserDO">
update sys_user
<set>
<if test="username != null">`username` = #{username}, </if>
<if test="name != null">`name` = #{name}, </if>
<if test="password != null">`password` = #{password}, </if>
<if test="deptId != null">`dept_id` = #{deptId}, </if>
<if test="email != null">`email` = #{email}, </if>
<if test="mobile != null">`mobile` = #{mobile}, </if>
<if test="status != null">`status` = #{status}, </if>
<if test="userIdCreate != null">`user_id_create` = #{userIdCreate}, </if>
<if test="gmtCreate != null">`gmt_create` = #{gmtCreate}, </if>
<if test="gmtModified != null">`gmt_modified` = #{gmtModified}, </if>
<if test="sex != null">`sex` = #{sex}, </if>
<if test="birth != null">`birth` = #{birth}, </if>
<if test="picId != null">`pic_id` = #{picId}, </if>
<if test="liveAddress != null">`live_address` = #{liveAddress}, </if>
<if test="hobby != null">`hobby` = #{hobby}, </if>
<if test="province != null">`province` = #{province}, </if>
<if test="city != null">`city` = #{city}, </if>
<if test="district != null">`district` = #{district}</if>
</set>
where user_id = #{userId}
</update>
<delete id="remove">
delete from sys_user where user_id = #{value}
</delete>
<delete id="batchRemove">
delete from sys_user where user_id in
<foreach item="userId" collection="array" open="(" separator="," close=")">
#{userId}
</foreach>
</delete>
<select id="listAllDept" resultType="long">
select DISTINCT dept_id from sys_user
</select>
</mapper>
yml配置文件
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3308/zczp?useUnicode=true&characterEncoding=utf8
username: root
password: bjj@password01!
initialSize: 1
minIdle: 3
maxActive: 20
# 配置获取连接等待超时的时间
maxWait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 30000
validationQuery: select 'x'
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
mybatis:
configuration:
map-underscore-to-camel-case: true
mapper-locations: /mapper/**/*Mapper.xml
typeAliasesPackage: com.example.**.domain
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl