File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes Auto increment the Varchar datatype Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Auto increment the Varchar datatype" Watch "Auto increment the Varchar datatype" New topic
Author

Auto increment the Varchar datatype

Mike Thomson
Ranch Hand

Joined: Nov 07, 2007
Posts: 115
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

Joined: Sep 26, 2006
Posts: 9381
    
    2

You could do that using Sequences I guess. But why would you need to have a varchar to be aoto incremented?


SCJP 1.4, SCWCD 1.4 - Hints for you, Certified Scrum Master
Did a rm -R / to find out that I lost my entire Linux installation!
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Mike Thomson
Ranch Hand

Joined: Nov 07, 2007
Posts: 115

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Auto increment the Varchar datatype