CRUD of MyBatis

1. namespace

The package name in the namespace must be consistent with the package name of the Dao/Mapper interface.

2. Select

• id is the method name in the corresponding namespace.
• resultType is the return value of Sql statement execution.
• parameterType is the parameter type.

2.1 Write interface

// Query users based on ID
User getUserById(int id);

2.2 Write the sql statement in the corresponding mapper

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

2.3 Test

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

3. Insert

3.1 Write interface

// Add a user
int addUser(User user);

3.2 Write the sql statement in the corresponding mapper

<!-- The attributes in the object can be taken out directly -->
<insert id="addUser" parameterType="com.ping.pojo.User">
  insert into mybatis.user (id, name, pwd) values (#{id},#{name},#{pwd});
</insert>

3.3 Test

// Additions, deletions and modifications need to commit the transaction
@Test
public void addUser() {
  SqlSession sqlSession = MybatisUtils.getSqlSession();
  UserMapper mapper = sqlSession.getMapper(UserMapper.class);
  int res = mapper.addUser(new User(4,"TOM","123456"));
  if (res>0){
    System.out.println("Inserted successfully")；
  }
  // Commit transaction
  sqlSession.commit();
  sqlSession.close();
}

4. Update

4.1 Write interface

// Modify user
int updateUser(User user);

4.2 Write the sql statement in the corresponding mapper

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

4.3 Test

@Test
public void updateUser() {
  SqlSession sqlSession = MybatisUtils.getSqlSession();
  UserMapper mapper = sqlSession.getMapper(UserMapper.class);
  mapper.updateUser(4,"TOM","123123");
  sqlSession.commit();
  sqlSession.close();
}

5. Delete

5.1 Write interface

// Delete a user
int deleteUser(int id);

5.2 Write the sql statement in the corresponding mapper

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

5.3 Test

@Test
public void deleteUser() {
  SqlSession sqlSession = MybatisUtils.getSqlSession();
  UserMapper mapper = sqlSession.getMapper(UserMapper.class);
  mapper.deleteUser(4);
  sqlSession.commit();
  sqlSession.close();
}

Note that additions, deletions and changes need to commit the transaction

5.4 Analysis error

• Tag matching error.
• Resource binding mapper, need to use path.
• The program configuration file must conform to the specification.
• NullPointerException, the resource is not registered.
• There are garbled Chinese characters in the output xml file.
• Maven resource export problem.

6. Map

If there are too many fields or parameters in the entity class or table in the database, you should consider using Map.

6.1 Write interface

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

6.2 Write the sql statement in the corresponding mapper

<!-- The attributes in the object can be directly taken out and pass the key of the map -->
<insert id="addUser" parameterType="map">
  insert into mybatis.user (id, pwd) values (#{userid},#{password});
</insert>

6.3 Test

@Test
public void addUser2(){
  SqlSession sqlSession = MybatisUtils.getSqlSession();
  UserMapper mapper = sqlSession.getMapper(UserMapper.class);
  Map<String, Object> map = new HashMap<String, Object>();
  map.put("userid",5);
  map.put("password","2222333");
  mapper.addUser2(map);
  sqlSession.close();
}

• Map passes parameters, and you can directly retrieve the key in sql. parameterType=”map”
• Objects pass parameters, and you can directly get the properties of the object in sql. parameterType=”Object”
• When there is only one basic type parameter, you can get it directly in sql. Can not write
• Use Map or annotation for multiple parameters.