I am doing a monologue :(
for the count distinct variant a possible 'database independant' solution could be to change the code in BrokerHelper and 'simulate' a distinct clause with a groupBy on all requested columns instead of generating the database dependant SQL portion.
for the non-distinct count: instead of generating a count(*) you should generate a count( attr1, attr2, ... ) where attr1, attr2,... are the columns defined in the ReportQuery
I do not know how using a groupBy instead of a distinct can affect performance, maybe some database guru may post some more info :)
Maybe somebody would like to know why I am so interested in this stuff...
The problem is:
We have a dinamically generated query that may potentially deliver lots of results > 50000, the results must be shown on a web page. If we wouldn't limit the max. number of results and display everything on the web page, this would just kill the app. server.
So what we do is: - Generate the query - Execute a count on the query and if it exceeds the max. number of results throw an exception - If the no. of results is ok, execute the *same* query (no count this time) and materialise the results
This works like a charm for QueryByCriteria, but doesn't with the ReportQueryByCriteria since the 'count' delivers a different number than the size of the results collection delivered by executing the complete query.
hopefully the last post... bye danilo
An addition to my previous post:
I saw in the BrokerHelper class that the Platform.concatenate() method is used to combine multi-primary keys, however there is a problem:
The method is thought for concatenating strings and not numeric fields. For ex. in Sybase and MS-SQL you use the || character for string concatenation, but the count( distinct expr ) ) clause uses the + character, other dbms use also other syntax. I think that a separate method should be added for this purpose, something like concatForDistinct() that would generate the correct String for the distinct clause.
Hi,
(Using OJB 1.0.1)
I think the PersistenceBroker.getCount() on ReportQueryByCriteria used on joined tables produces wrong or at least unexpected results. :)
An example:
( Table 1 ) 1 -> N ( Table 2 )
Build a ReportQueryByCriteria like this: new ReportQueryByCriteria( Table1.class, new String [] { "table1PK", "pathToTable2.table2PK" }, criteria );
now calling PersistenceBroker.getCount( reportQuery ) will produce a count( * ) statement, which can deliver different results than count( table1PK, table2PK ) depending on the used join clause and the WHERE criterias.
Furthermore if setting the query to DISTINCT, OJB uses the PK column(s) of the 'searchClass' and generates following statement: SELECT count( distinct table1PK ) ... which will also not produce the expected result, at least the one expected by me :)
I think the correct query (distinct and not) should just contain the same columns defined in the original ReportQuery.
OJB shares the same core algorithm for generating the count query for QueryByCriteria and ReportQueryByCriteria, while for the QueryByCriteria the algorithm is correct for the ReportQuery it probably isn't.
The 'evil' code is in class org.apache.ojb.broker.util.BrokerHelper
bye danilo
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
