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‘
    

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

上一篇:使用证书创建数据库镜像


下一篇:sql日期格式化