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);