框架 Spring Boot 技术入门到整合 8-4 整合mybatis-自定义mapper的实现

0　　　　课程地址

https://www.imooc.com/video/16789

 

1　　　　课程重点

1.1　　自定义mapper释义：

其实就是多表查询或者单表查询不同的字段，需要注意的是查多少个字段就展示多少个字段的值，其他未查的字段，即使有值，也展示为空。

另外 *mapper.xml 查询的时候最好不要直接查*

 

2　　　　课程demo

2.1　　自定义mapper demo

MyBatisCRUDController

package com.example.demo.controller;

/**
 * MyBatisCRUDController
 *
 * @author 魏豆豆
 * @date 2020/12/20
 */

import com.example.demo.pojo.SysUser;
import com.example.demo.service.UserService;
import com.example.demo.tools.JSONResult;
import org.n3r.idworker.Sid;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.Date;
import java.util.List;

@RestController
@RequestMapping("mybatis")
public class MyBatisCRUDController {

    @Autowired
    private UserService userService;

    @Autowired
    private Sid sid;

    @RequestMapping("/saveUser")
    public JSONResult saveUser() throws Exception {

        String userId = sid.nextShort();

        SysUser user = new SysUser();
        user.setId(userId);
        user.setUsername("imooc" + new Date());
        user.setNickname("imooc" + new Date());
        user.setPassword("abc123");
        user.setIsDelete(0);
        user.setRegistTime(new Date());

        userService.saveUser(user);

        return JSONResult.ok("保存成功");
    }

    @RequestMapping("/updateUser")
    public JSONResult updateUser() {

        SysUser user = new SysUser();
        user.setId("10011001");
        user.setUsername("10011001-updated" + new Date());
        user.setNickname("10011001-updated" + new Date());
        user.setPassword("10011001-updated");
        user.setIsDelete(0);
        user.setRegistTime(new Date());

        userService.updateUser(user);

        return JSONResult.ok("保存成功");
    }

    @RequestMapping("/deleteUser")
    public JSONResult deleteUser(String userId) {

        userService.deleteUser(userId);

        return JSONResult.ok("删除成功");
    }

    @RequestMapping("/queryUserById")
    public JSONResult queryUserById(String userId) {

        return JSONResult.ok(userService.queryUserById(userId));
    }

    @RequestMapping("/queryUserList")
    public JSONResult queryUserList() {

        SysUser user = new SysUser();
        user.setUsername("imooc");
        user.setNickname("lee");

        List<SysUser> userList = userService.queryUserList(user);

        return JSONResult.ok(userList);
    }

    @RequestMapping("/queryUserListPaged")
    public JSONResult queryUserListPaged(Integer page) {

        if (page == null) {
            page = 1;
        }

        int pageSize = 10;

        SysUser user = new SysUser();
//        user.setNickname("lee");

        List<SysUser> userList = userService.queryUserListPaged(user, page, pageSize);

        return JSONResult.ok(userList);
    }

    @RequestMapping("/queryUserByIdCustom3")
    public JSONResult queryUserByIdCustom2(String userId) {

        return JSONResult.ok(userService.queryUserByIdCustom(userId));
    }

    /**
     * 自定义mapper
     * @return
     */
   @RequestMapping("/queryUserByCusMapper")
   public JSONResult queryUserByCusMapper(String userId){
        return JSONResult.ok(userService.queryUserByCusMapper(userId));
   }

    @RequestMapping("/saveUserTransactional")
    public JSONResult saveUserTransactional() {

        String userId = sid.nextShort();

        SysUser user = new SysUser();
        user.setId(userId);
        user.setUsername("lee" + new Date());
        user.setNickname("lee" + new Date());
        user.setPassword("abc123");
        user.setIsDelete(0);
        user.setRegistTime(new Date());

        userService.saveUserTransactional(user);

        return JSONResult.ok("保存成功");
    }
}

 

UserService

package com.example.demo.service;

/**
 * UserService
 *
 * @author 魏豆豆
 * @date 2020/12/20
 */

import com.example.demo.pojo.SysUser;

import java.util.List;


public interface UserService {

    public void saveUser(SysUser user) throws Exception;

    public void updateUser(SysUser user);

    public void deleteUser(String userId);

    public SysUser queryUserById(String userId);

    public List<SysUser> queryUserList(SysUser user);

    public List<SysUser> queryUserListPaged(SysUser user, Integer page, Integer pageSize);

    public SysUser queryUserByIdCustom(String userId);

    public SysUser queryUserByCusMapper(String userId);

    public void saveUserTransactional(SysUser user);
}

 

UserServiceImpl

package com.example.demo.service.impl;

/**
 * UserServiceImpl
 *
 * @author 魏豆豆
 * @date 2020/12/20
 */
import java.util.List;

import com.example.demo.mapper.SysUserMapper;
import com.example.demo.mapper.SysUserMapperCus;
import com.example.demo.mapper.SysUserMapperCustom;
import com.example.demo.pojo.SysUser;
import com.example.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.thymeleaf.util.StringUtils;

import com.github.pagehelper.PageHelper;

import tk.mybatis.mapper.entity.Example;

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private SysUserMapper userMapper;

    @Autowired
    private SysUserMapperCustom userMapperCustom;

    @Autowired
    private SysUserMapperCus sysUserMapperCus;

    @Override
    @Transactional(propagation = Propagation.REQUIRED)
    public void saveUser(SysUser user) throws Exception {

        try {
            Thread.sleep(4000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        //mybaties jar包中的方法
        userMapper.insert(user);
    }

    @Override
    @Transactional(propagation = Propagation.REQUIRED)
    public void updateUser(SysUser user) {
        userMapper.updateByPrimaryKeySelective(user);
//        userMapper.updateByPrimaryKey(user);
    }

    @Override
    @Transactional(propagation = Propagation.REQUIRED)
    public void deleteUser(String userId) {
        userMapper.deleteByPrimaryKey(userId);
    }

    @Override
    @Transactional(propagation = Propagation.SUPPORTS)
    public SysUser queryUserById(String userId) {

        try {
            Thread.sleep(6000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }

        return userMapper.selectByPrimaryKey(userId);
    }
    @Override
    @Transactional(propagation = Propagation.SUPPORTS)
    public List<SysUser> queryUserList(SysUser user) {

        try {
            Thread.sleep(11000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }

        Example example = new Example(SysUser.class);
        Example.Criteria criteria = example.createCriteria();

        if (!StringUtils.isEmptyOrWhitespace(user.getUsername())) {
//            criteria.andEqualTo("username", user.getUsername());
            criteria.andLike("username", "%" + user.getUsername() + "%");
        }

        if (!StringUtils.isEmptyOrWhitespace(user.getNickname())) {
            criteria.andLike("nickname", "%" + user.getNickname() + "%");
        }

        List<SysUser> userList = userMapper.selectByExample(example);

        return userList;
    }

    @Override
    @Transactional(propagation = Propagation.SUPPORTS)
    public List<SysUser> queryUserListPaged(SysUser user, Integer page, Integer pageSize) {
        // 开始分页
        PageHelper.startPage(page, pageSize);

        Example example = new Example(SysUser.class);
        Example.Criteria criteria = example.createCriteria();

        if (!StringUtils.isEmptyOrWhitespace(user.getNickname())) {
            criteria.andLike("nickname", "%" + user.getNickname() + "%");
        }
        example.orderBy("registTime").desc();
        List<SysUser> userList = userMapper.selectByExample(example);

        return userList;
    }


    @Override
    @Transactional(propagation = Propagation.SUPPORTS)
    public SysUser queryUserByIdCustom(String userId) {

        List<SysUser> userList = userMapperCustom.queryUserSimplyInfoById(userId);

        if (userList != null && !userList.isEmpty()) {
            return (SysUser)userList.get(0);
        }

        return null;
    }

    @Override
    @Transactional(propagation = Propagation.SUPPORTS)
    public SysUser queryUserByCusMapper(String userId) {

        List<SysUser> userList = sysUserMapperCus.queryUserMapperCus(userId);

        if (userList != null && !userList.isEmpty()) {
            return (SysUser)userList.get(0);
        }

        return null;
    }

    @Override
    @Transactional(propagation = Propagation.REQUIRED)
    public void saveUserTransactional(SysUser user) {

        userMapper.insert(user);

        int a = 1 / 0;

        user.setIsDelete(1);
        userMapper.updateByPrimaryKeySelective(user);
    }
}

 

SysUserMapperCustom

package com.example.demo.mapper;

import com.example.demo.pojo.SysUser;

import java.util.List;

/**
 * SysUserMapperCustom
 *
 * @author 魏豆豆
 * @date 2020/12/22
 */
public interface SysUserMapperCustom {

    List<SysUser> queryUserSimplyInfoById(String id);
}

 

 

SysUserMapperCus

<?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.demo.mapper.SysUserMapperCus" >
  <!-- 查询简单的用户信息 select id 应为类中具体的方法做的映射-->
  <select id="queryUserMapperCus" resultType="com.example.demo.pojo.SysUser"
          parameterType="java.lang.String" >
    <!-- 通常查什么字段，用什么字段，不要用*-->
    select
    nickname,
    password
    from
    sys_user
    where
    id = #{id,jdbcType=VARCHAR}
  </select>
</mapper>

 

测试结果：