英文:
Best way to execute procedure with JPA StoredProcedureQuery in a for loop
问题
在for循环内执行存储过程的最佳方法是什么？
这是我目前的代码（这个“for”在将来会稍有不同，但基本相同）：
for(int i=0; i<300; i++){
StoredProcedureQuery query =
this.entityManager.createStoredProcedureQuery("testProcedure");
query.registerStoredProcedureParameter(1, String.class, ParameterMode.IN);
query.registerStoredProcedureParameter(2, String.class, ParameterMode.IN);
query.registerStoredProcedureParameter(3, String.class, ParameterMode.IN);
query.setParameter(1, "DF");
query.setParameter(2, i);
query.setParameter(3, "F");
query.execute();
}
这样做是否可行？我需要为一个需要通过列表（该列表是对表格的选择，并且有多条记录）进行迭代的项目执行存储过程。
我尝试过通过在查询中使用select声明一个带有游标的方式，并在其上进行迭代，但由于某些原因，存储过程没有被执行，类似于下面的代码：
DECLARE
CURSOR cur IS select s_number from testTable where s_number in ( "一些数字（列表）");
BEGIN
FOR c IN cur LOOP
testProcedure('DF', c.s_number, 'F');
END LOOP;
commit;
END;
我不能在数据库中使用这个游标来创建一个存储过程，因为我们需要将这个执行操作迁移到Java中。
非常感谢您的帮助！
英文:
What is the best way of executing a procedure inside a for loop?
This is what i have (this "for" is going to be slightly different in a future, but it's basically the same):
for(int i=0; i<300; i++){
StoredProcedureQuery query =
this.entityManager.createStoredProcedureQuery("testProcedure");
query.registerStoredProcedureParameter(1, String.class, ParameterMode.IN);
query.registerStoredProcedureParameter(2, String.class, ParameterMode.IN);
query.registerStoredProcedureParameter(3, String.class, ParameterMode.IN);
query.setParameter(1, "DF");
query.setParameter(2, i);
query.setParameter(3, "F");
query.execute();
}
Is this recommendable or not? I need to do this for a proyect which need to execute a SP iterating through a list (which is a select to a table and has multiple records).
I tried doing this declarating a cursor with the select in a Query (native query) and iterate over it, but for some reason the sp was not getting executed, something like this:
DECLARE
CURSOR cur IS select s_number from testTable where s_number in ( “some numbers (List)”);
BEGIN
FOR c IN cur LOOP
testProcedure('DF', c.s_number, 'F');
END LOOP;
commit;
END;
And i can't make a SP in the DB with this cursor because we need to migrate this execution to Java.
Any help is appreciated!!
答案1
得分: 1
我认为您可以将存储过程作为批处理执行，但您需要在纯jdbc中执行此操作：
Connection con = this.entityManager.unwrap(Session.class).connection();
CallableStatement stmt = con.prepareCall("{ call testProcedure(?,?,?) }");
for(int i=0; i<300; i++){
stmt.setString(1, "DF");
stmt.setInt(2, i);
stmt.setString(3, "F");
stmt.addBatch();
}
int [] updatesCount = stmt.executeBatch();
// 验证更新计数是否全部 >= 0
但是为什么不在数据库中的存储过程中执行这个操作呢？如果您需要在执行过程后将消息放入队列（例如），您可以使存储过程填充一个临时表格以保存结果，从而使得在您的Java代码中执行此操作成为可能。
英文:
I think you can execute the stored procedure as a batch, but you need to do this in plain jdbc:
Connection con = this.entityManager.unwrap(Session.class).connection();
CallableStatement stmt = con.prepareCall("{ call testProcedure(?,?,?) }");
for(int i=0; i<300; i++){
stmt.setString(1, "DF");
stmt.setInt(2, i);
stmt.setString(3, "F");
stmt.addBatch();
}
int [] updatesCount = stmt.executeBatch();
// Verify if all updatesCount is >= 0
But why not to do this in a procedure in database? If you need to put messages in a queue (example) after the process is executed, you can make the stored procedure fill a temporary table with the results for make it possible to do this in your java code.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论