This week's book giveaway is in the OCAJP forum.
We're giving away four copies of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) and have Khalid A Mughal & Rolf W Rasmussen on-line!
See this thread for details.
Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Auto increment the Varchar datatype

 
Mike Thomson
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I am developing a web application with Spring, Hibernate and MySql.
For database tables, I am using the auto increment number with int datatype. However, I want to use the varchar datatype as autoincrement.

for example:
--------------
for employee table

EMP1001
EMP1002
EMP1003
...
...


for dept table

DPT2001
DPT2002
.....

As shown in above example I also want to specify the range of the autoincrement. employee table range 1001-2000..like that.
Please help to provide some advice on how to achieve this for Mysql in Hibernate without much performance hit.
 
Joe Harry
Ranch Hand
Posts: 10124
3
Eclipse IDE Mac PPC Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You could do that using Sequences I guess. But why would you need to have a varchar to be aoto incremented?
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, since this is MySQL you don't have seqences availabkle to you. And even if you did, you would need a function too. Sequences don't support char data types, they are only ever intended to work with numeric values.

STepping back a moment, from a data modelling point of view why are you using character data? What does EMP or DPT tell you, other than a (redundant) short hand clue to the entity your key identifies? And do the incremented value have to be sequential?
 
Mike Thomson
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

why are you using character data?


Since all the tables id starts with 1,2,3... and I have complex relationship across the tables identifying the PK-FK combination is very confusing. Instead of all tha tables primary key starts with 1,2,3.... I can have a more meaningful id.


do the incremented value have to be sequential?


Not necessarliy, but how to configure it in hibernate for id.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic