postgresql - 如何获取NativeQuery的结果参数

我想问一下：

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对象，该对象具有长Srid和字符串sridText。
我需要得到这些值并将其放入一个列表中。
sridText上的子字符串，显然srid变成了长srid。
请帮忙！!!

最佳答案

找到了答案。首先，我做了一个名为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;

如您所见，我在返回时得到了一个文本[]，因此在我的java调用中，我将对象转换为字符串[]：

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;
}