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* <[EMAIL PROTECTED]
> <mailto:[EMAIL PROTECTED]>> 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.
