三
在这节课中学了关于sql的命令
1、查询
<sql id="sqlWhere">
<where><!-- 这种写法会自动去掉第一个and -->
<if test="username!=null and username!=''">
and username = #{username}
</if>
<if test="pwd!=null and pwd!=''">
and pwd = #{pwd}
</if>
<!-- id与字符串判断区别 -->
<if test="id != null">
and id =#{id}
</if>
<if test="realname!=null and realname!=''">
and realname like concat('%',#{realname},'%')<!-- '%'#{realname}'%' -->
</if>
</where>
</sql>
<!-- 查询列表 -->
<select id="list" parameterType="user" resultType="user">
select * from user
<include refid="sqlWhere" />
</select>
2、增加
<insert id="create" parameterType="user">
insert into user(username,pwd,realname)
values(#{username},#{pwd},#{realname})
</insert>
3、更新
<update id="update" parameterType="user">
update user
<set>
<if test="username!=null and username!=''">
username=#{username},
</if>
<if test="pwd!=null and pwd!=''">
pwd=#{pwd},
</if>
<if test="realname!=null and realname!=''">
realname=#{realname},
</if>
</set>
where id=#{id}
<!-- update user set username= #{username},pwd=#{pwd}
where id=#{id} -->
</update>
4、删除
<delete id="delete" parameterType="integer">
delete from user where id=#{id}
</delete>
5、批量操作
<update id="updateBatch" resultType="user" parameterType="List">
update user set pwd ='123' where id in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</update>
6、在UserController中执行
@ResponseBody
@RequestMapping("/create.do")
public boolean create(User user){
try{
userService.create(user);
}catch(Exception e){
System.out.println(e.getMessage());
return false;
}
return true;
}
7、运行程序,在地址栏中输入localhost:8080/shop/user/create.do?username=zb&pwd=123
执行的是添加命令,在mySQL数据库中就会出现你所添加的这一条命令