Posted on 2007-07-16 17:25
胡娟 阅读(2779)
评论(1) 编辑 收藏 所属分类:
JAVA
创建存储过程：
1、在企业管理器中新建存储过程
2、在查询分析器中编辑存储过程(带有参数和返回值的)
1
SET QUOTED_IDENTIFIER ON
2GO
3SET ANSI_NULLS ON
4GO
5
6ALTER procedure insertUserinfo @name varchar(50),@sex varchar(50),@birthday varchar(50),@returnValue int = 0 output
7
8as
9
10insert into userinfo (name,sex,birthday) values (@name,@sex,@birthday)
11
12set @returnValue = 1;
13
14GO
15SET QUOTED_IDENTIFIER OFF
16GO
17SET ANSI_NULLS ON
18GO
Java 调用存储过程
1.数据库连接
1package com.hujuan.conn;
2import java.sql.Connection;
3import java.sql.DriverManager;
4import java.sql.SQLException;
5
6
7
public class DatabaseConn 
{
8
9
public static Connection getConnection(){
10 String className= "com.microsoft.jdbc.sqlserver.SQLServerDriver";
11 String url = "jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=login";
12 String name = "sa";
13 String password = "sasa";
14 try {
15 Class.forName(className);
16
17 return DriverManager.getConnection(url,name,password);
18 } catch (ClassNotFoundException e) {
19 e.printStackTrace();
20 } catch (SQLException e) {
21 e.printStackTrace();
22
}
23 return null;
24 }
25
}
2.通过连接调用存储过程
1package com.hujuan.dao;
2import java.sql.CallableStatement;
3import java.sql.Connection;
4import java.sql.ResultSet;
5import java.sql.SQLException;
6import java.sql.Types;
7import com.hujuan.conn.DatabaseConn;
8
9public class SqlServerProc {
10
11 public static void main(String[] args) {
12 Connection conn = null;
13 CallableStatement call = null;
14 conn = DatabaseConn.getConnection();
15
16 try {
17 call = conn.prepareCall("{call insertUserinfo(?,?,?,?)}");
18 call.setString(1, "hujuan");
19 call.setString(2, "女");
20 call.setString(3, "1985-06-16");
21 call.registerOutParameter(4, Types.INTEGER);
22 call.executeUpdate();
23 int value = call.getInt(4);
24 System.out.println(value);
25 } catch (SQLException e) {
26 e.printStackTrace();
27 }finally{
28 try {
29 if(call != null)call.close();
30 if(conn != null)conn.close();
31 } catch (SQLException e) {
32 e.printStackTrace();
33 }
34 }
35
36 }
37}
返回结果集
1package com.hujuan.dao;
2import java.sql.CallableStatement;
3import java.sql.Connection;
4import java.sql.ResultSet;
5import java.sql.SQLException;
6import java.sql.Types;
7import com.hujuan.conn.DatabaseConn;
8
9public class SqlServerProc {
10
11 public static void main(String[] args) {Connection conn = null;
12 CallableStatement call = null;
13 ResultSet rs = null;
14 conn = DatabaseConn.getConnection();
15
16 try {
17 call = conn.prepareCall("{?=call selectUserinfo()}");
18 call.registerOutParameter(1, Types.REAL);
19 rs = call.executeQuery();
20 while(rs.next()){
21 System.out.println(rs.getString("name"));
22 }
23
24 } catch (SQLException e) {
25 e.printStackTrace();
26 }finally{
27 try {
28 if(rs != null)rs.close();
29 if(call != null)call.close();
30 if(conn != null)conn.close();
31 } catch (SQLException e) {
32 e.printStackTrace();
33 }
34 }
35 }
36
37}