background

I have just changed my job recently, and the interview questions will be sorted out and put up slowly at the weekend. Let's talk about this problem first. Just entering the new company, the leader directly asked me to participate in a project he was working on, Spring Boot+MyBatis+ Shiro +Vue. Anyway, these technology stacks are not the focus. The key point is that the leader made a multi table Association query. The foreground obtained result list always displays all the fields of the main table, and the two fields of the slave table to be displayed are empty. Let me see what's going on. This problem bothered me for two days. It's also my own dish. I've just worked for a year. There's a lot of code and design logic. Fortunately, this problem has finally been solved. It feels great to overcome difficulties (light spray of vegetables and chicken).

First, summarize

Record the solutions to such problems when searching online:

First, make sure that the mapper.xml tag corresponds to the method name in mapper.java and the package path where it is located
Secondly, make sure that the sql statement field database field - sql query - entity class attribute is the same

If there are no problems above, possible reasons:
① Database field name used_ Underline, but the entity class attribute name is named by the hump (in this case, if you do not want to write mybatis-config.xml, you can directly configure and enable hump naming in application.yml):

mybatis:
  mapper-locations: classpath*:mapper/*/*Mapper.xml
  configuration:    # Open hump naming rules
    map-underscore-to-camel-case: true

② Database field name used_ Underline. The entity class attribute name is the same as it. It is also an underline. At this time, pay attention to turn off hump naming configuration!!! Otherwise, no match is found and the result is null.
It's best to directly touch the same, and the province should also be equipped with hump naming.

③ Suppose tables a, B and C and the query result set are calculated as aBc. It is possible that you can map the fields of the relational table aBc to the attributes of table A. of course, you can't find anything except the fields of table a. There are two solutions:

1. Re create an aBc.java according to the linked table query result set aBc, which is the same as the principle of VO entity class (creation is troublesome, but the level is clear);
2. Use resultMap to map entity tables to relational tables, that is, define class objects (or collections) of B and C in relational table entity class A. However, this involves the association mapping of resultMap. (the function of id, result, association and collection tags) I'll summarize it again at the weekend. (convenient and fast, but the original entity class is modified, which is not very good. My humble opinion)

④ It's also my reason. The leader has been using the implementation method of the query list to call mapper.xml in mapper.java to query the linked table (i.e. the XML file is OK, but the method corresponding to the id is wrong). Now it seems that the problem is very simple. It's mainly used to sort out the whole set of code business and call logic most of the time... But I learned a lot of other knowledge by the way. It was a worthwhile trip, ha ha ha.

Personal problems and causes

First of all, the project uses a lot of methods to package the query result set into VO class (it's my dish, the first contact in this way), and then take a while to understand it. It is probably different from the entity class corresponding to the database table, which is specially used for user-defined result set packaging. Post a few pieces of code to smooth out the calling process:

1. Controller layer interface

    public ResponseBean findCkdCartonList(
            @RequestParam(value = "pageNum", defaultValue = "1") Integer pageNum,
            @RequestParam(value = "pageSize") Integer pageSize,
            CkdCartonVO ckdCartonVO) {
// ① Here, the findCkdCartons () method of the Service layer is called           
        PageVO<CkdCartonVO> ckdCartonList = ckdCartonService.findCkdCartons(pageNum, pageSize, ckdCartonVO);
        return ResponseBean.success(ckdCartonList);
    }

2. Service layer

public interface CkdCartonService {
	// ② service interface
	PageVO<CkdCartonVO> findCkdCartons(Integer pageNum, Integer pageSize, CkdCartonVO ckdCartonVO);
}

3. ServiceImpl

@Transactional
@Service
public class CkdCartonServiceImpl implements CkdCartonService {
	// Inject Mapper interface
	@Autowired
    private CkdCartonMapper ckdCartonMapper;
	
	// ③ Method of implementing service interface
	@Override
    public PageVO<CkdCartonVO> findCkdCartons(Integer pageNum, Integer pageSize, CkdCartonVO ckdCartonVO) {
        PageHelper.startPage(pageNum, pageSize);
        // ④ Note that the mapper mapping layer interface method (corresponding to mapper.xml) is called here. The method name here is the same as the mapper interface method name, but it is not necessary. Don't be misled
        List<CkdCartonVO> ckdCartonVOList=ckdCartonMapper.findCkdCartons(ckdCartonVO);
        PageInfo<CkdCartonVO> info = new PageInfo<>(ckdCartonVOList);
        return new PageVO<>(info.getTotal(), ckdCartonVOList);
    }
}

4. mapper layer

public interface CkdCartonMapper extends Mapper<CkdCarton> {

    // The interface here inherits the general Mapper and encapsulates the CkdCarton object, which is just to obtain its attribute fields. And we all know that mybatis doesn't need to write the implementation of mapper.java. It helps us do it
    List<CkdCartonVO> findCkdCartons(CkdCartonVO ckdCartonVO);
}    

The custom encapsulated result set CkdCartonVO has two more fields than CkdCarton, that is, the primary key IDs of the other two tables mentioned earlier.

5. mapper.xml sql query

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yunsystem.business.mapper.CkdCartonMapper">
<select id="findCkdCartons"  parameterType="com.yunsystem.common.vo.business.CkdCartonVO"
            resultType="com.yunsystem.common.vo.business.CkdCartonVO">
        SELECT ca.*, pa.pallet_number, po.purchase_order
        FROM ckd_carton_info ca, ckd_pallet_info pa, ckd_po_info po
        WHERE ca.`pallet_id` = pa.`pallet_id` and po.`po_id`=ca.`po_id`

        <if test="purchase_order != null and purchase_order != ''">
            and po.purchase_order like concat('%',#{purchase_order}, '%')
        </if>
        <if test="pallet_number != null and pallet_number != ''">
            and pa.pallet_number like concat('%',#{pallet_number}, '%')
        </if>
        <if test="carton_number != null and carton_number != ''">
            and ca.carton_number like concat('%',#{carton_number}, '%')
        </if>
    </select>

Field interpretation:
namespace: refers to the interface path where the mapped mapper.java is located
id = "xxxx" > indicates the unique identifier of this sql execution statement, and is also the method name of the interface [must be consistent to be found]
Parametertype = "" > indicates the parameters to be passed in the sql statement. The type should be consistent with the type of the corresponding interface method [optional]

resultMap = "" > define the parameter and call the id value of the defined mapping manager

resultType = "" > define the output parameter to match the common java type or the user-defined pojo [if the output parameter type is not specified, it will be the default type of the statement type, for example, the return value of the statement is int]