使用jpa从子查询中选择时出错

Question

我已尝试获取以下代码的结果：

List<SfsRepDemNote> lis = new ArrayList<SfsRepDemNote>();
            String sql = "select * from ( "
                    + "SELECT"
                    + "    core.SFS_REP_DEM_NOTES_ID,"
                    + "    Rank( ) Over( partition by core.hkid,core.scheme_code,core.demand_note_type Order by core.issue_date desc) rnk "
                    + "FROM"
                    + "    sfs_rep_dem_notes core"
                    + "    "
                    + "WHERE"
                    + "    core.void_ind = 'N'"
                    + "    AND   core.hold_ind = 'N'"
                    + "    AND   trunc(grace_due_date) <= TO_DATE( :dat ,'dd/mm/yyyy')"
                    + "    AND   hkid = :hkid "
                    + "    AND   exists ("
                    + "        SELECT"
                    + "            1"
                    + "        FROM"
                    + "            sfs_rep_dem_note_components com "
                    + "            JOIN sfs_loan_infos loinf ON com.sfs_loan_info_id = loinf.sfs_loan_info_id "
                    + "        WHERE"
                    + "            loinf.loan_ref_num in :ref  "
                    + "            and   core.sfs_rep_dem_notes_id = com.sfs_rep_dem_notes_id"
                    + "            AND   nvl(com.surcharge_unpaid,0) + nvl(com.instal_interest_unpaid,0) + nvl(com.instal_principal_unpaid,0) >= 1"
                    + "    )) where rnk = 1";
            Query query = super.getEntityManager().createQuery(sql);
            query.setParameter("dat", "01/02/2018");// letterDefaultDateFormat.format(defermentCase.getReceiveDate()));
            query.setParameter("hkid", "E80001");// defermentCase.getGaHkid());
            query.setParameter("ref", selectedValues);
            //logger.info(query.getParameterValue(3));

            List<Object[]> list = query.getResultList();
            logger.info("list =" + list.size());
            for (Object[] array : list) {
                //logger.info("id = " + array[0].toString());
                lis.add(findByPK(Long.parseLong(array[0].toString()),
                        SfsRepDemNote.class));
                // logger.info(tmpQuery.getResultList().size());
            }
            logger.info("end");
            return lis;

在某些情况下，我需要在上面使用“In cause”。我尝试使用createSelectNativeQuery()获得结果，但对于param：ref它不能在本机查询中工作，但在ejb ql中，我只是得到了以下错误消息：

null

我累坏了，大家能给我一些建议吗？thx寻求帮助

Answer 1

假设您使用的是纯jpa实现，只需将query query=super.getEntityManager（）.createNativeQuery(sql)；替换为query query=super.getEntityManager（）.createNativeQuery(sql)；。还可以部分编辑本机查询，从loinf.loan_ref_num IN：ref到loinf.loan_ref_num IN(:ref)。