I am working on a shoping web application.In which there is a fields for unique value Insertion.that is working fine.But I am inserting product like this
PI01
PI02
PI03
PI04
.
.
.
and I am getting value of last Inserted record like this
String sql="SELECT product_id FROM product_info ORDER BY product_id DESC LIMIT 1";
try {
pstmt=conn.prepareStatement(sql);
rs=pstmt.executeQuery();
if(!rs.next()){
return "PI01";
}
else{
String pastRecordId=rs.getString(1);
System.out.println(pastRecordId);
int id=Integer.parseInt(pastRecordId.substring(2, pastRecordId.length()));
int newId=id+1;
if(newId<=10){
productId="PI0"+newId;
}
else{
productId="PI"+newId;
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("Exiting getNewProductId() in AdminDao");
return productId;
I amusing Java for this case
Problem is last Id it return is using the last alphabetical numerical sorted record from db.
Not the actual last Inserted record in db?
So how can I get actual last Inserted record from db?not the alphabetically sorted record
Which I was getting
My Question is I am looking for some mechanism in database sides that will keep Information about last row that is Inserted in the mysql db table?.
AFAIK you can’t easily. If there are already a lot of them, you would probably need to write a function to change the existing values and then do the ALTER
My Java is rusty, but I’m sure there is a way to do a (inefficient) sort outside of the query, (again, would need to write a function)
Maybe the better solution would be to add an new key that is AUTO_INCREMENT
note that you could “simulate” the PI prefix on the auto_increment using concatenation on every query that accesses that column… which is made a lot easier using a view that has the same name as the table (the table would be renamed)
Auto increment is probably the best option. Searching text columns is much slower than integer columns too, so you should see a increase in query speed.
Is there a reason why you need an alpha numeric key?