Call postgresql stored function in java with return?

Question

So I have a stored function called "get_ss_org_struct_ver_id" that returns double precision

CREATE OR REPLACE FUNCTION public.get_ss_org_struct_ver_id(business_id double precision)
 RETURNS double precision
 LANGUAGE plpgsql
AS $function$
   DECLARE
      v_org_struct_ver_id   double precision;
   BEGIN
    
    select internalid into v_org_struct_ver_id from WOT_ORG_STRUCTURE_VER where now() between date_from and date_to and business_group_id = business_id;
      
   
      RETURN v_org_struct_ver_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN NULL;
   END;
$function$
;

The point is, I need to use this stored procedure in my set function:

CoreOrgStructure coreOrgStructure = new CoreOrgStructure();

coreOrgStructure.setOrgVerId(get_ss_org_struct_ver_id(businessGroupId));

I have to get the value of businessGroupId, but I don't know how to call the function. For now I just created that null return class like:

private Long get_ss_org_struct_ver_id(Long businessGroupId) {
        // TODO Auto-generated method stub
        return null;
    }

Can I just call the stored function in this return class?

WildanVEP · Accepted Answer · 2020-12-19 16:20:18Z

1

[EDITED : REALLY SOLVED HERE]

So, in my bean :

coreOrgStructure.setOrgVerId(coreOrgUnitsService.getOrgVerIdByBusinessGroupId(businessGroupId));

and at this getOrgVerIdByBusinessGroupId(businessGroupId) service its goin through to my DAO :

@Override
    public Long getOrgVerIdByBusinessGroupId(Long orgBusinessGroupId) {
        StringBuffer sbQuery = new StringBuffer();
        sbQuery.append(" select get_ss_org_struct_ver_id(:orgBusinessGroupId) ");
        
        Query result = getSession().createSQLQuery(sbQuery.toString());
        result.setLong("orgBusinessGroupId", orgBusinessGroupId);
        
        Number results = (Number) result.uniqueResult();
        if(results == null) {
            results = 0;
        }
        
        return (long) results.intValue();
    }

and, now its really worked! XD

edited Dec 19, 2020 at 16:20

answered Dec 18, 2020 at 10:46

WildanVEP

814 silver badges17 bronze badges

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

3 Comments

SternK Over a year ago

I do not see here any procedure call, just manipulation with StringBuffer and then converting the string to Long.

WildanVEP Over a year ago

aw yes, my bad, still fail, i didnt add this method in add new set, but in edited, and im testing in add not in edited argh -_-

WildanVEP Over a year ago

and now solved! can you up my edited answer?

Collectives™ on Stack Overflow

Call postgresql stored function in java with return?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related