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="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
