Bean with database outputs only one record 2

[evergreean43]

Earlier this week I posted a JSP Bean that showed null result. I got it working to now it will show a database value but now I want it to show all the records.

In the \Tomcat 5.5\logs it shows all the records but the JSP displays only 1 record which seems to be the last record value.

Please advise what I am doing wrong.

JSP:

<jsp:useBean id="tester" class="colors.Tester"/>
<jsp:setProperty name="tester"  property="lastName" value="*" />
 
<jsp:getProperty name="tester" property="lastName" />

Java class file:

package colors;
import java.io.*;
import java.sql.*;
import java.util.*;  

public class Tester
{   
   protected String lastName; 
   String mquery = "";

public Tester(){}  

public String getLastName()
{   
this.viewDatabaseTable();   
return lastName;
} 

public void setLastName(String aLastName)
{   
lastName = aLastName;
} 

public void viewDatabaseTable()
{ 
    exeQry(mquery);
}  

public void exeQry(String mquery)	
{					
try		
{				
Class.forName("org.gjt.mm.mysql.Driver");    
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/myfirst?user=root&password=aaaaaa");    
Statement stmt = conn.createStatement();    
ResultSet results = stmt.executeQuery("SELECT * from user");			
while(results.next())				
{						
lastName = results.getString("lastName");	
System.out.println(lastName);	
}		
}			
catch(Exception e)			
{				
System.out.println("Exception here.");					
}			
}
}

Output from JSP is:
Jones


The \Tomcat 5.5\logs output shows all the records which is what I want the JSP to show:
Edwardson
Smith
Carson
Jones

 

[sedj]

You iterate through the result set setting 'lastName' to the current result set's value - so you just set the 'lastName' value 4 times - the last of which is "Jones".

You need to to something more like :

lastName += resultSet.get("lastName") +",";

or something similar to build up the strings.

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[evergreean43]

Thanks,

It works and now outputs all my values.

Please advise how I handle the null output.

Here is my output:
nullEdwardson
Smith
Carson
Jones


My attempts to eliminate null didnt work because null still shows up in my output:

ResultSet results = stmt.executeQuery("SELECT * from user where lastName <> null");

I also tried this:

if(lastName != null}
{
    lastName += resultSet.getString("lastName") +"<br>";
}

 

[sedj]

Alter the code as below :

ResultSet results = stmt.executeQuery("SELECT * from user");            
lastName = "";
while(results.next())  {
    lastName += resultSet.getString("lastName") +"<br>";
}
// If no records came back, reset lastName to null
if (lastName.length() == 0) {
  lastName = null;
}

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[feherke]

Hi

You use wrong operator in the SQL statement.

ResultSet results = stmt.executeQuery("SELECT * from user where lastName [red]is not[/red] null");

Feherke.

http://rootshell.be/~feherke/

 

[sedj]

feherke - whilst correct, this will not correct the OP's problem, as the issue is not caused by a null record in the result set - but how 'lastName' variable is set.

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[feherke]

Hi

Yes, now I see it. Sorry, I read the thread in a hurry.

As an excuse, I did not said that my suggestion is a solution. ;-)

Feherke.

http://rootshell.be/~feherke/

 

[evergreean43]

Thanks,

I tried this and still get the null in front of my results.

while(results.next())			
{					
   lastName += results.getString("lastName") +"<br>";
   // If no records came back, reset lastName to null
   if (lastName.length() == 0) 
   {
     lastName = null;
   }	
}

Please advise.

 

[feherke]

Hi

In sedj's code there was a [highlight lightblue]lastName = "";[/highlight] line before the [tt]while[/tt] loop. Did you included that line too ?

Feherke.

http://rootshell.be/~feherke/

 

[evergreean43]

Sorry that was my mistake.

Thanks for all the help in making this work!