浅入浅出MyBatis(07):删除数据

January 14th 2015  | Tags: MyBatis, orm

2015-01-14

该文章是《专题:浅入浅出MyBatis》系列文章的一篇。点击进入该专题目录

工具与环境:Intellij IDEA 14,JDK 1.7,MyBatis 3.2,MySQL 5.5。

浅入浅出MyBatis(01):查询id为1的用户的信息的基础上,我们看一下该如何实现。

假如现在要:
1、根据id删除一个用户。
2、删除大于某一id的所有用户。

在user表中再插入一些数据

插入数据后,user所有数据如下:

mysql> select * from user;
+----+---------+-----------------+----------+
| id | name    | email           | password |
+----+---------+-----------------+----------+
|  1 | letian  | letian@111.com  | 123      |
|  2 | xiaosi  | xiaosi@111.com  | 123      |
|  6 | xiaowei | xiaowei@111.com | 456      |
|  9 | xiaohu  | xiaohu@111.com  | 456      |
| 10 | xiaoye  | xiaoye@111.com  | 456      |
+----+---------+-----------------+----------+
5 rows in set (0.00 sec)

修改letian.mybatis.dao下的UserMapper.java

将其内容修改为:

package letian.mybatis.dao;

import letian.mybatis.bean.User;

public interface UserMapper {

    User findById(int id);
    void delete(User user); // 删除user
    void deleteByGreaterThanId(int id); //删除id大于int id的用户
}

修改letian.mybatis.mapper下的UserMapper.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">

<mapper namespace="letian.mybatis.dao.UserMapper">

    <select id="findById" parameterType="Integer" resultType="letian.mybatis.bean.User">
        SELECT
        * FROM blog_db.user WHERE id=#{id}
    </select>

    <delete id="delete" parameterType="letian.mybatis.bean.User">
      DELETE FROM blog_db.user WHERE id=#{id}
    </delete>

    <delete id="deleteByGreaterThanId" parameterType="Integer">
      <![CDATA[
        DELETE FROM blog_db.user WHERE id>#{id}
      ]]>
    </delete>
</mapper>

由于<delete id="deleteByGreaterThanId"</delete>中的sql语句有>,所以使用<![CDATA[ ... ]]>将sql语句包裹起来。

修改Main.java

将其内容修改为:

import java.io.IOException;
import java.util.Iterator;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import letian.mybatis.bean.User;
import letian.mybatis.dao.UserMapper;


public class Main {

    public static void main(String[] args) throws IOException {

        SqlSessionFactory sessionFactory;
        sessionFactory = new SqlSessionFactoryBuilder()
                .build(Resources.getResourceAsReader("mybatis-config.xml"));

        SqlSession sqlSession = sessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        User user = userMapper.findById(6);

        userMapper.delete(user);
        userMapper.deleteByGreaterThanId(7);

        sqlSession.commit();
        sqlSession.close();

        System.out.println(user);
    }
}

执行Main.java,结果为:

User{id=6, name='xiaowei', email='xiaowei@111.com', password='456'}

可见,user对象对应的表中的数据虽然没有了,但是user对象没有被设为null。

查看一下user表中的内容:

mysql> select * from user;
+----+--------+----------------+----------+
| id | name   | email          | password |
+----+--------+----------------+----------+
|  1 | letian | letian@111.com | 123      |
|  2 | xiaosi | xiaosi@111.com | 123      |
+----+--------+----------------+----------+
2 rows in set (0.00 sec)

(完)