FAQ
Hi,

There is a 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;

I want to achieve the above result using cayenne.
In the above query, the criteria (where condition) and select list of columns are dynamic and depends on the scenario of a report the entire query will be new and dynamic.

Can anyone suggest the solution in cayenne.

I had tried in the below way,

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

Expression expression = ExpressionFactory.matchExp("userAllocation.role", 1);
expression = expression.andExp(ExpressionFactory
.noMatchExp("taskAssignment.transaction.taskAssignedDate", null));
SelectQuery tmpQuery = new SelectQuery(User.class, expression);
List<User> userList = context.performQuery(tmpQuery);
logger.info("execution is successfull...");
for (User tmpUser : userList) {
logger.info("---" + tmpUser.getEmailAddress());

if (tmpUser.readNestedProperty("taskAssignment.transaction.taskAssignedDate") != null) {
logger.info("@@Object only --- "
+ tmpUser.readNestedProperty("taskAssignment.transaction.taskAssignedDate"));
}
logger.info("---- " + tmpUser.readNestedProperty("userAllocation.role"));
logger.info("-lob-- " + tmpUser.readNestedProperty("userAllocation.lob"));
}
}

I am able to get the output... but don't know whether it is correct or not because some of the tables having the one..one/one..many relationships.
The output of the -- tmpUser.readNestedProperty("userAllocation.role") - is, @@Object only --- [Thu Nov 29 00:00:00 IST 2012, Thu Nov 29 00:00:00 IST 2012].
Why the brackets are part of the output?

Please suggest.

Thanks,
Sampath Uppula.


============================================================================================================================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.============================================================================================================================

Search Discussions

  • John Huss at Dec 12, 2012 at 9:20 pm
    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
  • Sampath Uppula at Dec 13, 2012 at 7:15 am
    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
    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 at Dec 13, 2012 at 7:38 am
    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
    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
  • Sampath Uppula at Dec 13, 2012 at 8:32 am
    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
    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
    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.============================================================================================================================
  • Aristedes Maniatis at Dec 14, 2012 at 10:20 am

    On 13/12/12 7:32pm, Sampath Uppula wrote:
    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.
    Not really. You asked for a solution to a problem which was fetching data and following relations. Note that your prefetches are not needed, they are only a way to improve performance in some situations.

    Nothing in Cayenne forces you to specify the specific entity in the generics. You can treat the List as <PersistentObject> and handle it how you choose.


    Ari



    --
    -------------------------->
    Aristedes Maniatis
    GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
  • Sampath Uppula at Dec 19, 2012 at 11:44 am
    Now I am working for reporting requirements, where I want to fetch the data from multiple tables and relationships between them are one-to-one and one-to-many and many-to-many.
    If we use 'SelectQuery' or 'SQLTemplate' the output is list<Root> objects.
    Using the root object we can fetch the references data that may be a List or an Object.

    Here the problem is, for one root object there is list of child objects.
    In my output, I have to create a new row for every child object with all other root and child objects.
    Can anybody suggest a solution using cayenne?

    I completely struck... not getting idea how to implement this?

    Thanks,
    Sampath Uppula.


    -----Original Message-----
    From: Aristedes Maniatis
    Sent: Friday, December 14, 2012 3:50 PM
    To: user@cayenne.apache.org
    Subject: Re: Problem in fetching the data from multiple tables with relationships.
    On 13/12/12 7:32pm, Sampath Uppula wrote:
    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.
    Not really. You asked for a solution to a problem which was fetching data and following relations. Note that your prefetches are not needed, they are only a way to improve performance in some situations.

    Nothing in Cayenne forces you to specify the specific entity in the generics. You can treat the List as <PersistentObject> and handle it how you choose.


    Ari



    --
    -------------------------->
    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.============================================================================================================================

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupuser @
categoriescayenne
postedDec 12, '12 at 12:27p
activeDec 19, '12 at 11:44a
posts7
users3
websitecayenne.apache.org

People

Translate

site design / logo © 2021 Grokbase