Printing out info from mysql database

[Rochal Collins]

Using Struts 1.3.8. Have successfully connected to the mysql database usind JNDI datasource. (i think) but I don't know how to print out the information from the database using the struts tlds. Any suggestions?

[Merrill Higginson]

Store the data as a List of JavaBeans in some scope, such as request. Example:

request.setAttribute("people", listOfPeople);

Then you can write something like this in your JSP:

<table>
<logic:iterate id="person" name="people" >
  <tr>
  <td><bean:write name="person" property="name" /></td>
  <td><bean:write name="person" property="address" /></td>
  <td><bean:write name="person" property="phone" /></td>
  </tr>
</logic:iterate>
</table>

[Rochal Collins]

Ok so where would the
request.setAttribute("employee", listOfEmployee);

go?

Sorry I'm new to Struts.....the listOfEmployee would be equal to ???

[Rochal Collins]

Figured it out! Thanks!

[Rochal Collins]

I thought I had it working but it isn't. How do I store the data from the database into a java bean list?

[Merrill Higginson]

First you have to create a new Class following the JavaBean model that represents the data from one row. Example:

public class Person {
  private String name;
  private String address;
  private String phone;
  // getters and setters for the above properties
}

Then in your Action class, instantiate an ArrayList and populate it from the rows in the database. Example:

List listOfPeople = new ArrayList();
 
// get the database connection, create an SQL statement, and execute it to get a ResultSet rs
 
while (rs.next()) {
    Person person = new Person();
    person.setName(rs.getString("NAME"));
    person.setAddress(rs.getString("ADDRESS"));
    person.setPhone(rs.getString("PHONE"));
    listOfPeople.add(person);
}

Once you have this list, you can put it in the request as shown in my last post.

[Rochal Collins]

Nothing is happening.

Here's how I connect to the database:

public class ConnectionBean implements HttpSessionBindingListener{
private Connection connection;
private Statement statement;

private static final String driver="com.mysql.jdbc.Driver";
private static final String dbURL="jdbc:mysql://localhost:3306/employee?autoReconnect=true";
private static final String login="root";
private static final String password="";

public ConnectionBean(){
try{
Class.forName(driver);
connection=DriverManager.getConnection(dbURL, login, password);
statement=connection.createStatement();
}catch (ClassNotFoundException e){
System.err.println("ConnectionBean: driver unavailable.");
connection=null;
}
catch(SQLException e){
System.err.println("ConnectionBean: driver not loaded.");
connection=null;
}
}

public Connection getConnection(){
return connection;
}

public ResultSet executeQuery(String sql) throws SQLException{
return statement.executeQuery(sql);
}

protected void finalize(){
try{
connection.close();
}catch(SQLException e){

}
}

public void valueBound(HttpSessionBindingEvent event) {
System.err.println("ConnectionBean: in the valueBound method.");
try{
if (connection == null || connection.isClosed()){
connection = DriverManager.getConnection(dbURL,login,password);
statement=connection.createStatement();
}
}catch (SQLException e){
connection =null;
}
}

public void valueUnbound(HttpSessionBindingEvent event) {
try{
connection.close();
}catch(SQLException e){

}finally{
connection = null;
}
}

}

my action

EmployeeSearchService service = new EmployeeSearchService();
ArrayList results2;

SearchForm searchForm = (SearchForm) form;

results2=service.listEmp();
searchForm.setResults(results2);
return mapping.getInputForward();

jsp

<logic resent name="searchForm" property="results2">
<table>
<logic:iterate id="e" name="employees" >
<tr>
<td><bean:write name="e" property="name" /></td>
<td><bean:write name="e" property="ssn" /></td>
</tr>
</logic:iterate>
</table>
</logic resent>

EmployeeSearchService

public ArrayList listEmp() throws SQLException{
ArrayList listOfEmployees = new ArrayList();
ConnectionBean cnbean = new ConnectionBean();
String sql="select * from myemploy";
ResultSet rs = cnbean.executeQuery(sql);

while (rs.next()) {
Employee employee = new Employee();
employee.setName(rs.getString("name"));
employee.setSsn(rs.getString("ssn"));
listOfEmployees.add(employee);
//System.out.println(listOfEmployees.add(employee));
}
return listOfEmployees;
}



What am I doing wrong?? I don't even know if its connecting to the DB!

[Merrill Higginson]

I don't even know if its connecting to the DB!

That's easy to fix. Just put System.out.println() statements in your code that print the values of variables at strategic points. You will then be able to see what is going on with your connection logic by looking at the SystemOut log.

I can definitely see a problem with your JSP, though:

Since you put your collection in the form bean, and I put my collection directly in the request object, your JSP code must differ from mine. You must reference the collection in the ActionForm in your logic:iterate statement. It should look something like this:

<logic:iterate id="e" name="searchForm" property="results2" >
<tr>
  <td><bean:write name="e" property="name" /></td>
  <td><bean:write name="e" property="ssn" /></td> 
</tr>
</logic:iterate>

[Rochal Collins]

I've tried system.out.println statements and nothing prints out.

Here are my errors now:

SEVERE: Null component Catalina:type=DataSource,path=/MiniHR,host=localhost,class=javax.sql.DataSource,name="jdbc/TestDB"
SEVERE: Error creating form bean of class com.jamesholmes.miniHR.SearchForm
SEVERE: Servlet.service() for servlet jsp threw exception
javax.servlet.jsp.JspException: Exception creating bean of class com.jamesholmes.miniHR.SearchForm: {1}

[Merrill Higginson]

The significant error here is:
Error creating form bean of class com.jamesholmes.miniHR.SearchForm

Are you sure you spelled the class name right in your struts-config.xml file? Has this class been compiled? does it have a coderanch, no-arguments constructor?

If you can't figure this out, post the relevant portions of your struts-config.xml file.

By the way, I just saw an error in my last post. The property should be "results" not "results2" in both the logic:iterate and the logic:present tags.