How To call procedure in JPA

Sanjay Singh

·

Follow

1 min read

·

Nov 18, 2022

--

Listen

Share

package com.hays.career.common.repository;

import com.hays.career.common.entity.UserApplication;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;
import java.util.List;


@Repository
public interface UserApplicationRepository extends JpaRepository<UserApplication, Integer> {

    @Transactional
    @Query(nativeQuery = true, value = "call online_audit_master.proc_hc_ad_user_application(:p_user_email,:p_workflow_type,:p_job_id,:p_s3_userdata_url,:p_s3_userdoc_url,:p_domain)")
    UserApplication procHcAdUserApplication(@Param("p_user_email") String p_user_email, @Param("p_workflow_type") String p_workflow_type,
                                            @Param("p_job_id") String p_job_id, @Param("p_s3_userdata_url") String p_s3_userdata_url, @Param("p_s3_userdoc_url") String p_s3_userdoc_url, @Param("p_domain") String p_domain);

    UserApplication findByuserAppIdAndJobId(int id, String jobId);


    @Transactional
    @Modifying
    @Query("update UserApplication u set u.status =:status , u.maxRetryCount =:maxRetryCount where u.jobId =:jobId and u.userAppId =:userAppId ")
    void updateStatusAndMaxRetryCount(@Param("status") String status, @Param("maxRetryCount") int maxRetryCount, @Param("jobId") String jobId, @Param("userAppId") Integer userAppId);

    @Transactional
    @Modifying
    @Query("update UserApplication u set u.status =:status , u.s3UserdataUrl =:s3UserdataUrl ,u.s3UserDocUrl =:s3UserDocUrl ,u.maxRetryCount =:maxRetryCount where u.jobId =:jobId and u.userAppId =:userAppId ")
    void updateS3UrlAndStatusAndMaxRetryCount(@Param("status") String status, @Param("s3UserdataUrl") String s3UserdataUrl, @Param("s3UserDocUrl") String s3UserDocUrl, @Param("jobId")
            String jobId, @Param("userAppId") Integer userAppId, @Param("maxRetryCount") Integer maxRetryCount);

    @Query(value = "select user FROM UserApplication user where user.status='N' and user.maxRetryCount <>0 And user.maxRetryCount <=:maxRetryCount And user.domain_id=:domainId And user.workflowType=:workflowType")
    List<UserApplication> findAllByStatusAndMaxRetryCountGreaterThanEqual(Pageable setLimit, @Param("maxRetryCount") int maxRetryCount, @Param("domainId") int localeId, @Param("workflowType") String workflowType);

    @Query(nativeQuery = true, value = "SELECT locale_id FROM online_system_master.pt_sm_locales where sitelocale=?1")
    int findLocalId(String siteLocal);

    @Query(nativeQuery = true, value = "SELECT domain_id FROM online_system_master.pt_sm_domains where iso_domain_name=?")
    int findDomainId(String domain);

}