0

As far I know, it is impossible to call a stored procedure written in mysql with JPA but what is with common functions (mysql)?

How can I call a stored mysql function with usage of JPA?

Maybe with the EntityManager method createNativeQuery?

(Try to make an example)

Vikrant Kashyap
  • 6,398
  • 3
  • 32
  • 52
EchoCache
  • 555
  • 2
  • 8
  • 22

1 Answers1

1

As far I know, it is impossible to call a stored procedure written in mysql with JPA

No. You can call stored procedures from JPA. See here for an example of how to do it.

How can I call a stored mysql function with usage of JPA?

You can call a MySQL stored function from JPA. JPA 2.1 introduces FUNCTION() function to call any pre-defined (such as CONCAT, SUBSTRING) or user-defined functions.

Say, for example, you've defined your own MySQL function such as shouldGetBonus

CREATE FUNCTION shouldGetBonus (INT department_id, INT project_id) 
RETURNS boolean
DETERMINISTIC
BEGIN 
  DECLARE is_eligible boolean;
  SET is_eligible = -- eligible to get bonus
  RETURN is_eligible;
END$$

and then specify your function in your JPQL query, for example, 

String query = "SELECT DISTINCT e FROM Employee e JOIN e.projects p WHERE FUNCTION('shouldGetBonus', e.department.id, p.id)"
TypedQuery<Employee> emps = entityManager.createQuery(query, Employee.class);
emps.getResultList();
Bunti
  • 1,730
  • 16
  • 20
  • I have found a similar solution. I just used "nativeQuery". However, can I do this: entityManager.find(Employee.class, FUNCTION('LAST_INSERT_ID()')); but that doesnt work ;( – EchoCache May 12 '16 at 16:23
  • No you cannot do this. Read the documentation for [EntityManager#find](http://docs.oracle.com/javaee/7/api/javax/persistence/EntityManager.html#find-java.lang.Class-java.lang.Object-) method. It expects an Object(Java object which has the identifier value) not a JPQL expression. On the other hand you cannot have a JPQL expression like `FUNCTION('LAST_INSERT_ID()')` as it is not valid grammar defined for JPQL. – Bunti May 12 '16 at 20:53
  • If you have some complex logic in `LAST_INSERT_ID` and not just returns the last inserted id, you have to use [Entitymanager#createNativeQuery](http://docs.oracle.com/javaee/7/api/javax/persistence/EntityManager.html#createNativeQuery-java.lang.String-java.lang.Class-) or one of its overloads to call the stored function. – Bunti May 12 '16 at 20:58