[cosmos-dev] Automatic key generation with Ibatis and Derby

["Hawkins, Joel" <Joel.Hawkins@xxxxxxxxxxxxx>]

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
The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.
The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.