Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: setting a default ID with BC4J Entity

Re: setting a default ID with BC4J Entity

From: Martin Doherty <martin.doherty_at_elcaro.moc>
Date: Wed, 13 Nov 2002 12:35:24 -0800
Message-ID: <_LyA9.5$FE.28@news.oracle.com>


Hey, I don't know much about Java, but I'll have a look at it for ya! (old joke rephrased).

I'd just like to point out that SELECT MAX(ID) + 1 is a VERY NAUGHTY WAY of trying to assign a unique key value to a new row. As you found out, it doesn't guarantee uniqueness if multiple transactions are using it simultaneously to generate new keys, plus as the table grows it will execute slower and s-l-o-w-e-r and s--l--o--w--e--r.

Are you aware of Oracle sequence objects? Check the SQL manual for CREATE SEQUENCE command. These handy little doodads are great for generating lots and lots of unique numeric keys without the contention and performance problems of every alternative method. They do have the property of being transaction-independent, which means that the resulting key values committed to your table may end up with gaps in the sequence (i.e. 1,2,5,8,9,10,14 etc.). If this isn't a problem for your application, all is cool.

Since sequences are an Oracle-specific feature, you might consider encapsulating their usage in your code if you need to minimize RDBMS dependencies.

Martin

Owen Gibbins wrote:

>Hello all,
>
>I'm trying to customize an Entity object in order to set the ID column of a
>newly created row to a unique number. I'm overriding the create method of
>the object. I managed to get it to work using the following:
>
> protected void create(AttributeList nameValuePair)
> {
> super.create(nameValuePair);
> final String stmt = "SELECT MAX(ID) + 1 FROM PRODUCER";
> Number id = new Number(0);
> ViewObject idView =
>getDBTransaction().createViewObjectFromQueryStmt(stmt);
> Row idRow = idView.next();
> if(idRow != null)
> {
> Object o = idRow.getAttribute(0);
> if(o != null)
> {
> if(o instanceof Number)
> id = (Number)o;
> else
> throw new Error("ProducerImpl.create: \"" + stmt + "\" did not
>produce type NUMBER.");
> }
> }
> setID(id);
> }
>
>The problem with this is that the SELECT statement only considers rows that
>have been posted to the database. I need the other newly created rows in the
>current transaction to be considered as well. So I tried the following:
>
> protected void create(AttributeList nameValuePair)
> {
> super.create(nameValuePair);
> ViewObject view = getDBTransaction().createViewObject("ProducerView");
> view.setOrderByClause("ID DESC");
> view.executeQuery();
> Number id;
> ProducerViewRow row = (ProducerViewRow)view.next();
> if(row != null)
> id = row.getID();
> else
> id = new Number(0);
> setID(id);
> }
>
>The createViewObject() throws an exception. So I suppose my question is: how
>do I create a view object from within an Entity method?
>
>Thanks for any help!
>
>Owen
>
>
>
>
Received on Wed Nov 13 2002 - 14:35:24 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US