hi reda,
the problem is imo that the subqueries are not extent aware (as mentioned in the other post about splitCriteria).
i suggest you separate the queries. the first one retrieves all the ids and the second uses these ids in an IN-clause:
ReportQueryByCriteria subQuery = new ReportQueryByCriteria(Company.class, compCrit);
subQuery.setAttributes(new String[] {"cpyId"});
Iterator subIter = broker.getReportQueryIteratorByQuery(subQuery);
Collection subIds = new ArrayList();
while (subIter.hasNext())
{
Object[] id = (Object[])subIter.next();
subIds.add(id[0]);
}Query empQuery = new QueryByCriteria(Employee.class, crit);
Criteria empCrit = new Criteria();
empCrit.addLike("name", "%A%");
empCrit.addIn("cpyId", subIds);hth jakob
Reda Benzair schrieb:
Hi,
I am facing a problem you already encountered, but don't seem to find a solution.
*SUMMARY*
I would like to include a reportQuery that returns only one attribute inside a Criteria :
*EXISTING*
Using OJB 1.0, on JDK 1.4.2 (with Tomcat 4.1.30)
Tables:
* A Company has a name * An Employee has a name * An Employee has a Company
class Company { Integer cpyId; //the ID String name; //the name
/* Collection of employees*/ Employee[] employees; }
class Employee { Integer empId; //the ID String name; //the name
/* Reference to a company */ Integer cpyId; Company company; }
Mapping: In fact, Company is an interface with one single extent CompanyImpl. Employee is an interface with one single extent EmployeeImpl.
------------------------------------------------------------------------
*PROBLEM*
I want to get all the user with an 'A' in their name, and who belong to a company with a 'B' in its name.
I know it could be done like this:
Criteria crit = new Criteria(); crit.addLike("company.name", "%B%"); crit.addLike("name", "%A%"); broker.getIteratorByQuery(new QueryByCriteria(Employee.class, crit));
but this is a simple exemple to help you understand my problem. I'm trying to use a subquery (because my subquery is much more complex than a simple like)
Criteria compCrit = new Criteria(); compCrit.addLike("name", "%B%"); Query empQuery = new QueryByCriteria(Employee.class, crit); Criteria empCrit = new Criteria(); empCrit.addLike("name", "%A%"); ReportQueryByCriteria subQuery = new ReportQueryByCriteria(Company.class, compCrit); subQuery.setAttributes(new String[] {"cpyId"}); empCrit.addIn("cpyId", subQuery); broker.getIteratorByQuery(empQuery);
This second piece of code generate a NullPointerException :
java.lang.NullPointerException
at org.apache.ojb.broker.accesslayer.sql.SqlQueryStatement$TableAlias.hashCode(SqlQueryStatement.java:1920)
at java.util.HashMap.hash(HashMap.java:261)
at java.util.HashMap.put(HashMap.java:379)
at org.apache.ojb.broker.accesslayer.sql.SqlQueryStatement.splitCriteria(SqlQueryStatement.java:1630)
at org.apache.ojb.broker.accesslayer.sql.SqlQueryStatement.<init>(SqlQueryStatement.java:130)
at org.apache.ojb.broker.accesslayer.sql.SqlSelectStatement.<init>(SqlSelectStatement.java:64)
at org.apache.ojb.broker.accesslayer.sql.SqlQueryStatement.getSubQuerySQL(SqlQueryStatement.java:910)
at org.apache.ojb.broker.accesslayer.sql.SqlQueryStatement.appendSubQuery(SqlQueryStatement.java:891)
at org.apache.ojb.broker.accesslayer.sql.SqlQueryStatement.appendParameter(SqlQueryStatement.java:876)
at org.apache.ojb.broker.accesslayer.sql.SqlQueryState ment.appendInCriteria(SqlQueryStatement.java:704)
at org.apache.ojb.broker.accesslayer.sql.SqlQueryStatement.appendCriteria(SqlQueryStatement.java:783)
at org.apache.ojb.broker.accesslayer.sql.SqlQueryStatement.appendSQLClause(SqlQueryStatement.java:856)
at org.apache.ojb.broker.accesslayer.sql.SqlQueryStatement.asSQLStatement(SqlQueryStatement.java:609)
at org.apache.ojb.broker.accesslayer.sql.SqlQueryStatement.appendClause(SqlQueryStatement.java:532)
at org.apache.ojb.broker.accesslayer.sql.SqlQueryStatement.appendWhereClause(SqlQueryStatement.java:484)
at org.apache.ojb.broker.accesslayer.sql.SqlSelectStatement.getStatement(SqlSelectStatement.java:251)
at org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl.getPreparedSelectStatement(SqlGeneratorDefaultImpl.java:151)
at org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl.java:276)
at org.apache.ojb.broker.accesslayer.RsQueryObject.perfo rmQuery(RsQueryObject.java:74)
at org.apache.ojb.broker.accesslayer.RsIterator.<init>(RsIterator.java:183)
at org.apache.ojb.broker.core.RsIteratorFactoryImpl.createRsIterator(RsIteratorFactoryImpl.java:58)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQuery(PersistenceBrokerImpl.java:1831)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getIteratorFromQuery(PersistenceBrokerImpl.java:1331)
at org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryReferenceBroker.java:111)
at org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryReferenceBroker.java:233)
at org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryReferenceBroker.java:253)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getCollectionByQuery(PersistenceBrokerImpl.java:1215)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery(DelegatingPersi stenceBroker.java:338)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery(DelegatingPersistenceBroker.java:338)
In fact, in this case, I would simply like OJB to retreive all the Employee that are returned by
select * from employee where name like '%A%' and cpyId in (select cpyId from company where name like '%B%')
I've found out you faced exactly the same problem, in a thread entitled "SqlQueryStatement.splitCriteria -> NullPointerException". Did you reach a solution?
thinks all
--------------------------------------------------------------------- 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]
