Mybatis -9.28 learning notes - pre stage optimization

In the first two stages, we learned how to 1. Create a project and 2. Use the add, delete, modify and query commands of the database in the Mybatis framework. This small chapter is mainly an optimization scheme to solve some problems that we may encounter in the first two chapters and some practical problems in the actual project

1. Use Map object

In the second chapter, we learned that in Mybatis, we can obtain parameters by mapping according to the establishment of entity classes, so as to simplify obtaining values and transmitting parameters through complex methods in JDBC. For example, create a User class and transfer values directly through the mapping method in UserMapper.xml, such as:

<!--first-->
<insert id="addUser" parameterType="com.szy.pojo.User" >
    insert into mybatis.user (id, name, pwd) values (#{id}, #{name}, #{pwd})
</insert>
<!--the second-->
<update id="updateUser" parameterType="com.szy.pojo.User" >
        update mybatis.user set name = #{name}, pwd = #{pwd} where id = #{id}
</update>

However, at this time, we find that there are other disadvantages:

1. When we use related methods, we need a new User object every time, and we need to assign the attribute values in the new object one by one. However, if an entity class has many attributes, it is very troublesome to build a new entity object every time.

   @Test
   public void addUser(){
   
       SqlSession sqlSession = MybatisUtils.getSqlSession();
       UserMapper mapper = sqlSession.getMapper(UserMapper.class);
       int res = mapper.addUser(new User(4, "Wang Wu", "123456"));
       if (res > 0){
           System.out.println("Insert successful");
       }
       sqlSession.commit();
       sqlSession.close();
   }
   
   @Test
   public void updateUser(){
       SqlSession sqlSession = MybatisUtils.getSqlSession();
       UserMapper mapper = sqlSession.getMapper(UserMapper.class);
       //Here, if we do not modify so many attribute values, we still need to new a complete User object
       int res = mapper.updateUser(new User(4 , "Wang Wu", "654321"));
       if (res > 0){
           System.out.println("Update succeeded!");
       }
       sqlSession.commit();
       sqlSession.close();
   
   }
   

2. Since Mybatis uses the method of attribute mapping, the attribute name in * * #{} must be the same as that of the User class * *. However, during the long-term project construction, we may forget that this is very unfavorable to our later development.

In order to solve the above problems, we can create a Map object to achieve the corresponding transfer effect!!!

1.1 solution

1. Modify the update method of the UserMapper class

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

2. Modify the parameterType attribute in the usermapper.xml file and change the value to map

   <!--here#The corresponding attribute name in {} can be written freely, as long as it is the same as the key of the map when passing the value -- >
   <update id="updateUser" parameterType="map" >
       update mybatis.user set name = #{username}, pwd = #{userpwd} where id = #{userid}
   </update>
   

3. When passing values, use the map object to pass values

   @Test
   public void updateUser(){
       SqlSession sqlSession = MybatisUtils.getSqlSession();
       UserMapper mapper = sqlSession.getMapper(UserMapper.class);
       Map<String, Object> map = new HashMap<String, Object>();
       map.put("userid", 1);
       map.put("username", "limt33");
       map.put("userpwd", "654321" );
       //We just need to add the values we need, not every attribute
       int res = mapper.updateUser(map);
       if (res > 0){
           System.out.println("Update succeeded!");
       }
       sqlSession.commit();
       sqlSession.close();
   
   }
   

2. Fuzzy query problem

In order to avoid the problem of sql injection in the future, we need to be vigilant about the use of #{} and ${} in xml

• #{} is equivalent to a placeholder at run time?. The operation sequence is to execute sql statements before taking values (prevent sql injection)

• ${} is equivalent to string splicing at run time. The operation sequence is to take values first and then execute sql statements (sql injection is easy to occur), such as:

   /*All user data will be queried*/
   select * from mybatis.user where id = 1 or 1=1 
  

In real life, we try to use #{} the method.

2.1 solution

1. Using wildcards in sql splicing operations

   <!--Fuzzy query statement-->
   <select id="getUserLike" resultType="com.szy.pojo.User">
       select * from mybatis.user where name like "%"#{valuse}"%"
   </select>
   

Used in UserMapperTest:

@Test
public void getUserLike(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();

    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    List<User> userList = mapper.getUserLike("Partial value of fuzzy query");

    for (User user : userList) {
        System.out.println(user);
    }
    
    sqlSession.close();

}

1. During Java code execution, wildcard%% is used when passing values

   <!--Fuzzy query statement-->
   <select id="getUserLike" resultType="com.szy.pojo.User">
       select * from mybatis.user where name like #{valuse}
   </select>
   

Used in UserMapperTest:

@Test
public void getUserLike(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();

    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    List<User> userList = mapper.getUserLike("%Partial value of fuzzy query%");

    for (User user : userList) {
        System.out.println(user);
    }

    sqlSession.close();

}

This article is the author's learning experience. In the follow-up, the content of Mybatis will be continuously updated. The learning time is short, the content is shallow, and if there are mistakes, I hope to correct them. Welcome to discuss them together