ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From friendVU admin <maill...@friendvu.com>
Subject Re: Multiple queries.
Date Tue, 15 Feb 2005 08:14:12 GMT
You can create 2 different sqlmaps. Each has a connection. (I use a 
singleton factory, before I create a _sqlmap)
Ex:
SqlMapFac.setPropFile("properties/sql-map-async.xml");

.V


David Putnam wrote:

> 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