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 <[EMAIL PROTECTED]> 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 < [EMAIL PROTECTED] > 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 > > > >
