Return-Path: Delivered-To: apmail-ibatis-user-java-archive@www.apache.org Received: (qmail 70346 invoked from network); 29 Aug 2005 14:40:15 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 29 Aug 2005 14:40:15 -0000 Received: (qmail 50251 invoked by uid 500); 29 Aug 2005 14:40:13 -0000 Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org Received: (qmail 50234 invoked by uid 500); 29 Aug 2005 14:40:13 -0000 Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user-java@ibatis.apache.org Delivered-To: mailing list user-java@ibatis.apache.org Received: (qmail 50221 invoked by uid 99); 29 Aug 2005 14:40:13 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 29 Aug 2005 07:40:13 -0700 X-ASF-Spam-Status: No, hits=0.1 required=10.0 tests=HTML_50_60,HTML_MESSAGE,RCVD_BY_IP,SPF_HELO_PASS,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: domain of jeffgbutler@gmail.com designates 64.233.162.193 as permitted sender) Received: from [64.233.162.193] (HELO zproxy.gmail.com) (64.233.162.193) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 29 Aug 2005 07:40:29 -0700 Received: by zproxy.gmail.com with SMTP id s1so639100nze for ; Mon, 29 Aug 2005 07:40:11 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=rsVO2lbpp/VPA3vwIsEM3BkHBhnAgcqBl5y1lq+wdAdsPtxOXx2qxbg17sj6gw5QmmU9e55fzLMOy11Of2lpu4dHTmBHll6FvZMhvJS8BCfIduE39wILNc25J4xdcunnos/3Ee4qDOclGzjTRgMmRU0Auhvvdr4eKV1E/KhPNbw= Received: by 10.36.108.8 with SMTP id g8mr648662nzc; Mon, 29 Aug 2005 07:40:11 -0700 (PDT) Received: by 10.36.252.49 with HTTP; Mon, 29 Aug 2005 07:40:09 -0700 (PDT) Message-ID: Date: Mon, 29 Aug 2005 09:40:09 -0500 From: Jeff Butler To: user-java@ibatis.apache.org Subject: Re: multiple insert within a select tag In-Reply-To: <6DB5565517FFA44498EA068EA7D23B33077DC0@ffm-mx2.banking.net> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_6106_13691763.1125326409294" References: <6DB5565517FFA44498EA068EA7D23B33077DC0@ffm-mx2.banking.net> X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N ------=_Part_6106_13691763.1125326409294 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Just to correct a possible misunderstanding - iBATIS does not detect the=20 dependancy on your delete. MySQL is doing it through a cascaded delete. iBA= TIS=20 is really just sending SQL to the database through JDBC - there's very=20 little magic going on here. iBATIS knows very little about your table structures or your intent with= =20 these kinds of operations. The solution in your case is to break the double= =20 insert into two different statements and call them independantly, and in th= e=20 right order, from your DAO layer - it will still be the same transaction an= d=20 connection so there will be little or no performance impact. This is a typical issue using auto generated keys - each database handles= =20 it differently so iBATIS is limited in what it can do for you automatically= .=20 As you've seen, updates and deletes can be handled by the database because= =20 the keys are already set. Inserts with auto generated keys are a special=20 case in all databases. Jeff Butler On 8/29/05, Farsi, Reza wrote:=20 >=20 > Hi Albert, > thanks a lot for the answer. Indeed it was the reason of the problem. I= =20 > added the following selectKey to my mapping file and it works: > > INSERT INTO instrument (internalNumber) VALUES (#internalNumber#)=20 > ***SELECT last_insert_i= d()** > > * > I'm now facing another problem: By deleting an instrument iBATIS detects= =20 > the dependency of the option data and removes it from the corresponding= =20 > table. I don't need to do anything, just calling "deleteInstrument" which= =20 > has been defined as follows: > > DELETE FROM instrument WHERE id=3D#id# > > By inserting a new instrument, I first have to call inserting of the new= =20 > instrument and then call manually the insert of eventually corresponding= =20 > option. See Java and mapping part below: > public void save(Instrument instrument) { > // is it save or update?=20 > if (instrument.getId() =3D=3D 0) { > * getSqlMapClientTemplate().insert("saveInstrument", instrument); > if (instrument.getOptionComponent() !=3D null) { > getSqlMapClientTemplate().insert("saveInstrumentOption", instrument); > * } > } else { > // ... update > } > } > > INSERT INTO instrument_option (instrument_id, name) VALUES (#id#,=20 > #optionComponent.name#); > > My question is: why can't I call both inserts toghther. I mean, how can = I=20 > call statements like: > > INSERT INTO instrument (internalNumber) VALUES (#internalNumber#)=20 > SELECT last_insert_id()= > > INSERT INTO instrument_option (instrument_id, name) VALUES (#id#,=20 > #optionComponent.name#); > * * > Thanks, > Reza > ------=_Part_6106_13691763.1125326409294 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Just to correct a possible misunderstanding - iBATIS does not detect t= he dependancy on your delete.  MySQL is doing it through a cascaded de= lete. =20
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 wi= th these kinds of operations.  The solution in your case is to break t= he double insert into two different statements and call them independantly,= and in the right order, from your DAO layer - it will still be the sa= me transaction and connection so there will be little or no performance imp= act.
 
This is a typical issue using auto generated keys - each database hand= les it differently so iBATIS is limited in what it can do for you automatic= ally.  As you've seen, updates and deletes can be handled by the datab= ase because the keys are already set.  Inserts with auto generated key= s are a special case in all databases.
 
Jeff Butler


 
On 8/29/05, = Farsi, Reza <Reza.Farsi@sun= gard.de> wrote:
Hi Albert,
&= nbsp;
thanks a lot for the answer. Indeed it was = the reason of the problem. I added the following selectKey to my mapping fi= le and it works:
 
    = <insert id=3D"saveInstrument" parameterClass=3D"in= strument">
        = ;    INSERT INTO instrument (internalNumber) VALUES (= #internalNumber#)  
=         &nb= sp;   <selectKey keyProperty=3D"id" resultClas= s=3D"int"> SELECT last_insert_id()</sel= ectKey >
    </insert&= gt;
 
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=3D"deleteInstrumen= t" parameterClass=3D"instrument" >
        DELETE FROM= instrument WHERE id=3D#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(In= strument instrument) {
      // is it save or u= pdate? 
      if (instrument.getId() =3D= =3D 0) {
           getSqlMapClientTempl= ate().insert("saveInstrument", instrument);
   =        if (instrument.getOptionComponent= () !=3D null) {
         &n= bsp;     getSqlMapClientTemplate().insert("saveIns= trumentOption", instrument);
          }
&n= bsp;     } else {
     &nbs= p;     // ... update
=      = }
 }
 
<insert id=3D"saveInstr= umentOption" parameterClass=3D"instrument">
&nbs= p;   INSERT INTO instrument_option (instrument_id, name) VALUES (#id#, #optionComponent. name#);
</insert>
 
My question is: why can't I call both inser= ts toghther. I mean, how can I call statements like:
 
    = <insert id=3D"saveInstrument" parameterClass=3D"in= strument">
        = ;    INSERT INTO instrument (internalNumber) VALUES (= #internalNumber#)  
=             <= /span><selectKey keyProperty=3D"id" resul= tClass=3D"int">SELECT last_insert_id() </selectKey >
   &= nbsp;        INSERT INTO instrume= nt_option (instrument_id, name) VALUES (#id#, #optionComp= onent.name#);
    </insert>
 
Thanks,
Reza
 
------=_Part_6106_13691763.1125326409294--