springboot(三) 用druid连接mybatis

在pom.xml里添加jar包：

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- druid阿里巴巴数据库连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.26</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.2.2</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.2.8</version>
</dependency>
<!-- MySql数据库驱动 -->
<dependency>
<groupId> mysql</groupId>
<artifactId> mysql-connector-java</artifactId>
<version> 5.0.5</version>
</dependency>

1、在application.properties里添加

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
2、在resources里创建mybatis.properties
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/db
spring.datasource.username=root
spring.datasource.password=123456


# 初始化大小，最小，最大
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=20
# 配置获取连接等待超时的时间
spring.datasource.maxWait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=SELECT 1 FROM DUAL
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
# 打开PSCache，并且指定每个连接上PSCache的大小
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20


# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.filters=stat,wall,logback
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

3、在com.demo下创建文件夹mybatis，创建文件MybatisConfig

[java] view plain copy

1. package com.query.mybatis;  
2.   
3. import lombok.extern.slf4j.Slf4j;  
4. import org.apache.ibatis.session.SqlSessionFactory;  
5. import org.mybatis.spring.SqlSessionFactoryBean;  
6. import org.mybatis.spring.annotation.MapperScan;  
7. import org.springframework.beans.factory.annotation.Qualifier;  
8. import org.springframework.beans.factory.annotation.Value;  
9. import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;  
10. import org.springframework.boot.context.properties.ConfigurationProperties;  
11. import org.springframework.context.annotation.Bean;  
12. import org.springframework.context.annotation.ComponentScan;  
13. import org.springframework.context.annotation.Configuration;  
14. import org.springframework.context.annotation.Primary;  
15. import org.springframework.core.io.support.PathMatchingResourcePatternResolver;  
16. import org.springframework.jdbc.datasource.DataSourceTransactionManager;  
17. import org.springframework.transaction.annotation.EnableTransactionManagement;  
18.   
19. import javax.sql.DataSource;  
20.   
21. /** 
22.  * Created by huguoju on 2017/1/4. 
23.  */  
24. @Configuration  
25. @Slf4j  
26. @EnableTransactionManagement  
27. @ComponentScan  
28. @MapperScan("com.demo.mapper")  
29. public class MybatisConfig {  
30.     @Value("${spring.datasource.type}")  
31.     private Class<? extends DataSource> dataSourceType;  
32.   
33.     @Bean(name="dataSource", destroyMethod = "close", initMethod="init")  
34.     @ConfigurationProperties(prefix = "spring.datasource")  
35.     public DataSource dataSource() {  
36.         log.info("-------------------- writeDataSource init ---------------------");  
37.         return DataSourceBuilder.create().type(dataSourceType).build();  
38.     }  
39.     @Bean  
40.     public SqlSessionFactory sqlSessionFactory() throws Exception {  
41.         SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();  
42.         sqlSessionFactoryBean.setDataSource(dataSource());  
43.         sqlSessionFactoryBean.setTypeAliasesPackage("com.demo.model");  
44.         PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();  
45.         sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath:com/demo/mapper/*.xml"));  
46.         sqlSessionFactoryBean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);  
47.         return sqlSessionFactoryBean.getObject();  
48.     }  
49.     /**  
50.      * 配置事务管理器  
51.      */  
52.     @Bean(name = "transactionManager")  
53.     @Primary  
54.     public DataSourceTransactionManager transactionManager(@Qualifier("dataSource") DataSource dataSource) throws Exception {  
55.         return new DataSourceTransactionManager(dataSource);  
56.     }  
57. }  

[java] view plain copy

1.   

以上就完成了配置，测试，在com/demo下创建mapper文件夹，创建UserMapper

[java] view plain copy

1. package com.demo.mapper;  
2.   
3.   
4. import com.demo.model.User;  
5. import org.apache.ibatis.annotations.ResultType;  
6. import org.apache.ibatis.annotations.Select;  
7. import org.springframework.stereotype.Repository;  
8.   
9.   
10. /** 
11.  * Created by huguoju on 2016/12/28. 
12.  */  
13. @Repository("userMapper")  
14. public interface UserMapper {  
15.     @Select("SELECT * from `user`  where user_code = #{userCode}")  
16.     @ResultType(User.class)  
17.     User selectByPrimaryKey(Integer userCode);  
18. }  

如果使用mapper.xml，要在pom.xml里添加

[java] view plain copy

1. <build>  
2.         <resources>  
3.             <resource>  
4.                 <directory>src/main/java</directory>  
5.                 <includes>  
6.                     <include>**/*.xml</include>  
7.                 </includes>  
8.             </resource>  
9.         </resources>  
10.     </build>  

否则mapper编译不到target里。

在com/demo下创建model文件夹，创建User

[java] view plain copy

1. package com.demo.model;  
2.   
3.   
4. import lombok.Data;  
5.   
6.   
7. /** 
8.  * Created by huguoju on 2016/12/28. 
9.  */  
10. @Data  
11. public class User {  
12.     private Integer userCode;  
13.     private String mobileNumber;  
14. }  

在com/demo下创建service文件夹，创建service文件，接口类忽略了，只复制实现类

[java] view plain copy

1. package com.demo.service.impl;  
2.   
3.   
4. import com.demo.mapper.UserMapper;  
5. import com.demo.model.User;  
6. import com.demo.service.UserService;  
7. import org.springframework.beans.factory.annotation.Autowired;  
8. import org.springframework.stereotype.Service;  
9.   
10.   
11. /** 
12.  * Created by huguoju on 2016/12/28. 
13.  */  
14. @Service("userService")  
15. public class UserServiceImpl implements UserService {  
16.     @Autowired  
17.     private UserMapper userMapper;  
18.     @Override  
19.     public User selectByPrimaryKey(Integer userCode) {  
20.         return userMapper.selectByPrimaryKey(userCode);  
21.     }  
22. }  

可以查询到数据。

4、用druid监控数据库，在com/demo下创建文件夹druid，创建文件DruidDataSourceConfig

[java] view plain copy

1. package com.demo.druid;  
2.   
3.   
4. import org.springframework.boot.bind.RelaxedPropertyResolver;  
5. import org.springframework.context.EnvironmentAware;  
6. import org.springframework.context.annotation.Configuration;  
7. import org.springframework.core.env.Environment;  
8. import org.springframework.transaction.annotation.EnableTransactionManagement;  
9.   
10.   
11. /** 
12.  * Created by huguoju on 2016/12/28. 
13.  * Druid的DataResource配置类 
14.  */  
15. @Configuration  
16. @EnableTransactionManagement  
17. public class DruidDataSourceConfig  implements EnvironmentAware {  
18.     private RelaxedPropertyResolver propertyResolver;  
19.   
20.   
21.     public void setEnvironment(Environment env) {  
22.         this.propertyResolver = new RelaxedPropertyResolver(env, "spring.datasource.");  
23.     }  
24. }  

创建DruidStatFilter

[java] view plain copy

1. package com.demo.druid;  
2.   
3.   
4. import com.alibaba.druid.support.http.WebStatFilter;  
5.   
6.   
7. import javax.servlet.annotation.WebFilter;  
8. import javax.servlet.annotation.WebInitParam;  
9.   
10.   
11. /** 
12.  * Created by huguoju on 2016/12/28. 
13.  * Druid拦截器，用于查看Druid监控 
14.  */  
15. @WebFilter(filterName="druidWebStatFilter",urlPatterns="/*",  
16.         initParams={  
17.                 @WebInitParam(name="exclusions",value="*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*")// 忽略资源  
18.         })  
19. public class DruidStatFilter extends WebStatFilter {  
20. }  

创建DruidStatViewServlet

[java] view plain copy

1. package com.demo.druid;  
2.   
3.   
4. import com.alibaba.druid.support.http.StatViewServlet;  
5.   
6.   
7. import javax.servlet.annotation.WebInitParam;  
8. import javax.servlet.annotation.WebServlet;  
9.   
10.   
11. /** 
12.  * Created by huguoju on 2016/12/28. 
13.  * Druid的Servlet 
14.  */  
15. @SuppressWarnings("serial")  
16. @WebServlet(urlPatterns = "/druid/*",  
17.         initParams={  
18.                 @WebInitParam(name="allow",value="127.0.0.1,192.168.1.126"),// IP白名单 (没有配置或者为空，则允许所有访问)  
19.                 @WebInitParam(name="deny",value="192.168.1.111"),// IP黑名单 (存在共同时，deny优先于allow)  
20.                 @WebInitParam(name="loginUsername",value="root"),// 用户名  
21.                 @WebInitParam(name="loginPassword",value="123456"),// 密码  
22.                 @WebInitParam(name="resetEnable",value="false")// 禁用HTML页面上的“Reset All”功能  
23.         })  
24. public class DruidStatViewServlet extends StatViewServlet {  
25. }  

在DemoApplication上面添加@ServletComponentScan

创建好之后，我们访问 http://localhost/druid/index.html,会自动跳到http://localhost/druid/login.html登录页面

账户和密码为上面的