mybatis-CRUD

一.在UserMapper接口中添加方法

//根据id查询用户
User getUser(int id);
 
//增加用户
int addUser(User user);
 
//修改用户
int updateUser(User user);
 
//删除用户
int deleteUser(int id);

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

<mapper namespace="com.lv.dao.UserMapper">
    <select id="getUserList" resultType="cnm">
        select * from mybatis.user
    </select>
 
    <select id="getUser" resultType="com.lv.pojo.User">
        select * from mybatis.user where id = #{id}
    </select>
 
    <insert id="addUser" parameterType="com.lv.pojo.User">
        insert into User(id,name,pwd) values (#{id},#{name},#{pwd})
    </insert>
 
    <update id="updateUser" parameterType="com.lv.pojo.User">
        update user set name = #{name},pwd = #{pwd} where id = #{id}
    </update>
 
    <delete id="deleteUser" parameterType="com.lv.pojo.User">
        delete from user where id = #{id};
    </delete>
</mapper>

三.测试

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

@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<User> selectUser(String name);

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

<select id="selectUser" resultType="com.lv.pojo.User">
    select *from USER where name like #{user};
</select>

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

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

<select id="selectUser" resultType="com.lv.pojo.User">
    select *from USER where name like "%"#{user}"%";
</select>

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

2.使用Map传递参数

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

//删除用户(使用Map)
int deleteUserUseMap(Map<String,Object> map);

<delete id="deleteUserUseMap" parameterType="int">
    delete from user where id = #{id}
</delete>

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