creating UpdatableRecord and TableRecord instances?

[Manuel Rossetti]

I have a use case that I am working on that I thought would be made easier by using jooq.  I've used jooq in the past but mostly with generated code.  This use case cannot assume generated code.

I am attempting to develop a simple tabular abstraction for reading and writing tabular data from files.  My plan is to have SQLite be the backend, but this would be hidden from the user by the abstraction.  The purpose of the abstraction is to make working with simple tabular data files easier for novices (students). I don’t want to use CSV.

I considered using java's RowSet abstractions (specifically CachedRowSet) but thought that jooq's Result and Record abstractions would be better.  Also, I hoped to take advantage of some the batch processing within jooq or the loader API.  

Here are the assumptions:

1. user specifies 1 or more columns and their data types (limited to boolean, double, long, integer, string)

2. there will be readers for reading in rows and other methods to extract columns, or parts of the store data to arrays etc.

3. there will be methods for writing row based data as specified in 1.

4. the processing will be sequential.  Open, read, close.  Open, write, close.

5. strive for a very simple abstraction

Using jooq it is trivial to create the underlying database, fields, table, etc.  The issue that has stymied me is working with Result and Record.  I hoped to use them during the reading and writing process.  Since I do not have generated classes, there are limitations to this approach.

The basic issue is how to make instances of UpdatableRecord and TableRecord and my own instances of Result that contains the records.  The docs say to not use UpdatableRecordImpl and TableRecordImpl.  

My research turned up this stack overflow question.  But, that ultimately assumes generated code.  So, my questions are:

1.     Is JOOQ a good underlying abstraction for this use case?

2.     Is so, what is the basic strategy to approach this problem using JOOQ?

3.     Is it possible to create UpdatableRecords and TableRecords w/o generated code? If so, what is the basic approach?

[Lukas Eder]

Hi Manuel,

Why can't you use generated code in this case?

There's currently no supported API to create table metadata manually without the code generator for using UpdatableRecord, and the likes. The relevant feature request is this:

https://github.com/jOOQ/jOOQ/issues/7444

Though, do you need it? If you're using the loader API, you can probably construct table/field references without any records?

Thanks

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 jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/651f8037-769b-4347-977e-577e73ea823an%40googlegroups.com.

[Manuel Rossetti]

Thanks for the excellent support.  Yes, issue 7444 is very much related to what I would like to do.  I cannot use the code generator because the functionality would be in a library and generating the code automatically for the user would not be feasible (given their arbitrary column specifications).  I have used the Loader API on some Excel work with JOOQ before and will see if that will be sufficient without Records and Result functionality.  I may investigate the use of the internal API but I understand the reasons for not doing so.

BTW. I am hoping to have a unit on JOOQ in my database class this fall.  I think students will find it very intersting.

[Lukas Eder]

Thanks, Manual. Makes sense - dynamic schema = no code generation.

I keep forgetting, there are CustomTable and CustomRecord which may be useful as well here:

https://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-queryparts/

Of course, you can always use the internal API. We won't break things for no reason, especially not between patch releases, but between minor releases, there are always new things that are added or changed, so it's at your own risk.

Would love to learn what jOOQ specific stuff you're teaching!

Cheers,

Lukas

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/d776d036-57d2-46c6-a646-9e3787c34399n%40googlegroups.com.

[Manuel Rossetti]

If I understand the CustomX classes, I could in theory I derive my own concrete implementations of those classes and potentially use those abstractions. I could then get instances of CustomRecord, for example, and essentially use it like Record, etc.  I guess the advantage would be that then what I do does not depend on the internal API, right?  I will have to investigate it further.

For a small database, I plan to show students how to:

1) use JOOQ code generation

2) use the generated classes for basic CRUD operations
3) illustrate some DDL and some query building

I will be assigning them a homework where they have to repeat the process on another database, fill the database, and do some basic CRUD.  The fact that JOOQ generates the code makes this process have a lot less friction.  Working with Results and Records is just so much easier.  The class is an undergraduate introductory course on database concepts.

[Lukas Eder]

On Wed, Jul 21, 2021 at 11:02 PM Manuel Rossetti <mdrfo...@gmail.com> wrote:

If I understand the CustomX classes, I could in theory I derive my own concrete implementations of those classes and potentially use those abstractions. I could then get instances of CustomRecord, for example, and essentially use it like Record, etc.  I guess the advantage would be that then what I do does not depend on the internal API, right?  I will have to investigate it further.

It's the current way of allowing to access otherwise internal API. It's not as good as https://github.com/jOOQ/jOOQ/issues/7444 will be, hopefully.

 

For a small database, I plan to show students how to:

1) use JOOQ code generation

2) use the generated classes for basic CRUD operations
3) illustrate some DDL and some query building

I will be assigning them a homework where they have to repeat the process on another database, fill the database, and do some basic CRUD.  The fact that JOOQ generates the code makes this process have a lot less friction.  Working with Results and Records is just so much easier.  The class is an undergraduate introductory course on database concepts.

Great! Thanks for sharing :)