MyBatis Implement Dao,mapperxml syntax

MyBatis implement Dao,mapperxml syntax

(1) Declare a SQL, you can embed other SQL according to the ID

<sql id= "Base_column_list" >
    ID, 
    user_id, 
    user_name 
    , 
    password, depart_id, user_ Creation_time, 
    user_pwd_modif_time, 
    user_status, 
    user_lock, 
    user_pwd_error, 
    user_pwd_ Error_date,
    Exit_time,password_old,
    post, email, office_telephone, 
    user_number, Id_card, Mobile_ Phone, 
    sex, Home_phone,logout,init_pwd_time,sort, Logout_time
  </sql>

(2) Use include to refer to the SQL declared in (1) by ID, and also note the syntax format like '% ' | | #{username}| | ' %’

<select id= "Selectbysearch" parametertype= "java.lang.String" resultmap= "Baseresultmap"  >
     Select 
    <include refid= "Base_column_list"/> from
     user
     where (user_name like '% ' | | #{username}| | ' % ' or user_id like '% ' | | #{username}| | ' % ') and 
     "user_status" = ' 0 '
 </select>

(3) Use the IF tag to control the parameters that need to be transferred, and if the parameter is not null or empty, this is the condition Sussu

<select id= "Selectdepartuser" resultmap= "Baseresultmap" >
   select ID, user_id, user_name, 
      depart_id From
    USER 
    where  user_status =  ' 0 '
    <if test= "_parameter!= null and _parameter!= '" >  
         and Depart_id=#{_parameter,jdbctype=varchar}  
    </if>  
  </select>

(4) Using foreach to process a list of parameters

  <update id= "Updatelogout" parametertype= "Repository.entity.user.User" >
    update user
    Set logout = ' 1 '
    where ID
    in <foreach item= "item" index= "index" collection= "list" open=   
           "(" separator= "," close= ")" >  
            #{item}  
     </foreach>
  </update>

The collection property is the name of the list or array, and the method parameter should be annotated
(5) using the Trim label, wrap the ID and user_id in parentheses, and the last one to remove the comma
Prefix: prefix the package's SQL statement.
Suffix: Adds a suffix to the package's SQL statement.
Prefixoverrides: If the package's SQL statement is an empty statement (often in cases where the if is judged to be no), remove the specified prefix, such as where.
Suffixoverrides: If the SQL statement of the parcel is an empty statement (often appearing in the case of if judgement is not), cancel the specified suffix, such as and | Or. commas, etc.

<insert id= "insertselective" parametertype= "Repository.entity.user.User" >
    insert INTO user user
    < Trim prefix= "(" suffix= ")" suffixoverrides= "," >
      <if test= "id!= null" >
        ID,
      </if>
      < If test= "userId!= null" >
        user_id,
      </if>
    </trim>
    <trim prefix= "VALUES (" suffix= ") "Suffixoverrides=", ">
      <if test=" id!= null ">
        #{id,jdbctype=varchar},
      </if>
      <if test= "userId!= null" >
        #{userid,jdbctype=varchar},
      </if>
    </trim>
  </ Insert>

(6) greater than, less than, equal to, fuzzy query

and O.create_time <= #{endtime,jdbctype=timestamp}//less than and
 o.create_time >= #{startTime,jdbcType= timestamp}//is greater than and
I.status_flag <![ cdata[<>]]> ' F '//Not equal to
<if test= "dutygroupname!= null and Dutygroupname!=" "> and
    duty_group_n AME like '% ' | | #{dutygroupname}| | ' % '    //fuzzy query
</if>

(7) The string comparison within the IF

<if test= "Projectstatus!= null and Projectstatus!= ' and Projectstatus '!= ' 0 '. toString ()" > and
        project_status = #{projectstatus,jdbctype=varchar}
</if>
<if test= ' projectstatus = = ' 0 ' > and
    project_ STATUS!= ' 0 '
</if>