Hi Boris,

I’m trying to get a more accurate understanding of “schema changing often 
enough” – because the way you described your examples, it seems that your code 
is wired against specific schema versions, not generic database models, in case 
of which I really suggest you look into coupling your data change management 
(e.g. via Flyway or Liquibase) to jOOQ’s code generation:
https://blog.jooq.org/2014/06/25/flyway-and-jooq-for-unbeatable-sql-development-productivity/

A lot of folks are doing that even with several database increments per day – 
this is not extraordinary. If your schema isn’t completely dynamic (but then, 
you won’t have field("first_name") in your code, because you wouldn’t know 
about "first_name" fields), then not using the code generation has mostly cons. 
You already ran into a few. It is common among new jOOQ users to see code 
generation as a “burden”, a step that is “hard” to get into existing CI 
pipelines (often because the pipelines may not be as formal as they could be). 
But if you think this through, you’ll see that you get two big wins:


  *   A more formal CI pipeline, which per se produces better quality, because 
you’ll make database increments a much more formal part of your deployment, 
including versioning of your schema and the derived artifacts e.g. from jOOQ 
(which you can version control as well! See: 
https://www.jooq.org/doc/latest/manual/code-generation/codegen-version-control/)
  *   Access to so many advanced jOOQ features, such as e.g. implicit joins: 
https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/select-statement/implicit-join/

Going back to your previous email. You were trying to use 
returning(field("id")) with plain SQL. There are some technical reasons why 
telling jOOQ the name of that field isn’t enough, if jOOQ doesn’t *know* that’s 
an identity column of your table("author"). This is just one more example where 
generated meta data would be helpful, i.e. in some dialects, this may not work, 
as you found out. I’m reluctant to walk you through workarounds – because… well 
that would work if you were using the code generator :)

I hope this helps,
Lukas

From: jooq-user@googlegroups.com <jooq-user@googlegroups.com> On Behalf Of 
Boris Heismann
Sent: Sonntag, 30. Mai 2021 22:00
To: jOOQ User Group <jooq-user@googlegroups.com>
Subject: Re: Field (author.id) is not contained in Row when using without code 
generation

@Robjs: this might be the case. However, I'm trying to evaluate the 
no-generated-code approach to identify both pros and cons.

Best regards,

Boris

On Sunday, May 30, 2021 at 9:36:19 PM UTC+2 
robjs...@gmail.com<mailto:robjs...@gmail.com> wrote:
Hm. You're writing Java against the current DDL.  Code generation is nearly 
instantaneous so it’s trivial to keep it up to date with the target database. 
Include  run the code gen once you have set your DDL (and when you change it 
again)and have your IDE tell you the current table and column names. Don’t 
check in the generated code.

It’s easy to tie DDL changes to code regeneration



On May 30, 2021, at 10:17 AM, Boris Heismann <boris.h...@gmail.com> wrote:

Hi Lukas,

Thank you very much for your explanation. Using name() helped a lot.

The reason to go with no code generator is the dynamic project structure. The 
objective is to develop an external tool to the already existing product which 
changes its schema often enough to make code generation pretty useless in this 
case. The product is designed in another programming language and is capable of 
using different DBs. So going with plain JDBC or something like Apache Derby 
isn't the choice.

Using name() I could overcome the complications I had with selects. However, 
now I'm on another point. I need to insert a number of records into two related 
tables. The tables are connected with the foreign key. I would like to do this 
covered into a transaction. In the example below authorRecord is null. This way 
I have no chance to find out the generated id for the author table. Can you 
please advise?


dslContext.transaction((configuration) -> {
                Record authorRecord = using(configuration)
                        .insertInto(table("author"), field("first_name"), 
field("last_name"))
                        .values("Evil", "Emerger")
                        .returning(field("id"))
                        .fetchOne();

                @NotNull Result<Record> bookRecord = using(configuration)
                        .insertInto(table("book"), field("author_id"), 
field("name"), field("publishing_date"))
                        .values(authorRecord.get(0), "Evolution of SQL", 2010)
                        .returning()
                        .fetch();

}
.......

Best regards,

Boris




On Fri, May 28, 2021 at 11:19 PM Lukas Eder <lukas...@gmail.com> wrote:
Hi Boris,

Thanks a lot for your message. Quick question first: Why are you not using the 
code generator? You will get a *much* better experience with jOOQ in general, 
if you're using the code generator. Omitting the code generator usually only 
makes sense when your schema is dynamic, and not known at compile time.

Meanwhile, regarding your particular issue, there's a common confusion between 
the plain SQL API (which you were using), and the API to construct references 
from identifiers (which you should be using).

See the relevant sections of the documentation here:
- https://www.jooq.org/doc/latest/manual/sql-building/plain-sql/
- https://www.jooq.org/doc/latest/manual/sql-building/names/

Or this blog post:
- https://blog.jooq.org/2020/04/03/whats-a-string-in-the-jooq-api/

I hope this helps,
Lukas

On Fri, May 28, 2021 at 8:48 PM Boris Heismann <boris.h...@gmail.com> wrote:
Hi,

I'm evaluating jOOQ pro trial 3.14.11. I'm playing with it without to use code 
generator. Using fetch.intoMap("author.id<http://author.id>") produces:
java.lang.IllegalArgumentException: Field (null) is not contained in Row 
("author"."id", "author"."first_name", "author"."last_name", "book"."id", 
"book"."author_id", "book"."name", "book"."pubshing_date")

The RDBMS used is MariaDB. The piece of code is:
        SelectFinalStep<Record> select = dslContext.select()
                .from(table("author")
                        .join(table("book"))
                        .on(field("book.author_id")
                                .eq(field("author.id<http://author.id>"))))
                .where(field("book.author_id")
                        .eq(1));
select.fetch().intoMap("author.id<http://author.id>");

Using select.fetch().intoMap("id") generates a warning that field "id" is 
ambiguous because the table "book" uses it as well:
INFO: Ambiguous match found for id. Both "author"."id" and "book"."id" match.

What is the right proceeding here?

Best regards.

Boris


--
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 jooq-user+...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/jooq-user/9c11fa4c-a325-4bd3-8784-54989d926c26n%40googlegroups.com<https://groups.google.com/d/msgid/jooq-user/9c11fa4c-a325-4bd3-8784-54989d926c26n%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
You received this message because you are subscribed to a topic in the Google 
Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit 
https://groups.google.com/d/topic/jooq-user/JLccMEQqF1k/unsubscribe.
To unsubscribe from this group and all its topics, send an email to 
jooq-user+...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/jooq-user/CAB4ELO5yGS-upLv7bo9CN%3DE045%3D2Pr1kXwbucRiXA_JQe7KLnw%40mail.gmail.com<https://groups.google.com/d/msgid/jooq-user/CAB4ELO5yGS-upLv7bo9CN%3DE045%3D2Pr1kXwbucRiXA_JQe7KLnw%40mail.gmail.com?utm_medium=email&utm_source=footer>.
--
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 jooq-user+...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/jooq-user/CAPZkK29PJyMyYYiT%3DOute0cVmLYmyY3Hb0U46JOnODj2EkmXGw%40mail.gmail.com<https://groups.google.com/d/msgid/jooq-user/CAPZkK29PJyMyYYiT%3DOute0cVmLYmyY3Hb0U46JOnODj2EkmXGw%40mail.gmail.com?utm_medium=email&utm_source=footer>.
--
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 
jooq-user+unsubscr...@googlegroups.com<mailto:jooq-user+unsubscr...@googlegroups.com>.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/jooq-user/be96af28-2a09-4a8f-a80e-1f869d337caen%40googlegroups.com<https://groups.google.com/d/msgid/jooq-user/be96af28-2a09-4a8f-a80e-1f869d337caen%40googlegroups.com?utm_medium=email&utm_source=footer>.

-- 
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 jooq-user+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/jooq-user/AM7PR03MB66107F5B7360E92AF2C619239F3F9%40AM7PR03MB6610.eurprd03.prod.outlook.com.

Reply via email to