• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Auto increment the Varchar datatype

 
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic