Hibernate: StoredProcedure with recursive depthsearch: Mapping/Output Problems

Question

I searching for help. I have to map my Postgres 9.4 Database (DB) with Hibernate 5.2, of course it's an study task. The biggest Problem is, that I'm no brain in Hibernate, Java and coding itself XD

It's an SozialNetwork DB. To map the DB with Hibernate doing fine. Now I should map a stored produce. This Produce should find the shortest friendship path between two persons. In Postgres the produce working fine.

That are the relevant DB-Tables: For Person:

CREATE TABLE Person (
 PID            bigint      NOT NULL,
 firstName      varchar(50) DEFAULT NULL,
 lastName       varchar(50)     DEFAULT NULL,
 (some more...)
 PRIMARY KEY (PID)
);

And for the Relationship between to Persons:

CREATE TABLE Person_knows_Person (
 ApID           bigint  NOT NULL,
 BpID           bigint  REFERENCES Person (PID) (..)
 knowsCreationDate  timestamp,
 PRIMARY KEY (ApID,BpID));

And that is the Stored Produce in short:

CREATE OR REPLACE FUNCTION ShortFriendshipPath(pid bigint, pid2 bigint)
  RETURNS TABLE (a_pid bigint, b_pid bigint, depth integer, path2 bigint[], cycle2 boolean)
AS $$
BEGIN
RETURN QUERY 
SELECT * FROM (
   WITH RECURSIVE FriendshipPath(apid, bpid, depth, path, cycle) AS(
   SELECT pkp.apid, pkp.bpid,1,
          ARRAY[pkp.apid], false
     FROM person_knows_person pkp 
     WHERE apid=$1 --OR bpid=$1
 UNION ALL
 SELECT pkp.apid, pkp.bpid, fp.depth+1, path || pkp.apid,
        pkp.apid = ANY(path)
    FROM person_knows_person pkp, FriendshipPath fp
    WHERE pkp.apid = fp.bpid AND NOT cycle)
SELECT * 
   FROM FriendshipPath WHERE bpid=$2) AS OKOK
UNION
SELECT * FROM (
   WITH RECURSIVE FriendshipPath(apid, bpid, depth, path, cycle) AS(
   SELECT pkp.apid, pkp.bpid,1,
   ARRAY[pkp.apid], false
   FROM person_knows_person pkp 
   WHERE apid=$2 --OR bpid=$1
UNION ALL
SELECT pkp.apid, pkp.bpid, fp.depth+1, path || pkp.apid,
       pkp.apid = ANY(path)
   FROM person_knows_person pkp, FriendshipPath fp
   WHERE pkp.apid = fp.bpid AND NOT cycle)
SELECT * 
FROM FriendshipPath WHERE bpid=$1)   AS YOLO
 ORDER BY depth ASC LIMIT 1;
END;
$$ LANGUAGE 'plpgsql' ;

(Sorry for so much code, but it's for both directions, and before I post some copy+reduce misttakes^^) The Call in Postgre for example:

 SELECT * FROM ShortFriendshipPath(10995116277764, 94);

gives me this Output: enter image description here

I use the internet for help and find 3 solutions for calling:

1. direct SQL call
2. call with NamedQuery and
3. map via XML

(fav found here) I faild with all of them XD

I favorite the 1. solution with this call in session:

Session session = HibernateUtility.getSessionfactory().openSession();
  Transaction tx = null;
  try {
    tx = session.beginTransaction();
    System.out.println("Please insert a second PID:"); 
    Scanner scanner = new Scanner(System.in);
    long pid2 = Long.parseLong(scanner.nextLine());
// **Insert of second ID*/
    Query query2 = session.createQuery("FROM " + Person.class.getName() + " WHERE pid = :pid ");
    query2.setParameter("pid", pid2);
    List<Person> listB = ((org.hibernate.Query) query2).list();
    int cnt1 = 0;
    while (cnt1 < listB.size()) {
        Person pers1 = listB.get(cnt1++);
        pid2 = pers1.getPid();
    }
// Query call directly:
    Query querySP = session.createSQLQuery("SELECT a_pid,path2 FROM ShortFriendshipPath(" + pid + "," + pid2 + ")");
    List <Object[]> list = ((org.hibernate.Query) querySP).list();
     for (int i=0; i<list.size();i++){
         Personknowsperson friendship = (Personknowsperson)result.get(i);
     } 
    } catch (Exception e) { (bla..)}
    } finally { (bla....) }

Than I get following Error:

javax.persistence.PersistenceException: org.hibernate.MappingException: No Dialect mapping for JDBC type: 2003 (..blabla...)

I understand why. Because my output is not of type Personknowsperson. I found an answer: that I have to say Hibernate what is the correct formate. And should use 'UserType'. So I try to find some explanations for how I create my UserType. But I found nothing, that I understand. Second Problem: I'm not sure what I should use for the bigint[] (path2). You see I'm expert -.-

Than I got the idea to try the 3.solution. But the first problem I had was where should I write the xml stuff. Because my Output is no table. So I try in the .cfg.xml but than Hibernate say that

Caused by: java.lang.IllegalArgumentException: org.hibernate.internal.util.config.ConfigurationException: Unable to perform unmarshalling at line number -1 and column -1 in RESOURCE hibernate.cfg.xml. Message: cvc-complex-type.2.4.a: Ungültiger Content wurde beginnend mit Element 'sql-query' gefunden. '{some links}' wird erwartet.

translation:

invalid content found starts with 'sql-query'

Now I'm a nervous wreck. And ask you. Could someone explain what I have to do and what I did wrong (for dummies please). If more code need (java classes or something else) please tell me. Critic for coding also welcome, cause I want improve =)

Answer 1

Ok, I'm not an expert in postgressql, not hibernate, nor java. (I'm working with C#, SQL Server, NHibernate so ...) I still try to give you some hints.

You probably can set the types of the columns using addXyz methods:

Query querySP = session
    .createSQLQuery("SELECT * FROM ShortFriendshipPath(...)")
    .addScalar("a_pid", LongType.INSTANCE)
    ...
    // add user type?

You need to create a user type for the array. I don't know how and if you can add it to the query. See this answer here.

You can also add the whole entity:

 Query querySP = session
    .createSQLQuery("SELECT * FROM ShortFriendshipPath(...)")
    .addEntity(Personknowsperson.class)
    ...;

I hope it takes the mapping definition of the corresponding mapping file, where you can specify the user type.

Usually it's much easier to get a flat list of values, I mean a separate row for each different value in the array. Like this:

Instead of

1 | 2 | (3, 4, 5) | false

You would get:

1 | 2 | 3 | false
1 | 2 | 4 | false
1 | 2 | 5 | false

Which seems denormalized, but is actually the way how you build relational data.

In general: use parameters when passing stuff like ids to queries.

 Query querySP = session
    .createSQLQuery("SELECT * FROM ShortFriendshipPath(:pid1, :pid2)")
    .setParameter("pid1", pid1)
    .setParameter("pid2", pid2)
    ...