[R2DBC-Spring-Data-kofu]:Expressing distinct in criteria API

Question

Hello I try to convert a jpql query to the criteria api. the sql query is SELECT COUNT(DISTINCT id) FROM `user` WHERE login != :anonymousUser I've just succeed to reach this query with criteria SELECT COUNT(id) FROM `user` WHERE login != :anonymousUser here is the code:

class UserRepository(
    private val repo: R2dbcEntityOperations
) {
    companion object {
        @JvmStatic
        private val userModel by lazy { User::class.java }
    }

    // expected query:
    //"SELECT COUNT(DISTINCT id) FROM `user` WHERE login != :anonymousUser"
    fun countAllByLoginNot(anonymousUser: String): Mono<Long> {
        return repo.select(userModel)
            //current query:
            //"SELECT COUNT(id) FROM `user` WHERE login != :anonymousUser"
            .matching(
                query(
                    where("login")
                        .not(anonymousUser).ignoreCase(true)
                )
            )
        .count()
    }
}

How do I introduce the distinct with criteria API?

Answer 1

I've been plagued with similar problems. Although I'm using java, I seem to have found something relevant that I hope will help you:

// template : R2dbcEntityTemplate
final StatementMapper statementMapper = template.getDataAccessStrategy().getStatementMapper();
statementMapper.createSelect("table_name")
    .distinct() // distinct!
    .doWithTable((table, spec) -> {
        // Do with table. 
        // See org.springframework.data.r2dbc.core.R2dbcEntityTemplate#doSelect or other.
        return  // return something
});

If you want to de-duplicate the count function, you may need to refer to org.springframework.data.relational.core.sql.SimpleFunction to implement a new class, such as CountDistinctFunction. You can refer to this:

/**
 *
 * Function: {@code COUNT(DISTINCT ... )}
 *
 * @see org.springframework.data.relational.core.sql.Functions
 * @see org.springframework.data.relational.core.sql.SimpleFunction
 * @author ForteScarlet
 */
public class CountDistinctFunction implements Expression {
    private static final String FUNCTION_NAME = "COUNT";
    private final List<Expression> expressions;

    private CountDistinctFunction(List<Expression> expressions) {
        this.expressions = expressions;
    }

    /** getInstance. */
    public static CountDistinctFunction getInstance(Expression... expressions) {
        return new CountDistinctFunction(Arrays.asList(expressions));
    }

    /**
     * @see SimpleFunction#toString()
     */
    @Override
    public @NotNull String toString() {
        return FUNCTION_NAME + "(DISTINCT " + StringUtils.collectionToDelimitedString(expressions, ", ") + ")";
    }

    /**
     * @see SimpleFunction#getFunctionName()
     */
    public String getFunctionName() {
        return FUNCTION_NAME;
    }

    /**
     * @see SimpleFunction#getExpressions()
     */
    public List<Expression> getExpressions() {
        return Collections.unmodifiableList(expressions);
    }


    /**
     * @see org.springframework.data.relational.core.sql.AbstractSegment
     */
    @SuppressWarnings("JavadocReference")
    @Override
    public void visit(@NotNull Visitor visitor) {
        Assert.notNull(visitor, "Visitor must not be null!");

        visitor.enter(this);
        visitor.leave(this);
    }

    /**
     * @see org.springframework.data.relational.core.sql.AbstractSegment
     */
    @SuppressWarnings("JavadocReference")
    @Override
    public int hashCode() {
        return toString().hashCode();
    }

    /**
     * @see org.springframework.data.relational.core.sql.AbstractSegment
     */
    @SuppressWarnings("JavadocReference")
    @Override
    public boolean equals(Object obj) {
        return obj instanceof Segment && toString().equals(obj.toString());
    }
}

And use it:

        R2dbcEntityTemplate template; = // template instance

        final StatementMapper statementMapper = template.getDataAccessStrategy().getStatementMapper();
        final StatementMapper.SelectSpec selectSpec = statementMapper.createSelect("your_table_name")
                .withCriteria(criteria)
                .doWithTable((table, spec) -> spec.withProjection(CountDistinctFunction.getInstance(table.column("id"))));

        final PreparedOperation<?> operation = statementMapper.getMappedObject(selectSpec);
        final Mono<Long> count = template.getDatabaseClient().sql(operation).map(r -> r.get(0, Long.class)).first();

Although this post has been a long time coming, I'll give my opinion on it. If anyone happens to see it and has a better plan, then please let me know too, thanks a lot!

(Translated via DeepL)