Javaを使いSQLServerのストアドから結果一覧を取得したい
Javaでシステム開発をしているのですが、SQLServerを使用しデータを取り出したいです。
いま問題となっているのはストアドプロシージャでの結果取得についてです。
私自身前任者から引き継いだばかりのプログラム初心者な上SQLのことはわかりませんので、
本当に申し訳ないですが状況説明すら間違えてる可能性があります。
やりたいことは、
SQLに名簿があり
ストアドプロシージャを実行すると条件にあった人物が表示されるというものです。
発生している問題・エラーメッセージ
SQLManagementStudio上でストアドプロシージャ実行し、条件にあった検索結果は出て、returnVolumeが0となっています。
上司はSQLは出来るのですがJavaは出来きず、私が未熟なので結果テーブルが取り出せません。
該当のソースコード
SQL側
USE [Dcat]
GO
/****** Object: StoredProcedure [dbo].[getExamineeRs] Script Date: 2018/11/22 15:30:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[getExamineeRs]
-- Add the parameters for the stored procedure here
@p_name as nvarchar(50) = null,
@p_kananame as nvarchar(50) = null,
@p_birth as date = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
declare @return int
set @return = 0
select @p_name = ltrim(rtrim(@p_name))
if (@p_name is null) and (@p_kananame is null) and (@p_birth is null) begin
--SELECT
-- [ExamineeId]
-- ,[ImportGroupId]
-- ,[ResidentNumber]
-- ,[ApoDate]
-- ,[TestDate]
-- ,[Name]
-- ,[KanaName]
-- ,[Gender]
-- ,[Birth]
-- ,[Age]
-- ,[RankId]
-- ,[LastTestId]
-- ,[Status]
-- ,[DeleteFlag]
-- ,[CreateDate]
-- ,[CreateUser]
-- ,[UpdateDate]
-- ,[UpdateUser]
-- FROM [dbo].[MExaminee]
set @return = 200
end else begin
if (@p_name is not null) and (@p_kananame is not null) and (@p_birth is not null) begin
select
[ExamineeId]
,[ImportGroupId]
,[ResidentNumber]
,[ApoDate]
,[TestDate]
,[Name] = LTRIM(RTRIM([Name]))
,[KanaName] = LTRIM(RTRIM([KanaName]))
,[Gender]
,[Birth]
,[Age]
,[RankId]
,[LastTestId]
,[Status]
,[DeleteFlag]
,[CreateDate]
,[CreateUser]
,[UpdateDate]
,[UpdateUser]
FROM [dbo].[MExaminee]
where
[Name] = @p_name
and [KanaName] = @p_kananame
and [Birth] = @p_birth
end else begin
if (@p_name is not null) and (@p_kananame is not null) begin
select
[ExamineeId]
,[ImportGroupId]
,[ResidentNumber]
,[ApoDate]
,[TestDate]
,[Name] = LTRIM(RTRIM([Name]))
,[KanaName] = LTRIM(RTRIM([KanaName]))
,[Gender]
,[Birth]
,[Age]
,[RankId]
,[LastTestId]
,[Status]
,[DeleteFlag]
,[CreateDate]
,[CreateUser]
,[UpdateDate]
,[UpdateUser]
FROM [dbo].[MExaminee]
where
[Name] = @p_name
and [KanaName] = @p_kananame
-- and [Birth] = @p_birth
end else begin
if (@p_name is not null) and (@p_birth is not null) begin
select
[ExamineeId]
,[ImportGroupId]
,[ResidentNumber]
,[ApoDate]
,[TestDate]
,[Name] = LTRIM(RTRIM([Name]))
,[KanaName] = LTRIM(RTRIM([KanaName]))
,[Gender]
,[Birth]
,[Age]
,[RankId]
,[LastTestId]
,[Status]
,[DeleteFlag]
,[CreateDate]
,[CreateUser]
,[UpdateDate]
,[UpdateUser]
FROM [dbo].[MExaminee]
where
[Name] = @p_name
-- and [KanaName] = @p_kananame
and [Birth] = @p_birth
end else begin
if (@p_kananame is not null) and (@p_birth is not null) begin
select
[ExamineeId]
,[ImportGroupId]
,[ResidentNumber]
,[ApoDate]
,[TestDate]
,[Name] = LTRIM(RTRIM([Name]))
,[KanaName] = LTRIM(RTRIM([KanaName]))
,[Gender]
,[Birth]
,[Age]
,[RankId]
,[LastTestId]
,[Status]
,[DeleteFlag]
,[CreateDate]
,[CreateUser]
,[UpdateDate]
,[UpdateUser]
FROM [dbo].[MExaminee]
where
-- [Name] = @p_name
[KanaName] = @p_kananame
and [Birth] = @p_birth
end else begin
if (@p_name is not null) begin
select
[ExamineeId]
,[ImportGroupId]
,[ResidentNumber]
,[ApoDate]
,[TestDate]
,[Name] = LTRIM(RTRIM([Name]))
,[KanaName] = LTRIM(RTRIM([KanaName]))
,[Gender]
,[Birth]
,[Age]
,[RankId]
,[LastTestId]
,[Status]
,[DeleteFlag]
,[CreateDate]
,[CreateUser]
,[UpdateDate]
,[UpdateUser]
FROM [dbo].[MExaminee]
where
[Name] = @p_name
-- [KanaName] = @p_kananame
-- and [Birth] = @p_birth
end else begin
if (@p_kananame is not null) begin
select
[ExamineeId]
,[ImportGroupId]
,[ResidentNumber]
,[ApoDate]
,[TestDate]
,[Name] = LTRIM(RTRIM([Name]))
,[KanaName] = LTRIM(RTRIM([KanaName]))
,[Gender]
,[Birth]
,[Age]
,[RankId]
,[LastTestId]
,[Status]
,[DeleteFlag]
,[CreateDate]
,[CreateUser]
,[UpdateDate]
,[UpdateUser]
FROM [dbo].[MExaminee]
where
-- [Name] = @p_name
[KanaName] = @p_kananame
-- and [Birth] = @p_birth
end else begin
if (@p_birth is not null) begin
select
[ExamineeId]
,[ImportGroupId]
,[ResidentNumber]
,[ApoDate]
,[TestDate]
,[Name] = LTRIM(RTRIM([Name]))
,[KanaName] = LTRIM(RTRIM([KanaName]))
,[Gender]
,[Birth]
,[Age]
,[RankId]
,[LastTestId]
,[Status]
,[DeleteFlag]
,[CreateDate]
,[CreateUser]
,[UpdateDate]
,[UpdateUser]
FROM [dbo].[MExaminee]
where
-- [Name] = @p_name
-- [KanaName] = @p_kananame
[Birth] = @p_birth
end
end
end
end
end
end
end
end
return (@return)
END
Java側
try {
ResultSet rs = Sql.exec(true, "getExamineeRs", params.name, params.kanaName, params.birth);
DataTable tbl = new DataTable(rs);
// DataTable tbl = new DataTable("MExaminee");
tbl.setLabelMap(
"Name", "名前",
"KanaName", "フリガナ",
"Birth", "生年月日",
"Gender", "性別",
"ExamineeId", "ID"
);
tbl.addHtml(0, "<td><input type=\"button\" value=\"選択\" onclick=\"rowClick(this);\"></td>");
out.write(tbl.getTable());
} catch (SQLException e) {
throw new UserRuntimeException(e);
}
上司は出来るということなので、
色々調べてたのですが、returnVolume以外を取り出す方法がわかりませんでした。
- Java1.8
- SQLServer2017