jOOQ - how to use .whereNotExists() properly?

问题:

I want to persist daily close entities, representing close prices of stocks.

public class DailyData {
    private Long       id;
    private String     ticker;
    private BigDecimal open;
    private BigDecimal high;
    private BigDecimal low;
    private BigDecimal close;
    private Timestamp  date;
    //getters, setters
}

Because of the limited API of the data provider, I may get duplicate entries for certain dates (if, for example, I only need two days, I still need to ask for a month worth of data). Obviously, I only want to have one record per date, so any date that already exists in the DB should not be persisted.

This may have already been answered here, but I am having trouble implementing it in practice. In particular, I don't understand how to pass actual values to be persisted. This is adapted from the example in the link:

Param<Integer> myId = param("date", Timestamp.class);
create.insertInto(DATA, DATA.TICKER, DATA.OPEN, DATA.HIGH, DATA.LOW, DATA.CLOSE, DATA.DATE)
            .select(
                    select(
                            date,
                            param("ticker", DATA.TICKER.getType()),
                            param("open", DATA.OPEN.getType()),
                            param("high", DATA.HIGH.getType()),
                            param("low", DATA.LOW.getType()),
                            param("close", DATA.CLOSE.getType()),
                            param("date", DATA.DATE.getType())
                    )
                            .whereNotExists(
                                    selectOne()
                                            .from(DATA)
                                            .where(DATA.DATE.eq(date))
                            )
            );

1. Where are the actual values passed on in the example? There is no call to .values() DSL command, which normally appears in jOOQ documentation to tell it what values are to be inserted.
2. Is .execute at the end not needed?
3. There is a batchInsert() command to persist many entities/rows at once. Is there a batch variety of the above mentioned example? Or do I simply have to iterate through all the entities and perform the uniqueness check on each one separately?

回答1:

1. Where are the actual values passed on in the example? There is no call to .values() DSL command, which normally appears in jOOQ documentation to tell it what values are to be inserted.

Why are you using the named parameter API through DSL.param()? Just pass DSL.val() and you'll be fine. E.g.

select(
    date,
    val(ticker),
    val(open),
    val(high),
    val(low),
    val(close),
    val(date)
)

In fact, there is also a DSL.param(String, T) method, which you could use to pass actual values.

Probably there should be more overloads. I've created a feature request for this: https://github.com/jOOQ/jOOQ/issues/7136

However, this query is probably better implemented using INSERT .. ON CONFLICT in PostgreSQL. See also my answer to this question here.

2. Is .execute at the end not needed?

Yes it is.

3. There is a batchInsert() command to persist many entities/rows at once. Is there a batch variety of the above mentioned example? Or do I simply have to iterate through all the entities and perform the uniqueness check on each one separately?

You can batch any statement. The relevant documentation is here: https://www.jooq.org/doc/latest/manual/sql-execution/batch-execution