SQL映射文件详情

MyBatis框架的条件查询

1.实现单一条件查询

根据用户的真实姓名模糊匹配查询用户信息

（1）用户表实体类

public class TSysUser implements Serializable {
private static final long serialVersionUID = 42L;
  private int id;
  private String account;
  private String realName;
  private String password;
  private int sex;
  private Date birthday;
  private String phone;
  private String address;
  private long roleId;
  private long createdUserId;
  private Date createdTime;
  private long updatedUserId;
  private Date updatedTime;
  //第二单元示例11
  //private String userRoleName; //角色名称
  private SysRole sysRole; //系统角色实体类
  //第二单元示例18
  private List<Address>addressList; //用户地址列表

  public int getId() {
    return id;
  }

  public void setId(int id) {
    this.id = id;
  }

  public String getAccount() {
    return account;
  }

  public void setAccount(String account) {
    this.account = account;
  }

  public String getRealName() {
    return realName;
  }

  public void setRealName(String realName) {
    this.realName = realName;
  }

  public String getPassword() {
    return password;
  }

  public void setPassword(String password) {
    this.password = password;
  }

  public int getSex() {
    return sex;
  }

  public void setSex(int sex) {
    this.sex = sex;
  }

  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 long getRoleId() {
    return roleId;
  }

  public void setRoleId(long roleId) {
    this.roleId = roleId;
  }

  public long getCreatedUserId() {
    return createdUserId;
  }

  public void setCreatedUserId(long createdUserId) {
    this.createdUserId = createdUserId;
  }

  public Date getCreatedTime() {
    return createdTime;
  }

  public void setCreatedTime(Date createdTime) {
    this.createdTime = createdTime;
  }

  public long getUpdatedUserId() {
    return updatedUserId;
  }

  public void setUpdatedUserId(long updatedUserId) {
    this.updatedUserId = updatedUserId;
  }

  public Date getUpdatedTime() {
    return updatedTime;
  }

  public void setUpdatedTime(Date updatedTime) {
    this.updatedTime = updatedTime;
  }

  public SysRole getSysRole() {
    return sysRole;
  }

  public void setSysRole(SysRole sysRole) {
    this.sysRole = sysRole;
  }

  public List<Address> getAddressList() {
    return addressList;
  }

  public void setAddressList(List<Address> addressList) {
    this.addressList = addressList;
  }

（2）在SysUserMapper接口中添加查询方法

/**
 * 根据用户真实姓名模糊匹配查询用户
 * @param realName
 * @return
 */
public List<TSysUser> getUsersByRealName(String realName);

（3）在SysUserMapper.xml中添加SQL语句映射

<select id="getUsersByRealName" resultType="TSysUser" parameterType="string">
    select * from t_sys_user
             where realName like CONCAT('%',#{param},'%')
</select>

（4）测试

@Test
 //第二单元示例1
void getUsersByRealName() {
    List<TSysUser> tSysUsers=sqlSession.getMapper(SysUserMapper.class).getUsersByRealName("李");
    if (tSysUsers!=null){
        for (TSysUser tSysUser:tSysUsers){
            logger.debug("查询到的用户列表：id="+tSysUser.getId()+"realName="+tSysUser.getRealName()+"password="+tSysUser.getPassword());
        }
    }
}

---

2.多条件查询

（1）将查询条件封装成Java对象作为入参

（2）将查询条件封装成Map对象作为入参

（3）使用@Param注解实现多参数入参

3.MyBatis架构的结果映射

（1）使用resultMap元素自定义结果映射

（2）嵌套结果映射

1.association元素

2.collection元素

4.MyBatis框架的增、删、改操作

（1）执行insert语句

/**
 * 添加用户
 * @param user
 * @return int 影响的行数
 */
public int add(TSysUser user);

在SysUserMapper.xml中使用insert元素映射插入语句

<insert id="add" parameterType="TSysUser">
    insert into t_sys_user(account,realName,password,sex,birthday,
                           phone,address,roleId,createdUserId,createdTime)
    values (#{account},#{realName},#{password},#{sex},#{birthday},
            #{phone},#{address},#{roleId},#{createdUserId},#{createdTime})
</insert>

测试类

 @Test
public void add() {
     logger.debug("testAdd!===========================");
     int count=0;
     try {
         sqlSession=MyBatisUtil.createSqlSession();
         TSysUser user=new TSysUser();
         user.setAccount("test001");
         user.setRealName("测试用户001");
         user.setPassword("1234567");
         Date birthday=new SimpleDateFormat("yyyy-MM-dd").parse("yyyy-MM-dd");
         user.setBirthday(birthday);
         user.setAddress("测试地址abc");
         user.setSex(1);
         user.setPhone("13411110000");
         user.setRoleId(1);
         user.setCreatedUserId(1);
         user.setCreatedTime(new Date());
         count=sqlSession.getMapper(SysUserMapper.class).add(user);
         //int i=2/0
         sqlSession.commit();
     }catch (Exception e){
         sqlSession.rollback();
         count=0;
     }finally {
         MyBatisUtil.closeSqlSession(sqlSession);
     }
     logger.debug("testAdd count:"+count);
 }

（2）执行update语句

/**
 * 修改用户
 * @param user
 * @return int 影响的行数
 */
public int modify(TSysUser user);

在SysUserMapper.xml中使用update元素映射插入语句

<update id="modify" parameterType="TSysUser">
    update t_sys_user set account=#{account},realName=#{realName},
                          password=#{password},sex=#{sex},phone=#{phone},address=#{address},
                          birthday=#{birthday},roleId=#{roleId},updatedUserId=#{updatedUserId},
                          updatedTime=#{updatedTime}
    where id=#{id}
</update>

测试类

@Test
void updatePwd() {
    logger.debug("updatePwd!===========================");
    String pwd="88888888";
    Integer id=17;
    int count=0;
    try {
        sqlSession=MyBatisUtil.createSqlSession();
        count=sqlSession.getMapper(SysUserMapper.class).updatePwd(id,pwd);
        sqlSession.commit();
    }catch (Exception e){
        e.printStackTrace();
        sqlSession.rollback();
        count=0;
    }finally {
        MyBatisUtil.closeSqlSession(sqlSession);
    }
    logger.debug("updatePwd count:"+count);
}

（3）执行delete语句

/**
 * 删除用户
 *
 * @param id 要删除的用户的id
 * @return
 */
public int deleteUserById(@Param("id")Integer id);

在SysUserMapper.xml中使用delete元素映射插入语句

<delete id="deleteUserById" parameterType="TSysUser">
    delete from t_sys_user where id=#{id}
</delete>

（4）二级缓存的使用方法

1.在MyBatis框架的核心配置文件中设置全局开启二级缓存

2.即使全局开启了二级缓存，默认情况下也是不使用二级缓存的。可以根据需要再SQL映射文件中配置缓存，为当前namespace启用二级缓存