Create and use a stored procedure with Play Framework and JPA

Question

I am using Play Framework 1.2.5, and I would like to optimize my SQL queries by creating stored procedures and using them, but I do not know how to do this.

To create a stored procedure using Java code, how do I do this? Also, should I do this in the @OnApplicationStart job so that I am sure that the procedures are created and saved when the application starts?

After that, how can I use my stored procedures? Using which function? How to pass parameters to my procedure? How can I get the result of my procedure? (as a rule, the result will be a SELECT query) And, finally, is it possible to bind the result of my procedure to the model in the playback structure?

I have a lot of questions, but I'm new to stored procedures with a playback platform and JPA, and I would like to make sure that I use them correctly

thanks for the help

Answer 1

I do not know how you should create them. Perhaps the OnApplicationStart method is what you need. Procedures already exist in my environment. We just use Play to call them. To call stored procedures, you should take a look at the Work interface. By doing this, you can follow the instructions in the database.

We created the OracleProcedure base class:

 public class CallOracleProcedure implements Work { private String anonymousPLSQL; private String[] parameters; public CallOracleProcedure(String anonymousPLSQL, String[] parameters) { this.anonymousPLSQL = anonymousPLSQL; this.parameters = parameters.clone(); } /** * Create a JDBC PreparedStatement and then execute the anonymous * PL/SQL procedure. */ @Override public void execute(Connection connection) { PreparedStatement statement = null; try { statement = connection.prepareStatement("begin " + anonymousPLSQL + "; end;"); if (parameters != null) { int i = 1; for (String param : parameters) { statement.setString(i++, param); } } statement.executeUpdate(); } catch (SQLException e) { Logger.error("Error performing anonymous pl/sql statement: '%s', with parameters: '%s' - catched error '%s'", anonymousPLSQL, parameters, e); } finally { if (statement != null) { try { statement.close(); } catch (Exception e) { Logger.error("Error closing statement: %s", e); } } } } } 

For each specific stored procedure, you can extend this class and pass the name and parameters to the constructor via super() :

 public class StoredProcedureCall extends CallOracleProcedure { public StoredProcedureCall(String param) { super("package.storedprocedure(?)", new String[] { orgname }); } } 

In your code, you can call it like this:

 StoredProcedureCall procedure = new StoredProcedureCall("your parameter"); session.doWork(procedure); 

If you need to call a procedure and get the return value, you can use CallableStatement in the execute() method:

 public class ProcedureWithReturnValue implements Work { private final String parameter; private String returnValue = null; public ProcedureWithReturnValue (final String parameter) { this.parameter = parameter; } @Override public void execute(Connection connection) { CallableStatement statement = null; try { statement = connection.prepareCall("begin ? := package.procedure(?); end;"); statement.registerOutParameter(1, OracleTypes.VARCHAR); statement.setString(2, parameter); statement.execute(); returnValue = statement.getString(1); } catch (SQLException e) { Logger.error("Error getting return value - catched error '%s'", e); } } public String getReturnValue() { return returnValue; } } 

Answer 2

To answer one of the questions:

You must create stored procedures directly in the database; they really have nothing to do with your application. When they exist in the database, you can call them from your application. How to create stored procedures depends on the database you are using.

Start with this, create a stored procedure for one of your requirements and make sure it works. Then take the next step and try calling it from your application.

Answer 3

Take a look at evolution ( http://www.playframework.com/documentation/1.2.7/evolutions ) to create stored procedures.