yulichang / mybatis-plus-join

支持连表查询的mybatis-plus,mybatis-plus风格的连表操作提供wrapper.leftJoin(),wrapper.rightJoin()等操作
https://mybatis-plus-join.github.io
Apache License 2.0
1.15k stars 125 forks source link

在进行多对多查询时,由Spring Security调用业务方法中selectCollections方法没有查询到连表的字段,而使用测试类直接调用业务方法selectCollection正常工作 #213

Open dzkd2019 opened 1 week ago

dzkd2019 commented 1 week ago

确认

当前程序版本

1.5.1

问题描述

我是一位初学者,如果问出的问题比较基础/蠢请见谅 我使用H2数据库,有Account与Authority两张表以及一张中间表ACCOUNT_AUTHORITY用于多对多查询,表结构如下:

create table ACCOUNT
(
    ID         BIGINT auto_increment,
    PASSWORD   CHARACTER VARYING not null,
    FIRST_NAME CHARACTER VARYING,
    LAST_NAME  CHARACTER VARYING,
    EMAIL      CHARACTER VARYING not null,
    constraint ACCOUNT_PK
        primary key (ID)
);

create table AUTHORITY
(
    ID   BIGINT auto_increment,
    NAME CHARACTER VARYING(16) not null,
    constraint AUTHORITY_PK
        primary key (ID)
);

create table ACCOUNT_AUTHORITY
(
    ID           BIGINT auto_increment,
    ACCOUNT_ID   BIGINT not null,
    AUTHORITY_ID BIGINT not null,
    constraint ACCOUNT_AUTHORITY_PK
        primary key (ID)
);

Account实体类中有 @TableField(exist = false) List<Authority> authorities; 的属性,在对应的 AccountService 中,我定义了一个方法可以查询该Account所拥有的Authority,方法实现如下:

@Override
    public <F> Optional<Account> getWithAuthorities(F field, SFunction<Account, F> func) {
        var wrapper = new MPJLambdaWrapper<Account>()
                .selectAll(Account.class)
                .selectCollection(Authority.class, Account::getAuthorities)
                .leftJoin(AccountAuthorityMiddle.class, AccountAuthorityMiddle::getAccountId, Account::getId)
                .leftJoin(Authority.class, Authority::getId, AccountAuthorityMiddle::getAuthorityId);

        wrapper.eq(func, field);

        List<Account> accounts = accountMapper.selectJoinList(wrapper);

        if(accounts.size() > 1) {
            log.warn("查询到多个账户,结果如下");
            for (Account account : accounts) {
                log.warn(account.toString());
            }
            return Optional.empty();
        }

        Account account = accounts.get(0);

        return Optional.ofNullable(account);
    }

在使用这个方法之前,我编写了一个测试方法:

@SpringBootTest
class BlogDemoApplicationTests {

    @Resource
    private UserDetailsService userDetailsService;

    @Test
    void test() {
        UserDetails details = userDetailsService.loadUserByUsername("ltf@abc.com");
        System.out.println(details);
    }
}

@Component("userDetailsService")
@RequiredArgsConstructor
public class UserDetailsServiceImpl implements UserDetailsService {
    private final AccountService accountService;

    @Override
    public UserDetails loadUserByUsername(String email) throws UsernameNotFoundException {
        var optionalAccount = accountService.getWithAuthorities(email, Account::getEmail);

        if(optionalAccount.isEmpty()) {
            throw new UsernameNotFoundException("Account not found");
        }
        Account account = optionalAccount.get();
        List<GrantedAuthority> grantedAuthorities = account
                .getAuthorities()
                .stream()
                .map(auth -> new SimpleGrantedAuthority(auth.getName()))
                .collect(toList());

        return new User(account.getEmail(), account.getPassword(), grantedAuthorities);
    }
}

在测试中方法可以正常工作,查询得到的结果为:org.springframework.security.core.userdetails.User [Username=du@abc.com, Password=[PROTECTED], Enabled=true, AccountNonExpired=true, CredentialsNonExpired=true, AccountNonLocked=true, Granted Authorities=[ROLE_ADMIN, ROLE_USER, ROLE_SUPER]]

但当我在web应用中实际使用时,该方法得到的结果为:

2024-10-18T14:24:33.304+08:00  WARN 5712 --- [blog-demo] [nio-3000-exec-1] o.e.b.service.impl.AccountServiceImpl    : 查询到多个账户,结果如下
2024-10-18T14:24:33.305+08:00  WARN 5712 --- [blog-demo] [nio-3000-exec-1] o.e.b.service.impl.AccountServiceImpl    : Account(id=1, email=du@abc.com, password=$2a$12$m/m1YLgHTU8/ZDteyyvn.uhZQzBvViXigd9FLppiUNxkUKXJNukXK, firstName=mrawa, lastName=yukil, posts=null, authorities=null)
2024-10-18T14:24:33.305+08:00  WARN 5712 --- [blog-demo] [nio-3000-exec-1] o.e.b.service.impl.AccountServiceImpl    : Account(id=1, email=du@abc.com, password=$2a$12$m/m1YLgHTU8/ZDteyyvn.uhZQzBvViXigd9FLppiUNxkUKXJNukXK, firstName=mrawa, lastName=yukil, posts=null, authorities=null)
2024-10-18T14:24:33.305+08:00  WARN 5712 --- [blog-demo] [nio-3000-exec-1] o.e.b.service.impl.AccountServiceImpl    : Account(id=1, email=du@abc.com, password=$2a$12$m/m1YLgHTU8/ZDteyyvn.uhZQzBvViXigd9FLppiUNxkUKXJNukXK, firstName=mrawa, lastName=yukil, posts=null, authorities=null)

查询得到了三条结果,与该Account所拥有三条Authority的事实相符合,但是并没有通过SelectCollection映射到authorities字段上,请问要如何解决这个问题呢?

详细堆栈日志

No response

dzkd2019 commented 1 week ago

Spring Security 的 UserDetailsService 调用方法生成的SQL为

SELECT     t.id,t.email,t.password,t.first_name,t.last_name   FROM account  t    LEFT JOIN ACCOUNT_AUTHORITY t1 ON (t1.account_id = t.id) LEFT JOIN authority t2 ON (t2.id = t1.authority_id)     WHERE   (t.email = 'du@abc.com')

selectCollections没有查询Authority表中的字段

而测试中生成的SQL为

SELECT     t.id,t.email,t.password,t.first_name,t.last_name,t2.id AS joina_id,t2.name   FROM account  t    LEFT JOIN ACCOUNT_AUTHORITY t1 ON (t1.account_id = t.id) LEFT JOIN authority t2 ON (t2.id = t1.authority_id)     WHERE   (t.email = 'du@abc.com')