mybatis-CRUD

一.在UserMapper接口中添加方法

//根据id查询用户
User getUser(int id);

//增加用户
int addUser(User user);

//修改用户
int updateUser(User user);

//删除用户
int deleteUser(int id);

二.在UserMapper.xml中添加对应的sql语句

    
        select * from mybatis.user
    

    
        select * from mybatis.user where id = #{id}
    

    
        insert into User(id,name,pwd) values (#{id},#{name},#{pwd})
    

    
        update user set name = #{name},pwd = #{pwd} where id = #{id}
    

    
        delete from user where id = #{id};
    

三.测试

注意:增删改需要提交事务

@Test
public void getUser(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    User user = mapper.getUser(1);
    System.out.println(user);
    sqlSession.close();
}

@Test
public void addUser(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    int admin4 = mapper.addUser(new User(4, "admin4", "123456"));
    //提交事务
    sqlSession.commit();
    sqlSession.close();
}

@Test
public void updateUser(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    int cnm = mapper.updateUser(new User(4, "cnm", "696969"));
    sqlSession.commit();
    sqlSession.close();
}

@Test
public void deleteUser(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    mapper.deleteUser(4);
    sqlSession.commit();
    sqlSession.close();
}

四.补充

1.模糊查询:

//根据名字模糊查询
List selectUser(String name);

第一种方法:在传值时加入通配符

    select *from USER where name like #{user};

@Test
public void selectUser(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    List userList = mapper.selectUser("%2%");
    for (User user : userList) {
        System.out.println(user);
    }
    sqlSession.close();
}

第二种方法:在SQL语句中拼接通配符,会引起sql注入

    select *from USER where name like "%"#{user}"%";

@Test
public void selectUser(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    List userList = mapper.selectUser("2");
    for (User user : userList) {
        System.out.println(user);
    }
    sqlSession.close();
}

2.使用Map传递参数

适用于实体类的属性有很多,但是只需要使用其中少部分属性的情况.只需用map来传递需要使用的属性,避免创建对象时产生的那些不必要的属性.

//删除用户(使用Map)
int deleteUserUseMap(Map map);

    delete from user where id = #{id}

@Test
public void deleteUserUseMap(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    Map map = new HashMap<>();
    map.put("id",3);
    mapper.deleteUserUseMap(map);
    sqlSession.commit();
    sqlSession.close();
}