Youtube版【中級編Java(30)】データベースアプリケーションの作成(11)―SQLのエスケープ―

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 {

		//検索条件を表示
		ServletContext sc = getServletContext();
		RequestDispatcher rd = sc.getRequestDispatcher("/WEB-INF/jsp/in.jsp");
		rd.forward(req, resp);
	}

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

		//検索条件を取得
		String kenName = req.getParameter("txtKenName");

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

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

			//結果をリクエストにセット
			req.setAttribute("ken_name", kenName);
			req.setAttribute("todofuken_list", todofukenList);

			//検索結果を表示
			ServletContext sc = getServletContext();
			RequestDispatcher rd = sc.getRequestDispatcher("/WEB-INF/jsp/out.jsp");
			rd.forward(req, resp);
		} catch(Exception e) {
			//エラーを表示
			ServletContext sc = getServletContext();
			RequestDispatcher rd = sc.getRequestDispatcher("/WEB-INF/jsp/err.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

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

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 {

//検索条件を表示

ServletContext sc = getServletContext();

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

rd.forward(req, resp);

}

@Override

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

//検索条件を取得

String kenName = req.getParameter("txtKenName");

try {

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

Kensaku k = new Kensaku();

k.execute(kenName);

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

//結果をリクエストにセット

req.setAttribute("ken_name", kenName);

req.setAttribute("todofuken_list", todofukenList);

//検索結果を表示

ServletContext sc = getServletContext();

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

rd.forward(req, resp);

} catch(Exception e) {

//エラーを表示

ServletContext sc = getServletContext();

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

rd.forward(req, resp);

}

package yurufuwa.prog.sample;

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

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

public class Kensaku {

	private ArrayList<Todofuken> todofukenList = null;

	public void execute(String kenName) throws Exception {

		Connection conn = null;

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

			//SQLを発行（完全一致で検索）
			PreparedStatement pstmt = conn.prepareStatement(
					"SELECT * FROM todofuken WHERE ken_name = ?"
			);
			pstmt.setString(1, kenName);

			ResultSet rs = pstmt.executeQuery();

			todofukenList = new ArrayList<Todofuken>();

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

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

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

		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} 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

62

63

64

65

66

package yurufuwa.prog.sample;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.util.ArrayList;

import javax.naming.InitialContext;

import javax.sql.DataSource;

public class Kensaku {

private ArrayList<Todofuken> todofukenList = null;

public void execute(String kenName) throws Exception {

Connection conn = null;

try {

//DBに接続

InitialContext ctx = new InitialContext();

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

conn = ds.getConnection();

//SQLを発行（完全一致で検索）

PreparedStatement pstmt = conn.prepareStatement(

"SELECT * FROM todofuken WHERE ken_name = ?"

);

pstmt.setString(1, kenName);

ResultSet rs = pstmt.executeQuery();

todofukenList = new ArrayList<Todofuken>();

//結果を取得

while(rs.next()) {

Todofuken t = new Todofuken(

rs.getString(1),

rs.getString(2),

rs.getString(3)

);

todofukenList.add(t);

}

rs.close();

pstmt.close();

} catch(Exception e) {

e.printStackTrace();

throw e;

} 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"%>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>都道府県一覧</title>
</head>
<body>

<h2>検索条件</h2>

<form action="./sv2" method="post">
	検索条件：
	<input type="text" name="txtKenName" /><br />
	<input type="submit" value="検索" />
</form>

</body>
</html>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

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

pageEncoding="UTF-8"%>

<!DOCTYPE html>

<html>

<head>

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

</head>

<body>

検索条件：

</form>

</body>

</html>

<%@ 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");
String kenName = (String)request.getAttribute("ken_name");
%>

<!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>

<h3>「<%= kenName %>」の検索結果</h3>

<% if(todofukenList != null && todofukenList.size() > 0){ %>
<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>
<% } else { %>
検索結果はありません。
<% } %>
</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

33

34

35

36

37

38

39

<%@ 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");

String kenName = (String)request.getAttribute("ken_name");

%>

<!DOCTYPE html>

<html>

<head>

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

</head>

<body>

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

<h3>「<%= kenName %>」の検索結果</h3>

<% if(todofukenList != null && todofukenList.size() > 0){ %>

<table>

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

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

<tr>

</tr>

<% } %>

</table>

<% } else { %>

検索結果はありません。

<% } %>

</body>

</html>

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

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

検索でエラーが発生しました。

</body>
</html>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

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

pageEncoding="UTF-8"%>

<!DOCTYPE html>

<html>

<head>

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

</head>

<body>

検索でエラーが発生しました。

</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>

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 {

		//検索条件を表示
		ServletContext sc = getServletContext();
		RequestDispatcher rd = sc.getRequestDispatcher("/WEB-INF/jsp/in.jsp");
		rd.forward(req, resp);
	}

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

		//検索条件を取得
		String kenName = req.getParameter("txtKenName");

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

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

			//結果をリクエストにセット
			req.setAttribute("ken_name", kenName);
			req.setAttribute("todofuken_list", todofukenList);

			//検索結果を表示
			ServletContext sc = getServletContext();
			RequestDispatcher rd = sc.getRequestDispatcher("/WEB-INF/jsp/out.jsp");
			rd.forward(req, resp);
		} catch(Exception e) {
			//エラーを表示
			ServletContext sc = getServletContext();
			RequestDispatcher rd = sc.getRequestDispatcher("/WEB-INF/jsp/err.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

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

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 {

//検索条件を表示

ServletContext sc = getServletContext();

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

rd.forward(req, resp);

}

@Override

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

//検索条件を取得

String kenName = req.getParameter("txtKenName");

try {

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

Kensaku k = new Kensaku();

k.execute(kenName);

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

//結果をリクエストにセット

req.setAttribute("ken_name", kenName);

req.setAttribute("todofuken_list", todofukenList);

//検索結果を表示

ServletContext sc = getServletContext();

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

rd.forward(req, resp);

} catch(Exception e) {

//エラーを表示

ServletContext sc = getServletContext();

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

rd.forward(req, resp);

}

package yurufuwa.prog.sample;

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

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

public class Kensaku {

	private ArrayList<Todofuken> todofukenList = null;

	public void execute(String kenName) throws Exception {

		Connection conn = null;

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

			//SQLを発行（部分一致で検索）
			PreparedStatement pstmt = conn.prepareStatement(
					"SELECT * FROM todofuken WHERE ken_name LIKE ?"
			);
			pstmt.setString(1, "%" + kenName + "%");

			ResultSet rs = pstmt.executeQuery();

			todofukenList = new ArrayList<Todofuken>();

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

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

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

		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} 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

62

63

64

65

66

package yurufuwa.prog.sample;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.util.ArrayList;

import javax.naming.InitialContext;

import javax.sql.DataSource;

public class Kensaku {

private ArrayList<Todofuken> todofukenList = null;

public void execute(String kenName) throws Exception {

Connection conn = null;

try {

//DBに接続

InitialContext ctx = new InitialContext();

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

conn = ds.getConnection();

//SQLを発行（部分一致で検索）

PreparedStatement pstmt = conn.prepareStatement(

"SELECT * FROM todofuken WHERE ken_name LIKE ?"

);

pstmt.setString(1, "%" + kenName + "%");

ResultSet rs = pstmt.executeQuery();

todofukenList = new ArrayList<Todofuken>();

//結果を取得

while(rs.next()) {

Todofuken t = new Todofuken(

rs.getString(1),

rs.getString(2),

rs.getString(3)

);

todofukenList.add(t);

}

rs.close();

pstmt.close();

} catch(Exception e) {

e.printStackTrace();

throw e;

} 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"%>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>都道府県一覧</title>
</head>
<body>

<h2>検索条件</h2>

<form action="./sv2" method="post">
	検索条件：
	<input type="text" name="txtKenName" /><br />
	<input type="submit" value="検索" />
</form>

</body>
</html>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

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

pageEncoding="UTF-8"%>

<!DOCTYPE html>

<html>

<head>

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

</head>

<body>

検索条件：

</form>

</body>

</html>

<%@ 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");
String kenName = (String)request.getAttribute("ken_name");
%>

<!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>

<h3>「<%= kenName %>」の検索結果</h3>

<% if(todofukenList != null && todofukenList.size() > 0){ %>
<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>
<% } else { %>
検索結果はありません。
<% } %>
</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

33

34

35

36

37

38

39

<%@ 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");

String kenName = (String)request.getAttribute("ken_name");

%>

<!DOCTYPE html>

<html>

<head>

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

</head>

<body>

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

<h3>「<%= kenName %>」の検索結果</h3>

<% if(todofukenList != null && todofukenList.size() > 0){ %>

<table>

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

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

<tr>

</tr>

<% } %>

</table>

<% } else { %>

検索結果はありません。

<% } %>

</body>

</html>

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

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

検索でエラーが発生しました。

</body>
</html>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

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

pageEncoding="UTF-8"%>

<!DOCTYPE html>

<html>

<head>

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

</head>

<body>

検索でエラーが発生しました。

</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>

package yurufuwa.prog.sample;

import java.io.IOException;

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 Sv3 extends HttpServlet {

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

		//データ追加ページを表示
		ServletContext sc = getServletContext();
		RequestDispatcher rd = sc.getRequestDispatcher("/WEB-INF/jsp/in.jsp");
		rd.forward(req, resp);
	}

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

		//入力パラメーターを取得
		String kenCode = req.getParameter("txtKenCode");
		String kenName = req.getParameter("txtKenName");
		String yomigana = req.getParameter("txtYomigana");

		try {
			//データの追加
			Tsuika t = new Tsuika();
			t.execute(kenCode, kenName, yomigana);

			int updateRows = t.getUpdateRows();

			//結果をリクエストにセット
			req.setAttribute("ken_code", kenCode);
			req.setAttribute("ken_name", kenName);
			req.setAttribute("yomigana", yomigana);
			req.setAttribute("update_rows", updateRows);

			//結果を表示
			ServletContext sc = getServletContext();
			RequestDispatcher rd = sc.getRequestDispatcher("/WEB-INF/jsp/out.jsp");
			rd.forward(req, resp);
		} catch(Exception e) {
			//エラーを表示
			ServletContext sc = getServletContext();
			RequestDispatcher rd = sc.getRequestDispatcher("/WEB-INF/jsp/err.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

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

package yurufuwa.prog.sample;

import java.io.IOException;

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 Sv3 extends HttpServlet {

@Override

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

//データ追加ページを表示

ServletContext sc = getServletContext();

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

rd.forward(req, resp);

}

@Override

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

//入力パラメーターを取得

String kenCode = req.getParameter("txtKenCode");

String kenName = req.getParameter("txtKenName");

String yomigana = req.getParameter("txtYomigana");

try {

//データの追加

Tsuika t = new Tsuika();

t.execute(kenCode, kenName, yomigana);

int updateRows = t.getUpdateRows();

//結果をリクエストにセット

req.setAttribute("ken_code", kenCode);

req.setAttribute("ken_name", kenName);

req.setAttribute("yomigana", yomigana);

req.setAttribute("update_rows", updateRows);

//結果を表示

ServletContext sc = getServletContext();

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

rd.forward(req, resp);

} catch(Exception e) {

//エラーを表示

ServletContext sc = getServletContext();

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

rd.forward(req, resp);

}

package yurufuwa.prog.sample;

import java.sql.Connection;
import java.sql.PreparedStatement;

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

public class Tsuika {

	private int updateRows = -1;

	public void execute(String kenCode, String kenName, String yomigana) throws Exception {

		Connection conn = null;

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

			//SQLを発行
			PreparedStatement pstmt = conn.prepareStatement(
				"INSERT INTO todofuken (ken_code,ken_name,yomigana) "
				+ "VALUES ( ?, ?, ?)"
			);
			pstmt.setString(1, kenCode);
			pstmt.setString(2, kenName);
			pstmt.setString(3, yomigana);

			updateRows = pstmt.executeUpdate();

			pstmt.close();

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

	public int getUpdateRows() {
		return updateRows;
	}
}

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

package yurufuwa.prog.sample;

import java.sql.Connection;

import java.sql.PreparedStatement;

import javax.naming.InitialContext;

import javax.sql.DataSource;

public class Tsuika {

private int updateRows = -1;

public void execute(String kenCode, String kenName, String yomigana) throws Exception {

Connection conn = null;

try {

//DBに接続

InitialContext ctx = new InitialContext();

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

conn = ds.getConnection();

//SQLを発行

PreparedStatement pstmt = conn.prepareStatement(

"INSERT INTO todofuken (ken_code,ken_name,yomigana) "

+ "VALUES ( ?, ?, ?)"

);

pstmt.setString(1, kenCode);

pstmt.setString(2, kenName);

pstmt.setString(3, yomigana);

updateRows = pstmt.executeUpdate();

pstmt.close();

} catch(Exception e) {

e.printStackTrace();

throw e;

} finally {

try {

//接続を閉じる

conn.close();

} catch(Exception e) {

}

public int getUpdateRows() {

return updateRows;

}

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

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>都道府県データ追加</title>
</head>
<body>

<h2>追加するデータを入力してください</h2>

<form action="./sv3" method="post">
	<table>
		<tr><td>都道府県コード</td><td><input type="text" name="txtKenCode" /></td></tr>
		<tr><td>都道府県名</td><td><input type="text" name="txtKenName" /></td></tr>
		<tr><td>読み仮名</td><td><input type="text" name="txtYomigana" /></td></tr>
	</table>
	<input type="submit" value="追加" />
</form>

</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

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

pageEncoding="UTF-8"%>

<!DOCTYPE html>

<html>

<head>

<title>都道府県データ追加</title>

</head>

<body>

<h2>追加するデータを入力してください</h2>

<table>

<tr><td>都道府県コード</td><td><input type="text" name="txtKenCode" /></td></tr>

<tr><td>都道府県名</td><td><input type="text" name="txtKenName" /></td></tr>

</table>

</form>

</body>

</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
String kenCode = (String)request.getAttribute("ken_code");
String kenName = (String)request.getAttribute("ken_name");
String yomigana = (String)request.getAttribute("yomigana");
int updateRows = (int)request.getAttribute("update_rows");
%>

<!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>

<h3>入力内容</h3>
<table>
	<tr><td>都道府県コード</td><td><%= kenCode %></td></tr>
	<tr><td>都道府県名</td><td><%= kenName %></td></tr>
	<tr><td>読み仮名</td><td><%= yomigana %></td></tr>
</table>

<h3>追加の件数</h3>
<%= updateRows %>件

</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"%>

<%

String kenCode = (String)request.getAttribute("ken_code");

String kenName = (String)request.getAttribute("ken_name");

String yomigana = (String)request.getAttribute("yomigana");

int updateRows = (int)request.getAttribute("update_rows");

%>

<!DOCTYPE html>

<html>

<head>

<title>都道府県データ追加</title>

</head>

<body>

<h2>データの追加結果</h2>

<table>

<tr><td>都道府県コード</td><td><%= kenCode %></td></tr>

<tr><td>都道府県名</td><td><%= kenName %></td></tr>

</table>

<h3>追加の件数</h3>

<%= updateRows %>件

</body>

</html>

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

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

データの追加でエラーが発生しました。

</body>
</html>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

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

pageEncoding="UTF-8"%>

<!DOCTYPE html>

<html>

<head>

<title>都道府県データ追加</title>

</head>

<body>

データの追加でエラーが発生しました。

</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>Sv3</display-name>
    <servlet-name>Sv3</servlet-name>
    <servlet-class>yurufuwa.prog.sample.Sv3</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>Sv3</servlet-name>
    <url-pattern>/sv3</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>Sv3</display-name>

<servlet-name>Sv3</servlet-name>

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

</servlet>

<servlet-mapping>

<servlet-name>Sv3</servlet-name>

<url-pattern>/sv3</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