Java处理带返回值的存储过程Procedure (SQL Server)

package com.zjx.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestProcedure {
	
	String url = "jdbc:microsoft:sqlserver://172.16.11.20:1433;DatabaseName=new_cmcc;SelectMethod=cursor";
	String username = "sa";
	String password = "111";
	
	public static void main(String[] args) {
		TestProcedure tp = new TestProcedure();
		Connection conn = tp.getConn();
		tp.executeStoredProcedureTest(conn);
		
		tp.getAllUsers(conn);
		
		try {
			if(!conn.isClosed()){
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

    
	public Connection getConn() {
		Connection conn = null;
		try {
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
			
		} catch (Exception e) {
			System.out.println("注册驱动失败！");
			e.printStackTrace();
		}
		try {
			conn = DriverManager.getConnection(url, username, password);
			
		} catch (Exception e) {
			System.out.println("数据库连接失败！");
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 
   create table "new_cmcc"."dbo"."users"(
        "id" int identity not null,
       "firstname" varchar(50) null,
       "lastname" varchar(50) not null,
       "age" int default '(20)' not null,
        constraint "id" primary key ("id")
    )
	 */
	public static void getAllUsers(Connection conn){
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from new_cmcc.dbo.users");
			while(rs.next()){
				System.out.println(rs.getInt(1)+"  " +rs.getString(2)+"  " +rs.getString(3)+"  " +rs.getInt(4));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
			
		} finally{
			try {
				rs.close();
				st.close();
				
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
		}
		
	}
	
	/**
	 * 
//drop procedure getNames;

CREATE PROCEDURE getNames
   (@cityName CHAR(50))
AS
BEGIN
   IF ((SELECT COUNT(*)
   FROM new_cmcc.dbo.users
   WHERE firstname = @cityName) > 0)
   RETURN 1
ELSE
   RETURN 0
END	 * @param con
	 */
	public static void executeStoredProcedureTest(Connection con) {
		   try {
		      CallableStatement cstmt = con.prepareCall("{? = call dbo.getNames(?)}");
		      cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
		      cstmt.setString(2, "周");		      cstmt.execute();
		      
		      System.out.println("RETURN STATUS: " + cstmt.getInt(1));
		      
		      cstmt.close();
		    
		   }
		   
		   catch (Exception e) {
		      e.printStackTrace();
		   }
		}
}