MyBatis : Mapper 接口以及 Example 使用实例

mapper接口中的常用方法解析

查询

• selectByPrimaryKey()

  User user = XxxMapper.selectByPrimaryKey(100); 
  

  select * 
  from user 
  where id = 100
  

• selectByExample()

  UserExample example = new UserExample();
  
  Criteria criteria = example.createCriteria();
  criteria.andUsernameEqualTo("wyw");
  criteria.andUsernameIsNull();
  example.setOrderByClause("username asc,email desc");
  
  List<?>list = XxxMapper.selectByExample(example);
  

  select * 
  from user 
  where username = 'wyw'
  	and  username is null 
  order by username asc,email desc
  

• countByExample()

  UserExample example = new UserExample();
  Criteria criteria = example.createCriteria();
  criteria.andUsernameEqualTo("wyw");
  int count = XxxMapper.countByExample(example);
  

  select count(*) 
  from user 
  where username='wyw'
  

插入

• insert()

  User user = new User();
  user.setId("dsfgsdfgdsfgds");
  user.setUsername("admin");
  user.setPassword("admin")
  user.setEmail("wyw@163.com");
  XxxMapper.insert(user);
  

  insert into user(ID,username,password,email) 
  values ('dsfgsdfgdsfgds','admin','admin','wyw@126.com');
  

更新

• updateByPrimaryKey()

  User user =new User();
  user.setId("dsfgsdfgdsfgds");
  user.setUsername("wyw");
  user.setPassword("wyw");
  user.setEmail("wyw@163.com");
  XxxMapper.updateByPrimaryKey(user);
  

  update user 
  set username='wyw', password='wyw', email='wyw@163.com' 
  where id='dsfgsdfgdsfgds'
  

• updateByPrimaryKeySelective()

  User user = new User();
  user.setId("dsfgsdfgdsfgds");
  user.setPassword("wyw");
  XxxMapper.updateByPrimaryKey(user);
  

  update user 
  set password='wyw' 
  where id='dsfgsdfgdsfgds'
  

  • updateByPrimaryKey对你注入的字段全部更新

  <update id="updateByPrimaryKeySelective"parameterType="com.taotao.pojo.TbItem">
  	update tb_item
  <set>
  <if test="title != null">
  	title = #{title,jdbcType=VARCHAR},
  </if>
  </set>
  	where id = #{id,jdbcType=BIGINT}
  </update>
  

  • updateByPrimaryKeySelective会对字段进行判断再更新(如果为Null就忽略更新)，如果你只想更新某一字段，可以用这个方法。

    <update id="updateByPrimaryKey" parameterType="com.taotao.pojo.TbItem">
    update tb_item
    set title = #{title,jdbcType=VARCHAR},
    where id = #{id,jdbcType=BIGINT}
    </update>
    

• updateByExample()

      UserExample example = new UserExample();
      Criteria criteria = example.createCriteria();
      criteria.andUsernameEqualTo("admin");
     User user = new User();
      user.setPassword("wyw");
      XxxMapper.updateByPrimaryKeySelective(user,example);
  

  ```sql
  update user 
  set password='wyw' 
  where username='admin'
  ```
  

  • updateByExample()更新所有的字段，包括字段为null的也更新

    <!--updateByExample需要将表的条件全部给出，
    	比如一个一个表有三个字段，就必须给三个字段给它，不给会设为null-->
    <update id="updateByExample" parameterType="map" >
        update tb_item
        set id = #{record.id,jdbcType=BIGINT},
          title = #{record.title,jdbcType=VARCHAR},
          sell_point = #{record.sellPoint,jdbcType=VARCHAR},
          price = #{record.price,jdbcType=BIGINT},
          num = #{record.num,jdbcType=INTEGER},
          barcode = #{record.barcode,jdbcType=VARCHAR},
          image = #{record.image,jdbcType=VARCHAR},
          cid = #{record.cid,jdbcType=BIGINT},
          status = #{record.status,jdbcType=TINYINT},
          created = #{record.created,jdbcType=TIMESTAMP},
          updated = #{record.updated,jdbcType=TIMESTAMP}
        <if test="_parameter != null" >
          <include refid="Update_By_Example_Where_Clause" />
        </if>
      </update>
    

  • updateByExampleSelective()更新想更新的字段

    <!--而updateByExampleSelective不同，当某一实体类的属性为null时，
    	mybatis会使用动态sql过滤掉，不更新该字段：-->
    <update id="updateByExampleSelective" parameterType="map" >
        update tb_item
        <set >
          <if test="record.id != null" >
            id = #{record.id,jdbcType=BIGINT},
          </if>
          <if test="record.title != null" >
            title = #{record.title,jdbcType=VARCHAR},
          </if>
          <if test="record.sellPoint != null" >
            sell_point = #{record.sellPoint,jdbcType=VARCHAR},
          </if>
          <if test="record.price != null" >
            price = #{record.price,jdbcType=BIGINT},
          </if>
          <if test="record.num != null" >
            num = #{record.num,jdbcType=INTEGER},
          </if>
          <if test="record.barcode != null" >
            barcode = #{record.barcode,jdbcType=VARCHAR},
          </if>
          <if test="record.image != null" >
            image = #{record.image,jdbcType=VARCHAR},
          </if>
          <if test="record.cid != null" >
            cid = #{record.cid,jdbcType=BIGINT},
          </if>
          <if test="record.status != null" >
            status = #{record.status,jdbcType=TINYINT},
          </if>
          <if test="record.created != null" >
            created = #{record.created,jdbcType=TIMESTAMP},
          </if>
          <if test="record.updated != null" >
            updated = #{record.updated,jdbcType=TIMESTAMP},
          </if>
        </set>
        <if test="_parameter != null" >
          <include refid="Update_By_Example_Where_Clause" />
        </if>
      </update>
    

    • 最好使用updateByExampleSelective，因为它能根据所需进行更新操作。

删除

• deleteByPrimaryKey()

  XxxMapper.deleteByPrimaryKey(1);
  

  delete from user where id=1
  

• deleteByExample()

  UserExample example = new UserExample();
  Criteria criteria = example.createCriteria();
  criteria.andUsernameEqualTo("admin");
  XxxMapper.deleteByExample(example);
  

  delete from user where username='admin'