Calling Store Procedure From JPA using OpenJPA
Date Feb 25, 2011 @ 11:40
Here is one simple example to call store procedure from JPA using OpenJPA and MySQL. This require MySql database. Database related setup:-
Create a table. mysql> create table test (id varchar(10)); Query OK, 0 rows affected (0.03 sec)Then run first procedure which creates 1000 records in table test
mysql> DELIMITER $$
mysql> DROP PROCEDURE IF EXISTS WhileLoopProc$$
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE PROCEDURE WhileLoopProc()
-> BEGIN
-> DECLARE x INT;
-> DECLARE str CHAR(10);
-> SET x = 1;
-> SET str = '';
-> WHILE x <= 10000 DO
-> SET str = x+1;
-> set x = x+1;
-> insert into test values (str);
->
-> END WHILE;
->
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql>
Run the procedure. After this procedure 10000 record will be created in test table.
mysql> DELIMITER $$
mysql> DROP PROCEDURE IF EXISTS WhileLoopProc2$$
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE PROCEDURE WhileLoopProc2()
-> BEGIN
-> select id from test;
->
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
test 2nd procedure
mysql> call WhileLoopProc2;
This will prepare the database and create procedure.
Java code to call the procedure:-
package com.mycompany.app;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import java.util.List;
public class App
{
public static void main( String[] args )
{
EntityManagerFactory emf = Persistence.createEntityManagerFactory("example-derby");
EntityManager em = emf.createEntityManager() ;
List result = em.createNativeQuery("call WhileLoopProc()").getResultList();
for(Object obj: result)
System.out.println(obj);
}
}
}
Output:-
D:\Softwares\apache-maven-2.2.1\my-app>mvn install
[INFO] Scanning for projects...
[INFO] ------------------------------------------------------------------------
[INFO] Building my-app
[INFO] task-segment: [install]
[INFO] ------------------------------------------------------------------------
[INFO] [resources:resources {execution: default-resources}]
[WARNING] Using platform encoding (Cp1252 actually) to copy filtered resources, i.e. build is platform dependent!
[INFO] Copying 4 resources
[INFO] [compiler:compile {execution: default-compile}]
[INFO] Compiling 1 source file to D:\Softwares\apache-maven-2.2.1\my-app\target\classes
[INFO] [resources:testResources {execution: default-testResources}]
[WARNING] Using platform encoding (Cp1252 actually) to copy filtered resources, i.e. build is platform dependent!
[INFO] skip non existing resourceDirectory D:\Softwares\apache-maven-2.2.1\my-app\src\test\resources
[INFO] [compiler:testCompile {execution: default-testCompile}]
[INFO] Nothing to compile - all classes are up to date
[INFO] [surefire:test {execution: default-test}]
[INFO] Surefire report directory: D:\Softwares\apache-maven-2.2.1\my-app\target\surefire-reports
-------------------------------------------------------
T E S T S
-------------------------------------------------------
Running com.mycompany.app.AppTest
Tests run: 1, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 0.078 sec
Results :
Tests run: 1, Failures: 0, Errors: 0, Skipped: 0
[INFO] Preparing exec:java
[WARNING] Removing: java from forked lifecycle, to prevent recursive invocation.
[INFO] No goals needed for project - skipping
[INFO] [exec:java {execution: default}]
109 INFO [com.mycompany.app.App.main()] openjpa.Runtime - Starting OpenJPA 0.9.6-incubating
312 INFO [com.mycompany.app.App.main()] openjpa.jdbc.JDBC - Using dictionary class "org.apache.openjpa.jdbc.sql.MySQLDiction
656 INFO [com.mycompany.app.App.main()] openjpa.MetaData - Found 0 classes with metadata in 16 milliseconds.
*******************1
*******************2
*******************3
*******************4
*******************5
*******************6
*******************7
*******************8
*******************9
*******************10
*******************11
*******************12
Here is the sample project source.
Run run.bat to execute this program;