ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Gagnon <dgag...@siunik.com>
Subject Re: Updating from ibatis 1.3.1 to 2.1.7 .. what about this one .. how to access the resultSet because performance matters
Date Thu, 17 Aug 2006 19:57:56 GMT
Sure.. maybe you will give me a better way to do my report.  For every 
report I issue a big SQL and go thought the resultSet (wich can have to 
generate SAX events to feed FOP to generate a PDF report. 

So I call the SQL read the resultSet and create SAX EVent,  per example, 
for each row:

handler.startElement("accountPayable");

            handler.element("id", rs.getString(1));
            BigDecimal paid = rs.getBigDecimal(2);
            handler.element("paid", moneyFormat.format(paid));
            BigDecimal amount = rs.getBigDecimal(3);
            handler.element("amount", moneyFormat.format(amount));
            handler.element("date", 
DateSupport.getDateAsString(language, new MultiFieldDate(rs.getDate(4))));
            handler.element("dateForcast", 
DateSupport.getDateAsString(language, new MultiFieldDate(rs.getDate(15))));

            BigDecimal minus30 = rs.getBigDecimal(5);
            handler.element("minus30", moneyFormat.format(minus30));
            BigDecimal between3160 = rs.getBigDecimal(6);
            handler.element("between3160", moneyFormat.format(between3160));
            BigDecimal between6190 = rs.getBigDecimal(7);
            handler.element("between6190", moneyFormat.format(between6190));
            BigDecimal plus91 = rs.getBigDecimal(8);
            handler.element("plus91", moneyFormat.format(plus91));
            handler.element("totalLine", 
moneyFormat.format(minus30.add(between3160.add(between6190).add(plus91))));

            SupplierNode clientNode = (SupplierNode) (parent);
            clientNode.total30Less = clientNode.total30Less.add(minus30);
            clientNode.total3160 = clientNode.total3160.add(between3160);
            clientNode.total6190 = clientNode.total6190.add(between6190);
            clientNode.total91More = clientNode.total91More.add(plus91);

            handler.endElement("accountPayable");

In this scenario I want to have my SQL statement in the SQLMap, be able 
to use the framework to map parameter. But I want to go though the 
ResulSet my self.  The reports are often huge and take time to create.  
So creating object for each row of the ResultSet appear to me like an 
overhead that will hit performance significantly. 

Maybe I'm wrong.  I'm still figuring out how to migrate some of my stuff 
to 2.1.7 so any help, or comments are welcome :-)

David

> Not sure when this was but stored proc calls can use a parameterMap.
>
> The issue of using straight JDBC has come up multiple times and 
> everytime, in the end, they see that there is really no reason to.  
> Supply a good reason and you might change my mind.
>
> Nathan
>
>
> On 8/17/06, *David Gagnon* <dgagnon@siunik.com 
> <mailto:dgagnon@siunik.com>> wrote:
>
>     I do love Ibatis ...  and all my DAOs using it the way it should .. of
>     course you only hear of thing that doesn't work :-)  Like piece of
>     code
>     that use StoredProcedure, or function that create report which are CPU
>     intensive.
>
>     When I start using stored procedure with Ibatis you were taking about
>     How to implement parameterMap (If I remember well :-)).
>     So its been a while ago and I need to standardize a bunch of stuff.
>
>     And even if I use JDBC directly, I like to store the statement in the
>     map anyway.  I have no SQL hardcoded in the code and all the SQL
>     are at
>     the same place (i.e.: in the SqlMap).
>
>     For now I found the ExtendedSqlMapClient interface and I will see
>     if I
>     cannot use it as an intermediate step (To avoid refactor too much
>     stuff
>     at the same time)
>
>     Thanks for the help! It's really appreciated!
>
>     Best Regard
>     /David
>
>
>     Clinton Begin wrote:
>     >
>     > Wow, I wonder why you're using iBATIS at all.  ;-)
>     >
>     > In this case you have three options:
>     >
>     > 1) Stop accessing the ResultSet and let iBATIS do its job.
>     > Performance at that level should not be an issue for you.
>     >
>     > 2) Use a RowHandler, it's a bit closer to the ResultSet, so you
>     can do
>     > whatever you like with it -- mostly to process row-by-row, so you
>     > don't have to load them all up into memory.
>     >
>     > 3) Bypass iBATIS entirely and just use JDBC for this piece.
>     >
>     > Cheers,
>     > Clinton
>     >
>     >
>     > On 8/17/06, *David Gagnon* <dgagnon@siunik.com
>     <mailto:dgagnon@siunik.com>
>     > <mailto:dgagnon@siunik.com <mailto:dgagnon@siunik.com>>> wrote:
>     >
>     >     Hi all,
>     >
>     >       Thanks for your help.  Upgrading is a huge job but it goes
>     well so
>     >     far.  How can I have access to the ResultSet.  Here I need
>     performance
>     >     so I need to access the resultSet directly.  More I have 60+
>     reports
>     >     that all access the resultSet directly.
>     >
>     >     Thanks for you help!
>     >
>     >     Regards
>     >     /David
>     >
>     >
>     >         public synchronized void processDataChangeNotification() {
>     >             String sql =
>     >     dbNotificationDao.getSqlMap
>     >     ().getMappedStatement(DbNotificationDaoX.NOTIFICATION_PREFIX
>     >     + "." + WebOsConstants.DB_GET).getSql(null);
>     >             DbConnection dbConDetail = null;
>     >             // allow only one at a time
>     >             try {
>     >
>     >                 ConnectionFactory connectionFactory =
>     (ConnectionFactory)
>     >     ManagersFactory.getManager(ConnectionFactory.MANAGER_KEY);
>     >
>     >                 dbConDetail = connectionFactory.getConnection();
>     >                 dbConDetail.startTransaction ();
>     >                 ResultSet detailRs2 = dbConDetail.executeQuery(sql);
>     >                 while (detailRs2.next()) {
>     >                     String entity = detailRs2.getString(1);
>     >                     cacheUpdateInfo.id1 = detailRs2.getString (2);
>     >                     cacheUpdateInfo.id2 = detailRs2.getString(3);
>     >                     cacheUpdateInfo.id3 = detailRs2.getString(4);
>     >                     cacheUpdateInfo.id4 = detailRs2.getString(5);
>     >                     cacheUpdateInfo.timestamp =
>     detailRs2.getString(6);
>     >                     CachedDao handler = (CachedDao)
>     >     dataChangeHandler.get(entity);
>     >                     if (handler != null) {
>     >                         handler.handleDataChange(entity,
>     >     cacheUpdateInfo);
>     >                     } else {
>     >                         log.error("No data change handler
>     register for
>     >     entity : " + entity);
>     >                     }
>     >                 }
>     >
>     >                 // delete processed notification
>     >                 sql =
>     >    
>     dbNotificationDao.getSqlMap().getMappedStatement(DbNotificationDaoX.NOTIFICATION_PREFIX
>
>     >     + "." + WebOsConstants.DB_DELETE).getSql(null);
>     >                 dbConDetail.execute(sql);
>     >
>     >                 dbConDetail.commitTransaction();
>     >
>     >             } catch (SQLException e) {
>     >                 log.error("Unable to write the db
>     (EntitySerialiser): " +
>     >     e.getMessage(), e);
>     >             } finally {
>     >                 if (dbConDetail != null)
>     >                     dbConDetail.closeAll();
>     >             }
>     >
>     >
>     >         }
>     >
>     >
>     >
>     >
>     >
>     >     Clinton Begin wrote:
>     >     > Using setUserConnection will work.   It's thread safe, so
>     don't
>     >     worry
>     >     > about concurrent access.  Just make sure to "unset" it by
>     calling
>     >     > setUserConnection (null) -- which reminds me, we should add a
>     >     > clearUserConnection() to make this code nicer.
>     >     >
>     >     > The other way to do it is with openSession (Connection
>     conn).  But I
>     >     > don't see a reason for you to do that here.
>     >     >
>     >     > As for accessing the SQL statements themselves -- be
>     warned, it will
>     >     > not be easy.  iBATIS 2.0 does a lot more to keep you away
>     from the
>     >     > guts of the framework to avoid upgrade issues...as you're
>     >     experiencing
>     >     > now.  ;-)
>     >     >
>     >     > Let us know when you get to that point, we'll see what we
>     can do.
>     >     >
>     >     > public final List getList(DbConnection dbCon, String
>     mapKey, Object
>     >     > param)
>     >     >     throws DatabaseException {
>     >     >   boolean handleTransactionLocally = false;
>     >     >   try {
>     >     >     if (dbCon == null) {
>     >     >       handleTransactionLocally = true;
>     >     >       dbCon = connectionFactory.getConnectio n();
>     >     >       dbCon.setReadOnlyConnection(true);
>     >     >     }
>     >     >     sqlMap.setUserConnection (dbCon.getConnection());
>     >     >     return sqlMap.queryForList(mapKey, param);
>     >     >   } catch (SQLException e) {
>     >     >     // log.error ("Error during sql: " + e.getMessage(), e);
>     >     >
>     >     >     ExceptionAdaptor.instance(exceptionAdaptorKey).getMappe
>     >     dException(e,
>     >     >       "getList: " + e.getMessage(), true,
>     >     > ExceptionAdaptor.ACTION_SEARCH , param);
>     >     >   } finally {
>     >     >     sqlMap.setUserConnection (null);
>     >     >     if (dbCon != null) {
>     >     >       dbCon.closeAll(handleTransactionLocally);
>     >     >     }
>     >     >   }
>     >     > }
>     >     >
>     >     > Clinton
>     >     >
>     >     >
>     >     >
>     >     >
>     >     > On 8/16/06, *David Gagnon* <dgagnon@siunik.com
>     <mailto:dgagnon@siunik.com>
>     >     <mailto: dgagnon@siunik.com <mailto:dgagnon@siunik.com>>
>     >     > <mailto:dgagnon@siunik.com <mailto:dgagnon@siunik.com>
>     <mailto:dgagnon@siunik.com <mailto:dgagnon@siunik.com>>>> wrote:
>     >     >
>     >     >     Hi all,
>     >     >
>     >     >     I'm having a lot of fun trying to convert my code to
>     >     2.1.7.  There is
>     >     >     still a lot of things I didn`t figured out yet.
>     >     >
>     >     >     Can you please tell me how to convert the following
>     function.?
>     >     >
>     >     >
>     >     >         public final List getList(DbConnection dbCon,
>     String mapKey,
>     >     >     Object
>     >     >     param) throws DatabaseException {
>     >     >
>     >     >             List list = null;
>     >     >             boolean handleTransactionLocally = false;
>     >     >             try {
>     >     >                 if (dbCon == null) {
>     >     >                     handleTransactionLocally = true;
>     >     >                     dbCon = connectionFactory.getConnection();
>     >     >                     dbCon.setReadOnlyConnection(true);
>     >     >                 }
>     >     >
>     >     >                 MappedStatement statement =
>     >     >     sqlMap.getMappedStatement(mapKey);
>     >     >                 list =
>     >     >     statement.executeQueryForList (dbCon.getConnection(),
>     >     >     param);
>     >     >             } catch (SQLException e) {
>     >     >     //            log.error("Error during sql: " +
>     >     e.getMessage(), e);
>     >     >
>     >     >     ExceptionAdaptor.instance
>     >     (exceptionAdaptorKey).getMappedException(e,
>     >     >     "getList: " + e.getMessage(), true,
>     >     >     ExceptionAdaptor.ACTION_SEARCH, param);
>     >     >             } finally {
>     >     >                 if (dbCon != null) {
>     >     >                     dbCon.closeAll(handleTransactionLocally);
>     >     >                 }
>     >     >             }
>     >     >             return list;
>     >     >         }
>     >     >
>     >     >
>     >     >
>     >     >     I saw a sqlMap.setUserConnection () but this method can be
>     >     called by
>     >     >     simultaneously so setting the connection in the sqlMap
>     >     doesn`t make
>     >     >     sense to me.  There is probably something that I don`t
>     >     understand!
>     >     >
>     >     >     Please help :-)
>     >     >
>     >     >     Best Regards.
>     >     >     /David
>     >     >     P.S.: Also I do need to get the SQL string from a map to
>     >     play with it
>     >     >     myself.  I'm not there yet but I hope there a way to get
>     >     it!  Thanks
>     >     >
>     >     >
>     >     >
>     >     >
>     >     >
>     >
>     >
>     >
>
>
>



Mime
View raw message