Create an autoincrement (non id) field in Grails & Oracle

Question

I have to keep track of sequential submissions per fiscal year. Given the fiscal year '2015' then the numbering should be '2015001, 2015002, 2015003, etc'.

I have defined a domain class to keep track of these settings:

class MYAPPConfig {
    String fiscalYear
    Integer requestCount

    static constraints = {
        fiscalYear (size: 4..4, nullable: false)
        requestCount(max: 999, nullable: false)
    }
}

The idea is that for a new fiscal year I will add a new record and the 'requestCount' will be reset to 0 (or 1 depending on how Grails wants to manage it).

Ideally this field should be mapped to an Oracle sequence field. If that's not possible then should I manage the increment logic in a service method?

My Grails version is 2.4.2

Thanks.

Salvador Valencia Salvador Valencia · Accepted Answer · 2014-08-28T05:24:43

I figured it out.

I took a different route (after more googling). I added dateCreated to my model which is managed by Grails so its updated automatically. So all I needed to do is get the record with the latest date (every year we will add a new record for the coming fiscal year). Take note of the [0] at the end of the call, that flattens the array of arrays returned and allows me to deal with a single object.

My model now looks like this ( MYAPPConfig.groovy ) :

class MYAPPConfig {         
    String fiscalYear
    Integer requestCount 
    Date dateCreated

    static constraints = {
        fiscalYear (size: 4..4, nullable: false)
        requestCount(max: 999, nullable: false)     
    }    
}

I created the following service ( ManageRequestsService.groovy )

import grails.transaction.Transactional

@Transactional
class ManageRequestService {

def getNextTrackingId() {
    def latestConfig = MYAPPConfig.listOrderByDateCreated(max:1, order: "desc")[0]
    def latestFiscal = latestConfig.fiscalYear
    Integer sequence = latestConfig.requestCount
    sequence++      
    latestConfig.requestCount = sequence
    latestConfig.save(flush:true)       
    return latestFiscal + sprintf('%03d', sequence)
}

}

And in my controller ( MyTestController.groovy ) I have:

class MyTestController {
    def manageRequestsService

    def test() {
        def trackingId = manageRequestsService.getNextTrackingId()
        render "Next id is: ${trackingId}"
    }
}

Giving the following output ( http://localhost:8080/MYAPP/myTest/test ):

Next id is: 2015001

Refresh page!

Next id is: 2015002

Refresh again!

Next id is: 2015003

Create an autoincrement (non id) field in Grails & Oracle

1 Answers