Looks good but you really shoud put your stmt.close() and conn.close() in a finally block so even if there is an error everything gets cleaned up properly. That syntax is for Java but the principle is the same for any programming language - always make sure you clean up your connections no matter what errors occur.
Locks are released when the containing transaction commits. There is no explicit "release."
Instead of calling "begin" and "commit" as statements, I do something more like below. As Aaron mentioned, this is JDBC, not SQL. Sorry people.
try { ... conn.setAutoCommit(false);
//do the insert on the table that generates the primary key via a sequence PreparedStatement pstmt = conn.prepareStatement ("my prepared statement"); pstmt.executeUpdate();
//your prepared statement above should do an //insert on a table that calls nextval(). //Calling currval() below will guarantee that you'll get //the value created by the insert statement //Check out the documentation on sequence functions
//get the new primary key String get_pkey = "{ ? = call currval('my_seq') }"; CallableStatement = conn.prepareCall(get_pkey); cstmt.registerOutParameter(1, Types.BIGINT); cstmt.execute();
long new_pkey = cstmt.getLong(1);
//do all of your updates/inserts on tables using new_pkey as a foreign key //I like to do this in batches Statement stmt = conn.createStatement(); stmt.addBatch("insert into... ) stmt.addBatch("update whatever set... ) stmt.executeBatch ();
conn.commit();
stmt.close(); conn.close();
} catch(SQLException e1) { //do something with error 1 if (conn != null) { try { conn.rollback(); } catch(SQLException e2) { //do something with error 2 } } }
