SQL语句

三

在这节课中学了关于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数据库中就会出现你所添加的这一条命令