teratail
回答率
85.35%
teratail header banner
teratail header banner
質問するログイン新規登録
トップに関する質問【JSP・Servlet・SQL】DBからの結果表示がうまくできない

編集履歴

質問編集履歴

2

内容修正

2020/09/07 02:44

投稿

ysda
ysda

スコア65

test CHANGED
File without changes
test CHANGED
@@ -12,11 +12,9 @@
12
12
 
13
13
  |:--|:--:|--:|--:|--:|
14
14
 
15
+
16
+
15
- |11|開発太郎|カイハツタロウ|3|2|
17
+ ![イメージ説明](a39ef81fb24921d61307daf02b46329a.png)
16
-
17
- |12|開発次郎|カイハツジロウ|3|2|
18
-
19
- |13|開発三郎|カイハツサブロウ|1|3|
20
18
 
21
19
 
22
20
 
@@ -26,15 +24,9 @@
26
24
 
27
25
  |:--|:--:|
28
26
 
27
+
28
+
29
- |1|国語|
29
+ ![イメージ説明](e269a7801e1c47c89f3b6b511f7ad615.png)
30
-
31
- |2|数学|
32
-
33
- |3|英語|
34
-
35
- |4|理科|
36
-
37
- |5|社会|
38
30
 
39
31
 
40
32
 
@@ -44,25 +36,9 @@
44
36
 
45
37
  |:--|:--:|--:|--:|
46
38
 
47
- |1|1|60|2020-03-02|
39
+
48
-
49
- |1|2|70|2020-03-03|
40
+
50
-
51
- |1|3|80|2020-03-04|
41
+ ![イメージ説明](a49c41edae5c83ef6c88d096b6bf40af.png)
52
-
53
- |1|4|90|2020-03-05|
54
-
55
- |1|5|100|2020-03-06|
56
-
57
- |2|1|50|2020-03-02|
58
-
59
- |2|2|60|2020-03-03|
60
-
61
- |2|3|90|2020-03-04|
62
-
63
- |2|4|40|2020-03-05|
64
-
65
- |2|5|30|2020-03-06|
66
42
 
67
43
 
68
44
 

1

内容の追加

2020/09/07 02:44

投稿

ysda
ysda

スコア65

test CHANGED
File without changes
test CHANGED
@@ -1,64 +1,78 @@
1
- ### 前提・実現したいこと
1
+ ### 発生している問題・エラーメッセージ
2
-
3
-
4
-
2
+
3
+
4
+
5
- 現在、生徒が受けた試験の結果をDBから表示するプログラムをJavaで作成しています。
5
+ 試験の成績表を出力するプログラムの作成中で、以下のテーブルを使用しています。
6
-
7
- DBの中にテーブルが3つ存在し、以下がその内容です。
8
6
 
9
7
 
10
8
 
11
9
  studentテーブル
12
10
 
13
- |student_id|student_name|student_furigana|school_year|school_class|
11
+ |student_id（生徒ID）|student_name（生徒名）|student_furigana（生徒カナ）|school_year（学年）|school_class（クラス）|
14
12
 
15
13
  |:--|:--:|--:|--:|--:|
16
14
 
17
- |生徒ID|生徒名|生徒カナ|学年|クラス|
15
+ |11|開発太郎|カイハツタロウ|3|2|
16
+
17
+ |12|開発次郎|カイハツジロウ|3|2|
18
+
19
+ |13|開発三郎|カイハツサブロウ|1|3|
18
20
 
19
21
 
20
22
 
21
23
  subjectテーブル
22
24
 
23
- |subject_id|subject_name|
25
+ |subject_id（教科ID）|subject_name（教科名）|
24
26
 
25
27
  |:--|:--:|
26
28
 
29
+ |1|国語|
30
+
31
+ |2|数学|
32
+
33
+ |3|英語|
34
+
27
- |教科ID|教科名|
35
+ |4|理科|
36
+
37
+ |5|社会|
28
38
 
29
39
 
30
40
 
31
41
  test_resultsテーブル
32
42
 
33
- |student_id|subject_id|score|test_day|
43
+ |student_id（外部キー）|subject_id（外部キー）|score（得点）|test_day（試験日）|
34
44
 
35
45
  |:--|:--:|--:|--:|
36
46
 
37
- |生徒ID|教科ID|得点|学年|
47
+ |1|1|60|2020-03-02|
48
+
38
-
49
+ |1|2|70|2020-03-03|
50
+
39
-
51
+ |1|3|80|2020-03-04|
52
+
40
-
53
+ |1|4|90|2020-03-05|
41
-
42
-
54
+
43
- ### 発生している問題・エラーメッセージ
55
+ |1|5|100|2020-03-06|
44
-
45
-
46
-
47
- 上記3つのテーブルを結合し、成績表をブラウザ上に出力したいのですが、
56
+
48
-
49
- うまくそちらができない状態になっております。
57
+ |2|1|50|2020-03-02|
58
+
50
-
59
+ |2|2|60|2020-03-03|
60
+
51
-
61
+ |2|3|90|2020-03-04|
62
+
52
-
63
+ |2|4|40|2020-03-05|
64
+
65
+ |2|5|30|2020-03-06|
66
+
67
+
68
+
53
- 下記のように、一つの成績表を作成するのにあたって、
69
+ 下記のような成績表を出力したいと考えています。
54
-
55
- student,test_resultsを結合し、そこから学年・クラス・生徒ID・名前を出力したものと3つのテーブルを作成し、教科・得点・試験日（・更新ボタン・削除ボタン）を出力したものを組み合わせたいと考えています。
56
70
 
57
71
  ![イメージ説明](81b77451a4862b8ea4ecdd97d6ceefdd.png)](77080b8ae922464ee958865e1c678e8c.png)
58
72
 
59
73
 
60
74
 
61
- 実際にソースを組んだところ、最初の成績表はうまく出力されているのですが、
75
+ 最初の成績表はうまく出力されているのですが、
62
76
 
63
77
  2つ目以降の成績表は上部の学年・クラス・生徒ID・名前のみしか出力されていない状態になっております。
64
78
 
@@ -76,55 +90,17 @@
76
90
 
77
91
 
78
92
 
79
- なぜそうなるのか、またその解決方法につきましてご教示いただけますと幸いです。
80
-
81
-
82
-
83
93
  ### 該当のソースコード
84
94
 
85
-
86
-
87
95
  TestResultsRegistEditServlet.java
88
96
 
97
+ test-results-regist.jsp（後述）の検索押下時に実行
98
+
89
99
  ```ここに言語を入力
90
100
 
91
101
 
92
102
 
93
- package servlet;
103
+ //インポート省略
94
-
95
-
96
-
97
- import java.io.IOException;
98
-
99
- import java.sql.Connection;
100
-
101
- import java.sql.DriverManager;
102
-
103
- import java.sql.PreparedStatement;
104
-
105
- import java.sql.ResultSet;
106
-
107
- import java.sql.SQLException;
108
-
109
-
110
-
111
- import javax.servlet.ServletException;
112
-
113
- import javax.servlet.http.HttpServlet;
114
-
115
- import javax.servlet.http.HttpServletRequest;
116
-
117
- import javax.servlet.http.HttpServletResponse;
118
-
119
-
120
-
121
- import beans.StudentBeans;
122
-
123
- import beans.SubjectBeans;
124
-
125
- import beans.TestResultsBeans;
126
-
127
-
128
104
 
129
105
  public class TestResultsRegistEditServlet extends HttpServlet{
130
106
 
@@ -136,14 +112,8 @@
136
112
 
137
113
  ResultSet result2 = null;
138
114
 
139
- ResultSet preStudentResult = null;
140
-
141
- ResultSet preSubjectResult = null;
142
-
143
115
  ResultSet preTestDayResult = null;
144
116
 
145
-
146
-
147
117
  String user = "suser";
148
118
 
149
119
  String password = "spass";
@@ -164,14 +134,6 @@
164
134
 
165
135
 
166
136
 
167
- StudentBeans student = new StudentBeans(request);
168
-
169
- SubjectBeans subject = new SubjectBeans(request);
170
-
171
- TestResultsBeans testResults = new TestResultsBeans(request);
172
-
173
-
174
-
175
137
  String student_id = request.getParameter("student_id");
176
138
 
177
139
  String student_name = request.getParameter("student_name");
@@ -202,186 +164,158 @@
202
164
 
203
165
 
204
166
 
205
- String preStudentSql = "select * from student";
167
+ String preTestDaySql = "select distinct test_day from test_results order by test_day";
206
-
168
+
207
- ps = conn.prepareStatement(preStudentSql);
169
+ ps = conn.prepareStatement(preTestDaySql);
208
170
 
209
171
  ps.execute();
210
172
 
211
- preStudentResult = ps.executeQuery();
173
+ preTestDayResult = ps.executeQuery();
212
-
174
+
213
- request.setAttribute("preStudentResult", preStudentResult);
175
+ request.setAttribute("preTestDayResult", preTestDayResult);
176
+
177
+
178
+
214
-
179
+ StringBuffer testResultsSql = new StringBuffer();
180
+
215
-
181
+ testResultsSql.append("select distinct student.school_year, student.school_class, student.student_id, student.student_name ");
182
+
216
-
183
+ testResultsSql.append("from test_results inner join student on student.student_id = test_results.student_id inner join subject on subject.subject_id = test_results.subject_id ");
184
+
217
- String preSubjectSql = "select * from subject";
185
+ testResultsSql.append("where test_day like '%");
186
+
218
-
187
+ testResultsSql.append(test_day + "%'");
188
+
189
+
190
+
191
+ StringBuffer sql2 = new StringBuffer();
192
+
193
+ sql2.append("select test_results.student_id, test_results.subject_id, subject.subject_name, score, test_day from test_results ");
194
+
195
+ sql2.append("inner join student on student.student_id = test_results.student_id ");
196
+
197
+ sql2.append("inner join subject on subject.subject_id = test_results.subject_id ");
198
+
199
+ sql2.append("where test_day like '%");
200
+
201
+ sql2.append(test_day + "%'");
202
+
203
+
204
+
205
+ if(!school_year.equals("") && !school_class.equals("")) {//学年クラスともに入力済み
206
+
207
+ testResultsSql.append("and student.student_id in (select student_id from student where school_year = ");
208
+
209
+ testResultsSql.append(school_year);
210
+
211
+ testResultsSql.append(" and school_class = ");
212
+
213
+ testResultsSql.append(school_class);
214
+
215
+ testResultsSql.append(") order by student.student_id, subject.subject_id");
216
+
217
+
218
+
219
+ sql2.append("and student.student_id in (select student_id from student where school_year = ");
220
+
221
+ sql2.append(school_year);
222
+
223
+ sql2.append(" and school_class = ");
224
+
225
+ sql2.append(school_class);
226
+
227
+ sql2.append(") order by student.student_id, subject.subject_id");
228
+
229
+ } else if(school_year.equals("") || school_class.equals("")) {
230
+
231
+ if(school_year.equals("") && school_class.equals("")) {
232
+
233
+ testResultsSql.append(" order by student.student_id, subject.subject_id");
234
+
235
+ sql2.append(" order by student.student_id, subject.subject_id");
236
+
237
+ } else if(!school_year.equals("")) {
238
+
239
+ testResultsSql.append("and student.student_id in (select student_id from student where school_year = ");
240
+
241
+ testResultsSql.append(school_year);
242
+
243
+ testResultsSql.append(") order by student.student_id, subject.subject_id");
244
+
245
+
246
+
247
+ sql2.append("and student.student_id in (select student_id from student where school_year = ");
248
+
249
+ sql2.append(school_year);
250
+
251
+ sql2.append(") order by student.student_id, subject.subject_id");
252
+
253
+ } else if(!school_class.equals("")) {
254
+
255
+ testResultsSql.append("and student.student_id in (select student_id from student where school_class = ");
256
+
257
+ testResultsSql.append(school_class);
258
+
259
+ testResultsSql.append(") order by student.student_id, subject.subject_id");
260
+
261
+
262
+
263
+ sql2.append("and student.student_id in (select student_id from student where school_class = ");
264
+
265
+ sql2.append(school_class);
266
+
267
+ sql2.append(") order by student.student_id, subject.subject_id");
268
+
269
+ }
270
+
271
+ }
272
+
273
+
274
+
219
- ps = conn.prepareStatement(preSubjectSql);
275
+ ps = conn.prepareStatement(new String(testResultsSql));
276
+
277
+ System.out.println(ps);
220
278
 
221
279
  ps.execute();
222
280
 
223
- preSubjectResult = ps.executeQuery();
281
+ result = ps.executeQuery();
224
-
225
- request.setAttribute("preSubjectResult", preSubjectResult);
282
+
226
-
227
-
228
-
229
- String preTestDaySql = "select distinct test_day from test_results order by test_day";
283
+
230
-
284
+
231
- ps = conn.prepareStatement(preTestDaySql);
285
+ ps = conn.prepareStatement(new String(sql2));
286
+
287
+ System.out.println(ps);
232
288
 
233
289
  ps.execute();
234
290
 
235
- preTestDayResult = ps.executeQuery();
291
+ result2 = ps.executeQuery();
236
-
292
+
293
+
294
+
237
- request.setAttribute("preTestDayResult", preTestDayResult);
295
+ request.setAttribute("result", result);
238
-
239
-
240
-
241
- StringBuffer testResultsSql = new StringBuffer();
296
+
242
-
243
- testResultsSql.append("select distinct student.school_year, student.school_class, student.student_id, student.student_name ");
244
-
245
- testResultsSql.append("from test_results inner join student on student.student_id = test_results.student_id inner join subject on subject.subject_id = test_results.subject_id ");
246
-
247
- testResultsSql.append("where test_day like '%");
248
-
249
- testResultsSql.append(test_day + "%'");
250
-
251
-
252
-
253
- StringBuffer sql2 = new StringBuffer();
254
-
255
- sql2.append("select test_results.student_id, test_results.subject_id, subject.subject_name, score, test_day from test_results ");
256
-
257
- sql2.append("inner join student on student.student_id = test_results.student_id ");
258
-
259
- sql2.append("inner join subject on subject.subject_id = test_results.subject_id ");
260
-
261
- sql2.append("where test_day like '%");
262
-
263
- sql2.append(test_day + "%'");
297
+ request.setAttribute("result2", result2);
264
-
265
-
266
-
267
- if(!school_year.equals("") && !school_class.equals("")) {//学年クラスともに入力済み
298
+
268
-
269
- testResultsSql.append("and student.student_id in (select student_id from student where school_year = ");
299
+ request.getRequestDispatcher("test-results-regist.jsp").forward(request, response);
300
+
301
+
302
+
270
-
303
+ } catch(Exception e) {
304
+
271
- testResultsSql.append(school_year);
305
+ e.printStackTrace();
306
+
272
-
307
+ } finally {
308
+
309
+ try {
310
+
311
+ conn.close();
312
+
313
+ } catch (SQLException e) {
314
+
273
- testResultsSql.append(" and school_class = ");
315
+ System.out.println("MySQLのクローズに失敗しました。");
274
-
275
- testResultsSql.append(school_class);
276
-
277
- testResultsSql.append(") order by student.student_id, subject.subject_id");
278
-
279
-
280
-
281
- sql2.append("and student.student_id in (select student_id from student where school_year = ");
282
-
283
- sql2.append(school_year);
284
-
285
- sql2.append(" and school_class = ");
286
-
287
- sql2.append(school_class);
288
-
289
- sql2.append(") order by student.student_id, subject.subject_id");
290
-
291
- } else if(school_year.equals("") || school_class.equals("")) {
292
-
293
- if(school_year.equals("") && school_class.equals("")) {
294
-
295
- testResultsSql.append(" order by student.student_id, subject.subject_id");
296
-
297
- sql2.append(" order by student.student_id, subject.subject_id");
298
-
299
- } else if(!school_year.equals("")) {
300
-
301
- testResultsSql.append("and student.student_id in (select student_id from student where school_year = ");
302
-
303
- testResultsSql.append(school_year);
304
-
305
- testResultsSql.append(") order by student.student_id, subject.subject_id");
306
-
307
-
308
-
309
- sql2.append("and student.student_id in (select student_id from student where school_year = ");
310
-
311
- sql2.append(school_year);
312
-
313
- sql2.append(") order by student.student_id, subject.subject_id");
314
-
315
- } else if(!school_class.equals("")) {
316
-
317
- testResultsSql.append("and student.student_id in (select student_id from student where school_class = ");
318
-
319
- testResultsSql.append(school_class);
320
-
321
- testResultsSql.append(") order by student.student_id, subject.subject_id");
322
-
323
-
324
-
325
- sql2.append("and student.student_id in (select student_id from student where school_class = ");
326
-
327
- sql2.append(school_class);
328
-
329
- sql2.append(") order by student.student_id, subject.subject_id");
330
-
331
- }
332
316
 
333
317
  }
334
318
 
335
-
336
-
337
- ps = conn.prepareStatement(new String(testResultsSql));
338
-
339
- System.out.println(ps);
340
-
341
- ps.execute();
342
-
343
- result = ps.executeQuery();
344
-
345
-
346
-
347
- ps = conn.prepareStatement(new String(sql2));
348
-
349
- System.out.println(ps);
350
-
351
- ps.execute();
352
-
353
- result2 = ps.executeQuery();
354
-
355
-
356
-
357
- request.setAttribute("result", result);
358
-
359
- request.setAttribute("result2", result2);
360
-
361
- request.setAttribute("school_year_searched", school_year);
362
-
363
- request.setAttribute("school_class_searched", school_class);
364
-
365
- request.getRequestDispatcher("test-results-regist.jsp").forward(request, response);
366
-
367
-
368
-
369
- } catch(Exception e) {
370
-
371
- e.printStackTrace();
372
-
373
- } finally {
374
-
375
- try {
376
-
377
- conn.close();
378
-
379
- } catch (SQLException e) {
380
-
381
- System.out.println("MySQLのクローズに失敗しました。");
382
-
383
- }
384
-
385
319
  }
386
320
 
387
321
 
@@ -432,6 +366,132 @@
432
366
 
433
367
 
434
368
 
369
+ <div id="search" class="tab-pane active">
370
+
371
+ <h2>試験結果検索</h2>
372
+
373
+ <div class="input-form">
374
+
375
+ <form action="testResultsRegistEdit" method="post">
376
+
377
+ <table class="input-form-table">
378
+
379
+ <tr>
380
+
381
+ <td>試験日</td>
382
+
383
+ <td>
384
+
385
+ <select name="test_day">
386
+
387
+ <option value="">すべての試験日</option>
388
+
389
+ <%
390
+
391
+ if(preTestDayResult != null) {
392
+
393
+ %>
394
+
395
+ <%
396
+
397
+ while(preTestDayResult.next()) {
398
+
399
+ %>
400
+
401
+ <option value="<%=preTestDayResult.getString(1) %>"><%=preTestDayResult.getString(1) %></option>
402
+
403
+ <%
404
+
405
+ }
406
+
407
+ %>
408
+
409
+ <%
410
+
411
+ }
412
+
413
+ %>
414
+
415
+ </select>
416
+
417
+ </td>
418
+
419
+ </tr>
420
+
421
+ <tr>
422
+
423
+ <td>学年</td>
424
+
425
+ <td>
426
+
427
+ <%if(school_year_searched != null) {%>
428
+
429
+ <input type="text" id="year" name="school_year" maxlength='10' value="<%=school_year_searched %>"
430
+
431
+ placeholder="学年（半角数字）">
432
+
433
+ <%} else {%>
434
+
435
+ <input type="text" id="year" name="school_year" maxlength='10'
436
+
437
+ placeholder="学年（半角数字）">
438
+
439
+ <%} %>
440
+
441
+ </td>
442
+
443
+ </tr>
444
+
445
+ <tr>
446
+
447
+ <td>クラス</td>
448
+
449
+ <td>
450
+
451
+ <%if(school_class_searched != null) {%>
452
+
453
+ <input type="text" id="class" name="school_class" maxlength='10' value="<%=school_class_searched %>"
454
+
455
+ placeholder="クラス（半角数字）">
456
+
457
+ <%} else {%>
458
+
459
+ <input type="text" id="class" name="school_class" maxlength='10'
460
+
461
+ placeholder="クラス（半角数字）">
462
+
463
+ <%} %>
464
+
465
+ </td>
466
+
467
+ </tr>
468
+
469
+ </table>
470
+
471
+ <div class="submit-button text-center">
472
+
473
+ <input type="hidden" name="mode" value="search">
474
+
475
+ <input type="submit" value="検索" >
476
+
477
+ </div>
478
+
479
+ </form>
480
+
481
+ </div>
482
+
483
+
484
+
485
+ <%
486
+
487
+ ResultSet result = (ResultSet) request.getAttribute("result");
488
+
489
+ ResultSet result2 = (ResultSet) request.getAttribute("result2");
490
+
491
+ %>
492
+
493
+
494
+
435
495
  <%if(result != null && result2 != null) {%>
436
496
 
437
497
  <% while(result.next()) {%>
@@ -494,9 +554,7 @@
494
554
 
495
555
  <%while(result2.next()) {%>
496
556
 
497
- <!--resultの生徒IDとresult2の生徒IDが一致しているかのチェック --!>
498
-
499
- <%if(result2.getString(1).equals(result.getString(3))) { %>
557
+ <%if(result2.getString(1).equals(result.getString(3))) { %>
500
558
 
501
559
  <tr>
502
560
 
@@ -524,6 +582,8 @@
524
582
 
525
583
  <%} %>
526
584
 
585
+ </div>
586
+
527
587
  //以下省略
528
588
 
529
589
  ```