aspose file tools*
The moose likes JDBC and the fly likes auto increment algo for db tables in java Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "auto increment algo for db tables in java" Watch "auto increment algo for db tables in java" New topic
Author

auto increment algo for db tables in java

farhan ahmed butt
Greenhorn

Joined: Aug 06, 2012
Posts: 12
em really stuck into a problem so need help from you guys.. Actually I am creating a simple website in j2ee however I am not using beans since I want to keep it simple.. the structure of website is like I have different Jsp pages and every jsp page has a corresponding servlet which then connects the jsp page to database to store and retrieve data..

Now the db structure of my application requires that I should know about the primary key that is going to be entered in the db before pressing submit in the form of jsp page.. Lets say I have two tables...
ToeDescription and ToeText..
ToeDescription has ToeID and ToeName etc and
ToeText has ToeID as foreign key among other fields and I have to update in both of them at the same time as per db schema.. So I need to know next ToeID of ToeDescription beforehand in order to maintain the foreign key constraint of ToeText.. Auto increment in mysql simply wont work as toeid has to be same for all the toetext tables toeid fields... I searched and got this algo from internet but It wont add after few increments after which it gives error of "Duplicate primary key"....



ResultSet rs=st.executeQuery("SELECT toe_id FROM toe_description");
int toe_id=0;
if(rs.last()){
System.out.println(toe_id);
toe_id=rs.getInt("toe_id")+1;

System.out.println(toe_id);

This algo however seems perfect since it takes the last primary key and increments it by 1 so we can know in our servlet about the value which will be entered in the table however it gives an duplicate primary key error after inserting few values in the table.. Do you guys know of any other algo which would work or any other method of resolving this problem.???


Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18987
    
    8

Well, that's a horribly inefficient way to get an unused value of toe_id. The fact that it doesn't even work makes it even less useful.

Anyway you're on the completely wrong track. Your database will have a way of assigning a unique value to a primary key field, so use that. When you insert a row, do not assign a value to the primary key field. Instead let the database do that. Then use the feature which your database provides to get the generated key.

If you do this, then you won't have to scan the toe_description table to find an unused value. And you won't risk race conditions between two threads running your code, which you would have done if the code you posted had been fixed to work better.

(And since this is a question about databases, it belongs in the JDBC forum. I will move it there.)
Mukesh Negi
Greenhorn

Joined: Jul 25, 2012
Posts: 17
@farhan: Use the following code.

Statement stmt = db.prepareStatement("your query", Statement.RETURN_GENERATED_KEYS);

stmt.executeUpdate();

ResultSet rs = stmt.getGeneratedKeys();
if (rs.next()){
yourKey = rs.getInt(1);
}


farhan ahmed butt
Greenhorn

Joined: Aug 06, 2012
Posts: 12
Mukesh Negi wrote:@farhan: Use the following code.

Statement stmt = db.prepareStatement("your query", Statement.RETURN_GENERATED_KEYS);

stmt.executeUpdate();

ResultSet rs = stmt.getGeneratedKeys();
if (rs.next()){
yourKey = rs.getInt(1);
}






Hey guys.. Probelm is that I cannot direclty insert in the database with this code.... I think I shuold give you guys an idea.. Thing is that I do know the auto increment method is a better one but my scnerio is a bit different.. Actually I have to get the toe_id value beforehand because I am using it to execute multiple queries after that.....

For example....

ResultSet rs=st.executeQuery("SELECT toe_id FROM toe_description");
int toe_id=0;
if(rs.last()){
System.out.println(toe_id);
toe_id=rs.getInt("toe_id")+1;

System.out.println(toe_id);


}

// So unless I get the exact value of toe_id, how am I suppose to give it later in the queries below which are dependent on this value..

Here I want to get the toe_id field and then use it like this afterwards in the same servlet..

String sql = "INSERT INTO toe_description ( toe_id, toe_name , Date , interface_id ) VALUES ('" + toe_id + "' ,'" + toe_name + "' ,'" + (dateFormat.format(date)) + "','" + InterfaceName + "' )";

String sql1 = "INSERT INTO toe_text (toe_id, type_id , text) VALUES ('" + toe_id + "' , '1' , '" + Purpose + "')";
String sql2 = "INSERT INTO toe_text (toe_id, type_id , text) VALUES ('" + toe_id + "' , '2' , '" + Scope + "')";

So basically I have to use same toe_id value in other tables namely toe_text and toe_description since toe_id is the foreign key in those tables and cannot be changed with an increment of new query.....



Howard Watson
Ranch Hand

Joined: Jan 07, 2004
Posts: 56
I've always used something like this in a transaction:

But, the MySQL documentation:
http://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_last-insert-id
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19783
    
  20

Or use the JDBC way:
1) call execute / executeUpdate with Statement.RETURN_GENERATED_KEYS.
2) call getGeneratedKeys.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
farhan ahmed butt
Greenhorn

Joined: Aug 06, 2012
Posts: 12
Howard Watson wrote:I've always used something like this in a transaction:

But, the MySQL documentation:
http://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_last-insert-id


Okay this seems legitimate but the problem is that ID variable is not initialized outside the if statement.. I have tried it several times but since your ID is not initialized outside IF.. If i do initialize it outside if like int ID; then java keeps on giving error that "ID" in this case must be initialized.. and if I initialize it outside if with int ID=0; then it will duplicate the key next time it executes... What should I doo.. em stuck here.. ((
Howard Watson
Ranch Hand

Joined: Jan 07, 2004
Posts: 56
If I understand the problem right; inside a transaction you would write a record to your first table, use JDBC or SQL to retrieve your new primary key, inside your if (rs.next()) {} you would use the ID as a foreign key in inserts to your second table. Commit the transaction.
Shrenik Patel
Greenhorn

Joined: Aug 06, 2012
Posts: 3
Hi Farhan,

If i am getting all these conversations right then you will need to use LAST_INSERT_ID() in the second query. What it will do, LAST_INSERT_ID() can be used to fetch latest autoincrement id being used in any INSERT query that was just got executed.so you will get last inserted id from the parent table then you can set that id as a foreign key in child table. If you have any confusion about this concept just refer this link http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html You might get clear idea about it.

Thanks
Shrenik Patel
Greenhorn

Joined: Aug 06, 2012
Posts: 3
Hi Farhan,

continuing from the last post, as Paul suggested you don't need to externally specify the auto increment value, let database handle this id generator. You just focus on inserting other values in the tables. After filtering your query looks like:



where in first query toe_id will get generated by database Auto_Increment. where in sql1 & sql2, LAST_INSERT_ID() will be the latest toe_id which was generated in first query.

Thanks
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18987
    
    8

Can I make one other suggestion? Do yourself a favour and use PreparedStatement objects to put data values into SQL statements, rather than constructing the SQL statements with string concatenation like that.

When you do it with string concatenation, it's hard to get the quotes right. And if the data you're trying to insert also contains a quote, then you end up with unmatched quotes and therefore bad SQL. Not to mention that if the data you're trying to insert is coming from user input, it's possible for evil users to do SQL injection attacks which can damage your database. Besides which, the string concatenation version is much harder for programmers to read and understand than the PreparedStatement version.
Mukesh Negi
Greenhorn

Joined: Jul 25, 2012
Posts: 17
@farhan: I'l stick to my above post. This will serve your purpose.
You first insert into your main table where there is auto increment value.

Than by using the following code you will be getting last inserted key. i.e., your last auto incerement value as database system stores the last key value inserted.

ResultSet rs = stmt.getGeneratedKeys();
if (rs.next()){
yourKey = rs.getInt(1);
}

After getting the auto increment value you can insert it into the tables where it is the foreign key.

Hope this will help you.
farhan ahmed butt
Greenhorn

Joined: Aug 06, 2012
Posts: 12
Shrenik Patel wrote:Hi Farhan,

continuing from the last post, as Paul suggested you don't need to externally specify the auto increment value, let database handle this id generator. You just focus on inserting other values in the tables. After filtering your query looks like:



where in first query toe_id will get generated by database Auto_Increment. where in sql1 & sql2, LAST_INSERT_ID() will be the latest toe_id which was generated in first query.

Thanks
,

Just tried this method and its giving me this error

SQLException caught: Cannot add or update a child row: a foreign key constraint fails (`secnok`.`toe_text`, CONSTRAINT `toe_text_ibfk_1` FOREIGN KEY (`toe_id`) REFERENCES `toe_description` (`toe_id`))
So basically problem is that it is inserting sql1 query but when it moves to sql2 its losing the lastt toe_id value I assume thus giving this error!? what should I do now!?
farhan ahmed butt
Greenhorn

Joined: Aug 06, 2012
Posts: 12
Mukesh Negi wrote:@farhan: I'l stick to my above post. This will serve your purpose.
You first insert into your main table where there is auto increment value.

Than by using the following code you will be getting last inserted key. i.e., your last auto incerement value as database system stores the last key value inserted.

ResultSet rs = stmt.getGeneratedKeys();
if (rs.next()){
yourKey = rs.getInt(1);
}

After getting the auto increment value you can insert it into the tables where it is the foreign key.

Hope this will help you.


Hi Mukesh...
As I previously mentioned problem is that the key I need to use out side... which in your code is "yourkey" is declared inside the if statement and cant be access outside it... and if declare it outside if then it will ask me to initialize it aswell which upon doing will result in same problem.. Let me quote you an example...


int youtkey=0;
ResultSet rs = stmt.getGeneratedKeys();
if (rs.next()){
yourKey = rs.getInt(1);
}

now the code is compileable but still will get the same error afterwards... Any suggestions!??
Howard Watson
Ranch Hand

Joined: Jan 07, 2004
Posts: 56
Just curious, are you doing something like this?
farhan ahmed butt
Greenhorn

Joined: Aug 06, 2012
Posts: 12
Howard Watson wrote:Just curious, are you doing something like this?


Well something like this..
Let me post the code...

Howard Watson
Ranch Hand

Joined: Jan 07, 2004
Posts: 56
I think that is why you are struggling with this. And consider implementing it as a transaction.
farhan ahmed butt
Greenhorn

Joined: Aug 06, 2012
Posts: 12
Howard Watson wrote:I think that is why you are struggling with this. And consider implementing it as a transaction.


Thanks a million dude.. You saved me! ))
If you could find little more time.. I have another issue Kindly check that as well...

http://www.coderanch.com/t/589165/JSP/java/Jsp-servlet-back-button
Mukesh Negi
Greenhorn

Joined: Jul 25, 2012
Posts: 17
@Howard @Farhan: I did the same thing without using transactional apprach, and it worked. I used MySql.

According to its documentation the last generated key is stored by the db system for each connection seperately.

I am not getting, why in case of Farhan it was not working.

Do any one know any specific reason why it dint worked, so tha I can correct myseslf..

Thanks.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: auto increment algo for db tables in java