ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Navanee <navaneethankr...@yahoo.com>
Subject Re: How to achieve Dynamic SQL query in delete statement
Date Wed, 17 Oct 2007 05:00:01 GMT

Hi Lisa and Nathan,

Thank you very much for your response.

Here is my code again to generate dynamic SQL


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap
    PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"

<sqlMap namespace="DBPurge">

    <parameterMap id="dbPurgeHashMap" class="java.util.HashMap">
        <parameter property="tablename" jdbcType="VARCHAR" />
        <parameter property="whereclause" jdbcType="VARCHAR" />

    <!-- Query to trim the older Data for Log Table -->
    <delete id="trimTable"  parameterClass="java.util.Map" >       
        DELETE FROM $tablename$  $whereclause$     


Java Pseudo Code

  for (int index=0;index<PurgeTable.TRIM_TABLENAME.length;index++) {

            try {

                tableName = DBPurgingConstants.TRIM_TABLENAME[index];
                String columnName   =
                String maxDateRangeToTrim = _maxDateRangeMap.get(tableName);
                //Here my date range will also be dynamic based on the
                String whereClause = " WHERE " +columnName + " <= "
                                      + "'" +maxDateRangeToTrim+"'";

                HashMap testMap1 = new HashMap();
                SqlMapClient sqlMapper = IbatisDAOFactory.configure();
            } catch (Exception e) {

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.

 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

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 #; 



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 
> <mailto:navaneethankrish@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.

View raw message