Spring Boot整合Mybatis项目开发Restful API接口

于 2019-01-02 01:00:51 发布
阅读量6.3k 收藏 28
点赞数 6
分类专栏： Java sql spring-boot xml jdbc 数据库批量操作 分页查询
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/heshengfu1211/article/details/85490612
版权

 前言：使用Spring boot搭建的项目开发人员可以使用大量的注解替代了繁琐的xml配置，在我的上一篇文章使用Spring Boot + Spring DataJpa 开发RestFul Api时，采用了Spring Data Jpa作为持久层框架，省略了大部分sql语句。但是采用Spring DataJpa作为持久层框架，Dao层直接继承CrudRepository或JpaRepository接口，对于不是深入理解底层映射原理的开发人员一时半会很难实现复杂的sql语句，包括一些动态sql语句。而在以前开发过的Spring MVC + MyBatis项目中我们更多的是使用xml标签语句完成sql语句，xml标签化的sql语句支持动态sql语句和存储过程等复查的查询SQL语句块。我本人之前在公司的项目开发中也一直采用xml标签化的sql语句完一些复杂的数据库操作业务逻辑。那么下面就来分享一下自己的这个项目吧，包括实现分页查询和批量操作的API接口。所所接口均通过post测试调用成功。

本项目使用的环境：

  • 开发工具：IntelliJ IDEA 2018.2.3
  • JDK版本：1.8.0_161
  • maven版本：3.5.3
  • mysql版本: 5.6
  • 操作系统：win10 64位
  • SpringBoot 版本：2.0.5 

1. 搭建项目

    1.1 进入站点https://start.spring.io/ 快速搭建一个Spring Boot 整合Mybatis的demo项目, 选择Spring Boot版本号（目前的稳定版本是2.1.1，本人在搭建这一项目时当时用的是2.1.0版本，但是发现启动时连接数据源一直报错，查了很多博客还是解决不了，于是换成了2.0.5版本就没报错了）在Group和Group下面的输入框中包名和项目名，添加Web、JDBC、Mysql、Mybatis等依赖, 然后点击生成项目按钮生成.zip的项目文件，会自动下载到本地，解压导入到IDEA工具中即可 

使用Spring Initializr生成demo工程
使用Spring Initializr生成Spring Boot项目

本人当时创建的项目groupId 为com.example, artifactId为mybatis, 在pom.xml文件中将spring boot的版本号改成了

2.0.5.RELEASE

1.2 项目目录结构及pom.xml文件

项目结构
项目代码结构图

 

1.3 pom.xml文件代码，手工引入了alibaba德鲁伊数据源连接池druid、jackson-core、jackson-databind、pagehelper等依赖

<?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>

	<groupId>com.example</groupId>
	<artifactId>mybatis</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>mybatis</name>
	<description>Demo project for Spring Boot</description>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.5.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<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>1.3.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-test</artifactId>
			<scope>test</scope>
		</dependency>
		<!-- alibaba德鲁伊数据源连接池依赖-->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.0.5</version>
		</dependency>
		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-core</artifactId>
		</dependency>
		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-databind</artifactId>
		</dependency>

		<dependency>
			<groupId>com.fasterxml.jackson.module</groupId>
			<artifactId>jackson-module-parameter-names</artifactId>
		</dependency>

		<!--分页插件 -->
		<dependency>
			<groupId>com.github.pagehelper</groupId>
			<artifactId>pagehelper</artifactId>
			<version>4.1.3</version>
		</dependency>
		<!-- thymeleaf模板引擎-->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-thymeleaf</artifactId>
		</dependency>

	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

2. 配置文件application.yml及启动类MybatisApplication.java

 2.1 数据库建表，创建用户并授权

    使用mysql-workbench客户端root账号登录成功后建表userinfo, 新建用户robbot并授权, 依次执行下面两部分sql脚本

建表userinfo，创建新用户robbot并授权

 

 

 

 

 

 

 

 

 

 

 

 

新建部门表dept,并插入4条记录

 

 

 

 

 

 

 

 

 

 

 

 

2.2 application.yml代码

  2.2 MybatisApplication.java源代码

  @MapperScan注解的basePackags属性开启扫描dao接口类所在的包，多个包可用逗号分隔；没有这个注解的话项目启动时会报异常

 @SpringBootApplication也可以用@EnableAutoConfiguration和@ComponentScan（basePackages={"com.xxx.xxx"}）代替

@SpringBootApplication是@EnableAutoConfiguration和@ComponentScan两个注解的合并，默认扫描启动类所在的包

package com.example.mybatis;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;



@SpringBootApplication
//@EnableAutoConfiguration
//@ComponentScan(basePackages = {"com.example.mybatis"})
@MapperScan(basePackages={"com.example.mybatis.dao"})
public class MybatisApplication{

	public static void main(String[] args) {

		SpringApplication.run(MybatisApplication.class, args);
	}


}

3. 接口开发

 3.1 实体类

    3.1.1 对应userinfo表的UserTO实体类

package com.example.mybatis.model;

import java.io.Serializable;

public class UserTO implements Serializable {
    private int id;

    private int deptNo;

    private String deptName;

    private String userAccount;

    private String password;

    private String nickName;

    private String emailAddress;

    private String birthDay;

    private Long phoneNum;

    public int getId() {
        return id;
    }

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

    public int getDeptNo() {
        return deptNo;
    }

    public void setDeptNo(int deptNo) {
        this.deptNo = deptNo;
    }

    public String getDeptName() {
        return deptName;
    }

    public void setDeptName(String deptName) {
        this.deptName = deptName;
    }

    public String getUserAccount() {
        return userAccount;
    }

    public void setUserAccount(String userAccount) {
        this.userAccount = userAccount;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getNickName() {
        return nickName;
    }

    public void setNickName(String nickName) {
        this.nickName = nickName;
    }

    public String getEmailAddress() {
        return emailAddress;
    }

    public void setEmailAddress(String emailAddress) {
        this.emailAddress = emailAddress;
    }

    public String getBirthDay() {
        return birthDay;
    }

    public void setBirthDay(String birthDay) {
        this.birthDay = birthDay;
    }

    public Long getPhoneNum() {
        return phoneNum;
    }

    public void setPhoneNum(Long phoneNum) {
        this.phoneNum = phoneNum;
    }
}

  3.1.2  接口响应实体类ServiceResponse

package com.example.mybatis.model;

import java.io.Serializable;

public class ServiceResponse<T> implements Serializable {

    private int status;

    private String message;

    private String serviceId;

    private T data;

    public int getStatus() {
        return status;
    }

    public void setStatus(int status) {
        this.status = status;
    }

    public String getMessage() {
        return message;
    }

    public void setMessage(String message) {
        this.message = message;
    }

    public String getServiceId() {
        return serviceId;
    }

    public void setServiceId(String serviceId) {
        this.serviceId = serviceId;
    }

    public T getData() {
        return data;
    }

    public void setData(T data) {
        this.data = data;
    }
}

  3.1.3 分页查询实体类PageInfo

package com.example.mybatis.model;

import java.io.Serializable;
import java.util.List;

public class PageInfo<T> implements Serializable {

    int pageNum;

    int pageSize;

    int startIndex;

    int endIndex;

    int totalRows;

    List<T> dataList;

    public int getPageNum() {
        return pageNum;
    }

    public void setPageNum(int pageNum) {
        this.pageNum = pageNum;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public int getStartIndex() {
        return startIndex;
    }

    public void setStartIndex(int startIndex) {
        this.startIndex = startIndex;
    }

    public int getEndIndex() {
        return endIndex;
    }

    public void setEndIndex(int endIndex) {
        this.endIndex = endIndex;
    }

    public List<T> getDataList() {
        return dataList;
    }

    public void setDataList(List<T> dataList) {
        this.dataList = dataList;
    }

    public int getTotalRows() {
        return totalRows;
    }

    public void setTotalRows(int totalRows) {
        this.totalRows = totalRows;
    }
}

3.1.4 分层开发接口代码结构

分层开发接口结构
分层开发接口代码结构

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  3.2  四层架构模式完成接口代码

     3.2.1 Controller层代码（注意Post接口请求体数据参数前必须加@RequestBody注解）

package com.example.mybatis.controller;

import com.example.mybatis.model.PageInfo;
import com.example.mybatis.service.IUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import com.example.mybatis.model.UserTO;
import com.example.mybatis.model.ServiceResponse;
import java.util.List;

/**
 * spring-boot 整合mybatis的 controller
 */
@RestController
@RequestMapping(value="/user")
public class MybatisController {
    @Autowired
    private IUserService userService;

    /**
     * 根据用户账号查询单个用户信息
     * @param userAccout
     * @return
     */
    @RequestMapping(value= "/userinfo",method = RequestMethod.GET,produces = {"application/json;charset=UTF-8"},consumes = {"application/json"})
    public ServiceResponse<UserTO> getUserInfoByAccount(@RequestParam("userAccount") String userAccout){

        return userService.queryUserInfoByAccount(userAccout);
    }

    /**
     * 添加单个用户信息
     * @param userTO
     * @return
     */
    @RequestMapping(value="add/userinfo",method=RequestMethod.POST,produces = {"application/json;charset=UTF-8"},consumes = {"application/json"})
    public ServiceResponse<String> addUserInfo(@RequestBody UserTO userTO){

        return userService.addUserTO(userTO);
    }

    /**
     * 修改单个用户信息
     * @param userTO
     * @return
     */
    @RequestMapping(value="/update/userinfo",method = RequestMethod.POST)
    public ServiceResponse<String> updateUserInfo(@RequestBody UserTO userTO){

        return userService.updateUserTO(userTO);
    }

    /**
     * 批量添加用户信息
     * @param userTOs
     * @return
     */
    @RequestMapping(value="/batchadd/userinfo",method=RequestMethod.POST)
    public ServiceResponse<String> batchAddUserInfo(@RequestBody List<UserTO> userTOs){

        return userService.batchAddUserTO(userTOs);
    }

    /**
     * 分页查询用户信息
     * @param pageNum
     * @param pageSize
     * @return
     */
    @RequestMapping(value="alluser/page",method=RequestMethod.GET)
    public ServiceResponse<PageInfo> queryAllUser(@RequestParam(value="pageNum") int pageNum,
                                                  @RequestParam(value="pageSize") int pageSize){
        return userService.selectAllUser(pageNum,pageSize);
    }

    /**
     * 批量修改用户信息
     * @param userItems
     * @return
     */
    @RequestMapping(value="/batch/update",method = RequestMethod.POST)
    public ServiceResponse<String> batchUpdateUserInfo(@RequestBody List<UserTO> userItems){

        return userService.batchAddUserTO(userItems);
    }

    /**
     * 根据用户名删除单个用户信息
     * @param userAccount
     * @return
     */
    @RequestMapping(value="/delete/userinfo",method=RequestMethod.DELETE)
    public  ServiceResponse<String> deleteUserInfo(@RequestParam("userAccount") String userAccount){
        return userService.deleteUserInfo(userAccount);
    }

    /**
     * 批量删除用户信息
     * @param userAccounts
     * @return
     */
    @RequestMapping(value="/delete/batch",method = RequestMethod.POST)
    public ServiceResponse<String> batchDeleteUserInfo(@RequestBody List<String> userAccounts){

        return userService.batchDeleteUserInfo(userAccounts);
    }


}

   3.2.2 Service层代码

           Service层接口类代码

package com.example.mybatis.service;

import com.example.mybatis.model.ServiceResponse;
import com.example.mybatis.model.UserTO;
import java.util.List;
import com.example.mybatis.model.PageInfo;

public interface IUserService {
    ServiceResponse<UserTO> queryUserInfoByAccount(String userAccount);

    ServiceResponse<String> addUserTO(UserTO userTO);

    ServiceResponse<String> updateUserTO(UserTO userTO);

    ServiceResponse<String> batchAddUserTO(List<UserTO> userTOs);

    ServiceResponse<PageInfo> selectAllUser(int pageNum,int pageSize);

    ServiceResponse<String> batchUpdateUserInfo(List<UserTO> userItems);

    ServiceResponse<String> deleteUserInfo(String userAccount);

    ServiceResponse<String> batchDeleteUserInfo(List<String> userAccounts);
}

        Service层实现类代码

package com.example.mybatis.service.impl;

import com.example.mybatis.business.IUserBusiness;
import com.example.mybatis.model.PageInfo;
import com.example.mybatis.model.ServiceResponse;
import com.example.mybatis.model.UserTO;
import com.example.mybatis.service.IUserService;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.slf4j.LoggerFactory;
import org.slf4j.Logger;

import java.util.List;


@Service
public class UserService implements IUserService {
    @Autowired
    private IUserBusiness userBusiness;

    private  static final Logger logger = LoggerFactory.getLogger(UserService.class);
    @Override
    public ServiceResponse<UserTO> queryUserInfoByAccount(String userAccount) {
        ServiceResponse<UserTO> response = new ServiceResponse<>();
        UserTO userTO = userBusiness.queryUsersByAccount(userAccount);
        response.setStatus(200);
        response.setMessage("ok");
        response.setData(userTO);
        return response;
    }

    @Override
    public ServiceResponse<String> addUserTO(UserTO userTO) {
        ServiceResponse<String> response = new ServiceResponse<>();
        try{
            userBusiness.addUserInfo(userTO);
            response.setMessage("ok");
            response.setStatus(200);
            response.setData("success");

        }catch (Exception ex){
            response.setMessage("inner service error:"+ex.getMessage());
            response.setStatus(500);
            response.setData("error");
            logger.error(ex.getMessage(),ex);
        }

        return response;
    }

    @Override
    public ServiceResponse<String> updateUserTO(UserTO userTO) {
        ServiceResponse<String> response = new ServiceResponse<>();
        try{
            userBusiness.updateUserInfo(userTO);
            response.setMessage("ok");
            response.setStatus(200);
            response.setData("success");
        }catch (Exception ex){
            response.setMessage("inner service error:"+ex.getMessage());
            response.setStatus(500);
            response.setData("error");
            logger.error(ex.getMessage(),ex);
        }

        return response;
    }

    @Override
    public ServiceResponse<String> batchAddUserTO(List<UserTO> userTOs) {
        ServiceResponse<String> response = new ServiceResponse<>();
        try{
            userBusiness.batchInsertUserInfo(userTOs);
            response.setStatus(200);
            response.setMessage("ok");
            response.setData("success");
        }catch (Exception ex){
            response.setMessage("inner service error:"+ex.getMessage());
            response.setStatus(500);
            response.setData("error");
            logger.error(ex.getMessage(),ex);
        }

        return response;
    }

    @Override
    public ServiceResponse<PageInfo> selectAllUser(int pageNum, int pageSize) {
        ServiceResponse<PageInfo> response = new ServiceResponse<>();
        PageInfo pageInfo = new PageInfo();
        pageInfo.setPageNum(pageNum);
        pageInfo.setPageSize(pageSize);
        int startIndex = (pageNum-1)*pageSize+1;
        int endIndex = pageNum*pageSize;
        pageInfo.setStartIndex(startIndex);
        int totalRows = userBusiness.queryTotalRows("userinfo");
        if(totalRows<endIndex){
            endIndex = totalRows;
        }
        pageInfo.setEndIndex(endIndex);
        pageInfo.setTotalRows(totalRows);
        List<UserTO> resultList = userBusiness.selectAllUser(startIndex,pageSize-1);
        pageInfo.setDataList(resultList);
        response.setStatus(200);
        response.setMessage("ok");
        response.setData(pageInfo);
        return response;
    }

    @Override
    public ServiceResponse<String> batchUpdateUserInfo(List<UserTO> userItems) {
        ServiceResponse<String> response = new ServiceResponse<>();
        try{
            userBusiness.batchUpdateUserInfo(userItems);
            response.setStatus(200);
            response.setMessage("ok");
            response.setData("success!");
        }catch(Exception ex){
            response.setStatus(500);
            response.setMessage("inner service error:"+ex.getMessage());
            response.setData("error");
            logger.error(ex.getMessage(),ex);

        }
        return response;
    }

    @Override
    public ServiceResponse<String> deleteUserInfo(String userAccount) {
        ServiceResponse<String> response = new ServiceResponse<>();
        try{
            userBusiness.deleteUserInfo(userAccount);
            response.setStatus(200);
            response.setMessage("ok");
            response.setData("success!");
        }catch(Exception ex){
            response.setStatus(500);
            response.setMessage("inner service error:"+ex.getMessage());
            response.setData("error");
            logger.error(ex.getMessage(),ex);
        }
        return response;
    }

    @Override
    public ServiceResponse<String> batchDeleteUserInfo(List<String> userAccounts) {
        ServiceResponse<String> response = new ServiceResponse<>();
        try{
            userBusiness.batchDeleteUserInfo(userAccounts);
            response.setStatus(200);
            response.setMessage("ok");
            response.setData("success!");
        }catch(Exception ex){
            response.setStatus(500);
            response.setMessage("inner service error:"+ex.getMessage());
            response.setData("error");
            logger.error(ex.getMessage(),ex);
        }
        return response;
    }
}

    3.2.3 Busines层代码

            Busines层接口类代码

package com.example.mybatis.business;

import com.example.mybatis.model.UserTO;
import java.util.List;

public interface IUserBusiness {
    UserTO queryUsersByAccount(String userAccount);

    void addUserInfo(UserTO userTO)throws Exception;

    void updateUserInfo(UserTO userTO)throws Exception;

    void batchInsertUserInfo(List<UserTO> items) throws Exception;

    List<UserTO> selectAllUser(int startIndex,int pageSize);

    int queryTotalRows(String dbName);

    void batchUpdateUserInfo(List<UserTO> items) throws Exception;

    void deleteUserInfo(String userAccount) throws Exception;

    void batchDeleteUserInfo(List<String> userAccounts) throws Exception;


}

        Business层实现类代码(注意实现类必须加@Component或@Named注解暴露接口，dao类的增删改操作要添加加事物注解@Transactional)

package com.example.mybatis.business.impl;

import com.example.mybatis.business.IUserBusiness;
import com.example.mybatis.dao.IUserDao;
import com.example.mybatis.model.UserTO;
import com.github.pagehelper.PageHelper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Component
public class UserBusiness implements IUserBusiness {
    @Autowired
    private IUserDao userDao;
    @Override
    public UserTO queryUsersByAccount(String userAccount){
        return userDao.queryUsersByAccount(userAccount);
    }

    @Override
    @Transactional(rollbackFor = {Exception.class},readOnly = false)
    public void addUserInfo(UserTO userTO) throws Exception {
        userDao.addUserInfo(userTO);
    }

    @Override
    @Transactional(rollbackFor = {Exception.class},readOnly = false)
    public void updateUserInfo(UserTO userTO) throws Exception {
        userDao.updateUserInfo(userTO);
    }

    @Override
    @Transactional(rollbackFor = {Exception.class},readOnly = false)
    public void batchInsertUserInfo(List<UserTO> items) throws Exception {
        userDao.batchAddUserInfo(items);
    }

    @Override
    public List<UserTO> selectAllUser(int startIndex,int pageSize) {
        //将分页参数传给PageHelper类的startPage（int pageNum,int pageSize）静态方法即可实现分页效果，非常简单
//        PageHelper.startPage(pageNum,pageSize);
        return userDao.selectAllUser(startIndex,pageSize);
    }

    @Override
    public int queryTotalRows(String dbName) {
        return userDao.queryTotalRows(dbName);
    }

    @Override
    @Transactional(rollbackFor = {Exception.class},readOnly = false)
    public void batchUpdateUserInfo(List<UserTO> items) throws Exception {
        userDao.batchUpdateUserInfo(items);
    }

    @Override
    @Transactional(rollbackFor = {Exception.class},readOnly = false)
    public void deleteUserInfo(String userAccount) throws Exception {
        userDao.deleteUserInfo(userAccount);
    }

    @Override
    @Transactional(rollbackFor = {Exception.class},readOnly = false)
    public void batchDeleteUserInfo(List<String> userAccounts) throws Exception {
        userDao.batchDeleteUserInfo(userAccounts);
    }
}

   3.2.4 Dao层代码

          dao接口类代码(有多个入参时可使用ibatis的参数绑定注解@Param)

package com.example.mybatis.dao;
import com.example.mybatis.model.UserTO;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
import java.util.List;

//import java.util.List;
//@Repository
public interface IUserDao {

    UserTO queryUsersByAccount(@Param("userAccount")String userAccount);

    List<UserTO> selectAllUser(@Param("startIndex") int startIndex,@Param("pageSize") int pageSize);

    void addUserInfo(UserTO userTO);

    void updateUserInfo(UserTO userTO);

    void batchAddUserInfo(List<UserTO> userItems);

    int queryTotalRows(@Param("dbName") String dbName);

    void batchUpdateUserInfo(List<UserTO> userItems);

    void deleteUserInfo(String userAccount);

    void batchDeleteUserInfo(List<String> userAccounts);


}

    dao接口实现类IUser.xml代码（注意该文件必须在resource目录下Spring容器才能在类路径classpath下找到）

<?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.example.mybatis.dao.IUserDao">
    <select id="queryUsersByAccount" parameterType="java.lang.String" resultType="UserTO">
            select u.id,u.user_account,u.password,u.nick_name,u.dept_no,d.dept_name,
                   u.phone_num,u.email_address,u.birth_day
            from userinfo u
            left join dept d on d.dept_no=u.dept_no
            where u.user_account=#{userAccount,jdbcType=VARCHAR}
    </select>
    <select id="selectAllUser" resultType="UserTO" >
            select a.id,a.user_account,a.password,a.nick_name,
                   a.dept_no,a.phone_num,a.email_address,a.birth_day
            from userinfo a
            where a.id>=#{startIndex,jdbcType=INTEGER} limit #{pageSize,jdbcType=INTEGER}
    </select>
    <select id="queryTotalRows" resultType="int">
        select count(1) from ${dbName}
    </select>
    <insert id="addUserInfo" parameterType="UserTO">
        insert into userinfo
            (user_account,
             password,
             phone_num,
             dept_no,
             birth_day,
             nick_name,
             email_address)
            values(
              #{userAccount,jdbcType=VARCHAR},
              #{password,jdbcType=VARCHAR},
              #{phoneNum,jdbcType=NUMERIC},
              #{deptNo,jdbcType=NUMERIC},
              #{birthDay,jdbcType=VARCHAR},
              #{nickName,jdbcType=VARCHAR},
              #{emailAddress,jdbcType=VARCHAR})
    </insert>
    <insert id="batchAddUserInfo" parameterType="java.util.List">
        insert into userinfo(
                    user_account,
                    password,
                    phone_num,
                    dept_no,
                    birth_day,
                    nick_name,
                    email_address
                 )
        <foreach collection="list"  item="item" index="index" separator="union all" >
              select
              #{item.userAccount,jdbcType=VARCHAR},
              #{item.password,jdbcType=VARCHAR},
              #{item.phoneNum,jdbcType=NUMERIC},
              #{item.deptNo,jdbcType=NUMERIC},
              #{item.birthDay,jdbcType=VARCHAR},
              #{item.nickName,jdbcType=VARCHAR},
              #{item.emailAddress,jdbcType=VARCHAR}
              from dual
        </foreach>
    </insert>

    <update id="updateUserInfo" parameterType="UserTO">
        update userinfo
        <trim prefix="set" suffixOverrides=",">
            <if test="password!=null and password!='' ">password=#{password,jdbcType=VARCHAR},</if>
            <if test="phoneNum!=null">phone_num=#{phoneNum,jdbcType=NUMERIC},</if>
            <if test="deptNo!=null">dept_no=#{deptNo,jdbcType=NUMERIC},</if>
            <if test="birthDay!=null">birth_day=#{birthDay,jdbcType=VARCHAR},</if>
            <if test="nickName!=null and nickName!='' ">nick_name=#{nickName,jdbcType=VACHAR},</if>
            <if test="emailAddress!=null and emailAddress!='' ">email_address=#{emailAddress,jdbcType=VARCHAR}</if>
        </trim>
        where user_account=#{userAccount,jdbcType=VARCHAR}
    </update>

    <update id="batchUpdateUserInfo" parameterType="java.util.List">
        <foreach collection="list" separator=";" item="item" close=";">
            update userinfo
            <trim prefix="set" suffixOverrides=",">
                <if test="password!=null and password!='' ">password=#{item.password,jdbcType=VARCHAR},</if>
                <if test="phoneNum!=null">phone_num=#{item.phoneNum,jdbcType=NUMERIC},</if>
                <if test="deptNo!=null">dept_no=#{item.deptNo,jdbcType=NUMERIC},</if>
                <if test="birthDay!=null">birth_day=#{item.birthDay,jdbcType=VARCHAR},</if>
                <if test="nickName!=null and nickName!='' ">nick_name=#{item.nickName,jdbcType=VACHAR},</if>
                <if test="emailAddress!=null and emailAddress!='' ">email_address=#{item.emailAddress,jdbcType=VARCHAR}</if>
            </trim>
            where user_account=#{item.userAccount,jdbcType=VARCHAR}
        </foreach>
    </update>

    <delete id="deleteUserInfo" parameterType="java.lang.String" >
        delete from userinfo where user_account=#{param1,jdbcType=VARCHAR}
    </delete>

    <delete id="batchDeleteUserInfo" parameterType="java.util.List">
        delete from userinfo where user_account in
        <foreach collection="list" item="item" open="（" close="）" separator=",">
            #{item,jdbcType=VARCHAR}
        </foreach>
    </delete>
</mapper>

3.3 接口测试

启动项目后利用postman对接口进行测试

   3.3.1 首先测试添加单个用户信息接口(Headers里设置Content-type: application/json, Body选择raw)：

postman测试添加单个用户信息接口

 

 

 

 

 

 

 

  接口响应信息截图如下，status:200 代表接口调用成功

添加单个用户信息接口响应信息

 

 

 

 

 

在mysql-workbench客户端query窗口中执行sql脚本：select * from userinfo, 发现数据库多了一个记录

数据库userinfo表查询结果

 

 

 

 

3.3.2 测试批量添加用户信息接口

postman测试批量添加用户信息接口

 

 

 

 

 

 

 

 

 

 

 

 

返回响应信息status:200代表接口调用成功

3.3.3 测试根据用户名查询用户信息接口

postman测试测试根据用户名查询用户信息接口

 

3.3.4 测试分页查询接口

  先利用批量插入接口添加20条左右数据，然后使用postman调用分页查询接口

postman测试分页查询接口

其他接口可使用postman一一测试，本博客不再一一截图。开发过程中，如遇到报异常，可查看具体异常信息，并通过debug模式断点调试查看具体原因，然后进行解决

参考文档

Spring boot Mybatis 整合（完整版）：https://blog.csdn.net/winter_chen001/article/details/77249029

Mybatis v3.4.6开发指南

请尊重原创作品付出的汗水，转载请注明出处！参考本片博客开发demo项目遇到难以解决的问题，欢迎给本人发邮件交流,

本人邮箱：302522375@qq.com

评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

heshengfu1211

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值