博客

这个比较简单,有几点注意一下就可以了,直接看代码

//UserMapper.java
void findByParamsAndPage(Map<String,Object> params);
//UserMapper.xml
<select id="findByParamsAndPage" parameterType="java.util.Map" resultMap="userdeptMap">
    select u.user_id,u.username,u.password,u.birthday,u.dept_id,d.dept_name
    from t_user u,t_dept d
    <where>
        <if test="username != null and username != ''">
            username like "%"#{username}"%"
        </if>
        <if test="password != null and password != ''">
            password like "%"#{password}"%"
        </if>
    </where>
    limit #{beginIndex},#{pageSize}
</select>
//test测试方法
public void findByParamsAndPage() throws Exception
{
    SqlSession session = M3Util.getSession();
    UserMapper userMapper = session.getMapper(UserMapper.class);
    int currentPage = 2;
    int pageSize = 10;
    Map<String,Object> params = new HashMap<String,Object>();
    params.put("username","张");
    params.put("password","123");
    params.put("beginIndex",(currentPage-1)*pageSize);
    params.put("pageSize",pageSize);
    List<User> list = userMapper.findByParamsAndPage(params);
    session.commit();
    session.close();
    for(User user: list)
    {
        System.out.println(user);
    }
}
//注意:where 中的 if 判断
//注意:limit放在where标签外面

批量删除,插入,修改

//批量删除
//UserMapper.java
void delBatchUserByList(List<Integer> ids);
void delBatchUserByArray(Integer[] ids);
//UserMapper.xml
//注意collection = "list",
<delete id="delBatchUserByList" parameterType="java.util.List">
    delete from t_user where user_id in
    <foreach collection="list" item="id" index="index" open="(" separator="," close=")">
        #{id}
    </foreach>
</delete>
//注意 collection="array" 和 parameterType="Object[]"
<delete id="delBatchUserByArray" parameterType="Object[]">
    delete from t_user where user_id in
    <foreach collection="array" item="id" index="index" open="(" separator="," close=")">
        #{id}
    </foreach>
</delete>
//注意:${value},这个地方不是#而是$,参数只能是value,别的会报错.
<delete id="delBatchUserByString" parameterType="java.lang.String">
    delete from t_user where user_id in (${value})
</delete>

批量插入

//UserMapper.java
void addUserBatch(List<User> list>);
//UserMapper.xml
//注意,括号的位置,和符合属性的取值-->user.dept.deptId
<insert id="addUserBatch" parameterType="java.util.List">
    insert into t_user (username,password,birthday,dept_id) values
    <foreach collection="list" item="user" index="index" separator=",">
        (#{user.username},#{user.password},#{user.birthday},#{user.dept.deptId})
    </foreach>
</insert>

批量修改

//UserMapper.java
void updUserBatch(List<User> list);
//UserMapper.xml
//注意:1. 这个地方的分隔符separator=";"
//2. 这个地方是整条语句放在foreach标签中
//3. jdbc链接方式要修改 追加&allowMultiQueries=true
//mysql.url=jdbc:mysql://localhost:3306/mybatistest1?characterEncoding=utf-8&allowMultiQueries=true
<update id="updUserBatch" parameterType="java.util.List">
    <foreach collection="list" item="user" index="index" separator=";">
        update t_user
        <set>
            username=#{user.username},password=#{user.password},birthday=#{user.birthday},dept_id=#{user.dept.deptId}
        </set>
        <where>
            user_id =#{user.userId}
        </where>
    </foreach>
</update>