1. Integrate jdbc

Compile yaml configuration file to connect to database;

spring:
  datasource:
    username: root
    password: password
    url:jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.cj.jdbc.Driver

The database can be connected.

JdbcTemplate mainly provides the following methods:

• Execute method: it can be used to execute any SQL statement, generally used to execute DDL statements;
• Update method and batchUpdate method: the update method is used to execute new, modify, delete and other statements; The batchUpdate method is used to execute batch related statements;
• Query method and queryForXXX method: used to execute query related statements;
• call method: used to execute stored procedures, functions and related statements.

Write a Controller, inject JDBC template, and write test methods for access testing;

package com.ty;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
import java.util.Map;

@RestController
public class JdbcController {
    @Autowired
    JdbcTemplate jdbcTemplate;
    @RequestMapping("/jdbc")
    public List<Map<String,Object>> getMaps(){
        String sql="select * from user";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        return maps;
    }
}

Integrate Druid

For Spring Boot 2.0 and above, Hikari data source is used by default. It can be said that Hikari and Driud are the best data sources on the current Java Web. Let's focus on how Spring Boot integrates Druid data source and how to realize database monitoring.

① Import druid dependencies
② Switch data sources; As mentioned earlier, com.com is used by default for Spring Boot 2.0 and above zaxxer. hikari. Hikaridatasource data source, but it can be accessed through spring datasource. Type specifies the data source.

spring:
  datasource:
    username: root
    password: password
    url: jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource

③ After data source switching, inject DataSource into the test class, obtain it, and output it to see whether the switching is successful;

④ Switching succeeded! Now that the switch is successful, you can set the initialization size, maximum number of connections, waiting time, minimum number of connections and other settings of the data source connection; You can view the source code

    #Spring Boot does not inject these attribute values by default and needs to bind itself
    #druid data source proprietary configuration
    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

	 #Configure filters for monitoring statistics interception, stat: monitoring statistics, log4j: logging, wall: defending sql injection
    #If allowed, an error occurs in Java lang.ClassNotFoundException: org. apache. log4j. Priority
    #Then import the log4j dependency. Maven address: https://mvnrepository.com/artifact/log4j/log4j
    filters: stat,wall,log4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

⑤ Introduce log4j dependency

        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>

⑥ Now programmers need to bind the parameters in the global configuration file for DruidDataSource and add them to the container instead of using the automatic generation of Spring Boot; We need to add the DruidDataSource component to the container and bind the properties;

@Configuration
public class DruidConfig {

    /*
       Add the custom Druid data source to the container and no longer let Spring Boot create it automatically
       Bind the Druid data source attribute in the global configuration file to com alibaba. druid. pool. Druiddatasource to make them effective
       @ConfigurationProperties(prefix = "spring.datasource"): The function is to add the global configuration file
       The prefix is spring The attribute value of datasource is injected into com alibaba. druid. pool. Druiddatasource is in a parameter with the same name
     */
    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druidDataSource() {
        return new DruidDataSource();
    }
}

Configure Druid data source monitoring
Druid data source has the function of monitoring and provides a web interface for users to view. Similarly, when installing a router, people also provide a default web page.

Therefore, the first step is to set Druid's background management page, such as login account, password, etc; Configure background management;

package com.ty.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.servlet.Filter;
import javax.sql.DataSource;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;

@Configuration
public class DruidConfiguration {
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource druidDataSource(){
        return new DruidDataSource();
    }

    @Bean
    public ServletRegistrationBean servletRegistrationBean(){
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");

        Map initParm =new HashMap();
        initParm.put("loginUsername","root");
        initParm.put("loginPassword","123456");

        initParm.put("allow","");
        bean.setInitParameters(initParm);

        return bean;

    }
    @Bean
    public FilterRegistrationBean filterRegistrationBean(){
        FilterRegistrationBean bean=new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());

        Map<String,String>  initParam=  new HashMap<>();
        initParam.put("exclusions","*.js,*.css,/druid/*,/jdbc/*");
        bean.setInitParameters(initParam);

        bean.setUrlPatterns(Arrays.asList("/*"));

        return bean;
    }
}

Integrate Mybatis

① Dependencies required to import MyBatis

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.1</version>
</dependency>

② Configure database connection information (unchanged)

spring:
  datasource:
    username: root
    password: password
    url: jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.cj.jdbc.Driver
	 type: com.alibaba.druid.pool.DruidDataSource



    #Spring Boot does not inject these attribute values by default and needs to bind itself
    #druid data source proprietary configuration
    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

    #Configure filters for monitoring statistics interception, stat: monitoring statistics, log4j: logging, wall: defending sql injection
    #If allowed, an error occurs in Java lang.ClassNotFoundException: org. apache. log4j. Priority
    #Then import the log4j dependency. Maven address: https://mvnrepository.com/artifact/log4j/log4j
    filters: stat,wall,log4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
mybatis:
  mapper-locations: clsspath:com/ty/mapper/*Mapper.xml
    

Reconfigure DruidDataSource
③ Test whether the database connection is successful

④ Create an entity class and import Lombok!

For @ Mapper annotation, we do not add this annotation in the pure mybatis project because there are no other layers to reference, but we obtain dao instances by specifying xml files in the form of reflection, and directly operate sqlsession sqlsession = mybatisutils getSqlSession(); UserMapper Mapper = sqlSession. getMapper(UserMapper.class); List userList = Mapper. getUserList();

For the SpringBoot project, we obtain the bean instance in the implementation class of the service layer interface, so @ Mapper injects it into the Spring container

⑤ Corresponding usermapper xml

⑥ maven configuring resource filtering

<resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>

⑦ Write UserController test