基于注解的mybatis的一些常见用法

1.数据准备

User实体类 省略get、set

public class User implements Serializable {
  /** 用户id */
  private Integer userId;
  /** 用户姓名 */
  private String name;
}

Hobby实体类

public class Hobby implements Serializable {
  /** 爱好id */
  private Integer hobbyId;
  /** 爱好名称 */
  private String name;
}

User和Hobby是多对多的关系需要用到中间表
user_hobby中间表

user_id	hobby_id
用户id	爱好id

数据库sql

# user表
CREATE TABLE `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
# hobby表
CREATE TABLE `hobby` (
  `hobby_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`hobby_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
# user_hobby中间表
CREATE TABLE `user_hobby` (
  `user_id` int(11) NOT NULL,
  `hobby_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 数据
INSERT INTO `user` (name) VALUES ('赵甲'),('钱乙'),('孙丙');
INSERT INTO hobby (name) VALUES ('读书'),('跑步'),('音乐'),('发呆'),('睡觉');
INSERT INTO test.user_hobby (user_id,hobby_id) VALUES (1,1),(1,3),(1,5),(2,1),(2,2),(3,4),(3,5);

执行查询

SELECT user.*,hobby.`name` 爱好 from user,user_hobby,hobby where user.user_id = user_hobby.user_id and user_hobby.hobby_id = hobby.hobby_id

查询结果

2.代码

主启动类添加@EnableTransactionManagement注解开启事务
controller

@RestController
@Slf4j
public class testController {
	@Autowired
    UserService userService;
    
    @RequestMapping("/addUser")
    public String addUser(@RequestParam("name") String name) {
        log.info("接收到的参数是" + name);
        User user = new User(null, name);
        if (userService.addUser(user) == 1) {
            // 添加成功
            log.info("添加成功 user的id是:" + user.getUserId());
            return "添加成功";
        } else {
            log.info("添加失败");
            return "添加失败";
        }
    }

	@RequestMapping("/findUserByNameAndHobby")
    public String findUserByNameAndHobby(@RequestParam(value = "name", required = false) String name,
                                         @RequestParam(value = "hobbyId", required = false) Integer hobbyId) {
        List<User> userList = userService.findUserByNameAndHobby(name, hobbyId);
        for (User user : userList) {
            log.info("user = " + user);
        }
        return "success";
    }

    @RequestMapping("/delUser")
    public String delUser(@RequestParam("userId") Integer userId) {
        if (userService.delUser(userId) == 0) {
            return "删除失败";
        } else {
            return "删除成功";
        }
    }
}

service

public interface UserService {
    Integer addUser(User user);

    List<User> findUserByNameAndHobby(String name,Integer hobbyId);

    Integer delUser(Integer userId);
}

servicesImpl

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserDao userDao;

    @Override
    public List<User> findAll() {
        return userDao.findAll();
    }

    @Override
    public Integer addUser(User user) {
        return userDao.addUser(user);
    }

    @Override
    public List<User> findUserByNameAndHobby(String name, Integer hobbyId) {
        return userDao.findUserByNameAndHobby(name, hobbyId);
    }

    @Override
    @Transactional(rollbackFor = {RuntimeException.class, Error.class})
    public Integer delUser(Integer userId) {
        try {
            if (userDao.delUserHobbyMiddle(userId) > 0) {
            	// 手动出现 除0异常
                int a = 1/0;
                // 删除中间表数据成功
                if (userDao.delUser(userId) == 1) {
                    // 删除用户成功
                    return 1;
                } else {
                    // 删除用户失败 回滚
                    throw new RuntimeException("删除用户失败");
                }
            } else {
                throw new RuntimeException("删除用户爱好中间表数据失败");
            }
        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        }
        return 0;

    }
}

userDao

@Mapper
public interface UserDao {

    @Select("select * from user")
    List<User> findAll();

    @Insert("insert into user(name) values (#{name})")
    @Options(useGeneratedKeys = true, keyProperty = "userId", keyColumn = "user_id")
    Integer addUser(User user);

    @Delete("delete from user where user_id = #{userId}")
    Integer delUser(Integer userId);

    @Select(value = {
            "<script>" +
                    "select user.* from user " +
                    "left join user_hobby on user.user_id = user_hobby.user_id " +
                    "left join hobby on user_hobby.hobby_id = hobby.hobby_id" +
                    "<where> 1 = 1 " +
                    "<if test = \"name != null \"> and user.name like concat('%',#{name},'%') </if>" +
                    "<if test = \"hobbyId != null \"> and user_hobby.hobby_id = #{hobbyId} </if>" +
                    "</where>" +
                    "</script>"
    })
    List<User> findUserByNameAndHobby(@Param("name") String name, @Param("hobbyId") Integer hobbyId);

    @Delete("delete from user_hobby where user_id = #{userId}")
    Integer delUserHobbyMiddle(@Param("userId") Integer userId);
}

3.测试

3.1新增用户的时候返回数据的id

新增前的数据

测试请求

反馈信息

log信息

新增后的数据

3.2删除用户事务控制

删除前的数据

测试请求

反馈

删除后的数据

因为在service层出现异常然后回滚，所以并没有对db做实际操作。

3.3动态sql

在dao层使用script进行动态的拼接条件
测试：

测试结果：

查询名字里包含乙 并且爱好中有id为2的用户

总结

列举了一些工作中常用的mybatis的用法。