电商项目开发6
用户管理
UserDao.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- 命名空間,xml文件和dao接口对应起來 -->
<mapper namespace="com.zq.dao.UserDao">
<resultMap type="com.zq.model.User" id="userMap">
<result property="id" column="id"/><!-- column是数据库字段的名字 property是java里面属性名 -->
<result property="username" column="user_name"/><!-- column是数据库字段的名字 property是java里面属性名 -->
<result property="pwd" column="user_pwd" /><!-- mybatis plugin插件快捷键 Alt+/ -->
<result property="realname" column="realname" />
<result property="grade" column="grade" />
<result property="total" column="total" />
<result property="account" column="account" />
</resultMap>
<select id="findById" parameterType="integer" resultType="user">
select * from user where id = #{id}
</select>
<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},'%')<!-- -->
</if>
</where>
</sql>
<!-- 查询列表 -->
<select id="list" parameterType="user" resultMap="userMap">
select * from user
<include refid="sqlWhere" />
</select>
<!-- id不需要,自增 -->
<insert id="create" parameterType="user">
insert into
user(username,pwd,realname)
values(#{username},#{pwd},#{realname})
</insert>
<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}
<!-- set username = #{username},pwd=#{pwd} where id = #{id} -->
</update>
<delete id="delete" parameterType="integer">
delete from user where id = #{id}
</delete>
<!-- 批量操作 -->
<update id="updateBatch" parameterType="List"><!-- List<Integer> -->
update user set pwd = '123' where id in
<foreach item="item" index="index" collection="list" open="("
separator="," close=")">
#{item}
</foreach>
</update>
<select id="findByTotal" parameterType="list" resultType="user">
select * from user
<where>
<foreach item="item" index="index" collection="list" open=" ( "
separator=" ) or ( " close=" ) ">
grade = #{item.grade} - 1 and total >= #{item.total}<!-- 大于和小于号要用转义符 -->
<!-- 另一种写法<![CDATA[grade = #{item.grade} - 1 and total >= #{item.total}]]> -->
</foreach>
</where>
</select>
<update id="updateGrade" parameterType="List"><!-- List<User>类型 -->
update user set grade = grade + 1 where id in
<foreach item="item" index="index" collection="list" open="("
separator="," close=")">
#{item.id}
</foreach>
</update>
</mapper>
UserDao.java
package com.zq.dao;
import java.util.List;
import java.util.Map;
import com.zq.model.User;
public interface UserDao {
public List<User> list(User user);
public void create(User user);
public void update(User user);
public void delete(Integer id);
public void updateBatch(List<Integer> list);
public User findById(Integer id);
public List<User> findByTotal(List<Map<String, Object>> gradeList);
public void updateGrade(List<User> users);
}