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

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

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

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

1.pom引入

<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);
}
}