Vijay,
> It seems that other option left is to use a jdbc connection to database.
BJ Freeman's suggestion with org.ofbiz.entity.jdbc.SQLProcessor will serve you here. It's as close
to using raw JDBC as you can get. Any more raw than that, and you may be cursing and swearing
about how manual and menial the task is.
> Is it also imperative then, that for very complex querries having
> "groupby"..."having" and other clauses it is better to use jdbc method of
> connection.
I would say this. If the queries are complex, all the more you should use the Entity Engine
(DynamicViewEntity or just <view-entity>).
Execution speed is important, true. However, so is bug-free development! Using raw JDBC,
maintaining your queries will be a nightmare (such as changes, maintenance, debugging, etc). That
is if you can even get past CREATING your queries CORRECTLY in the first place!
When I used raw JDBC for complex queries spanning over multiple tables, I had to strictly
structure my queries into "clean JOIN hierarchies". Lots of work.
Before long, raw JDBC gave way to some APIs (much like the Entity Engine, but much weaker), even
in PHP where things can be easily done in "raw and ugly" way. The "raw and ugly" way just wasn't
cutting it. Look at the new Zend framework (for PHP), and you'll see the evolution towards
something like OFBiz's Entity Engine.
> How does it affect the ofbiz philosophy of architecture if i use jdbc for
> executing these querries.
Not good. Centralizing all DB access through the Entity Engine will make your codes easier to
maintain. Any optimizations to the Entity Engine will benefit all of your DB queries all at once.
> As right now i cannot find any ways of implementing those aggregate
> functions(sum,min,max...) through Entity Engine (without creating views).
On the topic of views. Let's take PHP as an example, the biggest facilitator of the "just hack it"
philosophy.
Like I said above, complex queries spanning multiple tables are easiest to maintain if you
structure them cleanly into views first. Then those views can be reused over and over in many
places, with minimal chance for errors. You'll see PHP coders either putting the views into the
RDBMS itself (for VERY FAST execution speeds), or at least defining the views even in PHP codes.
In OFBiz's case, the Entity Engine doesn't seem to make use of RDBMS view mechanisms, IIRC. Yes,
that'll mean a performance hit in execution speeds.
However, the common practice is to just get the software up and running first, coded CLEANLY for
easy management and changes in future. Optimize later. Of course, optimize in areas where there
performance is hit worst, for biggest ROI.
> Also do you have any idea of performance behaviour on using jdbc connection?
Not sure. But my guess is that raw JDBC could be as much as 100 times faster than using
DynamicViewEntity. But the question you should ask is... does that matter? Are you making so many
instances of the same query that will warrant optimizing it 100 fold?
Jonathon
vijay Si wrote:
Jonathon
It seems that other option left is to use a jdbc connection to database. Is
it also imperative then, that for very complex querries having
"groupby"..."having" and other clauses it is better to use jdbc method of
connection.
How does it affect the ofbiz philosophy of architecture if i use jdbc for
executing these querries. As right now i cannot find any ways of
implementing those aggregate functions(sum,min,max...) through Entity Engine
(without creating views). Also do you have any idea of performance behaviour
on using jdbc connection?
Thanks.
On 9/15/07, Jonathon -- Improov <[EMAIL PROTECTED]> wrote:
There's no other (easy) way to query database, aside from using the Entity
Engine (which was what
I suggested).
If you're worried about performance hit with a huge (and unconditional)
cross-product, see
http://www.nabble.com/forum/ViewPost.jtp?post=12590081&framed=y and
https://issues.apache.org/jira/browse/OFBIZ-1232
Jonathon
vijay Si wrote:
Hi Jonathon,
i cannot use the view-entity for querrying, as it would cause a
performance
hit. Is there any other method to get the above querry. Do u know of any
ways to do it through bsh script.
Regards
On 9/15/07, Jonathon -- Improov <[EMAIL PROTECTED]> wrote:
Use the GenericDelegator and related classes, I think. Or maybe not. I
can't recall the aggregate
functions there.
The entity definitions do have those, though. The <view-entity>? Oh
yes,
the <view-entity>.
Try using DynamicViewEntity to construct a <view-entity>.
Jonathon
vijay Si wrote:
Is there any funtionality provided for using aggregate functions
(sum,min,max..) and count() for querrying to data base through bsh.
for egg: how can we write following in BSH :
Select sum(order_id),sum(grand_total) from order_header where
order_type_id="PURCHASE_ORDER";
Regards
------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.487 / Virus Database: 269.13.19/1008 - Release Date:
9/14/2007 8:59 AM
------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.487 / Virus Database: 269.13.19/1008 - Release Date:
9/14/2007 8:59 AM
------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.487 / Virus Database: 269.13.21/1010 - Release Date: 9/15/2007 7:54 PM
