二、Mybatis CURD操作

经过上面的操作，是不是对Mybatis的操作有点感觉了，不如趁热打铁把curd操作都实现一遍吧。

3.1、Select

1. 在dao/UserMapper中增加查询接口

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

2. 在dao/UserMapper.xml中增加实体类配置

   其中id就是配置文件对应的接口，parameterType参数类型，resultType返回结果类型

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

3. 测试

   @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();
   }

3.2、Update

1. 在dao/UserMapper中增加查询接口

   //更新用户信息
   int updateUser(User user);

2. 在dao/UserMapper.xml中增加实体类配置

   <update id="updateUser" parameterType="com.luca.pojo.User">
     update mybatis.user set `name` = #{name}, pwd = #{pwd} where id = #{id};
   </update>

3. 测试

   @Test
   public void TestUpdateUser() {
     SqlSession sqlSession = MybatisUtils.getSqlSession();
     UserMapper mapper = sqlSession.getMapper(UserMapper.class);
     int resp = mapper.updateUser(new User(3, "哈哈", "987890"));
     if (resp>0) {
       System.out.println("update success!");
     }
     sqlSession.commit();
     sqlSession.close();
   }

3.3、Insert

1. 在dao/UserMapper中增加查询接口

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

2. 在dao/UserMapper.xml中增加实体类配置

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

3. 测试

   @Test
   public void TestAddUser() {
     SqlSession sqlSession = MybatisUtils.getSqlSession();
     UserMapper mapper = sqlSession.getMapper(UserMapper.class);
     int resp = mapper.addUser(new User(4, "嘻嘻", "000000"));
     if (resp>0) {
       System.out.println("update success!");
     }
     sqlSession.commit();
     sqlSession.close();
   }

3.4、Delete

1. 在dao/UserMapper中增加查询接口

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

2. 在dao/UserMapper.xml中增加实体类配置

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

3. 测试

   @Test
   public void TestDeleteUser() {
     SqlSession sqlSession = MybatisUtils.getSqlSession();
     UserMapper mapper = sqlSession.getMapper(UserMapper.class);
     int resp = mapper.deleteUser(4);
     if (resp>0) {
       System.out.println("delete success!");
     }
     sqlSession.commit();
     sqlSession.close();
   }

需要注意的是，增删改的操作都需要在操作完成后提交事物，操作才会持久化到数据库中 sqlSession.commit()

3.5、万能的Map

在数据库的表字段较多时，使用对象的方式来执行sql会很麻烦，我们只需要去更新表中的部分字段；这个时候我们可以考虑使用Map来传递sql的参数，看下面的例子：

1. 在dao/UserMapper中增加查询接口

   //更新用户信息(使用map)
   int updateUserWithMap(Map<String, Object> map);

2. 在dao/UserMapper.xml中增加实体类配置

   <update id="updateUserWithMap" parameterType="map">
     update mybatis.user set `name` = #{username}, pwd = #{password} where id = #{id};
   </update>

3. 测试

   @Test
   public void TestUpdateUserWithMap() {
     SqlSession sqlSession = MybatisUtils.getSqlSession();
     UserMapper mapper = sqlSession.getMapper(UserMapper.class);
     Map<String, Object> map = new HashMap<>();
     map.put("id", 1);
     map.put("username", "didi");
     map.put("password", "dada");
     int i = mapper.updateUserWithMap(map);
     if (i>0) {
       System.out.println("update success.");
     }
     sqlSession.commit();
     sqlSession.close();
   }

可以看到，使用Map方式传递参数，可以只传递需要的参数，并且不需要和实体类中的表字段名称对应，这在实际开发中可以提供很多的便利。

3.6、模糊查询

在数据库中添加两个姓李的用户，接下来我们要查询出他们

1. 方式一：在传入的查询字符串中加上通配符

   //模糊查询
   List<User> getUsersByName(String name);

   <select id="getUsersByName" parameterType="String" resultType="com.luca.pojo.User">
     select * from mybatis.user where name like #{name}
   </select>

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

   查询结果

   Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
   User{id=2, name='李四', pwd='654321'}
   User{id=4, name='李武', pwd='987345'}
    
   Process finished with exit code 0

2. 方式一：在配置文件中增加前后通配符

   //模糊查询
   List<User> getUsersByName(String name);

   <select id="getUsersByName" parameterType="String" resultType="com.luca.pojo.User">
     select * from mybatis.user where name like "%"#{name}"%"
   </select>

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

   查询结果和上面的相同