Cheers,
Clinton
On 8/17/06, *David Gagnon* < [EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> 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* <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>
> <mailto:[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]>>
> > <mailto:[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]>>>
> > > <mailto:[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
> > >
> > >
> > >
> > >
> > >
> >
> >
> >
>
>
>
