JAVA中使用JDBC调用MS SQL存储过程

背景：

SQL SERVER2005 JAVA JDK1.4

WEBLOGIC测试环境

当进行一些复杂点的查询时，不愿意使用在java代码中拼复杂的语句，看起来很繁琐。用存储过程是一个不错的解决途径，可以使得代码简洁很多。

SQL PROCEDURE:

JAVA Code：

/**

* 进行查询

* @return List

*/

public List getVoteStat(String siteId) {

Session session = null;

List lstResult = new ArrayList();

try {

session = this.getSession();

Connection conn = session.connection();

String sql = "{ call SP_MESSAGE_VOTE_STAT(?) }";

PreparedStatement psmt = conn.prepareCall(sql);

psmt.setString(1, siteId);

//执行视图V_Message_stat

ResultSet rs = psmt.executeQuery();

while (rs.next()) {

Properties prop = new Properties();

//设置变量 带回到前台

prop.setProperty("MESSAGE_CATEGORY", rs.getString("MESSAGE_CATEGORY"));

prop.setProperty("CATEGORY_ID", rs.getString("CATEGORY_ID"));

prop.setProperty("VOTE_NO", rs.getString("vote_no"));

prop.setProperty("VOTE_YES", rs.getString("vote_yes"));

prop.setProperty("SATIFY_ZERO_PERCENT", rs.getString("satify_zero_percent"));

prop.setProperty("SATIFY_ONE_PERCENT", rs.getString("satify_one_percent"));

prop.setProperty("SATIFY_TWO_PERCENT", rs.getString("satify_two_percent"));

prop.setProperty("SATIFY_ZERO", rs.getString("satify_zero"));

prop.setProperty("SATIFY_ONE", rs.getString("satify_one"));

prop.setProperty("SATIFY_TWO", rs.getString("satify_two"));

// System.out.println(rs.getString(0));

lstResult.add(prop);

}

} catch (Exception ex) {

ex.printStackTrace();

} finally {

try {

session.close();

} catch (HibernateException ex) {

ex.printStackTrace();

}

session = null;

}

return lstResult;

}

ALTER PROCEDURE [ezoffice].[SP_MESSAGE_VOTE_STAT]

(

--参数

@v_site_id varchar(30)

)

AS

declare @e_sql nvarchar(2000);

--拼凑SQL语句进行查询

set @e_sql ='SELECT (select top 1 cg.category_title from cms_messagecategory cg

where cg.category_id=TEMP.CATEGORY_ID) as MESSAGE_CATEGORY,CATEGORY_ID, SUM(vote_no) AS vote_no, SUM(vote_yes) AS vote_yes,

CASE SUM(vote_yes) WHEN 0 THEN 0 ELSE CAST(CAST(SUM(manyi) AS decimal(9, 2))/ SUM(vote_yes) AS decimal(9, 2))*100 END AS satify_zero_percent,

CASE SUM(vote_yes) WHEN 0 THEN 0 ELSE CAST(CAST(SUM(yibanmanyi) AS decimal(9, 2))/ SUM(vote_yes) AS decimal(9, 2))*100 END AS satify_one_percent,

CASE SUM(vote_yes) WHEN 0 THEN 0 ELSE CAST(CAST(SUM(bumanyi) AS decimal(9, 2))/ SUM(vote_yes) AS decimal(9, 2))*100 END AS satify_two_percent,

SUM(manyi) AS satify_zero, SUM(yibanmanyi) AS satify_one, SUM(bumanyi) AS satify_two

FROM';

set @e_sql=@e_sql+'(SELECT CASE ISNULL(msg.VOTE_STATUS, ''0'') WHEN ''0'' THEN 1 ELSE 0 END AS vote_no, CASE ISNULL(msg.VOTE_STATUS, ''0'')

WHEN ''1'' THEN 1 ELSE 0 END AS vote_yes, ISNULL(VOTE_RESULT, ''-1'') AS VOTE_RESULT, SITE_ID, CATEGORY_ID,

CASE isnull(msg.vote_result, ''-1'') WHEN ''0'' THEN 1 ELSE 0 END AS manyi,

CASE isnull(msg.vote_result, ''-1'') WHEN ''1'' THEN 1 ELSE 0 END AS yibanmanyi,

CASE isnull(msg.vote_result, ''-1'') WHEN ''2'' THEN 1 ELSE 0 END AS bumanyi

FROM ezoffice.CMS_MESSAGE AS msg

WHERE 1=1';

if @v_site_id is not null begin SET @e_sql=@e_sql+' and msg.site_id='+ @v_site_id; end

set @e_sql=@e_sql+' ) AS TEMP ';

set @e_sql=@e_sql+' GROUP BY CATEGORY_ID ';

execute (@e_sql);