Skip to content

S
servlets
Switch branch/tag
Find file
Normal viewHistoryPermalink
consultajsp.jsp 2.03 KB
Newer Older
consulta SQL en jsp
10224579
 
Oscar Enrique Gonzalez Escurra committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 
<%@ page import="java.sql.*" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Consulta</title>
</head>
<body>
    <%
        Connection connection = null;
        try {
            Class.forName("org.postgresql.Driver");
            connection = DriverManager
                    .getConnection("jdbc:postgresql://localhost:5432/bootcamp_market", "postgres", "postgres");
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
            System.exit(0);
        }
        try {
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("select a.nombre, apellido, count(b.cliente_id) Cantidad_factura from cliente a " +
                                "inner join factura b " +
                                "on a.id=b.cliente_id " +
                                "group by a.nombre, a.apellido " +
                                "order by Cantidad_factura desc;");
    %>
            <table border="1">
                <tr>
                    <th>Nombre</th>
                    <th>Apellido</th>
                    <th>Cantidad de Facturas</th>
                </tr>
    <%
            while (rs.next()) {
                String nombre = rs.getString("nombre");
                String apellido = rs.getString("apellido");
                int cantidad = rs.getInt("Cantidad_factura");
    %>
                <tr>
                    <td><%= nombre %></td>
                    <td><%= apellido %></td>
                    <td><%= cantidad %></td>
                </tr>
    <%
            }
            rs.close();
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    %>
    </table>
</body>
</html>