在仅设置某些参数时,如何在JDBC中调用存储过程 [英] How in JDBC can you call a stored procedure when only setting some parameters
问题描述
如果您只想设置一些参数,使用JDBC调用存储过程的最佳方法是什么?
What is the best way to make a call to a stored procedure using JDBC if you only want to set some of the parameters?
如果我只是使用SQL ,我可以在SQL中按名称设置参数,以调用sproc。例如。如果我有一个包含9个参数的存储过程,并且我想设置参数1,2和9,其余的保留其默认值,我可以运行此SQL:
If I was just using SQL, I could set the paramerers by name in the SQL to call the sproc. E.g. if I have a stored procedure with nine parameters and I wanted to set parameters 1,2 and 9, leaving the rest to their default values, I can run this SQL:
exec my_stored_procedure
@parameter_1 = "ONE",
@parameter_2 = "TWO",
@parameter_9 = "NINE"
使用JDBC(特别是jConnect 6.0),似乎在使用CallableStatement时,必须通过整数索引设置参数,而不是他们的名字。如果我尝试为上述存储过程创建一个CallableStatement,使用9个参数,并且只设置参数1,2和9,如下所示:
Using JDBC (Specifically jConnect 6.0), it seems that when using a CallableStatement, you have to set the parameters by their integer index, not their name. If I try the to create a CallableStatement for the above stored procedure, with 9 parameters, and only set parameters 1,2 and 9, like this:
myStoredProcedureCall =
sybConn.prepareCall("{call my_stored_procedure (?, ?, ?, ?, ?, ? , ?, ?, ?)}");
myStoredProcedureCall.setString(1, "ONE");
myStoredProcedureCall.setString(2, "TWO");
myStoredProcedureCall.setString(9, "NINE");
ResultSet paramResults = myStoredProcedureCall.executeQuery();
然后我抛出这个SQLException:
Then I get this SQLException thrown:
*** SQLException caught ***
SQLState: JZ0SA
Message: JZ0SA: Prepared Statement: Input parameter not set, index: 2.
Vendor: 0
对于我想要做的一些背景,我需要创建一个接收的进程有关IBM MQ流中产品的信息,然后在第三个应用程序中创建产品。第三方应用程序使用Sybase来存储它的数据,并创建一个产品,我需要调用一个包含大约130个参数的存储过程。对于我需要创建的产品类型,只需要设置其中的大约15个参数,其余参数将保留为默认值。
For some background on what I am trying to do, I need to create a process that receives information about products from a IBM MQ stream, and then creates a product in a 3rd application. The 3rd party application uses Sybase to store it's data, and to create a product I need to call a stored procedure that has about 130 parameters. For the type of product I need to create, only about 15 of these parameters need to be set, the rest will be left to the default values.
我考虑过的选项是:
- 创建一个自定义存储过程,仅设置我需要的值,然后调用第三方产品sproc。
- 为Java中的所有参数设置默认值。
当然必须有一种更简单的方法来做到这一点?
Surely there must be an easier way to do this?
推荐答案
JDBC不支持此功能。您必须创建一个SQL字符串并执行该字符串:
This feature isn't supported by JDBC. You will have to create an SQL string and execute that:
String sql = "exec my_stored_procedure\n@parameter_1 = ?,\n@parameter_2 = ?,\n@parameter_9 = ?";
PreparedStatement stmt = ...
stmt.setString( 1, "ONE" );
stmt.setString( 2, "TWO" );
stmt.setString( 3, "NINE" );
stmt.execute();
请记住:JDBC不会尝试理解您发送到数据库的SQL除外一些特殊字符,如 {}
和?
。我曾经写过一个JDBC数据库,它接受JavaScript代码片段作为SQL:我只是实现 DataSource
,连接
和 ResultSet
我可以使用JDBC接口查询我的应用程序的内存模型,但使用JavaScript作为查询语言。
Remember: JDBC doesn't try to understand the SQL that you're sending to the database except for some special characters like {}
and ?
. I once wrote a JDBC "database" which would accept JavaScript snippets as "SQL": I simply implemented DataSource
, Connection
and ResultSet
and I could query my application's memory model using the JDBC interface but with JavaScript as query language.
这篇关于在仅设置某些参数时,如何在JDBC中调用存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!