Struts を利用した Web アプリケーションの練習問題(解答編・その 2)

今回は DAO 編と View 編です。データベースへのアクセスについては、今回は Commons DBCP を利用するやり方を採用しましたが、context.xml を使うやり方を採用した人は過去の記事をもとに読み替えてください(context.xml を使う場合は SettingUtil.java と jdbc.properties は不要)。

DAO 編

SettingUtil.java

package jp.mydns.akanekodou.util;

import java.util.Properties;
import java.io.InputStream;
import java.io.IOException;

public class SettingUtil {
    private static SettingUtil inst = new SettingUtil();
    private static final String PREFNAME = "jdbc.properties";
    private Properties prop;

    private SettingUtil() {
        prop = new Properties();
        InputStream in = Thread.currentThread().getContextClassLoader().getResourceAsStream(PREFNAME);
        try {
            prop.load(in);
        } catch(IOException ie) {
            ie.printStackTrace();
        }
    }

    public static String getProperty(String key) {
        return inst.prop.getProperty(key);
    }
}

jdbc.properties

jdbc.driver.classname = com.mysql.jdbc.Driver
jdbc.driver.url = jdbc:mysql://localhost:3306/(database_name)
jdbc.driver.user = (user_name)
jdbc.driver.pass = (password)

括弧内は適宜読み替えてください。

DaoUtil.java

package jp.mydns.akanekodou.dao;

import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;

import jp.mydns.akanekodou.util.SettingUtil;

public class DaoUtil {
    private DataSource source;
    private static DaoUtil inst = new DaoUtil();

    private static final String JDBC_DRIVER = "jdbc.driver.classname";
    private static final String JDBC_URL = "jdbc.driver.url";
    private static final String JDBC_USER = "jdbc.driver.user";
    private static final String JDBC_PASS = "jdbc.driver.pass";

    private DaoUtil() {
        source = createDataSource();
    }

    public static DataSource getSource() {
        return inst.source;
    }

    private DataSource createDataSource() {
        BasicDataSource bds = new BasicDataSource();
        bds.setDriverClassName(SettingUtil.getProperty(JDBC_DRIVER));
        bds.setUrl(SettingUtil.getProperty(JDBC_URL));
        bds.setUsername(SettingUtil.getProperty(JDBC_USER));
        bds.setPassword(SettingUtil.getProperty(JDBC_PASS));
        return bds;
    }
}

CityDAO.java

package jp.mydns.akanekodou.dao;

import java.sql.*;
import javax.sql.DataSource;

import jp.mydns.akanekodou.model.*;

public class CityDAO {
    private static final String BASESQL = "SELECT * FROM major_city NATURAL JOIN district";
    private static final String ALL = BASESQL + " ORDER BY city_id";
    private static final String SELECT = BASESQL + " WHERE city_id = ?";

    private DataSource source;

    public CityDAO() {
        source = DaoUtil.getSource();
    }

    public City find(int id) throws SQLException {
        City city = new City();

        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        try {
            con = source.getConnection();
            pst = con.prepareStatement(SELECT);
            pst.setInt(1, id);
            rs = pst.executeQuery();

            if(rs.next()) {
                city = getCity(rs);
            }
        } catch(SQLException se) {
            se.printStackTrace();
            throw se;
        } finally {
            if(rs != null) rs.close();
            if(pst != null) pst.close();
            if(con != null) con.close();
        }

        return city;
    }

    public CityList all() throws SQLException {
        CityList list = new CityList();
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            con = source.getConnection();
            stmt = con.createStatement();
            rs = stmt.executeQuery(ALL);

            while(rs.next()) {
                list.add(getCity(rs));
            }
        } catch(SQLException se) {
            se.printStackTrace();
            throw se;
        } finally {
            if(rs != null) rs.close();
            if(stmt != null) stmt.close();
            if(con != null) con.close();
        }

        return list;
    }

    private City getCity(ResultSet rs) throws SQLException {
        City city = new City();

        city.setCityId(rs.getInt("city_id"));
        city.setCityName(rs.getString("city_name"));
        city.setPrefName(rs.getString("pref_name"));
        city.setDesignatedDay(rs.getDate("designated_day"));
        city.setArea(rs.getDouble("area"));
        city.setPopulation(rs.getInt("population"));
        city.setDstName(rs.getString("dst_name"));

        return city;
    }
}

View 編

list.jsp

<%@ page contentType="text/html; charset=utf-8" pageEncoding="utf-8" %>
<%@ taglib uri="http://struts.apache.org/tags-html" prefix="html" %>
<%@ taglib uri="http://struts.apache.org/tags-bean" prefix="bean" %>
<%@ taglib uri="http://struts.apache.org/tags-logic" prefix="logic" %>
<!doctype html>
<html:html>
<head>
    <title>日本の政令指定都市一覧</title>
    <link rel="stylesheet" href="css/list.css" type="text/css">
</head>
<body>
<bean:define id="citylist" name="citylist" scope="request" type="jp.mydns.akanekodou.model.CityList" />
    <table>
        <caption>日本の政令指定都市一覧</caption>
        <thead>
            <tr>
                <th id="no">No.</th>
                <th id="pref">都道府県</th>
                <th id="name">都市名</th>
                <th id="button"></th>
            </tr>
        </thead>
        <tbody>
        <logic:iterate id="city" name="citylist" property="list" scope="request" indexId="index">
            <tr class="tr<%= index % 2 %>">
                <td><bean:write name="city" property="cityId" format="#" /></td>
                <td><bean:write name="city" property="prefName" /></td>
                <td><bean:write name="city" property="cityName" /></td>
                <td class="td1">
                    <html:form action="detail.do" method="post">
                        <div>
                            <html:hidden property="id" value="${pageScope.city.cityId}" />
                            <html:submit value="詳細" />
                        </div>
                    </html:form>
                </td>
            </tr>
        </logic:iterate>
        </tbody>
    </table>
</body>
</html:html>

途中の EL 式は私もはまったところです。ここは pageScope を使うのが正解です。

detail.jsp

<%@ page contentType="text/html; charset=utf-8" pageEncoding="utf-8" %>
<%@ taglib uri="http://struts.apache.org/tags-html" prefix="html" %>
<%@ taglib uri="http://struts.apache.org/tags-bean" prefix="bean" %>
<!doctype html>
<html:html>
<bean:define id="city" name="city" scope="request" type="jp.mydns.akanekodou.model.City" />
<head>
    <title><bean:write name="city" property="cityName" />の詳細</title>
    <link rel="stylesheet" href="css/detail.css" type="text/css">
</head>
<body>

    <table>
        <caption><bean:write name="city" property="cityName" />のデータ</caption>
        <tr>
            <th>指定日</th>
            <td><bean:write name="city" property="designatedDay" format="yyyy 年 M 月 d 日" /></td>
        </tr>
        <tr>
            <th>地方</th>
            <td><bean:write name="city" property="dstName" /></td>
        </tr>
        <tr>
            <th>都道府県</th>
            <td><bean:write name="city" property="prefName" /></td>
        </tr>
        <tr>
            <th>面積</th>
            <td><bean:write name="city" property="area" format="#,###.##" />km&sup2;</td>
        </tr>
        <tr>
            <th>人口</th>
            <td><bean:write name="city" property="population" format="#,###,###" />人</td>
        </tr>
    </table>
    <p><html:link href="list.do">一覧に戻る</html:link></p>
    <p>※ブラウザの「戻る」ボタンは使わないでください。</p>
</body>
</html:html>

error.html

<!doctype html>
<html>
<head>
    <title>Error Page</title>
</head>
<body>
    <h1>エラー</h1>
    <p>このページに直接アクセスすることはできません。</p>
    <p><a href="list.do">一覧に戻る</a></p>
    <p>※ブラウザの「戻る」ボタンは使わないでください。</p>
</body>
</html>

css/list.css (再掲)

table {
    margin : auto;
    border : 1px outset black
}

caption {
    font-size : 20pt;
    color : #008b8b
}

thead {
    background-color : #008b8b;
    color : #e6e6fa;
    font-size : 14pt
}

th { border : 1px inset black }
th#no { width : 30px }
th#pref { width : 100px }
th#name { width : 120px }
th#button { width : 80px }

tr.tr0 { background-color : #b0e0e6 }
tr.tr1 { background-color : #e6e6fa }

td {
    text-align : left;
    border : 1px inset black
}
td.td1 { text-align : center }

css/detail.css (再掲)

table { margin : auto }

caption {
    font-size : 20pt;
    color : #6a5acd
}

th, td { text-align : left }

th {
    background-color : #6a5acd;
    color : #e6e6fa;
    font-size : 14pt
}

td {
    background-color : #e6e6fa;
    color : #6a5acd;
    font-size : 14pt;
    width : 180px
}

p { text-align : center }

後は Controller だけですね !