mybatis增删改查

1.在第一天的基础上在添加接口并写入如下抽象方法 分别是查找（ID）查询所有 模糊查询 添加 更新 删除
2.在UserMapper.xml 中分别添加SQL语句

package ztw.mybatis.mapper;

import ztw.mybatis.bean.User;

import java.util.List;

public interface UserMapper {
    User findById(int id);
    List<User> findAll();
    List<User> findByName(String name);
    int insert(User user);
    int update(User user);
    int deleteById(int id);

}

<?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="ztw.mybatis.mapper.UserMapper">

    <select id="findById" parameterType="int" resultType="User">
        SELECT * FROM t_user WHERE id = #{id};
    </select>

    <select id="findAll" resultType="User">
        SELECT * FROM t_user;
    </select>

    <select id="findByName" resultType="User">
        select * from t_user where name like concat(#{name},"%");
    </select>

    <insert id="insert" parameterType="User" useGeneratedKeys="true" keyProperty="id">
        insert into t_user(name,age,address) values(#{name},#{age},#{address});
    </insert>

<update id="update" parameterType="User" >
    update t_user set name = #{name},age=#{age},address=#{address} where id =#{id};
</update>
    <delete id="deleteById" parameterType="int">
        delete from t_user where id = #{id};
    </delete>
</mapper>

1.添加

//插入数据
    @Test
    public void testInsert(){
        User user = new User();
        user.setName("王雨涵");
        user.setAge(25);
        user.setAddress("m78星云");
        int count = userMapper.insert(user);
        sqlSession.commit();
        if(count>0){
            System.out.println("记录插入成功");
            System.out.println("记录新纪录"+user);
        } else {
            System.out.println("记录插入失败");
        }
    }

2.删除

 // 删除数据
    @Test
    public void TestDeleteById(){
        int id = 6;
        User user = userMapper.findById(id);
        System.out.println("待删除的记录"+user);
        userMapper.deleteById(id);
        sqlSession.commit();
        System.out.println("编号为["+id +"]的记录删除成功)");
        user = userMapper.findById(id);
        if(user!=null){
            System.out.println(user);
        } else {
            System.out.println("编号为["+id+"]的用户未找到");
        }
    }

由于ID是6 的已经删除了，所以位null

3.更新

 //更新数据
    @Test
    public void testUpdate(){
        int id = 7;
        User user = userMapper.findById(id);
        System.out.println("更新前的数据"+user);
        user.setName("赛迦");
        user.setAge(180000);
        user.setAddress("北京呀");
        int count = userMapper.update(user);
        sqlSession.commit();
        if(count>0){
            System.out.println("更新成功");
            System.out.println("更新后的纪录"+userMapper.findById(id));
        } else {
            System.out.println("更新失败");
        }
    }

4.查找

 //通过id查找
    @Test
    public void testFindById() {
        int id = 1;
        User user = userMapper.findById(id);
        if (user != null) {
            System.out.println(user);
        } else {
            System.out.println("编号为[" + id + "]的用户未找到。");
        }
    }
    //查询所有
    @Test
    public void testFindAll() {
        List<User> users = userMapper.findAll();
        users.forEach(user -> System.out.println(user));
    }
    //模糊查找
    @Test
    public void TestFindByName(){
     String name = "赛迦";
     List<User> users = userMapper.findByName(name);
     if(users.size()>0){
         users.forEach(user -> System.out.println(user));
     } else{
         System.out.println("姓名为["+name+"]的记录未找到");
     }
    }

源码

package ztw.mybatis.mapper;

import ztw.mybatis.bean.User;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;

import java.io.Reader;
import java.util.List;


public class TestUserMapper {

    private SqlSession sqlSession; // SQL会话
    private UserMapper userMapper; // 用户映射器

    @Before
    public void init() {
        try {
            // 读取MyBatis配置文件作为字符输入流
            Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
            // 基于MyBatis配置文件构建SQL会话工厂
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
            // 利用SQL会话工厂获取SQL会话
            sqlSession = factory.openSession();
            // 利用SQL会话获取用户映射器对象
            userMapper = sqlSession.getMapper(UserMapper.class);
            // 提示用户SQL会话对象创建成功
            System.out.println("sqlSession对象已创建。");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    //通过id查找
    @Test
    public void testFindById() {
        int id = 1;
        User user = userMapper.findById(id);
        if (user != null) {
            System.out.println(user);
        } else {
            System.out.println("编号为[" + id + "]的用户未找到。");
        }
    }
    //查询所有
    @Test
    public void testFindAll() {
        List<User> users = userMapper.findAll();
        users.forEach(user -> System.out.println(user));
    }
    //模糊查找
    @Test
    public void TestFindByName(){
     String name = "赛迦";
     List<User> users = userMapper.findByName(name);
     if(users.size()>0){
         users.forEach(user -> System.out.println(user));
     } else{
         System.out.println("姓名为["+name+"]的记录未找到");
     }
    }
    //插入数据
    @Test
    public void testInsert(){
        User user = new User();
        user.setName("王雨涵");
        user.setAge(25);
        user.setAddress("m78星云");
        int count = userMapper.insert(user);
        sqlSession.commit();
        if(count>0){
            System.out.println("记录插入成功");
            System.out.println("记录新纪录"+user);
        } else {
            System.out.println("记录插入失败");
        }
    }
    //更新数据
    @Test
    public void testUpdate(){
        int id = 7;
        User user = userMapper.findById(id);
        System.out.println("更新前的数据"+user);
        user.setName("赛迦");
        user.setAge(180000);
        user.setAddress("北京呀");
        int count = userMapper.update(user);
        sqlSession.commit();
        if(count>0){
            System.out.println("更新成功");
            System.out.println("更新后的纪录"+userMapper.findById(id));
        } else {
            System.out.println("更新失败");
        }
    }
    // 删除数据
    @Test
    public void TestDeleteById(){
        int id = 6;
        User user = userMapper.findById(id);
        System.out.println("待删除的记录"+user);
        userMapper.deleteById(id);
        sqlSession.commit();
        System.out.println("编号为["+id +"]的记录删除成功)");
        user = userMapper.findById(id);
        if(user!=null){
            System.out.println(user);
        } else {
            System.out.println("编号为["+id+"]的用户未找到");
        }
    }
    @After
    public void destroy() {
        // 关闭SQL会话
        sqlSession.close();
        // 提示用户SQL会话对象关闭
        System.out.println("sqlSession对象已关闭。");
    }
}