用Mybatis进行简单的CRUD和模糊查询

简单的CRUD

第一步

先在UserMapper.java接口中添加具体要实现的方法

public interface UserMapper {
    //查询全部用户
    List<User> getUserList();
    //根据ID查询用户
    User getUserById(int id);
    //insert into 一个用户
    int addUser(User user);
    int updateUser(User user);
    int deleteUser(int id);
    //万能的Map
    int addUser2(Map<String,Object> map);
    
}

第二步

在UserMapper.xml中添加上面接口要实现的标签

在这里需要注意：
传入参数的类型 parameterType="int"
返回结果的类型 resultType="com.shadow.pojo.User
如果参数过多，但要使用的参数很少，用实体类的构造器全部赋值太麻烦，可以使用Map< key,value >来进行传入参数,但是必须注意要写对应Map的key

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace=绑定一个对应的Dao/Mapper接口-->
<mapper namespace="com.shadow.dao.UserMapper">
    <select id="getUserList" resultType="com.shadow.pojo.User">
    select * from mybatis.user
    </select>

    <select id="getUserById" parameterType="int" resultType="com.shadow.pojo.User">
        select * from mybatis.user where id = #{id}
    </select>
    
    <insert id="addUser" parameterType="com.shadow.pojo.User">
        insert into mybatis.user (id,name,password) values (#{id},#{name},#{password})
    </insert>

    <update id="updateUser" parameterType="com.shadow.pojo.User">
        update mybatis.user set name=#{name},password=#{password} where id =#{id}
    </update>

    <delete id="deleteUser" parameterType="int">
        delete from mybatis.user where id=#{id};
    </delete>

    <!--    万能的Map
-->
    <insert id="addUser2" parameterType="map">
        insert into mybatis.user (id,name,password) values (#{userid},#{username},#{userPassword})
    </insert>
    

</mapper>

第三步

在测试类中进行测试

这里要注意的是：增删改都需要提交事务，否则不能实际的修改数据库

public class UserMapperTest {
    @Test
    public void test() {
        //第一步：获取sqlSession对象
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        //第二步：执行SQL
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.getUserList();

        for (User user : userList) {
            System.out.println(user);
        }
        //关闭sqlSession
        sqlSession.close();
    }
    @Test
    public void getUserById(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User userById = mapper.getUserById(2);

        System.out.println(userById.toString());
        sqlSession.close();
    }
    //增删改  需要提交事物
    @Test
    public void addUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int i = mapper.addUser(new User(6,"小明","123456"));
        if (i > 0) {
            System.out.println("插入成功！");
        }
        //提交事务
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void updateUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int count = mapper.updateUser(new User(5, "小红", "333333"));
        if (count > 0) {
            System.out.println("修改成功！");
        }
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void deleteUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int count = mapper.deleteUser(6);
        if (count > 0) {
            System.out.println("删除成功！");
        }
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void addUser2() {
        try (SqlSession sqlSession = MybatisUtils.getSqlSession()){
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            Map<String, Object> map = new HashMap<>();
            map.put("userid",7);
            map.put("username","小兰");
            map.put("userPassword","145632");
            mapper.addUser2(map);
            sqlSession.commit();
        }

    }
}

小结：

• 所有的增删改操作都需要提交事务！
• 接口所有的普通参数，尽量都写上@Param参数，尤其是多个参数时，必须写上！
• 有时候根据业务的需求，可以考虑使用map传递参数！
• 为了规范操作，在SQL的配置文件中，我们尽量将Parameter参数和resultType都写上！

模糊查询

第一步

先在UserMapper.java接口中添加具体要实现的方法

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

第二步

在UserMapper.xml中添加上面接口要实现的标签

   <select id="getUserLike" resultType="com.shadow.pojo.User">
        select * from mybatis.user where name like #{value};
    </select>

第三步

在测试类中进行测试

 @Test
    public void getUserLike(){
        try(SqlSession sqlSession = MybatisUtils.getSqlSession()){
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            List<User> userList = mapper.getUserLike("%小%");

            for (User user : userList) {
                System.out.println(user);
            }
        }
    }