# JV27 課題No.05
###### tags: `課題`
* 期限 7月15日まで
クラスDBより、学籍番号検索
1件検索・MVC
プリントp58,p59を使う
* class_index.html(メニュー追加)
↓
* gakuseki_select.html
↓
* gakuseki_servlet.java(servlet)
↓
* gakuseki_bean.java(bean)
↓
* gakuseki_show.jsp
クラス、出席番号、学籍番号、氏名、生年月日を表示
もしくは、該当者なし
:::success
:heavy_check_mark: 成功！！
:::
:::info
:information_source: リンク
:::
:::warning
:warning: 警告
:::
:::danger
:anger: エラー
:::
:::spoiler gakusek_select.html
```htmlembedded=
<!-- -->
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link
href="https://cdn.jsdelivr.net/npm/tailwindcss@2.1/dist/tailwind.min.css"
rel="stylesheet"
type="text/css"
/>
<link
href="https://cdn.jsdelivr.net/npm/daisyui@0.26.3/dist/full.css"
rel="stylesheet"
type="text/css"
/>
<title>学籍名簿情報</title>
</head>
<body>
<div class="">
<div class="">
<div class="">
学籍名簿 - 検索
</div>
<form action="./servlet/gakuseki_servlet" method="POST">
<div class="">
<label class="">
<input
type="radio"
name="SELECT"
checked
class=""
value="1"
/>
<span class=""></span>
<span>学籍番号検索</span>
</label>
</div>
<input
type="text"
name="GAKUSEKI_NO"
class=""
placeholder="学籍番号"
/>
<div class="">
<label class="">
<input
type="radio"
name="SELECT"
class=""
value="2"
/>
<span class=""></span>
<span>氏名検索</span>
</label>
</div>
<input
type="text"
name="SIMEI_1"
class=""
placeholder="姓"
/>
<input
type="text"
name="SIMEI_2"
class=""
placeholder="名"
/>
<div class="">
<label class="">
<input
type="radio"
name="SELECT"
class=""
value="3"
/>
<span class=""></span>
<span>クラス＆出席番号検索</span>
</label>
</div>
<select
name="CLASS_NO"
class=""
>
<option value="AT11A192" selected>AT11A192</option>
<option value="AT11B203">AT11B203</option>
<option value="CG11A172">CG11A172</option>
<option value="IT11A172">IT11A172</option>
<option value="AT12A165">AT12A165</option>
<option value="AT12B165">AT12B165</option>
<option value="IH12A101">IH12A101</option>
<option value="IW12A185">IW12A185</option>
<option value="AT13A223">AT13A223</option>
<option value="AP13A223">AP13A223</option>
<option value="IH13A223">IH13A223</option>
<option value="IW13A187">IW13A187</option>
<option value="CD13A166">CD13A166</option>
<option value="AP14A226">AP14A226</option>
<option value="AT14A226">AT14A226</option>
<option value="IH14A223">IH14A223</option>
<option value="CT14A187">CT14A187</option>
<option value="IW14A187">IW14A187</option>
<option value="xx14Axx">xx14Axx</option>
</select>
<input
type="text"
name="SYUSSEKI_NO"
class=""
placeholder="出席番号"
/>
<div class="">
<a href="class_index.html" class=""
>戻る</a
>
<button class="">検索</button>
</div>
</form>
</div>
</div>
</body>
</html>
```
:::
:::spoiler gakuseki_servlet.java
```java=
// gakuseki_servlet.java
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class gakuseki_servlet extends HttpServlet {
public void doPost (
HttpServletRequest req,
HttpServletResponse res )
throws ServletException , IOException {
req.setCharacterEncoding("UTF-8");
res.setContentType("text/html;charset=UTF-8");
String selectStr,gakuseki_noStr,simei1_Str,simei2_Str,class_noStr,syusseki_noStr;
try {
nhs00157.gakuseki_bean gakuseki_bean = new nhs00157.gakuseki_bean();
selectStr = req.getParameter("SELECT");
if (selectStr.equals("1")){
gakuseki_noStr = req.getParameter("GAKUSEKI_NO");
gakuseki_bean.setJyouken("gakuseki_no = '" + gakuseki_noStr + "'");
} else if (selectStr.equals("2")){
simei1_Str = req.getParameter("SIMEI_1");
simei2_Str = req.getParameter("SIMEI_2");
gakuseki_bean.setJyouken("simei_1 = '" + simei1_Str + "' and simei_2 = '" + simei2_Str + "'");
} else {
class_noStr = req.getParameter("CLASS_NO");
syusseki_noStr = req.getParameter("SYUSSEKI_NO");
gakuseki_bean.setJyouken("class_no = '" + class_noStr + "' and syusseki_no = '" + syusseki_noStr + "'");
}
gakuseki_bean.DBselect();
req.setAttribute("gakuseki_bean", gakuseki_bean);
ServletContext sc = getServletContext();
sc.getRequestDispatcher("/gakuseki_show.jsp").forward(req, res);
}
catch (Exception ex) {
PrintWriter out;
req.setCharacterEncoding("UTF-8");
res.setContentType("text/html;charset=UTF-8");
out = res.getWriter();
ex.printStackTrace(out);
}
}
}
```
```java=
// gakuseki_bean.java
package nhs00157;
import java.sql.*;
import java.io.*;
public class gakuseki_bean implements Serializable {
final String URL = "jdbc:mysql://localhost/nhs00157db?useUnicode=true&characterEncoding=UTF-8";
final String USER = "miyasan";
final String PASSWORD = "0301";
final String DRIVER = "com.mysql.jdbc.Driver";
Connection con = null;
Statement stmt = null;
int hint_flag;
String class_no,gakuseki_no,syusseki_no,simei_1,simei_2,kana_1,kana_2,umare;
String table = "class_table";
String field = "*";
String jyouken = "";
String sort = "";
public gakuseki_bean() {
super();
}
public void DBselect() {
try {
Class.forName(DRIVER);
con = DriverManager.getConnection(URL,USER,PASSWORD);
stmt = con.createStatement();
Statement stmt = con.createStatement();
StringBuffer query = new StringBuffer();
query.append("select ");
query.append(field);
query.append(" from ");
query.append(table);
query.append(" where ");
query.append(jyouken);
query.append(" ");
query.append(sort);
ResultSet rs = stmt.executeQuery(query.toString());
System.out.println(query);
if (rs.next() == true) {
hint_flag = 1;
class_no = rs.getString("class_no");
gakuseki_no = rs.getString("gakuseki_no");
syusseki_no = rs.getString("syusseki_no");
simei_1 = rs.getString("simei_1");
simei_2 = rs.getString("simei_2");
kana_1 = rs.getString("kana_1");
kana_2 = rs.getString("kana_2");
umare = rs.getString("umare");
} else {
hint_flag = 0;
}
rs.close();
stmt.close();
con.close();
}
catch (SQLException ex) {
ex.printStackTrace();
} catch (Exception ex) {
ex.printStackTrace();
}
}
public void setJyouken(String inJyouken) {
jyouken = inJyouken;
}
public int getHint_flag() {
return hint_flag;
}
public String getClass_no() {
if (class_no.equals("")) return "";
return class_no;
}
public String getGakuseki_no() {
if (gakuseki_no.equals("")) return "";
return gakuseki_no;
}
public String getSyusseki_no() {
if (syusseki_no.equals("")) return "";
return syusseki_no;
}
public String getSimei_1() {
if (simei_1.equals("")) return "";
return simei_1;
}
public String getSimei_2() {
if (simei_2.equals("")) return "";
return simei_2;
}
public String getKana_1() {
if (kana_1.equals("")) return "";
return kana_1;
}
public String getKana_2() {
if (kana_2.equals("")) return "";
return kana_2;
}
public String getUmare() {
if (umare.equals("")) return "";
return umare;
}
}
```
:::
:::spoiler gakuseki_show.jsp
```htmlmixed=
<!-- -->
<%@ page contentType="text/html;charset=UTF-8" %>
<% response.setContentType("text/html;charset=UTF-8"); %>
<html>
<head>
<link
href="https://cdn.jsdelivr.net/npm/tailwindcss@2.1/dist/tailwind.min.css"
rel="stylesheet"
type="text/css"
/>
<link href='https://cdn.jsdelivr.net/npm/daisyui@1.1.1/dist/full.css' rel='stylesheet' type='text/css' />
<title>名簿検索２</title>
</head>
<body>
<jsp:useBean id="gakuseki_bean" scope="request" class="nhs00157.gakuseki_bean"/>
<div class=''>
<div class=''>
名簿検索
</div>
<% if (gakuseki_bean.getHint_flag() == 1) { %>
<div class=''>
<table class=''>
<thead>
<tr>
<th>
クラス
</th>
<th>
出席番号
</th>
<th>
学籍番号
</th>
<th>
氏名（姓）
</th>
<th>
氏名（名）
</th>
<th>
カナ（姓）
</th>
<th>
カナ（名）
</th>
<th>
生年月日
</th>
</tr>
</thead>
<tbody>
<tr>
<td>
<jsp:getProperty name="gakuseki_bean" property="class_no"/>
<%-- <%= gakuseki_bean.getClass_no() %> --%>
</td>
<td>
<jsp:getProperty name="gakuseki_bean" property="syusseki_no"/>
<%-- <%= gakuseki_bean.getGakuseki_no() %> --%>
</td>
<td>
<jsp:getProperty name="gakuseki_bean" property="gakuseki_no"/>
<%-- <%= gakuseki_bean.getSyusseki_no() %> --%>
</td>
<td>
<jsp:getProperty name="gakuseki_bean" property="simei_1"/>
<%-- <%= gakuseki_bean.getSimei_1() %> --%>
</td>
<td>
<jsp:getProperty name="gakuseki_bean" property="simei_2"/>
<%-- <%= gakuseki_bean.getSimei_2() %> --%>
</td>
<td>
<jsp:getProperty name="gakuseki_bean" property="kana_1"/>
<%-- <%= gakuseki_bean.getKana_1() %> --%>
</td>
<td>
<jsp:getProperty name="gakuseki_bean" property="kana_2"/>
<%-- <%= gakuseki_bean.getKana_2() %> --%>
</td>
<td>
<jsp:getProperty name="gakuseki_bean" property="umare"/>
<%-- <%= gakuseki_bean.getUmare() %> --%>
</td>
</tr>
</tbody>
</table>
</div>
<% } else { %>
<h1 class="">該当なし</h1>
<% } %>
<div class=''>
<a href='/JV27/gakuseki_select.html'><button class=''>検索に戻る</button></a>
<a href='/JV27/class_index.html'><button class=''>ホームに戻る</button></a>
</div>
</div>
</body>
</html>
```
:::
## 雑談 (0→やまぴ 1→みや 2→やすい 3→りょうくん 4→アミカ 5→やの)
---
<span style="color: #ff3333">aaa</span>
<span style="text-decoration: underline">aaa</span>
Sign in with Wallet
Connect another wallet