第10章ウェブアプリの例

10.1 郵便番号検索

テーブル:zipsのためのCREATE TABLE文

CREATE TABLE zips(
  jis CHAR(10) DEFAULT '' NOT NULL,
  old_zip CHAR(5) NOT NULL,
  zip CHAR(7) NOT NULL,
  addr1_ruby VARCHAR(10) DEFAULT '' NOT NULL,
  addr2_ruby TEXT NOT NULL,
  addr3_ruby TEXT NOT NULL,
  addr1 VARCHAR(10) DEFAULT '' NOT NULL,
  addr2 TEXT NOT NULL,
  addr3 TEXT NOT NULL,
  addr4 TEXT NOT NULL,
  establishment_ruby TEXT NOT NULL,
  establishment TEXT NOT NULL,
  KEY zip_idx (zip)
) DEFAULT CHARACTER SET utf8;

データのロード

SET NAMES utf8;

LOAD DATA LOCAL INFILE "KEN_ALL_UTF8.CSV" INTO TABLE zips
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
(jis,old_zip,zip,addr1_ruby,addr2_ruby,addr3_ruby,addr1,addr2,addr3);

LOAD DATA LOCAL INFILE "JIGYOSYO_UTF8.CSV" INTO TABLE zips
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
(jis,establishment_ruby,establishment,addr1,addr2,addr3,addr4,zip,old_zip);

-- Windowsのコマンドプロンプトでは以下が必要
SET NAMES cp932;

10.2 GETによる検索

zip.jsp

<?xml version="1.0" encoding="UTF-8" ?>
<%@ page language="java" contentType="text/html; charset=UTF-8" import="java.sql.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>郵便番号検索結果</title>
</head>
<body>
<%
Class.forName("com.mysql.jdbc.Driver").newInstance();
String uri="jdbc:mysql://localhost/mydb?characterEncoding=UTF-8";
Connection conn = DriverManager.getConnection(uri,"test","pass");
String zip=request.getParameter("zip");

PreparedStatement stmt=conn.prepareStatement("SELECT * FROM zips WHERE zip LIKE ?");
stmt.setString(1,zip+"%");

stmt.setMaxRows(100); 
ResultSet rs=stmt.executeQuery();

out.print("<ul>");
while(rs.next())
  out.println("<li>"
    +rs.getString("zip")+": "
    +rs.getString("addr1")
    +rs.getString("addr2")
    +rs.getString("addr3")
    +rs.getString("addr4")
    +rs.getString("establishment")
    +"</li>");
out.print("</ul>");
%>
</body>
</html>

10.4 Ajaxによるインターフェースの改良

zip.html

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<script type="text/javascript" src="prototype.js"></script>
<script type="text/javascript">
function load(code){
  new Ajax.Updater('result','zip.jsp?zip='+code);
}
</script>
<title>郵便番号検索</title>
</head>
<body>
<form action="zip.jsp" method="get">
  <p>
    <input name="zip" value="" type="text" onkeyup="load(this.value);" />
    <input name="submit" value="検索" type="submit" />
  </p>
</form>
<div id='result'></div>
</body>
</html>

ユーザ認証

JDBCRealmを利用するための準備

USE mydb;

CREATE TABLE users (
  user_name VARCHAR(15) NOT NULL PRIMARY KEY,
  user_pass VARCHAR(15) NOT NULL
);

CREATE TABLE user_roles (
  user_name VARCHAR(15) NOT NULL,
  role_name VARCHAR(15) NOT NULL,
  PRIMARY KEY (user_name, role_name)
);

INSERT INTO users (user_name,user_pass) VALUES ('testA','passA');
INSERT INTO user_roles (user_name,role_name) VALUES ('testA','guest');

context.xml

<?xml version="1.0" encoding="UTF-8"?>
<Context path="/myweb" docBase="myweb" reloadable="true">
  <Realm
    className="org.apache.catalina.realm.JDBCRealm"
    connectionURL="jdbc:mysql://localhost/mydb"
    connectionName="test"
    connectionPassword="pass"
    debug="99"
    driverName="com.mysql.jdbc.Driver"
    userTable="users"
    userNameCol="user_name"
    userCredCol="user_pass"
    userRoleTable="user_roles"
    roleNameCol="role_name" />
</Context>

web.xmlに追記する要素

  <security-role>
    <role-name>guest</role-name>
  </security-role>
  <security-constraint>
    <web-resource-collection>
      <web-resource-name>secure pages</web-resource-name>
      <url-pattern>/HelloWorld.jsp</url-pattern>
    </web-resource-collection>
    <auth-constraint>
      <role-name>guest</role-name>
    </auth-constraint>
  </security-constraint>
  <login-config>
    <auth-method>DIGEST</auth-method>
  </login-config>

第10章 ウェブアプリの例

10.1 郵便番号検索

10.2 GETによる検索

10.4 Ajaxによるインターフェースの改良

ユーザ認証

第10章ウェブアプリの例