cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sampath Uppula <SU0064...@TechMahindra.com>
Subject RE: Problem in fetching the data from multiple tables with relationships.
Date Thu, 13 Dec 2012 08:32:10 GMT
Thanks Ari.

Please look at the following solution... I had implemented for similar scenario.
In the below code, I am able to fetch all the data from the relationships tables. But the
problem is I am forced to hardcode the return types (List<Role> List<LOB> ...).

Because of that I am not able to develop a strategic solution.

In other words the requirement is, 
Depends on the requirement, columns in the criteria and columns in the fetch will be different.
How can I achieve this using cayenne?
Hope you understand my problem now.

--sample code
	private static void testSQL() {
		ObjectContext context = DataContext.createDataContext();

		Expression expression = ExpressionFactory.matchExp("userAllocation.role", 1);
		expression = expression.andExp(ExpressionFactory
				.matchExp("taskAssignment.transaction.taskStatus", "INPROGRESS"));
		expression = expression.andExp(ExpressionFactory.matchExp("taskAssignment.assignUser", null));

		SelectQuery tmpQuery = new SelectQuery(User.class, expression);
		tmpQuery.addPrefetch("userAllocation");
		tmpQuery.addPrefetch("userAssign");
		tmpQuery.addPrefetch("taskAssignment");

		List<User> userList = context.performQuery(tmpQuery);
		List<Role> roleObject = null;
		List<LOB> lobObject = null;

		for (User tmpUser : userList) {
			logger.info("---" + tmpUser.getEmailAddress());
			List tmpList = (ArrayList) tmpUser.readNestedProperty("taskAssignment.transaction.taskAssignedDate");
			for (Object str : tmpList) {
				logger.info("--taskAssignedDate- " + str.toString());
			}

			roleObject = (List<Role>) tmpUser.readNestedProperty("userAllocation.role"); //this
can be achieved other way like tmpUser.getUserAllocation().get(0).getRole();
			lobObject = (List<LOB>) tmpUser.readNestedProperty("userAllocation.lob");

			if (roleObject != null && roleObject.get(0) != null)
				logger.info("Role---- " + roleObject.get(0).getPrimaryKey() + "  " + roleObject.get(0).getRoleName());
			else
				logger.info("---- no role is present ");
			if (lobObject != null && lobObject.get(0) != null)
				logger.info("lob---- " + lobObject.get(0).getPrimaryKey() + "  " + lobObject.get(0).getLobName());
			else
				logger.info("-- lob is not present");
		}
		context = null;
	}
-- 

Thanks,
Sampath Uppula

-----Original Message-----
From: Aristedes Maniatis [mailto:ari@maniatis.org] 
Sent: Thursday, December 13, 2012 1:07 PM
To: user@cayenne.apache.org
Subject: Re: Problem in fetching the data from multiple tables with relationships.

If you really want to use Cayenne for the power it gives you, rather than writing SQL, I recommend
you work your way through the tutorials. Yes, you can use SQLTemplate. No, that's probably
not what you want to do here.

At its heart, your query is just um.ID = 112. Start by making that work in Cayenne using a
simple select query. Just fetch one um object. And then follow the relations from that object
to the other objects and properties you need.

resultUM.getUA().getRole()

And remember, that rarely in Cayenne will you want to fetch or query on primary keys. Yes,
it will sometimes be needed, but mostly you let Cayenne handle that for you behind the scenes.


Ari



On 13/12/12 6:14pm, Sampath Uppula wrote:
> Hi John,
> Thanks for the reply.
> For the below query, can you provide a sample using SQLTemplate?
>
> 1. how to use the SQLTemplate to execute the below query?
> SELECT um.ID,
>         um.FIRST_NAME,
>         um.LAST_NAME,
>         ua.ROLE_ID,
>         tt.TASK_ASSIGNED_DATE,
>         tt.TASK_CLOSED_DATE,
>         tt.TASK_MASTER_ID
>    FROM t_user_master um,
>         t_user_allocation_details ua,
>         t_task_assignment_detail ta,
>         t_task_transaction_detail tt
>   WHERE     um.ID = ua.USER_ID
>         AND ta.USER_ID = um.ID
>         AND ta.TASK_TRANSACTION_ID = tt.ID
>         AND um.ID = 112;
> 2. how to read the data from the result set or List?
>
> Appreciate your help.
>
> Thanks,
> Sampath Uppula
>
>
>
> -----Original Message-----
> From: John Huss [mailto:johnthuss@gmail.com]
> Sent: Thursday, December 13, 2012 2:50 AM
> To: user@cayenne.apache.org
> Subject: Re: Problem in fetching the data from multiple tables with relationships.
>
> I'm not really sure what your question is.  Cayenne will fetch all of the
> fields that are modeled and turn them into objects.  And you can add
> prefetching paths to your SelectQuery to pre-fetch the related objects,
> otherwise they will be loaded when accessed.  If you want to write your own
> SQL and get a HashMap of the data you can use SQLTemplate instead of
> SelectQuery.
>
> John
>
> ============================================================================================================================Disclaimer:
 This message and the information contained herein is proprietary and confidential and subject
to the Tech Mahindra policy statement, you may review the policy at <a href="http://www.techmahindra.com/Disclaimer.html">http://www.techmahindra.com/Disclaimer.html</a>
externally and <a href="http://tim.techmahindra.com/tim/disclaimer.html">http://tim.techmahindra.com/tim/disclaimer.html</a>
internally within Tech Mahindra.============================================================================================================================
>

-- 
-------------------------->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C  5EFA EF6A 7D2E 3E49 102A

============================================================================================================================Disclaimer:
 This message and the information contained herein is proprietary and confidential and subject
to the Tech Mahindra policy statement, you may review the policy at <a href="http://www.techmahindra.com/Disclaimer.html">http://www.techmahindra.com/Disclaimer.html</a>
externally and <a href="http://tim.techmahindra.com/tim/disclaimer.html">http://tim.techmahindra.com/tim/disclaimer.html</a>
internally within Tech Mahindra.============================================================================================================================

Mime
View raw message