springboot(三) 用druid连接mybatis

超、凢脫俗 2022-07-13 06:23 173阅读 0赞

在pom.xml里添加jar包：

<dependency>  
<groupId>org.springframework.boot</groupId>  
<artifactId>spring-boot-starter-jdbc</artifactId>  
</dependency>  
  
<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>  
  
<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

package com.query.mybatis;
    
    import lombok.extern.slf4j.Slf4j;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.ComponentScan;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Primary;
    import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
    import org.springframework.jdbc.datasource.DataSourceTransactionManager;
    import org.springframework.transaction.annotation.EnableTransactionManagement;
    
    import javax.sql.DataSource;
    
    /**
     * Created by huguoju on 2017/1/4.
     */
    @Configuration
    @Slf4j
    @EnableTransactionManagement
    @ComponentScan
    @MapperScan("com.demo.mapper")
    public class MybatisConfig {
        @Value("${spring.datasource.type}")
        private Class<? extends DataSource> dataSourceType;
    
        @Bean(name="dataSource", destroyMethod = "close", initMethod="init")
        @ConfigurationProperties(prefix = "spring.datasource")
        public DataSource dataSource() {
            log.info("-------------------- writeDataSource init ---------------------");
            return DataSourceBuilder.create().type(dataSourceType).build();
        }
        @Bean
        public SqlSessionFactory sqlSessionFactory() throws Exception {
            SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
            sqlSessionFactoryBean.setDataSource(dataSource());
            sqlSessionFactoryBean.setTypeAliasesPackage("com.demo.model");
            PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
            sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath:com/demo/mapper/*.xml"));
            sqlSessionFactoryBean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
            return sqlSessionFactoryBean.getObject();
        }
        /**
         * 配置事务管理器
         */
        @Bean(name = "transactionManager")
        @Primary
        public DataSourceTransactionManager transactionManager(@Qualifier("dataSource") DataSource dataSource) throws Exception {
            return new DataSourceTransactionManager(dataSource);
        }
    }

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

package com.demo.mapper;
    
    
    import com.demo.model.User;
    import org.apache.ibatis.annotations.ResultType;
    import org.apache.ibatis.annotations.Select;
    import org.springframework.stereotype.Repository;
    
    
    /**
     * Created by huguoju on 2016/12/28.
     */
    @Repository("userMapper")
    public interface UserMapper {
        @Select("SELECT * from `user`  where user_code = #{userCode}")
        @ResultType(User.class)
        User selectByPrimaryKey(Integer userCode);
    }

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

否则mapper编译不到target里。

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

package com.demo.model;
    
    
    import lombok.Data;
    
    
    /**
     * Created by huguoju on 2016/12/28.
     */
    @Data
    public class User {
        private Integer userCode;
        private String mobileNumber;
    }

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

package com.demo.service.impl;
    
    
    import com.demo.mapper.UserMapper;
    import com.demo.model.User;
    import com.demo.service.UserService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    
    /**
     * Created by huguoju on 2016/12/28.
     */
    @Service("userService")
    public class UserServiceImpl implements UserService {
        @Autowired
        private UserMapper userMapper;
        @Override
        public User selectByPrimaryKey(Integer userCode) {
            return userMapper.selectByPrimaryKey(userCode);
        }
    }

可以查询到数据。

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

package com.demo.druid;
    
    
    import org.springframework.boot.bind.RelaxedPropertyResolver;
    import org.springframework.context.EnvironmentAware;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.core.env.Environment;
    import org.springframework.transaction.annotation.EnableTransactionManagement;
    
    
    /**
     * Created by huguoju on 2016/12/28.
     * Druid的DataResource配置类
     */
    @Configuration
    @EnableTransactionManagement
    public class DruidDataSourceConfig  implements EnvironmentAware {
        private RelaxedPropertyResolver propertyResolver;
    
    
        public void setEnvironment(Environment env) {
            this.propertyResolver = new RelaxedPropertyResolver(env, "spring.datasource.");
        }
    }

创建DruidStatFilter

package com.demo.druid;
    
    
    import com.alibaba.druid.support.http.WebStatFilter;
    
    
    import javax.servlet.annotation.WebFilter;
    import javax.servlet.annotation.WebInitParam;
    
    
    /**
     * Created by huguoju on 2016/12/28.
     * Druid拦截器，用于查看Druid监控
     */
    @WebFilter(filterName="druidWebStatFilter",urlPatterns="/*",
            initParams={
                    @WebInitParam(name="exclusions",value="*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*")// 忽略资源
            })
    public class DruidStatFilter extends WebStatFilter {
    }

创建DruidStatViewServlet

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

在DemoApplication上面添加@ServletComponentScan

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

账户和密码为上面的