java JDBC中如何在只设置一些参数的情况下调用存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11880517/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How in JDBC can you call a stored procedure when only setting some parameters
提问by JonnyWizz
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?
如果您只想设置一些参数，那么使用 JDBC 调用存储过程的最佳方法是什么？
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:
如果我只是使用 SQL，我可以在 SQL 中按名称设置参数以调用 sproc。例如，如果我有一个带有 9 个参数的存储过程，并且我想设置参数 1,2 和 9，将其余的保留为默认值，我可以运行以下 SQL：
exec my_stored_procedure
@parameter_1 = "ONE",
@parameter_2 = "TWO",
@parameter_9 = "NINE"
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:
使用 JDBC（特别是 jConnect 6.0）时，似乎在使用 CallableStatement 时，您必须通过它们的整数索引而不是它们的名称来设置参数。如果我尝试为上述存储过程创建一个 CallableStatement，有 9 个参数，并且只设置参数 1,2 和 9，如下所示：
myStoredProcedureCall =
sybConn.prepareCall("{call my_stored_procedure (?, ?, ?, ?, ?, ? , ?, ?, ?)}");
myStoredProcedureCall.setString(1, "ONE");
myStoredProcedureCall.setString(2, "TWO");
myStoredProcedureCall.setString(9, "NINE");
ResultSet paramResults = myStoredProcedureCall.executeQuery();
Then I get this SQLException thrown:
然后我得到这个 SQLException 抛出：
*** SQLException caught ***
SQLState: JZ0SA
Message: JZ0SA: Prepared Statement: Input parameter not set, index: 2.
Vendor: 0
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.
对于我正在尝试做的事情的一些背景知识，我需要创建一个从 IBM MQ 流接收产品信息的流程，然后在第三个应用程序中创建一个产品。第 3 方应用程序使用 Sybase 来存储它的数据，并创建一个产品，我需要调用一个具有大约 130 个参数的存储过程。对于我需要创建的产品类型，只需要设置其中的15个左右参数，其余的将保留为默认值。
Options I have considered are:
我考虑过的选项是：
- Creating a custom stored procedure that sets only the values that I need, then calls the 3rd party product sproc.
- Setting default values for all parameters in the Java.
- 创建一个仅设置我需要的值的自定义存储过程，然后调用 3rd 方产品 sproc。
- 在 Java 中为所有参数设置默认值。
Surely there must be an easier way to do this?
当然必须有更简单的方法来做到这一点？
回答by Aaron Digulla
This feature isn't supported by JDBC. You will have to create an SQL string and execute that:
JDBC 不支持此功能。您必须创建一个 SQL 字符串并执行它：
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();
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, Connectionand ResultSetand I could query my application's memory model using the JDBC interface but with JavaScript as query language.
请记住：JDBC 不会尝试理解您发送到数据库的 SQL，除了一些特殊字符，如{}和?。我曾经写过一个JDBC“数据库”，这将接受JavaScript代码段“SQL”：我简单地实现DataSource，Connection而且ResultSet，我可以使用JDBC接口查询我的应用程序的内存模型，但与JavaScript作为查询语言。
回答by Subir Kumar Sao
You can try hard coding null or blank in the String
您可以尝试在字符串中硬编码 null 或空白
"{call my_stored_procedure (?, ?, null, null, null, null , null, null, ?)}"
"{call my_stored_procedure (?, ?, null, null, null, null , null, null, ?)}"
回答by Mohammod Hossain
myStoredProcedureCall.setString(9, "NINE");
in above code index no 9 but it will be 3 because your parameter sequence start from 1 ;
Alhrough you can another sql exception.
I think you should use
myStoredProcedureCall =
sybConn.prepareCall("{call my_stored_procedure (?, ?, null, null, null, null , null, null, ?)}");
myStoredProcedureCall.setString(1, "ONE");
myStoredProcedureCall.setString(2, "TWO");
myStoredProcedureCall.setString(3, "NINE");
ResultSet paramResults = myStoredProcedureCall.executeQuery();
回答by abobjects.com
//abobjects.com
Below works for me
def getIDGEN( sTableName ):
proc = db.prepareCall("{ ? = call DB.dbo.usp_IDGEN_string_v6(?, ?) }");
//abobjects.com 下面对我有用 def getIDGEN(sTableName):
proc = db.prepareCall("{ ? = call DB.dbo.usp_IDGEN_string_v6(?, ?) }");
proc.registerOutParameter(3, types.INTEGER)
proc.setString(2, sTableName)
proc.setInt(3, 0)
proc.execute()
li_RI_ID = proc.getInt(3)
print str(li_RI_ID) + " obtained from usp_IDGEN_string_v6"
return li_RI_ID
my sproc is
我的 sproc 是
create proc usp_IDGEN_string_v6 (@tablename varchar(32) , @ID numeric )
as
declare @seq numeric
declare @nextID numeric
select @nextID=IDGEN_ID from DB_IDGEN where IDGEN_TableName = @tablename
select @seq=@nextID + 1 from DB_IDGEN where IDGEN_Table
Name = @tablename
update DB_IDGEN set IDGEN_ID = @seq where IDGEN_TableName = @tablename
select @ID = @seq
return @ID
