MyBatis-2-CRUD操作及拓展

​ namespace中的包名必须和Dao/Mapper接口的包名一致！

选择，查询语句

• id，就是对应的namespace中的方法名
• resultType，是sql语句执行的返回值！
• parameterType，是参数的类型

1. 编写接口

   UserMapper.java

   public interface UserMapper {
       
       List getUserList();
   
       
       User getUserById(int id);
   
       
       int addUser(User user);
   
       
       int updateUser(User user);
   
       
       int deleteUserById(int id);
   }
   

2. 编写对应的mapper中的sql语句

   UserMapper.xml

   
   
   
   
       
       
           select * from mybatis.user
       
   
       
           select * from mybatis.user where id = #{id}
       
   
       
       
           insert into mybatis.user (id, name, pwd) values (#{id}, #{name}, #{pwd})
       
   
       
           update mybatis.user
           set name = #{name}, pwd = #{pwd}
           where id = #{id};
       
   
       
           delete from mybatis.user where id = #{id};
       
   
   
   

3. 测试

   UserMapperTest.java

   public class UserMapperTest {
       @Test
       public void testGetUserList() {
           //第1步：获取SqlSession对象
           SqlSession sqlSession = MybatisUtils.getSqlSession();
           //第2步：执行sql
           //method1:
           UserMapper mapper = sqlSession.getMapper(UserMapper.class);
           List userList = mapper.getUserList();
   
           //method2:
           //List userList = sqlSession.selectList("com.ano.dao.UserMapper.getUserList");
   
           for (User user : userList) {
               System.out.println(user);
           }
           //第3步：关闭sqlSession
           sqlSession.close();
       }
   
       @Test
       public void testGetUserById() {
           SqlSession sqlSession = MybatisUtils.getSqlSession();
           UserMapper mapper = sqlSession.getMapper(UserMapper.class);
           User user = mapper.getUserById(1);
           System.out.println(user);
           sqlSession.close();
       }
   
       
       @Test
       public void testAddUser() {
           SqlSession sqlSession = MybatisUtils.getSqlSession();
           UserMapper mapper = sqlSession.getMapper(UserMapper.class);
           User user = new User(4,"babe","123456");
           int i = mapper.addUser(user);
           if(i > 0) {
               System.out.println("添加用户成功");
           }
           //提交事务
           sqlSession.commit();
           sqlSession.close();
       }
       
       @Test
       public void testUpdateUser() {
           SqlSession sqlSession = MybatisUtils.getSqlSession();
           UserMapper mapper = sqlSession.getMapper(UserMapper.class);
           User baby = new User(4, "baby", "111111");
           int i = mapper.updateUser(baby);
           if(i>0) {
               System.out.println("修改用户成功");
           }
           sqlSession.commit();
           sqlSession.close();
       }
   
       @Test
       public void testDeleteUserById(){
           SqlSession sqlSession = MybatisUtils.getSqlSession();
           UserMapper mapper = sqlSession.getMapper(UserMapper.class);
           int i = mapper.deleteUserById(4);
           if(i > 0) {
               System.out.println("删除成功！");
           }
           sqlSession.commit();
           sqlSession.close();
       }
   }
   

4. 注意点：增删改需要提交事务！！！

​ 如果实际项目开发中，实体类、或者数据库中的表和字段或者参数过多，应当考虑使用Map!

• 以修改用户密码为例：

  int updateUserByMap(Map map);
  

  
      update mybatis.user set pwd = #{password} where id = #{userid}
  
  

• 测试：

  @Test
  public void testUpDateUserByMap() {
      SqlSession sqlSession = MybatisUtils.getSqlSession();
      UserMapper mapper = sqlSession.getMapper(UserMapper.class);
      Map map = new HashMap<>();
      map.put("userid",1);
      map.put("password","88888888");
      int i = mapper.updateUserByMap(map);
      if(i>0) {
          System.out.println("修改密码成功");
      }
      sqlSession.commit();
  
      sqlSession.close();
  }
  

• Map传递参数，直接在sql中取出key即可；【parameterType=“map”】

• 而实体类对象传递参数，直接在sql中取对象的属性即可；【parameterType=“com.ano.pojo.User”】

• 只有一个基本类型参数的情况下，可以直接在sql中取到。

• 多个参数用Map，或者用注解。

两种方式：

• 在Java代码执行的时候，传递通配符% %

  List userList = mapper.getUserByName("%a%");
  

• 在sql拼接中使用通配符

  select * from mybatis.user where name like "%"#{value}"%"
  

完整代码示例：

1. UserMapper.java

   List getUserByName(String value);
   

2. UerMapper.xml

   
     
       
           select * from mybatis.user where name like #{value}
       
   
   
   

3. 测试代码：

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