preface

Happy Army Day everyone ~ personal blog: aaatao66.github. IO /

The Denver nuggets: juejin. Cn/user / 536217…

Update personal blog first, nuggets second.

Integrating Mybtis with Spring Boot is very simple, and with this article you can get started quickly without any pressure, but before I begin I want to mention my version information:

• Maven 3.2.5
• JDK 1.8
• Spring Boot 2.1.6

Create a project

The automatic configuration of IDEA is still used, but here, we need to check the following dependencies:

If you check MyBatis, you will find that your POM file has:

		<dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.0</version>
        </dependency>
Copy the code

This article rely on

As long as it is with * – spring – the boot – the starter, is spring boot the official recommendation, the mybatis is here, let’s look at some of the mybatis package under all package:

We noticed that it introduced mybatis — spring packages and so on, and mybatis — spring-boot-autoconfigure, which means automatic configuration, which is a feature of Spring Boot

Configure the Druid data source

Spring Boot2.x’s data source is Hikari’s and 1.x is Tomcat’s, so we need to configure our own data source

My previous article introduces the Druid: aaatao66. Making. IO / 2019/07/30 /…

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.16</version>
        </dependency>
Copy the code

It would be nice to introduce this dependency

Then in the application.yml configuration file:

spring:
  datasource:
    password: root
    username: root
    url: jdbc:mysql://localhost:3306/mybatis? serverTimezone=UTC
    driver-class-name: com.mysql.cj.jdbc.Driver
    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
    # Configure the filters for monitoring statistics interception. After removing the filters, the MONITORING interface SQL cannot be counted. 'wall' is used for the firewall
    filters: stat,wall,log4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true; druid.stat.slowSqlMillis=500
Copy the code

As I mentioned in my last article, the following properties are not valid and require special configuration if they are to be valid, and I have configured the Druid monitoring as described in the previous chapter:

package com.carson.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.sql.DataSource;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;

@Configuration
public class DruidConfig {

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

    // Configure Druid monitoring
    //1) Configure a Servlet to manage the backend
    @Bean
    public ServletRegistrationBean statViewServlet(a) {
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        Map<String, String> initParams = new HashMap<>();
        // Here is the druid monitor account password, which can be set freely
        initParams.put("loginUsername"."admin");
        initParams.put("loginPassword"."123456");
        initParams.put("allow"."");
        initParams.put("deny"."192.123.11.11");
        // Set the initialization parameters
        bean.setInitParameters(initParams);
        return bean;
    }

    // 2) Configure a filter for monitoring
    @Bean
    public FilterRegistrationBean webStatFilter(a) {
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());

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

        bean.setInitParameters(initParams);

        bean.setUrlPatterns(Arrays.asList("/ *"));
        returnbean; }}Copy the code

Start the main class to see if it can access the Druid monitor, and add log4j dependencies if you report an error:

        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
<! -- I don't know why, but if you don't add log4j, you will get an error -->
Copy the code

View the effect:

Use SpringBoot to build tables

Add two SQL files to create tables under Resources/SQL:

• department.sql:

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
  `id`             INT(11) NOT NULL AUTO_INCREMENT,
  `departmentName` VARCHAR(255)     DEFAULT NULL,
  PRIMARY KEY (`id`))ENGINE = InnoDB
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8;
Copy the code

• employee.sql:

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
  `id`       INT(11) NOT NULL AUTO_INCREMENT,
  `lastName` VARCHAR(255)     DEFAULT NULL.`email`    VARCHAR(255)     DEFAULT NULL.`gender`   INT(2)           DEFAULT NULL.`d_id`     INT(11)          DEFAULT NULL,
  PRIMARY KEY (`id`))ENGINE = InnoDB
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8;
Copy the code

And write to application.yml:

    schema:
      - classpath:sql/department.sql
      - classpath:sql/employee.sql
    initialization-mode: always  
Copy the code

Schema ` is with password/username, etc. At the same level, if you don’t understand yml file with the properties of the difference, then you can see my previous article: aaatao66. Making. IO / 2019/06/30 /…

By the way, if you are springBoot 2.x or older, you may need to add the initialization-mode attribute.

Run the main class to see if the table was created successfully. My database already generates these two tables, so I won’t take screenshots here

If your program starts with an error after setting the SQL file:

• Restart idea (restart method good ah!)

• Check whether the SCHEMA is correctly configured with the name of the SQL file

• schema:

  – (space) classpath: SQL/XXX. SQL

  Pay attention to the format

Corresponds to the database entity class

• Employee.java

package com.carson.domain;

public class Employee {

    private Integer id;
    private String lastName;
    private Integer gender;
    private String email;
    private Integer dId;

    public Integer getId(a) {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getLastName(a) {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public Integer getGender(a) {
        return gender;
    }

    public void setGender(Integer gender) {
        this.gender = gender;
    }

    public String getEmail(a) {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Integer getdId(a) {
        return dId;
    }

    public void setdId(Integer dId) {
        this.dId = dId; }}Copy the code

• Department.java

package com.carson.domain;

public class Department {
    private Integer id;
    private String departmentName;

    public Integer getId(a) {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getDepartmentName(a) {
        return departmentName;
    }

    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName; }}Copy the code

Remember to put my config file just nowschemaProperties are commented out, and we don’t want to create the table again the next time we run it

Database interaction

– annotations version

• Create a Mapper and write SQL statements directly onto it

package com.carson.mapper;

import com.carson.domain.Department;
import org.apache.ibatis.annotations.*;

// Specify that this is a mapper
@Mapper
public interface DepartmentMapper {

    @Select("select * from department where id=#{id}")
    public Department getDepById(Integer id);

    @Delete("delete from department where id=#{id}")
    public int deleteDepById(Integer id);

    @Insert("insert into department(departmentName) values(#{departmentName})")
    public int insertDept(Department department);

    @Update("update department set departmentName=#{departmentName} where id=#{id}")
    public int updateDept(Department department);
}
Copy the code

Then write a Controller:

package com.carson.controller;

import com.carson.domain.Department;
import com.carson.mapper.DepartmentMapper;
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;

@RestController // represents the controller that returns JSON data
public class DeptController {

    @Autowired
    DepartmentMapper departmentMapper;

    @GetMapping("/dept/{id}")
    public Department getDept(@PathVariable("id") Integer id) {
        return departmentMapper.getDepById(id);
    }

    @GetMapping
    public Department inserDept(Department department) {
        departmentMapper.insertDept(department);
        returndepartment; }}Copy the code

• Placeholder parameters in urls can be bound to input parameters of controller handling methods using @pathvariable: {XXX} placeholders in URLS can be bound to input parameters of action methods using @pathvariable (” XXX “).

To start the main class, type this: localhost:8080/dept? DepartmentName =AA, which adds a data entry to the database:

There is a problem with not getting id when inserting data:

So we’ll use an @options annotation:

    @Options(useGeneratedKeys = true,keyProperty = "id")
    @Insert("insert into department(departmentName) values(#{departmentName})")
    public int insertDept(Department department);
Copy the code

Add it to the @value annotation of the insert in mapper

• useGeneratedKeys: Uses the generated primary key
• keyPropertyWhich attribute in the Department is our primary keyid

Try inserting a piece of data:

I wrote about this earlier in the article, but I actually forgot to comment out the schema, causing the database to be recreated every time I run it

One more question

We change the database field name to Department_name and the entity class to departmentName;

And the SQL statement is also corrected

    @Options(useGeneratedKeys = true,keyProperty = "id")
    @Insert("insert into department(department_name) values(#{departmentName})")
    public int insertDept(Department department);

    @Update("update department set department_name=#{departmentName} where id=#{id}")
    public int updateDept(Department department);
Copy the code

Then in the query operation:

We find that we can’t get departmentName. We used to use the configuration file to deal with this situation in Spring before, but now we don’t have the XML file, what should we do?

Nothing is difficult in the world

Create a custom configuration class:

package com.carson.config;

import org.mybatis.spring.boot.autoconfigure.ConfigurationCustomizer;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MyBatisConfig {

    @Bean
    public ConfigurationCustomizer configurationCustomizer(a) {
        return new ConfigurationCustomizer() {
            @Override
            public void customize(org.apache.ibatis.session.Configuration configuration) 			 {
                configuration.setMapUnderscoreToCamelCase(true); }}; }}Copy the code

Note here setMapUnderscoreToCamelCase below, it means:

Translation solution well, my English is too poor, visit http://localhost:8080/dept/1 query operation again, I found that is not null:

{"id":1."departmentName":"jackMa"}
Copy the code

Manager Ma showed it correctly!

MapperScanannotations

A scanner that scans mapper interfaces

I tagged it to the launch class (you can tag it anywhere):

Specify a package that scans all mapper interfaces in the package to prevent you from having too many Mapper files and forgetting to annotate @mapper to improve accuracy

– Configuration file version

The annotated version may seem convenient, but if you are dealing with complex SQL, such as dynamic SQL, you still need XML configuration files.

Create a Mapper interface for Employee:

package com.carson.mapper;

import com.carson.domain.Employee;

public interface EmployeeMapper {

    public Employee getEmpById(Integer id);

    public void insertEmp(Employee employee);

}

Copy the code

Create a mybatis global config file under Resources/Mybatis:

<?xml version="1.0" encoding="UTF-8" ? >
        
      
<configuration>

</configuration>
Copy the code

Create employeemapper.xml mapping file in resources/mybatis/mapper:

<?xml version="1.0" encoding="UTF-8" ? >

      

<! -- Bind interface and write two SQL statements -->
<mapper namespace="com.carson.mapper.EmployeeMapper">

    <select id="getEmpById" resultType="com.carson.domain.Employee">
    select * from employee where id = #{id}
  </select>

    <insert id="insertEmp">
        insert into employee(lastName,email,gender,d_id) values (#{lastName},	  		#{email},#{gender},#{d_id})
    </insert>
</mapper>
Copy the code

Then add a configuration to the application.yml configuration file:

The # mybatis attribute is identical to the Spring attribute
mybatis:
Specify the global configuration file
  config-location: classpath:mybatis/mybatis-config.xml
# specify mapper mapping file, * stands for all
  mapper-locations: classpath:mybatis/mapper/*.xml
Copy the code

Let’s add a section of Controller to the DeptController class:

    @Autowired
    EmployeeMapper employeeMapper
    
    @GetMapping("emp/{id}")
    public Employee getEmp(@PathVariable("id") Integer id) {
        return employeeMapper.getEmpById(id);
    }
Copy the code

Localhost :8080/emp/1

We found that dId was not queried because the database field is d_id and the Java field is dId, so we need to configure something as we dId in the annotations version. Let’s open the mapper global configuration file and add:

<?xml version="1.0" encoding="UTF-8" ? >
        
      
<configuration>
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
</configuration>
Copy the code

MapUnderscoreToCamelCase: Whether to enable Automatic CamelCase mapping, that is, a similar mapping from the classic database column name A_COLUMN to the classic Java property name aColumn.

Try again:

You can see it’s been a success

Either version. Depending on your actual situation, annotations are convenient, but not for complex business

---

Goodbye and thank you