how to send a ResultSet object in jsp back to html (javascript)?
i have a Jsp page which queries a mysql database , i want to send the Resultset object to an html page as a response object ? i need the resultset object to populate a table and a chart.
1.how do i cast the resultSet object into a javascript object ?
i am using get xmlHTTPrequest to call the jsp page
Don't use JSP. Use a Servlet which queries the DB, obtains a List with results and converts it to a JSON string which JS can seamlessly use.
First create a javabean class which represents a single row of the DB table. Eg Product .
public class Product {
private Long id;
private String name;
private String description;
private BigDecimal price;
// Add/generate c'tors, getters, setters and other boilerplate.
}
The create a DAO class which fires the query and maps the ResultSet to a List<Product> .
public class ProductDAO {
// ...
public List<Product> find(String search) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
List<Product> products = new ArrayList<Product>();
try {
connection = database.getConnection();
statement = connection.prepareStatement(SQL_FIND);
statement.setString(1, search);
resultSet = statement.executeQuery();
while (resultSet.next()) {
Product product = new Product();
product.setId(resultSet.getLong("id"));
product.setName(resultSet.getString("name"));
product.setDescription(resultSet.getString("description"));
product.setPrice(resultSet.getBigDecimal("price"));
products.add(product);
}
} finally {
if (resultSet != null) try { resultSet.close(); } catch (SQLException logOrIgnore) {}
if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
}
return products;
}
}
Then create a Servlet class which uses the DAO class to obtain the products and converts it to a JSON string with a little help of Google Gson.
public class ProductServlet extends HttpServlet {
// ...
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
List<Product> products = productDAO.find(request.getParameter("search"));
response.setContentType("application/json");
response.setCharacterEncoding("UTF-8");
response.getWriter().write(new Gson().toJson(products));
} catch (SQLException e) {
throw new ServletException("DB error", e);
}
}
}
Map this servlet in web.xml on an url-pattern of /products and call it in JavaScript as follows (I am using jQuery since it eliminates crossbrowsersensitive boilerplate so that you end up with 10 times less JavaScript code).
<!DOCTYPE html>
<html lang="en">
<head>
<title>SO question 4407861</title>
<script src="http://code.jquery.com/jquery-latest.min.js"></script>
<script>
$(document).ready(function() {
$('#searchform').submit(function() {
$.getJSON("products", $(this).serialize(), function(products) {
var table = $('#resulttable');
$.each(products, function(index, product) {
$('<tr>').appendTo(table)
.append($('<td>').text(product.id))
.append($('<td>').text(product.name))
.append($('<td>').text(product.description))
.append($('<td>').text(product.price));
});
});
return false;
});
});
</script>
</head>
<body>
<form id="searchform">
<input type="text" name="search">
<input type="submit">
</form>
<table id="resulttable"></table>
</body>
</html>
上一篇: JSP中的结果集