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 09:00:50 GMT


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 
>> <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#a13249570
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.


Mime
View raw message