CODEWITHSUNDEEP
javapostgresqlhibernate
WildanVEP
WildanVEP

Reputation: 81

Call postgresql stored function in java with return?

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?

Upvotes: 0

Views: 71

Answers (1)

WildanVEP
WildanVEP

Reputation: 81

[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

Upvotes: 1

Related Questions