Is it possible to return a ResultSet to work with in JSTL?

Question

is it possible to return ResultSet variable to JSTL foreach tag? I am getting null point error and for the wrong reason, it's saying that db2.MyServ class doesn't exist even though its right there. anyone know what i'm doing wrong and how to itterate over my ResultSet rs on jstl?

MyServ2 class(imports etc omitted)

     package db2;

public class MyServ2 extends HttpServlet {
    private static final long serialVersionUID = 1L;
       private DBClass db;
       private ResultSet rs;

    public MyServ2() {
        super();
        db = new DBClass();
        db.dbConnect("jdbc:oracle:thin:@elanweb:1510:xxxxx", "xxxxx", "xxx");

    }


    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {


        rs = db.getResultSet(request.getParameter("query"));
        try {
            while(rs.next()){
                System.out.println(rs.getString(1).toString());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }


    public ResultSet getRs()
    {
        return rs;
    }

}

index.jsp

    <%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>

    <%@ page import="db2.MyServ2" %>
    <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>

<html>
<head>
</head>
<body>
successful login
<jsp:useBean id="myserv2" class="db2.MyServ2"/>

<c:if test="${myserv2.rs.Next()}">
<c:forEach var="person" items="${myserv2.rs}">
<c:out value="${myserv2.rs.string(1).toString()}"></c:out>
</c:forEach>
</c:if>
</body>
</html>

I've created a bean and saved strings to it. When i call them from my MyServ2 class for debugging, they work fine, but when i call them from my webpage as jstl they return null as if the bean is no populated. Does everything reset as soon as i redirect back to the webpage?

<jsp:useBean id="mybean" class="beans.UserBean"></jsp:useBean>

<c:out value="${mybean.name}"></c:out><br></br>

added in MyServ class the following

rs = db.getResultSet(request.getParameter("query"));
    try {
        while(rs.next()){
            mybean.SetName(rs.getString(1).toString());
            mybean.Setsurname( rs.getString(2).toString());
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }

See also: http://stackoverflow.com/questions/2280034/jsp-helper-class-for-printing-content and http://stackoverflow.com/questions/1727603/places-where-java-beans-used — BalusC, Nov 26 '10 at 14:21
Don't save your bean at the servlet, please: concurrent calls to the servlet will overwrite your values!. Use request.setAttribute("name", bean); at the servlet, and access it via EL at the JSP... — Tomas Narros, Nov 26 '10 at 15:13
@Tom. I don't understand. Could you write an example please. — 124697, Nov 26 '10 at 15:18
Edited my anwser. Take a look on it for how to send a value to the request. As you'll see, no "myBean" property at the serlvet class, but a new instance every time you need it. — Tomas Narros, Nov 26 '10 at 15:21
I would like to answer, but there's too much wrong with the code that it's necessary to redo everything. See the links I provided to get the idea how to do it properly. Do **above all** *not* pass the SQL query string around as request parameter! This puts the doors *wide* open to [SQL injection attacks](http://unixwiz.net/techtips/sql-injection.html). — BalusC, Nov 26 '10 at 15:21
@user521180, BalusC gave you 2 important links where you could refer to. The edited post is a **very bad** practice. — Buhake Sindi, Nov 26 '10 at 15:22
I was going to introduce the **DAO Pattern** as a reference guide but I'm going to refrain from it. — Buhake Sindi, Nov 26 '10 at 15:24
@Tom how do I access the bean from the jsp page now? @BalusC @Elite My programming is terrible i've just started. but I will be reading up more about the correct way of doing things starting with the links suggested. thanks — 124697, Nov 26 '10 at 15:37
Thank you both. I will try to understand it better over the weekend. — 124697, Nov 26 '10 at 17:16

Tomas Narros · Accepted Answer · 2010-11-26T15:47:55.193

This is not a good idea. Not at all.

Where do you close the Resultset? And the Connection? You should provide a reliable way to release this resources, which otherwise could cause serious problems at your app.

Moreover, setting a connection as an instance value at an HttpServlet is generally considered as a bad practice, because you have to keep it alive for all the Servlet live. Also, if there are communication issues, you'll need to restart your application to make your servlet reconnect. Not to talk about saving the resultset at serlvet instance level, and all the concurrency issues it can cause.

Mapping your rs rows at a JavaBean to be used at the JSP won't be so hard. And try to refactorize your code for a proper database connection handling.

EDIT: At your last snippet of code, I see you still save data at the servlet instance. This can drive to concurrency problems. Check this:

Resultset rs=null; //declare a local variable
try {
    //wrong code get the query from the request parameter! 
    // rs = db.getResultSet(request.getParameter("query"));
    String query="select col from table where a='b'"; // whatever
    rs = db.getResultSet(query);
    //just one value? no need of while
    if(rs.next()){
        MyBean bean=new MyBean();
        bean.setName(rs.getString(1));
        bean.setSurname(rs.getString(2));
        //here is where you put your bean to the request
        request.setAttribute("myBean", bean);
    }
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    //Don't forget closing your Rs
    if(rs!=null) {rs.close();}
}

And then, using JSTL you can already access the request attrbitue, very similar as you last JSP example, using the name set at the setAttribute method:

<c:out value="${myBean.name}" /><br></br>
<c:out value="${myBean.surname}" /><br></br>

Buhake Sindi · Answer 2 · 2010-11-29T12:36:34.220

My simple answer is no, for the various reasons:

ResultSet doesn't conform to JavaBeans specification (it has no getters/setters method and it's not serializable).

Rather, populate your ResultSet into a bean and use JSTL to retrieve data from the bean.

For your edited post, I suggest scrapping it all together (as BalusC commented, it can lead to SQL Injection) and follow the JavaBeans spefication.

This is an example of what I meant:

JavaBean user:

public class UserEntity implements Serializable {

 private String firstName;
 private String middleName = "";
 private String lastName;
 private Gender gender;
 private String emailAddress;
 private Date birthDate;
        private boolean searchable = false;

        //Getters and Setters here...

}

From MySQLUserDAO, I mapped my entity (javabean) from ResultSet.

protected UserEntity mapEntity(ResultSet rs) throws SQLException {
  // TODO Auto-generated method stub
  UserEntity user = null;

  if (rs != null) {
   user = new UserEntity();

   user.setId(rs.getLong("USER_ID"));
   user.setFirstName(rs.getString("FIRST_NAME"));
   user.setMiddleName(rs.getString("MIDDLE_NAME"));
   user.setLastName(rs.getString("LAST_NAME"));
   user.setEmailAddress(rs.getString("EMAIL_ADDRESS"));

   String gender = rs.getString("GENDER");
   if ("M".equals(gender)) {
    user.setGender(Gender.MALE);
   } else if ("F".equals(gender)) {
    user.setGender(Gender.FEMALE);
   }

   user.setBirthDate(rs.getDate("DOB"));
   user.setCreationDate(rs.getDate("CREATION_DATE"));
                        user.setSearchable(rs.getBoolean("SEARCHABLE"));
  }

  return user;
 }

And finally, the retrieve() method (from MySQLUserDAO).

public UserEntity retrieve(Long id) throws DAOException {
  // TODO Auto-generated method stub
  PreparedStatement ps = null;
  ResultSet rs = null;
  UserEntity user = null;

  try {
   ps = getConnection().prepareStatement(SQL_RETRIEVE);
   ps.setLong(1, id);
   rs = ps.executeQuery();
   if (rs != null && rs.next()) {
    user = mapEntity(rs);
   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   throw new DAOException(e);
  } finally {
   try {
    close(rs, ps);
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    logger.error("Error closing statement or resultset.", e);
   }
  }

  return user;
 }

Now, to use UserEntity to JSP, I do....

UserEntity user = MySQLUserDAO.retrieve(userId); //Pseudocode....
request.setAttribute("user", user);

and using JSTL, I can do:

<c:out value="${user.firstName}">

where user is the attribute name from the request (which returns UserEntity user) and firstName calls user.getFirstName() (the method from UserEntity).

Hope you follow BalusC's example.

There's however JSTL `sql` taglib, but its use is discouraged for other purposes than quick prototyping/demo. — BalusC, Nov 26 '10 at 14:17
@BalusC, oh yes! I forgot about the taglib. Thanks for a refresher. — Buhake Sindi, Nov 26 '10 at 14:18

Is it possible to return a ResultSet to work with in JSTL?

2 Answers2