Mybatis动态SQL实现增删改查

刺骨的言语ヽ痛彻心扉 2022-03-16 15:25 271阅读 0赞

### 目录 ###

*   *  新增数据
     *  批量新增数据
     *  删除数据
     *  修改数据
     *  查询数据

## 新增数据 ##

<insert id="register" parameterType="com.wyj.entity.po.User">
    	insert into <include refid="tableName"/>
    	<trim prefix="(" suffix=")" suffixOverrides=",">
    		<if test="id != null">
    			id,
    		</if>
    		<if test="username!= null and username != '' ">
    			username,
    		</if>
    		<if test="password!= null and password != '' ">
    			password,
    		</if>
    	</trim>
    	<trim prefix="values (" suffix=")" suffixOverrides=",">
    		<if test="id != null">
    			#{id,jdbcType=INTEGER},
    		</if>
    		<if test=" username != null and username != '' ">
    			#{username,jdbcType=VARCHAR},
    		</if>
    		<if test=" password!= null and password!= '' ">
    			#{password,jdbcType=VARCHAR},
    		</if>
    	</trim>
    </insert>

## 批量新增数据 ##

<insert id="insertUserList" parameterType="com.wyj.entity.po.User">
        insert into `user`(name,sex,age,address,phone) values
        <foreach collection="list" index="index" item="user" separator=",">
            <trim prefix="(" suffix=")">
                #{user.name},#{user.sex},#{user.age},#{user.address},#{user.phone}
            </trim>
        </foreach>
    </insert>

public void insertUserList(List<User> userList);

## 删除数据 ##

<delete id="deleteById" parameterType="com.wyj.entity.po.User">
    	delete from <include refid="tableName"/>
    	<where>
    		<if test=" id != null">
    			id=#{id,jdbcType=INTEGER}
    		</if>
    	</where>
    </delete>

## 修改数据 ##

<update id="updateByUsername" parameterType="com.wyj.entity.po.User">
    	update <include refid="tableName"/>
    	<set>
    		<if test=" password!= null and password!= '' ">
    			password=#{password,jdbcType=VARCHAR},
    		</if>
    		<if test=" name!= null and name!= '' ">
    			name=#{name,jdbcType=VARCHAR},
    		</if>
    	</set>
    	<where>
    		<if test=" username != null and username != '' ">
    			username=#{username,jdbcType=VARCHAR}
    		</if>
    	</where>
    </update>

## 查询数据 ##

<select id="findAll" resultMap="tableResultMap">
    	select * from <include refid="tableName"/>
     	<where>
    		<if test = " name != null and user != '' ">
    			and `name` like CONCAT('%',#{name,jdbcType=VARCHAR},'%')
    		</if>
    		<if test = " sex != null and sex != '' ">
    			and sex = #{sex,jdbcType=VARCHAR}
    		</if>
    		<if test = " age != null and age != '' ">
    			and age = #{age,jdbcType=VARCHAR}
    	</where>
     </select>