QuerySpec for DB query

[magupta]

Need help in converting below SQL query into queryspec

select * from wind.WTChangeOrder2 ch left outer join wind.WTChangeOrder2Master chm
on ch.idA3masterReference = chm.idA2A2
left outer join wind.ObjectHistory obj on obj.idA3A5 = ch.idA2A2
left outer join wind.LifeCycleHistory lch on lch.idA2A2=obj.idA3B5
where chm.WTCHGORDERNUMBER = '00039'
and lch.action='Set_State';

[olivierfresse]

This should do the job.

Not tested 🙂

import wt.change2.WTChangeOrder2;
import wt.change2.WTChangeOrder2Master;
import wt.fc.PersistenceHelper;
import wt.fc.QueryResult;
import wt.lifecycle.LifeCycleHistory;
import wt.lifecycle.ObjectHistory;
import wt.query.QueryException;
import wt.query.QuerySpec;
import wt.query.SearchCondition;
import wt.util.WTAttributeNameIfc;
import wt.util.WTException;

/**
 * $Rev::                                                                                                   $:  Version
 * $Author::                                                                                                $:  Dernier modificateur
 * $Date::                                                                                                  $:  Date du dernier commit
 * Url de la version la plus recente
 * $HeadURL$
 **/
public class MyQuery {

    public static void main(String[] args) {
        /*
        select * from wind.WTChangeOrder2 ch left outer join wind.WTChangeOrder2Master chm
        on ch.idA3masterReference = chm.idA2A2
        left outer join wind.ObjectHistory obj on obj.idA3A5 = ch.idA2A2
        left outer join wind.LifeCycleHistory lch on lch.idA2A2=obj.idA3B5
        where chm.WTCHGORDERNUMBER = '00039'
        and lch.action='Set_State';
         */

        try {
            QuerySpec querySpec = new QuerySpec();
            int indChOrder = querySpec.appendClassList(WTChangeOrder2.class,true);
            int indChOrderMaster = querySpec.appendClassList(WTChangeOrder2Master.class,true);
            int indObjHistory = querySpec.appendClassList(ObjectHistory.class,false);
            int indLcHistory = querySpec.appendClassList(LifeCycleHistory.class,false);

            // Joins
            querySpec.appendWhere(new SearchCondition(WTChangeOrder2.class, "masterReference.key.id",WTChangeOrder2Master.class, WTAttributeNameIfc.ID_NAME),new int[]{indChOrderMaster,indChOrder});
            querySpec.appendAnd();
            querySpec.appendWhere(new SearchCondition(ObjectHistory.class, "roleAObjectRef.key.id",WTChangeOrder2.class, WTAttributeNameIfc.ID_NAME),new int[]{indObjHistory,indChOrder});
            querySpec.appendAnd();
            querySpec.appendWhere(new SearchCondition(LifeCycleHistory.class, WTAttributeNameIfc.ID_NAME,ObjectHistory.class, "roleBObjectRef.key.id"),new int[]{indLcHistory,indObjHistory});

            // Select

            querySpec.appendAnd();
            querySpec.appendWhere(new SearchCondition(WTChangeOrder2Master.class, WTChangeOrder2Master.NUMBER, SearchCondition.EQUAL, "00039"),new int[]{indChOrderMaster});
            querySpec.appendAnd();
            querySpec.appendWhere(new SearchCondition(LifeCycleHistory.class, LifeCycleHistory.ACTION, SearchCondition.EQUAL, "Set_State"),new int[]{indChOrderMaster});

            QueryResult queryResult = PersistenceHelper.manager.find(querySpec);
            
            // use queryResult ...
            

        } catch (QueryException e) {
            e.printStackTrace();
        } catch (WTException e) {
            e.printStackTrace();
        }

    }

}