Learning content: using annotation development (CRUD)

1. How to use annotations

1.1. Annotation is implemented on the interface

1.2. The interface needs to be bound in the core configuration file

1.3 preparation of test procedures

We can use annotations without writing usermapper XML (can be understood as interface implementation class) file.

Detailed execution process of Mybatis:

Essence: reflection mechanism implementation
Bottom layer: dynamic proxy

Using annotations to map simple statements will make the code more concise, but for slightly more complex statements, Java annotations will not only fail, but also make your already complex SQL statements more chaotic. Therefore, if you need to do some very complex operations, it is best to use XML to map statements.

2. Realize CRUD with annotations (add, delete, modify and query)

Data in database:

2.1. We can automatically submit transactions when the tool class is created

public static SqlSession getSqlSession() {
   //When true is set here, the transaction is automatically committed
   //You don't have to write code manually (sqlSession.commit();)
   return sqlSessionFactory.openSession(true);
}

2.2. Query (query user by id)

• Write the interface and add comments

When a method has multiple parameters, all parameters must be preceded by@Param("Parameter name")annotation
 Only basic data types need to be added@Param()，The reference data type does not need to be added
sql In a statement#The property name in {property name} must be consistent with the property name in @ Param("property name"), otherwise it will not be found

//Query user by id
//When there are multiple parameters in the method, all parameters must be preceded by @ Param("parameter name") annotation
//Only the basic data type needs to be added with @ Param(), and the reference data type does not need to be added
//The attribute name in #{attribute name} in sql statement must be consistent with the attribute name in @ Param("attribute name"), otherwise it will not be found
@Select("select * from user where id=#{id}")
User getUserById(@Param("id") int id);

• Write test program

//Query user by id
@Test
public void getUserById(){
   SqlSession sqlSession = MybatisUtils.getSqlSession();
   UserMapper mapper = sqlSession.getMapper(UserMapper.class);
   User userById = mapper.getUserById(1);
   System.out.println(userById);
   sqlSession.close();
}

result:

As mentioned earlier, the reason why the password cannot be found here is that the attribute name in the database is pwd and the attribute name in the entity class is password. The two are inconsistent, so the password cannot be found( For details, see).

However, there is no result mapping set that cannot be used. The sql statement can only be changed to select id, name, PWD as password from mybatis User where id = #{id}, but it will be troublesome if there are many attributes in the entity class, so try to keep the attribute names in the database and the entity class consistent.

Modified results:

2.3. Add

• Write the interface and add comments

@Insert("insert into user(id,name,pwd) values (#{id},#{name},#{password})")
int addUser(User user);

• Write test program

@Test
public void addUser(){
  SqlSession sqlSession = MybatisUtils.getSqlSession();
  UserMapper mapper = sqlSession.getMapper(UserMapper.class);
  int result = mapper.addUser(new User(4,"pc","123"));
  if (result>0){
       System.out.println("Successfully added!");
   }
  sqlSession.close();
}

result:

2.4 modification

• Write the interface and add comments

@Update("update user set name=#{name},pwd=#{password} where id=#{id}")
int updateUser(User user);

• Write test program

@Test
    public void updateUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int result = mapper.updateUser(new User(4,"lb","123"));
        if (result>0){
            System.out.println("Modification succeeded!");
        }
        sqlSession.close();
    }

result:

2.5 deletion

• Write the interface and add comments

//delete
@Delete("delete from user where id=#{id}")
int deleteUser(@Param("id") int id);

• Write test program

@Test
    public void deleteUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int result = mapper.deleteUser(4);
        if (result>0){
            System.out.println("Delete succeeded!");
        }
        sqlSession.close();
    }

result:

3. Comment on @ Param()

• Parameters of basic type or String type need to be added
• Reference types do not need to be added
• If there is only one basic type, it can be ignored, but it is recommended that everyone add it!
• What we refer to in SQL is the attribute name set in @ Param()!

4. #{} and ${} differences

Extension:

• The data passed in with ${is directly displayed in the generated sqI. As in the above statement, with role. Id = ${roleld, JDBC type = int & Er}, the value of sq | is role during parsing_ Id = roleid, an error will be reported during execution;

• The ${method cannot prevent sq| injection;

• $- generally used to input the incoming database objects, such as database table names;

• Try to use #} when you can use #};

be careful:
When using the order by dynamic parameter in mybats sorting, it should be noted that ${} is used instead of #{}.