RE: Complex dynamic-SQL problems

[Yuvraj Shinde]

   I have simple search query.but  in result DTO I get two values null .

 

________________________________

From: Jonathan Alvarsson [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, September 05, 2007 8:14 PM
To: user-java@ibatis.apache.org
Subject: Re: Complex dynamic-SQL problems

 

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]
<mailto:[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