ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeff Butler <jeffgbut...@gmail.com>
Subject Re: multiple insert within a select tag
Date Mon, 29 Aug 2005 14:40:09 GMT
Just to correct a possible misunderstanding - iBATIS does not detect the 
dependancy on your delete. MySQL is doing it through a cascaded delete. iBATIS 
is really just sending SQL to the database through JDBC - there's very 
little magic going on here.
 iBATIS knows very little about your table structures or your intent with 
these kinds of operations. The solution in your case is to break the double 
insert into two different statements and call them independantly, and in the 
right order, from your DAO layer - it will still be the same transaction and 
connection so there will be little or no performance impact.
 This is a typical issue using auto generated keys - each database handles 
it differently so iBATIS is limited in what it can do for you automatically. 
As you've seen, updates and deletes can be handled by the database because 
the keys are already set. Inserts with auto generated keys are a special 
case in all databases.
 Jeff Butler

 On 8/29/05, Farsi, Reza <Reza.Farsi@sungard.de> wrote: 
> Hi Albert,
>  thanks a lot for the answer. Indeed it was the reason of the problem. I 
> added the following selectKey to my mapping file and it works:
>   <insert id="saveInstrument" parameterClass="instrument">
> INSERT INTO instrument (internalNumber) VALUES (#internalNumber#) 
> *<selectKey keyProperty="id" resultClass="int">**SELECT last_insert_id()**</selectKey

> >
> *</insert>
>  I'm now facing another problem: By deleting an instrument iBATIS detects 
> the dependency of the option data and removes it from the corresponding 
> table. I don't need to do anything, just calling "deleteInstrument" which 
> has been defined as follows:
>   <delete id="deleteInstrument" parameterClass="instrument">
> DELETE FROM instrument WHERE id=#id#
> </delete>
>  By inserting a new instrument, I first have to call inserting of the new 
> instrument and then call manually the insert of eventually corresponding 
> option. See Java and mapping part below:
>  public void save(Instrument instrument) {
> // is it save or update? 
> if (instrument.getId() == 0) {
> * getSqlMapClientTemplate().insert("saveInstrument", instrument);
> if (instrument.getOptionComponent() != null) {
> getSqlMapClientTemplate().insert("saveInstrumentOption", instrument);
> * }
> } else {
> // ... update
> }
> }
>  <insert id="saveInstrumentOption" parameterClass="instrument">
> INSERT INTO instrument_option (instrument_id, name) VALUES (#id#, 
> #optionComponent.name#);
> </insert>
>  My question is: why can't I call both inserts toghther. I mean, how can I 
> call statements like:
>   <insert id="saveInstrument" parameterClass="instrument">
> INSERT INTO instrument (internalNumber) VALUES (#internalNumber#) 
> <selectKey keyProperty="id" resultClass="int">SELECT last_insert_id()</selectKey

> >
>  INSERT INTO instrument_option (instrument_id, name) VALUES (#id#, 
> #optionComponent.name#);
> * *</insert>
>  Thanks,
> Reza

View raw message