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 Complex dynamic-SQL problems
Date Wed, 05 Sep 2007 10:19:07 GMT
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