CODEWITHSUNDEEP
oracle-databasehibernatejpa
Tot
Tot

Reputation: 207

How to call Oracle function in Hibernate

I would like to call an Oracle function which checks if the user is valid or not in my Java Bean. The function has the following code:

  FUNCTION valid_user1(p_username IN VARCHAR2, p_password IN VARCHAR2)
    RETURN BOOLEAN AS
    v_dummy VARCHAR2(1);
  BEGIN
    BEGIN
      SELECT '1'
        INTO v_dummy
        FROM t_user
       WHERE username = UPPER(p_username)
         AND password = get_hash(p_username, p_password);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20000,
                                'Invalid username/pass!');
    END;
    RETURN TRUE;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN FALSE;
  END;
END;

I have never called a function in Hibernate and i don't know how to proceed. I read some articles where it was written that is better to use procedure than functions but i did not understand why. Please give me some guidelines how to execute the function using EntityManager. Thanks in advance!

Upvotes: 1

Views: 1990

Answers (1)

Manjunath
Manjunath

Reputation: 1685

Try this:- 

 Boolean valid = (Boolean)entityManager.createNativeQuery("SELECT valid_user1(?1,?2) FROM DUAL")
                          .setParameter(1, userName)
                          .setParameter(2, pwd)
                          .getSingleResult();

Upvotes: 1

Related Questions