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 record insertion Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "record insertion" Watch "record insertion" New topic

record insertion

rudresh kumar
Ranch Hand

Joined: Jan 04, 2006
Posts: 83
HI All,

I am trying to insert records of one application in different tables, like Main details, address, payment, each respective tables.

i am doing like this in my code
if(main details success)
insert address
if(address success)
insert payment this

problem is if any of the table is not inserted then i am not able to roll back,

can some one help me in solving this, an example or reference would be good

Campbell Ritchie

Joined: Oct 13, 2005
Posts: 46412
Sounds as if you need a stored procedure, and a transaction. You will need to read any standard databasees book,

Other members may be better able to help than me, but it goes something like this:-

See whether that helps. To call the procedure, you say "CALL multiple_insert('Rudresh Kumar', '123 High St', 123.45);"

You need to check carefully depending which database you are using; the syntax seems to vary slightly; you might say "execute" instead of "call," or "begin transaction" instead of "start."

References? The Java Tutorial includes stored procedures and transactions.
I haven't found any other good stored procedure tutorials; this is probably the least bad.
Philip Greenspun explains why you need ACID transactions here, and MySqL have a tutorial here.
Jan Cumps

Joined: Dec 20, 2006
Posts: 2565

I would say: transaction: yes, stored procedure: no.

The transaction mechanism will take care that all inserts are applied, or none.

Stored procedures will not help you with that. They are a mechanism to run code inside the database, on the database server.
I would not advise to commit inside a stored procedure. It might surprise java developers who are trying to rollback. They think that they rolled back to were they started the transaction, but they are only rolling back to after the stored procedure (Applying the Rule of Least Surprise).

Regards, Jan
[ May 12, 2007: Message edited by: Jan Cumps ]

OCUP UML fundamental and ITIL foundation
Herman Schelti
Ranch Hand

Joined: Jul 17, 2006
Posts: 387
hi Rudresh,

If you use only one Connection object you can do a rollback on all tables by:
1. conn.setAutoCommit(false); //default-value = "true"
2. conn.rollback() after failure, conn.commit() after succes.

Campbell Ritchie

Joined: Oct 13, 2005
Posts: 46412
Thank you, Jan Cumps. I shall remember that.
I agree. Here's the link:
subject: record insertion
It's not a secret anymore!