Hi,

Im trying to display all the rows from a table (im running on Postgres and NetBeans 6.5). I only get one row..

my code:

the actor class

package appsClass;


import java.sql.*;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */


 */
public class Actor
{
    private int actor_id;
    private String lastname;
    private String firstname;
    
 
    public void setActor_id(int actor_id)
    { 
    
        this.actor_id = actor_id;
        
    }
    public int getActor_id()
    { 
        return (this.actor_id);
    }
    
    public void setLastname(String lastname) 
    { 
    
        this.lastname = lastname;
        
    }
    public String getLastname() 
    { 
        return (this.lastname);
    }
    
        
    public void setFirstname(String firstname) 
    { 
    
        this.firstname = firstname;
        
    }
    public String getFirstname() 
    { 
        return (this.firstname);
    }
    
    
    public Actor (ResultSet resultSet) throws SQLException
    {
        this.actor_id = resultSet.getInt("actor_id");
        this.lastname = resultSet.getString("lastname");
        this.firstname = resultSet.getString("firstname");
        
    }
  
}

// Servlet

package servlet;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */



import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import dbconn.dbconn;
import appsClass.Actor;



public class actors extends HttpServlet {

  public void doGet(HttpServletRequest req, HttpServletResponse res)
                               throws ServletException, IOException {

    String qryStr = "";
    Statement stmt = null;
    ResultSet rs = null;

   // res.setContentType("text/html");
    PrintWriter out = res.getWriter();
    Connection conn = null;

       //using movie_resolved view

    try{
      // Get a Connection to the database

     conn = dbconn.getJDBCConnection();

     // Create a Statement object
      stmt = conn.createStatement();

      // Execute an SQL query, get a ResultSet
      qryStr = "SELECT * from actor";
      

      rs = stmt.executeQuery(qryStr); 
      
     if (rs.next())
      {
      

         Actor actor = new Actor (rs);
         actor.setActor_id(rs.getInt("actor_id"));
         actor.setLastname(rs.getString("lastname"));
         actor.setFirstname(rs.getString("firstname"));


        req.setAttribute("actor", actor);
        RequestDispatcher view = req.getRequestDispatcher("actor.jsp");
          view.forward(req, res);
      }
     
     
    }


    catch(ClassNotFoundException e) {
     out.println("Couldn't load database driver: " + e.getMessage());
    }

    catch(SQLException e) {
      out.println("SQLException caught: " + e.getMessage());
    }
    finally {
      // Always close the database connection.
      try {
        if (conn != null) conn.close();
      }
      catch (SQLException ignored) { }
   }
  }
}

the JSP (for views)

<%-- 
--%>
<%@ page language="java" %>
<%@ page import ="java.sql.*;"%>


<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">


<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Actor</title>
    </head>
    
    <body>
        <h2>This is Actor Class</h2>
        
        <table>
        <TR>
            <TD>Actor ID: </TD>
            <TD>${actor.actor_id}</TD>
        </TR>
        
        <TR>
            <TD>Lastname: </TD>
            <TD>${actor.lastname}</TD>
        </TR>
         <TR>
            <TD>Firstname: </TD>
            <TD>${actor.firstname}</TD>
        </TR>
        </table>
        
    </body>
</html>

I want to display list of user in the table.. but couldnt figure it out.. help me..

The issues lie in this section

if (rs.next())
      {
         Actor actor = new Actor (rs);
         actor.setActor_id(rs.getInt("actor_id"));
         actor.setLastname(rs.getString("lastname"));
         actor.setFirstname(rs.getString("firstname"));


        req.setAttribute("actor", actor);
        RequestDispatcher view = req.getRequestDispatcher("actor.jsp");
          view.forward(req, res);
      }

You are reading only once because of "if" statement. These need to be replaced with something that is able to run it multiple times so "while" loop would be correct choice.

Even if you do put while in action there is issue of data collection. I'm little surprised that you not getting error on this

Actor actor = new Actor (rs);
         actor.setActor_id(rs.getInt("actor_id"));
         actor.setLastname(rs.getString("lastname"));
         actor.setFirstname(rs.getString("firstname"));

as your Actor object initialized with ResultSet as argument and later on you overwrite your variables again. On top of that as it is only single object it can contain only one set of values for Actor object. Therefore Actor should be declared as collection Vector or much better ArrayList (easy to iterate through)

Now there is last thing to sort out.

req.setAttribute("actor", actor);
        RequestDispatcher view = req.getRequestDispatcher("actor.jsp");
          view.forward(req, res);

Session declaration and forwarding should be outside of this set of brackets because you want to collect all data, you MUST close database connection and only after this continue with rest of data processing such as session set up and redirecting/forwarding

Have look at this post it should be beneficial to what you doing. In that example MySQL is used, but is minor detail as that can be easily changed. Nevertheless I'm happy to see you using servlets for your project.

The issues lie in this section

if (rs.next())
      {
         Actor actor = new Actor (rs);
         actor.setActor_id(rs.getInt("actor_id"));
         actor.setLastname(rs.getString("lastname"));
         actor.setFirstname(rs.getString("firstname"));


        req.setAttribute("actor", actor);
        RequestDispatcher view = req.getRequestDispatcher("actor.jsp");
          view.forward(req, res);
      }

You are reading only once because of "if" statement. These need to be replaced with something that is able to run it multiple times so "while" loop would be correct choice.

- - Yeah. That would make sense because there is multiple rows.. but what do I do in the while loop?? I tried reading the tutorial but still couldnt get mine working.. Please give me some directions..tqs..

Even if you do put while in action there is issue of data collection. I'm little surprised that you not getting error on this

Actor actor = new Actor (rs);
         actor.setActor_id(rs.getInt("actor_id"));
         actor.setLastname(rs.getString("lastname"));
         actor.setFirstname(rs.getString("firstname"));

as your Actor object initialized with ResultSet as argument and later on you overwrite your variables again. On top of that as it is only single object it can contain only one set of values for Actor object. Therefore Actor should be declared as collection Vector or much better ArrayList (easy to iterate through)

- - It will be helpful if anyone can put sample code with bits of explanation. I really want to do this project myself but with given time frame I'm just worried I cant learn everything from the beginning. I do have Java knowledge but not that expert.. any help is highly appreciated..

Now there is last thing to sort out.

req.setAttribute("actor", actor);
        RequestDispatcher view = req.getRequestDispatcher("actor.jsp");
          view.forward(req, res);

Session declaration and forwarding should be outside of this set of brackets because you want to collect all data, you MUST close database connection and only after this continue with rest of data processing such as session set up and redirecting/forwarding

- - so do I put this after the while loop??

If you downloaded attached code, not just what is showed on site you would found something like this

/*
	 * Get list of all members from provided group
	 */
	public ArrayList<UserBean> getUsersList(String userGroup)
	{
		ArrayList<UserBean> list = new ArrayList<UserBean>();
		Connection conn = getConnection();	
	    if (conn != null) 
	    {
	    	ResultSet rs = null;
	    	PreparedStatement preparedStatement = null;
			//String dbStr=null;
			try
			{
				String strQuery = 
					"SELECT uid, firstName, lastName, email, phone FROM user WHERE uid IN("
					+"SELECT uid FROM usergroup_mapping WHERE groupid=("
					+"SELECT groupid FROM usergroup WHERE groupName=?))";
				preparedStatement = conn.prepareStatement(strQuery);
				preparedStatement.setString(1,userGroup);
				rs = preparedStatement.executeQuery();
				
				
				while(rs.next())
				{
					UserBean user = new UserBean();
					user.setUid(rs.getString("uid"));
					user.setFirstName(rs.getString("firstName"));
					user.setLastName(rs.getString("lastName"));					
					user.setEmail(rs.getString("email"));
					user.setPhone(rs.getInt("phone"));
					list.add(user);
				}				
			}//end of try
			catch(SQLException ex){ex.printStackTrace();}
			finally 
			{
				try 
	        	{
	        		rs.close();
	        		preparedStatement.close();
	        	}
	          	catch (SQLException e) {e.printStackTrace();}
	          	putConnection(conn);	        
	        }//end of finally
	    }//end of if
	    return list;
	}

start your own thread, show some effort, and explain your problem in enough detail that others can make sense of it.

This article has been dead for over six months. Start a new discussion instead.