This discussion is archived
2 Replies Latest reply: Jun 12, 2012 12:48 PM by 917633 RSS

using 2 tables framing of dynamic query wherein columns are decided runtime

917633 Newbie
Currently Being Moderated
Hi Team,
I had a requirement could anyone help me out or suggest your views. we are using Adf faces as UIlayer and Toplink for dataaccess. In that we had 2 tables systemdefinedTable and userdefinedTable which has one-to-one relation. Userdefinedtable has columns col1,col2,col3.......col50 and this comes at runtime from xml like col1 as Lastname,col2 as firstname.

Now we need to generate a dynamic query that joins the systemdefinedTable and userdefinedTable to produce the result for the UI layer. Now suppose if the xml has 2 cols tag.
The dynamic query may look like this:
Select S.ID, S.NAME, S.STATUS, U.COL1 as FirstName, U.COL2 as LastName
From userdefinedTable U, systemdefinedTable S
Where S.QID = U.QID
Next if the xml has 3 more column tags then COL1,COL2,COL3 ...like wise columns will be decided runtime..So please share your views or any related blogs to achieve this.

Thanks in Advance for your help.
  • 1. Re: using 2 tables framing of dynamic query wherein columns are decided runtime
    cdelahun Pro
    Currently Being Moderated
    Hello,

    If you are using TopLink and need dynamically generated queries, why not use TopLink Expressions for your queries, and return java objects back. Then you can display which ever fields from the fully populated objects that you need.

    See the docs for TopLink queries here:
    http://docs.oracle.com/cd/E17904_01/web.1111/b32441/qryun.htm#autoId28

    A simple example returning all systemdefinedObjects would be:
    ReadAllQuery query = new ReadAllQuery(systemdefinedObject.class);
    query.addJoinedAttribute(query.getExpressionBuilder().get("user"));
    Collection<systemdefinedObject> results = session.executeQuery(query);

    This allows populating the cache and for caching the statements and the query results.

    But I'm not sure exactly what you are after.

    Best Regards,
    Chris
  • 2. Re: using 2 tables framing of dynamic query wherein columns are decided runtime
    917633 Newbie
    Currently Being Moderated
    Christopher,

    Thanks for your Reply. I am new to Oracle toplink. Actually i need to get the particular columns in systemdefinetable and dynamic cols(col1,col2......col50) from userdefined table which has one-one relationship. so i didnt find related sample using expressions. so i had written below code by framing sql query into one stringbuffer

    Query query = getSystemEntityManager().createNativeQuery(sb.toString());
    List result = query.getResultList();

    Now i want to bind this dynamic columns list into jspx page. How can i achieve this? In this columns will be decided at runtime so i can not use static columns in the jspx page. could you Please help me out.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points