SQL SERVER 2008存储过程生成编号，JDBC调用

存储过程

USE [20150513GT]GO/****** Object:  StoredProcedure [dbo].[PRO_AUTO_PROJECT_ORDERID]    Script Date: 09/29/2016 11:31:08 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- Batch submitted through debugger: SQLQuery18.sql|7|0|C:\Users\Administrator\AppData\Local\Temp\~vs6A2F.sql-- =============================================-- Author:      huangjp-- Create date: 2016-09-28-- Description: 通过参数自动生成事项编号-- 规则(type+zjcode+date+001) 部门类型 + 镇街代码 + 年月 + 序列号（三位） EG: A001506197-- =============================================ALTER PROCEDURE [dbo].[PRO_AUTO_PROJECT_ORDERID]    -- Add the parameters for the stored procedure here    @in_ProjectId nvarchar(20),    @in_UserName nvarchar(30),     @in_prjPKid nvarchar(20),    @out_OrderId nvarchar(50) outputAS    declare         @temp_order_flag nvarchar(255),        @temp_departType nvarchar(2),        @temp_ZjCode nvarchar(10),        @temp_Date nvarchar(6),             @temp_sql_text1 nvarchar(5),        @temp_sql_text2 nvarchar(5),        @ParmDefinition nvarchar(500),        @sql nvarchar(255)              BEGIN    SET NOCOUNT ON;    set @temp_order_flag = (select i.order_id from o_pfo i where i.id = @in_prjPKid);       if LEN(@temp_order_flag) is null or LEN(@temp_order_flag) = 0        begin            set @temp_departType = (select case when project_departid='3' then 'A' else 'B' end from o_g where id=@in_ProjectId);            -- find zjcode,date            select                 @temp_ZjCode = (case when u.zjcode is null or len(u.zjcode)=0 then '00' else u.zjcode end)                    from UG_GROUP u where u.TYPE = 'org' and u.GROUP_ID in (                        select g.GROUP_ID from u_g g where g.USER_ID = (                            select uu.USER_ID from u_ur uu where uu.NAME = @in_UserName ));                select @temp_Date=SUBSTRING(CONVERT(varchar(12),getdate(),112),3,4);            set @temp_sql_text1 = '%';            set @temp_sql_text2 = '''';                set @sql = N'select  top 1 @temp_orderOUT = order_id from o_pfo where order_id like'                + @temp_sql_text2 + @temp_sql_text1 + @temp_departType + @temp_ZjCode + @temp_Date + @temp_sql_text1 + @temp_sql_text2 + ' and len(order_id) > 8 order by order_id desc';            SET @ParmDefinition = N'@temp_orderOUT varchar(255) output';                exec sp_executesql @sql,@ParmDefinition,@temp_orderOUT=@out_OrderId output;            if @out_OrderId is null                begin                  set @out_OrderId = @temp_departType + @temp_ZjCode + @temp_Date + '001';                end            else                begin                     declare                         @temp_seq_len int,                        @temp_seq_str varchar(3);                     set @temp_seq_str = (select convert(int,SUBSTRING(@out_OrderId,8,3))) + 1;                     --set @temp_seq_str = (select convert(int,SUBSTRING('A001607122',8,3))) + 1;                     set @temp_seq_len = LEN(@temp_seq_str);                     while @temp_seq_len < 3                         begin                            set @temp_seq_str = '0' + @temp_seq_str;                            set @temp_seq_len = @temp_seq_len + 1;                        end                               set @out_OrderId = SUBSTRING(@out_OrderId,1,7) + @temp_seq_str;                                     end                 begin                    begin tran                    begin try                      update o_pfo set order_id = @out_OrderId where id = @in_prjPKid                             commit                    end try                    begin catch                        rollback                        select ERROR_NUMBER() as 返回错误号,ERROR_SEVERITY() as 返回严重性,ERROR_STATE() as 返回错误状态号,ERROR_PROCEDURE() as 返回出错误的存储过程或触发器的名称,ERROR_LINE() as 返回导致错误的例程中的行号,ERROR_MESSAGE() as 返回错误消息的完整文本该文本                      end catch                end        end    else        begin         set @out_OrderId = @temp_order_flag;        end     select @out_OrderIdEND

JDBC调用

String cOrderId = (String) this.getHibernateTemplate().execute(new HibernateCallback() {            @Override            public Object doInHibernate(Session session) throws HibernateException,                    SQLException {                String rStr = "";                String proc = " {call PRO_AUTO_PROJECT_ORDERID(?,?,?,?)} ";                Connection conn = null;                  CallableStatement cstmt = null;                  try {                      conn = session.connection();                      //conn.setAutoCommit(false);                      cstmt = conn.prepareCall(proc);                      cstmt.setString("@in_ProjectId", (String)fm_params.get("projectId"));                    cstmt.setString("@in_UserName", (String)fm_params.get("userName"));                    cstmt.setString("@in_prjPKid", (String)fm_params.get("prjPkId"));                    cstmt.registerOutParameter("@out_OrderId", java.sql.Types.VARCHAR);                    cstmt.execute();                      conn.commit();                      /*因返回的是结果集，要加判断，如不加则报错java.sql.SQLException: Output parameters have not yet been processed. Call getMoreResults().*/                    if(!cstmt.getMoreResults()){                        rStr = cstmt.getString(4);                    }                    /*cstmt.getMoreResults();                    ResultSet rs = (ResultSet) cstmt.getObject(4);                    while (rs.next()){                        rStr = rs.getString(1);                    }                    rs.close();*/                } catch (Exception ex) {                      try {                          conn.rollback();                      } catch (SQLException e1) {                          logger.error(e1);                          e1.printStackTrace();                      }                      ex.printStackTrace();                  } finally {                      if(cstmt != null) {                          try {                              cstmt.close();                          }catch(Exception ex) {}                      }                  }                  logger.info("saveOrupdateObject: call PRO_AUTO_PROJECT_ORDERID,return value=" + rStr);                return rStr;            }        });