IllegalStateException: Query argument not found in the list of parameters provided during query execution

Support
#1

Hello Team,

I have a datasource defined on a browse screen with a query that contains a UNION construct from the same entity as following:

    <dsContext>
        <groupDatasource id="employeesGroupsDs"
                         class="com.company.vp.entity.EmployeesGroup"
                         view="employeesGroup-view-setup">
            <query>
                <![CDATA[select e from vp$EmployeesGroup e]]>
            </query>
        </groupDatasource>
        <groupDatasource id="workSchedulesDs"
                         class="com.company.vp.entity.WorkSchedule"
                         view="workSchedule-view_employeesGroup">
            <query>
                <![CDATA[        select e from vp$WorkSchedule e where e.employeesGroup.id = :ds$employeesGroupsDs and (current_date between e.startDate and coalesce(e.endDate, current_date))
        union
        select e from vp$WorkSchedule e where e.country.id = :ds$employeesGroupsDs.country.id and (current_date between e.startDate and coalesce(e.endDate, current_date))]]>
            </query>
        </groupDatasource>
    </dsContext>

while opening the screen and upon selecting a parent record it shows the following error:
IllegalStateException: Query argument ds_employeesGroupsDs_country_id not found in the list of parameters provided during query execution.

when i tried every single select in the union separately it works, that makes me think the problem is with the UNION, but eclipse link should be supporting unions, right?

#2

After A lot of debugging for the reason, it seems that only one reference to the related DS can be used -even multiple times-, so for example i can use the following and it will work fine but of course it will retrieve wrong records:

select e from vp$WorkSchedule e where e.employeesGroup.id = :ds$employeesGroupsDs.id and (current_date between e.startDate and coalesce(e.endDate, current_date))
        union
select e2 from vp$WorkSchedule e2 where e2.country.id = :ds$employeesGroupsDs.id and (current_date between e2.startDate and coalesce(e2.endDate, current_date))

so i can either refer to :ds$employeesGroupsDs.id and use the same reference as many times in my query as i want or i can use :ds$employeesGroupsDs.country.id and use the same reference as many times as i want, but if used both as i want to it results in only the first one in the bind list.

i think this is a bug. i appreciate if any one can help me with an alternative, my goal is to select a single record based on the unions so in my original query if the first select in the union retrieved a record then it will be showed otherwise i will show the second record retrieved from the second select of the union.

#3

I found an alternative, i implemented a custom datasource and run my queries one by one and append the results to a List and return it to the screen.

#4

Hi Tarek,
We face same issue here, could you share how to custom the datasource to make it select from union?
Thanks a lot~

#5

Hi Anjingjing,

The way i have done it is as following:

  1. create a service with a function that returns a list of the type you want to display, the service will be responsible to get the information you want from the database for example:
@Service(WorkScheduleService.NAME)
public class WorkScheduleServiceBean implements WorkScheduleService {
    @Inject
    Persistence persistence;

    @Inject
    VPDateUtilsService vpDateUtilsService;

    //returns specific work schedules linked to employees group or empty list if none found
    @Transactional(readOnly = true)
    @Override
    public List<WorkSchedule> getEmployeesGroupWorkSchedules(EmployeesGroup employeesGroup){
        EntityManager entityManager = persistence.getEntityManager();
        TypedQuery<WorkSchedule> query = entityManager.createQuery("select wrks from vp$WorkSchedule wrks where wrks.employeesGroup.id = :employeesGroupId", WorkSchedule.class);
        query.setViewName("workSchedule-view_employeesGroup");
        query.setParameter("employeesGroupId", employeesGroup.getId());
        return query.getResultList();
    }

    @Transactional(readOnly = true)
    @Override
    public List<WorkSchedule> getEmployeeWorkSchedules(Employee employee) {
        EntityManager entityManager = persistence.getEntityManager();
        TypedQuery<WorkSchedule> query = entityManager.createQuery("select wrks from vp$WorkSchedule wrks where wrks.employee.id = :employeeId", WorkSchedule.class);
        query.setViewName("workSchedule-view_employee");
        query.setParameter("employeeId", employee.getId());
        return query.getResultList();
    }

    //returns sector work schedules linked to employees group sector or empty list if none found
    @Transactional(readOnly = true)
    @Override
    public List<WorkSchedule> getSectorWorkSchedules(EmployeesGroup employeesGroup){
        EntityManager entityManager = persistence.getEntityManager();
        TypedQuery<WorkSchedule> query = entityManager.createQuery("select wrks from vp$WorkSchedule wrks where wrks.employeesGroup is null and wrks.sector.id = :employeesGroupSectorId", WorkSchedule.class);
        query.setViewName("workSchedule-view_employeesGroup");
        query.setParameter("employeesGroupSectorId", employeesGroup.getSector().getId());
        return query.getResultList();
    }

    @Transactional(readOnly = true)
    @Override
    public List<WorkSchedule> getAllWorkSchedules(EmployeesGroup employeesGroup) {
        List<WorkSchedule> list = new ArrayList<>();
        list.addAll(getEmployeesGroupWorkSchedules(employeesGroup));
        list.addAll(getSectorWorkSchedules(employeesGroup));
        return list;
    }

    @Transactional(readOnly = true)
    @Override
    public List<WorkSchedule> getAllWorkSchedules(Employee employee) {
        List<WorkSchedule> list = new ArrayList<>();
        list.addAll(getEmployeesGroupWorkSchedules(employee.getEmployeesGroup()));
        list.addAll(getSectorWorkSchedules(employee.getEmployeesGroup()));
        list.addAll(getEmployeeWorkSchedules(employee));
        return list;
    }
}

have a look at getAllWorkSchedules function where the List.addAll is the alternative of the SQL union.

  1. create a custom data source in the same package as your screen (or a difference package depends on how you want to organize your code), for example:
public class WorkScheduleDatasource extends CustomGroupDatasource<WorkSchedule, UUID> {
    @Override
    protected Collection<WorkSchedule> getEntities(Map<String, Object> params) {
        WorkScheduleService workScheduleService = AppBeans.get(WorkScheduleService.class);

        if(params.containsKey("employeesGroup")) {
            return workScheduleService.getAllWorkSchedules((EmployeesGroup) params.get("employeesGroup"));
        }
        else{
            return null;
        }
    }
}
  1. now, you have a custom datasource that can be incorporated into your screen (as group datasource not a collection datasource), for example:

        <groupDatasource id="workSchedulesDs"
                         allowCommit="false"
                         class="com.company.vp.entity.WorkSchedule"
                         datasourceClass="com.company.vp.web.employeesgroup.WorkScheduleDatasource"/>
  1. finally you need to pass a parameter from the browse screen to your datasource refresh method, for example:
public class EmployeesGroupBrowse extends AbstractLookup {

    @Inject
    private GroupDatasource<EmployeesGroup, UUID> employeesGroupsDs; //this is the master data source that will refresh the custom datasource when it's selected value changes

    @Inject
    private WorkScheduleDatasource workSchedulesDs;

   @Override
    public void init(Map<String, Object> params) {

        employeesGroupsDs.addItemChangeListener(event -> {
            workSchedulesDs.refresh(ParamsMap.of("employeesGroup", employeesGroupsDs.getItem()));
        });
}
}
1 Like
#6

Hi @tarek.ghonamy

Thank you so much for providing detailed solution! This perfectly resolved our problem.