<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
             http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"
             version="2.1">

    <persistence-unit name="orientsoftware">
        <description>
            Persistence unit for Hibernate User Guide
        </description>

        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>

        <class>org.hibernate.documentation.userguide.Document</class>

        <properties>
            <property name="jakarta.persistence.jdbc.driver" value="org.postgresql.Driver" />

            <property name="jakarta.persistence.jdbc.url" value="jdbc:postgresql://localhost:5432/test" />

            <property name="jakarta.persistence.jdbc.user" value="postgres" />

            <property name="jakarta.persistence.jdbc.password" value="postgres" />

            <property name="hibernate.show_sql" value="true" />

            <property name="hibernate.hbm2ddl.auto" value="create" />
            
            <property name="hibernate.default_schema" value="test"/>
            
        </properties>

    </persistence-unit>

</persistence>

package hibernate.models;

import jakarta.persistence.Access;
import jakarta.persistence.AccessType;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
import lombok.AccessLevel;
import lombok.Getter;
import lombok.Setter;
import lombok.experimental.FieldDefaults;

@Entity
@Table(name = "hibernate", schema = "test")
@Access(AccessType.FIELD)
@FieldDefaults(level = AccessLevel.PRIVATE)
@Getter
@Setter
public class Hibernate {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Column(name = "id", nullable = false)
	Long id;
	
	@Column(name = "name")
	String name;
}

<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.orientsoftware</groupId>
	<artifactId>hibernate</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<properties>
		<hib.version>6.2.4.Final</hib.version>
	</properties>

	<build>
		<finalName>hibernate</finalName>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.8.1</version>
				<configuration>
					<release>17</release>
				</configuration>
			</plugin>
		</plugins>
	</build>

	<dependencies>
		<dependency>
			<groupId>org.hibernate.orm</groupId>
			<artifactId>hibernate-core</artifactId>
			<version>${hib.version}</version>
		</dependency>
		<dependency>
			<groupId>org.hibernate.orm</groupId>
			<artifactId>hibernate-c3p0</artifactId>
			<version>${hib.version}</version>
		</dependency>

		<dependency>
			<groupId>org.hibernate.orm</groupId>
			<artifactId>hibernate-envers</artifactId>
			<version>${hib.version}</version>
		</dependency>

		<!--
		https://mvnrepository.com/artifact/org.hibernate.validator/hibernate-validator -->
		<dependency>
			<groupId>org.hibernate.validator</groupId>
			<artifactId>hibernate-validator</artifactId>
			<version>8.0.0.Final</version>
		</dependency>
		
		 <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<version>1.18.24</version>
		</dependency>
		
		<dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.5.4</version>
        </dependency>
		
	</dependencies>
</project>

package hibernate;

import java.util.Iterator;
import java.util.List;

import org.hibernate.Session;
import org.hibernate.procedure.ProcedureCall;
import org.hibernate.result.Output;
import org.hibernate.result.ResultSetOutput;

import hibernate.models.Hibernate;
import jakarta.persistence.EntityManager;
import jakarta.persistence.EntityManagerFactory;
import jakarta.persistence.ParameterMode;
import jakarta.persistence.Persistence;

public class App {

	public static void main(String[] args) {
		
		EntityManagerFactory factory = Persistence.createEntityManagerFactory("orientsoftware");
		EntityManager entityManager = factory.createEntityManager();
		
		entityManager.find(Hibernate.class, 1l);
		
		Session session = entityManager.unwrap(Session.class);
		ProcedureCall sp = session.createStoredProcedureCall("test.test_function");
		sp.registerParameter(1, void.class, ParameterMode.REF_CURSOR);
		sp.registerParameter(2, String.class, ParameterMode.IN);
		sp.setParameter(2, "name");
		
		Output output = sp.getOutputs().getCurrent();
		List<Object[]> resultList = ((ResultSetOutput) output).getResultList();
		
		Iterator itr = resultList.iterator();
		System.out.println(itr.toString());
		
		session.close();
		entityManager.close();
	}
}

CREATE OR REPLACE FUNCTION test.test_function(
	param text)
    RETURNS refcursor
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$ 
	DECLARE
		resultSet REFCURSOR;
    BEGIN
     	select * from test.hibernate
		where name like param;
		RETURN resultSet;
    END;	
$BODY$;

ALTER FUNCTION test.test_function(text)
    OWNER TO postgres;