Mybatis maps database vARCHAR fields to List properties of DO objects

Sometimes a DO object does not correspond exactly to a database table field. For example, DO uses the List attribute and the corresponding database field is a string separated by commas or Spaces. To avoid introducing new fields and inelegant conversion logic specifically for field conversion, it is necessary to map the list attribute of DO to the string in the database.

Database table structure

The database table uses the DEPARTMENTS table from the mysql use case library employees

The table structure can be expressed as follows

create table departments(
    dept_no    char(4)      not null   primary key,
    dept_name  varchar(40)  not null
);
Copy the code

The default content is

The target

For example, if we want to read the last d009, I want to receive the dept_name field with List

, split the String with Spaces, Departments(deptNo= D009, deptName=[Customer, Service])

coding

The following example contains four classes/interfaces

• Departments
• DepartmentsDao
• DepartmentsDaoTest
• ListStringTypeHandler

@Data
@ToString
@EqualsAndHashCode
@NoArgsConstructor
@AllArgsConstructor
public class Departments {
    private String deptNo;
    private List<String> deptName;
}
Copy the code

@Mapper
public interface DepartmentsDao {

    /** * query ** by id@param deptNo deptNo
     * @returnObject * /
    @Select("select * from departments where dept_no = #{deptNo}")
    @Results({@Result(property = "deptNo", column = "dept_no"), @Result(property = "deptName", column = "dept_name", typeHandler = ListStringTypeHandler.class)})
    Departments selectById(@Param("deptNo") String deptNo);

    /** * query ** based on names@param names names
     * @returnObject * /
    @Select("select * from departments where dept_name = #{names, " + "typeHandler=com.yhh.play.mybatis.dao.type.handler.ListStringTypeHandler}")
    @Results({@Result(property = "deptNo", column = "dept_no"), @Result(property = "deptName", column = "dept_name", typeHandler = ListStringTypeHandler.class)})
    Departments selectByNames(@Param("names") List<String> names);

    /** * insert **@paramThe departments object *@returnSuccessful */
    @Insert("insert into departments values (#{deptNo}, #{deptName," + "typeHandler=com.yhh.play.mybatis.dao.type.handler.ListStringTypeHandler})")
    boolean insert(Departments departments);

    /** * delete ** according to deptNo@param deptNo deptNo
     * @returnSuccessful */
    @Delete("delete from departments where dept_no = #{deptNo}")
    boolean deleteByDeptNo(@Param("deptNo") String deptNo);

}
Copy the code

@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest(classes = Application.class)
public class DepartmentsDaoTest {

    @Autowired
    private DepartmentsDao departmentsDao;

    @Test
    public void test(a) {
        String deptNo = "d101";
        List<String> deptName = Arrays.asList("a"."b"."c");
        Departments departments = newDepartments(deptNo, deptName); departmentsDao.deleteByDeptNo(deptNo); Assert.assertTrue(departmentsDao.insert(departments)); Assert.assertEquals(departments, departmentsDao.selectById(deptNo)); Assert.assertEquals(departments, departmentsDao.selectByNames(deptName)); Assert.assertTrue(departmentsDao.deleteByDeptNo(deptNo)); Assert.assertNull(departmentsDao.selectById(deptNo)); }}Copy the code

public class ListStringTypeHandler extends BaseTypeHandler<List<String>> {

    private static final String DELIM = "";

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i,
                                    List<String> parameter, JdbcType jdbcType)
            throws SQLException {
        String value = StringUtils.collectionToDelimitedString(parameter, DELIM);
        ps.setString(i, value);
    }

    @Override
    public List<String> getNullableResult(ResultSet rs, String columnName)
            throws SQLException {
        String value = rs.getString(columnName);
        return Arrays.asList(StringUtils.tokenizeToStringArray(value, DELIM));
    }

    @Override
    public List<String> getNullableResult(ResultSet rs, int columnIndex)
            throws SQLException {
        String value = rs.getString(columnIndex);
        return Arrays.asList(StringUtils.tokenizeToStringArray(value, DELIM));
    }

    @Override
    public List<String> getNullableResult(CallableStatement cs, int columnIndex)
            throws SQLException {
        String value = cs.getString(columnIndex);
        returnArrays.asList(StringUtils.tokenizeToStringArray(value, DELIM)); }}Copy the code

As you can see from the code example above, you can convert the List attribute in a DO object to the VARCHAR type in the database by customizing TypeHandler

It is also possible to parse the VARCHAR type in the database into the List property of a DO object

ListStringTypeHandler (Mybatis) ¶ Mybatis (Mybatis) ¶ ListStringTypeHandler (Mybatis) ¶ Mybatis (Mybatis) ¶ Used to convert javaType to jdbcType, the last three methods are used to convert jdbcType to javaType. Mybatis has a lot of TypeHandler, but unfortunately I did not find the List and vARCHar interoperability implementation class, if anyone found a better solution please feel free to comment ~