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 David Gagnon <dg...@siunik.com> on 2006/08/17 17:22:22 UTC
Re: Updating from ibatis 1.3.1 to 2.1.7 .. what about this one .. how
to access the resultSet because performance matters
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
> <ma...@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
>
>
>
>
>
Re: Updating from ibatis 1.3.1 to 2.1.7 .. what about this one .. how
to access the resultSet because performance matters
Posted by David Gagnon <dg...@siunik.com>.
> >> So creating object for each row of the ResultSet appear to me like an
> >> overhead that will hit performance significantly.
>
> I doubt it will be that significant, especially if you use a
> RowHandler. This sounds like a case where you could.
>
> You'll never know if you don't try it! (simple solution first)
Yes but you know the problem is that there more than 60 existing reports
.. so a bunch of work/test in perspective. Anyway I will look into it.
Maybe it's not that complicated... :-)
I'll keep you informed.
Thanks for your help!
/David
>
> Cheers,
> Clinton
>
> On 8/17/06, *David Gagnon* < dgagnon@siunik.com
> <ma...@siunik.com>> wrote:
>
> 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
> <ma...@siunik.com>
> > <mailto:dgagnon@siunik.com <ma...@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
> <ma...@siunik.com>
> > <mailto:dgagnon@siunik.com <ma...@siunik.com>>
> > > <mailto:dgagnon@siunik.com <ma...@siunik.com>
> <mailto:dgagnon@siunik.com <ma...@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
> <ma...@siunik.com>
> > <mailto:dgagnon@siunik.com <ma...@siunik.com>>
> > > <mailto: dgagnon@siunik.com
> <ma...@siunik.com> <mailto: dgagnon@siunik.com
> <ma...@siunik.com>>>
> > > > <mailto:dgagnon@siunik.com
> <ma...@siunik.com> <mailto:dgagnon@siunik.com
> <ma...@siunik.com>>
> > <mailto: dgagnon@siunik.com <ma...@siunik.com>
> <mailto:dgagnon@siunik.com <ma...@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
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
> >
>
>
>
Re: Updating from ibatis 1.3.1 to 2.1.7 .. what about this one .. how to access the resultSet because performance matters
Posted by Clinton Begin <cl...@gmail.com>.
>> So creating object for each row of the ResultSet appear to me like an
>> overhead that will hit performance significantly.
I doubt it will be that significant, especially if you use a RowHandler.
This sounds like a case where you could.
You'll never know if you don't try it! (simple solution first)
Cheers,
Clinton
On 8/17/06, David Gagnon <dg...@siunik.com> wrote:
>
> 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
> > <ma...@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
> > <ma...@siunik.com>
> > > <mailto:dgagnon@siunik.com <ma...@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
> > <ma...@siunik.com>
> > > <mailto: dgagnon@siunik.com <ma...@siunik.com>>
> > > > <mailto:dgagnon@siunik.com <ma...@siunik.com>
> > <mailto:dgagnon@siunik.com <ma...@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
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
> >
>
>
>
Re: Updating from ibatis 1.3.1 to 2.1.7 .. what about this one .. how to access the resultSet because performance matters
Posted by Koka Kiknadze <22...@gmail.com>.
>
> Supply a good reason and you might change my mind.
>
I guess the only good reason can be if some vendor specific things start to
perform much better (e.g. oracle batching vs standard one), but I think (and
even hope) that'll be 'start of the end' for that vendor, lol
Re: Updating from ibatis 1.3.1 to 2.1.7 .. what about this one .. how
to access the resultSet because performance matters
Posted by David Gagnon <dg...@siunik.com>.
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
> <ma...@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
> <ma...@siunik.com>
> > <mailto:dgagnon@siunik.com <ma...@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
> <ma...@siunik.com>
> > <mailto: dgagnon@siunik.com <ma...@siunik.com>>
> > > <mailto:dgagnon@siunik.com <ma...@siunik.com>
> <mailto:dgagnon@siunik.com <ma...@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
> > >
> > >
> > >
> > >
> > >
> >
> >
> >
>
>
>
Re: Updating from ibatis 1.3.1 to 2.1.7 .. what about this one .. how to access the resultSet because performance matters
Posted by Nathan Maves <na...@gmail.com>.
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 <dg...@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
> > <ma...@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
> > <ma...@siunik.com>
> > > <mailto:dgagnon@siunik.com <ma...@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
> > >
> > >
> > >
> > >
> > >
> >
> >
> >
>
>
>
Re: Updating from ibatis 1.3.1 to 2.1.7 .. what about this one .. how
to access the resultSet because performance matters
Posted by David Gagnon <dg...@siunik.com>.
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
> <ma...@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
> <ma...@siunik.com>
> > <mailto:dgagnon@siunik.com <ma...@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
> >
> >
> >
> >
> >
>
>
>
Re: Updating from ibatis 1.3.1 to 2.1.7 .. what about this one .. how to access the resultSet because performance matters
Posted by Clinton Begin <cl...@gmail.com>.
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 <dg...@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
> > <ma...@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
> >
> >
> >
> >
> >
>
>
>