Here’s how I set up the Statistical Schema in Derby to do
automatic key generation:
CREATE TABLE COSMOS_STAT.STATISTICAL_OBSERVATION
(
ID INTEGER GENERATED
BY DEFAULT AS IDENTITY,
CAPTURE_TIME
TIMESTAMP NOT NULL ,
DATA_SET_ID
INTEGER NOT NULL ,
KEY_ENTRY
INTEGER NOT NULL ,
INTEGRAL_VALUE
BIGINT,
DECIMAL_VALUE
DOUBLE,
STRING_VALUE VARCHAR(255)
);
ALTER TABLE COSMOS_STAT.STATISTICAL_OBSERVATION
ADD
CONSTRAINT COSMOS_STAT.STATISTICAL_OBSERVATION_PK Primary Key (
ID);
ALTER TABLE COSMOS_STAT.STATISTICAL_OBSERVATION
ADD
CONSTRAINT COSMOS_STAT.STATISTICAL_OBSERVATION_DATASET_FK Foreign Key (
DATA_SET_ID)
REFERENCES COSMOS_STAT.STATISTICAL_DATASET(
ID);
ALTER TABLE COSMOS_STAT.STATISTICAL_OBSERVATION
ADD
CONSTRAINT COSMOS_STAT.STATISTICAL_OBSERVATION_UNQ_OBSV Unique (
CAPTURE_TIME,
DATA_SET_ID, KEY_ENTRY);
And here’s the iBatis
mapping to return the key after an insert:
<insert id="addObservation"
parameterClass="observation">
INSERT INTO
COSMOS_STAT.STATISTICAL_OBSERVATION(CAPTURE_TIME,
DATA_SET_ID, KEY_ENTRY, INTEGRAL_VALUE, DECIMAL_VALUE, STRING_VALUE)
VALUES(#timeStamp#, #dataSet#, #keyEntry#, #integralValue:BIGINT#,
#continuousValue:DOUBLE#, #stringValue#)
<selectKey keyProperty="id"
resultClass="int">
values IDENTITY_VAL_LOCAL()
</selectKey>
</insert>
Finally, here’s the code that handles an insert:
Interger id = (Integer)sqlMapper.insert("addObservation", impl);
Cheers,
Joel
=00