Hibernate @SqlInsert w/ PostgreSQL function -- What does my function need to return?

Question

I have a JPA app that is receiving GBs of string data each day. The biggest storage sink is a set of strings that can be pretty long and are repeated very often (maybe 1000 unique strings and I might get tens of thousands of repetitions). To this end I've created a function (needs work, I am pretty bad at postgresql) seen here:

CREATE OR REPLACE FUNCTION dedup_srvice_name(in_id bigint,in_name varchar)
RETURNS SETOF servicename
LANGUAGE plpgsql
AS $function$
DECLARE found_row servicename%ROWTYPE;
BEGIN

SELECT  into found_row  * from servicename where servicename.name = in_name LIMIT      1;
IF found_row IS NULL THEN
    INSERT INTO  servicename VALUES(in_id,in_name) returning id,name;       
    ELSE
    RETURN NEXT found_row;
    RETURN;
    END IF;               

  RETURN;
END;

The idea is attempt to insert the row, unless the string already exists, then return an existing ID to be used by the other side of the relationship (many objects have the same "servicename" in this example.

How does hibernate know what the returend id was? I originally tried to do an "Instead of Insert" type trigger, but realized I can't do these on tables.

What does my function need to return for hibernate to pick up on the ID? Or does it not work that way?

My class is very simple and just needs a @SqlInsert Annotation

public class ServiceName implements Serializable {

private static final long serialVersionUID = 1L;

@Id
private int id;

@Index(name = "name")
private String name;

EDIT

Using the answer below I have done the following:

            SQLQuery query = session.createSQLQuery("select id,name from dedup_srvice_name(?,?)");

            query.setParameter(0, 0).setParameter(theName);

            List<ServiceName> names = query.addEntity(ServiceName.class).list();                
            software.setName(names.get(0));

And this works just fine. Entity reads the ID back in and I can attach this to my relation

gerrytan · Accepted Answer · 2013-07-02 03:56:43Z

1

Assuming you have following entity class:

@Entity
public class MyString {
  private long id;
  private String name;
  // getters & setters..
}

You can use Hibernate native SQL query:

Session session = // obtain hibernate session somehow..
String sql = // sql to call your db function..
List<MyString> mystrings = session.createSQLquery(sql).addEntity(MyString.class).list();

Hibernate will map java properties based on the result set column name. you can use as keyword or decorate the java field with @Column annotation

edited Jul 2, 2013 at 3:56

answered Jul 2, 2013 at 2:38

gerrytan

41.2k12 gold badges88 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yablargo Over a year ago

Thanks for this, will try it out today.

Yablargo Over a year ago

Worked like a champ. Is there a .single() type method instead of .list() at the end of AddEntity?

Collectives™ on Stack Overflow

Hibernate @SqlInsert w/ PostgreSQL function -- What does my function need to return?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related