springboot--整合Druid数据源,MyBatis和log4j日志输出(与JdbcTemplate 对比)

步骤：导入依赖–>编写配置文件–>与spring boot整合(编写配置文件将DruidDataSource添加到容器由spring接管)

环境依赖

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>       
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.23</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>

配置文件:
数据源的更换只需在配置文件中数据源下修改type属性
MyBatis的整合只需在配置文件中进行相关配置即可

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mybatis?useUnicode=true&useSSL=true&characterEncoding=utf-8&serverTimezone=UTC
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver
    #更换数据源，修改type即可
    type: com.alibaba.druid.pool.DruidDataSource
    #   数据源其他配置
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    #   配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    filters: stat,wall,log4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
    
#整合mybatis
mybatis:
  type-aliases-package: com.druid.pojo
  mapper-locations: classpath:mybatis/mapper/*.xml

配置类

@Configuration
public class DruidConfig {
    @Bean
    //将配置文件中的属性映射到DruidDataSource类属性中，自动复制
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource druidDataSource(){
        return new DruidDataSource();
    }

//Servlet和Filter相当于web.xml配置内容。springBoot内置了服务器
    //注册后台监控
    @Bean
    public ServletRegistrationBean statViewServlet(){
        ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
        //后台需要有人登录，账号密码设置
        Map<String,String> initParams = new HashMap<String, String>();
        //账号密码设置的KEY是固定的loginUsername loginPassword
        initParams.put("loginUsername","123456");
        initParams.put("loginPassword","123456");
        //允许谁可以访问，空允许所有
        initParams.put("allow","");
        //禁止谁可以访问
//        initParams.put("wang","192.168.0.110");
        bean.setInitParameters(initParams);//设置初始化参数

        return bean;
    }

    //注册过滤器
    @Bean
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean<Filter> bean = new FilterRegistrationBean<>();
        //设置过滤器
        bean.setFilter(new WebStatFilter());
        Map<String,String> initParams = new HashMap<String, String>();
        //哪些内容不进行统计
        initParams.put("exclusions","*.js,*.css,/druid/*");

        bean.setInitParameters(initParams);
        return bean;
    }

}

完成以上配置就可以在dao接口使用@Mapper或者在主配置类添加要扫描的包

//要么在这写@Mapper要么在启动类配置扫描包@MapperScan("com.harbin.mapper")
@Mapper
@Repository
public interface UserMapper {

    List<User> queryUserList();

    User queryUserById(int id);

    int addUser(User user);

    int delUser(int id);

    int updateUser(User user);
}

或指定包扫描：

@SpringBootApplication
@MapperScan("com.harbin.mapper") //要么在启动类配置扫描包@MapperScan("com.druid.mapper")要么在接口写@Mapper
public class SpringbootDataApplication {

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

}

如测试需要使用log4j打印日志，导入依赖并编写相应的配置文件：

log4j.rootLogger=debug, stdout, R

log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout

log4j.appender.stdout.layout.ConversionPattern=%5p - %m%n

log4j.appender.R=org.apache.log4j.RollingFileAppender
log4j.appender.R.File=firestorm.log

log4j.appender.R.MaxFileSize=100KB
log4j.appender.R.MaxBackupIndex=1

log4j.appender.R.layout=org.apache.log4j.PatternLayout
log4j.appender.R.layout.ConversionPattern=%p %t %c - %m%n

log4j.logger.自己的包名=DEBUG

以下时jdbc应用，可供对比体验与使用数据源之间的差别

@RestController
@RequestMapping("/jdbc")
public class JDBCController {
    @Autowired
    JdbcTemplate jdbcTemplate;

    @GetMapping("/addUser")
    public String addUser(){
        String sql = "insert into user(id, name, pwd) VALUES (?,?,?)";

        int add = jdbcTemplate.update(sql,21,"testJDBC","uptoyourself");
        return "addUser-Done"+add;
    }

    @GetMapping("/delUser/{id}")
    public String delUser(@PathVariable("id") int id){
        String sql = "delete from user where id=?";

        int del = jdbcTemplate.update(sql,id);
        return "delUser-Done"+del;
    }

    @GetMapping("/updateUser/{id}")
    public String updateUser(@PathVariable("id") int id){
        String sql = "update user set name=?,pwd=? where id=?";
        Object[] obj = new Object[3];
        obj[0]="testJDBC";
        obj[1]="uptoyourself";
        obj[2]=id;

        int update = jdbcTemplate.update(sql,obj);
        return "updateUser-Done"+update;
    }

    @GetMapping("/queryuser")
    public List<Map<String,Object>> selectUser(){
        String sql = "select id,name,pwd from user";
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
        return mapList;
    }

}