ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Nathan Maves" <nathan.ma...@gmail.com>
Subject Re: Complex dynamic-SQL problems
Date Wed, 05 Sep 2007 13:34:05 GMT
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="MoleculeDescrip
> tor">
>         <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