Create and read keysnumbers with JDBC

[Former Member]

Hi all,

Is it possible to create a key and read this key in one sql statement.

Richard

[Former Member]

Richard,

I'm not sure if this is what you are looking for, but here is a scenario I had. I have a table with an Identity field that auto increments with each new record.

I create a record with:

INSERT INTO TableName (Field2, Field3) values ('val1', 'val2')

Then the next statement would be:

SELECT IDENT_CURRENT('TableName') AS 'Identity'

This creates a record set with an integer field called Identity. It holds the value of the last incremented value in the table. There are some other sql statements you could use. They each work a little differently. It will depend on your situation. Look into the SQL documentation.

Brian

[Former Member]

Hi Brian, I don't know if for Richard is the same but for me the "as is" situation is that I have an entity bean linked with a DB table and I want to map this entity bean in such a way that whenever I call the create method of the EBean automatically the system manage the new key generation... now I'm working on a procedure finded on the SAP docs, if it works I will let you know... in the meantime every suggestions is well accepted... 😜

Thank you

Stefano

[Former Member]

Stefano !

Do you use CMP ?

You might use TransferObjects/ValueObjects/DTOs (which is all the same) to update your EntityBeans. If you are using them (preferrably handeled through a Session Facade (Stateless/Stateful Bean with all the service/process oriented methods within and given you are using container-managed transactions at this level (i.e. "required") you're on a good way.

To the key:

I've written my own KeyGenerator.java accessing a defined keytable within any database.

The thing is, to switch off auto-commit at the db-connection object to increment and read the key value. Switching off auto-commit causes two statement to be handeled within ONE transaction and - depending on transaction-isolation defined - at least a write lock during transaction (don't use isolation-level TRANSACTION_NONE).

Here is the code (rowname == Object I want a key for, i.e. Customer):

/** getting next number */

private Integer getNextNumber(String rowName)

{

this.checkConnection();

Integer next = null;

String updateString = "UPDATE KEYTABLE SET " + rowName +"=" + rowName + " + 1 ";

String selectString = "SELECT " + rowName + " FROM KEYTABLE";

PreparedStatement selectStatement = null;

PreparedStatement updateStatement = null;

ResultSet rs = null;

try

{

selectStatement = con.prepareStatement(selectString);

updateStatement = con.prepareStatement(updateString);

boolean oldFlag = con.getAutoCommit();

con.setAutoCommit(false);

updateStatement.executeUpdate();

updateStatement.close();

rs = selectStatement.executeQuery();

con.commit();

con.setAutoCommit(oldFlag);

rs.next();

next = new Integer(rs.getInt(rowName));

}

catch (SQLException e)

{

e.printStackTrace();

try

{

con.rollback();

}

catch (SQLException e1)

{

e1.printStackTrace();

}

}

finally

{

try

{

selectStatement.close();

updateStatement.close();

rs.close();

this.dropConnection();

}

catch (SQLException e)

{

e.printStackTrace();

}

}

return next;

}

This value is set (as well as the others) on creation of the VO and the whole VO is passed into the ejbCreate(SomeVO vo) / ejbPostCreate(SomeVO vo). Within this ejbCreate() I call this.setID(vo.getID()); and so on.

Advantage: all within ONE transaction handeled

Hope this helps.

Matthias

[Former Member]

Hi Matthias,

probably your solution is a good solution and a standardized way to solve the question... but I'm an ABAP programmer and I'm a little fixed with ABAP concepts... so:

the REAL advantage of ABAP numeric range objects (and concepts) is that you haven't got to worry about uniqueness of your number... you simply call the Function and everything its ok... now you are telling me that in JAVA personality I've got not only to call the method, but also to build it...

It is the same of telling that in ABAP you do not simple call the function module, you also have to create it... It's a big chapuzilla!

do u agree?

Stefano

[Former Member]

Hi Stefano !

So I'm a Java/J2EE developer with less than zero experience in ABAP.

There are a few things availabe you might want to use, there is an UUDI-pattern which gives you unique numbers but I'm not sure what SAP has build in for supporting automatic key generation.

As the other guys have explained, you might use sequences but your might end up in database vendor lock-in.

Anyway, writing this class for key generation is pretty simple but it might be you have to do it on your own (as there are many things within J2EE you have to do on your own).

Not to become philosophic but I like it this way, therefore it is not sure whether I should / do agree.

Anyway, what made you move towards EJB ??

Regards

Matthias

[Former Member]

...ok, you do not agree... everyone could have its own opinion...

I move towards EJB 'cause I need to build a webDynpro application and, after thousands pages of documentations, I definitively choose to learn this language (and philosophy of course) too.

About the key generation I have read that with CMP EntityBean you can omit the specification of the key and define an unknown key that should be automatically assigned (in an unknown ways!)... this the pass:

<i>If your container-managed entity beans do not have a natural primary key, you can omit specifying the primary key class or the primary key fields and rely on the EJB Container to generate your primary keys. In this case, the system assumes that the primary key is unknown.

Follow the steps below to set the EJB Container to generate the bean’s primary key automatically.

...

<b>1. On the Fields tab, select Primary Key.</b>

<b>2. In the right pane, choose the Unknown option.</b>

The NetWeaver Developer Studio then:

<b>a.</b> Creates the default ejb_pk field in persistent.xml.

<b>b.</b> Sets java.lang.Object as the type of the primary key class. That is, the value of the <prim-key-class> element in ejb-jar.xml is set to java.lang.Object.

<b>c.</b> Automatically sets the type of the argument of the bean’s findByPrimaryKey method to java.lang.Object.

The EJB Container will generate a system primary key field of type java.lang.Long.

<b>3. In the persistent.xml file, specify the mapping of this field to the column in the database where the values of the primary key will be stored.</b>

The JDBC type of this column must be java.sql.Types.BIGINT and this column must be the only primary key column in the database table. For more information, see Object/Relational Mapping Rules.

<b>4. Specify the primary key deployment properties, that is specify the unknown primary key interval.</b></i>

I have understood step 1 to 3 but the 4 is a mistery for me... what's the mean of the primary key interval... and if I specify this is my job finished?

anyone could help me again?

[Former Member]

You're sure you want to do this ?

@see spec:

In this special case, the type of the argument of the findByPrimaryKey method must be declared as

java.lang.Object. The Bean Provider must specify the primary key class in the deployment

descriptor as of the type java.lang.Object.

You're sure of casting appropriate in every situation ?

@see spec:

Use of entity beans with a deferred primary key type specification limits the client application programming

model, because the clients written prior to deployment of the entity bean may not use, in general,

the methods that rely on the knowledge of the primary key type.

To be honest, I wouldn't use it but it's up to you.

Regards

Matthias

[Former Member]

Hi all,

thanks for all the input.

I'm using a SAPDB and Microsoft SQL server. and I have created the tables with Netweaver Developer Studio.

Kind Regards,

Richard

[Former Member]

Hi all, especially Alessandro, do u remember me?

Richard explain perfectly the problem... is it possible that SAP hasn't provided a std way to build a numeric range? Of course sequence could be a solution... but in NWDS there no way to specify that a simple type has the "sequence" type.

There should be a solution... i cannot believe that SAP has not provided it!

Stefano

[Former Member]

Hi Alessandro,

In my java application I use a table(be_counter) for unique key values for my key fields. before I create a record in one of my tables update the unique value of the key field in be_counter and than I select the new value from be_counter to create the record.

The problem I have is that somebody can update the unique value before I have executed my select statement.

Is there a possiblity to update the value and put the new value in a parameter in one SQL Statement.

Kind Regards,

Richard

[Former Member]

With standard SQL column types, no - there is no equivalent of an atomic fetch/set action.

However, if you have access to sequences, then you can use these. Failing that, why not create a wapper class that creates a sub-transaction (in Oracle, these are called savepoints), and then locks the table while performing the fetch/update, followed by a commit? That would have the same effect as a sequence.

[former_member184154]

Ken had a good idea.

However if your db is Oracle (let us know), and your be_count is a sequence (if it is not, and you can alter the table, just create a sequence for that field) you can achieve your task by SQL'ing something like:

INSERT MYTABLE SET be_count = be_count.nextval

which will give you the next available number. That should be very similar to SAP Number Range Objects.

To create an oracle sequence (just an example you can change to feet your needs):

CREATE SEQUENCE <table>.<field>       
INCREMENT BY 1
START WITH 1
MAXVALUE 999999999999
MINVALUE 1
CYCLE
NOCACHE;

Finally, if your db is not Oracle, I'm 99% sure you can find an equivalent.

Alex

[former_member184154]

Can you try to further explain your aim?