MyBatis-02-简单的CRUD操作

三、CRUD

• 注意：增删改必须要当做事务进行执行，执行完之后要记得提交，如果执行失败记得回滚

3.1 Retrieve(select)

• mapper接口新增

  User getUserById(int id);
  User getUserByName(String name);
  

• xml配置文件新增

  <select id="getUserById" parameterType="int" resultType="com.pbx.pojo.User">
      select * from `mybatis`.`user` where `id` = #{id}
  </select>
  <select id="getUserByName" parameterType="String" resultType="com.pbx.pojo.User">
      select * from `mybatis`.`user` where `name` = #{name};
  </select>
  

• 测试

  @Test
  public void testSelect() {
      SqlSession sqlSession = MyBatisUtils.getSqlSession();
      UserMapper mapper = sqlSession.getMapper(UserMapper.class);
      User user = mapper.getUserById(1);
      System.out.println(user);
      user = mapper.getUserByName("bruce");
      System.out.println(user);
      sqlSession.close();
  }
  

  

3.2 Create(insert)

• mapper接口新增

  int addUser(User user);
  

• xml配置文件新增

  <insert id="addUser" parameterType="com.pbx.pojo.User">
      insert into `mybatis`.`user` (`id`, `name`, `pwd`) values (#{id}, #{name}, #{pwd});
  </insert>
  

• 测试

  @Test
  public void testInsert() {
      SqlSession sqlSession = MyBatisUtils.getSqlSession();
      UserMapper mapper = sqlSession.getMapper(UserMapper.class);
      mapper.addUser(new User(4, "华某人", "woshishabi"));
      User user = mapper.getUserById(4);
      System.out.println(user);
      sqlSession.commit();
      sqlSession.close();
  }
  

  

3.3 Update

• mapper接口新增

  int updateUserName(User user);
  

• xml配置文件新增

  <update id="updateUserName" parameterType="com.pbx.pojo.User">
      update `mybatis`.`user` set `name` = #{name} where id = #{id};
  </update>
  

• 测试

  @Test
  public void testUpdate() {
      SqlSession sqlSession = MyBatisUtils.getSqlSession();
      UserMapper mapper = sqlSession.getMapper(UserMapper.class);
      System.out.println("更新前：");
      System.out.println(mapper.getUserById(4));
      mapper.updateUserName(new User(4,"小猪","woshishabi"));
      System.out.println("更新后：");
      System.out.println(mapper.getUserById(4));
      sqlSession.commit();
      sqlSession.close();
  }
  

  

3.4 Delete

• mapper接口新增

  int deleteUserById(int id);
  

• xml配置文件新增

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

• 测试

  @Test
  public void testDelete(){
      SqlSession sqlSession = MyBatisUtils.getSqlSession();
      UserMapper mapper = sqlSession.getMapper(UserMapper.class);
      System.out.println("删除前：");
      System.out.println(mapper.getUserById(4));
      mapper.deleteUserById(4);
      System.out.println("删除后：");
      System.out.println(mapper.getUserById(4));
      sqlSession.commit();
      sqlSession.close();
  }
  

  

3.5 模糊查询

• 注意，在进行模糊查询时，由于需要拼接通配符，所以可能会导致潜在的SQL注入的问题

• 建议

  • 在Java业务逻辑代码中，传递通配符

    string wildcardname = “%smi%”;
    list<name> names = mapper.selectlike(wildcardname);
    
    <select id=”selectlike”>
    	select * from foo where bar like #{value}
    </select>
    

  • 在SQL拼接中使用通配符，

    string wildcardname = “smi”;
    list<name> names = mapper.selectlike(wildcardname);
    
    <select id=”selectlike”>
    	select * from foo where bar like "%"#{value}"%"
    </select>
    

  • 以上方法均目的是在SQL语句的构造时，写死通配符。防止用户奇怪的输入从而导致sql注入的漏洞

• 永远不要相信用户的任何输入，安全第一

3.6 奇淫技巧——通过map传递参数

• 在某些时候，实体类中的属性过多，但是我们又只需要修改其中的几个，那么，在业务执行的时候，传递实体类对象会导致操作复杂。

• mapper接口、

  int updateUser(Map<String, Object> map);
  

• xml配置

  <update id="updateUser" parameterType="map">
      update `mybatis`.`user` set `name` = #{uname}, `pwd` = #{upwd} where id = #{uid};
  </update>
  

• 测试

  public void testUpdate2(){
      SqlSession session = MyBatisUtils.getSqlSession();
      UserMapper mapper = session.getMapper(UserMapper.class);
      Map<String, Object> map = new HashMap<>();
      map.put("uid", 1);
      map.put("uname", "hello");
      map.put("upwd", "myu");
      User user = mapper.getUserById(1);
      System.out.println(user);
      mapper.updateUser(map);
      user = mapper.getUserById(1);
      System.out.println(user);
      session.commit();
      session.close();
  }
  

  

• 注意：

  • map中的key一定要和xml配置中设置的参数名相匹配