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
