Integrate mybatis (Druid, paging plug-in) to build web services

First, local environment

• Operating system: Mac OS X 10.13.2

• Editor: IntelliJ IDEA 2017

• JDK version: JDK 1.8

• Maven version: Apache-Maven-3.5.0

• SpringBoot version: SpringBoot 2.0

2. Pom dependency files

<?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 http://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.1.3. RELEASE</version>
		<relativePath/> <! -- lookup parent from repository -->
	</parent>
	<groupId>com.lonelycountry</groupId>
	<artifactId>springboot-mybatis</artifactId>
	<version>0.0.1 - the SNAPSHOT</version>
	<name>springboot-mybatis</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-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.0.0</version>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-configuration-processor</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<! -- Paging plugin -->
		<dependency>
			<groupId>com.github.pagehelper</groupId>
			<artifactId>pagehelper-spring-boot-starter</artifactId>
			<version>1.2.5</version>
		</dependency>
		<! Alibaba druid database connection pool -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid-spring-boot-starter</artifactId>
			<version>1.1.9</version>
		</dependency>
	</dependencies>

	<build>
    	<plugins>
    		<plugin>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-maven-plugin</artifactId>
    		</plugin>
    		<! -- This automatic generation tool is not easy to use.
    		<! --<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>
</project>
Copy the code

Configuration file and engineering structure

1. Configuration files

application.yml

spring:
  datasource:
    name: springboot-demo
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      filters: stat
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: root
      password: * * * * * *
      url: jdbc:mysql://localhost:3306/springboot-mybatis
      initial-size: 1
      min-idle: 1
      max-active: 20
      max-wait: 60000
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000
      validation-query: SELECT 'x'
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      pool-prepared-statements: false
      max-pool-prepared-statement-per-connection-size: 20
  http:
    encoding:
      charset: UTF-8
server:
  port: 80
  tomcat:
    uri-encoding: utf-8
mybatis:
  mapper-locations: classpath:mapping/*.xml
  type-aliases-package: com.lonelycountry.springbootmybatis.po
  configuration:
    map-underscore-to-camel-case: true
pagehelper:
  helper-dialect: mysql
  reasonable: true
  support-methods-arguments: true
  params: count=countSql
Copy the code

2. Directory structure

Write code

1. Build the Po layer and database tables

(1), the User class

package com.lonelycountry.springbootmybatis.po;

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;
import org.apache.ibatis.type.Alias;

import java.sql.Timestamp;

@Alias("User")// Set the alias, mapper. XML does not need to fill in the full path every time
@ToString
@Setter
@Getter
@NoArgsConstructor
@AllArgsConstructor
public class User {
    private Long id;

    private String username;

    private String password;

    private Integer age;

    private Timestamp createDate;

}
Copy the code

(2), the User table

2. Build the Dao layer

package com.lonelycountry.springbootmybatis.dao;

import com.lonelycountry.springbootmybatis.po.User;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface UserMapper {
    List<User> listUsers(a);

    List<User> listUsers(@Param("pageNum") int pageNum, @Param("pageSize") int pageSize);

    User getUser(@Param("id") Long id);

    void insertUser(User user);

    void updateUser(User user);

    void batchInsertUser(@Param("users") List<User> users);

    void deleteUser(@Param("id") Long id);

    void batchDeleteUser(@Param("ids") List<Long> ids);
}
Copy the code

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

      
<mapper namespace="com.lonelycountry.springbootmybatis.dao.UserMapper">
  <select id="listUsers" resultType="User">
    select * from user
  </select>

  <select id="getUser" resultType="User">
    select * from user
    <where>
      <if test="id ! = null">
        id = #{id}
      </if>
    </where>
  </select>

  <insert id="insertUser">
    insert into user (id,username,password,age,create_date)
    values (#{id},#{username},#{password},#{age},#{createDate})
  </insert>

  <insert id="batchInsertUser">
    insert into user (id,username,password,age,create_date)
    values
    <foreach collection="users" item="user" index="index" separator=",">
      (#{user.id},#{user.username},#{user.password},#{user.age},#{user.createDate})
    </foreach>
  </insert>

  <update id="updateUser">
    update user
    <set>
      username = #{username},
      password = #{password},
      age = #{age},
      create_date = #{createDate}
    </set>
    <where>
      id = #{id}
    </where>
  </update>
  
  <delete id="deleteUser">
    delete from user
    <where>
      <if test="id ! = null">
        id = #{id}
      </if>
    </where>
  </delete>

  <delete id="batchDeleteUser">
    delete from user
    <where>
      id in
      <foreach collection="ids" item="id" index="index" separator="," open="(" close=")">
        <if test="id ! = null">
          #{id}
        </if>
      </foreach>
    </where>
  </delete>
</mapper>
Copy the code

Write the general increase, delete, change, check and part of the batch operation. Please refer to the official document of Mybatis for details.

3. Build the Controller layer

UserController

package com.lonelycountry.springbootmybatis.controller;

import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.lonelycountry.springbootmybatis.dao.UserMapper;
import com.lonelycountry.springbootmybatis.po.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.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import java.sql.Timestamp;
import java.util.List;

/ * * *@author zhuqiwei
 * 2019-02-19.
 */
@RestController
public class UserController {
    @Autowired
    UserMapper userMapper;
    @Autowired
    private HttpServletRequest request;

    @GetMapping("/user/all")
    public List<User> getUser(a) {
        List<User> users = userMapper.listUsers();
        return users;
    }

    @GetMapping("/user/{id}")
    public User getUser(@PathVariable Long id) {
        User user = userMapper.getUser(id);
        return user;
    }

    @PostMapping("/user/create")
    public User insertUser(@RequestBody User user) {
        Timestamp timestamp = new Timestamp(System.currentTimeMillis());
        user.setCreateDate(timestamp);
        userMapper.insertUser(user);
        return user;
    }

    @PostMapping("/user/update")
    public User updateUser(@RequestBody User user) {
        Timestamp timestamp = new Timestamp(System.currentTimeMillis());
        user.setCreateDate(timestamp);
        userMapper.updateUser(user);
        return user;
    }

    @PostMapping("/user/create/batch")
    public List<User> insertUser(@RequestBody List<User> users) {
        Timestamp timestamp = new Timestamp(System.currentTimeMillis());
        users.forEach(user -> {
            user.setCreateDate(timestamp);
        });

        userMapper.batchInsertUser(users);
        return users;
    }

    @PostMapping("/user/delete/{id}")
    public List<User> deleteUser(@PathVariable("id") Long id) {
        userMapper.deleteUser(id);
        return getUser();
    }

    @PostMapping("/user/delete/batch")
    public List<User> updateUser(@RequestBody List<Long> ids) {
        userMapper.batchDeleteUser(ids);
        return getUser();
    }

    @GetMapping("/user/all/{page}/{size}")
    public PageInfo<User> getUser(@PathVariable("page") int page, @PathVariable("size") int size) {
        1 / / method
        //List<User> users = PageHelper.startPage(page, size).doSelectPage(() -> userMapper.listUsers());
        //return users;

        2 / / method
        //PageHelper.startPage(page, size);
        //List<User> users = userMapper.listUsers();
        //Page<User> userPage = (Page<User>) users;
        //return userPage;

        4 / / method
        PageHelper.startPage(page, size);
        List<User> users = userMapper.listUsers();
        PageInfo<User> pageInfo = new PageInfo<>(users);
        return pageInfo;

        //return null;
    }

    @GetMapping("/user/all/flip")
    public List<User> getUser2(a) {
        3 / / method
        PageHelper.startPage(request);
        List<User> users = userMapper.listUsers();
        Page<User> userPage = (Page<User>) users;
        returnuserPage; }}Copy the code

Note: In the acquisition of all user data when the use of paging plug-in and achieve four methods, specific related documents can refer to the official documentation of Mybatis paging plug-in.

Five, description,

This article is originally published.