jOOQ复杂更新-如何锁定表？

发布于 2024-12-18 08:19:02 字数 1121 浏览 4 评论 0原文

这是嵌套集模型的“添加节点”SQL 查询

LOCK TABLE mytestdb.tbltree WRITE;

SELECT @myRight := rgt FROM mytestdb.tbltree
WHERE name = 'apples';

UPDATE mytestdb.tbltree SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE mytestdb.tbltree SET lft = lft + 2 WHERE lft > @myRight;

INSERT INTO mytestdb.tbltree(name, lft, rgt) 
VALUES('beans', @myRight + 1, @myRight + 2);

UNLOCK TABLES;

以及到 jOOQ 的映射

Record record = create.select(Tbltree.RGT)
    .from(Tbltree.TBLTREE)
    .where(Tbltree.NAME.equal("apples"))
    .fetchOne();

int myright = record.getValue(Tbltree.RGT);

create.update(Tbltree.TBLTREE).set(Tbltree.RGT, Tbltree.RGT.add(2))
      .where(Tbltree.RGT.greaterThan(myright)).execute();

create.update(Tbltree.TBLTREE).set(Tbltree.LFT, Tbltree.LFT.add(2))
      .where(Tbltree.LFT.greaterThan(myright)).execute();

TbltreeRecord record2 = (TbltreeRecord) create 
    .insertInto(Tbltree.TBLTREE, Tbltree.NAME, Tbltree.LFT, Tbltree.RGT)
    .values("cherries",myright+1,myright+2)
    .returning(Tbltree.ID)
    .fetchOne();

我应该如何锁定表？我应该吗？

谢谢

原文

here is a "add node" SQL query for nested set model

LOCK TABLE mytestdb.tbltree WRITE;

SELECT @myRight := rgt FROM mytestdb.tbltree
WHERE name = 'apples';

UPDATE mytestdb.tbltree SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE mytestdb.tbltree SET lft = lft + 2 WHERE lft > @myRight;

INSERT INTO mytestdb.tbltree(name, lft, rgt) 
VALUES('beans', @myRight + 1, @myRight + 2);

UNLOCK TABLES;

and the mapping into jOOQ

Record record = create.select(Tbltree.RGT)
    .from(Tbltree.TBLTREE)
    .where(Tbltree.NAME.equal("apples"))
    .fetchOne();

int myright = record.getValue(Tbltree.RGT);

create.update(Tbltree.TBLTREE).set(Tbltree.RGT, Tbltree.RGT.add(2))
      .where(Tbltree.RGT.greaterThan(myright)).execute();

create.update(Tbltree.TBLTREE).set(Tbltree.LFT, Tbltree.LFT.add(2))
      .where(Tbltree.LFT.greaterThan(myright)).execute();

TbltreeRecord record2 = (TbltreeRecord) create 
    .insertInto(Tbltree.TBLTREE, Tbltree.NAME, Tbltree.LFT, Tbltree.RGT)
    .values("cherries",myright+1,myright+2)
    .returning(Tbltree.ID)
    .fetchOne();

How should I lock the table? Should I?

thx

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

╰沐子 2024-12-25 08:19:02

我不太确定您的各种查询在做什么，所以我不知道您的情况是否需要显式表锁定。但我可以帮助你解决语法问题。

从你的语法来看，我猜测你正在使用 MySQL 作为底层数据库。您至少有三个选项可以将 SQL 转换为 jOOQ：

在 jOOQ 中也使用 MySQL LOCK TABLES 语句：

尝试{
  create.execute("锁定表mytestdb.tbltree WRITE");
  // [...] 你的 jOOQ 代码
}

// 请务必再次解锁您的桌子，以防失败！
最后 {
  create.execute("解锁表");
}

使用 FOR UPDATE 子句，该子句也由 SQL-1992 规范（针对游标）。这个解决方案可能有点昂贵，因为 MySQL 实际上会为 Result 准备一个游标：
```
create.selectFrom(TBLTREE).forUpdate().execute();
```
扩展 jOOQ 并为org.jooq.Query 创建您自己的 org.jooq.Query code>LOCK TABLES 和 UNLOCK TABLES 语句。

I'm not quite sure what your various queries are doing, so I don't know whether explicit table locking is necessary in your case. But I can help you with the syntax.

From your syntax, I'm guessing that you're using MySQL as the underlying database. You have at least three options to translate your SQL into jOOQ:

Use the MySQL LOCK TABLES statement also in jOOQ:

try {
  create.execute("LOCK TABLES mytestdb.tbltree WRITE");
  // [...] your jOOQ code
}

// Be sure to unlock your tables again, in case of failure!
finally {
  create.execute("UNLOCK TABLES");
}

Use the FOR UPDATE clause, which is also specified by the SQL-1992 specification (for cursors). This solution might be a bit expensive, as MySQL will actually prepare a cursor for the Result:
```
create.selectFrom(TBLTREE).forUpdate().execute();
```
Extend jOOQ and create your own org.jooq.Query for the LOCK TABLES and UNLOCK TABLES statements.