Implementing auto increment programitcally

[Satyajeet Kadam]

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

 
   
CREATE TABLE `ts_user` (                                                 
           `user_id` int(11) NOT NULL                              
           `ref_company_id` int(11) default NULL,                                 
           `email` varchar(25) default NULL,                                      
           `password` varchar(10) default NULL,                                   
           `name` varchar(25) default NULL,                                       
           PRIMARY KEY  (`user_id`),                                              
           KEY `ref_company_id` (`ref_company_id`),                               
           FOREIGN KEY (`ref_company_id`) REFERENCES `ts_company` (`company_id`)  
         ) TYPE=InnoDB                                                            
 

I am getting error when i excute query:

  
Email id from AdminManager=[e-mail]
Admin Name from Admin Manager=satya
1
rcount =0
java.sql.SQLException: Syntax error or access violation: You have an error in yo
ur SQL syntax.  Check the manual that corresponds to your MySQL server version f
or the right syntax to use near ')' at line 1
 

  
 
 con=ds.getConnection();
 cadmin=con.createStatement();
 [B]ResultSet ts_usercount=cadmin.executeQuery("select count(*) from ts_user;");[/B] //System.out.println("from ts_user_count"+ts_usercount.getInt("count(*)"));
 if(ts_usercount.next())
 System.out.println(ts_usercount.getInt("count(*)"));
// @param :rcount
// @return: return row count from resultset
 if (ts_usercount!=null)
  {
       int rcount=(ts_usercount.getRow())+1;
  } 
 else
 {
 
 rcount=1;
 }
 
  
 
 System.out.println("rcount ="+rcount);
 

[Scott Selikoff]

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.

[Satyajeet Kadam]

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?

  
ResultSet ts_usercount=cadmin.executeQuery("select count(*)as sum_count  from ts_user;");
 int count=0;
 while(ts_usercount.next())
 {
     int count1= ts_usercount.getInt("sum_count");
     int tcount=count1+1;
     
    [B] System.out.println("tcount==="+tcount);
     Statement stmt = con.createStatement();
     stmt.executeUpdate("insert into ts_user(user_id)"
     +"values('tcount');");          [/B]     
 }   

Tomcat console

tcount===2


databse value
104