cascade insert: with sql or java?

[Andreas Bauer]

Hi,

I'm a bit stuck. I have three tables over which I want to spread let's
user informations. I have a table where I insert username, pwd. Then I
have a table with extended user info where I want to insert forename,
surname etc. But for this insert I'd need the id from the first insert.
Do I do this with sql or should I do an select right after the insert to
get the id?

sql = "insert into......"
ps.executeUpdate(sql);
sql ="select id from table where username = dd"
rs = ps.executeQuery()
while(rs.next())
{
int id = rs.getInt....
}

But this seems a bit circumstantial to me.

Any suggestions?

Regards,

Andi

 

[chris brat]

Have you considered using an external sequence ?

1.) Query the sequence for the id or use your own generated id and keep
in memory
2.) Populate the id as part of the "insert into..."
3.) Poplate the same id in the 2nd "insert into"

Chris

 

[Andreas Bauer]

Have you considered using an external sequence ?

Yes. I thought about stored procedures or I wanted to lookup the feature
of CMP EntityBeans. But these techniques require some time of
orientation. So I thought of something "quicker"

1.) Query the sequence for the id

that's what I thought of

or use your own generated id and keep
Own generated? Before I do the insert, i. d. I create my own id and use
it for al 3 inserts?

 

[chris brat]

Exactly. ;-)

Stored procedures are usually the best option because they are 'closer'
to the database and can usually be optimised. If you were going to use
CMP beans you would still need to generate or lookup the key before you
inserted your rows - if I remember correctly it isn't done for you.

Your application could generate the ids, either a combination of
machine name, location, timestamp etc, combinations of name, surname,
idno, date of birth - thought these are not always possible or
appropriate.

For the application I currently work on I set up a "sequence" table,
which is just a table with a varchar(10) and an int column, because the
database we use doesnt support sequences itself.

I then wrote a Sequencer class with one method getNext(String
sequenceName) that returns an int value representing the next value
(The method does a query and an update on the row without the rest of
my application knowing the logic - I'll replace it if we change
database or I find a better solution).

I then use this id for numerous inserts into 2 different tables. I't
may not be pretty but it does work.

 

[steve]

Hi,

I'm a bit stuck. I have three tables over which I want to spread let's
user informations. I have a table where I insert username, pwd. Then I
have a table with extended user info where I want to insert forename,
surname etc. But for this insert I'd need the id from the first insert.
Do I do this with sql or should I do an select right after the insert to
get the id?

sql = "insert into......"
ps.executeUpdate(sql);
sql ="select id from table where username = dd"
rs = ps.executeQuery()
while(rs.next())
{
int id = rs.getInt....
}

But this seems a bit circumstantial to me.

Any suggestions?

Regards,

Andi

which database is it?

if it is oracle you can do a insert /update , with a returning clause

so it would be " insert xxxxx into yyyy returning id"

steve

 

[Guest]

With MySQL, I use:

SELECT LAST_INSERT_ID()

to get the unique key of the column which is defined as AUTO_INCREMENT

Works great.

Keith
www.cognisense.com

 

[Lee Fesperman]

Hi,

I'm a bit stuck. I have three tables over which I want to spread let's
user informations. I have a table where I insert username, pwd. Then I
have a table with extended user info where I want to insert forename,
surname etc. But for this insert I'd need the id from the first insert.
Do I do this with sql or should I do an select right after the insert to
get the id?

sql = "insert into......"
ps.executeUpdate(sql);
sql ="select id from table where username = dd"
rs = ps.executeQuery()
while(rs.next())
{
int id = rs.getInt....
}

But this seems a bit circumstantial to me.

Any suggestions?

JDBC 3 provides methods for retrieving keys generated by INSERT.

 

[Andreas Bauer]

which database is it?

It's DB2.

 

[chris brat]

Hi Lee,

Is this the getGeneratedKeys() method in the Statement class?

C

 

[Lee Fesperman]

Is this the getGeneratedKeys() method in the Statement class?

That's part of the puzzle, but you also need to request that generated keys be returned
by using extended forms of executeXXX() in java.sql.Statement or prepareStatement() in
java.sql.Connection.

 

[bugbear]

JDBC 3 provides methods for retrieving keys generated by INSERT.

But tragically (and annoyingly) it's not yet widely
supported/implemented.

BugBear

 

[ossareh]

A question of form here: Do you need to seperate this information out?
As it all pertains to a user record surely then it should be in the
'user' table?

We currently have at least 5 tables which pertain to a user object,
which to be fair is just confusing. The tables were created to match
the user object which is split into a similar number of parts. This was
clearly a good idea when we designed our system but we're not realising
that this idea is not so great. Our next major release will involve
migrating all user "profile" data into one table.

 

[ossareh]

oh,

and if this is needed then feel free to copy the way that we do it

select id from user_id;
update user_id set id = id+1;

The select and update is in a synchronised method. Not a great solution
but it works for us.

note: that there is only one row in this table with a BIGINT.