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 Navanee <na...@yahoo.com> on 2007/10/16 16:57:34 UTC
How to achieve Dynamic SQL query in delete statement
Hi
Is there a way to pass dynamic Query values to delete statement. like below
<delete id="trimTable" parameterClass="String" >
$value$
</delete>
In my case, I need to delete some ten tables, But I want to have only one
delete sql map statement like above instead of having ten different SQL
statement,
I'll pass the $value$ as below for all tables,
String dynamicQuery = "DELETE FROM TABLE1 WHERE NAME = 'TEST'";
sqlMapper.delete("trimTable",dynamicQuery);
I wrote a sample code to achive the above, but during execution the code
hangs and does not come out of it, when there is a valid data to delete, But
finishes it execution where there is no data in the table.
What might cause the above issue, is there any workaround/solution for
this...
Could someone please share/provide solution on this?
Thanks and Regards,
Navanee
--
View this message in context: http://www.nabble.com/How-to-achieve-Dynamic-SQL-query-in-delete-statement-tf4634689.html#a13235168
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: How to achieve Dynamic SQL query in delete statement
Posted by Lisa Jenkins <li...@investoranalytics.com>.
I think you've listed your own solution. The hanging may be a db issue,
meaning the time it takes for the query to execute/number of rows it
takes to process. How long does it take the query to run outside of
iBatis (when running directly on the db)?
Navanee wrote:
> Hi
>
> Is there a way to pass dynamic Query values to delete statement. like below
>
> <delete id="trimTable" parameterClass="String" >
> $value$
> </delete>
>
> In my case, I need to delete some ten tables, But I want to have only one
> delete sql map statement like above instead of having ten different SQL
> statement,
>
> I'll pass the $value$ as below for all tables,
>
> String dynamicQuery = "DELETE FROM TABLE1 WHERE NAME = 'TEST'";
> sqlMapper.delete("trimTable",dynamicQuery);
>
> I wrote a sample code to achive the above, but during execution the code
> hangs and does not come out of it, when there is a valid data to delete, But
> finishes it execution where there is no data in the table.
>
> What might cause the above issue, is there any workaround/solution for
> this...
>
> Could someone please share/provide solution on this?
>
> Thanks and Regards,
> Navanee
>
Re: How to achieve Dynamic SQL query in delete statement
Posted by Navanee <na...@yahoo.com>.
The problem got resolved after restarting the server. I guess there is some
table lock problem.
~Navanee~
Hi Lisa and Nathan,
Thank you very much for your response.
Here is my code again to generate dynamic SQL
SQL Map
-------
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap
PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
"http://www.ibatis.com/dtd/sql-map-2.dtd">
<sqlMap namespace="DBPurge">
<parameterMap id="dbPurgeHashMap" class="java.util.HashMap">
<parameter property="tablename" jdbcType="VARCHAR" />
<parameter property="whereclause" jdbcType="VARCHAR" />
</parameterMap>
<!-- Query to trim the older Data for Log Table -->
<delete id="trimTable" parameterClass="java.util.Map" >
DELETE FROM $tablename$ $whereclause$
</delete>
</sqlMap>
Java Pseudo Code
-----------------
for (int index=0;index<PurgeTable.TRIM_TABLENAME.length;index++) {
try {
tableName = DBPurgingConstants.TRIM_TABLENAME[index];
String columnName =
DBPurgingConstants.TRIM_COLUMNNAME[index];
String maxDateRangeToTrim = _maxDateRangeMap.get(tableName);
//Here my date range will also be dynamic based on the
table...
String whereClause = " WHERE " +columnName + " <= "
+ "'" +maxDateRangeToTrim+"'";
HashMap testMap1 = new HashMap();
testMap1.put("tablename",tableName);
testMap1.put("whereclause",whereClause);
SqlMapClient sqlMapper = IbatisDAOFactory.configure();
sqlMapper.delete("trimTable",testMap1);
} catch (Exception e) {
e.printStackTrace();
}
}
Lisa,
Still this does not work. For testing purpose, I have only one row in my
Table. But still it hangs. But it works great for empty table... Is there a
known issue, or can't I pass $value$ attribute in <delete> statement.
Nathan,
I need to delete huge amount of data (say million row in each table), I
don't want to do it at a stretch, instead I want to do it for table by
table....
If I do like this, then I need to define a map for maxDateRangeToTrim, it
might not look good; thats Y I wanted to generate SQL in java as in above
pseudo code.
<delete id="trimTable">
delete from X where id=#maxDateRangeToTrim#;
delete from Y where id1=#maxDateRangeToTrim1 #;
</delete>
PLEASE ADVISE.
~Navanee~
Lisa Jenkins wrote:
>
> Actually, you can use 1 map and call it 10 times, with <delete
> id="trimTable" > delete from $tablename$ $whereClause$</delete>as the
> query. And pass parmMap for tablename and whereClause vars.
>
> Nathan Maves wrote:
>> Not sure how you are going to avoid writing 10 different sql
>> statements.... and now you have some sql based in two different
>> places (.java and .xml).
>>
>> I think you are over thinking this and would have already been done if
>> you just placed the 10 delete statements in your sqlmap files :)
>>
>> of course this is just my opinion
>>
>> Nathan
>>
>> On 10/16/07, *Navanee* <navaneethankrish@yahoo.com
>> <ma...@yahoo.com>> wrote:
>>
>>
>
>
--
View this message in context: http://www.nabble.com/How-to-achieve-Dynamic-SQL-query-in-delete-statement-tf4634689.html#a13249570
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: How to achieve Dynamic SQL query in delete statement
Posted by Navanee <na...@yahoo.com>.
Hi Lisa and Nathan,
Thank you very much for your response.
Here is my code again to generate dynamic SQL
SQL Map
-------
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap
PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
"http://www.ibatis.com/dtd/sql-map-2.dtd">
<sqlMap namespace="DBPurge">
<parameterMap id="dbPurgeHashMap" class="java.util.HashMap">
<parameter property="tablename" jdbcType="VARCHAR" />
<parameter property="whereclause" jdbcType="VARCHAR" />
</parameterMap>
<!-- Query to trim the older Data for Log Table -->
<delete id="trimTable" parameterClass="java.util.Map" >
DELETE FROM $tablename$ $whereclause$
</delete>
</sqlMap>
Java Pseudo Code
-----------------
for (int index=0;index<PurgeTable.TRIM_TABLENAME.length;index++) {
try {
tableName = DBPurgingConstants.TRIM_TABLENAME[index];
String columnName =
DBPurgingConstants.TRIM_COLUMNNAME[index];
String maxDateRangeToTrim = _maxDateRangeMap.get(tableName);
//Here my date range will also be dynamic based on the
table...
String whereClause = " WHERE " +columnName + " <= "
+ "'" +maxDateRangeToTrim+"'";
HashMap testMap1 = new HashMap();
testMap1.put("tablename",tableName);
testMap1.put("whereclause",whereClause);
SqlMapClient sqlMapper = IbatisDAOFactory.configure();
sqlMapper.delete("trimTable",testMap1);
} catch (Exception e) {
e.printStackTrace();
}
}
Lisa,
Still this does not work. For testing purpose, I have only one row in my
Table. But still it hangs. But it works great for empty table... Is there a
known issue, or can't I pass $value$ attribute in <delete> statement.
Nathan,
I need to delete huge amount of data (say million row in each table), I
don't want to do it at a stretch, instead I want to do it for table by
table....
If I do like this, then I need to define a map for maxDateRangeToTrim, it
might not look good; thats Y I wanted to generate SQL in java as in above
pseudo code.
<delete id="trimTable">
delete from X where id=#maxDateRangeToTrim#;
delete from Y where id1=#maxDateRangeToTrim1 #;
</delete>
PLEASE ADVISE.
~Navanee~
Actually, you can use 1 map and call it 10 times, with <delete
id="trimTable" > delete from $tablename$ $whereClause$</delete>as the
query. And pass parmMap for tablename and whereClause vars.
Nathan Maves wrote:
> Not sure how you are going to avoid writing 10 different sql
> statements.... and now you have some sql based in two different
> places (.java and .xml).
>
> I think you are over thinking this and would have already been done if
> you just placed the 10 delete statements in your sqlmap files :)
>
> of course this is just my opinion
>
> Nathan
>
> On 10/16/07, *Navanee* <navaneethankrish@yahoo.com
> <ma...@yahoo.com>> wrote:
>
>
--
View this message in context: http://www.nabble.com/How-to-achieve-Dynamic-SQL-query-in-delete-statement-tf4634689.html#a13247138
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: How to achieve Dynamic SQL query in delete statement
Posted by Nathan Maves <na...@gmail.com>.
you sure can, but again you are then storing you SQL in your .java files :)
On 10/16/07, Lisa Jenkins <li...@investoranalytics.com> wrote:
>
> Actually, you can use 1 map and call it 10 times, with <delete
> id="trimTable" > delete from $tablename$ $whereClause$</delete>as the
> query. And pass parmMap for tablename and whereClause vars.
>
> Nathan Maves wrote:
> > Not sure how you are going to avoid writing 10 different sql
> > statements.... and now you have some sql based in two different
> > places (.java and .xml).
> >
> > I think you are over thinking this and would have already been done if
> > you just placed the 10 delete statements in your sqlmap files :)
> >
> > of course this is just my opinion
> >
> > Nathan
> >
> > On 10/16/07, *Navanee* <navaneethankrish@yahoo.com
> > <ma...@yahoo.com>> wrote:
> >
> >
> > Hi
> >
> > Is there a way to pass dynamic Query values to delete statement.
> > like below
> >
> > <delete id="trimTable" parameterClass="String" >
> > $value$
> > </delete>
> >
> > In my case, I need to delete some ten tables, But I want to have
> > only one
> > delete sql map statement like above instead of having ten
> > different SQL
> > statement,
> >
> > I'll pass the $value$ as below for all tables,
> >
> > String dynamicQuery = "DELETE FROM TABLE1 WHERE NAME =
> > 'TEST'";
> > sqlMapper.delete("trimTable",dynamicQuery);
> >
> > I wrote a sample code to achive the above, but during execution
> > the code
> > hangs and does not come out of it, when there is a valid data to
> > delete, But
> > finishes it execution where there is no data in the table.
> >
> > What might cause the above issue, is there any workaround/solution
> for
> > this...
> >
> > Could someone please share/provide solution on this?
> >
> > Thanks and Regards,
> > Navanee
> > --
> > View this message in context:
> >
> http://www.nabble.com/How-to-achieve-Dynamic-SQL-query-in-delete-statement-tf4634689.html#a13235168
> > Sent from the iBATIS - User - Java mailing list archive at
> > Nabble.com <http://Nabble.com>.
> >
> >
>
>
Re: How to achieve Dynamic SQL query in delete statement
Posted by Lisa Jenkins <li...@investoranalytics.com>.
Actually, you can use 1 map and call it 10 times, with <delete
id="trimTable" > delete from $tablename$ $whereClause$</delete>as the
query. And pass parmMap for tablename and whereClause vars.
Nathan Maves wrote:
> Not sure how you are going to avoid writing 10 different sql
> statements.... and now you have some sql based in two different
> places (.java and .xml).
>
> I think you are over thinking this and would have already been done if
> you just placed the 10 delete statements in your sqlmap files :)
>
> of course this is just my opinion
>
> Nathan
>
> On 10/16/07, *Navanee* <navaneethankrish@yahoo.com
> <ma...@yahoo.com>> wrote:
>
>
> Hi
>
> Is there a way to pass dynamic Query values to delete statement.
> like below
>
> <delete id="trimTable" parameterClass="String" >
> $value$
> </delete>
>
> In my case, I need to delete some ten tables, But I want to have
> only one
> delete sql map statement like above instead of having ten
> different SQL
> statement,
>
> I'll pass the $value$ as below for all tables,
>
> String dynamicQuery = "DELETE FROM TABLE1 WHERE NAME =
> 'TEST'";
> sqlMapper.delete("trimTable",dynamicQuery);
>
> I wrote a sample code to achive the above, but during execution
> the code
> hangs and does not come out of it, when there is a valid data to
> delete, But
> finishes it execution where there is no data in the table.
>
> What might cause the above issue, is there any workaround/solution for
> this...
>
> Could someone please share/provide solution on this?
>
> Thanks and Regards,
> Navanee
> --
> View this message in context:
> http://www.nabble.com/How-to-achieve-Dynamic-SQL-query-in-delete-statement-tf4634689.html#a13235168
> Sent from the iBATIS - User - Java mailing list archive at
> Nabble.com <http://Nabble.com>.
>
>
Re: How to achieve Dynamic SQL query in delete statement
Posted by Nathan Maves <na...@gmail.com>.
Not sure how you are going to avoid writing 10 different sql statements....
and now you have some sql based in two different places (.java and .xml).
I think you are over thinking this and would have already been done if you
just placed the 10 delete statements in your sqlmap files :)
of course this is just my opinion
Nathan
On 10/16/07, Navanee <na...@yahoo.com> wrote:
>
>
> Hi
>
> Is there a way to pass dynamic Query values to delete statement. like
> below
>
> <delete id="trimTable" parameterClass="String" >
> $value$
> </delete>
>
> In my case, I need to delete some ten tables, But I want to have only one
> delete sql map statement like above instead of having ten different SQL
> statement,
>
> I'll pass the $value$ as below for all tables,
>
> String dynamicQuery = "DELETE FROM TABLE1 WHERE NAME = 'TEST'";
> sqlMapper.delete("trimTable",dynamicQuery);
>
> I wrote a sample code to achive the above, but during execution the code
> hangs and does not come out of it, when there is a valid data to delete,
> But
> finishes it execution where there is no data in the table.
>
> What might cause the above issue, is there any workaround/solution for
> this...
>
> Could someone please share/provide solution on this?
>
> Thanks and Regards,
> Navanee
> --
> View this message in context:
> http://www.nabble.com/How-to-achieve-Dynamic-SQL-query-in-delete-statement-tf4634689.html#a13235168
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>