Youtube版【中級編Java(24)】データベースアプリケーションの作成(5)―検索結果の格納―

package yurufuwa.prog.sample;

import java.io.IOException;
import java.util.ArrayList;

import jakarta.servlet.RequestDispatcher;
import jakarta.servlet.ServletContext;
import jakarta.servlet.ServletException;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;

public class Sv2 extends HttpServlet {

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

		//都道府県名の一覧を取得
		Kensaku k = new Kensaku();
		k.execute();

		ArrayList<Todofuken> todofukenList = k.getTodofukenList();

		//結果をリクエストにセットして、JSPにフォワード
		req.setAttribute("todofuken_list", todofukenList);

		ServletContext sc = getServletContext();
		RequestDispatcher rd = sc.getRequestDispatcher("/WEB-INF/jsp/out.jsp");
		rd.forward(req, resp);
	}
}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

package yurufuwa.prog.sample;

import java.io.IOException;

import java.util.ArrayList;

import jakarta.servlet.RequestDispatcher;

import jakarta.servlet.ServletContext;

import jakarta.servlet.ServletException;

import jakarta.servlet.http.HttpServlet;

import jakarta.servlet.http.HttpServletRequest;

import jakarta.servlet.http.HttpServletResponse;

public class Sv2 extends HttpServlet {

@Override

protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

//都道府県名の一覧を取得

Kensaku k = new Kensaku();

k.execute();

ArrayList<Todofuken> todofukenList = k.getTodofukenList();

//結果をリクエストにセットして、JSPにフォワード

req.setAttribute("todofuken_list", todofukenList);

ServletContext sc = getServletContext();

RequestDispatcher rd = sc.getRequestDispatcher("/WEB-INF/jsp/out.jsp");

rd.forward(req, resp);

}

package yurufuwa.prog.sample;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import javax.naming.InitialContext;
import javax.sql.DataSource;

public class Kensaku {

	private ArrayList<Todofuken> todofukenList = null;

	public void execute() {

		todofukenList = new ArrayList<Todofuken>();

		Connection conn = null;

		try {
			//DBに接続
			InitialContext ctx = new InitialContext();
			DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/mysql");
			conn = ds.getConnection();

			//SQLを発行
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from todofuken");

			//結果を取得
			while(rs.next()) {

				Todofuken t = new Todofuken();
				t.setKenCode(rs.getString(1));
				t.setKenName(rs.getString(2));
				t.setYomigana(rs.getString(3));

				todofukenList.add(t);
			}
			rs.close();
			stmt.close();

		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			try {
				//接続を閉じる
				conn.close();
			} catch(Exception e) {
				
			}
		}
	}

	public ArrayList<Todofuken> getTodofukenList() {
		return todofukenList;
	}

}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

package yurufuwa.prog.sample;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Statement;

import java.util.ArrayList;

import javax.naming.InitialContext;

import javax.sql.DataSource;

public class Kensaku {

private ArrayList<Todofuken> todofukenList = null;

public void execute() {

todofukenList = new ArrayList<Todofuken>();

Connection conn = null;

try {

//DBに接続

InitialContext ctx = new InitialContext();

DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/mysql");

conn = ds.getConnection();

//SQLを発行

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery("select * from todofuken");

//結果を取得

while(rs.next()) {

Todofuken t = new Todofuken();

t.setKenCode(rs.getString(1));

t.setKenName(rs.getString(2));

t.setYomigana(rs.getString(3));

todofukenList.add(t);

}

rs.close();

stmt.close();

} catch(Exception e) {

e.printStackTrace();

} finally {

try {

//接続を閉じる

conn.close();

} catch(Exception e) {

}

public ArrayList<Todofuken> getTodofukenList() {

return todofukenList;

}

package yurufuwa.prog.sample;

public class Todofuken {

	private String kenCode = null;
	private String kenName = null;
	private String yomigana = null;

	public String getKenCode() {
		return kenCode;
	}
	public void setKenCode(String kenCode) {
		this.kenCode = kenCode;
	}
	public String getKenName() {
		return kenName;
	}
	public void setKenName(String kenName) {
		this.kenName = kenName;
	}
	public String getYomigana() {
		return yomigana;
	}
	public void setYomigana(String yomigana) {
		this.yomigana = yomigana;
	}
}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

package yurufuwa.prog.sample;

public class Todofuken {

private String kenCode = null;

private String kenName = null;

private String yomigana = null;

public String getKenCode() {

return kenCode;

}

public void setKenCode(String kenCode) {

this.kenCode = kenCode;

}

public String getKenName() {

return kenName;

}

public void setKenName(String kenName) {

this.kenName = kenName;

}

public String getYomigana() {

return yomigana;

}

public void setYomigana(String yomigana) {

this.yomigana = yomigana;

}

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.util.ArrayList" %>
<%@ page import="yurufuwa.prog.sample.Todofuken" %>
<%
ArrayList<Todofuken> todofukenList = 
	(ArrayList<Todofuken>)request.getAttribute("todofuken_list");
%>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>都道府県一覧</title>
<style>table, th, td { border-collapse: collapse; border: 1px black solid; }</style>
</head>
<body>

<h2>都道府県一覧</h2>

<table>
<tr><th>都道府県コード</th><th>都道府県名</th><th>読み仮名</th></tr>
<% for(Todofuken t : todofukenList) { %>
	<tr>
		<td><%= t.getKenCode() %></td>
		<td><%= t.getKenName() %></td>
		<td><%= t.getYomigana() %></td>
	</tr>
<% } %>
</table>
</body>
</html>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

<%@ page language="java" contentType="text/html; charset=UTF-8"

pageEncoding="UTF-8"%>

<%@ page import="java.util.ArrayList" %>

<%@ page import="yurufuwa.prog.sample.Todofuken" %>

<%

ArrayList<Todofuken> todofukenList =

(ArrayList<Todofuken>)request.getAttribute("todofuken_list");

%>

<!DOCTYPE html>

<html>

<head>

<title>都道府県一覧</title>

</head>

<body>

<h2>都道府県一覧</h2>

<table>

<tr><th>都道府県コード</th><th>都道府県名</th><th>読み仮名</th></tr>

<% for(Todofuken t : todofukenList) { %>

<tr>

</tr>

<% } %>

</table>

</body>

</html>

package yurufuwa.prog.sample;

import java.io.IOException;
import java.util.ArrayList;

import jakarta.servlet.RequestDispatcher;
import jakarta.servlet.ServletContext;
import jakarta.servlet.ServletException;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;

public class Sv2 extends HttpServlet {

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

		//都道府県名の一覧を取得
		Kensaku k = new Kensaku();
		k.execute();

		ArrayList<Todofuken> todofukenList = k.getTodofukenList();

		//結果をリクエストにセットして、JSPにフォワード
		req.setAttribute("todofuken_list", todofukenList);

		ServletContext sc = getServletContext();
		RequestDispatcher rd = sc.getRequestDispatcher("/WEB-INF/jsp/out.jsp");
		rd.forward(req, resp);
	}
}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

package yurufuwa.prog.sample;

import java.io.IOException;

import java.util.ArrayList;

import jakarta.servlet.RequestDispatcher;

import jakarta.servlet.ServletContext;

import jakarta.servlet.ServletException;

import jakarta.servlet.http.HttpServlet;

import jakarta.servlet.http.HttpServletRequest;

import jakarta.servlet.http.HttpServletResponse;

public class Sv2 extends HttpServlet {

@Override

protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

//都道府県名の一覧を取得

Kensaku k = new Kensaku();

k.execute();

ArrayList<Todofuken> todofukenList = k.getTodofukenList();

//結果をリクエストにセットして、JSPにフォワード

req.setAttribute("todofuken_list", todofukenList);

ServletContext sc = getServletContext();

RequestDispatcher rd = sc.getRequestDispatcher("/WEB-INF/jsp/out.jsp");

rd.forward(req, resp);

}

package yurufuwa.prog.sample;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import javax.naming.InitialContext;
import javax.sql.DataSource;

public class Kensaku {

	private ArrayList<Todofuken> todofukenList = null;

	public void execute() {

		todofukenList = new ArrayList<Todofuken>();

		Connection conn = null;

		try {
			//DBに接続
			InitialContext ctx = new InitialContext();
			DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/mysql");
			conn = ds.getConnection();

			//SQLを発行
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from todofuken");

			//結果を取得
			while(rs.next()) {

				Todofuken t = new Todofuken(
					rs.getString(1),
					rs.getString(2),
					rs.getString(3)
				);

				todofukenList.add(t);
			}
			rs.close();
			stmt.close();

		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			try {
				//接続を閉じる
				conn.close();
			} catch(Exception e) {
				
			}
		}
	}

	public ArrayList<Todofuken> getTodofukenList() {
		return todofukenList;
	}

}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

package yurufuwa.prog.sample;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Statement;

import java.util.ArrayList;

import javax.naming.InitialContext;

import javax.sql.DataSource;

public class Kensaku {

private ArrayList<Todofuken> todofukenList = null;

public void execute() {

todofukenList = new ArrayList<Todofuken>();

Connection conn = null;

try {

//DBに接続

InitialContext ctx = new InitialContext();

DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/mysql");

conn = ds.getConnection();

//SQLを発行

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery("select * from todofuken");

//結果を取得

while(rs.next()) {

Todofuken t = new Todofuken(

rs.getString(1),

rs.getString(2),

rs.getString(3)

);

todofukenList.add(t);

}

rs.close();

stmt.close();

} catch(Exception e) {

e.printStackTrace();

} finally {

try {

//接続を閉じる

conn.close();

} catch(Exception e) {

}

public ArrayList<Todofuken> getTodofukenList() {

return todofukenList;

}

package yurufuwa.prog.sample;

public record Todofuken(String kenCode, String kenName, String yomigana) {

}

1

2

3

4

5

package yurufuwa.prog.sample;

public record Todofuken(String kenCode, String kenName, String yomigana) {

}

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.util.ArrayList" %>
<%@ page import="yurufuwa.prog.sample.Todofuken" %>
<%
ArrayList<Todofuken> todofukenList = 
	(ArrayList<Todofuken>)request.getAttribute("todofuken_list");
%>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>都道府県一覧</title>
<style>table, th, td { border-collapse: collapse; border: 1px black solid; }</style>
</head>
<body>

<h2>都道府県一覧</h2>

<table>
<tr><th>都道府県コード</th><th>都道府県名</th><th>読み仮名</th></tr>
<% for(Todofuken t : todofukenList) { %>
	<tr>
		<td><%= t.kenCode() %></td>
		<td><%= t.kenName() %></td>
		<td><%= t.yomigana() %></td>
	</tr>
<% } %>
</table>
</body>
</html>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

<%@ page language="java" contentType="text/html; charset=UTF-8"

pageEncoding="UTF-8"%>

<%@ page import="java.util.ArrayList" %>

<%@ page import="yurufuwa.prog.sample.Todofuken" %>

<%

ArrayList<Todofuken> todofukenList =

(ArrayList<Todofuken>)request.getAttribute("todofuken_list");

%>

<!DOCTYPE html>

<html>

<head>

<title>都道府県一覧</title>

</head>

<body>

<h2>都道府県一覧</h2>

<table>

<tr><th>都道府県コード</th><th>都道府県名</th><th>読み仮名</th></tr>

<% for(Todofuken t : todofukenList) { %>

<tr>

</tr>

<% } %>

</table>

</body>

</html>

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="https://jakarta.ee/xml/ns/jakartaee" xmlns:web="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="https://jakarta.ee/xml/ns/jakartaee https://jakarta.ee/xml/ns/jakartaee/web-app_5_0.xsd" id="WebApp_ID" version="5.0">
  <display-name>testWeb</display-name>

  <servlet>
    <description></description>
    <display-name>Sv2</display-name>
    <servlet-name>Sv2</servlet-name>
    <servlet-class>yurufuwa.prog.sample.Sv2</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>Sv2</servlet-name>
    <url-pattern>/sv2</url-pattern>
  </servlet-mapping>

</web-app>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

<?xml version="1.0" encoding="UTF-8"?>

<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="https://jakarta.ee/xml/ns/jakartaee" xmlns:web="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="https://jakarta.ee/xml/ns/jakartaee https://jakarta.ee/xml/ns/jakartaee/web-app_5_0.xsd" id="WebApp_ID" version="5.0">

<display-name>testWeb</display-name>

<display-name>Sv2</display-name>

<servlet-name>Sv2</servlet-name>

<servlet-class>yurufuwa.prog.sample.Sv2</servlet-class>

</servlet>

<servlet-mapping>

<servlet-name>Sv2</servlet-name>

<url-pattern>/sv2</url-pattern>

</servlet-mapping>

</web-app>

<?xml version="1.0" encoding="UTF-8"?>
<Context> 
	<Resource
		name="jdbc/mysql" 
		auth="Container"
		type="javax.sql.DataSource"
		factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
		maxActive="5" maxIdle="5" initialSize="5"
		username="yuruku" password="fuwatto"
		driverClassName="com.mysql.cj.jdbc.Driver"
		url="jdbc:mysql://localhost/yuruku"/>
</Context>

1

2

3

4

5

6

7

8

9

10

11

12