springBoot系列教程04:mybatis及druid数据源的集成及查询缓存的使用

首先说下查询缓存:查询缓存就是相同的数据库查询请求在设定的时间间隔内仅查询一次数据库并保存到redis中,后续的请求只要在时间间隔内都直接从redis中获取,不再查询数据库,提高查询效率,降低服务器负荷

通过druid数据源和mybatis来操作数据库

1.pom引入

        <!-- 使用druid配置mysql数据源 -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.18</version>
        </dependency>

2.数据库链接信息配置

database.type=mysql
spring.datasource.name=test
spring.datasource.url=jdbc:mysql://192.168.032:3306/test?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.filters=stat
spring.datasource.maxActive=20
spring.datasource.initialSize=1
spring.datasource.maxWait=60000
spring.datasource.minIdle=1
spring.datasource.timeBetweenEvictionRunsMillis=60000
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=select 'x'
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
spring.datasource.poolPreparedStatements=true
spring.datasource.maxOpenPreparedStatements=20

3.数据源配置(部分内容及标签在后续文章中会提及到)

package com.xiao.config;

import java.sql.SQLException;

import javax.sql.DataSource;

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.cloud.context.config.annotation.RefreshScope;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;

@Configuration
@RefreshScope
public class DruidConfig {
    private Logger logger = LoggerFactory.getLogger(getClass());

    @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.filters}")
    private String filters;

    @Bean
    public ServletRegistrationBean druidServlet() {
        ServletRegistrationBean reg = new ServletRegistrationBean();
        reg.setServlet(new StatViewServlet());
        reg.addUrlMappings("/druid/*");
        reg.addInitParameter("loginUsername", "admin");
        reg.addInitParameter("loginPassword", "123456");
        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/*");
        filterRegistrationBean.addInitParameter("profileEnable", "true");
        filterRegistrationBean.addInitParameter("principalCookieName", "USER_COOKIE");
        filterRegistrationBean.addInitParameter("principalSessionName", "USER_SESSION");
        return filterRegistrationBean;
    }

    @Bean
    @Primary
    @RefreshScope
    public DataSource druidDataSource() {
        DruidDataSource datasource = new DruidDataSource();

        datasource.setUrl(this.dbUrl);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);
        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);
        try {
            datasource.setFilters(filters);
        } catch (SQLException e) {
            logger.error("druid configuration initialization filter", e);
        }
        return datasource;
    }

}

4.配置sessionFactory及事物

package com.xiao.config;

import javax.sql.DataSource;

import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.transaction.annotation.TransactionManagementConfigurer;

import com.xiao.interceptor.PageInterceptor;

@Configuration
@EnableTransactionManagement
@MapperScan("com.xiao.mapper")
public class SessionFactoryConfig implements TransactionManagementConfigurer {

    @Autowired
    private DataSource dataSource;

    private String typeAliasPackage = "com.xiao.domain";

    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactoryBean createSqlSessionFactoryBean() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setPlugins(new Interceptor[] { new PageInterceptor() });
        sqlSessionFactoryBean.setTypeAliasesPackage(typeAliasPackage);
        Resource[] resources = new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/*.xml");
        sqlSessionFactoryBean.setMapperLocations(resources);
        return sqlSessionFactoryBean;
    }

    @Bean
    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean
    @Override
    public PlatformTransactionManager annotationDrivenTransactionManager() {
        return new DataSourceTransactionManager(dataSource);
    }
}

5.定义mapper

package com.xiao.mapper;

import java.util.List;

import org.springframework.cache.annotation.CacheConfig;
import org.springframework.cache.annotation.Cacheable;

import com.xiao.domain.User;
import com.xiao.domain.UserParamVo;

/**
 * @since 2017年12月7日 下午1:58:46
 * @author 肖昌伟 317409898@qq.com
 * @description
 */

@CacheConfig(cacheNames = "users") 
public interface UserMapper {
    
    @Cacheable(key ="#p0") 
    public int dataCount(String tableName);

    public List<User> getUsers(UserParamVo param);
}

注意上面红色部分,这部分标记为对datacount方法进行缓存,缓存时间在redis的配置中

    @Autowired
    UserMapper userMapper;

    @RequestMapping(value = "/dbcache/test")
    public Result cacheTest(@RequestParam(value = "tableName") String tableName) {
        return new Result("数据库中【" + tableName + "】表的条数为:" + userMapper.dataCount(tableName) + " [10秒钟内多次请求仅访问一次数据库]");
    }

执行上面请求后并不断刷新,发现仅请求一次,超过设置的时间间隔后才会再次请求

需要注意的是:主方法上面也要开起缓存才生效

package com.xiao;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.cache.annotation.EnableCaching;
import org.springframework.cloud.client.discovery.EnableDiscoveryClient;
import org.springframework.cloud.netflix.feign.EnableFeignClients;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@EnableDiscoveryClient
@SpringBootApplication
@EnableFeignClients
@EnableTransactionManagement
@EnableCaching
public class SpringBootClient01Application {

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Android源码框架分析

听说你Binder机制学的不错,来面试下这几个问题(三)

很多文章将Binder框架定义了四个角色:Server,Client,ServiceManager、以及Binder驱动,但这容易将人引导到歧途:好像所有的Bi...

2022
来自专栏Netkiller

Spring boot with Apache Hive

本文节选自《Netkiller Database 手札》 5.26. Spring boot with Apache Hive 5.26.1. Maven ...

7975
来自专栏Android 开发学习

IntelliJ IDEA spring mvc +mybatis 环境搭建服务器(下)直接使用类来生成Mapper:

2016
来自专栏北京马哥教育

两大Linux发行版迎来大幅更新 Debian 9及Fedora 26 Beta终于发布

Debian 9终于发布 Debian 发行版宣布正式释出代号为 Stretch 的 Debian 9,该版本将提供五年的支持。Stretch 将专门献给于 2...

3244
来自专栏云计算与大数据

How to Monitor Zookeeper

As per previous articles, our general rule of thumb is “collect all possible/rea...

2084
来自专栏好好学java的技术栈

SpringMVC+RestFul详细示例实战教程一(实现跨域访问+postman测试)

注意:由于文章篇幅太长,超出了字数,这是文章的第一部分,明天分享文章的第二部分,请见谅!

5412
来自专栏大魏分享(微信公众号:david-share)

怎样一个金箍圈(Pipeline),让至尊宝(Openshift)完成了到孙悟空(DevOps)的蜕变

但说出这句话,和实现Devops全工具链落地之间的差距,与造出原子弹和E=MC2公式的差距,实不逞多让。

4644
来自专栏分布式系统进阶

Librdkafka的Transport层

rd_kafka_recv按kafka的协议来收包, 先收4字节,拿到payload长度, 再根据这个长度收够payload内容, 这样一个完整的respons...

1861
来自专栏运维

CentOS6.3 x86_64位安装xfce4桌面vncserver服务

CentOS6.3 x86_64位安装xfce4桌面vncserver服务 注意这里用的是最小化安装纯净系统212个包CentOS-6.3-x86_64-...

1391
来自专栏Strive

zabbix服务zabbix server is not running: the information...

搭建环境 | 系统:centos 7 | Java 1.8 | zabbix 3.4

1.5K4

扫码关注云+社区

领取腾讯云代金券