Greetings I write REST services. On Oracle, a database has a function that returns an object of type EXP_TYPE , which is also defined on the database:

  function GET_EXP_TYPE(pID in EXP_TYPE.ID%TYPE) return PKG_TYP.EXP_TYPE pipelined;

My task is to simply call this function on native SQL , without using the Criteria API.

The general idea is clear, but so far it has not been possible to make an intelligible list of the sequence of actions of the type (very approximately):

  1. Query = entityManager.createNamedQuery("TO CALL PROCEDURE", aResultClass);
  2. Прописать result-set-mapping
  3. Прописать приведение STRUCT к тому что мне нужно

Besides, I can not systematically organize it. Will you help by example? Or code.

Listen, I ask the second question of this kind and no answer or comment. Does nobody know anything?

JPA: Hibernate 5.0.10

IDE: JBoss Dev Studio

3 answers 3

You can use hibernate - Native SQL

Example:

 sess.createSQLQuery("SELECT * FROM tableName");

Read more here:

https://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querysql.html

    To call native thrusts, there is just such a method: EntityManager.createNativeQuery (String sql)

    and the function can be substituted into select your_function () from dual

    According to the documentation, you can also call a specific function. https://docs.jboss.org/hibernate/orm/4.1/devguide/en-US/html/ch13.html#sp_query

    For example:

     CREATE OR REPLACE FUNCTION selectAllEmployments RETURN SYS_REFCURSOR AS st_cursor SYS_REFCURSOR; BEGIN OPEN st_cursor FOR SELECT EMPLOYEE,EMPLOYER,STARTDATE,ENDDATE,REGIONCODE,EID,VALUE,CURRENCY FROM EMPLOYMENT; RETURN st_cursor; END;

    Execution results are projected into properties:

     <sql-query name="selectAllEmployees_SP" callable="true"> <return alias="emp" class="Employment"> <return-property name="employee" column="EMPLOYEE"/> <return-property name="employer" column="EMPLOYER"/> <return-property name="startDate" column="STARTDATE"/> <return-property name="endDate" column="ENDDATE"/> <return-property name="regionCode" column="REGIONCODE"/> <return-property name="id" column="EID"/> <return-property name="salary"> <return-column name="VALUE"/> <return-column name="CURRENCY"/> </return-property> </return> { ? = call selectAllEmployments() } </sql-query>

      At once I will tell that in Hibernate or in tooth by foot, I know only Oracle. If you have a pipelined function and you need to get data from it "as from simple SQL", then you just need to wrap it in the view (on the oracle side, of course):

       create or replace view my_view as select * from table(my_pipelined_function());

      Then you will have at your disposal the usual view, for an external observer it is absolutely no different from a simple table. And requests to it can be done whatever.