ResultSet NullPointerException [英] ResultSet NullPointerException
问题描述
我有一个存储过程,我想从JDBC调用它,我在行中得到了空指针异常.
while (restuls.next()) {
我的代码是:
Connection con = Database.getConnection();
CallableStatement callableStatement = null;
try {
String storedProcedure = "{call getAllCustomerAddresses(?,?,?,?,?,?,?)}";
callableStatement = con.prepareCall(storedProcedure);
callableStatement.setInt(1, this.getID());
callableStatement.registerOutParameter(2,
java.sql.Types.INTEGER);
callableStatement.registerOutParameter(3,
java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(4,
java.sql.Types.INTEGER);
callableStatement.registerOutParameter(5,
java.sql.Types.INTEGER);
callableStatement.registerOutParameter(6,
java.sql.Types.INTEGER);
callableStatement.registerOutParameter(7,
java.sql.Types.VARCHAR);
callableStatement.execute();
System.out.println(callableStatement.getInt(2));
System.out.println(callableStatement.getString(3));
System.out.println(callableStatement.getInt(4));
System.out.println(callableStatement.getInt(5));
System.out.println(callableStatement.getInt(6));
System.out.println(callableStatement.getString(7));
ResultSet restuls = callableStatement.getResultSet();
while (restuls.next()) {
int addressID = restuls.getInt(2);
String label = restuls.getString(3);
int regionID = restuls.getInt(4);
int areaID = restuls.getInt(5);
int cityID = restuls.getInt(6);
String description = restuls.getString(7);
this.addresses.add(new CustomerAddressImpl(this, label,
description, RegionImpl.getInstance(regionID),
AreaImpl.getInstance(areaID), CityImpl
.getInstance(cityID), addressID));
}
查看代码,System.out.println
正在工作,并且正在从数据库中打印正确的值,所以请问为什么结果集为null ??
另一件事,我必须使用结果集,因为存储过程返回许多行.
我真的很困惑为什么我可以打印正确的值但结果集为空
预先感谢
编辑
如果您想给您存储过程,请告诉我
存储过程
ALTER PROCEDURE [dbo].getAllCustomerAddresses(
@customerID INT,
@addressID INT OUTPUT,
@label VARCHAR(200) OUTPUT,
@regionID INT OUTPUT,
@areaID INT OUTPUT,
@cityID INT OUTPUT,
@description TEXT OUTPUT
)
AS
SET NOCOUNT Off;
SELECT @addressID = [ID],
@label = [label],
@regionID = [regionID],
@areaID = [areaID],
@cityID = [cityID],
@description = [description]
FROM Customer_Address
WHERE customerID = @customerID
您的存储过程实际上不会生成ResultSet
,因为您使用的是输出参数(不是100%的确定,我没有SQL Server方便测试).
您可能只需要调用 CallableStatement.getObject(String)
(或类型特定的吸气剂)来获取值.如果要作为ResultSet
处理,则不应在存储过程中使用输出参数,而应在不分配输出参数的情况下将存储过程作为选择写入.这将从存储过程中创建一个结果集
另一种可能是您的存储过程在返回结果集之前先返回一个或多个更新计数. execute()
指示第一个结果是更新计数还是ResultSet
.您将需要重复调用 getMoreResults()
和 SET NOCOUNT OFF
,该信号指示还要返回要更新(我相信选择)计数的SQL Server(或Sybase),您可能要尝试使用SET NOCOUNT ON
.
您还可以尝试像这样处理execute()
的结果,以找出在结果集之前是否确实存在多个更新计数等:
boolean result = pstmt.execute();
while(true)
if (result) {
ResultSet rs = pstmt.getResultSet();
// Do something with resultset ...
} else {
int updateCount = pstmt.getUpdateCount();
if (updateCount == -1) {
// no more results
break;
}
// Do something with update count ...
}
result = pstmt.getMoreResults();
}
另请参见 Java SQL:Statement.hasResultSet()? >
I have a stored procedure, I want to call it from JDBC, I got null pointer exception in the line"
while (restuls.next()) {
My code is:
Connection con = Database.getConnection();
CallableStatement callableStatement = null;
try {
String storedProcedure = "{call getAllCustomerAddresses(?,?,?,?,?,?,?)}";
callableStatement = con.prepareCall(storedProcedure);
callableStatement.setInt(1, this.getID());
callableStatement.registerOutParameter(2,
java.sql.Types.INTEGER);
callableStatement.registerOutParameter(3,
java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(4,
java.sql.Types.INTEGER);
callableStatement.registerOutParameter(5,
java.sql.Types.INTEGER);
callableStatement.registerOutParameter(6,
java.sql.Types.INTEGER);
callableStatement.registerOutParameter(7,
java.sql.Types.VARCHAR);
callableStatement.execute();
System.out.println(callableStatement.getInt(2));
System.out.println(callableStatement.getString(3));
System.out.println(callableStatement.getInt(4));
System.out.println(callableStatement.getInt(5));
System.out.println(callableStatement.getInt(6));
System.out.println(callableStatement.getString(7));
ResultSet restuls = callableStatement.getResultSet();
while (restuls.next()) {
int addressID = restuls.getInt(2);
String label = restuls.getString(3);
int regionID = restuls.getInt(4);
int areaID = restuls.getInt(5);
int cityID = restuls.getInt(6);
String description = restuls.getString(7);
this.addresses.add(new CustomerAddressImpl(this, label,
description, RegionImpl.getInstance(regionID),
AreaImpl.getInstance(areaID), CityImpl
.getInstance(cityID), addressID));
}
look at the code, the System.out.println
is working , and It is printing the right values from database, so why the results set is null please??
another thing, I must use result set because the stored procedure returns many rows.
I am really confusing why I can print the right values but the result set is null
Thanks in advance
Edit
If you want to give you the stored procedure tell me please
Stored Procedure
ALTER PROCEDURE [dbo].getAllCustomerAddresses(
@customerID INT,
@addressID INT OUTPUT,
@label VARCHAR(200) OUTPUT,
@regionID INT OUTPUT,
@areaID INT OUTPUT,
@cityID INT OUTPUT,
@description TEXT OUTPUT
)
AS
SET NOCOUNT Off;
SELECT @addressID = [ID],
@label = [label],
@regionID = [regionID],
@areaID = [areaID],
@cityID = [cityID],
@description = [description]
FROM Customer_Address
WHERE customerID = @customerID
Your stored procedure doesn't actually produce a ResultSet
because you are using output parameters (not 100% sure, I don't have a SQL Server handy to test).
You may just need to call CallableStatement.getObject(int)
or CallableStatement.getObject(String)
(or a type specific getter) to get the values instead. If you want to process as a ResultSet
, then you should not use the output parameters in your stored procedures, but write the stored procedure as a select without assigning to output parameter. That will create a result set from the stored procedure
Another possibility might by that your stored procedure is first returning one or more update counts before returning the result set. The boolean return value of execute()
indicates whether the first result is an update count or a ResultSet
. You will need to repeatedly call getMoreResults()
and getUpdateCount()
to be sure you have processed every result.
Your posted stored procedure contains SET NOCOUNT OFF
which signals to SQL Server (or Sybase) that you want update (and I believe select) counts returned as well, you might want to try using SET NOCOUNT ON
.
You can also try to process the results of execute()
like this to find out if there are indeed multiple update counts etc before the result set:
boolean result = pstmt.execute();
while(true)
if (result) {
ResultSet rs = pstmt.getResultSet();
// Do something with resultset ...
} else {
int updateCount = pstmt.getUpdateCount();
if (updateCount == -1) {
// no more results
break;
}
// Do something with update count ...
}
result = pstmt.getMoreResults();
}
See also Java SQL: Statement.hasResultSet()?
这篇关于ResultSet NullPointerException的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!