Data

1. Configuration file

When creating a new project, select mysql and jdbc Driver to import dependencies

Configure the user name, password, url, and Driver in the configuration file

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

Note that since mysql used in SpringBoot is version 8.0 +, the url must contain time zone information

serverTimezone=Asia/Shanghai

2. Test use

package com.wang;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

@SpringBootTest
class Springboot04DataApplicationTests {

    //As long as the DataSource is automatically assembled, we can use it
    @Autowired
    DataSource dataSource;

    @Test
    void contextLoads() throws SQLException {
        //View the default data source: Hikari HikariDataSource
        System.out.println(dataSource.getClass());

        //Get database connection
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
        
        //Close database connection
        connection.close();
    }

}

be careful

• Bind DataSource using auto assembly
• Call jdbc with getConnection method
• The default data source is Hikari HikariDataSource

3. CRUD

package com.wang.controller;

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

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

@RestController
public class jdbcController {

    //XXXX template: springboot's configured template bean is ready to use and encapsulates CRUD
    //jdbc
    //redis
    @Autowired
    JdbcTemplate jdbcTemplate;

    //Query all information in the database
    //Without entity classes, how to get things in the database? = = > Map
    @GetMapping("/userList")
    public List<Map<String, Object>> userList() {
        String sql = "select * from user";
        List<Map<String, Object>> list_maps = jdbcTemplate.queryForList(sql);
        return list_maps;
    }

    @GetMapping("/addUser")
    public String addUser() {
        String sql = "insert into mybatis.user(id, name, pwd) values(5, 'Xiao Ming', '123456')";
        int update = jdbcTemplate.update(sql);
        if (update == 1) {
            return "addUser-OK";
        } else {
            return "addUser-Fail";
        }
    }

    //Splice SQL, pass parameters in RestFul style, and annotate with @ PathVariable
    @GetMapping("/updateUser/{id}")
    public String updateUser(@PathVariable("id") int id) {
        String sql = "update mybatis.user set name = ?, pwd = ? where id=" + id;

        //encapsulation
        Object[] objects = new Object[2];
        objects[0] = "Xiaoming 2";
        objects[1] = "1233211234567";

        //update in jdbcTemplate overloads prepareStatement, which can be directly transferred to the required object
        int update = jdbcTemplate.update(sql, objects);
        if (update == 1) {
            return "updateUser-OK";
        } else {
            return "updateUser-Fail";
        }
    }

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

        //update in jdbcTemplate overloads prepareStatement, which can be directly transferred to the required object
        //int is also an object. You can pass it in directly
        int update = jdbcTemplate.update(sql, id);
        if (update == 1) {
            return "deleteUser-OK";
        } else {
            return "deleteUser-Fail";
        }
    }

}

be careful

• XXXX template: springboot's configured template bean is ready to use and encapsulates CRUD
• Except for queries, update statements are used
• update in jdbcTemplate overloads prepareStatement, which can be directly transferred to the required object
• Query for query
• Without entity classes, how to get things in the database? = = > Map = = > the map is stored in the list, and the field name and value in the database are stored in the form of K-V in the map. The field name is of String type and the value is of Object type

4. Druid

1. Import dependency

<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.24</version>
</dependency>

2. Specify the data source and configure Druid

Specify the type in the configuration file and configure Druid

spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    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 by 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: defensive sql injection
    #If allowed, an error will be reported in Java lang.ClassNotFoundException: org. apache. log4j. Priority
    #Then import 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

3. Bind Druid to spring datasource

package com.wang.config;

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

import javax.sql.DataSource;
import java.util.HashMap;

@Configuration
public class DruidConfig {

    //Combine the custom Bean with spring Datasource binding
    @ConfigurationProperties(prefix = "spring.datasource")
    //Register DruidDataSource with Bean
    @Bean
    public DataSource druidDataSource() {
        return new DruidDataSource();
    }
}

be careful

• @ConfigurationProperties binding configuration, and prefix can be specified later

4. Enable background monitoring

package com.wang.config;

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

import javax.sql.DataSource;
import java.util.HashMap;

@Configuration
public class DruidConfig {

    //Combine the custom Bean with spring Datasource binding
    @ConfigurationProperties(prefix = "spring.datasource")
    //Register DruidDataSource with Bean
    @Bean
    public DataSource druidDataSource() {
        return new DruidDataSource();
    }

    //Background monitoring, you can access / druid to fix the written code
    //Because spring boot has a built-in servlet container, there is no web XML, alternative: ServletRegistrationBean
    @Bean
    public ServletRegistrationBean statViewServlet() {
        ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");

        //Someone needs to log in at the background and configure the account and password
        HashMap<String, String> initParameters = new HashMap<>();
        //Add configuration
        //The login key is a fixed loginUsername loginPassword
        initParameters.put("loginUsername", "admin");
        initParameters.put("loginPassword", "123456");

        //Who is allowed to access? If the value is blank, everyone can access
        initParameters.put("allow", "");

        //IP whitelist
        //initParameters.put("allow","192.168.1.12,127.0.0.1");
        //IP blacklist
        //initParameters.put("deny","192.168.4.23");
        //Can I reset the data
        initParameters.put("resetEnable","false");

        bean.setInitParameters(initParameters);   //Set initialization parameters

        return bean;
    }

}

The key is as follows

public static final String SESSION_USER_KEY    = "druid-user";
public static final String PARAM_NAME_USERNAME = "loginUsername";
public static final String PARAM_NAME_PASSWORD = "loginPassword";
public static final String PARAM_NAME_ALLOW    = "allow";
public static final String PARAM_NAME_DENY     = "deny";
public static final String PARAM_REMOTE_ADDR   = "remoteAddress";

To use log4j, configure log4j under resources properties

#Output the log information with the level of DEBUG to the two destinations of console and file. The definitions of console and file are in the following code
log4j.rootLogger=DEBUG,console,file

#Relevant settings of console output
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n

#Relevant settings for file output
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/wang.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n

#Log output level
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

be careful

• After configuring with methods in JavaConfig, be sure to register beans
• Through servletregistrationbean = new servletregistrationbean < > (New statviewservlet(), "/ Druid / *"); Configure stat and set path
• We need to register stat with web XML, because spring boot has built-in servlet container, so there is no web XML, alternative: ServletRegistrationBean
• Put it into the setInitParameters method in the form of key value pairs (map)
• The key is fixed. Don't Scribble

5. Set the Filter for background monitoring

//filter
//We need to register the filter. Similarly, use the filter registration bean to register
@Bean
public FilterRegistrationBean webStatFilter() {
    FilterRegistrationBean bean = new FilterRegistrationBean();

    bean.setFilter(new WebStatFilter());

    //What requests can be filtered
    HashMap<String, String> initParameters = new HashMap<>();

    //These things are not counted
    initParameters.put("exclusions", "*.js,*.css,/druid/*");

    bean.setInitParameters(initParameters);

    return bean;
}

The key is as follows

public final static String PARAM_NAME_PROFILE_ENABLE         = "profileEnable";
public final static String PARAM_NAME_SESSION_STAT_ENABLE    = "sessionStatEnable";
public final static String PARAM_NAME_SESSION_STAT_MAX_COUNT = "sessionStatMaxCount";
public static final String PARAM_NAME_EXCLUSIONS             = "exclusions";
public static final String PARAM_NAME_PRINCIPAL_SESSION_NAME = "principalSessionName";
public static final String PARAM_NAME_PRINCIPAL_COOKIE_NAME  = "principalCookieName";
public static final String PARAM_NAME_REAL_IP_HEADER         = "realIpHeader";

be careful

• Similar to stat, we need to register filter and use FilterRegistrationBean
• Don't forget to register Bean

5. MyBatis

1. Import dependency

mybatis-spring-boot-starter

<!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.3</version>
</dependency>

2. Write entity class

package com.wang.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    private int id;
    private String name;
    private String pwd;
}

3. Write Dao layer

package com.wang.mapper;

import com.wang.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

import java.util.List;

//This annotation indicates that this is a Mapper class of MyBatis
//You can also add @ MapperScan("") scanning package to the startup class
@Mapper
//This annotation indicates that it is Dao layer
@Repository
public interface UserMapper {

    List<User> queryUserList();

    User queryUserById(int id);

    int addUser(User user);

    int updateUser(User user);

    int deleteUser(int id);
}

be careful

• @Mapper
  • This annotation indicates that this is a Mapper class of MyBatis
  • You can also add @ MapperScan("") scanning package to the startup class
• @Repository
  • This annotation indicates that it is Dao layer
• The above two notes are indispensable

4. Integrate Mybatis

Add to profile

# Integrate Mybatis
mybatis:
  type-aliases-package: com.wang.pojo
  mapper-locations: classpath:MyBatis/mapper/*.xml

Set alias and make xxxmapper XML path

be careful

• In the path setting, there is no path after classpath:/

5. Write xxxmapper xml

Create a new folder under the path corresponding to the configuration file (resources path)

UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wang.mapper.UserMapper">

    <select id="queryUserList" resultType="User">
        select *
        from user;
    </select>

    <select id="queryUserById" resultType="User">
        select *
        from user
        where id = #{id};
    </select>

    <insert id="addUser" parameterType="User">
        insert into user (id, name, pwd)
        VALUES (#{id}, #{name}, #{pwd});
    </insert>

    <update id="updateUser" parameterType="User">
        update user
        set name = #{name},
            pwd  = #{pwd}
        where id = #{id};
    </update>

    <delete id="deleteUser" parameterType="_int">
        delete
        from user
        where id = #{id};
    </delete>


</mapper>

6. Write controller

package com.wang.controller;

import com.wang.mapper.UserMapper;
import com.wang.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class UserController {

    @Autowired
    private UserMapper userMapper;

    @GetMapping("/userList")
    public List<User> queryUserList() {
        List<User> userList = userMapper.queryUserList();
        return userList;
    }

    @GetMapping("/addUser")
    public String addUser() {
        int i = userMapper.addUser(new User(6, "Xiao Ming", "123456"));
        return i == 1 ? "ok" : "fail";
    }

    @GetMapping("/updateUser")
    public String updateUser() {
        int i = userMapper.updateUser(new User(6, "Xiaoming 2", "654321"));
        return i == 1 ? "ok" : "fail";
    }

    @GetMapping("/deleteUser")
    public String deleteUser() {
        int i = userMapper.deleteUser(6);
        return i == 1 ? "ok" : "fail";
    }
}

be careful

• In SpringBoot, automatic assembly @ AutoWired should be used for all introductions. Dao's interface should be introduced here. In the actual project, Service layer classes should be introduced (lazy and not written here)