JPA JPQl or Stored Procedures in MySQL

Question

I am developing an application that uses JPA 2.0 with MySQL 5 database.

I know that JPA hides the calls to the DB queries and makes it underthehood, and all i use is JPQL,

now does this makes the need for stored procedures less ? and how can i use stored procedures with JPA 2.0 ?

possible duplicate of [Calling stored procedure from Java / JPA](http://stackoverflow.com/questions/3572626/calling-stored-procedure-from-java-jpa) — Perception, Mar 22 '12 at 16:35

score 3 · Accepted Answer · edited Oct 07 '13 at 07:43

3

Stored procedures have mostly gone out of favor (you can read some ideas on why here: http://www.codinghorror.com/blog/2004/10/who-needs-stored-procedures-anyways.html).

In general unless you have performance issues or are working with legacy systems, it's advised not to use stored procedures. Object Relational Mapping solutions, such as JPA, are now the industry standard, mostly because they let you write less, and are easier to maintain then other methods (such as using the DAO pattern and JDBC directly).

For example consider adding a new column to a table you are using CRUD (Create,retrieve,update,delete) with:

Using a JPA you add the column to the table and add the property to the class with an annotation, that's it.
With stored procedures, you'll also need to add the parameter to the procedures to update or create a new entity, you'll need to add the column to the insert or update statement inside the procedure, in some cases you'll need to add the column to the select clause inside the procedure.

In regards to using stored procedures, JPA doesn't have support for stored procedures per se, but there are two way you can work around it:

Some JPA implementation (such as EclipseLink or Hibernate) support stored procedures, and you can use their implementation directly , you'll have to see what implementation you are using.
You can use the "native query" feature, that let's you write an sql in the Native database query language, for instance for mySQL you can write something like this:
```
Query query = entityManager.createNativeQuery("{ CALL procedure(?) }");  
query.setParameter(1, parameterName);
```
You need to also remember that the procedure must return a result set and you can't use out parameters.

edited Oct 07 '13 at 07:43

engma

1,849
2
26
55

answered Oct 22 '12 at 06:28

Alon Bar David

1,757
14
15

3

This answer is quite opinionated, citing a linked blog post by Jeff Atwood, which is really not so true in 2013. There are lots of other good reasons to use SP's, other than performance. – Lukas Eder Nov 20 '13 at 10:50
It is considered best practice (especially in Java and Enterprise) to use an ORM, and even though best practices are trends and change, I haven't heard of a resurgence of SPs into favor. Can you provide a link or some info as to the advantages of SPs over ORM or DAOs? Also why is the blog post no longer relevant in 2013? I'm not up to date with advances in SPs, has there been any changes in the field? – Alon Bar David Nov 27 '13 at 19:23
1

There has never been a clear and terminal pro or con decision in favour or against using SPs. I just happen to have worked on large enterprise (Java and non-Java) systems, where SPs were essential. I'm particularly looking at advances in SQL in general, and also PL/SQL and T-SQL and other languages that indicate to me that there is a significant market for these vendors to continue to put development efforts in that area. Besides, Jeff Atwood himself also wrote this, 2 years later: http://www.codinghorror.com/blog/2006/06/object-relational-mapping-is-the-vietnam-of-computer-science.html – Lukas Eder Nov 27 '13 at 19:29
Can you share why they were essential? and I'm not sure what SQL advances are you referring to. And yes, ORMs have issues of their own (especially when dealing with lazy loading or complicated hierarchies), but they are still the most used solution (and for good reasons, IMO). – Alon Bar David Nov 27 '13 at 20:01
*Essential:* Performance, security, architecture (as more than one application accessed that particular business logic). *SQL advances*: SQL:1999 grouping sets, (recursive) common table expressions, SQL:2003 MERGE statement, window functions, SQL:2008 partitioned outer joins, SQL:2011 temporal databases. And XML used to be the "most used solution too". :-) It was a hype, superseded by the next hype. All I'm saying is that it's worth looking at things in the long term. Besides, JPA 2.1 added first-class" "support for SPs. Not beautiful, but still: http://stackoverflow.com/a/13005553/521799 – Lukas Eder Nov 27 '13 at 20:16
entityManager supposrt SPs apparently: http://docs.oracle.com/javaee/7/api/javax/persistence/EntityManager.html#createStoredProcedureQuery%28java.lang.String%29 - maybe edit this part ? – Mr_and_Mrs_D Feb 21 '14 at 21:38

score 1 · Answer 2 · edited Nov 20 '13 at 10:48

if you use EclipseLink JPA, you can use @NamedStoredProcedureQuery annotation for your stored procedures.

Example :

@NamedStoredProcedureQuery(
    name="findAllEmployees", 
    procedureName="EMP_READ_ALL", 
    resultClass=Employee.class, 
    parameters = {
        @StoredProcedureParameter(
            queryParameter="result", 
            name="RESULT_CURSOR",
            direction=Direction.OUT_CURSOR
        )
    }
)
@Entity
public class Employee {
  ...
}

You can reference here.

JPA JPQl or Stored Procedures in MySQL

2 Answers2