解答例 - 実習課題１ - ３．サーブレットの基本２

（実習課題１）

2章の実習課題2を改良しなさい。
データベースへの接続に関する設定（ユーザ名、パスワードなど）は、Webアプリケーション全体の設定に変えること。
解答例

/*
 * DisplayDbTableServlet.java TECHSCORE Java Servlet3章 実習課題1
 * 
 * Copyright (c) 2004 Four-Dimensional Data, Inc.
 */
package com.techscore.servlet.chapter3.exercise1;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class DisplayDbTableServlet extends HttpServlet {

    private static final String[] PARAMS = { "sql1", "sql2", "sql3" };

    private String[] sqls = new String[PARAMS.length];

    private String url = null;

    private String username = null;

    private String password = null;

    private String driverClassName = null;

    private boolean isEmptySqls() {
        for (int i = 0; i < sqls.length; i++) {
            if (sqls[i] == null) {
                return true;
            }
        }
        return false;
    }

    private Connection getConnection() throws SQLException {
        Connection conn = null;
        if (url != null && username != null && password != null
                && driverClassName != null) {
            try {
                Class.forName(driverClassName);
                conn = DriverManager.getConnection(url, username, password);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        }
        return conn;
    }

    private String getHtmlTable(HttpServletRequest request,
            HttpServletResponse response, Connection conn, String sql)
            throws SQLException {
        Statement statement = conn.createStatement();
        ResultSet result = statement.executeQuery(sql);
        StringBuffer buffer = new StringBuffer();
        buffer.append("<table border=\"1\">");
        while (result.next()) {
            buffer.append("<tr><td>").append(result.getInt(1)).append(
                    "</td>");
            buffer.append("<td>").append(result.getString(2))
                    .append("</td>");
            buffer.append("<td>").append(result.getString(3))
                    .append("</td>");
            buffer.append("<td>").append(result.getInt(4)).append(
                    "</td></tr>");
        }
        buffer.append("</table>");
        result.close();
        statement.close();
        return buffer.toString();
    }

    private String getSuitableSql(String param) {
        String sql = null;
        for (int i = 0; i < sqls.length; i++) {
            if (param.equals(PARAMS[i])) {
                sql = sqls[i];
            }
        }
        return sql;
    }

    public void init() throws ServletException {
        ServletContext context = this.getServletContext();

        url = context.getInitParameter("url");
        username = context.getInitParameter("username");
        password = context.getInitParameter("password");
        driverClassName = context.getInitParameter("driverClassName");
        for (int i = 0; i < PARAMS.length; i++) {
            sqls[i] = context.getInitParameter(PARAMS[i]);
        }
    }

    protected void doGet(HttpServletRequest request,
            HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html; charset=Shift_JIS");
        PrintWriter writer = response.getWriter();
        writer.println("<html><head><title>"
                + "Servlet3章Exercise1</title></head><body>");
        if (!this.isEmptySqls()) {
            StringBuffer buffer = new StringBuffer();

            buffer.append("<form action=\"\" method=\"post\">");
            for (int i = 0; i < PARAMS.length; i++) {
                buffer.append("<input type=\"radio\" name=\"sql\" value=\"");
                buffer.append(PARAMS[i]).append("\">");
                buffer.append(PARAMS[i].toUpperCase()).append("<br>");
            }
            buffer.append("<input type=\"submit\" value=\"送信\">");
            buffer.append("</form>");
            writer.println(buffer.toString());
        }
        writer.println("</body></html>");

    }

    protected void doPost(HttpServletRequest request,
            HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html; charset=Shift_JIS");
        PrintWriter writer = response.getWriter();
        writer.println("<html><head><title>"
                + "Servlet3章Exercise1</title></head><body>");
        String param = request.getParameter("sql");
        if (param != null) {
            String sql = getSuitableSql(param);
            Connection conn = null;
            try {
                conn = this.getConnection();
                writer.println(getHtmlTable(request, response, conn, sql));
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        } else {
            writer.println("SQL文を選択してください。<br>");
        }
        writer.println("<a href=\"servlet3-1\">戻る</a></body></html>");
    }
}
↑このページの先頭へ