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

}

}

}