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:[email protected]]
Sent: Thursday, December 13, 2012 1:07 PM
To: [email protected]
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:[email protected]]
> Sent: Thursday, December 13, 2012 2:50 AM
> To: [email protected]
> 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.============================================================================================================================
