Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Subqueries using ExpressionBuilder
Subqueries using ExpressionBuilder [message #642676] Thu, 02 December 2010 05:15 Go to next message
Fabio Collini is currently offline Fabio Collini
Messages: 2
Registered: December 2010
Junior Member
Hi,
we need to execute a query that should return a list of entities and a calculated value and that executes a filter on that calculated value. We developed the following code that executes this query using ExpressionBuilder:

	public void testExecuteQuery()
	{

		ExpressionBuilder mainEb = new ExpressionBuilder();

		ReportQuery mainQuery = new ReportQuery(LocazioneFisica.class, mainEb);

		mainQuery.addAttribute("main", mainEb);
		mainQuery.addAttribute("occupata", creaOccupataExpr(mainEb));

		mainQuery.setSelectionCriteria(creaOccupataExpr(mainEb).equal("true"));
		List<ReportQueryResult> resultList = (List<ReportQueryResult>) ((JpaEntityManager) entityManager.getDelegate()).getActiveSession()
				.executeQuery(mainQuery);
	}

	private Expression creaOccupataExpr(ExpressionBuilder mainEb)
	{
		ExpressionBuilder subEb = new ExpressionBuilder();
		ReportQuery subQuery = new ReportQuery(Contenitore.class, subEb);
		subQuery.addAttribute("id");
		subQuery.setSelectionCriteria(subEb.get("locazioneFisica").equal(mainEb));
		Map<Expression, Object> map = new HashMap<Expression, Object>();
		map.put(mainEb.get("area").notNull(), "false");
		map.put(mainEb.exists(subQuery), "true");
		Expression occupataExpr = mainEb.caseConditionStatement(map, "false");
		return occupataExpr;
	}


The problem is that the subSelect is executed two times, once in the result clause and once in the selection criteria. Is it possible to perform the same query with a single execution of subselect?
We would need a method to add an external query of a reportQuery, something like:

select * from (
  select obj.*, calculatedValue from ....
)
where calculatedValue is not null


Thanks in advance
Best regards, Fabio
Re: Subqueries using ExpressionBuilder [message #642805 is a reply to message #642676] Thu, 02 December 2010 13:22 Go to previous message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Sub-selects in the from clause are not currently supported. You could log an enhancement request. I'm actually surprised that a sub-select in the select clause worked.

If you need this type of query you can use native SQL.


James : Wiki : Book : Blog : Twitter
Previous Topic:No Cache Synchronization after em.clear?
Next Topic:A tool for Converting JPA entities to XML/JAXB ?
Goto Forum:
  


Current Time: Fri Aug 22 09:57:16 EDT 2014

Powered by FUDForum. Page generated in 0.01798 seconds