如何使用下一个序列号为grails创建的文件设置插入？ [英] How to set up an insert to a grails created file with next sequence number?

问题描述

我使用JMS队列来读取和插入数据到由grails创建的postgres表中。问题是获取下一个序列值。我认为我已经找到了解决方案，并在下面的语句中（通过将DEFAULT放在ID应该去​​的地方），但它不再有效。我必须改变一些东西，因为我需要重新创建表格。解决此问题的最佳方法是什么？

ps = c.prepareStatement（INSERT INTO xml_test（id，version，xml_text）
VALUES（DEFAULT，0，？））;

更新：

为回应建议的解决方案，我做了以下工作：

将此项添加到域中：

  class XmlTest {
 String xmlText 
 static constraints = {
 id generator：'sequence'，params：[name：'xmltest_sequence'] 
} 
} 
  

并将insert语句更改为以下内容：

  ps = c。 prepareStatement（INSERT INTO xml_test（id，version，xml_text）
 VALUES（nextval（'xmltest_sequence'），0，？））; 
  

但是，当我运行该语句时，出现以下错误：

[java] 1 org.postgresql.util.PSQLException：ERROR：relationxmltest_sequencedoes not exist

有什么想法？

解决方案

问题解决了。

事实证明，当grails创建一个表时，它不会为其指定特定的序列生成器。

相反，grails对所有表使用单个序列生成器。这就是所谓的hibernate_sequence。

因此，为了解决这个问题，我在我的SQL语句中包含了nextval：

ps = c.prepareStatement（INSERT INTO xml_test（id，version，text_field）VALUES（nextval（'hibernate_sequence'），0，？））;

I'm using a JMS queue to read from and insert data into a postgres table created by grails. The problem is obtaining the next sequence value. I thought I had found the solution with the following statement (by putting "DEFAULT" where the ID should go), but it's no longer working. I must have changed something, because I needed to recreate the table. What's the best way to get around this problem?

ps = c.prepareStatement("INSERT INTO xml_test (id, version, xml_text) VALUES (DEFAULT, 0, ?)");

UPDATE:

In response to the suggested solution, I did the following:

Added this to the the domain:

class XmlTest {
    String xmlText
    static constraints = {
        id generator:'sequence', params:[name:'xmltest_sequence']
    }
}

And changed the insert statement to the following:

ps = c.prepareStatement("INSERT INTO xml_test (id, version, xml_text) 
VALUES (nextval('xmltest_sequence'), 0, ?)");

However, when I run the statement, I get the following error:

[java] 1 org.postgresql.util.PSQLException: ERROR: relation "xmltest_sequence" does not exist

Any thoughts?

解决方案

Problem solved.

It turns out that when grails creates a table, it doesn't assign a specific sequence generator to it.

Instead, grails uses a single sequence generator for all tables. This is called "hibernate_sequence".

So, to get around the problem, I included the "nextval" for that in my SQL statement:

ps = c.prepareStatement("INSERT INTO xml_test (id, version, text_field) VALUES (nextval('hibernate_sequence'), 0, ?)");

这篇关于如何使用下一个序列号为grails创建的文件设置插入？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！