Intantiating Field and Table by name
Murray Cumming
http://stackoverflow.com/questions/10264001/instantiating-a-jooq-field-by-name
I am using jOOQ in some generic code, without using jOOQ's code
generation. The table names and field names are not known until runtime.
To create a SQL query, I can use
Factory.field("somefield")
and
factory.select(fields).from("sometable")
but both those strings are just arbitrary SQL, not specifically field or
table names. It would be great if I could specify these explicitly, to
get the benefit of quoting, escaping, the "parent.field" syntax, and
some protection against SQL injection. And I think that would be healthy
for the API overall.
This would be nice, for instance:
Factory.field("someparent", "somefield")
where the parent parameter would be either the table name or an alias
for the table, or an alias (such as an alias to a join to another
table).
Maybe it would need a DataType parameter too.
And this would be nice too:
Factory.tableByName("sometable")
(There is already a table("SQL") method.)
where the name would be either a table or an alias.
By the way, it looks like jOOQ strongly assumes that we will use its
code generation. So much so that the documentation pages uses
database-specific API (BOOK, BOOKSTORE, etc) that must have been
generated, without explicitly mentioning that. In case people arrive at
a page without reading the previous pages, it might be helpful to add a
link to a description of the code generation, to each place where it is
implicitly used.
Lukas Eder
> As mentioned here:
> http://stackoverflow.com/questions/10264001/instantiating-a-jooq-field-by-name
> I am using jOOQ in some generic code, without using jOOQ's code
> generation. The table names and field names are not known until runtime.
Thanks for sharing your thoughts. I agree that jOOQ lacks some sort of
convenient way of using arbitrary qualifiers as Field, Table, Schema,
Routine, and other entities. As I mentioned on that Stack Overflow
question, jOOQ actually relies on this itself, internally. But in the
public API, we need to give this a little more thought.
> This would be nice, for instance:
> Factory.field("someparent", "somefield")
> where the parent parameter would be either the table name or an alias
> for the table, or an alias (such as an alias to a join to another
> table).
> Maybe it would need a DataType parameter too.
Yes, optional Class<T> or DataType<T> parameters are always useful for
org.jooq.Field<T> constructors. The problem with the Factory.field()
method, however, is that it's already overloaded quite a bit. Your
suggestion would collide with Factory.field(String, Object...), which
is used for something entirely different. The same applies for
Factory.table(String, Object...).
> And this would be nice too:
> Factory.tableByName("sometable")
> (There is already a table("SQL") method.)
> where the name would be either a table or an alias.
Yes, maybe this little extra verbosity would be helpful for
disambiguation in this case. Or maybe:
Schema Factory.qualifiedSchema(String...)
Table Factory.qualifiedTable(String...)
Field<?> Factory.qualifiedField(String...)
<T> Field<T> Factory.qualifiedField(Class<T>, String...)
<T> Field<T> Factory.qualifiedField(DataType<T>, String...)
Or even:
Name Factory.name(String)
And then build Schemata, Tables, Fields using names:
Schema Factory.schema(Name...)
Table Factory.table(Name...)
Field<?> Factory.field(Name...)
<T> Field<T> Factory.field(Class<T>, Name...)
<T> Field<T> Factory.field(DataType<T>, Name...)
Another option is to use the existing API and enhance it compatibly.
Take Factory.field(String, QueryPart...) for instance:
http://www.jooq.org/javadoc/latest/org/jooq/impl/Factory.html#field%28java.lang.String,%20java.lang.Class,%20org.jooq.QueryPart...%29
It allows for a dedicated escape syntax for QueryPart placeholders
{0}, {1}, {2} and keywords {keyword}. This could be an option, as
well, to allow for [escaped names]
Of course, all of these elements can be combined.
Any other API ideas?
> By the way, it looks like jOOQ strongly assumes that we will use its
> code generation. So much so that the documentation pages uses
> database-specific API (BOOK, BOOKSTORE, etc) that must have been
> generated, without explicitly mentioning that. In case people arrive at
> a page without reading the previous pages, it might be helpful to add a
> link to a description of the code generation, to each place where it is
> implicitly used.
Yes, the very first section of the manual mentions the sample database, here:
http://www.jooq.org/manual/JOOQ/ExampleDatabase/
But you're right. I'll check how many references would be needed to
clarify that in the manual. If it's not a huge number, I could add
such a hint to all relevant paragraphs. Maybe even some sort of
footnotes...?
Cheers
Lukas
2012/4/24 Murray Cumming <mur...@murrayc.com>:
Murray Cumming
Personally, I would prefer a clear simple API like tableByName() and
fieldByName(). The indirection of a Name class would make the API a bit
tedious, and there are already so many classes that seem to leak into
the top-level API.
> Another option is to use the existing API and enhance it compatibly.
> Take Factory.field(String, QueryPart...) for instance:
> http://www.jooq.org/javadoc/latest/org/jooq/impl/Factory.html#field%28java.lang.String,%20java.lang.Class,%20org.jooq.QueryPart...%29
>
> It allows for a dedicated escape syntax for QueryPart placeholders
> {0}, {1}, {2} and keywords {keyword}. This could be an option, as
> well, to allow for [escaped names]
I would prefer jOOQ to just do the right thing if I've told it what the
table (or alias) name is and what the field name is. Telling it to use a
particular escape syntax, or even having to tell it to use _a_ escape
syntax, seem superfluous in a high-level public API.
> Of course, all of these elements can be combined.
>
> Any other API ideas?
>
> > By the way, it looks like jOOQ strongly assumes that we will use its
> > code generation. So much so that the documentation pages uses
> > database-specific API (BOOK, BOOKSTORE, etc) that must have been
> > generated, without explicitly mentioning that. In case people arrive at
> > a page without reading the previous pages, it might be helpful to add a
> > link to a description of the code generation, to each place where it is
> > implicitly used.
>
> Yes, the very first section of the manual mentions the sample database, here:
> http://www.jooq.org/manual/JOOQ/ExampleDatabase/
>
> But you're right. I'll check how many references would be needed to
> clarify that in the manual. If it's not a huge number, I could add
> such a hint to all relevant paragraphs. Maybe even some sort of
> footnotes...?
I would add something like "This uses the classes that we generated in
Step 2 (with a link)" near the top of pages such as this:
http://www.jooq.org/tutorial.php#step2
And/or I might like a link to the actual class names in the example
code. Actually, API links in the example code would be really useful,
though that's a lot of work, and lots of links to keep up-to-date.
Murray Cumming
> I would add something like "This uses the classes that we generated in
> Step 2 (with a link)" near the top of pages such as this:
> http://www.jooq.org/tutorial.php#step2
Sorry, I mean, pages such as this:
http://www.jooq.org/tutorial.php#step5
or this:
http://www.jooq.org/manual-single-page/#Factory
(though the link in that case would be to here, I think:)
http://www.jooq.org/manual-single-page/#ExampleDatabase
Lukas Eder
> fieldByName(). The indirection of a Name class would make the API a bit
> tedious, and there are already so many classes that seem to leak into
> the top-level API.
You're right. There are quite a few types that are not really needed,
or at least should be hidden from the public API. This was recently
brought up by Christopher Deckers, contributor of the jOOQ Console:
https://groups.google.com/d/topic/jooq-user/q6Yf7voPaqQ/discussion
There's clearly need for action, eventually. The public API will be
reviewed as of jOOQ 3.0.0:
https://sourceforge.net/apps/trac/jooq/ticket/1192
Any concrete feedback on current types is welcome!
For the record: The jOOQ DSL evolved step by step. It wasn't easy to
foresee which types will last and which ones won't. Many "internal"
features turned out to be useful in the "external" API. The same may
apply to a new Name type (or an improved NamedQueryPart type). This
might prove to be very useful for type-checking in SQL syntax clauses
that only accept column names, for instance. Name would then be a
super-type of TableField. Hard to say right now, so I think the
combination of various ways of creating a Name QueryPart might
eventually turn out to be a nice-to-have feature for the DSL in
general.
>> Another option is to use the existing API and enhance it compatibly.
>> Take Factory.field(String, QueryPart...) for instance:
>> http://www.jooq.org/javadoc/latest/org/jooq/impl/Factory.html#field%28java.lang.String,%20java.lang.Class,%20org.jooq.QueryPart...%29
>>
>> It allows for a dedicated escape syntax for QueryPart placeholders
>> {0}, {1}, {2} and keywords {keyword}. This could be an option, as
>> well, to allow for [escaped names]
>
> I would prefer jOOQ to just do the right thing if I've told it what the
> table (or alias) name is and what the field name is. Telling it to use a
> particular escape syntax, or even having to tell it to use _a_ escape
> syntax, seem superfluous in a high-level public API.
Your immediate use-case doesn't justify the need for such a feature in
the public API, indeed. But having such an escape syntax might make
implementing custom QueryParts much easier while at the same time
allowing for removing quite a few internal classes (which was the case
with the {0}, {1} placeholder syntax).
While these features seem highly "internal", they turn out to be very
useful for jOOQ extension, eventually. The way you're requesting it
right now. Not every database uses the same syntax for escaping names,
so in jOOQ's plain SQL context, it might turn out to be useful to
support a database-independent syntax
> I would add something like "This uses the classes that we generated in
> Step 2 (with a link)" near the top of pages such as this:
> http://www.jooq.org/tutorial.php#step2
I'd prefer to keep it in the manual. The tutorial is external to the
manual (think of the PDF version). But of course, I could make the
tutorial *part* of the manual...
> And/or I might like a link to the actual class names in the example
> code. Actually, API links in the example code would be really useful,
> though that's a lot of work, and lots of links to keep up-to-date.
That's a nice idea. It wouldn't be so much work. The manual is
generated from an XML structure. It supports <reference/> tags that
can link to jOOQ classes - which are translated to Javadoc links.
They're always up to date. I'll think about that, thanks for the hint!
Cheers
Lukas
Lukas Eder
> http://www.jooq.org/tutorial.php#step5
> or this:
> http://www.jooq.org/manual-single-page/#Factory
> (though the link in that case would be to here, I think:)
> http://www.jooq.org/manual-single-page/#ExampleDatabase
There are already cross-references in the manual, both HTML and PDF.
Is that what you meant?
Lukas Eder
https://sourceforge.net/apps/trac/jooq/ticket/1352
Any other more sophisticated internal solutions may be implemented in
the future, when needed.
Cheers
Lukas
Murray Cumming
parameter to avoid ambiguity when two tables in the query have fields
with the same name.
--
Murray Cumming
mur...@murrayc.com
www.murrayc.com
www.openismus.com
Lukas Eder
> Thanks. That helps but fieldByName() really needs a tableName/aliasName
> parameter to avoid ambiguity when two tables in the query have fields
> with the same name.
fieldByName("field_name");
fieldByName("alias", "field_name");
fieldByName("schema", "table", "field_name");
fieldByName("catalog", "schema", "table", "field_name");
Agreed, the "fieldName" argument name to the method might be a bit confusing...
Murray Cumming
http://www.jooq.org/javadoc/latest/org/jooq/impl/Factory.html#fieldByName%28java.lang.String...%29
Thanks. That is working great for me.
For people like me who don't read it all properly, maybe it would be
worthwhile to change "given its field name" to "given its field name
and, optionally, the table (or alias) name, and the schema and catalog
names."
Lukas Eder
> For people like me who don't read it all properly, maybe it would be
> worthwhile to change "given its field name" to "given its field name
> and, optionally, the table (or alias) name, and the schema and catalog
> names."
Thanks
Lukas