二、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. //更新使用者資訊
   int updateUser(User user);
              

2. <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. //添加使用者
   int addUser(User user);
              

2. <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. //删除使用者
   int deleteUser(int id);
              

2. <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. //更新使用者資訊(使用map)
   int updateUserWithMap(Map<String, Object> map);
              

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