File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Implementing auto increment  programitcally Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of REST with Spring (video course) this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Implementing auto increment  programitcally " Watch "Implementing auto increment  programitcally " New topic

Implementing auto increment programitcally

Satyajeet Kadam
Ranch Hand

Joined: Oct 19, 2006
Posts: 224
I using mysql databse, i am implementing auto increament field programitically. Find the no of records in table and incrementing it by 1.
I want to this for "user_id field"

Each time when user adds a new record it will programtically increment user_id by 1 to no of records currently present in ts_user table

I am getting error when i excute query:

Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3749

You should use an alias such as "select count(*) as the_sum ..." then you can call getInt("the_sum") to read it. In particular though, selecting count is a *really* bad practice for auto-incrementing data. First and foremost, if an old record is deleted, the entire system stops being able to insert records. Selecting the max id + 1 is generally better, but keep in mind none of these techniques are thread safe if you don't wrap them in a transaction. Two users both trying to insert records could collide if they both read the same id.

[OCA 8 Book] [Blog]
Satyajeet Kadam
Ranch Hand

Joined: Oct 19, 2006
Posts: 224
Thanks for your solution, it incrementing value by 1 but databse is getting upddated with different value.

Q How to implement transactions in follwoing code?

Tomcat console


databse value
subject: Implementing auto increment programitcally
It's not a secret anymore!