Multi Module Spring Project

[Gawie Kellerman]

Hi,

Can somebody please direct me to an example project or discussion where one can mix in tables/records from various modules into a single DSL context.  To elaborate - what I find not too obvious (and that not only goes for Jooq but also most other JDBC "orm" libraries for that matter) is how to have one database divided into its constituent logical parts as different Java JARs/modules; and make them participate in the same context (fairly dynamically).

Here is a conceptual example.  Same schema/database where:

User, Group, UserInGroup, its management etc is in one Java module 

Document, Version, Meta is in another Java module

A third projec that is able to pull these 2 together over a single context and execution within the same transaction context.

This also leads to another question in that I can only find Jooq filter of tables by schema.  Is there any way that I can filter tables for creation in the project based on table name pattern or manual selection.  Not all tables within the same database is part of the same module (jar). 

BTW - I am trying to do this using Spring; I don't however think that it matters as the problem I am describing is not specific to Spring. 

Note: For the module segregation issue described above; I have found that all "smart" Db technologies like JPA etc makes this very hard - and that the safest route usually is plain JDBC or JDBC with a very simple JDBC assist layer - this however means writing reams of SQL as well as a separation between your code and the underlying structure; which leads to significant run time risk or/and a lot more work.

Regards,

Gawie

[Lukas Eder]

Hi Gawie, 

Thanks for your message and for your interesting questions. I'll comment inline

2017-09-24 21:40 GMT+02:00 Gawie Kellerman <gawie.k...@gmail.com>:

Hi,

Can somebody please direct me to an example project or discussion where one can mix in tables/records from various modules into a single DSL context.  To elaborate - what I find not too obvious (and that not only goes for Jooq but also most other JDBC "orm" libraries for that matter) is how to have one database divided into its constituent logical parts as different Java JARs/modules; and make them participate in the same context (fairly dynamically).

Here is a conceptual example.  Same schema/database where:

User, Group, UserInGroup, its management etc is in one Java module 

Document, Version, Meta is in another Java module

A third projec that is able to pull these 2 together over a single context and execution within the same transaction context.

Indeed, these are very common questions and as you said, they're not really related to any ORM / tool (although some tools like jOOQ may help you get it "right"), but are more strategic architectural decisions one has to make.

I mostly fared best not worrying about modularising a schema too much, and treating it all as one monolithic database. This works well up to some schema / database complexity (e.g. say 1000 tables), when things start getting a bit more tricky.

In any case, if you do decide to modularise your database / schema also from a jOOQ perspective, the code generator will certainly help you with this. I'll be happy to answer any specific questions about that.

 

This also leads to another question in that I can only find Jooq filter of tables by schema.  Is there any way that I can filter tables for creation in the project based on table name pattern or manual selection.  Not all tables within the same database is part of the same module (jar). 

This is what the <include> / <exclude> configuration elements are useful for. You can put arbitrary regular expressions there, up to generating code only for a single table per module.

 

BTW - I am trying to do this using Spring; I don't however think that it matters as the problem I am describing is not specific to Spring. 

Note: For the module segregation issue described above; I have found that all "smart" Db technologies like JPA etc makes this very hard - and that the safest route usually is plain JDBC or JDBC with a very simple JDBC assist layer - this however means writing reams of SQL as well as a separation between your code and the underlying structure; which leads to significant run time risk or/and a lot more work.

Perhaps, I'm not fully aware of that risk / work you're mentioning. Would you mind describing the risk part of this use case a bit more? 

Cheers,

Lukas

[Gawie Kellerman]

Hi Lukas,

First off - thank you for the response.

The subdividing of the schema is more for the purpose of being able to drop/add in 'standalone' components rather than creation smaller logical items.  I find that I use various patterns repeatedly, optional of course.  So I really want to take my user roles and permissions, document management, meta attributes or other and put it in a project/jar/module on its own.  As stated previously, simply creating two jooq-gen.xml's and generating its tables into the relevant projects...  and mixing this project as dependencies into a third spring project makes one project's dao work, the other one fails.

When I talk about the risk of using plain old SQL, over something like JOOQ, JPA or similar:

1. In plain old SQL one has the same column/field repeated across many queries and updates.  It is hence very easy to add a field to the data model and miss it in one or more queries.  Sadly some of these will yield a runtime exception "very late"; or even worse - have no runtime exception but yield an unpopulated value.

2. You have to take care of delimiting etc.  Failure to do so can lead to SQL injection or other nasties. 

I will have a better look at the spec around include - thanks for the comment.  I however have done my test of trying to create 'jooq components' by forcing my test tables in difference schemas.

Regards,

Gawie

--
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/Z9qbQQJnypQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.