mybatis实现CRUD（增删改查），操作数据库中表数据

mybatis学习

实现增删改查操作数据

1、接口 UserMapper.java

public interface UserMapper {
    //查询全部数据
    List<User> getUserList();

    //按id查询
    User getUserById(int id);

    //插入数据
    int addUser(User user);

    //修改数据
    int updateUser(User user);

    //删除数据
    int deleteUser(int id);
}

2、接口对应的配置文件 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">

<!--namespace: 绑定一个对应的Dao（Mapper）接口；-->
<mapper namespace="com.xxw.dao.UserMapper">

    <!--select查询语句-->
    <select id="getUserList" resultType="com.xxw.pojo.User" parameterType="int">
        select * from mybatis_xxw.user
    </select>
    
    <!--根据ID查询数据-->
    <select id="getUserById" resultType="com.xxw.pojo.User">
        select *
        from mybatis_xxw.user
        where id = #{id};
    </select>

    <!--插入一条数据-->
    <insert id="addUser" >
        insert into mybatis_xxw.user (id,name,pwd)
        values (#{id},#{name},#{pwd});
    </insert>

    <!--修改数据-->
    <update id="updateUser" parameterType="com.xxw.pojo.User">
        update mybatis_xxw.user
        set name=#{name},pwd=#{pwd}
        where id=#{id};
    </update>
    
    <!--删除数据-->
    <delete id="deleteUser" parameterType="int">
        delete from mybatis_xxw.user
        where id = #{id}
    </delete>
</mapper>

3、编写测试类：UserMapperTest.java

public class UserMapperTest {

    @Test
    //查询全部数据
    public void test(){
        //第一步：获取sqlSession对象
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        //获取mapper：getMapper
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        
        List<User> userList = userMapper.getUserList();
        for (User user : userList){
            System.out.println(user);
        }
        //关闭SqlSession
        sqlSession.close();
    }

    @Test
    //按id查询数据
    public void getUserById(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = userMapper.getUserById(1);
        System.out.println(user);
        sqlSession.close();
    }

    @Test
    //插入一条数据
    public void addUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        int res = userMapper.addUser(new User(6, "xiaodong", "666"));

        if (res>0){
            System.out.println("数据插入成功...");
        }else {
            System.out.println("数据插入失败...");
        }
        //提交事务
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    //修改一条数据
    public void updateUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.updateUser(new User(5,"wangwu","123"));
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    //删除一条数据
    public void deleteUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.deleteUser(7);
        sqlSession.commit();
        sqlSession.close();
    }
}

注意：

UserMapper.xml配置文件中需注意的点

• List itemnamespace：命名空间，对应dao层mapper名称；
• List itemid，对应namespace里的方法名；
• List itemresultType：对应返回类型，只在select中用到；
• List itemparameterType：参数类型，有一个where条件的，就对应该字段的类型；多个字段时用User。

实现模糊查询

定义接口

//模糊查询
    List<User> getUserLike(String value);

接口配置文件

<!--模糊查询-->
    <select id="getUserLike" parameterType="com.xxw.pojo.User" resultType="com.xxw.pojo.User">
        select * from mybatis_xxw.user
        where name
        like #{value};
    </select>

测试类

@Test
    //模糊查询
    public void getUserLike(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = mapper.getUserLike("%王%");
        for (User user : userList){
            System.out.println(user);
        }
        sqlSession.close();
    }