How to send the SQL query results to a jsp page?

Question

I have a database with the fields id(number), name(string), address(string). I wrote a java program EmployeeDAO to execute the query. I'm storing it in a ResultSet object "rs". I need to display this result set as a table in a JSP page. How do i send this "rs" to the JSP page? some help?

public class EmployeeDAO {

public _ list() throws Exception {

       try

       {
  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
  String url = "jdbc:odbc:employee_dsn";
        Connection con = DriverManager.getConnection(url);
  Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("Select * from emp_table");
 }
     catch (Exception e)
 { 
            System.out.println(e);
     }
  }

Answer 1

The elegant solution would be to map your resultset to a list of objects. Take a look at springs RowMapper to get an idea of how to handle this.

In your jsp you can than use a <c:forEach/> loop to write out this list.

Answer 2

First of all create Java model class Employee with the same fields as the columns are in the emp_table. For example:

public class Employee {
  private String name;
  private String lastName;
  public void setName(String name) {
    this.name = name;
  }
  public String getName() {
    return this.name;
  }
  public String getLastName() {
    return this.lastName;
  }
  public void setLastName(String lastName) {
    this.lastName = lastName;
  }
}

Then in your method _list() iterate over the result set like this:

public List<Employee> _ list() throws Exception {
     Connection con = null;
     ResultSet rs = null;
     List<Employee> result = new ArrayList<Employee>();
     try
       {
         Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
         String url = "jdbc:odbc:employee_dsn";
         con = DriverManager.getConnection(url);
         Statement stmt = con.createStatement();
         rs = stmt.executeQuery("Select * from emp_table");
         while (rs.next()) {
           Employee emp = new Employee();
          emp.setName(rs.getString("emp_name"));
          emp.setLastName(rs.getString("emp_last_name"));
          result.add(emp);
         }

        }
        catch (Exception e)
        {       
            System.out.println(e);
        } finally {
            if (null != rs) {
              try { rs.close()} catch(Exception ex) {};
            }
            if (null != con) {
              try { con.close()} catch(Exception ex) {};
            }
        }
return result;
  }

In your JSP you may iterate over the collection like this:

<table>
  <c:forEach var="emp" items="${empDao._list}">
    <tr>
      <td>${emp.name}</td>
      <td>${emp.lastName}</td>
    </tr>
  </c:forEach>
</table>