Is it possible to return a ResultSet to work with in JSTL?
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();
}
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>
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.
加载中,请稍侯......
精彩评论