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* <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> 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* <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>
> <mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>>> 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* <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>
> <mailto: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>>
> > <mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
<mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>>>> 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
> >
> >
> >
> >
> >
>
>
>
