ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jaybytez <jayby...@gmail.com>
Subject Re: commitTransaction in a batch
Date Wed, 21 Jun 2006 21:24:52 GMT

Here are the details...could be something in the way I am configuring this
(obviously).

I am running WebLogic 8.1.4, Oracle 9.1, JDK 1.4.2.  I am pulling my
Connections from JNDI and using the container to handle my transactions
through a SessionFacade layer that has the transaction demarcation.  The
transaction timeout level for this is 600 seconds.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig
    PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
    "http://www.ibatis.com/dtd/sql-map-config-2.dtd">

<sqlMapConfig>
    <settings cacheModelsEnabled="true" enhancementEnabled="true"
        lazyLoadingEnabled="true" errorTracingEnabled="true"
        maxRequests="32" maxSessions="10"
        maxTransactions="5" useStatementNamespaces="false" />

    <transactionManager type="EXTERNAL" commitRequired="false">
        <dataSource type="JNDI">
            <property name="DataSource" value="jdbc/providerDb" />
        </dataSource>
    </transactionManager>

    <!-- Identify all SQL Map XML files to be loaded by this
        SQL map.  Notice the paths are relative to the classpath. -->
    <sqlMap resource="ibatis/ProviderFooDAO.xml" />

</sqlMapConfig>

My ProviderDAO.xml looks like this.

<?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="ProviderFacilityDAO">
  
  <insert id="batchInsertFacilityService"
    parameterClass="com.foo.to.FacilityServiceTO">
    <![CDATA[    
    INSERT INTO facility_service (provider_medicare_number,
facility_service_code)  
    VALUES (#medicareId#, #serviceCode#)
    ]]>
  </insert>
  
  <insert id="batchInsertFacilityServiceLookup"
    parameterClass="string">
    <![CDATA[
 		INSERT INTO facility_service_lookup ( facility_service_code,
facility_service_desc ) 
 		SELECT facility_service_lookup_seq.nextval, #value# 
    	FROM dual
    ]]>
  </insert>
  
  <resultMap id="serviceLookupResult" class="com.foo.to.ServiceTO">
    <result property="serviceDesc" column="facility_service_desc"/>
    <result property="serviceCode" column="facility_service_code"/>
  </resultMap>
  
  <select id="queryListFacilityServiceLookup"
resultMap="serviceLookupResult">
    <![CDATA[
      SELECT facility_service_desc, facility_service_code
      FROM facility_service_lookup
    ]]>
  </select>
    
  <delete id="deleteFacilityService">
    <![CDATA[
    DELETE FROM facility_service
    ]]>
  </delete>

  <delete id="deleteFacilityServiceLookup">
    <![CDATA[
    DELETE FROM facility_service_lookup
    ]]>
  </delete>

  <update id="updateFacilityService">
    <![CDATA[
    ANALYZE TABLE facility_service COMPUTE STATISTICS FOR TABLE FOR ALL
INDEXES FOR ALL INDEXED COLUMNS
    ]]>
  </update>

  <update id="updateFacilityServiceLookup">
    <![CDATA[
	ANALYZE TABLE facility_service_lookup COMPUTE STATISTICS FOR TABLE FOR ALL
INDEXES FOR ALL INDEXED COLUMNS
    ]]>
  </update>
  
</sqlMap>

They run in this order.
1) I run the two delete statements.
2) I run the batchInsertFooServiceLookup
3) I query with the queryListFooServiceLookup to get my lookup table
4) I run batchInsertFooService
5) I run the two update/analyze statements

The step #4 inserts the 100,000 records and this is where I get the
OutOfMemory.  I use a DAO Proxy where I name my DAO interface methods by the
same name of the ids in the sqlMap file.  Therefore developers don't have to
code any DAO implementation, it just fully uses iBatis behind the scenes. 
Here is my Proxy class:

public class DAOProxy implements InvocationHandler {
    private static final Logger logger = Logger.getLogger(DAOProxy.class);
    private static int INSERT = 0;
    private static int DELETE = 1;
    private static int UPDATE = 2;
    private static int SELECT = 3;
    private SQLExceptionConverter exceptionConverter;
    private String sqlMapFileName;

    /**
     * Creates a new DAOProxy object.
     *
     * @param fileName param
     */
    public DAOProxy(String fileName) {
        super();
        sqlMapFileName = fileName;
        exceptionConverter = new GenericExceptionConverterImpl();
    }


    /**
     * Creates a new DAOProxy object.
     *
     * @param fileName param
     */
    public DAOProxy(String fileName, SQLExceptionConverter
exceptionConverter) {
        super();
        sqlMapFileName = fileName;
        this.exceptionConverter = exceptionConverter;
    }

    /**
     * @param obj param
     * @param method param
     * @param args param
     *
     * @return returned
     *
     * @throws Throwable can be thrown
     * @throws DataAccessException can be thrown
     */
    public Object invoke(Object obj, Method method, Object args[])
      throws Throwable {
        logger.debug("invoke");

        SqlMapClient sqlMap =
IbatisSqlMapConfig.getSqlMapInstance(this.sqlMapFileName);
        Object returnValue = null;

        String methodName = method.getName();
        String ucaseMethodName = methodName.toUpperCase();
        
        Object singleArgument = null;

        //DEBUG
        if(logger.isDebugEnabled()) {
            logger.debug("Proxy Called");
            logger.debug("Object:" + obj.getClass().getName());
            logger.debug("Method:" + methodName);

            //DEBUG
            if(args != null) {
                for(int i = 0; i < args.length; i++) {
                    logger.debug("Arg:" + i + ":" + args [i]);
                }
            }
        }

        if(ucaseMethodName.startsWith("GET") ||
ucaseMethodName.startsWith("SELECT") || ucaseMethodName.startsWith("EXEC"))
{
            singleArgument = validateSingleArgument(args);            
            returnValue = queryForObject(sqlMap, methodName,
singleArgument);
        }           
        else if(ucaseMethodName.startsWith("INSERT")) {
            singleArgument = validateSingleArgument(args);            
            returnValue = insert(sqlMap, methodName, singleArgument);
        }
        else if(ucaseMethodName.startsWith("UPDATE")) {
            singleArgument = validateSingleArgument(args);            
            returnValue = update(sqlMap, methodName, singleArgument);
        }
        else if(ucaseMethodName.startsWith("DELETE")) {
            singleArgument = validateSingleArgument(args);            
            returnValue = delete(sqlMap, methodName, singleArgument);
        }   
        else if(ucaseMethodName.startsWith("BATCHINSERT")) {
            singleArgument = validateSingleArgument(args);            
            returnValue = batch(sqlMap, INSERT, methodName, singleArgument);
        }
        else if(ucaseMethodName.startsWith("BATCHUPDATE")) {
            singleArgument = validateSingleArgument(args);            
            returnValue = batch(sqlMap, UPDATE, methodName, singleArgument);
        }
        else if(ucaseMethodName.startsWith("BATCHDELETE")) {
            singleArgument = validateSingleArgument(args);            
            returnValue = batch(sqlMap, DELETE, methodName, singleArgument);
        }
        else if(ucaseMethodName.startsWith("QUERYLIST")) {
            singleArgument = validateSingleArgument(args);            
            returnValue = queryForList(sqlMap, methodName, singleArgument);
        }
        else if(ucaseMethodName.startsWith("QUERYMAP")) {
            validateMappedArguments(args);            
            returnValue = queryForMap(sqlMap, methodName, args[0], args[1],
args[2]);
        }        
        else {
            throw new DataAccessException("dao method does not appear to be
a get, insert, update or delete method.  don't know how to handle it");
        }

        if(logger.isDebugEnabled()) {
            logger.debug("returnValue = " + returnValue);
        }

        return returnValue;
    }


    /**
     * @param sqlMap
     * @param methodName
     * @param object
     * @param object2
     * @param object3
     * @return
     */
    private Object queryForMap(SqlMapClient sqlMap, String methodName,
Object argument, Object key, Object value) {
        Map object = null;

        try {
            object = sqlMap.queryForMap(methodName, argument, (String)key,
(String)value);
        }
        catch(SQLException e) {
            exceptionConverter.rethrow(methodName, e);
        }

        return object;
    }


    /**
     * @param args
     * @return
     */
    private Object validateSingleArgument(Object[] args) {
        Object argument = null;
        if(args == null) {
            argument = null;            
        }
        else {
            argument = args[0];            
        }
        if((args != null) && (args.length > 1)) {
            throw new DataAccessException("You must specify only 1(one)
argument/parameter to be passed in.  This argument may be a Primitive, Map
or Object (TO).");
        }
        return argument;
    }
    
    /**
     * @param args
     * @return
     */
    private void validateMappedArguments(Object[] args) {
        if((args == null) || (args.length > 3)) {
            throw new DataAccessException("You must specify exactly 3(three)
arguments/parameters to be passed in.  The first argument may be a
Primitive, Map or Object (TO), followed by a String for the key, and
followed by a String for the value.");
        }
    }


    /**
     * @param sqlMap The SqlMapClient that is being used based on the
database being hit.
     * @param batchType param
     * @param methodName The name of the DAO method being called.
     * @param argument The Object being passed into the underlying
Statement.
     * @return returned
     */
    private Object batch(SqlMapClient sqlMap, int batchType, String
methodName, Object argument) {
        int commitSize = 100;
        boolean commitBatch = false;
        int totalRecordsUpdated = 0;
        int commitBatchUpdated = 0;

        try {
            List list = (List)argument;
            sqlMap.startTransaction();
            sqlMap.startBatch();

            for(int i = 0; i < list.size(); i++) {
                if(batchType == INSERT) {
                    sqlMap.insert(methodName, list.get(i));
                }
                else if(batchType == DELETE) {
                    sqlMap.delete(methodName, list.get(i));
                }
                else if(batchType == UPDATE) {
                    sqlMap.update(methodName, list.get(i));
                }

                if(i == (commitSize - 1)) {
                    commitBatch = true;
                }
                else if(i == (list.size() - 1)) {
                    commitBatch = true;
                }

                if(commitBatch) {
                    commitBatchUpdated = sqlMap.executeBatch();
                    sqlMap.commitTransaction();                    
                    totalRecordsUpdated = totalRecordsUpdated +
commitBatchUpdated;
                    commitBatch = false;
                }
            }
            
            //Currently iBatis does not return what the driver returns for
batch
            //counts.  iBatis converts the -2 Oracle values to 0.  And it
only
            //returns a full count.  Not an array of values.  The future
version
            //of iBatis will fix this with an executeBatchDetailed.  Once
this
            //occurs...use the validateBatchResults method and you can
validate
            //the totalRecordsUpdated against 0 and the size of the list.
            validateBatchResult(methodName, new int[]{totalRecordsUpdated});
//            validateBatchResults(methodName, totalRecordsUpdated);            
//            if(totalRecordsUpdated == 0) {
//                throw new DataAccessException("No records were modified by
the batch statement. " + methodName);
//            }
//            if(totalRecordsUpdated != list.size()) {
//                throw new DataAccessException("Not all of the records were
successfully updated/inserted/deleted in batch mode. " + methodName);
//            }
        }
        catch(SQLException e) {
            exceptionConverter.rethrow(methodName, e);
        }
        finally {
            try {
                sqlMap.endTransaction();
            }
            catch(SQLException e) {
                exceptionConverter.rethrow(methodName, e);
            }
        }

        return new int[] {totalRecordsUpdated};
    }

    /**
     * @param sqlMap The SqlMapClient that is being used based on the
database being hit.
     * @param methodName The name of the DAO method being called.
     * @param argument The Object being passed into the underlying
Statement.
     * @return returned
     */
    private Integer delete(SqlMapClient sqlMap, String methodName, Object
argument) {
        Integer integer = null;

        try {
            int record = sqlMap.delete(methodName, argument);
            validateResult(methodName, record);
            integer = new Integer(record);
        }
        catch(SQLException e) {
            exceptionConverter.rethrow(methodName, e);
        }

        return integer;
    }


    /**
     * @param sqlMap The SqlMapClient that is being used based on the
database being hit.
     * @param methodName The name of the DAO method being called.
     * @param argument The Object being passed into the underlying
Statement.
     * @return returned
     */
    private Object insert(SqlMapClient sqlMap, String methodName, Object
argument) {
        Object object = null;

        try {
            object = sqlMap.insert(methodName, argument);
        }
        catch(SQLException e) {
            exceptionConverter.rethrow(methodName, e);
        }

        return object;
    }


    /**
     * @param sqlMap The SqlMapClient that is being used based on the
database being hit.
     * @param methodName The name of the DAO method being called.
     * @param argument The Object being passed into the underlying
Statement.
     * @return returned
     */
    private Object queryForList(SqlMapClient sqlMap, String methodName,
Object argument) {
        Object object = null;

        try {
            object = sqlMap.queryForList(methodName, argument);
        }
        catch(SQLException e) {
            exceptionConverter.rethrow(methodName, e);
        }

        return object;
    }


    /**
     * @param sqlMap The SqlMapClient that is being used based on the
database being hit.
     * @param methodName The name of the DAO method being called.
     * @param argument The Object being passed into the underlying
Statement.
     * @return returned
     */
    private Object queryForObject(SqlMapClient sqlMap, String methodName,
Object argument) {
        List returnValue = null;

        try {
            returnValue = sqlMap.queryForList(methodName, argument, 0, 2);
        }
        catch(SQLException e) {
            exceptionConverter.rethrow(methodName, e);
        }

        if(returnValue == null) {
            throw new NoUniqueRecordException("No unique record was returned
for the query method: " + methodName);
        }
        else if(returnValue.size() > 1) {
            throw new UniqueRecordViolationException("More than one unique
record returned for the query method: " + methodName);
        }

        return returnValue;
    }


    /**
     * @param sqlMap The SqlMapClient that is being used based on the
database being hit.
     * @param methodName The name of the DAO method being called.
     * @param argument The Object being passed into the underlying
Statement.
     * @return returned
     */
    private Integer update(SqlMapClient sqlMap, String methodName, Object
argument) {
        Integer integer = null;

        try {
            int record = sqlMap.update(methodName, argument);
            validateResult(methodName, record);
            integer = new Integer(record);            
        }
        catch(SQLException e) {
            exceptionConverter.rethrow(methodName, e);
        }

        return integer;
    }


    /**
     * The purpose of this method is to validate a batch result (int [])
that 
     * contains a int describing whether or not the record failed to
update/delete/insert.
     * If one of the records failed, throw a DataAccessException so that a
rollback
     * occurs.
     * 
     * @param methodName The name of the DAO method being called.
     * @param records The result records from an execute batch.
     * 
     * @throws DataAccessException Record failed to updated.
     */
    private void validateBatchResult(String methodName, int records[]) {
        for(int counter = 0; counter < records.length; counter++) {
            int result = records [counter];

            if(result < 0) {
                throw new DataAccessException("Update/delete/insert did not
update the requested row/rows successfully. methodName -> " + methodName + "
record number -> " + counter + " return code -> " + result);
            }
        }
    }


    /**
     * The purpose of this method is to validate an execute result (int)
that 
     * contains a int describing whether or not the record failed to
update/delete/insert.
     * If the record failed, throw a DataAccessException so that a rollback
     * occurs.
     * 
     * @param methodName The name of the DAO method being called.
     * @param record The result record from an execute.
     * 
     * @throws DataAccessException Record failed to updated.
     */
    private void validateResult(String methodName, int record) {
        if(record <= 0) {
            throw new DataAccessException("Update/delete/insert did not
update the requested row/rows successfully. methodName -> " + methodName + "
record -> " + record);
        }
    }
}

Thanks for your help and I hope I provided enough information...

jay blanton


--
View this message in context: http://www.nabble.com/executeBatch-not-returning-int-of-records-updated.-t1819686.html#a4982934
Sent from the iBATIS - User - Java forum at Nabble.com.


Mime
View raw message