0

In Grails I would like to have an id that has a prefix and is autoincremented.

I would have these prefixes: A and B.

Then I would have a following sequence:

new MyObject('A').save() ---> id = A-1

new MyObject('A').save() ---> id = A-2

new MyObject('B').save() ---> id = A-3

and so on... Is it possible to combine assigned generator with auto-increment?

Amio.io
  • 20,677
  • 15
  • 82
  • 117
  • What's the underlying DB? – Mike W Mar 16 '17 at 13:46
  • @mikew, it is MySQL. – Amio.io Mar 16 '17 at 13:48
  • 1
    Do you have the notion of sequences in MySQL like Oracle? https://docs.oracle.com/cd/B28359_01/server.111/b28310/views002.htm#ADMIN11792 You could set the domain strategy to assigned, pull the value from the sequence before saving and prefix the letter then save – Mike W Mar 16 '17 at 14:09
  • Interesting idea. Have ever done it in Grails. (Of course, I'll have a look but to hear about an experience would be nice. ) – Amio.io Mar 16 '17 at 14:17

2 Answers2

1

Answer is Oracle specific but gives general idea.

Create a sequence in DB:

create sequence MY_SEQ minvalue 1 maxvalue 9999999 start with 1 increment by 1;

Then in Grails:

class MyService {

    def dataSource

    def save( params ) {
        def myDom = new MyDomain( params )
        myDom.id = generateId()
        myDom.save()
    }

    def generateId() {
        def db
        try {
            db = Sql.newInstance( dataSource )
        "A-${db.firstRow( 'SELECT MY_SEQ.NEXTVAL NEXT_ID FROM DUAL' ).NEXT_ID}"
        }
        finally {
            db?.close()
        }
    }
}
Mike W
  • 3,853
  • 2
  • 11
  • 18
0

Just leave the primary key as an integer and add a getter method to derive the A1, B1 value. It's not a good idea to mess with primary keys like you're proposing.

Todd Sharp
  • 3,207
  • 2
  • 19
  • 27
  • thx but we need a descriptive id. Currently we have `{A|B}-{randomlyGeneratedLong()}`. – Amio.io Mar 16 '17 at 12:12
  • That's not the job of a primary key. A primary key is used to uniquely identify a database row. If anything, what you want is a composite key, but GORM discourages them. More [here](http://stackoverflow.com/a/7383118/511250). – Todd Sharp Mar 16 '17 at 13:54