How to change field list on SelectQuery

33 views
Skip to first unread message

Simon Niederberger

unread,
May 13, 2018, 4:53:01 AM5/13/18
to jOOQ User Group
Given a SelectQuery<? extends Record>, I'm struggling with the effects from basically an empty field list.

  1. The query is created with jooq.selectQuery(getTable());
  2. Later, some joins might be added
When fetching from the query, the issue is that multiple tables with identical field names lead to wrong data when fetching into a POJO.

How can I change the field list of the SelectQuery? Or would that be part of https://github.com/jOOQ/jOOQ/issues/1492 ?

jOOQ 3.10.2

Current workaround (note the placement in the org.jooq.impl package in order to access package-private methods):
/*
 * Created on 13 May 2018
 */
package org.jooq.impl;

import java.util.Iterator;
import java.util.Set;

import org.jooq.Field;
import org.jooq.SelectQuery;

import com.google.common.collect.Sets;

public final class SelectQueryProjectionModifier {

    private SelectQueryProjectionModifier() {
        // hide c'tor
    }

    /**
     * jOOQ 3.x doesn't expose a {@link SelectQuery}'s fields in a modifyable way
     *
     * @param query
     * @param fields
     *
     */
    public static void setFields(final SelectQuery<?> query, final Field<?>[] fields) {
        if (query instanceof SelectQueryImpl) {
            final Set<Field<?>> selectFields = Sets.newHashSet(fields);
            final SelectFieldList fieldList = ((SelectQueryImpl<?>) query).getSelect0();
            if (fieldList.isEmpty()) {
                fieldList.addAll(selectFields);
            } else {
                for (final Iterator<Field<?>> iterator = fieldList.listIterator(); iterator.hasNext();) {
                    final Field<?> field = iterator.next();
                    if (!selectFields.contains(field)) {
                        iterator.remove();
                    }
                }
            }
        }
    }
}



Lukas Eder

unread,
May 14, 2018, 3:15:03 AM5/14/18
to jooq...@googlegroups.com
2018-05-13 10:53 GMT+02:00 Simon Niederberger <simon.nie...@gmail.com>:
Given a SelectQuery<? extends Record>, I'm struggling with the effects from basically an empty field list.

  1. The query is created with jooq.selectQuery(getTable());
  2. Later, some joins might be added
When fetching from the query, the issue is that multiple tables with identical field names lead to wrong data when fetching into a POJO.

I'm assuming that by this, you mean using your query as a derived table. Then, yes, you're not allowed to have ambiguous column names. Only top level SELECT statements may expose ambiguous column names in SQL.

However, I'm not 100% certain what you're trying to do. Would you mind explaining your use case a bit more?
 
How can I change the field list of the SelectQuery? Or would that be part of https://github.com/jOOQ/jOOQ/issues/1492 ?

#1492 has been rejected, and there won't be anything similar to replace it.
 
Current workaround (note the placement in the org.jooq.impl package in order to access package-private methods):

Don't do that! :-)

1. From JDK 9 onwards (at least when you move to the module path), you won't be able to have your own org.jooq.impl package
2. We'll change internal API in pretty much every minor release, so your code will break very certainly

Simon Niederberger

unread,
May 14, 2018, 2:08:02 PM5/14/18
to jOOQ User Group

On Monday, May 14, 2018 at 9:15:03 AM UTC+2, Lukas Eder wrote:


2018-05-13 10:53 GMT+02:00 Simon Niederberger <simon.nie...@gmail.com>:
Given a SelectQuery<? extends Record>, I'm struggling with the effects from basically an empty field list.

  1. The query is created with jooq.selectQuery(getTable());
  2. Later, some joins might be added
When fetching from the query, the issue is that multiple tables with identical field names lead to wrong data when fetching into a POJO.

I'm assuming that by this, you mean using your query as a derived table. Then, yes, you're not allowed to have ambiguous column names. Only top level SELECT statements may expose ambiguous column names in SQL.

However, I'm not 100% certain what you're trying to do. Would you mind explaining your use case a bit more?

I need a Select implementation to which I can later add conditions and joins (not fluently, more like the Criteria builder of Hibernate), I came across SelectQuery which fits perfectly. To create such a SelectQuery, I find

final SelectQuery<CustomfieldRecord> baseQuery = jooq.selectQuery(getTable())
whereas
jooq.select(getTable().fields()).from(getTable());
returns a SelectJoinStep (for fluent queries). Adding a join later
baseQuery.addJoin(BillingAccount.BILLING_ACCOUNT, Customfield.CUSTOMFIELD.ACCOUNT_UUID.eq(BillingAccount.BILLING_ACCOUNT.UUID));
baseQuery.addConditions(BillingAccount.BILLING_ACCOUNT.COMMUNITY.eq(principal.getCommunity()));
results in selecting fields of both tables, as the internal projection is on '*'. Adding 
baseQuery.fields(getTable().fields());
doesn't have the desired effect.
 
How can I change the field list of the SelectQuery? Or would that be part of https://github.com/jOOQ/jOOQ/issues/1492 ?

#1492 has been rejected, and there won't be anything similar to replace it.
 
Current workaround (note the placement in the org.jooq.impl package in order to access package-private methods):

Don't do that! :-)

1. From JDK 9 onwards (at least when you move to the module path), you won't be able to have your own org.jooq.impl package
2. We'll change internal API in pretty much every minor release, so your code will break very certainly

I'm with you, I'd rather not do that. So how do I tell a SelectQuery to only select certain fields?

Lukas Eder

unread,
May 15, 2018, 3:20:45 AM5/15/18
to jooq...@googlegroups.com
2018-05-14 20:08 GMT+02:00 Simon Niederberger <simon.nie...@gmail.com>:
I'm assuming that by this, you mean using your query as a derived table. Then, yes, you're not allowed to have ambiguous column names. Only top level SELECT statements may expose ambiguous column names in SQL.

However, I'm not 100% certain what you're trying to do. Would you mind explaining your use case a bit more?

I need a Select implementation to which I can later add conditions and joins (not fluently, more like the Criteria builder of Hibernate), I came across SelectQuery which fits perfectly. To create such a SelectQuery, I find

final SelectQuery<CustomfieldRecord> baseQuery = jooq.selectQuery(getTable())
whereas
jooq.select(getTable().fields()).from(getTable());
returns a SelectJoinStep (for fluent queries). Adding a join later
baseQuery.addJoin(BillingAccount.BILLING_ACCOUNT, Customfield.CUSTOMFIELD.ACCOUNT_UUID.eq(BillingAccount.BILLING_ACCOUNT.UUID));
baseQuery.addConditions(BillingAccount.BILLING_ACCOUNT.COMMUNITY.eq(principal.getCommunity()));
results in selecting fields of both tables, as the internal projection is on '*'.

Indeed, without any explicit projection, jOOQ is going to project "*" (or rather, the explicit column list produced by all the tables, if it is available from the meta model). You can easily add the projection yourself, though:

baseQuery.addSelect(...);

The confusing bit here is that you're getting a SelectQuery<CustomfieldRecord>, whose type is misleading. This is a very early API design flaw from version 1.0, which we haven't fixed for backwards compatibility reasons. A workaround would be to use:

SelectQuery<Record> baseQuery = jooq.selectQuery();
baseQuery.addFrom(getTable());

I wonder if we should deprecate that other method or improve its Javadoc...

Adding 
baseQuery.fields(getTable().fields());
doesn't have the desired effect.

Yes indeed. That method is for retreiving projected fields from the query, not setting them:

I suggest looking at these two pages for some ideas about what can be done when doing dynamic SQL with jOOQ:


A mistake a lot of people make is they want to use either the DSL API or the model API as a model for their intermediate "partial" or "incomplete" queries. That's often not a good idea. Granted, the APIs could be improved to allow for that, but they haven't been designed this way.

I hope this helps,
Lukas

Simon Niederberger

unread,
May 18, 2018, 3:11:08 AM5/18/18
to jOOQ User Group
I searched hi and lo for a "*field*" method to add that projection, but

baseQuery.addSelect(...);

is exactly what I was looking for.

Thanks a lot Lukas, you are doing an awesome job with jOOQ!

Lukas Eder

unread,
May 20, 2018, 4:55:58 AM5/20/18
to jooq...@googlegroups.com
2018-05-18 9:11 GMT+02:00 Simon Niederberger <simon.nie...@gmail.com>:
I searched hi and lo for a "*field*" method to add that projection, but

baseQuery.addSelect(...);

is exactly what I was looking for.

The method names in SelectQuery are all called setXYZ() or addXYZ() with XYZ being the name or something akin to the name of the clause to which you want to add objects. With 1-2 exceptions, of course.
 
Thanks a lot Lukas, you are doing an awesome job with jOOQ!

Thanks for your nice words! 
Reply all
Reply to author
Forward
0 new messages