mybatis注解与xml常用语句汇总

public interface usermapper {

  //查询

  @select ( "select * from user where id=#{id}" )

  user selectuser( int id);

  //查询全部

  @select ( "select * from user" )

  list<user> selectuserlist();

  //增加数据

  @insert ( "insert into user (name) values(#{name})" )

  boolean insertuser(string name);

  //修改用户

  @update ( "update user set name=#{name} where id=#{id}" )

  boolean updateuser( @param ( "name" ) string name, @param ( "id" ) int id);

  //删除用户

  @delete ( "delete from user where id=#{id}" )

  boolean deleteuser( int id);

}

@select ( "select * from user" )

@results ({

  @result (id = true ,property = "id" ,column = "id" ), //id=true 对应于主键

  @result (property = "uid" ,column = "uid" ),

  @result (property = "user" ,column = "uid" ,javatype = user. class ,

  one = @one (select = "com.example.dao.userdao.finduserbyid" ,fetchtype = fetchtype. default ))

  //user 对应实体类中一对一的实体类名字，uid表示通过uid外键查询user，javatype表示查询结果

  //映射成user类型对象，one=表示一对xx fetchtype.default默认是立即加载全部查询，使用lazy懒加载需要才查询

})

list<user> selectuserlist();

<resultmap id= "baseresultmap" type= "xx" >

  <id column= "id" property= "id" jdbctype= "bigint" />

  <result column= "aa" property= "aa" jdbctype= "varchar" />

  <result column= "bb" property= "bb" jdbctype= "integer" />

  <result column= "cc" property= "cc" jdbctype= "decimal" javatype= "java.math.bigdecimal" />

  <result column= "dd" property= "dd" jdbctype= "date" />

</resultmap>

<sql id= "base_column_list" >

  aa, bb

  </sql>

  <sql id= "where" >

  <trim prefix= "where" prefixoverrides= "and|or" >

  < if test= "id != null and id != ''" >

   and t.id = #{id}

  </ if >

  < if test= "content != null and content != ''" >

   and t.content like concat( '%' , #{content}, '%' )

  </ if >

  and t.app_code in

  <foreach item= "item" index= "index" collection= "appcodes"

   open= "(" separator= "," close= ")" >

   #{item}

  </foreach>

  and t.user_id=u.id and t.removed= 0

  </trim>

</sql>

<insert id= "insert" parametertype= "xxx"

  usegeneratedkeys= "true" keycolumn= "id" keyproperty= "id" >

  insert into xxx (

  <trim suffixoverrides= "," >

   < if test= "title != null and title != '' " >

    title ,

   </ if >

  </trim>

  ) values (

  <trim suffixoverrides= "," >

   < if test= "title != null and title != '' " >

    #{title} ,

   </ if >

  </trim>

  )

</insert>

<update id= "update" parametertype= "xxx" >

  update xxx

  <set>

   < if test= "title != null and title != '' " >

    title = #{title} ,

   </ if >

  </set>

  where

  id = #{id}

</update>

<update id= "xxxupdate" parametertype= "java.util.list" >

  update xxx

  <trim prefix= "set" suffixoverrides= "," >

   <trim prefix= "aa =case" suffix= "end," >

    <foreach collection= "orders" item= "item" index= "index" >

     < if test= "item.aa !=null" >

      when id=#{item.id} then #{item.aa}

     </ if >

    </foreach>

   </trim>

   <trim prefix= "bb =case" suffix= "end," >

    <foreach collection= "orders" item= "item" index= "index" >

     < if test= "item.bb !=null" >

      when id=#{item.id} then #{item.bb}

     </ if >

    </foreach>

   </trim>

  </trim>

  where id in

  <foreach collection= "orders" index= "index" item= "item" separator= "," open= "(" close= ")" >

   #{item.id,jdbctype=bigint}

  </foreach>

</update>

#mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.stdoutimpl