以下是《mybatis从入门到精通》中的学习笔记
数据库表设计
数据库数据
package com.mybatis.chapter3;
import java.util.Date;
public class SysRole {
private Long id;
private String role_Name;
private Long enabled;
private Long create_by;
private Date create_time;
public Long getEnabled() {
return enabled;
}
public Long getId() {
return id;
}
public Date getCreate_time() {
return create_time;
}
public Long getCreate_by() {
return create_by;
}
public String getRole_Name() {
return role_Name;
}
public void setEnabled(Long enabled) {
this.enabled = enabled;
}
public void setId(Long id) {
this.id = id;
}
public void setCreate_time(Date create_time) {
this.create_time = create_time;
}
public void setCreate_by(Long create_by) {
this.create_by = create_by;
}
public void setRole_Name(String role_Name) {
this.role_Name = role_Name;
}
}
package com.mybatis.chapter3;
import java.util.Date;
public class SysUser {
private Long id;
private String userName;
private String userPassword;
private String userEmail;
private String userInfo;
private byte[] headImg;
private Date create_time;
private SysRole role;
public SysRole getRole() {
return role;
}
public Long getId() {
return id;
}
public byte[] getHeadImg() {
return headImg;
}
public Date getCreate_time() {
return create_time;
}
public String getUserEmail() {
return userEmail;
}
public String getUserInfo() {
return userInfo;
}
public String getUserName() {
return userName;
}
public String getUserPassword() {
return userPassword;
}
public void setId(Long id) {
this.id = id;
}
public void setCreate_time(Date create_time) {
this.create_time = create_time;
}
public void setHeadImg(byte[] headImg) {
this.headImg = headImg;
}
public void setUserEmail(String userEmail) {
this.userEmail = userEmail;
}
public void setUserInfo(String userInfo) {
this.userInfo = userInfo;
}
public void setRole(SysRole role) {
this.role = role;
}
public void setUserName(String userName) {
this.userName = userName;
}
public void setUserPassword(String userPassword) {
this.userPassword = userPassword;
}
}
package com.mybatis.chapter3;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.Date;
import java.util.List;
@RestController
public class UserController {
@Autowired
UserService userService;
@GetMapping("/selectByUser")
public List<SysUser> selectByUser()
{
SysUser sysUser = new SysUser();
sysUser.setUserName("ad");
sysUser.setUserEmail("test@mybatis.tk");
return userService.selectByUser(sysUser);
}
@GetMapping("/selectByUserWithTagWhere")
public List<SysUser> selectByUserWithTagWhere()
{
SysUser sysUser = new SysUser();
sysUser.setUserName("ad");
sysUser.setUserEmail("test@mybatis.tk");
return userService.selectByUserWithTagWhere(sysUser);
}
@GetMapping("/selectByUserWithTagChoose")
public SysUser selectByUserWithTagChoose()
{
SysUser sysUser = new SysUser();
sysUser.setUserName("admin");
return userService.selectByUserWithTagChoose(sysUser);
}
@GetMapping("/updateByIdSelective")
public int updateByIdSelective()
{
SysUser sysUser = new SysUser();
sysUser.setId(3L);
sysUser.setUserEmail("15695203200@163.com");
userService.updateByIdSelective(sysUser);
return 1;
}
@GetMapping("/updateByIdSelectiveWithTagSet")
public int updateByIdSelectiveWithTagSet()
{
SysUser sysUser = new SysUser();
sysUser.setId(4L);
sysUser.setUserEmail("1419561484@qq.com");
userService.updateByIdSelectiveWithTagSet(sysUser);
return 1;
}
@GetMapping("/insert2")
public int insert2()
{
SysUser sysUser = new SysUser();
sysUser.setUserEmail("1419561484@qq.com");
sysUser.setUserName("xxx");
sysUser.setUserPassword("xaxa");
userService.insert2(sysUser);
return 1;
}
}
package com.mybatis.chapter3;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface UserMapper {
//最简单的查询
List<SysUser> selectByUser(SysUser sysUser);
//使用where标签查询
List<SysUser> selectByUserWithTagWhere(SysUser sysUser);
//使用choose标签查询
SysUser selectByUserWithTagChoose(SysUser sysUser);
//最简单的修改
int updateByIdSelective(SysUser sysUser);
//使用set标签修改
int updateByIdSelectiveWithTagSet(SysUser sysUser);
//使用resttype进行高级结果映射<一对一>
SysUser selectUserAndRoleById(Long id);
int insert2(SysUser sysUser);
SysUser userRoleMap(Long id);
//使用restset进行高级结果映射<一对一>
SysUser selectUserAndRoleById2(Long id);
}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatis.chapter3.UserMapper">
<select id="selectByUser" parameterType="com.mybatis.chapter3.SysUser" resultType="com.mybatis.chapter3.SysUser">
select id,
user_name userName,
user_password userPassword,
user_email userMail,
user_info userInfo,
head_img headImg,
create_time createTime
from sys_user
<!--由于两个条件都是动态的,所以如果没有1+1这个默认条件,当两个id判断都不满足时,最后生成的sql会以where结束-->
where 1=1
<!--if标签有一个必填的属性test,test 的属性值是一个符合OGNL要求的判断表达式,表达式的结果可以是true/false-->
<if test="userName != null and userName !=''">
and user_name like concat('%',#{userName},'%')
</if>
<if test="userEmail != null and userEmail !=''">
and user_email = #{userEmail}
</if>
</select>
<select id="selectByUserWithTagWhere" resultType="com.mybatis.chapter3.SysUser">
select id,
user_name userName,
user_password userPassword,
user_email userMail,
user_info userInfo,
head_img headImg,
create_time createTime
from sys_user
<!--where标签的作用:如果该标签包含的元素中有返回值,就插入一个where;如果where后面的
字符串是以AND和OR开头的,就将他们剔除-->
<where>
<if test="userName != null and userName!=''">
and user_name like concat('%',#{userName},'%')
</if>
<if test="userEmail != null and userEmail !=''">
and user_email = #{userEmail}
</if>
</where>
</select>
<select id="selectByUserWithTagChoose" resultType="com.mybatis.chapter3.SysUser">
select id,
user_name userName,
user_password userPassword,
user_email userEmail,
user_info userInfo,
head_img headImg,
create_time createTime
from sys_user
<!--由于两个条件都是动态的,所以如果没有1+1这个默认条件,当两个id判断都不满足时,最后生成的sql会以where结束-->
where 1=1
<!--1个choose中至少有1个when,有0个/1个otherwise-->
<choose>
<when test="id!=null">
and id = #{id}
</when>
<when test="userName != null and userName!=''">
and user_name =#{userName}
</when>
<otherwise>
and 1=2
</otherwise>
</choose>
</select>
<update id="updateByIdSelective">
update sys_user
set
<if test="userName!=null and userName!=''">
user_name=#{userName},
</if>
<if test="userPassword!=null and userPassword!=''">
user_password=#{userPassword},
</if>
<if test="userEmail!=null and userEmail!=''">
user_email=#{userEmail},
</if>
<if test="userInfo!=null and userInfo!=''">
user_info=#{userInfo},
</if>
<if test="headImg!=null and headImg!=''">
head_img=#{headImg},
</if>
<if test="create_time!=null and create_time!=''">
create_time=#{create_time,jdbcType=TIMESTAMP},
</if>
id=#{id}
where id=#{id}
</update>
<update id="updateByIdSelectiveWithTagSet">
update sys_user
<!--set标签的作用:如果该标签包含的元素中有返回值,就插入一个set;如果set后面的字符串是以逗号结尾的,
就将这个逗号剔除-->
<set>
<if test="userName!=null and userName!=''">
user_name=#{userName},
</if>
<if test="userPassword!=null and userPassword!=''">
user_password=#{userPassword},
</if>
<if test="userEmail!=null and userEmail!=''">
user_email=#{userEmail},
</if>
<if test="userInfo!=null and userInfo!=''">
user_info=#{userInfo},
</if>
<if test="headImg!=null and headImg!=''">
head_img=#{headImg},
</if>
<if test="create_time!=null and create_time!=''">
create_time=#{create_time,jdbcType=TIMESTAMP},
</if>
id=#{id}
</set>
where id=#{id}
</update>
<insert id="insert2" useGeneratedKeys="true" keyProperty="id">
<!--使用insert中需要注意,若在列的部分增加if条件,则values的部分也需要增加相同的if条件,
必须保证上下可以互相对应,完全匹配-->
insert into sys_user(
user_name,user_password,
<if test="userEmail != null and userEmail != ''">
user_email,
</if>
user_info,head_img,create_time)
values (
#{userName},#{userPassword},
<if test="userEmail != null and userEmail != ''">
#{userEmail},
</if>
#{userInfo},#{headImg,jdbcType=BLOB},
#{create_time,jdbcType=TIMESTAMP}
)
</insert>
</mapper>
package com.mybatis.chapter3;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserService {
@Autowired
UserMapper userMapper;
public List<SysUser> selectByUser(SysUser sysUser)
{
return userMapper.selectByUser(sysUser);
}
public List<SysUser> selectByUserWithTagWhere(SysUser sysUser)
{
return userMapper.selectByUserWithTagWhere(sysUser);
}
public int updateByIdSelective(SysUser sysUser)
{
return userMapper.updateByIdSelective(sysUser);
}
public int updateByIdSelectiveWithTagSet(SysUser sysUser)
{
return userMapper.updateByIdSelectiveWithTagSet(sysUser);
}
public int insert2(SysUser sysUser)
{
return userMapper.insert2(sysUser);
}
public SysUser selectByUserWithTagChoose(SysUser sysUser)
{
return userMapper.selectByUserWithTagChoose(sysUser);
}
public SysUser selectUserAndRoleById(Long id)
{
return userMapper.selectUserAndRoleById(id);
}
public SysUser userRoleMap(Long id)
{
return userMapper.userRoleMap(id);
}
public SysUser selectUserAndRoleById2(Long id)
{
return userMapper.selectUserAndRoleById2(id);
}
}
spring.datasource.data = com.alibaba.druid.pool.DruidDataSource
spring.datasource.url = jdbc:mysql:///user?serverTimezone=GMT%2B8
spring.datasource.username = root
spring.datasource.password = *******
server.port=8181