You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Jonathan Alvarsson <jo...@gmail.com> on 2007/09/05 12:19:07 UTC
Complex dynamic-SQL problems
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
RE: how to execute a complete dynamic querry in ibatis(urgent)
Posted by Yuvraj Shinde <Yu...@sos.sungard.com>.
Thanks its working now
________________________________
From: Meindert [mailto:meindert@pastelebusiness.com]
Sent: Wednesday, September 12, 2007 2:57 PM
To: user-java@ibatis.apache.org
Subject: RE: how to execute a complete dynamic querry in ibatis(urgent)
Add remapResults="true" to your select tag.
________________________________
From: Yuvraj Shinde [mailto:Yuvraj.Shinde@sos.sungard.com]
Sent: Wednesday, September 12, 2007 11:14 AM
To: user-java@ibatis.apache.org
Cc: Yuvraj Shinde
Subject: how to execute a complete dynamic querry in ibatis(urgent)
Hi all,
I am executing a dynamic query using <dynamic> tag.
When I call this select query for first time it works well.
But for second time when I pass new query it doesn't work.
As ibatis use prepared statement to construct a query. It
always refer to first query columns and give exception for second query.
Is there is any way to disable prepared statement in
ibatis.can some body give the solution.
Following is mine query.
<select id="SEARCH_QUERRY_DATA" parameterClass="String"
resultClass="java.util.HashMap" >
<dynamic>
$querryString$
</dynamic>
</select>
First time value of querryString is " select * from bank" -
works fine.
Second time value of querryString is " select * from cust" -
doesn't works as query refer to columns of first table ie bank because
of prepared
statemant.
Regards
Yuvraj
Yuvraj Shinde * Senior Software Engineer - Zainet* SunGard *
Offshore Services * Pride Portal,CTS No 103A/5A/1A/1B Bhamburda,Senapati
Bapat Road, shivajinagar,Pune 411016 Tel Direct +91 20 66248045 * Main
+91 20 66248000 * Fax +91 20 25606222 yuvraj.shinde@sos.sungard.com
<bl...@sos.sungard.com> * www.sungard.com
<blocked::http://www.sungard.com>
RE: how to execute a complete dynamic querry in ibatis(urgent)
Posted by Meindert <me...@pastelebusiness.com>.
Add remapResults="true" to your select tag.
_____
From: Yuvraj Shinde [mailto:Yuvraj.Shinde@sos.sungard.com]
Sent: Wednesday, September 12, 2007 11:14 AM
To: user-java@ibatis.apache.org
Cc: Yuvraj Shinde
Subject: how to execute a complete dynamic querry in ibatis(urgent)
Hi all,
I am executing a dynamic query using <dynamic> tag.
When I call this select query for first time it works well. But for second
time when I pass new query it doesn't work.
As ibatis use prepared statement to construct a query. It always refer to
first query columns and give exception for second query.
Is there is any way to disable prepared statement in ibatis.can some body
give the solution.
Following is mine query.
<select id="SEARCH_QUERRY_DATA" parameterClass="String"
resultClass="java.util.HashMap" >
<dynamic>
$querryString$
</dynamic>
</select>
First time value of querryString is " select * from bank" - works fine.
Second time value of querryString is " select * from cust" - doesn't
works as query refer to columns of first table ie bank because of prepared
statemant.
Regards
Yuvraj
Yuvraj Shinde . Senior Software Engineer - Zainet. SunGard . Offshore
Services . Pride Portal,CTS No 103A/5A/1A/1B Bhamburda,Senapati Bapat Road,
shivajinagar,Pune 411016 Tel Direct +91 20 66248045 . Main +91 20 66248000 .
Fax +91 20 25606222 <bl...@sos.sungard.com>
yuvraj.shinde@sos.sungard.com . <blocked::http://www.sungard.com>
www.sungard.com
how to execute a complete dynamic querry in ibatis(urgent)
Posted by Yuvraj Shinde <Yu...@sos.sungard.com>.
Hi all,
I am executing a dynamic query using <dynamic> tag.
When I call this select query for first time it works well.
But for second time when I pass new query it doesn't work.
As ibatis use prepared statement to construct a query. It
always refer to first query columns and give exception for second query.
Is there is any way to disable prepared statement in
ibatis.can some body give the solution.
Following is mine query.
<select id="SEARCH_QUERRY_DATA" parameterClass="String"
resultClass="java.util.HashMap" >
<dynamic>
$querryString$
</dynamic>
</select>
First time value of querryString is " select * from bank" -
works fine.
Second time value of querryString is " select * from cust" -
doesn't works as query refer to columns of first table ie bank because
of prepared
statemant.
Regards*
Yuvraj
Yuvraj Shinde * Senior Software Engineer - Zainet* SunGard *
Offshore Services * Pride Portal,CTS No 103A/5A/1A/1B Bhamburda,Senapati
Bapat Road, shivajinagar,Pune 411016 Tel Direct +91 20 66248045 * Main
+91 20 66248000 * Fax +91 20 25606222 yuvraj.shinde@sos.sungard.com
<bl...@sos.sungard.com> * www.sungard.com
<blocked::http://www.sungard.com>
RE: Complex dynamic-SQL problems
Posted by Yuvraj Shinde <Yu...@sos.sungard.com>.
I have simple search query.but in result DTO I get two values null .
________________________________
From: Jonathan Alvarsson [mailto:jonathan.alvarsson@gmail.com]
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 <na...@gmail.com> 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 < jonathan.alvarsson@gmail.com
<ma...@gmail.com> > 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
Re: Complex dynamic-SQL problems
Posted by Jonathan Alvarsson <jo...@gmail.com>.
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 <na...@gmail.com> 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 < jonathan.alvarsson@gmail.com > 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
>
>
>
>
Re: Complex dynamic-SQL problems
Posted by Nathan Maves <na...@gmail.com>.
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 <jonathan.alvarsson@gmail.com > 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