mybatis mapper.xml 基础的增删改查

1. 新增的几种情景

   1.      <!--单条新增-->
          <insert id="addUser" parameterType="User">
            INSERT INTO `user` (
      	    `userId`,
      	    `username`,
      	    `nickname`,
      	    `password`
            )
              VALUES(
              #{userId},
              #{phone},
              #{nickname},
              #{password}
              )
          </insert>
      
      
        
      <!--新增数据返回主键，这时主键必须是自增主键-->
      
      
      <insert id="addUser" parameterType="User">
          <selectKey keyProperty="id" resultType="long" order="AFTER">
        		SELECT LAST_INSERT_ID()
        	</selectKey>
            INSERT INTO `user` (
      	    `userId`,
      	    `username`,
      	    `nickname`,
      	    `password`
            )
              VALUES
            (
              #{userId},
              #{phone},
              #{nickname},
              #{password}
              )
       </insert>
      
      
      
      <!--新增数据若数据已存在则更新-->
      
      <insert id="addUser" parameterType="User">
            INSERT INTO `user` (
      	    `userId`,
      	    `username`,
      	    `nickname`,
      	    `password`
            )
              VALUES
           (
              #{userId},
              #{phone},
              #{nickname},
              #{password}
              )
      
              ON DUPLICATE KEY UPDATE nickname=#{nickname};
       </insert>
      
      
        <!--批量新增数据-->
       <insert id="insertUserList" parameterType="java.util.List">
          INSERT INTO
          <include refid="table"/>
          (
          `userId`,
          `username`,
          `nickname`,
          `password`
          ) 
          VALUES
          <foreach collection="list" item="item" index="index" separator=",">
              (
              #{item.userId},
              #{item.username},
              #{item.nickname},
              #{item.password}
              )
          </foreach>
      
        </insert>
      
      
      
      
      
      
      
      
      
      
      

       

2. 删除

   1.     <!-- 删除 -->
         <delete id="deleteUserByuserId" parameterType="java.lang.String">
              DELETE FROM
              <include refid="table"/>
              where
              userId=#{userId}
          </delete>

       

3. 修改

   1.       <!--修改信息-->
          <update id="updateUser" parameterType="User">
              UPDATE
              <include refid="table"/>
              <set>
                  <if test="nickname != null and nickname  !=''">
                      nickname=#{nickname},
                  </if>
                  <if test="email != null and email  !=''">
                      email=#{email},
                  </if>
                  <if test="phone != null and phone  !=''">
                      phone=#{phone}
                  </if>
              </set>
              WHERE UserId= #{userId}
          </update>
      
      
      
       

       

4. 查询

   1. <select id="getUser" parameterType="User" resultType="User">
          SELECT
          <include refid="Base_Column_List"/>
          FROM
          <include refid="table"/>
          <trim prefix="where" prefixOverrides="and">
              <if test="userId != null and userId != ''">
                  and userId=#{userId}
              </if>
              <if test="password != null and password != ''">
                  and password=#{password}
              </if>
          </trim>
      
      </select>