2024.9.20(週五)

记得关月亮發表於2024-09-22

完善昨天的查的部分

<%@ page import="java.sql.*" %>
<%@ page import="util.DBConnection" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"
         pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>統計結果</title>
</head>
<body>
<h2>統計結果</h2>
<%
    String criteria = request.getParameter("criteria");
    String sql = "";
    if ("tech_activity_type".equals(criteria)) {
        sql = "SELECT tech_activity_type, COUNT(*) as count FROM tech_requirements GROUP BY tech_activity_type";
    } else if ("institution_type".equals(criteria)) {
        sql = "SELECT institution_type, COUNT(*) as count FROM tech_requirements GROUP BY institution_type";
    }

    Connection con = null;
    Statement stmt = null;
    ResultSet res = null;
    try {
        con = DBConnection.getConnection();
        stmt = con.createStatement();
        res = stmt.executeQuery(sql);
        if (res.isBeforeFirst()) {
%>
<table border="1">
    <tr>
        <th>型別</th>
        <th>數量</th>
    </tr>
    <%
        while (res.next()) {
            if ("tech_activity_type".equals(criteria)) {
                out.println("<tr><td>" + res.getString("tech_activity_type") + "</td><td>" + res.getInt("count") + "</td></tr>");
            } else if ("institution_type".equals(criteria)) {
                out.println("<tr><td>" + res.getString("institution_type") + "</td><td>" + res.getInt("count") + "</td></tr>");
            }
        }
    %>
</table>
<%
        } else {
            out.println("<p>沒有找到符合條件的記錄。</p>");
        }
    } catch (Exception e) {
        e.printStackTrace();
        out.println("<p>錯誤資訊:" + e.getMessage() + "</p>");
    } finally {
        if (res != null) {
            try {
                res.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
%>
</body>
</html>