Установка списочного параметра в нативный запрос

Вопросы и проблемы
#1

Добрый день.
Не получается установить в качестве параметра нативного запроса список UUID.

val uuids = listOf(UUID.fromString("8f7062d1-70f7-9cd5-4fdc-f828291587ad"), UUID.fromString("21977d4c-06f7-e1b1-9729-5ed525b24578"))

    persistence.runInTransaction { em: EntityManager ->
        result = em.createNativeQuery("select \n" +
                "sum(COALESCE(detail.containers_amount_plan, 0)) as containers_plan,\n" +
                "sum(COALESCE(detail.containers_amount_fact, 0)) as containers_fact,\n" +
                "sum(COALESCE(detail.containers_amount_picked_up, 0)) as containers_piked_up,\n" +
                "sum(COALESCE(detail.containers_amount_empty, 0)) as containers_empty,\n" +
                "sum(COALESCE(detail.containers_amount_plan, 0))  - sum(COALESCE(detail.containers_amount_fact, 0)) as containers_not_removed,\n" +
                "(cast(sum(COALESCE(detail.containers_amount_fact, 0)) as float) + cast(sum(COALESCE(detail.containers_amount_empty, 0)) as float))/ NULLIF(sum(COALESCE(detail.containers_amount_plan, 0)), 0) * 100  as containers_percentage_of_completion, \n" +
                "count(detail.id) as ca_plan, \n" +
                "sum(case when detail.report_id is not null then 1 else 0 end) as ca_fact, \n" +
                "sum(case when COALESCE(report.containers_amount_empty, 0) = COALESCE(detail.containers_amount_plan, 0) then 1 else 0 end) as ca_empty, \n" +
                "(count(detail.id) - sum(case when detail.report_id is not null then 1 else 0 end) + sum(case when COALESCE(report.containers_amount_empty, 0) = COALESCE(detail.containers_amount_plan, 0) then 1 else 0 end)) as ca_not_removed, \n" +
                "cast((sum(case when detail.report_id is not null then 1 else 0 end) + sum(case when COALESCE(report.containers_amount_empty, 0) = COALESCE(detail.containers_amount_plan, 0) then 1 else 0 end)) as float) / count(detail.id) * 100 as ca_percentage_of_completion \n" +
                "\n" +
                "from dispatching_garbage_removal_task_details detail \n" +
                "join dispatching_garbage_removal_task task on task.id = detail.garbage_removal_task_id \n" +
                "join dispatching_container_area c on c.id = detail.container_area_id \n" +
                "join dispatching_garbage_removal_region_container_area_link ca_link on detail.container_area_id = ca_link.container_area_id \n" +
                "join dispatching_garbage_removal_region region on ca_link.garbage_removal_region_id = region.id \n" +
                "join dispatching_employee employee on task.driver_id = employee.id \n" +
                "left join dispatching_person person on employee.person_id = person.id \n" +
                "left join dispatching_vehicle vehicle on task.vehicle_id = vehicle.id \n" +
                "left join dispatching_vehicle_assembly assembly on vehicle.assembly_id = assembly.id \n" +
                "left join dispatching_vehicle_garbage_container vehicle_container on assembly.container_id = vehicle_container.id \n" +
                "left join dispatching_garbage_load_type load_type_id on vehicle_container.load_type_id = load_type_id.id \n" +
                "left join dispatching_vehicle_number number on vehicle.number_id = number.id \n" +
                "left join dispatching_garbage_removal_report_from_driver report on detail.report_id = report.id \n" +
                "where task.start_date between #dateFrom and #dateTo and region.id in #regionIds\n" +
                "\n")
                .setParameter("dateFrom", dateFrom)
                .setParameter("dateTo", dateTo)
                .setParameter("regionIds", uuids)
                .resultList
    }

Выбрасывает исключение:

11:45:36.829 ERROR c.h.cuba.core.sys.ServiceInterceptor - Exception:
javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.3.7-cuba): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: Can’t infer the SQL type to use for an instance of java.util.Arrays$ArrayList. Use setObject() with an explicit Types value to specify the type to use.
Error Code: 0

error.log (50.8 КБ)

Пробовал также поменять тип параметра на set
val uuids = setOf(UUID.fromString("8f7062d1-70f7-9cd5-4fdc-f828291587ad"), UUID.fromString("21977d4c-06f7-e1b1-9729-5ed525b24578"))
Аналогичная ошибка:

11:52:12.823 ERROR c.h.cuba.core.sys.ServiceInterceptor - Exception:
javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.3.7-cuba): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: Can’t infer the SQL type to use for an instance of java.util.LinkedHashSet. Use setObject() with an explicit Types value to specify the type to use.
Error Code: 0

Если убрать условие " and region.id in #regionIds" и соответствующую установку параметра .setParameter(“regionIds”, uuids), данный код работает без ошибок

Подскажите, в чем моя ошибка и как исправить?

#2

Посмотрите этот ответ

.

1 симпатия