开发者

How to set up an insert to a grails created file with next sequence number?

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 开发者_如何学JAVAit'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?


You can get the value of any sequence in PostgreSQL using nextval function, in your case:

INSERT INTO xml_test (id, version, xml_text) VALUES (nextval('sequence_name_for_this_table'), 0, ?);

And in your grails domain class you can choose the sequence name:

static mapping = {
    id generator:sequence, params:[name:'sequence_name_for_this_table']
}


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, ?)");

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜