What way is right to invok ResultSet form JavaBean in Servle

bill1

oracle's package P_T_Reoprt

create or replace package P_T_Reoprt
as
type v_cursor is ref cursor&#59;
function T_Reoprt (j varchar2) return v_cursor&#59;
end P_T_Reoprt&#59;

create or replace package body P_T_Reoprt
as
function T_Reoprt (j varchar2) return v_cursor
is
rc v_cursor&#59;
begin
  open rc for select * from customer where customer.sex = j&#59;
  return rc&#59;
end&#59;
end P_T_Reoprt&#59;

------------------------------------------------

DBConnection.java(a method of the javabean's source)
....
  public ResultSet executeProduce(){
    rs = null&#59;
    try {
    //connect the database
    conn = DriverManager.getConnection("jdbcracle:thin192.168.0.176:1521:yzttest","bill","bill&quot&#59;
    System.out.println("004:connection success!&quot&#59;
    // Prepare a PL/SQL call
    CallableStatement call =  conn.prepareCall ("{?=call P_T_Reoprt.T_Reoprt(?)}&quot&#59;
    System.out.println("005:prepare PL/SQL Call success!&quot&#59;
    // Find out all Record
    call.registerOutParameter (1,OracleTypes.CURSOR)&#59;
    call.setString (2,"M&quot&#59;
    System.out.println("006L/SQL define success !&quot&#59;
    call.execute ()&#59;
    System.err.println("007L/SQL execute success!&quot&#59;
    ResultSet rs = (ResultSet)call.getObject (1)&#59;
    System.out.println("008:take resultset success!&quot&#59;
    }
    catch(SQLException ex) {
    System.err.println("012:fail to DBConnection.executeProduce: " + ex.getMessage())&#59;
    }
    return rs&#59;
  }
....

------------------------------------------------

when i invok that DBConnection's executeProduce method in the Servlet, that had happened and error when execute at "while(rs.next())"
...
    DBConnection JavaBean = new DBConnection()&#59;
    System.out.println("001:JavaBean instance success!&quot&#59;
    ResultSet rs=JavaBean.executeProduce()&#59;
    System.out.println("002:method invok success!&quot&#59;
    while (rs.next())//<<--------------that has error
    System.out.println(&quot;018:there have record!&quot;)&#59;
    System.out.println (rs.getString(1))&#59;
    System.out.println(&quot;013:return record success!&quot;)&#59;
...


What way is right to invok ResultSet form JavaBean in Servlet?
My way is error,now?

cinc

[這個貼子最后由cinc在 2002/10/30 00:16am 編輯]

You can use DAO Pattern and MVC Framework:

Servlet, JSP ->; JavaBean and DAO ->; Database

Servlet or JSP is the View layer of MVC Framework.
It must not contain any codes that directly access database.

JavaBean and DAO is the Model layer of MVC Framework.
JavaBeans is the representation of table in database
You can place business code in DAO Object, including code communicating with database.

For example:

If you have a table in database called : article

create table article(
  id      integer,
  title   char(10),
  content char(30)
)

1.Model layer : JavaBean and DAO Object

You can create a JavaBean to represent an article in database

public class Article{
  int id&#59;
  String title&#59;
  String content&#59;
  public void set...(){
  }
  public ... get...(){
  }
  ...
}

we ususally use DAO Object to access database, for article, we create:

public class ArticleDAO{
  /**
   * find one article, return an article
   */
  public Article findById(int id){
    // execute SQL : select title, content from article where id = 10&#59;
    Article article = new Article()&#59;
    article.setId(id)&#59;
    article.setTitle(title)&#59;
    article.setContent(content)&#59;
    return article&#59;
  }
  
  /**
   * find all the articles which contains the keyword, return a vector
   * containing these articles
   */
  public Vector findByKeyword(String keyword){
    Vector resultVector = new Vector()&#59;
    // execute SQL : select title, content from article where content like &quot;%keyword%&quot;&#59;
    while (resultset.next){
      Article article = new Article()&#59;
      article.setId(id)&#59;
      article.setTitle(title)&#59;
      article.setContent(content)&#59;
      resultVector.add (article)&#59;
    }
    return resultVector&#59;
  }
}


2.View Layer : servlet or jsp
/**
  * user want to find all the article that contain the keyword
  */
public void doGet(...){
  String keyword = getParameter(&quot;keyword&quot&#59;
  ArticleDAO articleDAO = new ArticleDAO()&#59;
  Vector resultVector = articleDAO.findByKeyword(keyword)&#59;
  // display each article in resultVector
}

DAO Pattern is a widely used pattern in web development of servlet and jsp
more detail:
http://java.sun.com/blueprints/patterns/DAO.html

Other consideration about design pattern:
use Factory to Create DAO Object
  public class DAOFactory{
    public ArticleDAO getArticleDAO(){
      ...
    }
   ...
  }
Factory is an Singleton

：）

bill1

No, my operation bean encapsuled in PL/SQL.

cinc

呵呵，沒做過，不過應(yīng)該是一樣的。
你先寫個 application （帶 main 的）試試。

：）

pilgram

</pre>;
because the v_cursor is not initialized ,it's an invalidate cursor
please refer to &quot;http://www.csee.umbc.edu/help/oracle8/java.815/a64685/samapp3.htm&quot;
you can change it as following:


oracle's package P_T_Reoprt
create or replace package P_T_Reoprt
type v_cursor is ref cursor return table_name%ROWTYPE&#59;
function T_Reoprt (j varchar2) return v_cursor&#59;
end P_T_Reoprt&#59;