Youtube版【中級編Java(36)】添付ファイルの扱い(2)―データベースの検索結果をダウンロード―

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 Sv13 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");

      //都道府県名の一覧を取得
      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);
   }
}

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

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 Sv13 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");

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

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);

}

package yurufuwa.prog.sample;

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

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

public class Sv14 extends HttpServlet {

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

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

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

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

      //ダウンロード処理
      Download d = new Download();
      d.execute(resp, 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

package yurufuwa.prog.sample;

import java.io.IOException;

import java.util.ArrayList;

import jakarta.servlet.ServletException;

import jakarta.servlet.http.HttpServlet;

import jakarta.servlet.http.HttpServletRequest;

import jakarta.servlet.http.HttpServletResponse;

public class Sv14 extends HttpServlet {

@Override

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

//検索条件を取得

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

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

Kensaku k = new Kensaku();

k.execute(kenName);

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

//ダウンロード処理

Download d = new Download();

d.execute(resp, todofukenList);

}

package yurufuwa.prog.sample;

import java.io.BufferedWriter;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.net.URLEncoder;
import java.util.ArrayList;

import jakarta.servlet.ServletOutputStream;
import jakarta.servlet.http.HttpServletResponse;

public class Download {

   public void execute(HttpServletResponse resp, ArrayList<Todofuken> todofukenList) throws IOException {

      //CSVのMIMEタイプを設定
      resp.setContentType("text/csv");

      //ダウンロードの指定（ファイル名付き）
      resp.setHeader(
            "Content-Disposition",
            "attachment;filename=\"" + URLEncoder.encode("都道府県一覧.csv", "UTF-8") + "\"");

      //CSVデータを出力ストリームに書き込み
      try(ServletOutputStream sos = resp.getOutputStream();
          OutputStreamWriter osw = new OutputStreamWriter(sos, "UTF-8");
          BufferedWriter bw = new BufferedWriter(osw);) {

         //検索結果を書き込み
         for(Todofuken t : todofukenList) {
            bw.write(t.kenCode() + "," + t.kenName() + "," + t.yomigana());
            bw.newLine();
         }

         bw.flush();
      }
   }
}

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

package yurufuwa.prog.sample;

import java.io.BufferedWriter;

import java.io.IOException;

import java.io.OutputStreamWriter;

import java.net.URLEncoder;

import java.util.ArrayList;

import jakarta.servlet.ServletOutputStream;

import jakarta.servlet.http.HttpServletResponse;

public class Download {

public void execute(HttpServletResponse resp, ArrayList<Todofuken> todofukenList) throws IOException {

//CSVのMIMEタイプを設定

resp.setContentType("text/csv");

//ダウンロードの指定（ファイル名付き）

resp.setHeader(

"Content-Disposition",

"attachment;filename=\"" + URLEncoder.encode("都道府県一覧.csv", "UTF-8") + "\"");

//CSVデータを出力ストリームに書き込み

try(ServletOutputStream sos = resp.getOutputStream();

OutputStreamWriter osw = new OutputStreamWriter(sos, "UTF-8");

BufferedWriter bw = new BufferedWriter(osw);) {

//検索結果を書き込み

for(Todofuken t : todofukenList) {

bw.write(t.kenCode() + "," + t.kenName() + "," + t.yomigana());

bw.newLine();

}

bw.flush();

}

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(String kenName) {

      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 WHERE ken_name LIKE '%"
            + kenName
            + "%'"
         );

         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();
         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

62

63

64

65

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(String kenName) {

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 WHERE ken_name LIKE '%"

+ kenName

+ "%'"

);

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();

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

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

<h2>検索条件</h2>

<form action="./sv13" 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){ %>

<a href="./sv14?txtKenName=<%= kenName %>">ダウンロードはこちら</a><br />
<br />

<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

40

41

42

43

44

<%@ 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){ %>

<a href="./sv14?txtKenName=<%= kenName %>">ダウンロードはこちら</a><br />

<br />

<table>

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

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

<tr>

</tr>

<% } %>

</table>

<% } else { %>

検索結果はありません。

<% } %>

</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>Sv13</display-name>
    <servlet-name>Sv13</servlet-name>
    <servlet-class>yurufuwa.prog.sample.Sv13</servlet-class>
  </servlet>
  <servlet>
    <description></description>
    <display-name>Sv14</display-name>
    <servlet-name>Sv14</servlet-name>
    <servlet-class>yurufuwa.prog.sample.Sv14</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>Sv13</servlet-name>
    <url-pattern>/sv13</url-pattern>
  </servlet-mapping>
  <servlet-mapping>
    <servlet-name>Sv14</servlet-name>
    <url-pattern>/sv14</url-pattern>
  </servlet-mapping>
</web-app>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

<?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>Sv13</display-name>

<servlet-name>Sv13</servlet-name>

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

</servlet>

<display-name>Sv14</display-name>

<servlet-name>Sv14</servlet-name>

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

</servlet>

<servlet-mapping>

<servlet-name>Sv13</servlet-name>

<url-pattern>/sv13</url-pattern>

</servlet-mapping>

<servlet-mapping>

<servlet-name>Sv14</servlet-name>

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

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

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

Youtube版【中級編Java(36)】添付ファイルの扱い(2)―データベースの検索結果をダウンロード―

ABOUTこの記事をかいた人

NEW POSTこのライターの最新記事

Youtube版【中級編Java(40)】Webシステムのログイン(2)―リ…

Youtube版【初級編Java(18)】Objectクラス(4) - ha…

Youtube版【中級編Java(39)】Webシステムのログイン(1)―シ…

Youtube版【初級編Java(17)】Objectクラス(3) - eq…

サイト内検索

カテゴリー

RECOMMENDこちらの記事も人気です。

Youtube版【中級編Java(9)】JSPの仕組み(2)―JSPが変…

Youtube版【初めてみようJava(42)】ポリモーフィズム―オブジ…

Youtube版【中級編Java(38)】添付ファイルの扱い(4)―バイ…

Youtube版【Java】四捨五入の計算方法の選択-複数ある計算方法-

【Java】Apache Commons CSVを使って、CSVファイル…

Youtube版【Java】フォルダをまるごと削除する-クラスを使った再…

Youtube版【初めてみようJava(10)】for文を使った繰り返し…

Youtube版【Java】監視プログラムに音声通知を追加する-音声ファ…

ABOUTこの記事をかいた人

NEW POSTこのライターの最新記事

Youtube版【中級編Java(40)】Webシステムのログイン(2)―リ…

Youtube版【初級編Java(18)】Objectクラス(4) - ha…

Youtube版【中級編Java(39)】Webシステムのログイン(1)―シ…

Youtube版【初級編Java(17)】Objectクラス(3) - eq…

サイト内検索

カテゴリー

タグ