Mybatis plus multi table associated query column ‘ID’ in where claim is approximate reprint

Time:2022-5-10

Reprinted from https://blog.csdn.net/qq_3383…

1、 Error reporting information
Caused by: Column ‘xxxx’ in where clause is ambiguous

2、 Error reporting reason
Both table person and table class have field ID and name, so alias should be added to distinguish them.

PersonVOMapper.java


public interface PersonVOMapper extends BaseMapper<PersonVO> {

    List<PersonVO> getPersonVOList(@Param(Constants.WRAPPER) Wrapper<PersonVO> queryWrapper);
    
}


PersonVOMapper.xml
<?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.sionas.mapper.PersonVOMapper">

    <select id="getPersonVOList" resultType="com.sionas.domain.PersonVO">
        SELECT
            p.id AS personId,
            p.name AS personName,
            p.sex,
            p.age,
            c.id AS classId,
            c.name AS className
        FROM
            person p
        LEFT JOIN class c ON c.id = p.class_id;
        ${ew.customSqlSegment}
    </select>
</mapper>

${EW. Customsqlsegment} is the final SQL condition of mybatis plus’s dynamic condition constructor

PersonServiceImpl.java



LambdaQueryWrapper<PersonVO> wrapper = new LambdaQueryWrapper<>();
wrapper.like(PersonVO::getPersonName, keyword)
       .or().like(PersonVO::getClassName, keyword);

List<PersonVO> list = personVOMapper.getPersonVOList(wrapper);

At this time, the error column ‘name’ in where clause is ambiguous will be reported, which means that the column “name” in the where clause is ambiguous.

Reason: after multi table query, the field name is duplicate, and the query result set contains two names. I don’t know which one to query. The conditional statement is for the query result set, so the field renaming at this time is invalid.

3、 Solution
Method 1:
Use explicit field names and table names Field name


LambdaQueryWrapper<PersonVO> wrapper = new LambdaQueryWrapper<>();
wrapper.like("p.name", keyword)
       .or().like("c.name", keyword);

List<PersonVO> list = personVOMapper.getPersonVOList(wrapper);

Method 2:
Take the query results as sub queries, and then add conditional statements

    SELECT
        *
    FROM (
        SELECT
            p.id AS personId,
            p.name AS personName,
            p.sex,
            p.age,
            c.id AS classId,
            c.name AS className
        FROM
            person p
        LEFT JOIN class c ON c.id = p.class_id
    ) AS result
    ${ew.customSqlSegment}

You can query directly in the following way without completing the table name:

LambdaQueryWrapper<PersonVO> wrapper = new LambdaQueryWrapper<>();
wrapper.like(PersonVO::getPersonName, keyword)
       .or().like(PersonVO::getClassName, keyword);

List<PersonVO> list = personVOMapper.getPersonVOList(wrapper);

————————————————
Copyright notice: This article is the original article of CSDN blogger “siona_xin”, which follows the CC 4.0 by-sa copyright agreement. For reprint, please attach the original source link and this notice.
Original link: https://blog.csdn.net/qq_3383…