ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Putnam <dput...@wellsfargo.com>
Subject Multiple queries.
Date Tue, 15 Feb 2005 03:34:11 GMT
All,

I am trying to perform an operation using SqlMaps.  I am performing one
query to get a list of modified rows in one table, then for each row I want
to perform an update or insert on another table within a transaction.  I am
used to transactions only existing when I say they should, so I am using
explicit transactions.

What seems to be occurring is that once the inner transaction starts, the
outer query stops behaving like a cursor and fetches the entire result set
into memory. In one version, SqlMap.startTransaction() complained that
another transaction was already in progress.

So here is a snippet of my current main:
        Date laststart = new GregorianCalendar(2005,0,1,0,0,0).getTime();
        CustomerLinkTask newLinkTask = new CustomerLinkTask(sqlMap, process,
maxDeadlockRetry);
        try {
            sqlMap.queryWithRowHandler("modifiedCustomers", laststart,
newLinkTask);
        } catch (SQLException e) {
            logger.error(e);
        }
        return 0;
The modifiedCustomers query returns all customers modified since the
timestamp specified.  The sqlMap, and a deadlock retry are passed on to the
CustomerLinkTask.  Here is the handleRow function for the CustomerLinkTask:

    public void handleRow(Object row) {
        Customer customer = (Customer) row;

        try {
            sqlMap.startTransaction();
        }catch (SQLException e) {
            logger.error(e);
        }

        Investor investor = null;
        investor = getLinkedInvestor(customer);
        if (investor == null) {
            // if the investor is not linked, see if we can find an existing
one
            investor = findExistingInvestor(customer);
            if (investor == null) {
                // if we can find an existing investor we will need to
create one
                investor = createInvestor(customer);
                createInvestorLink(investor, customer);
            } else {
                // create a link to the investor we found
                createInvestorLink(investor, customer);
                updateInvestor(investor, customer);
            }
        } else {
            // update the linked investor
            updateInvestor(investor, customer);
        }

        process.setLastItem(process.getLastItem() + 1);
        try {
            sqlMap.commitTransaction();
        } catch (SQLException e) {
            logger.error(e);
        } finally {
            try {
                sqlMap.endTransaction();
            } catch (SQLException e) {
                logger.error(e);
            }
        }
    }

getLinkedInvestor and findExistingInvestor are queries, createInvestor,
createInvestorLink and updateInvestor insert or update the database and
should be within a transaction.

When I have done similar things in perl with DBI, I opened multiple
connections to the database. However, when I tried using connections
(SqlMapClient.openConnection()), I did not get two independent connections
to the database.

Am I approaching this wrong?  I cannot have the transaction started outside
the rowHandler, as I expect to process 1.4 million rows. Much to large for a
single transaction.

Thanks

David Putnam
Wells Fargo Funds Management, LLC
One Hundred Heritage Reserve
Menomonee Falls, WI 53051-4400
MAC:   N9882-030
Voice: 414-359-3678




*****DISCLOSURE STATEMENT*****
This transmission may contain information which is confidential, proprietary
and privileged.  If you are not the individual or entity to which it is
addressed, note that any review, disclosure, copying, retransmission or
other use is strictly prohibited.  If you received this transmission in
error, please notify the sender immediately and delete the material from
your system.  This transmission is for informational purposes only, and is
not intended as an offer or solicitation for the purchase or sale of any
financial instrument or as an official confirmation of any transaction.  Any
information regarding specific investments or other products is not
warranted as to completeness or accuracy and is subject to change without
notice.

Mime
View raw message