jOOQ-如何正确使用.whereNotExists()? [英] 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
}
由于API的限制数据提供者,我可能会在某些日期获得重复的条目(例如,如果我只需要两天,我仍然需要索取一个月的数据)。显然,我只希望每个日期有一条记录,因此数据库中已经存在的任何日期都不应保留。
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))
)
);
- 示例中传递的实际值在哪里?不会调用.values()DSL命令,该命令通常出现在jOOQ文档中,以告诉它要插入哪些值。
- .execute结尾是否不需要?
- 有一个batchInsert()命令可一次持久存储许多实体/行。上面提到的例子有很多吗?还是只需要遍历所有实体并分别对每个实体执行唯一性检查?
推荐答案
- 示例中传递的实际值在哪里?没有调用.values()DSL命令,该命令通常出现在jOOQ文档中,以告诉它要插入什么值。
为什么要通过 DSL.param()
使用命名参数API?只需传递 DSL.val()
就可以了。例如,
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)
)
实际上,还有一个 DSL.param(String,T)
方法,您可以使用它传递实际值。
In fact, there is also a DSL.param(String, T)
method, which you could use to pass actual values.
可能应该有更多的重载。我为此创建了一个功能请求: https://github.com/jOOQ/jOOQ/ issue / 7136
Probably there should be more overloads. I've created a feature request for this: https://github.com/jOOQ/jOOQ/issues/7136
但是,使用 INSERT .. ON CONFLICT
可以更好地实现此查询PostgreSQL。 在此处另请参见我对这个问题的回答。
However, this query is probably better implemented using INSERT .. ON CONFLICT
in PostgreSQL. See also my answer to this question here.
- 是否不需要在末尾执行。
是的。
- 有一个batchInsert( )命令一次保留多个实体/行。上面提到的例子有很多吗?还是只需要遍历所有实体并分别对每个实体进行唯一性检查?
您可以批处理任何语句。相关文档在这里:
https:// www.jooq.org/doc/latest/manual/sql-execution/batch-execution
You can batch any statement. The relevant documentation is here: https://www.jooq.org/doc/latest/manual/sql-execution/batch-execution
这篇关于jOOQ-如何正确使用.whereNotExists()?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!