SpringBoot integration Mybatis

Posted May 27, 202013 min read

SpringBoot integration MyBatis

\ ------ Now that you have started to integrate Mybatis, I believe that the basic SpringBoot project can certainly be created by yourself, so I will not repeat them here, not much B, let us straight Ben theme

MyBatis --- semi-automatic ORM framework

Nowadays, the common persistence layer frameworks are:Hibernate, MyBatis, JPA ...

Compared to Hibernate, MyBatis is easier to get started

Today I will demonstrate how SpringBoot integrates Mybatis to everyone and learn together.

If you have any friends who have been in contact with these two technology stacks, you can go to Kangkang first and come back later ~~.

This is an authoritative explanation from Baidu Encyclopedia

        MyBatis is an excellent persistence layer framework that supports customized SQL, stored procedures, and advanced mapping. MyBatis avoids almost all JDBC code and manually setting parameters and obtaining result sets. MyBatis can use simple XML or annotations to configure and map native information, and map interfaces and Java POJOs(Plain Ordinary Java Objects) to records in the database.

        MyBatis is an excellent persistence layer framework that supports ordinary SQL queries, stored procedures, and advanced mapping. MyBatis eliminates the manual setting of almost all JDBC codes and parameters and the retrieval of result sets. MyBatis uses simple XML or annotations for configuration and original mapping to map interfaces and Java POJOs(Plain Ordinary Java Objects) to records in the database.

Project framework

Insert picture description here


Maven's pom.xml file

Dependencies to be imported --- You can check the imported technology stack when creating a Spring Boot project

<? xml version = "1.0" encoding = "UTF-8"?>
<project xmlns = "http://maven.apache.org/POM/4.0.0" xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation = "http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion> 4.0.0 </modelVersion>
    <parent>
        <groupId> org.springframework.boot </groupId>
        <artifactId> spring-boot-starter-parent </artifactId>
        <version> 2.2.6.RELEASE </version>
        <relativePath /> <!-lookup parent from repository->
    </parent>
    <groupId> com.cody </groupId>
    <artifactId> sbootmybatis </artifactId>
    <version> 0.0.1-SNAPSHOT </version>
    <name> sbootmybatis </name>
    <description> Demo project for Spring Boot </description>

    <properties>
        <java.version> 1.8 </java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId> org.springframework.boot </groupId>
            <artifactId> spring-boot-starter-thymeleaf </artifactId>
        </dependency>
        <dependency>
            <groupId> org.mybatis.spring.boot </groupId>
            <artifactId> mybatis-spring-boot-starter </artifactId>
            <version> 2.1.2 </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-web </artifactId>
        </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>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId> org.springframework.boot </groupId>
                <artifactId> spring-boot-maven-plugin </artifactId>
            </plugin>
        </plugins>
        <resources>
            <!-The configuration here is to identify the mapper.xml file, which can also be configured in application.properties->
            <resource>
                <directory> src/main/java </directory>
                <includes>
                    <include> **/*. xml </include>
                </includes>
            </resource>
        </resources>
    </build>
</project>

mapper configuration file

UserMapper.xml

\ == Note the location of the file ==

<? 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">
<!-namespace:Change the unique identifier of the mapper.xml mapping file and must be the same as the path of the interface of the data processing layer->
<mapper namespace = "com.cody.sbootmybatis.sbootmybatis.mapper.UserMapper">
<!-The property attribute must be added, different from whether the primary key is added->
    <resultMap id = "user" type = "User">
        <id column = "userid" property = "userid" javaType = "int"> </id>
        <result column = "username" property = "username" javaType = "String"> </result>
        <result column = "userage" property = "userage" javaType = "int"> </result>
        <result column = "userpwd" property = "userpwd" javaType = "String"> </result>
    </resultMap>
    <!-id value must be consistent with the interface name of the data processing layer->
    <!-User here->
    <select id = "queryAlluser" resultType = "User">
        select * from user
    </select>
    <select id = "queryuserbyid" parameterType = "int" resultMap = "user" resultType = "User">
         select * from user
         <trim suffixOverrides = "and" prefixOverrides = "and">
        <where>
              <if test = "userid! = null">
                 and userid = # {userid}
              </if>
         </where>
         </trim>
    </select>
    <select id = "queryuserbyname" resultType = "User" parameterType = "string" resultMap = "user">
        select * from user
        <trim suffixOverrides = "and" prefixOverrides = "and">
            <where>
                <if test = "username! = null">
                    and username = # {username}
                </if>
            </where>
        </trim>
    </select>
    <update id = "UpdUser" parameterType = "User">
    </update>
    <delete id = "DelUser"> </delete>
    <insert id = "AddUser" parameterType = "User">
        insert into user value(${userid}, # {username}, ${userage}, # {userpwd})
    </insert>
</mapper>

Application.properties configuration

# Port configuration
server.port = 8081
# Database access configuration
spring.datasource.type = com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name = com.mysql.cj.jdbc.Driver
#Change database_name to the name of your database
spring.datasource.url = jdbc:mysql://localhost:3306/database_name? serverTimezone = UTC & useUnicode = true & characterEncoding = utf-8
spring.datasource.username = root
spring.datasource.password = 123456

application.yml configuration

# Configure the alias of Mybatis package, xxxType = "xxx" in the mapper.xml file, you can directly write the class name of the entity class, you do not need to write all the class names
# <select id = "queryAlluser" resultType = "User">
# select * from user
# </select>
# <select id = "queryAlluser" resultType = "com.xxx.xxx.entity.User">
# select * from user
# </select>
mybatis:
  type-aliases-package:com.xxx.xxx.entity
 #Disable the template buffer, you can observe the changes of the html page in real time in the development environment
 spring:
  thymeleaf:
   cache:false

controller

logincontroller.java

/**
loginController
Simple login test
Use with index.html
* /
import com.cody.sbootmybatis.sbootmybatis.entity.User;
import com.cody.sbootmybatis.sbootmybatis.mapper.UserMapper;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.annotation.Resource;
import java.util.Map;

/**
* @author xxx
* @date 2020/5/6-10:09
* /
@Controller
public class logincontrollor {
@Resource
private UserMapper userMapper;
@RequestMapping(value = "/user/login", method = RequestMethod.POST)
@ResponseBody
public String login(@RequestParam("username") String username,
@RequestParam("password") String password, Map <String, Object> map) {
User user = userMapper.queryuserbyname(username);
if(username.equals(user.getUsername()) && password.equals(user.getuserpwd())) {
return "Login successful";
}
else {
return "Login failed";
}
}
}


mycontroller.java

/**
Add, delete and modify
* /

package com.cody.sbootmybatis.sbootmybatis.controllor;

import com.cody.sbootmybatis.sbootmybatis.mapper.UserMapper;
import com.cody.sbootmybatis.sbootmybatis.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;

@RestController
@RequestMapping("/user")
public class mycontroller {
    @Autowired(required = false)

    private UserMapper userMapper;
    @RequestMapping(value = {"/queryAlluser"}, method = RequestMethod.GET)
   /**
     *
     *
     * @return List <User>
     *
     *
     * * /
    public List <User> queryalluser() {
        List <User> list = userMapper.queryAlluser();
        return list;
    }
    @RequestMapping(value = {"/queryuserbyid"}, method = RequestMethod.GET)
   /**
     *
     *
     * @param userid
     * @return user
     *
     *
     * /
    public User queryuserbyid(String userid) {
        User user = userMapper.queryuserbyid(Integer.parseInt(userid));
        return user;
    }
    @RequestMapping(value = {"/adduser"}, method = RequestMethod.GET)
   /**
     *
     *
     * @param useri
     * @return user
     *
     *
     * /
    public String adduser(User user) {
      boolean flag = userMapper.AddUser((User) user);
        return flag? "success":"fail";
    }
    @RequestMapping(value = {"/Upuserbyid"}, method = RequestMethod.GET)
   /**
     *
     *
     * @param userid
     * @return user
     *
     *
     * /
    public String Upuser(User user) {
        boolean flag = userMapper.UpdUser((User) user);
        return flag? "success":"fail";
    }
    @RequestMapping(value = {"/Deluserbyid"}, method = RequestMethod.GET)
   /**
     *
     *
     * @param userid
     * @return user
     *
     *
     * /
    public String Deluserbyid(String userid) {
        boolean flag = userMapper.DelUser(Integer.parseInt(userid));
        return flag? "success":"fail";
    }
}

mapper

package com.cody.sbootmybatis.sbootmybatis.mapper;

import com.cody.sbootmybatis.sbootmybatis.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
* @author Cody
* @date 2020/5/2-13:25
* @Decription:
* /
@Mapper
public interface UserMapper {
/**
* Check Read(Retrieve)
* fetch data
* @return List
* /
List queryAlluser();
/**
* Check Read(Retrieve)
* fetch data by userid
* @param userid
* @return User
* /
/**
@Select("select * from user where userid = # {userid}")
I like to talk about SQL statements written in the mapper.xml file, so that the code is relatively neat
In later development, once the SQL statement becomes cumbersome, it is not conducive to maintenance
However, it is completely feasible to use annotations in this way. After all, some people may write configuration files and may have written quickly.
* /
User queryuserbyid(int userid);
/**
* Add(Create)
* add data by user
* @param user
* @return int
* /
boolean AddUser(User user);
/**
* Delete
* @param id
* @return int
* /
boolean DelUser(int id);
/**
* Update
* @param user
* @return boolean
* /
boolean UpdUser(User user);

    User queryuserbyname(String name);
}

front end

Place the index.html file under the template folder

<! DOCTYPE html>
<html lang = "en" xmlns:th = "http://www.thymeleaf.org">
<head>
    <meta charset = "UTF-8">
    <title> Title </title>
</head>
<body>
<form method = "post" action = "/user/login">
    <input name = "username" type = "text" placeholder = "Please enter a user name" />
    <input type = "password" name = "password" />
    <input type = "submit" th:value = "Login" />
</form>
</body>
</html>

test

Test login operation

Database information:
Insert picture description here

Insert picture description here

Insert picture description here

Insert picture description here

Insert picture description here


Test CRUD(that is, add, delete, modify and check)

Query all

Insert picture description here


Query by ID Insert picture description here


Add User

Pass? And & pass by value

Insert picture description here
Insert picture description here


Precautions

1. The path of the mappe.xml file including the file name must be exactly the same as the interface of the data layer

Insert picture description here

2. The application.properties file and the application.yml file do not need to be written, you can choose one of the two, but I have written it here


IDEA plugin to make development more convenient

An IDEA plugin for everyone:MyBatisX

Function: Writing methods in the interface can directly write CRUD methods in the mapper.xml file to generate the corresponding tags

The shortcut key used in the figure is:\ == alt + enter ==
Insert picture description here


Advantages of MyBatis framework

  1. Compared with JDBC, the amount of code is reduced by more than 50%.
  2. MyBatis is the simplest persistence framework, small and easy to learn.
  3. MyBatis is quite flexible and will not impose any impact on the existing design of the application or database. SQL is written in XML, completely separated from the program code, reducing the degree of coupling, easy for unified management and optimization, and reusable.
  4. Provide XML tags to support writing dynamic SQL statements.
  5. Provide mapping tags to support the mapping between objects and the ORM field of database .

Disadvantages of the MyBatis framework

  1. The workload of writing SQL statements is relatively large, especially when there are many fields and associated tables. It has certain requirements for developers to write SQL statements.
  2. The SQL statement depends on the database, resulting in poor database portability, and the database cannot be changed at will.

SpringBoot integration errors encountered in Mybatis

This is some of the problems I encountered during integration, I hope that the detours I have taken can help everyone a lot.

There was an unexpected error(type = Internal Server Error, status = 500).
Error attempting to get column 'u_account' from result set. Cause:java.sql.SQLDataException:Cannot determine value type from string '201577D0510'; Cannot determine value type from string '201577D0510'; nested exception is java.sql.SQLDataException:Cannot determine value type from string '201577D0510'
org.springframework.dao.DataIntegrityViolationException:Error attempting to get column 'u_account' from result set. Cause:java.sql.SQLDataException:Cannot determine value type from string '201577D0510'
; Cannot determine value type from string '201577D0510'; nested exception is java.sql.SQLDataException:Cannot determine value type from string '201577D0510'
    at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:84)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
    at org.mybatis.spring.SqlSessionTemplate $SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
    at com.sun.proxy. $Proxy65.selectList(Unknown Source)

Solution:

Added parameterless construction method in entity subclass, exception disappeared

java.sql.SQLException:Access denied for user '' @ 'localhost'(using password:NO)

    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:836)
    at com.mysql.cj.jdbc.ConnectionImpl. <init>(ConnectionImpl.java:456)
    at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:246)

Solution:== Time zone error ==

Change configuration information:

spring:
  datasource:
    username:root
    password:123456
    # If the time zone is wrong, you need to add serverTimezone = UTC
    url:jdbc:mysql://localhost:3306/mybatis? serverTimezone = UTC & useUnicode = true & characterEncoding = utf-8
    driver-class-name:com.mysql.cj.jdbc.Driver

There is an error message when using @Autowired annotation

  When using the Spring boot + mybatis framework, the Mapper class is used in the service implementation class. When the @Autowired annotation is added to the Mapper class, an error message is found:could not autowire, no beans of "XXX" type found, but the program is compiled and run. normal.

Processing method:

Option 1:@Autowired(required = false) Set the required attribute value to false, the error disappears

Option 2:Replace @Autowired annotation with @Resource annotation, the error disappears
Similarities and differences between @Resource annotation and @Autowired annotation

These two annotations are used for bean injection. They both save the get and set methods for an object variable, and automatically inject instantiated objects(that is, inject dependencies) into this object. The injection method is different:

@Autowired is based on spring's annotation org.springframework.beans.factory.annotation.Autowired, it is assembled by type by default, if you want it to be assembled by name, you need to add @qualifier("name" under @autowired) `Annotation, when the only implementation class cannot be found, an error is reported. @Autowired annotation must require dependent objects to exist by default, if you want to allow null values, you should set its required property to false,

@Resource is based on the j2ee annotation(which can reduce the coupling with spring),(JDK1.6 and above support) the default is to annotate by name, if you do not specify the name of the assembly bean, when the annotation is written on the field, the field is taken by default Name, according to the name search and assembly through the set method, if there are multiple subclasses, an error will be reported. It should be noted that once the name attribute is specified, it will only be assembled according to the name

Similarities and differences between the solution and @Autowired annotation

These two annotations are used for bean injection. They both save the get and set methods for an object variable, and automatically inject instantiated objects(that is, inject dependencies) into this object. The injection method is different:

@Autowired is based on spring's annotation org.springframework.beans.factory.annotation.Autowired, it is assembled by type by default, if you want it to be assembled by name, you need to add @qualifier("name" under @autowired) \ `Annotation, when the only implementation class cannot be found, an error is reported. @Autowired annotation must require dependent objects to exist by default, if you want to allow null values, you should set its required property to false,

@Resource is based on the j2ee annotation(which can reduce the coupling with spring),(JDK1.6 and above support) the default is to annotate by name, if you do not specify the name of the assembly bean, when the annotation is written on the field, the field is taken by default Name, according to the name search and assembly through the set method, if there are multiple subclasses, an error will be reported. It should be noted that once the name attribute is specified, it will only be assembled according to the name
\ \

The Ending

\ ---------- Progress "100 million" a little bit every day ------------

\ ----- Relyonyouself-