17,082
社区成员
发帖
与我相关
我的任务
分享
--创建用户表
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