dynamically choose columns in select with R2DB2

user_4753897 0
Viewed 1509

I have been trying to pass the columns I want to select in but out of the box it appears it is not possible. I have tried things like

@Query("SELECT :columns FROM USERS u WHERE u.LOCALE = :locale AND u.id IN (:ids)")
Flux<Users> retrieveExportData(@Param("columns") String columns,
                                       @Param("locale") String locale,
                                       @Param("ids") String[] ids);

and with

private final R2dbcEntityTemplate template;

I tried to create my own query and but that was not working because it has to be of type Criteria and that was just creating a complexity that just was not worth it.

It would be nice if I could add the columns like

    criteriaList.add(
        Criteria.where("LOCALE").is(locale)
    );
    Criteria criteria = Criteria.from(criteriaList);

and execute it like

    Flux<Users> users = this.template.select(User.class)
        .matching(Query.query(criteria))
        .all();

or just calling the repository like in my first example.

Has anyone been able to do this successfully?

----- update 1 -----

I tried doing like so:

import org.springframework.r2dbc.core.DatabaseClient;

DatabaseClient databaseClient = DatabaseClient.create(connectionFactory);

String sql = "SELECT " + columns + "  FROM USERS u WHERE u.LOCALE=" + locale + " AND u.id IN (" + ids + ")";

return databaseClient.sql(sql)
    .fetch()
    .all().cast(User.class);

but Since Spring 4.3.6.RELEASE, LinkedCaseInsensitiveMap doesn't extend LinkedHashMap and HashMap, but only implements Map interface.

This results in a

Cannot cast org.springframework.util.LinkedCaseInsensitiveMap to User at java.base/java.lang.Class.cast

error.

I then tried the jooq approach suggested in the answers but it just produces syntax errors. Example

private final DSLContext ctx = DSL.using(connectionFactory);
private final Users users = ctx.newRecord(Users.USERS); <-- USERS not found

@Query("SELECT :columns FROM USERS u WHERE u.LOCALE = :locale AND u.id IN (:ids)")
public Flux<Users> retrieveExportData(
    List<Field<?>> columns,
    String locale,
    String[] ids
) {
    return Flux.from(ctx
        .select(columns)
        .from("USERS")
        .where(users.LOCALE.eq(locale)) <--- LOCALE not found
        .and(users.ID.in(ids)) <--- ID not found
    ).map(r -> r.into(Users.class)); <---- into not found
}

the library look promising. I will try to get it working.

spring-data-r2dbc r2dbc
2 Answers

You cannot replace a bind parameter (:columns) by syntactic elements like this, other than actual bind values. For this type of dynamic SQL, you'll have to resort to some sort of query building mechanism.

Perhaps look at jOOQ, which has R2DBC support? Your implementation would then look like this:

@Query("SELECT :columns FROM USERS u WHERE u.LOCALE = :locale AND u.id IN (:ids)")
public Flux<Users> retrieveExportData(
    List<Field<?>> columns,
    String locale,
    String[] ids
) {
    return Flux.from(ctx
        .select(columns)
        .from(USERS)
        .where(USERS.LOCALE.eq(locale))
        .and(USERS.ID.in(ids))
    ).map(r -> r.into(Users.class));
}

Disclaimer: I work for the company behind jOOQ.

user_5217990

you can write dynamic SQL like so.

import org.springframework.r2dbc.core.DatabaseClient;

DatabaseClient databaseClient = DatabaseClient.create(connectionFactory);

String sql = "SELECT " + columns + "  FROM USERS u WHERE u.LOCALE=" + locale + " AND u.id IN (" + ids + ")";

return databaseClient.sql(sql)
    .fetch()
    .all().cast(User.class);

and you will need this dependency

implementation "org.springframework.boot:spring-boot-starter-data-r2dbc:2.6.2"
user_47538970
Related