Springboot operation database

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

2,JdbcTemplate

                 1. With the data source (com.zaxxer.hikari.HikariDataSource), you can get the data. With the connection, you can use the native JDBC statement to operate the database;
                 2. Even without using a third-party database operation framework, such as MyBatis, Spring itself makes a lightweight encapsulation of the native JDBC, that is, JdbcTemplate.
                 3. All CRUD methods for database operations are in the JdbcTemplate.
                 4. Spring Boot not only provides the default data source, but also the configured JdbcTemplate is placed in the container by default. Programmers only need to inject it themselves

5. The automatic configuration of JdbcTemplate depends on the JdbcTemplateConfiguration class under the org.springframework.boot.autoconfigure.jdbc package

         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

3. Testing

package com.rk.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//Returns a string without using the view parser
public class JDBCController {

    @Autowired
    JdbcTemplate jdbcTemplate;

    //Query all data in the user table and return to the front page
    @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;
    }

    //Add a user
    @GetMapping("/addUser")
    public String addUser(){
        String sql="insert into user(id,name,pwd) values (4,'Wang Min','123456')";
        jdbcTemplate.update(sql);
        return "add-ok";

    }
    
    //Modify information
    @GetMapping("/updateUser/{id}")
    public String updateUser(@PathVariable("id") int id){
        String sql="update user set  name=?,pwd=? where id="+id;
        //Packaging parameters
        Object[] objects=new Object[2];
        objects[0]="Rowling";
        objects[1]="123456";
        jdbcTemplate.update(sql,objects);
        return "update-ok";
    }

    //delete user
    @GetMapping("/deleteUser/{id}")
    public String deleteUser(@PathVariable("id") int id){
        String sql="delete from user where id=?";
        jdbcTemplate.update(sql,id);
        return "delete-ok";
    }
}

2, Integrated Druid

1. Introduction to Druid

1. A large part of Java programs need to operate the database. In order to improve the performance, they have to use the database connection pool when operating the database. Druid is a database connection pool implementation on Alibaba's open source platform. It combines the advantages of C3P0, DBCP and other DB pools, and adds log monitoring.
2. Druid can well monitor DB pool connections and SQL execution. It is naturally a DB connection pool for monitoring.
3. Druid has deployed more than 600 applications in Alibaba, which has been severely tested by large-scale deployment in the production environment for more than a year.
4. 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.

2. Add dependency

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.21</version>
</dependency>

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

3. Binding and configuring data sources

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


    #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 the error is allowed, java.lang.ClassNotFoundException:org.apache.log4j.Priority will be reported
    #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

4. Customize the DruidDataSource component and configure Druid data source monitoring

package com.rk.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 java.util.HashMap;
import java.util.Map;

@Configuration
public class DruidConfig {

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

            ///Configure the Servlet of Druid monitoring management background;
            //There is no web.xml file in the built-in Servlet container, so the Servlet registration method of Spring Boot is used
    @Bean
    public ServletRegistrationBean statViewServlet() {
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
         // These parameters can be found in com.alibaba.druid.support.http.StatViewServlet
         // Found in the parent class of com.alibaba.druid.support.http.ResourceServlet
        Map<String, String> initParams = new HashMap<>();
        initParams.put("loginUsername", "admin"); //Login account of background management interface
        initParams.put("loginPassword", "123456"); //Login password of background management interface
         //Who is allowed to access in the background
         //initParams.put("allow", "localhost"): indicates that only the local machine can access it
         //initParams.put("allow", ""): when it is empty or null, it means that all accesses are allowed
        initParams.put("allow", "");
         //deny: Druid, who is denied access in the background
         //initParams.put("rk", "192.168.1.20"); Indicates that this ip access is prohibited
         //Set initialization parameters
        bean.setInitParameters(initParams);
        return bean;
    }

}

Start: Enter druid/login.html page (druid is automatically generated)

After entering

5. Configure Druid web monitoring filter

    //Configure Druid monitoring function
    //WebStatFilter: used to configure management association monitoring statistics between Web and Druid data sources
    @Bean
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean bean=new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());
        //exclusions: sets which requests are filtered and excluded so that statistics are not performed
        Map<String, String> initParams = new HashMap<>();
        initParams.put("exclusions", "*.js,*.css,/druid/*,/jdbc/*");
        bean.setInitParameters(initParams);
        //"/ *" means to filter all requests
        bean.setUrlPatterns(Arrays.asList("/*"));
        return bean;
    }

3, Integrate Mybatis

1. Dependencies required to import MyBatis

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

         <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.21</version>
        </dependency>

        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </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>
        </dependency>
        <dependency>
            <groupId>org.elasticsearch</groupId>
            <artifactId>elasticsearch</artifactId>
            <version>2.4.0</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-web</artifactId>
            <version>5.3.12</version>
        </dependency>
    </dependencies>

2. Configure database information

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


    #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 the error is allowed, java.lang.ClassNotFoundException:org.apache.log4j.Priority will be reported
    #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

3. Create entity class

package com.rk.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;
}

4. Create UserMapper

package com.rk.mapper;
import com.rk.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
@Mapper//Indicates that this is a mapper class of mybatis
@Repository
public interface UserMapper {
    List<User> queryUserList();//Query all users
    void deleteUser(@Param("uid") int id);//Delete by id
    void updateUser(@Param("uid") int id);//Modify according to id
    void addUser(User user);//Add user
    User queryByid(@Param("uid") int id);//Query user by id
}

5. Create the corresponding UserMapper.xml under resources

<?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.rk.mapper.UserMapper">
    <select id="queryUserList" resultType="User">
        select * from user
    </select>

    <delete id="deleteUser" parameterType="int">
        delete from user where id=#{uid}
    </delete>

    <update id="updateUser" parameterType="int">
        update user
        set name='Rowling'
        where id=#{uid}
    </update>

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

    <select id="queryByid" resultType="User" parameterType="int">
        select * from user
        where id=#{uid}
    </select>

</mapper>

If you create mapper.xml directly under the pojo file directory, you need to filter the resource path in < build > of pom.xml

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

6. Bind the UserMapper.xml file path in the configuration file

#Specify the core configuration file and Mapper mapping file for myBatis
mybatis.mapper-locations=classpath:mapper/*.xml
# Note: the path of the corresponding entity class
mybatis.type-aliases-package=com.rk.pojo

7. Write test class UserController

package com.rk.controller;

import com.rk.mapper.UserMapper;
import com.rk.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
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;

@RestController
public class UserController {

    @Autowired
    private UserMapper userMapper;
    @GetMapping("/query")
    public List<User>  query(){
        List<User> users = userMapper.queryUserList();
        return users;
    }

    @GetMapping("/update/{id}")
    public void  updateUser(@PathVariable("id") int id){
        userMapper.updateUser(id);
    }

    @GetMapping("/delete/{id}")
    public void  deleteUser(@PathVariable("id") int id){
        userMapper.deleteUser(id);
    }

    @GetMapping("/add")
    public void  AddUser(){
        User user=new User();
        user.setId(4);
        user.setName("Luo min");
        user.setPwd("luolin");
        userMapper.addUser(user);
    }


    @GetMapping("/querybyid/{id}")
    public User queryUser(@PathVariable("id") int id){
        return userMapper.queryByid(id);
    }

}

8. Currently, Druid database connection pool is used, and DruidConfig is customized

package com.rk.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 java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
@Configuration//Represents a spring configuration class, equivalent to bean.xml
public class DruidConfig {

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

            ///Configure the Servlet of Druid monitoring management background;
            //There is no web.xml file in the built-in Servlet container, so the Servlet registration method of Spring Boot is used
    @Bean
    public ServletRegistrationBean statViewServlet() {
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
         // These parameters can be found in com.alibaba.druid.support.http.StatViewServlet
         // Found in the parent class of com.alibaba.druid.support.http.ResourceServlet
        Map<String, String> initParams = new HashMap<>();
        initParams.put("loginUsername", "admin"); //Login account of background management interface
        initParams.put("loginPassword", "123456"); //Login password of background management interface
         //Who is allowed to access in the background
         //initParams.put("allow", "localhost"): indicates that only the local machine can access it
         //initParams.put("allow", ""): when it is empty or null, it means that all accesses are allowed
        initParams.put("allow", "");
         //deny: Druid, who is denied access in the background
         //initParams.put("rk", "192.168.1.20"); Indicates that this ip access is prohibited
         //Set initialization parameters
        bean.setInitParameters(initParams);
        return bean;
    }


    //Configure Druid monitoring function
    //WebStatFilter: used to configure management association monitoring statistics between Web and Druid data sources
    @Bean
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean bean=new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());
        //exclusions: sets which requests are filtered and excluded so that statistics are not performed
        Map<String, String> initParams = new HashMap<>();
        initParams.put("exclusions", "*.js,*.css,/druid/*,/jdbc/*");
        bean.setInitParameters(initParams);
        //"/ *" means to filter all requests
        bean.setUrlPatterns(Arrays.asList("/*"));
        return bean;
    }
}

Tags: Database Mybatis Spring Boot Back-end

Posted on Wed, 27 Oct 2021 07:21:07 -0400 by medar

Programmer Help