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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 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) output
AS
    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_OrderId
END

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