使用存储过程实现简单搜索 我在jsp页面中调用存储过程,但得到的结果集里面没有数据 大家帮忙看一下
存储过程代码:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOALTER PROCEDURE SearchCatalog
(
@PageNumber TINYINT,
@ProductsPerPage TINYINT,
@HowManyResults SMALLINT OUTPUT,
@AllWords BIT,
@Word1 VARCHAR(15) = NULL,
@Word2 VARCHAR(15) = NULL,
@Word3 VARCHAR(15) = NULL,
@Word4 VARCHAR(15) = NULL,
@Word5 VARCHAR(15) = NULL)
AS
/**//* 创建临时表,保存搜索的结果(Sql Server2005适用,Sql Server2000见如何在数据层分页以提高性能) */
DECLARE @Products TABLE
(RowNumber SMALLINT IDENTITY (1,1) NOT NULL,
pID INT,
pName VARCHAR(50),
pDescription VARCHAR(1000),
Rank INT) /**//* Any-words search */
IF @AllWords = 0
INSERT INTO @Products
SELECT ID, Name, Description,
3 * dbo.WordCount(@Word1, Name) + dbo.WordCount(@Word1, Description) + 3 * dbo.WordCount(@Word2, Name) + dbo.WordCount(@Word2, Description) +
3 * dbo.WordCount(@Word3, Name) + dbo.WordCount(@Word3, Description) +
3 * dbo.WordCount(@Word4, Name) + dbo.WordCount(@Word4, Description) +
3 * dbo.WordCount(@Word5, Name) + dbo.WordCount(@Word5, Description)
AS Rank FROM Product
ORDER BY Rank DESC
/**//* all-words search */ IF @AllWords = 1 INSERT INTO @Products
SELECT ID, Name, Description,
(3 * dbo.WordCount(@Word1, Name) + dbo.WordCount
(@Word1, Description)) * CASE WHEN @Word2 IS NULL THEN 1 ELSE 3 * dbo.WordCount(@Word2, Name) + dbo.WordCount(@Word2, Description) END *
CASE WHEN @Word3 IS NULL THEN 1
ELSE 3 * dbo.WordCount(@Word3, Name) + dbo.WordCount(@Word3, Description) END * CASE WHEN @Word4 IS NULL THEN 1 ELSE 3 * dbo.WordCount(@Word4, Name) + dbo.WordCount(@Word4, Description) END *
CASE WHEN @Word5 IS NULL THEN 1 ELSE 3 * dbo.WordCount(@Word5, Name) + dbo.WordCount(@Word5,Description) END
AS Rank
FROM Product ORDER BY Rank DESC/**//* 在外部变量保存搜索结果数 */ SELECT @HowManyResults = COUNT(*) FROM @Products
WHERE Rank > 0/**//* 按页返回结果*/ SELECT pID, pName, pDescription,Rank
FROM @Products
WHERE Rank > 0
AND RowNumber BETWEEN (@PageNumber-1) * @ProductsPerPage + 1
AND @PageNumber * @ProductsPerPage
ORDER BY Rank DESC
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOJSP的代码为:
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String url="jdbc:sqlserver://localhost:1433;databaseName=MyBBS";
String sql = "{call SearchCatalog(?,?,?,?,?,?,?,?,?)}";
Connection cn = null;
CallableStatement cmd = null;
try{
cn = DriverManager.getConnection(url,"sa","sa" );
cmd = cn.prepareCall(sql);
cmd.setInt(1,1);
cmd.setInt(2,2);
cmd.registerOutParameter(3,1);
cmd.setInt(4,0);
cmd.setString(5,"a");
cmd.setString(6,null);
cmd.setString(7,null);
cmd.setString(8,null);
cmd.setString(9,null);
ResultSet rs = cmd.executeQuery();
int returnValue = cmd.getInt(3);
while(rs != null && rs.next() )
{
out.println("查询数据为:");
out.println("<br>");
out.println(rs.getInt("ID") + " " + rs.getString("Name") + " " + rs.getString("Description"));
}
out.println("<br>" + returnValue );
}
catch(SQLException e)
{
e.printStackTrace();
cmd = null;
cn = null;
}
finally
{
if(cmd != null)
{
cmd.close();
cmd = null;
}
if( cn != null)
{
cn.close();
cn = null;
}
}存储过程SearchCatalog没问题 在查询分析器运行能得到预期结果,但在jsp页面调用时返回的结果集ResultSet内无数据,刚开始我以为可能是存储过程中不能使用表变量返回数据,也可能是Java中调用存储过程使用executeQuery()得不到存储过程返回的表变量的数据。接下来我试了一下:
写了个存储过程
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER procedure proc_test (@pid,@result int output) as
begin
declare @p table(id int,name varchar(20))
insert into @p
select id, Name from Product
select @result= max(id) from @p
select * from @p where id = @pid
end GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO在jsp页面中调用此存储过程 能够得到返回的结果集(表变量P中的数据)。但调用SearchCatalog 为什么得不到结果集呢 ?郁闷中
大家帮帮忙 告诉我 问题出在哪?
存储过程代码:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOALTER PROCEDURE SearchCatalog
(
@PageNumber TINYINT,
@ProductsPerPage TINYINT,
@HowManyResults SMALLINT OUTPUT,
@AllWords BIT,
@Word1 VARCHAR(15) = NULL,
@Word2 VARCHAR(15) = NULL,
@Word3 VARCHAR(15) = NULL,
@Word4 VARCHAR(15) = NULL,
@Word5 VARCHAR(15) = NULL)
AS
/**//* 创建临时表,保存搜索的结果(Sql Server2005适用,Sql Server2000见如何在数据层分页以提高性能) */
DECLARE @Products TABLE
(RowNumber SMALLINT IDENTITY (1,1) NOT NULL,
pID INT,
pName VARCHAR(50),
pDescription VARCHAR(1000),
Rank INT) /**//* Any-words search */
IF @AllWords = 0
INSERT INTO @Products
SELECT ID, Name, Description,
3 * dbo.WordCount(@Word1, Name) + dbo.WordCount(@Word1, Description) + 3 * dbo.WordCount(@Word2, Name) + dbo.WordCount(@Word2, Description) +
3 * dbo.WordCount(@Word3, Name) + dbo.WordCount(@Word3, Description) +
3 * dbo.WordCount(@Word4, Name) + dbo.WordCount(@Word4, Description) +
3 * dbo.WordCount(@Word5, Name) + dbo.WordCount(@Word5, Description)
AS Rank FROM Product
ORDER BY Rank DESC
/**//* all-words search */ IF @AllWords = 1 INSERT INTO @Products
SELECT ID, Name, Description,
(3 * dbo.WordCount(@Word1, Name) + dbo.WordCount
(@Word1, Description)) * CASE WHEN @Word2 IS NULL THEN 1 ELSE 3 * dbo.WordCount(@Word2, Name) + dbo.WordCount(@Word2, Description) END *
CASE WHEN @Word3 IS NULL THEN 1
ELSE 3 * dbo.WordCount(@Word3, Name) + dbo.WordCount(@Word3, Description) END * CASE WHEN @Word4 IS NULL THEN 1 ELSE 3 * dbo.WordCount(@Word4, Name) + dbo.WordCount(@Word4, Description) END *
CASE WHEN @Word5 IS NULL THEN 1 ELSE 3 * dbo.WordCount(@Word5, Name) + dbo.WordCount(@Word5,Description) END
AS Rank
FROM Product ORDER BY Rank DESC/**//* 在外部变量保存搜索结果数 */ SELECT @HowManyResults = COUNT(*) FROM @Products
WHERE Rank > 0/**//* 按页返回结果*/ SELECT pID, pName, pDescription,Rank
FROM @Products
WHERE Rank > 0
AND RowNumber BETWEEN (@PageNumber-1) * @ProductsPerPage + 1
AND @PageNumber * @ProductsPerPage
ORDER BY Rank DESC
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOJSP的代码为:
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String url="jdbc:sqlserver://localhost:1433;databaseName=MyBBS";
String sql = "{call SearchCatalog(?,?,?,?,?,?,?,?,?)}";
Connection cn = null;
CallableStatement cmd = null;
try{
cn = DriverManager.getConnection(url,"sa","sa" );
cmd = cn.prepareCall(sql);
cmd.setInt(1,1);
cmd.setInt(2,2);
cmd.registerOutParameter(3,1);
cmd.setInt(4,0);
cmd.setString(5,"a");
cmd.setString(6,null);
cmd.setString(7,null);
cmd.setString(8,null);
cmd.setString(9,null);
ResultSet rs = cmd.executeQuery();
int returnValue = cmd.getInt(3);
while(rs != null && rs.next() )
{
out.println("查询数据为:");
out.println("<br>");
out.println(rs.getInt("ID") + " " + rs.getString("Name") + " " + rs.getString("Description"));
}
out.println("<br>" + returnValue );
}
catch(SQLException e)
{
e.printStackTrace();
cmd = null;
cn = null;
}
finally
{
if(cmd != null)
{
cmd.close();
cmd = null;
}
if( cn != null)
{
cn.close();
cn = null;
}
}存储过程SearchCatalog没问题 在查询分析器运行能得到预期结果,但在jsp页面调用时返回的结果集ResultSet内无数据,刚开始我以为可能是存储过程中不能使用表变量返回数据,也可能是Java中调用存储过程使用executeQuery()得不到存储过程返回的表变量的数据。接下来我试了一下:
写了个存储过程
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER procedure proc_test (@pid,@result int output) as
begin
declare @p table(id int,name varchar(20))
insert into @p
select id, Name from Product
select @result= max(id) from @p
select * from @p where id = @pid
end GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO在jsp页面中调用此存储过程 能够得到返回的结果集(表变量P中的数据)。但调用SearchCatalog 为什么得不到结果集呢 ?郁闷中
大家帮帮忙 告诉我 问题出在哪?
解决方案 »
- s2sh框中的jsp的js中forward方法
- 解析xls文件,解析的总条数问题------急!急
- 关于HIbernate和Spring 使用annotation和xml配置问题
- struts2 页面设置国际化跳转问题
- 关于log4j的一些问题
- 无法登陆服务器
- 在公司里ajax一般用什么框架?
- Apache2.2.4+Tomcat6.0 整合 问题..........
- struts的问题,请高手解决,问了好多论坛,都没有人解答
- JB8+WL7,配JB时需要一webservices.jar,哪里有呢?
- hibernate连接配置sqlserver创建表的问题
- Struts+Spring的dispatchaction问题
取出rs中的数据后 在调用CallableStatement的getInt(3)得到返回值。如果执行CallableStatement的executeQuery()后 先调用CallableStatement的getInt(3)得到返回值 再取出rs中的数据,则rs中无数据 取存储过程的返回值和返回结果集的先后顺序不同 为什么会有不同的结果呢 请大家解释一下