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 jaybytez <ja...@gmail.com> on 2006/06/20 21:28:05 UTC
executeBatch not returning int of records updated.
I have a method that loops through a list and tries to commit every 100
records for batch updates/inserts/deletes. When I run the
sqlMap.executeBath, the return value of the int is 0. Is there a reason
this is occurring? Am I doing the batch incorrectly? It is at the line
where the code is commitBatchUpdated = sqlMap.executeBatch();
I do a test against the total records update so that I know if no records
were updated or if some of the records failed so that the whole batch did
not complete.
/**
* @param argument param
* @param sqlMap param
* @param methodName param
* @param batchType param
*
* @return returned
*/
private Object batch(Object argument, SqlMapClient sqlMap, String
methodName) {
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++) {
sqlMap.insert(methodName, list.get(i));
if(i == (commitSize - 1)) {
commitBatch = true;
}
else if(i == (list.size() - 1)) {
commitBatch = true;
}
if(commitBatch) {
commitBatchUpdated = sqlMap.executeBatch();
totalRecordsUpdated = totalRecordsUpdated +
commitBatchUpdated;
commitBatch = false;
}
}
sqlMap.commitTransaction();
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 Integer(totalRecordsUpdated);
}
Thanks,
Jay Blanton
--
View this message in context: http://www.nabble.com/executeBatch-not-returning-int-of-records-updated.-t1819686.html#a4961604
Sent from the iBATIS - User - Java forum at Nabble.com.
Re: commitTransaction in a batch
Posted by jaybytez <ja...@gmail.com>.
I have not tested it, but after looking at this post, I think I may have
found the issue:
In my method below, I startBatch and then executeBatch after the first 100
records, then I never do a startBatch again. I would assume that I have to
call startBatch again or else the first 100 records are batched but every
record after that is not batched but sent to the database individually.
private Object batch(Object argument, SqlMapClient sqlMap, String
methodName) {
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++) {
sqlMap.insert(methodName, list.get(i));
if(i == (commitSize - 1)) {
commitBatch = true;
}
else if(i == (list.size() - 1)) {
commitBatch = true;
}
if(commitBatch) {
commitBatchUpdated = sqlMap.executeBatch();
totalRecordsUpdated = totalRecordsUpdated +
commitBatchUpdated;
commitBatch = false;
}
}
sqlMap.commitTransaction();
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 Integer(totalRecordsUpdated);
}
I should be doing this in my one bit code:
if(commitBatch) {
commitBatchUpdated = sqlMap.executeBatch();
totalRecordsUpdated = totalRecordsUpdated +
commitBatchUpdated;
if(i == (list.size() - 1)) {
sqlMap.startBatch();
}
commitBatch = false;
}
--
View this message in context: http://www.nabble.com/executeBatch-not-returning-int-of-records-updated.-t1819686.html#a4985090
Sent from the iBATIS - User - Java forum at Nabble.com.
Re: commitTransaction in a batch
Posted by jaybytez <ja...@gmail.com>.
Thank you very much for your response...it really helps me out.
jay blanton
--
View this message in context: http://www.nabble.com/executeBatch-not-returning-int-of-records-updated.-t1819686.html#a4983669
Sent from the iBATIS - User - Java forum at Nabble.com.
Re: commitTransaction in a batch
Posted by Jeff Butler <je...@gmail.com>.
Aha! I think the problem is related to the EXTERNAL transaction manager.
When you use this transaction manager iBATIS doesn't do any commits or
rollbacks - it assumes the container is doing it. So the
commitTransaction() method does nothing in this case. Even though you've
sprinkled these calls to commitTransaction() throughout your batch, there is
really only one commit - the commit that WebLogic does at the end of the
facade method. So WebLogic's transaction service is filling up because the
transaction gets to be too huge.
One way to fix this is to have a facade method that inserts 100 records, and
then call that method repeatedly from the client. Then WebLogic can commit
after every facade method (this is a pretty good option).
Or you can tune the WebLogic transaction manager to give it more memory.
Or you can stop using container managed transactions and manage all
transactions yourself with iBATIS methods - but then you've removed the only
good argument for using EJBs and you might as well switch to Tomcat :).
Or you can find some other way to insert 100,000 records by not going
through the session facade (I would do this one or the first option). I
don't know if you're remoting or not, but if you are the Serialization for
such a huge transaction would be really consuming.
Jeff Butler
On 6/21/06, jaybytez <ja...@gmail.com> wrote:
>
>
> 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.
>
>
Re: commitTransaction in a batch
Posted by jaybytez <ja...@gmail.com>.
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.
Re: commitTransaction in a batch
Posted by Jeff Butler <je...@gmail.com>.
Time to switch to WebSphere! (just kidding)
What's the database? What's your transaction configuration in iBATIS? Are
you using an EXTERNAL transaction manager by any chance? If so, then the
intermediate commits are ignored.
I just did a simple test of inserting 1,000,000 records with DB2 and
HSQLDB. DB2 could handle it either way, HSQLDB could not - I got out of
memory errors with HSQLDB.
I don't think the out of memory errors are coming from iBATIS. Probably
something else is choking.
Jeff Butler
On 6/21/06, jaybytez <ja...@gmail.com> wrote:
>
>
> Thank you very much for your help.
>
> I did make the changes you mentioned in option 1. I also made sure that I
>
> used no dot notation in my insert syntax. I am only updating two columns
> in
> a table, but like I said, over 100,000 records. My insert is as simple as
> this:
>
> <insert id="batchInsertFacilityService"
> parameterClass="foo.to.FacilityServiceTO">
> <![CDATA[
> INSERT INTO facility_service (provider_medicare_number,
> facility_service_code)
> VALUES (#medicareId#, #serviceCode#)
> ]]>
> </insert>
>
> I use the method below to commit every 100 records and WebLogic is still
> running out of memory.
>
> jay
> --
> View this message in context:
> http://www.nabble.com/executeBatch-not-returning-int-of-records-updated.-t1819686.html#a4980209
> Sent from the iBATIS - User - Java forum at Nabble.com<http://nabble.com/>
> .
>
>
Re: commitTransaction in a batch
Posted by jaybytez <ja...@gmail.com>.
Thank you very much for your help.
I did make the changes you mentioned in option 1. I also made sure that I
used no dot notation in my insert syntax. I am only updating two columns in
a table, but like I said, over 100,000 records. My insert is as simple as
this:
<insert id="batchInsertFacilityService"
parameterClass="foo.to.FacilityServiceTO">
<![CDATA[
INSERT INTO facility_service (provider_medicare_number,
facility_service_code)
VALUES (#medicareId#, #serviceCode#)
]]>
</insert>
I use the method below to commit every 100 records and WebLogic is still
running out of memory.
jay
--
View this message in context: http://www.nabble.com/executeBatch-not-returning-int-of-records-updated.-t1819686.html#a4980209
Sent from the iBATIS - User - Java forum at Nabble.com.
Re: commitTransaction in a batch
Posted by Jeff Butler <je...@gmail.com>.
When you commit and there is an active batch, then iBATIS will automatically
execute the batch before the commit. So you can do this to cause
intermediate commits in a large batch:
try {
startTransaction();
startBatch();
...100 inserts
executeBatch(); // this line is optional
commitTransaction();
startBatch();
...100 inserts
executeBatch(); // this line is optional
commitTransaction();
etc.
} finally {
endTransaction();
}
You can also do it this way (which will cause one large commit):
try {
startTransaction();
startBatch();
...100 inserts
executeBatch();
startBatch();
...100 inserts
executeBatch();
etc.
commitTransaction();
} finally {
endTransaction();
}
But then you'll have one large commit - which could be the source of your
problem. I would go with option 1.
Jeff Butler
On 6/21/06, jaybytez <ja...@gmail.com> wrote:
>
>
> In this example method that I have, do I want to do a commitTransaction
> everytime after I do a executeBatch...or at the end of my batch process.
>
> So if I have 1000 records and I execute a batch of them every 100 records.
> Do I go to 100 then executeBatch and commitTransaction, then go to the
> next
> 100 and executeBatch and commitTransaction? Or do I executeBatch for
> every
> 100 records and then commitTransaction?
>
> The reason I ask is that I am running a batch process that inserts 100,000
> or so records and my WebLogic instance keeps crashing with OutOfMemory
> (even
> better that a rollback cannot occur so half of my transaction commits). I
>
> don't have this issue running regular SQL, so I am trying to discover what
> I
> am doing wrong to cause this performance hit.
>
> Thanks,
>
> -jay blanton
> --
> View this message in context:
> http://www.nabble.com/executeBatch-not-returning-int-of-records-updated.-t1819686.html#a4978497
> Sent from the iBATIS - User - Java forum at Nabble.com<http://nabble.com/>
> .
>
>
commitTransaction in a batch
Posted by jaybytez <ja...@gmail.com>.
In this example method that I have, do I want to do a commitTransaction
everytime after I do a executeBatch...or at the end of my batch process.
So if I have 1000 records and I execute a batch of them every 100 records.
Do I go to 100 then executeBatch and commitTransaction, then go to the next
100 and executeBatch and commitTransaction? Or do I executeBatch for every
100 records and then commitTransaction?
The reason I ask is that I am running a batch process that inserts 100,000
or so records and my WebLogic instance keeps crashing with OutOfMemory (even
better that a rollback cannot occur so half of my transaction commits). I
don't have this issue running regular SQL, so I am trying to discover what I
am doing wrong to cause this performance hit.
Thanks,
-jay blanton
--
View this message in context: http://www.nabble.com/executeBatch-not-returning-int-of-records-updated.-t1819686.html#a4978497
Sent from the iBATIS - User - Java forum at Nabble.com.
Re: executeBatch not returning int of records updated.
Posted by jaybytez <ja...@gmail.com>.
If I wait for the next release to acquire the executeBatchDetailed...do you
have any dates on when the next release including this code will be?
Thanks,
-jay blanton
--
View this message in context: http://www.nabble.com/executeBatch-not-returning-int-of-records-updated.-t1819686.html#a4962596
Sent from the iBATIS - User - Java forum at Nabble.com.
Re: executeBatch not returning int of records updated.
Posted by Jeff Butler <je...@gmail.com>.
Have a look at this:
http://issues.apache.org/jira/browse/IBATIS-159
The comments at the end describe a different batch method we're implementing
for the next release of iBATIS (executeBatchDetailed). The new method will
give you exactly what the driver returns, rather than having iBATIS try to
interpret what comes back from the driver. The code is committed now in
SVN, but it's not in the documentation yet. If you're adventurous, you
could build iBATIS from source and try it out.
Jeff Butler
On 6/20/06, jaybytez <ja...@gmail.com> wrote:
>
>
> Is there a version of Oracle that this depends on. Currently in our J2EE
> framework we can check the int[] and int size and value everytime we do a
> delete/insert/update. We are actually able to do successful tests against
> the value in an int against PreparedStatement.SUCCESS_NO_INFO for
> instance.
>
> For example, this currently works if I use straight Jdbc.
>
> int results[] = ps.executeBatch();
> for (int i=0; i<results.length; i++) {
> int result = results[i];
> if ((result != PreparedStatement.SUCCESS_NO_INFO) &&
> (result != 1)) {
> BaseTO object = null;
> if (list != null) { object = (BaseTO)list.get(i); }
> throw new RuntimeException(label + " result=" + result
> + ",
> expecting 1: " +
> (object == null ? "" : object.toString()));
> }
> }
>
> Thanks for your info...
>
> -jay
> --
> View this message in context:
> http://www.nabble.com/executeBatch-not-returning-int-of-records-updated.-t1819686.html#a4961768
> Sent from the iBATIS - User - Java forum at Nabble.com.
>
>
Re: executeBatch not returning int of records updated.
Posted by jaybytez <ja...@gmail.com>.
Awesome!!!....You guys rock.
Thank you for this response...it is very helpful to better understand. I
want to make sure I separate iBatis fact from my fiction. That way as I
work through migrating my code to iBatis...I can verify that things are
working as before according to the Oracle database drivers.
Thank you very much for this information.
Jay Blanton
--
View this message in context: http://www.nabble.com/executeBatch-not-returning-int-of-records-updated.-t1819686.html#a4961968
Sent from the iBATIS - User - Java forum at Nabble.com.
Re: executeBatch not returning int of records updated.
Posted by Sven Boden <li...@pandora.be>.
From the Oracle 9i documentation: "For a prepared statement batch, it
is not possible to know the number of rows affected in the database by
each individual statement in the batch. Therefore, all array elements
have a value of -2. According to the JDBC 2.0 specification, a value of
-2 indicates that the operation was successful but the number of rows
affected is unknown."
ibATIS translates the -2 to 0. So it seems it doesn't work on batched
statemements.
Regards,
Sven
jaybytez wrote:
>Is there a version of Oracle that this depends on. Currently in our J2EE
>framework we can check the int[] and int size and value everytime we do a
>delete/insert/update. We are actually able to do successful tests against
>the value in an int against PreparedStatement.SUCCESS_NO_INFO for instance.
>
>For example, this currently works if I use straight Jdbc.
>
> int results[] = ps.executeBatch();
> for (int i=0; i<results.length; i++) {
> int result = results[i];
> if ((result != PreparedStatement.SUCCESS_NO_INFO) && (result != 1)) {
> BaseTO object = null;
> if (list != null) { object = (BaseTO)list.get(i); }
> throw new RuntimeException(label + " result=" + result + ",
>expecting 1: " +
> (object == null ? "" : object.toString()));
> }
> }
>
>Thanks for your info...
>
>-jay
>--
>View this message in context: http://www.nabble.com/executeBatch-not-returning-int-of-records-updated.-t1819686.html#a4961768
>Sent from the iBATIS - User - Java forum at Nabble.com.
>
>
>
>
>
Re: executeBatch not returning int of records updated.
Posted by jaybytez <ja...@gmail.com>.
Is there a version of Oracle that this depends on. Currently in our J2EE
framework we can check the int[] and int size and value everytime we do a
delete/insert/update. We are actually able to do successful tests against
the value in an int against PreparedStatement.SUCCESS_NO_INFO for instance.
For example, this currently works if I use straight Jdbc.
int results[] = ps.executeBatch();
for (int i=0; i<results.length; i++) {
int result = results[i];
if ((result != PreparedStatement.SUCCESS_NO_INFO) && (result != 1)) {
BaseTO object = null;
if (list != null) { object = (BaseTO)list.get(i); }
throw new RuntimeException(label + " result=" + result + ",
expecting 1: " +
(object == null ? "" : object.toString()));
}
}
Thanks for your info...
-jay
--
View this message in context: http://www.nabble.com/executeBatch-not-returning-int-of-records-updated.-t1819686.html#a4961768
Sent from the iBATIS - User - Java forum at Nabble.com.
Re: executeBatch not returning int of records updated.
Posted by Sven Boden <li...@pandora.be>.
If you're using Oracle it's a known problem, and not in iBATIS. The JDBC
spec lets vendors skip certain parts, and Oracle decided to skip the
part in their driver where they return the number of rows updated by a
prepared statement.
Regards,
Sven
jaybytez wrote:
>I have a method that loops through a list and tries to commit every 100
>records for batch updates/inserts/deletes. When I run the
>sqlMap.executeBath, the return value of the int is 0. Is there a reason
>this is occurring? Am I doing the batch incorrectly? It is at the line
>where the code is commitBatchUpdated = sqlMap.executeBatch();
>
>I do a test against the total records update so that I know if no records
>were updated or if some of the records failed so that the whole batch did
>not complete.
>
> /**
> * @param argument param
> * @param sqlMap param
> * @param methodName param
> * @param batchType param
> *
> * @return returned
> */
> private Object batch(Object argument, SqlMapClient sqlMap, String
>methodName) {
> 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++) {
>
> sqlMap.insert(methodName, list.get(i));
>
> if(i == (commitSize - 1)) {
> commitBatch = true;
> }
> else if(i == (list.size() - 1)) {
> commitBatch = true;
> }
>
> if(commitBatch) {
> commitBatchUpdated = sqlMap.executeBatch();
> totalRecordsUpdated = totalRecordsUpdated +
>commitBatchUpdated;
> commitBatch = false;
> }
> }
>
> sqlMap.commitTransaction();
>
> 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 Integer(totalRecordsUpdated);
> }
>
>Thanks,
>
>Jay Blanton
>--
>View this message in context: http://www.nabble.com/executeBatch-not-returning-int-of-records-updated.-t1819686.html#a4961604
>Sent from the iBATIS - User - Java forum at Nabble.com.
>
>
>
>
>
iBatis batch executing same performance as Jdbc - Thanks!
Posted by jaybytez <ja...@gmail.com>.
I found out that all the problems I was having with OutOfMemoryErrors was
related directly to the Java Proxy in my own code and not iBatis...my
apologies.
Outside of that...thanks for the help. I was able to do a successful batch
implementation. When I excluded the
startTransaction/commitTransaction/endTransaction...my process took 5
minutes to run. When I included it, I got my full process to run under 1
minute. This is directly equal to the speed I found when using straight
Jdbc and/or Spring Jdbc Templates.
This is Awesome!!! And thank you so much for the responses. I love the
ease of configuration for connections and the fact that I can flip a sqlMap
config name (in my properties file) and load a Jndi Based
Connection/Transaction Manager (for running inside the container in
production) and then flip the name to load a Jdbc Connection Manager (for
running tests outside the container in development). That is huge!!! And
without any Java code modifications. I have been dreaming of this day!
Here is my batch method used with iBatis for doing the same thing in Jdbc as
addBatch/executeBatch for every 100 records in a list of records. That way
I use the speed of batching, without chancing an overflow of the
PreparedStatement/Connection with too much batched data.
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 != 0) && (i % commitSize == 0)) {
commitBatch = true;
}
else if(i == (list.size() - 1)) {
commitBatch = true;
}
if(commitBatch) {
commitBatchUpdated = sqlMap.executeBatch();
totalRecordsUpdated = totalRecordsUpdated +
commitBatchUpdated;
if(i != (list.size() - 1)) {
sqlMap.startBatch()
}
commitBatch = false;
}
}
sqlMap.commitTransaction();
validateBatchResult(methodName, new int[]{totalRecordsUpdated});
}
catch(SQLException e) {
exceptionConverter.rethrow(methodName, e);
}
finally {
try {
sqlMap.endTransaction();
}
catch(SQLException e) {
exceptionConverter.rethrow(methodName, e);
}
}
return new int[] {totalRecordsUpdated};
}
Thanks,
jay blanton
--
View this message in context: http://www.nabble.com/executeBatch-not-returning-int-of-records-updated.-t1819686.html#a5006256
Sent from the iBATIS - User - Java forum at Nabble.com.