springboot-mybatis-druid

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
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值