Hi,
what we‘re doing in our project sounds similar and might be helpful for
Henning’s request. SelectQuery provides asTable() so it can be used anywhere or
normal table can be used. To get the correct field out of the view we use this
method (with some sanity checks whether the field exists or not):
private Field<?> getSelectField(SelectQuery query, String name) {
List<Field<?>> fields = query.getFields();
int index = fields.indexOf(field);
return query.getSelect().get(index);
}
So something like this can be done:
Factory create = …
SelectQuery minorView = …
SelectQuery queryName =
create.selectQuery(minorView.asTable()).where(getSelectField(minorView,
“name”).eq(“smith”));
SelectQuery queryAge =
create.selectQuery(minorView.asTable()).where(getSelectField(minorView,
“age”).greaterThan(16));
getSelectField was used because otherwise the aliasing of the field somehow got
screwed up. AFAIR it used the tableAlias inside the SelectQuery instead of the
alias of the select itself, which results in errors if you use the view in
joins or where clauses.
Maybe wrap the query for the view and the getSelectField calls in their own
class with getTable(), getMinorName, etc and it should be very close to what
you want.
I hope this approach is somehow useful or did I totally misunderstand the
question?
Cheers,
Jörg
Von: [email protected] [mailto:[email protected]] Im Auftrag
von Henning Blohm
Gesendet: Freitag, 16. November 2012 14:03
An: [email protected]
Betreff: Re: Views and aliasing in jOOQ
Hi Lukas,
thanks for that extremely quick and detailed reply!
Henning
Am Freitag, 16. November 2012 13:34:51 UTC+1 schrieb Lukas Eder:
Hello Henning,
Unfortunately, this isn't possible out of the box, right now. It
sounds like a very nice idea, though. I have registered feature
request #1969 for this.
https://github.com/jOOQ/jOOQ/issues/1969
Of course, you can always write actual database views and let the code
generator generate meta-data for those.
Also, you could manually implement some org.jooq.Table objects, but
that might be quite a bit of work. There are CustomTable types, which
unfortunately aren't as powerful as you would like to see them:
http://www.jooq.org/doc/2.6/manual/sql-building/queryparts/custom-queryparts/
This is due to the methods toSQL() and bind() being final in
CustomTable's class hierarchy, which is probably a design flaw. It
should be fixed in jOOQ 3.0 as of #1970:
https://github.com/jOOQ/jOOQ/issues/1970
Cheers
Lukas
2012/11/16 Henning Blohm <[email protected]<javascript:>>:
> Hi,
>
> I am trying to achieve the construction of a (logical) view using jOOQ. As a
> simple case, assume there is a table students with name and age and I want
> to turn this into
>
> minors = select s.name<http://s.name> as minor_name from students s where
> s.age<21;
>
> Some other piece of code can then filter those further as in
>
> select m.minor_name from minors where m.minor_name='smith';
>
> In Java terms, "minors" would be a typed "queryable" with a custom POJO type
> (with field "minorName").
>
> Real world examples would be more complex of course
>
> I spent some time looking for a suitable construct in jOOQ but was not
> succesful.
>
> Any helpful directions would be great!
>
> Thanks,
> Henning
