How to send a ResultSet object in JSP back to HTML (JavaScript)?_问答_开发者

How to send a ResultSet object in JSP back to HTML (JavaScript)?

开发者 https://www.devze.com 2023-01-29 16:18 出处：网络

I have a JSP page that 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.

相关专题：javascript

I have a JSP page that 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.

How do I cast the resultSet object into a javascript object?
How do I send a resultSet object from JSP to HTML? (I mean the s开发者_如何学编程yntax)

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. E.g. 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>