Spring boot practice (XIII) | integration of mybatis (XML version)

Time:2020-1-21

WeChat public number: an excellent waster
If you have any questions or suggestions, please leave a message in the background. I will try my best to solve your problems.

Preface

For example, today I will introduce the integration of springboot and mybatis and the use of mybatis. I have previously introduced the use of mybatis annotated version of springboot integration. In the previous article, I introduced the theory of mybatis. Today I will not introduce the theory of mybatis. I am interested in jumping to read: Spring boot practice (XIII) | integration of mybatis (annotated version)

Preparation

  • SpringBoot 2.1.3
  • IDEA
  • JDK 8

Create table

CREATE TABLE `student`  (
  `id` int(32) NOT NULL AUTO_INCREMENT,
  `Student [ID ` varchar (32) character set utf8 collate utf8 [general] Ci not null comment 'student number',
  `Name ` varchar (32) character set utf8 collate utf8  general Ci not null comment 'name',
  `Age ` int (11) null default null comment 'age',
  `City ` varchar (64) character set utf8 collate utf8 ﹣ general ﹣ Ci not null comment 'city',
  `Dormitory ` varchar (64) character set utf8 collate utf8 ﹣ general ﹣ CI null default null comment 'dormitory',
  `Major` varchar (64) CHARACTER SET utf8 COLLATE utf8 general CI NULL DEFAULT NULL COMMENT 'major',
  PRIMARY KEY (`id`) USING BTREE
)ENGINE=INNODB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8;

Introducing dependency

<dependencies>
        <! -- JDBC connection driver -- >
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <! -- web startup class -- >
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <! -- mybatis depends on -- >
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.0</version>
        </dependency>
        <! -- Druid database connection pool -- >
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.14</version>
        </dependency>
        <! -- MySQL connection class -- >
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
            <scope>runtime</scope>
        </dependency>
        <! -- paging plug-in -- >
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.5</version>
        </dependency>
        <! -- test depends on -- >
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <! -- springboot Maven plug-in -- >
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <! -- mybatis generator auto generate code plug-in -- >
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.2</version>
                <configuration>
                    <configurationFile>${basedir}/src/main/resources/generator/generatorConfig.xml</configurationFile>
                    <overwrite>true</overwrite>
                    <verbose>true</verbose>
                </configuration>
            </plugin>
        </plugins>
    </build>

The code explanation is very detailed, but to mention here, the mybatis generator plug-in is used to generate code automatically, and the PageHelper plug-in is used for physical paging.

Project configuration

server:
  port: 8080

spring:
    datasource:
        name: test
        url: jdbc:mysql://127.0.0.1:3306/test
        username: root
        password: 123456
        #Druid related configuration
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        filters: stat
        maxActive: 20
        initialSize: 1
        maxWait: 60000
        minIdle: 1
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 300000
        validationQuery: select 'x'
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: true
        maxOpenPreparedStatements: 20

##This configuration node is an independent node. Many students are easy to put this configuration under the spring node, which causes the configuration to be unrecognized
mybatis:
  Mapper locations: classpath: mapping / *. XML note: be sure to map the path of the mapper mapping XML file
  Type aliases package: com.nasus.mybatisxml.model note: the path of the corresponding entity class

#PageHelper paging plug-in
pagehelper:
    helperDialect: mysql
    reasonable: true
    supportMethodsArguments: true
    params: count=countSql

Mybatis generator configuration file

Note here that when configuring the configuration file corresponding to the generator plug-in in pom.xml, add this sentence to pom.xml to indicate the configuration file path corresponding to the configuration file corresponding to the generator plug-in. This is already configured in POM. See POM configuration above.

${basedir}/src/main/resources/generator/generatorConfig.xml

generatorConfig.xml :

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
    <! -- database driver: select the database driver package on your local hard disk -- >
    <classPathEntry  location="D:\repository\mysql\mysql-connector-java.1.47\mysql-connector-java-5.1.47.jar"/>
    <context id="DB2Tables"  targetRuntime="MyBatis3">
        <commentGenerator>
            <property name="suppressDate" value="true"/>
            <! -- remove auto generated comments true: Yes: false: no -- >
            <property name="suppressAllComments" value="true"/>
        </commentGenerator>
        <! -- database link URL, user name, password -- >
        <jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://127.0.0.1/test" userId="root" password="123456">
        </jdbcConnection>
        <javaTypeResolver>
            <property name="forceBigDecimals" value="false"/>
        </javaTypeResolver>
        <! -- package name and location of the build model -- >
        <javaModelGenerator targetPackage="com.nasus.mybatisxml.model" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
            <property name="trimStrings" value="true"/>
        </javaModelGenerator>
        <! -- package name and location of the generated mapping file -- >
        <sqlMapGenerator targetPackage="mapping" targetProject="src/main/resources">
            <property name="enableSubPackages" value="true"/>
        </sqlMapGenerator>
        <! -- package name and location where Dao is generated -- >
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.nasus.mybatisxml.mapper" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
        </javaClientGenerator>
        <! -- the tablename of the table to be generated is the table name or view name in the database domainobjectname is the entity class name -- >
        <table tableName="student" domainObjectName="Student" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
    </context>
</generatorConfiguration>

The code comments are very detailed, not much to say.

Code generation process

Step 1: select edit configuration

Step 2: choose to add Maven configuration

Step 3: add the command mybatis generator: generate – E and click OK

Step 4: run the configuration to generate code

Special attention!!! Do not run the same table multiple times, because mapper’s mapping file will generate code multiple times, resulting in errors. Remember. To run more than once, delete the mapper mapping file code generated last time and run it again.

Step 5: check the generated results

Problems encountered

Please refer to the solutions written by others to solve the problems I encounteredWrong database time zoneas well asGenerate only insert methodsThese two questions. All are solved by reading the following article:

Mybatis generator generates code automatically and possible problems

Generated code

1. Entity class: student.java

package com.nasus.mybatisxml.model;

public class Student {

    private Long id;

    private Integer age;

    private String city;

    private String dormitory;

    private String major;

    private String name;

    private Long studentId;

   //Omit get and set methods
}

2. Mapper interface: studentmapper.java

package com.nasus.mybatisxml.mapper;

import com.nasus.mybatisxml.model.Student;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface StudentMapper {
    int deleteByPrimaryKey(Long id);

    int insert(Student record);

    int insertSelective(Student record);

    Student selectByPrimaryKey(Long id);

    //The method I added, and I want to add this method in the mapping file accordingly
    List<Student> selectStudents();

    int updateByPrimaryKeySelective(Student record);

    int updateByPrimaryKey(Student record);
}

3. Mapping file: studentmapper.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.nasus.mybatisxml.mapper.StudentMapper" >
  <resultMap id="BaseResultMap" type="com.nasus.mybatisxml.model.Student" >
    <id column="id" property="id" jdbcType="BIGINT" />
    <result column="age" property="age" jdbcType="INTEGER" />
    <result column="city" property="city" jdbcType="VARCHAR" />
    <result column="dormitory" property="dormitory" jdbcType="VARCHAR" />
    <result column="major" property="major" jdbcType="VARCHAR" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <result column="student_id" property="studentId" jdbcType="BIGINT" />
  </resultMap>

  <sql id="Base_Column_List" >
    id, age, city, dormitory, major, name, student_id
  </sql>

  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long" >
    select 
    <include refid="Base_Column_List" />
    from student
    where id = #{id,jdbcType=BIGINT}
  </select>

  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long" >
    delete from student
    where id = #{id,jdbcType=BIGINT}
  </delete>

  <insert id="insert" parameterType="com.nasus.mybatisxml.model.Student" >
    insert into student (id, age, city, 
      dormitory, major, name, 
      student_id)
    values (#{id,jdbcType=BIGINT}, #{age,jdbcType=INTEGER}, #{city,jdbcType=VARCHAR}, 
      #{dormitory,jdbcType=VARCHAR}, #{major,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR}, 
      #{studentId,jdbcType=BIGINT})
  </insert>

  <insert id="insertSelective" parameterType="com.nasus.mybatisxml.model.Student" >
    insert into student
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="age != null" >
        age,
      </if>
      <if test="city != null" >
        city,
      </if>
      <if test="dormitory != null" >
        dormitory,
      </if>
      <if test="major != null" >
        major,
      </if>
      <if test="name != null" >
        name,
      </if>
      <if test="studentId != null" >
        student_id,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=BIGINT},
      </if>
      <if test="age != null" >
        #{age,jdbcType=INTEGER},
      </if>
      <if test="city != null" >
        #{city,jdbcType=VARCHAR},
      </if>
      <if test="dormitory != null" >
        #{dormitory,jdbcType=VARCHAR},
      </if>
      <if test="major != null" >
        #{major,jdbcType=VARCHAR},
      </if>
      <if test="name != null" >
        #{name,jdbcType=VARCHAR},
      </if>
      <if test="studentId != null" >
        #{studentId,jdbcType=BIGINT},
      </if>
    </trim>
  </insert>

  <update id="updateByPrimaryKeySelective" parameterType="com.nasus.mybatisxml.model.Student" >
    update student
    <set >
      <if test="age != null" >
        age = #{age,jdbcType=INTEGER},
      </if>
      <if test="city != null" >
        city = #{city,jdbcType=VARCHAR},
      </if>
      <if test="dormitory != null" >
        dormitory = #{dormitory,jdbcType=VARCHAR},
      </if>
      <if test="major != null" >
        major = #{major,jdbcType=VARCHAR},
      </if>
      <if test="name != null" >
        name = #{name,jdbcType=VARCHAR},
      </if>
      <if test="studentId != null" >
        student_id = #{studentId,jdbcType=BIGINT},
      </if>
    </set>
    where id = #{id,jdbcType=BIGINT}
  </update>

  <update id="updateByPrimaryKey" parameterType="com.nasus.mybatisxml.model.Student" >
    update student
    set age = #{age,jdbcType=INTEGER},
      city = #{city,jdbcType=VARCHAR},
      dormitory = #{dormitory,jdbcType=VARCHAR},
      major = #{major,jdbcType=VARCHAR},
      name = #{name,jdbcType=VARCHAR},
      student_id = #{studentId,jdbcType=BIGINT}
    where id = #{id,jdbcType=BIGINT}
  </update>

  <! -- Method I added -- >
  <select id="selectStudents" resultMap="BaseResultMap">
    SELECT
    <include refid="Base_Column_List" />
    from student
  </select>
</mapper>

Serviec level

1. Interface:

public interface StudentService {

    int addStudent(Student student);

    Student findStudentById(Long id);

    PageInfo<Student> findAllStudent(int pageNum, int pageSize);

}

2. Implementation class

@Service
public class StudentServiceImpl implements StudentService{

    //Error will be reported, no impact
    @Resource
    private StudentMapper studentMapper;

    /**
     *Add student information
     * @param student
     * @return
     */
    @Override
    public int addStudent(Student student) {
        return studentMapper.insert(student);
    }

    /**
     *Query student information according to ID
     * @param id
     * @return
     */
    @Override
    public Student findStudentById(Long id) {
        return studentMapper.selectByPrimaryKey(id);
    }

    /**
     *Query all student information and paginate
     * @param pageNum
     * @param pageSize
     * @return
     */
    @Override
    public PageInfo<Student> findAllStudent(int pageNum, int pageSize) {
        //Pass parameters to this method to realize physical paging, which is very simple.
        PageHelper.startPage(pageNum, pageSize);
        List<Student> studentList = studentMapper.selectStudents();
        PageInfo result = new PageInfo(studentList);
        return result;
    }
}

Controller layer

@RestController
@RequestMapping("/student")
public class StudentController {

    @Autowired
    private StudentService studentService;

    @GetMapping("/{id}")
    public Student findStidentById(@PathVariable("id") Long id){
        return studentService.findStudentById(id);
    }

    @PostMapping("/add")
    public int insertStudent(@RequestBody Student student){
        return studentService.addStudent(student);
    }

    @GetMapping("/list")
    public PageInfo<Student> findStudentList(@RequestParam(name = "pageNum", required = false, defaultValue = "1") int pageNum,
            @RequestParam(name = "pageSize", required = false, defaultValue = "10") int pageSize){
        return studentService.findAllStudent(pageNum,pageSize);
    }
}

Startup class

@SpringBootApplication
@Mapperscan ("com. Nasus. Mybatisxml. Mapper") // scan the mapper interface. You must add
public class MybatisxmlApplication {

    public static void main(String[] args) {
        SpringApplication.run(MybatisxmlApplication.class, args);
    }
}

In a word, @ mapperscan (“com. Nasus. Mybatisxml. Mappe”) is a key annotation, which corresponds to the package path corresponding to mapper (DAO) in the project, and must be added, otherwise it will lead to exceptions.

Postman test

1. Insert method:

2. According to the ID query method:

3. Pagination query method:

Source download

https://github.com/turoDog/De…

How about a star?

Posterior language

If this article is of any help to you, please help me. Your good looks are my driving force for writing.

In addition, after attention, send1024Free study materials are available. For details, please refer to this old article: Python, C + +, Java, Linux, go, front end, algorithm data sharing