如何获取NativeQuery的结果参数

Question

我想进行这个查询：

Select
    srid,
    substring(srtext
              from (position('DATUM["' in srtext)+7)
              for (position('ID["' in srtext)+2)
                - (position('DATUM["' in srtext)+7))
  from spatial_ref_sys
  order by substring

所以，我创建了一个

Query query = em().createNativeQuery(QUERY)

还要创建一个Srid对象，该对象具有一个Long Srid和一个String sridText。

我需要获取这些值并将其放入列表中。sridText上的子字符串，显然还有长srid中的srid。

请帮忙！！！

Answer 1

找到了答案。首先，我做了一个名为getSrid的plpgsql函数

CREATE OR REPLACE FUNCTION getSRID() returns text[] as $$
declare
 resultado text[];
 consulta cursor for
 Select srid,substring(srtext from (position('DATUM["' in srtext)+7) 
 for(position('ID["' in srtext)+2)
  - (position('DATUM["' in srtext)+7)) as sub
 from spatial_ref_sys;
 cont int;
 i int;
 sridd int;
 srTextt text;
begin
 open consulta;
 i=1;
 EXECUTE 'SELECT COUNT(*) FROM SPATIAL_REF_SYS' into cont;
 resultado := ARRAY[cont];
 while(i<cont) loop
  fetch consulta into sridd,srTextt;
  resultado[i] := sridd;
  resultado[i+1] := srTextt;
  i:=i+2;
 end loop;
 return resultado;
end
$$ language plpgsql; 

正如你所看到的，我在返回时得到了一个Text[]，所以在我的java调用中，我将对象强制转换为String[]：

public List<SridDTO> listaa(){
    int i = 0;
    List<SridDTO> list = new ArrayList<SridDTO>();
    Query query = em().createNativeQuery("Select getSrid()");
    String[] vector = (String[]) query.getSingleResult();
    while(i<vector.length){
        SridDTO sridDTO = new SridDTO(new Long(vector[i]),vector[i+1]);
        i = i + 2;
        list.add(sridDTO);
    }
    return list;
}