通过Play Framework和JPA创建和使用存储过程 [英] Create and use a stored procedure with Play Framework and JPA

问题描述

我正在使用Play框架1.2.5，我想通过创建存储过程并使用它们来优化我的SQL查询，但是我不知道该怎么做.

I'm using Play framework 1.2.5 and I would like to optimize my SQL queries by creating stored procedures and using them but I don't know how to do.

要通过Java代码创建存储过程，该怎么办?另外，我应该在@OnApplicationStart作业中执行此操作，以便确保在应用程序启动时创建并存储了这些过程吗?

To create the stored procedure via the Java code how should I do ? Also, should I do it in an @OnApplicationStart job so that I'm sure the procedures are created and stored when the application starts ?

之后，如何使用存储过程?使用哪个功能?如何将参数传递给过程?如何获取程序结果? (通常，结果将是一个SELECT查询)，最后，是否可以将我的过程的结果绑定到play框架中的模型上?

After, how can I use my stored procedures ? Using which function ? How can I pass the parameters to my procedure ? How can I retrieve the result of my procedure ? (generally the result will be a SELECT query) And finally, is it possible to bind the result of my procedure to a model in the play framework ?

我有很多问题，但是我对使用Play框架和JPA的存储过程是陌生的，我想确保自己正确使用了它们

I have a lot of questions but I'm new to stored procedures with play framework and JPA and I would like to be sure I'm using them correctly

谢谢您的帮助

推荐答案

我不知道您应该如何创建它们.也许您需要的是OnApplicationStart方法.在我的环境中，程序已经到位.我们只是使用Play来调用它们.要调用存储过程，应查看Work接口.通过实现此功能，您可以在数据库中执行语句.

I don't know how you should create them. Perhaps the OnApplicationStart method is what you need. In my environment the procedures are already in place. We just use Play to invoke them. To invoke stored procedures, you should take a look at the Work interface. By implementing this you can execute statements in the database.

我们创建了一个基本的OracleProcedure类:

We've created a basic OracleProcedure 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);
                }
            }
        }
    }
}

对于每个特定的存储过程，您可以扩展此类，并通过super()将名称和参数传递给构造函数:

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 then call it like this:

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

如果需要调用过程并检索返回值，则可以在execute()方法中使用CallableStatement:

If you need to call a procedure and retrieve a return value you can use a 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;
    }
}

这篇关于通过Play Framework和JPA创建和使用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！