- 报错如下:
org.springframework.dao.DataIntegrityViolationException:
### Error querying database. Cause: java.sql.SQLIntegrityConstraintViolationException: Column 'name' in where clause is ambiguous
### The error may exist in file [D:\CIS\cis\cis-org\target\classes\mapper\ClassroomMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select ocr.*,oc.name as campusName from org_classroom ocr left join org_campus oc on oc.id= ocr.campus_id where ocr.org_id = ? and ocr.deleted=0 and ocr.status = ? and name like concat(?,'%') LIMIT ?
### Cause: java.sql.SQLIntegrityConstraintViolationException: Column 'name' in where clause is ambiguous
; Column 'name' in where clause is ambiguous; nested exception is java.sql.SQLIntegrityConstraintViolationException: Column 'name' in where clause is ambiguous
根据提供的报错信息,可以看出问题出在 SQL 查询语句中。报错信息指出 Column 'name' in where clause is ambiguous,即查询语句中的 name 列在 WHERE 子句中存在歧义。
这种错误通常是因为查询语句中涉及到多个表,且这些表中都包含了名为 name 的列,导致数据库无法确定具体使用哪个表的 name 列来进行条件筛选。
- 针对上述报错中这个语句:
### SQL: select ocr.*,oc.name as campusName from org_classroom ocr left join org_campus oc on oc.id= ocr.campus_id where ocr.org_id = ? and ocr.deleted=0 and ocr.status = ? and name like concat(?,'%') LIMIT ?
我们可以得出错误出在SQL的操作中,我们进入相应的xxxMapper.xml文件中找到该语句。
select ocr.*,oc.name as campusName from org_classroom ocr
left join org_campus oc on oc.id= ocr.campus_id
where ocr.org_id = #{dto.orgId} and ocr.deleted=0
and name like concat(#{dto.name},'%')
解决该问题的方法是,在查询语句中明确指定使用哪个表的 name 列。根据你提供的查询语句,可能需要将 name 列前面加上表的别名或者完整的表名,例如这里的错误就是进行如下修改: ocr.name 或者 oc.name,以消除歧义。
select ocr.*,oc.name as campusName from org_classroom ocr
left join org_campus oc on oc.id= ocr.campus_id
where ocr.org_id = #{dto.orgId} and ocr.deleted=0
and ocr.name like concat(#{dto.name},'%')
得以解决歧义问题。