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();
}
}
}
页:
[1]