--
创建用户表
create table users (
uid varchar2 ( 10 ) not null primary key ,
userName varchar2 ( 20 ) not null ,
pwd varchar2 ( 20 ) not null
);
-- 创建序列
create or replace sequence seq
start with 1
increment by 1
maxvalue 20
minvalue 20
cycle
cache 20 ;
-- 创建一个有返回 procedure 首先需要创建一个包
create or replace package pk_userlist
as
type ref_users is ref cursor ; -- 声明动态游标
end pk_userlist;
create or replace procedure sel_userlist(u_cursor out pk_userlist.ref_users)
is
begin
open u_cursor for select * from users;
end sel_userlist;
create table users (
uid varchar2 ( 10 ) not null primary key ,
userName varchar2 ( 20 ) not null ,
pwd varchar2 ( 20 ) not null
);
-- 创建序列
create or replace sequence seq
start with 1
increment by 1
maxvalue 20
minvalue 20
cycle
cache 20 ;
-- 创建一个有返回 procedure 首先需要创建一个包
create or replace package pk_userlist
as
type ref_users is ref cursor ; -- 声明动态游标
end pk_userlist;
create or replace procedure sel_userlist(u_cursor out pk_userlist.ref_users)
is
begin
open u_cursor for select * from users;
end sel_userlist;
下面是 java调用代码
//
建立一个持久类
public class Users implement java.io.Serializable ... {
private Integer uid;
private String userName;
private String pwd;
public Users()...{
}
public Users(Integer uid)...{
this.uid = uid;
}
public Users(Integer uid,String userName,String pwd)...{
this.uid = uid;
this.userName = userName;
this.pwd = pwd;
}
public void setUid(Integer uid)...{
this.uid = uid;
}
public Integer getUid()...{
return uid;
}
public void setUserName(String userName)...{
this.userName = userName;
}
public String getUserName()...{
return userName;
}
public void setPwd(String pwd)...{
this.pwd = pwd;
}
public String getPwd()...{
return pwd;
}
} // end class Users
// 连接数据库类
public class ConManager ... {
private static String driver = "oracle.jdbc.driver.OracleDriver";
private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";
private static String userName = "scott";
private static String pwd = "tiger";
//加载 oracle 驱动
static ...{
try...{
Class.forName(driver);
}catch(ClassNotFoundException ex)...{
ex.printStackTrace();
}catch(Exception ex)...{
ex.printStackTrace();
}
}
//获取连接
public static Connection getConnection() ...{
try...{
return DriverManager.getConnection(url,userName,pwd);
}catch(SQLException ex)...{
ex.printStackTrace();
return null;
}catch(Exception ex)...{
ex.printStackTrace();
return null;
}
}//end getConnection()
} // end class ConManager
public class UserDaoImpl ... {
private Connection conn = null;
private CallableStatement proc = null;
private ResultSet rs = null;
private Users users = null;
//查询方法,返回列表
public List getUserAll()...{
List result = new ArrayList();
try...{
conn = ConManager.getConnection();
//如果得到的 connection 为空,直接返回一个 null;
if(conn == null)...{
return null;
}
/**//*调用存储过程*/
proc = conn.prepareCall("{ call scott.sel_userlist(?)}");
/**//*传入一个输出参数,因为前面定义的存储过程参数是包,包里定义了一个游标,所以这里传进来一个游标用来输出*/
proc.registerOutParameter(1,oracle.jdbc.OracleTypes);
/**//*执行存储过程*/
proc.execute();
/**//*得到结果集,并转换为 ResultSet */
rs = (ResultSet)proc.getObject(1);
while(rs.next())...{
users = new Users();
users.setUid(rs.getInt("uid"));
users.setUserName(rs.getString("userName"));
users.setPwd(rs.getString("pwd"));
result.add(users);
}
/**//*
*这里判断并不是必须,可在前台处理,前台我这就不写了,这里这样写只是为了更好的理解,
*真正开发最好放在前台处理
*前台写法与这里一样,如果是结合struts or servlet 写法如下:
*List list = UserDaoImpl.getUserAll();
*if(list == null){
* request.setAttribute("error","没有你要查询的数据!!!");
*}
*/
if(result.size() > 0 && result != null)...{
return result;
}
else...{
throw new Exception("没有你要查询的数据!!!");
}
}catch(SQLException ex)...{
ex.printStackTrace();
}catch(Exception ex)...{
ex.printStackTrace();
}finally...{
try...{
//释放所有资源,关闭连接
users = null;
this.close(rs,proc,null,conn);
}catch(SQLException ex)...{
ex.printStackTrace();
}
}
return null;
} //end getUserAll()
//关闭连接
public void close(ResultSet rs,CallableStatement proc,PreparedStatement ps,Connection conn)...{
if(rs != null)...{
try...{
rs.close();
}catch(SQLException ex)...{
ex.printStackTrace();
}
}
if(proc != null)...{
try...{
proc.close();
}catch(SQLException ex)...{
ex.printStackTrace();
}
}
if(ps != null)...{
try...{
ps.close();
}catch(SQLException ex)...{
ex.printStackTrace();
}
}
if(conn != null)...{
try...{
conn.close();
}catch(SQLException ex)...{
ex.printStackTrace();
}
}
}//end close();
} // end class UserDaoImpl
public class Users implement java.io.Serializable ... {
private Integer uid;
private String userName;
private String pwd;
public Users()...{
}
public Users(Integer uid)...{
this.uid = uid;
}
public Users(Integer uid,String userName,String pwd)...{
this.uid = uid;
this.userName = userName;
this.pwd = pwd;
}
public void setUid(Integer uid)...{
this.uid = uid;
}
public Integer getUid()...{
return uid;
}
public void setUserName(String userName)...{
this.userName = userName;
}
public String getUserName()...{
return userName;
}
public void setPwd(String pwd)...{
this.pwd = pwd;
}
public String getPwd()...{
return pwd;
}
} // end class Users
// 连接数据库类
public class ConManager ... {
private static String driver = "oracle.jdbc.driver.OracleDriver";
private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";
private static String userName = "scott";
private static String pwd = "tiger";
//加载 oracle 驱动
static ...{
try...{
Class.forName(driver);
}catch(ClassNotFoundException ex)...{
ex.printStackTrace();
}catch(Exception ex)...{
ex.printStackTrace();
}
}
//获取连接
public static Connection getConnection() ...{
try...{
return DriverManager.getConnection(url,userName,pwd);
}catch(SQLException ex)...{
ex.printStackTrace();
return null;
}catch(Exception ex)...{
ex.printStackTrace();
return null;
}
}//end getConnection()
} // end class ConManager
public class UserDaoImpl ... {
private Connection conn = null;
private CallableStatement proc = null;
private ResultSet rs = null;
private Users users = null;
//查询方法,返回列表
public List getUserAll()...{
List result = new ArrayList();
try...{
conn = ConManager.getConnection();
//如果得到的 connection 为空,直接返回一个 null;
if(conn == null)...{
return null;
}
/**//*调用存储过程*/
proc = conn.prepareCall("{ call scott.sel_userlist(?)}");
/**//*传入一个输出参数,因为前面定义的存储过程参数是包,包里定义了一个游标,所以这里传进来一个游标用来输出*/
proc.registerOutParameter(1,oracle.jdbc.OracleTypes);
/**//*执行存储过程*/
proc.execute();
/**//*得到结果集,并转换为 ResultSet */
rs = (ResultSet)proc.getObject(1);
while(rs.next())...{
users = new Users();
users.setUid(rs.getInt("uid"));
users.setUserName(rs.getString("userName"));
users.setPwd(rs.getString("pwd"));
result.add(users);
}
/**//*
*这里判断并不是必须,可在前台处理,前台我这就不写了,这里这样写只是为了更好的理解,
*真正开发最好放在前台处理
*前台写法与这里一样,如果是结合struts or servlet 写法如下:
*List list = UserDaoImpl.getUserAll();
*if(list == null){
* request.setAttribute("error","没有你要查询的数据!!!");
*}
*/
if(result.size() > 0 && result != null)...{
return result;
}
else...{
throw new Exception("没有你要查询的数据!!!");
}
}catch(SQLException ex)...{
ex.printStackTrace();
}catch(Exception ex)...{
ex.printStackTrace();
}finally...{
try...{
//释放所有资源,关闭连接
users = null;
this.close(rs,proc,null,conn);
}catch(SQLException ex)...{
ex.printStackTrace();
}
}
return null;
} //end getUserAll()
//关闭连接
public void close(ResultSet rs,CallableStatement proc,PreparedStatement ps,Connection conn)...{
if(rs != null)...{
try...{
rs.close();
}catch(SQLException ex)...{
ex.printStackTrace();
}
}
if(proc != null)...{
try...{
proc.close();
}catch(SQLException ex)...{
ex.printStackTrace();
}
}
if(ps != null)...{
try...{
ps.close();
}catch(SQLException ex)...{
ex.printStackTrace();
}
}
if(conn != null)...{
try...{
conn.close();
}catch(SQLException ex)...{
ex.printStackTrace();
}
}
}//end close();
} // end class UserDaoImpl
上面的代码实现了用 java 调用oracle存储过程返回一个列表,还要请各位读者多多指教