Mybatis动态SQL实现增删改查

最新推荐文章于 2023-03-02 08:32:21 发布

断橋殘雪

最新推荐文章于 2023-03-02 08:32:21 发布

阅读量2.5k

点赞数

CC 4.0 BY-SA版权

分类专栏： Mybatis 文章标签： Mybatis

本文链接：https://blog.youkuaiyun.com/qq_35620501/article/details/88016611

Mybatis 专栏收录该内容

6 篇文章

订阅专栏

本文详细介绍了使用MyBatis进行数据批量插入、更新、删除及查询的方法，包括动态SQL的使用技巧，如trim、foreach、if等元素的应用，以实现更灵活的数据操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

新增数据

<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>

<sql id="tableName">
	user
</sql>

批量新增数据

<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>

<sql id="tableName">
	user
</sql>

修改数据

<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>

<sql id="tableName">
	user
</sql>

查询数据

<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>

<sql id="tableName">
	user
</sql>

<resultMap id="tableResultMap" type="com.wyj.entity.po.User">
	<id column="id" property="id">
	<result column="username" property="username">
	<result column="password" property="password">
	<result column="name" property="name">
	<result column="sex" property="sex">
	<result column="age" property="age">
</resultMap>