How do I get the next ID in GRAILS?
I need to get the next available id in sequence using GORM or a custom query. How can i do this?
I am using oracle.
UPDATE:
I need this value BEFORE , because my string will use this value. I have a table called IMAGE, with a FILE_NAME column, for example:
- ID | FILE_NAME
- 123 | foo_bar_123.png
- 124 | xxx_yyy_124.png
Tks is a lot.
source to share
Why do you need a value before inserting? Could you please get the information as part of the insert using a sentence RETURNING
i.e.
INSERT INTO table_name( id, file_name )
VALUES( some_sequence.nextval, <<bind variable>> )
RETURNING id INTO <<bind variable>>
Or access it after pasting with
SELECT sequence_name.currval
FROM dual
currval
sequence returns the most recent value of the sequence created in the current session, so it is effectively thread safe. Since sequences are meant to provide numbers in a highly parallel environment, you can't really know what will happen nextval
if you don't really get it nextval
. Even if you could, there is no guarantee that another thread won't show up and get the value you looked at before yours happened INSERT
, so it would be safe to look into nextval
the multiplayer environment.
source to share
I adapted @ Cave's advice. So my solution is:
Change my mapping:
class Image {
...
id column: "ID", generator: "sequence", params: [sequence:"MY_SEQUENCE"]
...
}
in
class Image {
...
id column: "ID", generator: "assigned"
...
}
And set id mannualy using this:
def getLastImageId(){
def sql = "SELECT MY_SEQUENCE.nextval FROM dual"
def query = sessionFactory.currentSession.createSQLQuery(sql);
def result = query.list()
return result[0]
}
...
newImage.id = getLastImageId()
newImage.fileName = "foo_bar_${newImage.id}.png"
source to share