Mybatisplus多表联查字段名重复处理Columnxxxxinwhereclause。。。问题描述
由以下表foo、和bar
foo
bar
-- ----------------------------
-- Table structure for bar
-- ----------------------------
DROP TABLE IF EXISTS`bar`;
CREATE TABLE`bar`(
`id`int(11)NOT NULL AUTO_INCREMENT,
`goods`varchar(255)CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`alias`varchar(255)CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY(`id`)USING BTREE
)ENGINE=InnoDB AUTO_INCREMENT=4CHARACTER SET= utf8mb4 COLLATE= utf8mb4_general_ci ROW_FORMAT = Dynamic; -- ----------------------------
-- Records of bar
-- ----------------------------
INSERT INTO`bar`VALUES(1,'愉快肥皂','ykfz');
INSERT INTO`bar`VALUES(2,'快乐肥宅⽔','klfzs');
INSERT INTO`bar`VALUES(3,'⾦坷垃','jkl');
-- ----------------------------
-- Table structure for foo
-- ----------------------------
DROP TABLE IF EXISTS`foo`;
CREATE TABLE`foo`(
`id`int(11)NOT NULL AUTO_INCREMENT,
`inc`varchar(255)CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`alias`varchar(255)CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`bar_id`int(11)NULL DEFAULT NULL,
PRIMARY KEY(`id`)USING BTREE
)ENGINE=InnoDB AUTO_INCREMENT=4CHARACTER SET= utf8mb4 COLLATE= utf8mb4_general_ci ROW_FORMAT = Dynamic; -- ----------------------------
-- Records of foo
join和in哪个查询更快-- ----------------------------
INSERT INTO`foo`VALUES(1,'天天好','tth',1);
INSERT INTO`foo`VALUES(2,'天天棒','ttb',2);
INSERT INTO`foo`VALUES(3,'giao','g',3);
SET FOREIGN_KEY_CHECKS =1;
现在需要关联对这两个表进⾏关联查询
由于foo和bar中都有id和alias字段，所以需要给他们增加别名，得到查询的SQL语句如下。
SELECT
foo.id AS id,
inc,
foo.alias AS alias,
goods,
bar.alias AS goods_alias
FROM
foo
JOIN bar ON foo.bar_id = bar.id;
根据上⾯的SQL我们可以按照
构建⼀个Mapper和xml
xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN" "/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.demo.mapper.FooBarMapper">
<select id="getPatchItemList"resultType="com.demo.FooBar">
SELECT
foo.id AS id,
inc,
foo.alias AS alias,
goods,
bar.alias AS goods_alias
FROM
foo
JOIN bar ON foo.bar_id = bar.id;
${ew.customSqlSegment}
</select>
</mapper>
${ew.customSqlSegment} 是Mybatis Plus的动态条件构造器的最终条件SQL
Mapper
public interface FooBarMapper extends BaseMapper<FooBar>{
List<FooBar>getPatchItemList(
@Param(Constants.WRAPPER) Wrapper<FooBar> wrapper);
}
但我们执⾏下⾯查询语句时
Wrapper<FooBar> wrapper = Wrappers.<FooBar>lambdaQuery()
.like(FooBar::getAlias,"b");// --> alias like '%b%'
List<ProviderDataItem> res =
会报错 Column ‘alias’ in where clause is ambiguous，提⽰的意义是说这个 alias 字段是不够明确是暧昧的。
原因： 这是由于连表之后字段中alias重复了，查询结果集中含有两个alias不知道是哪⼀个才是要查询的，条件语句是针对查询结果集的所以字段重命名是⽆效的。
解决⽅式
1.使⽤明确的字段名称 表名.字段名
Wrapper<FooBar> wrapper = Wrappers.<FooBar>Query()
.like("foo.alias","b");
2.把查询结果作为⼦查询，然后在增加条件语句
SELECT
*
FROM
(
SELECT
foo.id AS id,
inc,
foo.alias AS alias,
goods,
bar.alias AS goods_alias
FROM
foo
JOIN bar ON foo.bar_id = bar.id
)AS t ${ew.customSqlSegment}
这种⽅式就可以直接使⽤下⾯⽅式进⾏查询⽽不需要补全表名：
Wrapper<FooBar> wrapper = Wrappers.<FooBar>lambdaQuery() .like(FooBar::getAlias,"b");
该种⽅式是由我同事告诉我的，：P

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。