aspose file tools*
The moose likes JDBC and the fly likes how to get primary key after doing an insert Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to get primary key after doing an insert" Watch "how to get primary key after doing an insert" New topic
Author

how to get primary key after doing an insert

Dave Robbins
Ranch Hand

Joined: Sep 16, 2003
Posts: 131
Hello All

I have a table where I'm storing the main data I'm interested in
one of the fields is a zipcode
obviously this may be repeated so proper normalization suggests I should pull it out into another table
so now I have my main table which has a field that points to a table of zipcodes, and this other table with all the zipcodes
my question is, when I do an insert, I first insert the zipcode in it's table (if it's not already there)
a key get's auto-generated for it, which I need to insert into the main table
what's the proper way to do this??
what I want is to ask the database "if this zip code exists, give me it's key, if not, insert it and give me its key"
how do I do this??

using mysql

TIA

Dave
Virag Saksena
Ranch Hand

Joined: Nov 27, 2005
Posts: 71
#1. If you are not doing any validation on the zip code or storing any dependent fields like city/state, you don't really need to move it to another table. Let it be an attribute of the table.

#2. There is a big debate about surrogate keys - Why won't you let the ZIP code be the primary key of the table zipcodes. It is a natural prinmary key ? Why generate an artificial key ?

#3. If you still want an auto-generated primary, I am assuming your table will have the columns SOME_PK, ZIP_CODE

so here is some sample code, take care to close your preparedstatements, resultsets in a finally block, and other exception handling code


<a href="http://www.auptyma.com" target="_blank" rel="nofollow">The Peak of Performance</a>
Dave Robbins
Ranch Hand

Joined: Sep 16, 2003
Posts: 131
Hi

I'm pretty much a rookie at DB design but I was under the impression it was considered good form to always have a row identifier that did nothing but identify the row, contained no further information
I recall an instance in the past of working with an existing database where failure to follow this principal caused a lot of problems, so I'm trying to avoid that (I don't really think it would be a problem in this case)

anyway
I see where you're going with this query

select SOME_PK from zip where ZIP_CODE = ?

but the "?" syntax doesn't seem to work for me
is it specific to a particular RDBMS?
I'm using MySQL

Thanks
Dave
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61230
    
  66

Originally posted by Dave Robbins:
I was under the impression it was considered good form to always have a row identifier that did nothing but identify the row, contained no further information


That is a good principle. What I queston is the need to move the zip code to another table.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Virag Saksena
Ranch Hand

Joined: Nov 27, 2005
Posts: 71
Dave,
With good design, you should have a clearly identified natural primary key. However finding a good primary key (which will never change) is a non-trivial operation, so often people will use a generated key.

The advantage of using a generated ID is that you can change any of the columns without having to cascade the update to the children.

The disadvantage is performance : If you have ZIP code as a column in the original table (rather than foreign key) then queries like
zip code between 12345 and 12378
can be efficiently processed using an index.

The ? is the bind variable/query parameter. It does work in Oracle and postgreSQL. What exception are you getting ?
Dave Robbins
Ranch Hand

Joined: Sep 16, 2003
Posts: 131
Bear,

the reason I think the zip needs to be placed in it's own table is because this is a web app and it's going to be collecting several different kinds of data from users, each of which will be "identified" by it's zipcode
heck, here's more info than you want to know
I keep bees
the app is going to collect info about when flowers bloom, when pollen starts to flow, when drones are first raised, etc
each of these events is tied to the location where it happens by zip code
I'm also going out to "wunderground.com" and collecting LOT'S of weather data about each zip code
the idea is to collate all these events to the weather in the year they occur and then try to predict when things are going to happen this year (beekeeping is all about "timing")
so, since several pieces of data are tied to the zipcode I think it deserves it's own table

Virag

the error I get is 1064 which appears to have something to do with ? being a reserved keyword
I think I'm just using it improperly
I'll fiddle around with it and see if I can get it to work

Dave
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: how to get primary key after doing an insert