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>
测试结果: