Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Posts
    37

    Question Unanswered: java jdbc transaction & oracle sequence

    Hello,

    I wrote following code.

    Code:
    	public void insert(KKBInquiryVO bean) throws Exception {
    		Connection cn = null;
    		try {
    			cn = OracleDAOFactory.getConnection();
    			cn.setAutoCommit(false);
    			OracleInsertSQLs sql = OracleInsertSQLs.getInstance();
    
    			// get ID for KKBInquiries
    			Long ID = sql.getNextID(cn);
    			bean.setID(ID);
    			sql.insertKKB_INQUIRIES(cn, bean);
    
    			// get ID for Applicants (Debtor)
    			ID = sql.getNextID(cn);
    			bean.getDebtor().setID(ID);
    			sql.insertAPPLICANTS(cn, bean.getDebtor());
    
    			if (bean.getGuarantor1() != null) {
    				// get ID for Applicants (Guarantor1)
    				ID = sql.getNextID(cn);
    				bean.getGuarantor1().setID(ID);
    				sql.insertAPPLICANTS(cn, bean.getGuarantor1());
    				if (bean.getGuarantor2() != null) {
    					// get ID for Applicants (Guarantor1)
    					ID = sql.getNextID(cn);
    					bean.getGuarantor2().setID(ID);
    					sql.insertAPPLICANTS(cn, bean.getGuarantor2());
    				}
    			}
    			cn.setAutoCommit(true);
    			cn.commit();
    		} catch (SQLException sqle) {
    			DbUtils.rollback(cn);
    			throw sqle;
    		} finally {
    			DbUtils.close(cn);
    		}
    	}
    the code for getNextID() :
    Code:
    public Long getNextID(Connection cn) throws SQLException {
    		return (Long) new QueryRunner().query(cn, "SELECT KRS.KRSSEQUENCE.NEXTVAL FROM DUAL", new ScalarHandler());
    	}
    cn : java.sql.Connection
    the insertAPPLICANTS methods throw SQLException's.

    up to this line getNextID is called 4 times, thus 4 values are generated for this thread.

    lets say thread 1 starts getting IDs 1,2, a.s.o.

    1) my first question here is :
    would a second thread get for example ID number 3 from the sequence ? or would it wait for thread 1's transaction until it releases the sequence and get ID number 5 ? are sequences beeing locked ?
    (simple question maybe but I'm new to Oracle and sequences)

    2) after setting AutoCommit to false, starting a transaction lets say an exception is thrown at this line :

    sql.insertAPPLICANTS(cn, bean.getGuarantor2());

    (that's right before the cn.commit(); call)

    what would be the next ID the sequence would generate ? would it re-generate the rolledback numbers 1,2,3,4 ?



    thanks in advance

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by msegmx
    would it re-generate the rolledback numbers 1,2,3,4
    No, never. Once a value has been obtained from sequence, that value is "gone".

    If you want to ensure your IDs have no gaps than sequences are the wrong way to do it.

    Additionally: in most of the cases (95%) putting a meaning into an ID (such as "should not have gaps in there") indicates a wrong DB design.

  3. #3
    Join Date
    Oct 2002
    Posts
    37
    ID is a surrogate key. it's not important to have gaps or not.

    thanks for answering.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •