File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
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

Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Auto increment the Varchar datatype" Watch "Auto increment the Varchar datatype" New topic

Auto increment the Varchar datatype

Mike Thomson
Ranch Hand

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


for dept table


As shown in above example I also want to specify the range of the autoincrement. employee table range 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: 9344

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

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:
subject: Auto increment the Varchar datatype
Similar Threads
Leaving any property unmapped to a column in Hibernate using annotation
sequence generator with mysql
Hibernate Mapping Types.
Java Datatype mapping with psotgres cidr type
Joining tables