查询所有用户
<select id="findAll" resultType="entity.User">
select * from user
</select>
查询指定用户
<select id="findById" resultType="entity.User" parameterType="java.lang.Integer">
select * from user where id = #{id};
</select>
模糊查找,这里要用 @
<select id="findMohu" resultType="entity.User" parameterType="java.lang.String">
select * from user where username like '%${value}%' or sex like '%${value}%';
</select>
判断查找,使用 if 语句,注意
<select id="selectByName" resultType="entity.User" parameterType="java.lang.String">
select * from user
-- 满足 if 就只走前面,不满足就全都走
<if test="username != null and username != ''">
where username = #{username}
</if>
</select>
删除用户
<delete id="deleteById" parameterType="java.lang.Integer">
delete from user where id = #{id};
</delete>
更新用户
<update id="UpdateById" parameterType="entity.User">
update user set sex = #{sex}, birthday = #{birthday}, address = #{address}, username = #{username} where id = #{id};
</update>
插入用户
<insert id="insertUser" parameterType="entity.User">
insert into user (username, birthday, sex, address) values(#{username}, #{birthday}, #{sex}, #{address});
</insert>
插入用户并返回该用户的 id
<insert id="insertUserRetId" parameterType="entity.User">
<selectKey keyProperty="id" resultType="int" order="AFTER">
select LAST_INSERT_ID()
</selectKey>
insert into user (username, birthday, sex, address) values(#{username}, #{birthday}, #{sex}, #{address});
</insert>
这是很经典的语句
<select id="selectByMul" resultType="entity.User" parameterType="entity.User">
select * from user
-- 进入 where 条件(当 where 条件多个时)
<where>
-- 满足 if 就只走前面,不满足就全都走
-- 如果username 不是空,就进入 if 语句
<if test="username != null and username != ''">
username = #{username}
</if>
-- 如果地址不是空,就进入 if 语句,选择地址等于的
-- 如果地址是空,不进入 if 语句,相当于只执行上面的
<if test="address != null and address != ''">
and address = #{address}
</if>
</where>
</select>
更新操作,使用 <set>
<update id="updateById" parameterType="entity.User">
update user
<set>
<if test="username != null and username != ''">
username = #{username}
</if>
<if test="address != null and address != ''">
, address = #{address};
</if>
<if test="sex != null and sex != ''">
, sex = #{sex};
</if>
<if test="birthday != null and birthday != ''">
, birthday = #{birthday};
</if>
<where>
id = #{id};
</where>
</set>
</update>
更多推荐
Mybatis 常用 sql 语句汇总
发布评论