jOOQ binding insert values via bean

[Marcel Overdijk]

I'm currently using JDBI on a project with a DAO like:

@SqlBatch("insert_continent")
fun insertContinents(@BindBean continents: List<Continent>)

Note this uses the @BindBean to automatically bind bean properties to sql statements.

E.g. the insert_continent sql file contains:

INSERT INTO continent
( id
, code
, name
)
VALUES
( :id
, :code
, :name
);

and the bean is Java class like:

public class Continent implements Serializable {

    private String id;
    private String code;
    private String name;

    

    // getters + setters

}

Now with jOOQ I want to do something similar, e..g having an insert like:

val continent = .. // some continent

val query = ctx
    .insertInto(
        table("continent"),
        field("id"),
        field("code"))

        field("name"))
    .values(
        continent.id,
        continent.code,

        continent.name
    )

But then instead of access all properties by hand, just bind the bean.

Would that be possible with jOOQ?

Note: I do not want to use code generation for this.

Also the Continent class is provided and cannot be changed.

Thanks,

Marcel

[Lukas Eder]

Hi Marcel,

The SQL DSL can't do that for you, but you can turn any POJO into a Record using Record.from() or DSLContext.newRecord(...). You can then use .insertInto(table).set(record). There's a RecordUnmapper SPI that governs how to "unmap" POJOs into records, with a DefaultRecordUnmapper implementation that works just like the DefaultRecordMapper in the other way. Use this SPI in case you need to override the defaults.

Note, I always point out the benefits of using the code generator to folks who don't:

https://blog.jooq.org/why-you-should-use-jooq-with-code-generation/

The main reason not to use the code generator is because the schema is dynamic. If it isn't, you'll get tons of benefits from providing type information to jOOQ (and work around tons of problems).

I hope this helps,

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/6eca1697-beef-4c63-921a-757cdcf0d8a7n%40googlegroups.com.

[Marcel Overdijk]

Hi Lukas, and thanks for your reply. This helps a lot.

I used jOOQ In the past with code generation and I understand the benefits especially in terms of type information and type safety.

In my case I'm not having a dynamic schema, but also not an active database (I need to generate create table statements + insert statements).

After reading your blog post I found about the DDLDatabase which gives me the possibility to generate classes after all, which is very nice.

So I came up with this:

        Continent continent = new Continent();
        continent.setId("europe");
        continent.setAlphaCode("eu");
        continent.setName("Europe");

        ContinentRecord continentRecord = ctx.newRecord(CONTINENT, continent);

        String insert = ctx
                .insertInto(CONTINENT)
                .set(continentRecord)
                .getSQL(ParamType.INLINED);

Notice the `alphaCode` in my Continent which is just `code` in the table.

The generated statement then becomes:

insert into continent (id, name) values ('europe', 'Europe')

because it cannot map the alphaCode/code it just omits it...

I also tried with:

        String insert = ctx
                .insertInto(CONTINENT)
                .columns(CONTINENT.fields())
                .values(continentRecord)
                .getSQL(ParamType.INLINED);

but it the just supplies a null value when it cannot find a mapping:

insert into continent (id, code, name) values ('europe', null, 'Europe')

In terms of type safety, would it be possible to fail (exception) when there is no default mapping available?

And would there be a way to implement a RecordUnmapper where one only needs to map fields that do not match automatically?
Or would it be better to use another tool - like MapStruct - to map from Continent POJO to ContinentRecord?

Thanks,

Marcel

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/At5WzUp-1Po/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/CAB4ELO5JqTBn1hKXcMVrCzHcFqodY%3D1zF0Xub6XW0J-ReAx_yg%40mail.gmail.com.

[Lukas Eder]

Hi Marcel,

On Mon, Mar 18, 2024 at 3:38 PM Marcel Overdijk <marcelo...@gmail.com> wrote:

Hi Lukas, and thanks for your reply. This helps a lot.

I used jOOQ In the past with code generation and I understand the benefits especially in terms of type information and type safety.

Sure, I knew you were aware of it, I just point this out at every occasion, given there are other readers.

 

In terms of type safety, would it be possible to fail (exception) when there is no default mapping available?

You can implement a RecordListener that fails if a record isn't entirely populated.  The RecordListener.loadEnd() event could help here. I don't think this would be a good out of the box behaviour. There's no reason to assume that everyone always uses 1:1 POJO to Record mappings.

 

And would there be a way to implement a RecordUnmapper where one only needs to map fields that do not match automatically?

You can implement one that delegates to the DefaultRecordUnmapper, and then does some extra work on the resulting Record.

 

Or would it be better to use another tool - like MapStruct - to map from Continent POJO to ContinentRecord?

Well, not sure what "better" means in this context. I can't recommend other tools because I don't know them. You'll exchange one set of problems for another, as I'd expect :)

[Marcel Overdijk]

Yes I see. I will check out the RecordListener later then.

Btw, based on the generated classes, I wanted to see if I could generate the DDL again from it ;-)

I came up with this:

        // Table creation statement.

        String create = ctx
                .createTable(COUNTRY)
                .columns(COUNTRY.fields())
                .constraint(primaryKey(COUNTRY.getPrimaryKey().getFields()))
                .constraints(
                        COUNTRY.getUniqueKeys().stream()
                                .map(it -> unique(it.getFields()))
                                .collect(Collectors.toList())
                )
                .constraints(
                        COUNTRY.getReferences().stream()
                                .map(it -> foreignKey(it.getFields()).references(it.getInverseKey().getTable(), it.getKeyFields()))
                                .collect(Collectors.toList())
                )
                .getSQL(ParamType.INLINED);

        println(create);

        COUNTRY.getIndexes().forEach((index) -> {
            String stmt = ctx
                    .createIndex(index.getName())
                    .on(index.getTable(), index.getFields())
                    .getSQL(ParamType.INLINED);

            println(stmt);
        });

As far as I could see there is no one-liner that dumps the whole table creation including columns, constraints and indexes, right?

So above -- and especially the  getInverseKey().getTable(), in the fk -- is a good approach, or am I missing something?

Cheers,

Marcel

--

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/At5WzUp-1Po/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/CAB4ELO7Kr1duM_cyv8yRTaKC-E8-gnfw10bgiWncYjz7UjTQTQ%40mail.gmail.com.

[Marcel Overdijk]

Related to generating the schema ddl I found there is the dsl.ddl(schema) method of jooq.

So no need what I'm doing above ;-) and I changed my code to:

val jdbcUrl = "jdbc:sqlite:${outputFile.absolutePath}"
val jdbcProperties = Properties().apply {
    setProperty("date_class", "text")
    setProperty("date_string_format", "yyyy-MM-dd")
}

DriverManager.getConnection(jdbcUrl, jdbcProperties).use { connection ->

    val ctx = using(connection, SQLDialect.SQLITE)

    // Enable foreign keys

    ctx.execute("PRAGMA foreign_keys = ON;")

    // Create schema.

    val ddl = ctx.ddl(DEFAULT_SCHEMA)

    // Execute each DDL statement
    for (query in ddl.queries()) {
        println(query) // Print the DDL to be executed

        query.execute()

    }

Tables are generated, but it fails on the foreign keys then...

I noticed it generates the ddl in the order:

create table country ...

create index ...

alter table country add constraint CONSTRAINT_2E foreign key (continent_id) references continent (id)

which causes:

> SQL [alter table country add constraint CONSTRAINT_2E foreign key (continent_id) references continent (id)]; [SQLITE_ERROR] SQL error or missing database (near "constraint": syntax error)

and I found this stackoverflow article:

https://stackoverflow.com/questions/1884818/how-do-i-add-a-foreign-key-to-an-existing-sqlite-table

which states that SQLite does not support adding a constraint to an existing table, but that it should be part of the create table statement.

Am I using this incorrectly?

PS: I understand that for creating the constraints inline in the create table statement, the order of the tables is important.

And I also see that when using the DDLDatabase in the codegen there is no order maintained in the generated schema class.

So not sure if this can work at all.

Other way would be to use the way I did in my previous post, and create the table.+ columns + constraints, making sure the correct order of execution is maintained...

[Lukas Eder]

On Mon, Mar 18, 2024 at 10:26 PM Marcel Overdijk <marcelo...@gmail.com> wrote:

Related to generating the schema ddl I found there is the dsl.ddl(schema) method of jooq.

Great that you've found it! :)

 

I noticed it generates the ddl in the order:

create table country ...

create index ...

alter table country add constraint CONSTRAINT_2E foreign key (continent_id) references continent (id)

which causes:

> SQL [alter table country add constraint CONSTRAINT_2E foreign key (continent_id) references continent (id)]; [SQLITE_ERROR] SQL error or missing database (near "constraint": syntax error)

Can you please create an issue:

https://github.com/jOOQ/jOOQ/issues/new/choose

 

PS: I understand that for creating the constraints inline in the create table statement, the order of the tables is important.

And I also see that when using the DDLDatabase in the codegen there is no order maintained in the generated schema class.

So not sure if this can work at all.

We can do a topological sort.

[Marcel Overdijk]

Thanks Lukas,

I created https://github.com/jOOQ/jOOQ/issues/16470 for this.

> We can do a topological sort.

That sounds hopeful!  Not sure how to do it, so if there is anything you share that would be great.

I'm also posting here some example using Mapstruct, for if somebody else comes to this thread via Google.

So imagine the table has a "code" column but in the POJO it is "alpha2Code".

Then we can use Mapstruct to create a mapper like:

import org.jooq.RecordUnmapper;
import org.mapstruct.Mapper;
import org.mapstruct.Mapping;
import org.mapstruct.ReportingPolicy;
import org.mapstruct.factory.Mappers;

@Mapper(unmappedTargetPolicy = ReportingPolicy.ERROR)
public interface ContinentMapper extends RecordUnmapper<Continent, ContinentRecord> {

    ContinentMapper INSTANCE = Mappers.getMapper(ContinentMapper.class);

    @Override
    @Mapping(target = "code", source = "alpha2Code")
    ContinentRecord unmap(Continent source);
}

Above Mapstruct mapper will do the trick:

Continent continent = new Continent();
continent.setId("europe");

continent.setAlpha2Code("eu");
continent.setName("Europe");
continent.setDemonym("European");

// Map POJO to jOOQ generated record.

ContinentRecord continentRecord = ContinentMapper.INSTANCE.unmap(continent);

The nice thing with the (unmappedTargetPolicy = ReportingPolicy.ERROR is that the compilation will fail if there target properties that are not found in the source.

This provides full type safety :-)

Cheers,

Marcel

--

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/At5WzUp-1Po/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/CAB4ELO7KcYd-2%2Br0DLvM--kzAvS1Vg9r8Y34Gm2LOx216dMJkA%40mail.gmail.com.

[Lukas Eder]

On Tue, Mar 19, 2024 at 9:36 AM Marcel Overdijk <marcelo...@gmail.com> wrote:

Thanks Lukas,

I created https://github.com/jOOQ/jOOQ/issues/16470 for this.

> We can do a topological sort.

That sounds hopeful!  Not sure how to do it, so if there is anything you share that would be great.

Well, if there are no cycles, then it's just a DAG where tables are vertices and foreign keys are edges.

If there are cycles, then we must use this approach (the only way I can see how cycles can be made in SQLite):

create table t (i int primary key);
create table u (i int references t, j int primary key);
alter table t add j int references u;

This form of the ALTER statement currently cannot be represented in the jOOQ API, so it won't be an easy fix.