vector
vector

Reputation: 7576

How to retrieve the latest created db entry?

I have the following class and need to manually increment the nextId field.

class SomeIdClass {

Family family
Integer nextId = 0
long timeCreated = new Date().time }

So far I've been trying to retrieve and the latest db entry to increment it and I'm just not having any luck with that. Or am I just going about it in a totally wrong manner?

Thaks

ps: this is what I tried but get a list of Package.SomeId objects

 def si = SomeId.executeQuery(" from SomeId where nextId =  (select max( nextId ) from SomeId) ")

Upvotes: 5

Views: 10318

Answers (8)

Jay
Jay

Reputation: 193

Ummh, try

SomeIdClass.where {
   // any criteria - or none
}.max('nextId').list()[0]

But of course you should be using a sequence generator for ids.

Upvotes: 0

BenHuman
BenHuman

Reputation: 175

You can simply get the last saved value this way:

 //works only if the primary key 'id' is non-composite
 def lastEntry = SomeIdClass.last(sort: 'id')


//alternative method which will work even for composite primary key
def entryList= SomeIdClass.findAll{[sort:'id',order:'asc']}.last()

Upvotes: 3

user3877963
user3877963

Reputation: 348

// retrieve the last person

def p = Person.last()

//get the current id
def currentId = p.id

//increment the id manually
def nextId = currentId+1


You can also use the generator in the domain class mappings.



    static mapping = {
            table 'PERSON'
            id  generator: 'increment'
            version false

        }

Upvotes: 0

Charles Wood
Charles Wood

Reputation: 882

From http://www.hsqldb.org/doc/guide/ch09.html#create_table-section

The last inserted value into an identity column for a connection is available using the function IDENTITY(), for example (where Id is the identity column):

INSERT INTO Test (Id, Name) VALUES (NULL,'Test');
CALL IDENTITY();

So, assuming you're using HSQL, you may be able to do:

SomeIdClass.executeQuery("call identity();")

to get the last inserted ID and add to it. MySQL has its own similar feature if HSQL is not the correct route.

This answer is NOT TESTED.

Upvotes: 1

aasukisuki
aasukisuki

Reputation: 1244

What about replacing

long timeCreated = new Date().time

with

Date dateCreated 

which grails automatically populates, to your domain class?

Then you could do something along the lines of

SomeIdClass.listOrderByDateCreated(max: 1, order: "desc")

Also, you do know that by default grails gives every domain object an id that auto-increments right?

Upvotes: 2

Arturo Herrero
Arturo Herrero

Reputation: 13122

My two cents for return the last row in Grails:

DomainClass.find("from DomainClass order by id desc")

Upvotes: 4

Maricel
Maricel

Reputation: 2089

Why not using a sequence? You can use a sequence that is global to all your domain classes or you can define a specific sequence for that domain. You can do something like this:

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

..and if for some reason you still need to have a nextId, you can create a get method that returns the value of id, something like:

def getNextId() {
  return id
}

If you do this then you would need to define nextId as a transient value.

This of course assuming you don't need id and nextId to be different.

Upvotes: 1

ataylor
ataylor

Reputation: 66069

You can do this:

def maxNextId = DomainClass.executeQuery("select max(nextId) from DomainClass")[0]

Without seeing the whole context, it's hard to tell what you're doing, but as an aside, this looks pretty questionable. This method to assign ids to domain objects is probably the wrong way to go about it. But in any case, what if a new object gets inserted into the database with a greater nextId in between the time you do the query and use the value?

Upvotes: 2

Related Questions