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/21 19:13:56 UTC
commitTransaction in a batch
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: 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/>
> .
>
>