Hi Thomas,
2017-03-17 16:47 GMT+01:00 Thomas GILLET <[email protected]>:
> Hello all,
>
> One thing I felt missing this week is an easy way to create a custom
> derived table.
> When writing a complex (sub-)query, I sometimes like to package it in its
> own class, together with its (usually aliases) fields.
>
> Here a very simple example:
>
> // Packaged query:
>
> public class MyQuery {
>
> public final Table<...> QUERY;
> public final Field<Integer> FIELD;
>
> public MyQuery(String alias) {
>
> // The query
> this.QUERY =
> select(SOME_TABLE.SOME_FIELD)
> .from(SOME_TABLE)
> .asTable(alias);
>
> // The fields of the query, aliased accordingly
> this.FIELD = QUERY.field(SOME_FIELD);
> }
> }
>
> // Usage:
>
> MyQuery q1 = new MyQuery("hello");
> MyQuery q2 = new MyQuery("world");
>
> select(q1.FIELD, q2.FIELD).from(q1.QUERY.join(q2.QUERY).on(...));
>
> The idea being that I can alias my packaged query very easily, almost as
> if it was a generated table.
> Not so useful here, but worth the trouble when the query has a lot of
> fields.
>
Yeah, that's this feature request here:
https://github.com/jOOQ/jOOQ/issues/1969
You're creating a view but you're not storing it in SQL, you're "storing it
in jOOQ". Would definitely be nice!
> That was to explain the idea. Now the real thing:
> Since it is *almost *like a generated table, why not make it *exactly *like
> a generated table? That is make MyQuery into an actual Table rather than
> having a Table field.
> Here is what I would find very cool:
>
> public class MyQuery extends SomeKindOfTableImpl<...> {
>
> public final static MyQuery MY_QUERY = new MyQuery();
>
> public final Field<Integer> FIELD = createField("field", ...);
>
> public MyQuery() {
> setTheQuery(
> select(anyExpression.as(FIELD))
> ...;
> );
> }
> }
>
> // And then use it as any other table:
> select(MY_QUERY.FIELD).from(MY_QUERY);
>
> // Or aliased:
> MyQuery q = MY_QUERY.as("yop");
> select(q.FIELD).from(q);
>
>
> And here is a very very ugly try at a SomeKindOfTableImpl implementation:
>
> public abstract class DerivedTableImpl<R extends Record> extends TableImpl
> <R> {
>
> /**
> * Default constructor.<br>
> * The underlying query must then be set by subclasses through {@code
> query()}.
> */
> protected DerivedTableImpl(String name) {
> super(name);
> }
>
> /**
> * Copy constructor, to be used when implementing {@code as()} in
> subclasses.<br>
> * The underlying query is copied from the given table.
> */
> protected DerivedTableImpl(String name, DerivedTableImpl<R> other) {
> super(name);
> setWrapped(this, getWrapped(other));
> }
>
> /**
> * Sets the underlying query.<br>
> * Should be called by subclasses in their (not copy) constructor.
> */
> protected DerivedTableImpl<R> query(Select<? extends R> query) {
> setWrapped(this, createDerivedTable(query));
> return this;
> }
>
> // Force subclasses to covariantly override this method
> /** {@inheritDoc} **/
> @Override
> public abstract DerivedTableImpl<R> as(String as);
>
> //////// BLACK MAGIC
>
> /**
> * Set the internal alias of a {@code TableImpl}, even though field
> and class are final and package-private.<br>
> * Allow to control the {@code wrapInParentheses} flag, and to set the
> alias after the constructor call.
> */
> private static <R extends Record> void setWrapped(TableImpl<R> table,
> Table<R> wrapped) {
> Object alias = wrapped == null ? null : on("org.jooq.impl.Alias").
> create(wrapped, table.getName(), true).get();
> on(table).set("alias", alias);
> }
>
> /**
> * Get the table wrapped in the internal alias of a {@code TableImpl}.
> */
> private static <R extends Record> Table<R> getWrapped(TableImpl<R>
> table) {
> Object alias = on(table).get("alias");
> return alias == null ? null : on(alias).call("wrapped").get();
> }
>
> /**
> * Create a {@code DerivedTable} instance, even though the class is
> package-private.<br>
> * Allow to get a clean {@code DerivedTable} without aliasing (unlike
> {@code Select.asTable()}).
> */
> private static <R extends Record> Table<R> createDerivedTable(Select<?
> extends R> query) {
> return Reflect.on("org.jooq.impl.DerivedTable").create(query).get
> ();
> }
> }
>
> Not intended to be used in real-life, but it seems to work. Just to show
> that all necessary code is already there, it's just not accessible.
> For those interested, there is a way to do it without black-magic (create
> a Table implementation delegating to query.asTable(), tweak accept() to
> only visit the original query and add extra parenthesis, and use an
> instance of this as the aliased param of TableImpl constructor). But it
> is quite cumbersome.
>
> Well! I may be the only one needing such a feature, but I found it very
> handy when packaging custom queries together with jOOQ generated classes in
> one single lib.
> Cheers,
> Thomas
>
Yes, there will be some sort of implementation. Probably not using
reflection, though :) I cannot give any authoritative info yet about how
this could best be implemented, but rest assured, I also want this! :)
Lukas
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
