ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jonathan Alvarsson" <jonathan.alvars...@gmail.com>
Subject Re: Complex dynamic-SQL problems
Date Wed, 05 Sep 2007 14:43:44 GMT
Yea that's what I ended up doing. I am using a GenericDAO solution using
Spring AOP magic. And each DAO needing special treatment (that is not only
calling an iBatis mapping) means I have to write an extra class and an extra
Spring bean so it would have been great if it were possible. But since it
probably isn't worth it I will stay away from trouble... :)

-- 
// Jonathan

On 9/5/07, Nathan Maves <nathan.maves@gmail.com> wrote:
>
> To address the insert issue...
>
> Only specific drivers can handle this type of multiple statements.  I
> would say a best practice would be to use your DAO layer to iterate over
> your set and do the batch inserts there.  The batch might even be faster.
>
>
> Nathan
>
> On 9/5/07, Jonathan Alvarsson < jonathan.alvarsson@gmail.com > wrote:
> >
> > Hi I want to perform some rather complex stuff with dynamic SQL and
> > wonders
> > Can this be done? and if it can be done, how?
> >
> > I have a class Moleculedescriptor extending AbstractDescriptor
> > containing a List of doubles. I want to construct one mapping that can be
> > used to persist such an object with the complete list of doubles. So far I
> > have written this:
> >
> > CREATE TABLE AbstractDescriptor (
> >     id            VARCHAR(36) NOT NULL UNIQUE,
> >     pcmBaseObject VARCHAR(36) NOT NULL,
> >
> >     PRIMARY KEY (id),
> >     FOREIGN KEY (pcmBaseObject)   REFERENCES PCMBaseObject(id)
> > ) ENGINE=InnoDB;
> >
> > CREATE TABLE DescriptorValue (
> >     id       BIGINT AUTO_INCREMENT NOT NULL,
> >     value    DOUBLE                NOT NULL,
> >     arrayPos INT                   NOT NULL,
> >     abstractDescriptor VARCHAR(36) NOT NULL,
> >
> >     PRIMARY KEY (id),
> >     FOREIGN KEY (abstractDescriptor) REFERENCES AbstractDescriptor(id)
> > ) ENGINE=InnoDB;
> >
> > CREATE TABLE MoleculeDescriptor (
> >     id                 VARCHAR(36) NOT NULL UNIQUE,
> >     abstractDescriptor VARCHAR(36) NOT NULL,
> >
> >     PRIMARY KEY (id),
> >     FOREIGN KEY (abstractDescriptor) REFERENCES PCMBaseObject(id)
> > )ENGINE=InnoDB;
> >
> > <resultMap class="MoleculeDescriptor" id="moleculeDescriptor">
> >         <result property="id"     column="id"   />
> >         <result property="name"   column="name" />
> >         <result property="values" column="id" select="
> > MoleculeDescriptor.getValueList" />
> > </resultMap>
> >
> > And now for my strange insert mapping:
> >
> > <procedure id="MoleculeDescriptor.insert "
> > parameterClass="MoleculeDescriptor">
> >         <dynamic prepend="CALL insertMoleculeDescriptor(#id#, #name#);">
> >
> >             <iterate property="values"
> >                      prepend="INSERT INTO AbstractDescriptor
> > VALUES(abstractDescriptor, arrayPos, value)"
> >                      close=";" >
> >                 (#id#, iteration, #value#)
> >             </iterate>
> >         </dynamic>
> > </procedure>
> >
> > Oh the reason for a stored procedure for insert is that it does this:
> > CREATE PROCEDURE insertMoleculeDescriptor ( IN pid VARCHAR(36), IN pname
> > VARCHAR(50) )
> >     BEGIN
> >         INSERT INTO PCMBaseObject (id, name) values (pid, pname);
> >         INSERT INTO AbstractDescriptor (id, pcmBaseObject) values (pid,
> > pid);
> >         INSERT INTO MoleculeDescriptor (id, name, pcmBaseObject) values
> > (pid, pname, pid);
> >     END;
> >
> > I am mapping my inheritance hierarki one class to one table.
> >
> > However how do I get the arrayPos field correct when inserting the
> > values in the list and can I even do such a thing as I try in:
> > MoleculeDescriptor.insert?
> > When I get things out I guess I only need to order by arraypos in the
> > select statement but the insert is trixy...
> >
> > --
> > // Jonathan
>
>
>
>

Mime
View raw message