How to get the last created db record?

Question

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 have been trying to extract the last db entry as well to increase it, and I was just out of luck with this. Or am I just not quite right about this?

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) ") 

Answer 1

You can do it:

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

Not seeing the whole context, it’s hard to say what you are doing, but aside it looks rather dubious. This method for assigning identifiers to domain objects is probably the wrong way. But in any case, what if a new object is inserted into the database with a large nextId value between the query execution time and using the value?

Answer 2

My two cents for returning the last line in Grails:

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

Answer 3

How about a replacement

 long timeCreated = new Date().time 

with

 Date dateCreated 

which grails automatically populates into your domain class?

Then you could do something in the lines

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

Also, do you know that by default grails gives each domain object an identifier that automatically increments it?

Answer 4

Why not use a sequence? You can use a sequence that is global for 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 nextId, you can create a get method that returns an id value, something like:

 def getNextId() { return id } 

If you do this, you will need to define nextId as a transition value.

This, of course, assumes that you do not need id, and nextId is different.

Answer 5

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

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

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

So, if you use HSQL, you can do:

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

to get the last inserted id and add to it. MySQL has its own similar function if HSQL is not the right route.

This answer has NOT been verified.

Answer 6

You can simply get the last saved value as follows:

  //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() 

Answer 7

 // 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 }