Mybatis映射进阶实战

public Integer addUser(User user);


 
      insert into smbms_user (userCode,userName,userPassword,gender,birthday,phone,
								address,userRole,createdBy,creationDate)
				values (#{userCode},#{userName},#{userPassword},#{gender},#{birthday},#{phone},
				#{address},#{userRole},#{createdBy},#{creationDate})


@Test
    public void testaddUser() throws IOException {
        SqlSession sqlSession = MyBatisUtil.createSqlSession();
        User user = new User();
        user.setUserCode("test001");
        user.setUserName("测试用户001");
        user.setUserPassword("1234567");
        user.setGender(1);
        user.setBirthday("2011-01-01");
        user.setPhone("13688783697");
        user.setAddress("地址测试");
        user.setUserRole(1);
        user.setCreatedBy(1);
        user.setCreationDate("2022-03-04");
        user.setModifyBy(33);
        user.setModifyDate("2029-01-03");
        int flag = sqlSession.getMapper(UserMapper.class).addUser(user);
        if(flag>0){
            System.out.println("添加成功");
        }
        sqlSession.commit();
        sqlSession.close();
    }

public Integer updateUser(User user);



        update smbms_user set userCode = #{userCode},userName= #{userName},userPassword= #{userPassword},gender= #{gender},birthday= #{birthday},phone= #{phone},
								address= #{address},userRole= #{userRole},createdBy= #{createdBy},creationDate= #{creationDate}
								where id = #{id}
    
    
@Test
    public void testupdateUser() throws IOException {
        SqlSession sqlSession = MyBatisUtil.createSqlSession();
        User user = new User();
        user.setId(1);
        user.setUserCode("test002");
        user.setUserName("测试用户001");
        user.setUserPassword("1234567");
        user.setGender(1);
        user.setBirthday("2011-01-01");
        user.setPhone("13688783697");
        user.setAddress("地址测试");
        user.setUserRole(1);
        user.setCreatedBy(1);
        user.setCreationDate("2022-03-04");
        user.setModifyBy(33);
        user.setModifyDate("2029-01-03");
        int flag = sqlSession.getMapper(UserMapper.class).updateUser(user);
        if(flag>0){
            System.out.println("添加成功");
        }
        sqlSession.commit();
        sqlSession.close();
    }    

核心思想：超过三个参数建议封装成对象，两个参数建议使用@Param

public Integer updatePwd(@Param("userPassword")String userPassword,@Param("id")Integer id);


 
		  update smbms_user set userPassword = #{userPassword}
		  where id = #{id}
	
	
@Test
    public void testupdatePwd() throws IOException {
        SqlSession sqlSession = MyBatisUtil.createSqlSession();
//        String userPassword,@Param("id")Integer id
        String userPassword = "9999";
        Integer id = 1;
        int flag = sqlSession.getMapper(UserMapper.class).updatePwd(userPassword,id);
        if(flag>0){
            System.out.println("添加成功");
        }
        sqlSession.commit();
        sqlSession.close();
    }	

public Integer delById(Integer id);

  
      delete from smbms_user where id = #{id}
    

  @Test
    public void testdelById() throws IOException {
        SqlSession sqlSession = MyBatisUtil.createSqlSession();
        Integer id = 1;
        int flag = sqlSession.getMapper(UserMapper.class).delById(id);
        if(flag>0){
            System.out.println("删除成功");
        }
        sqlSession.commit();
        sqlSession.close();
    }

核心思想

id
一般对应数据库中该行的主键id，设置此项可提高MyBatis性能

result
映射到JavaBean的某个“简单类型”属性

association
映射到JavaBean的某个“复杂类型”属性，比如JavaBean类

collection
映射到JavaBean的某个“复杂类型”属性，比如集合

A.核心思想：

association
复杂的类型关联，一对一
内部嵌套
映射一个嵌套JavaBean属性
属性
property：映射数据库列的实体对象的属性
javaType：完整Java类名或者别名
resultMap：引用外部resultMap
子元素
id
result
property：映射数据库列的实体对象的属性
column：数据库列名或者别名

B.实战：

Role

package cn.smbms.pojo;

import java.util.Date;

public class Role {
	
	private Integer id;   //id
	private String roleCode; //角色编码
	private String roleName; //角色名称
	private Integer createdBy; //创建者
	private Date creationDate; //创建时间
	private Integer modifyBy; //更新者
	private Date modifyDate;//更新时间
	
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getRoleCode() {
		return roleCode;
	}
	public void setRoleCode(String roleCode) {
		this.roleCode = roleCode;
	}
	public String getRoleName() {
		return roleName;
	}
	public void setRoleName(String roleName) {
		this.roleName = roleName;
	}
	public Integer getCreatedBy() {
		return createdBy;
	}
	public void setCreatedBy(Integer createdBy) {
		this.createdBy = createdBy;
	}
	public Date getCreationDate() {
		return creationDate;
	}
	public void setCreationDate(Date creationDate) {
		this.creationDate = creationDate;
	}
	public Integer getModifyBy() {
		return modifyBy;
	}
	public void setModifyBy(Integer modifyBy) {
		this.modifyBy = modifyBy;
	}
	public Date getModifyDate() {
		return modifyDate;
	}
	public void setModifyDate(Date modifyDate) {
		this.modifyDate = modifyDate;
	}
	
}

User

package cn.smbms.pojo;

import java.util.Date;

public class User {
	private Integer id; //id 
	private String userCode; //用户编码
	private String userName; //用户名称
	private String userPassword; //用户密码
	private Integer gender;  //性别
	private Date birthday;  //出生日期
	private String phone;   //电话
	private String address; //地址
	private Integer userRole;    //用户角色ID
	private Integer createdBy;   //创建者
	private Date creationDate; //创建时间
	private Integer modifyBy;     //更新者
	private Date modifyDate;   //更新时间
	
	private Integer age;//年龄
	//private String userRoleName; //用户角色名称
	
	//association
	private Role role; //用户角色
	
	
	public Role getRole() {
		return role;
	}
	public void setRole(Role role) {
		this.role = role;
	}

	public Integer getAge() {
		
		Date date = new Date();
		Integer age = date.getYear()-birthday.getYear();
		return age;
	}
	

	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getUserCode() {
		return userCode;
	}
	public void setUserCode(String userCode) {
		this.userCode = userCode;
	}
	public String getUserName() {
		return userName;
	}
	public void setUserName(String userName) {
		this.userName = userName;
	}
	public String getUserPassword() {
		return userPassword;
	}
	public void setUserPassword(String userPassword) {
		this.userPassword = userPassword;
	}
	public Integer getGender() {
		return gender;
	}
	public void setGender(Integer gender) {
		this.gender = gender;
	}
	public Date getBirthday() {
		return birthday;
	}
	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}
	public String getPhone() {
		return phone;
	}
	public void setPhone(String phone) {
		this.phone = phone;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public Integer getUserRole() {
		return userRole;
	}
	public void setUserRole(Integer userRole) {
		this.userRole = userRole;
	}
	public Integer getCreatedBy() {
		return createdBy;
	}
	public void setCreatedBy(Integer createdBy) {
		this.createdBy = createdBy;
	}
	public Date getCreationDate() {
		return creationDate;
	}
	public void setCreationDate(Date creationDate) {
		this.creationDate = creationDate;
	}
	public Integer getModifyBy() {
		return modifyBy;
	}
	public void setModifyBy(Integer modifyBy) {
		this.modifyBy = modifyBy;
	}
	public Date getModifyDate() {
		return modifyDate;
	}
	public void setModifyDate(Date modifyDate) {
		this.modifyDate = modifyDate;
	}
}

UserMapper

	public List getUserListByRoleId(@Param("userRole")Integer roleId);
	


	
		select u.*,r.id as r_id,r.roleCode,r.roleName from smbms_user u,smbms_role r 
				where u.userRole = #{userRole} and u.userRole = r.id
	
	
		
		
		
		
		
			
			
			
		 
	 
(2)获取指定用户的相关信息及其地址列表-collection 
A核心思想：collection
 复杂类型集合，一对多
 内部嵌套
 映射一个嵌套结果集到一个列表
 属性
 property：映射数据库列的实体对象的属性
 ofType：完整Java类名或者别名(集合所包括的类型)
 resultMap：引用外部resultMap
 子元素
 id
 result
 property：映射数据库列的实体对象的属性
 column：数据库列名或者别名
 
B实战:
 
1.Address
 
package cn.smbms.pojo;

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

public class User {
	private Integer id; //id 
	private String userCode; //用户编码
	private String userName; //用户名称
	private String userPassword; //用户密码
	private Integer gender;  //性别
	private Date birthday;  //出生日期
	private String phone;   //电话
	private String address; //地址
	private Integer userRole;    //用户角色ID
	private Integer createdBy;   //创建者
	private Date creationDate; //创建时间
	private Integer modifyBy;     //更新者
	private Date modifyDate;   //更新时间
	
	private Integer age;//年龄
	//private String userRoleName; //用户角色名称
	
	//association
	private Role role; //用户角色
	
	//collection
	private List addressList;//用户地址列表
	
	public List getAddressList() {
		return addressList;
	}
	public void setAddressList(List addressList) {
		this.addressList = addressList;
	}
	public Role getRole() {
		return role;
	}
	public void setRole(Role role) {
		this.role = role;
	}

	public Integer getAge() {
		
		Date date = new Date();
		Integer age = date.getYear()-birthday.getYear();
		return age;
	}
	

	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getUserCode() {
		return userCode;
	}
	public void setUserCode(String userCode) {
		this.userCode = userCode;
	}
	public String getUserName() {
		return userName;
	}
	public void setUserName(String userName) {
		this.userName = userName;
	}
	public String getUserPassword() {
		return userPassword;
	}
	public void setUserPassword(String userPassword) {
		this.userPassword = userPassword;
	}
	public Integer getGender() {
		return gender;
	}
	public void setGender(Integer gender) {
		this.gender = gender;
	}
	public Date getBirthday() {
		return birthday;
	}
	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}
	public String getPhone() {
		return phone;
	}
	public void setPhone(String phone) {
		this.phone = phone;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public Integer getUserRole() {
		return userRole;
	}
	public void setUserRole(Integer userRole) {
		this.userRole = userRole;
	}
	public Integer getCreatedBy() {
		return createdBy;
	}
	public void setCreatedBy(Integer createdBy) {
		this.createdBy = createdBy;
	}
	public Date getCreationDate() {
		return creationDate;
	}
	public void setCreationDate(Date creationDate) {
		this.creationDate = creationDate;
	}
	public Integer getModifyBy() {
		return modifyBy;
	}
	public void setModifyBy(Integer modifyBy) {
		this.modifyBy = modifyBy;
	}
	public Date getModifyDate() {
		return modifyDate;
	}
	public void setModifyDate(Date modifyDate) {
		this.modifyDate = modifyDate;
	}
}
 
2.mapper,xml
 
 

     public List getAddressListByUserId(@Param("id")Integer userId);
     
 

         select u.*,a.id as a_id,a.contact,a.addressDesc,a.postCode,a.tel
                 from smbms_user u,smbms_address a where u.id = a.userId and u.id=#{id}
     
 

  
 
	public List getAddressListByUserId(@Param("id")Integer userId);
	


		select u.*,a.id as a_id,a.contact,a.addressDesc,a.postCode,a.tel
				from smbms_user u,smbms_address a where u.id = a.userId and u.id=#{id}