今回は、「データベースアプリケーションの作成(11)―SQLのエスケープ―」です。
PreparedStatementを使って、SQLのエスケープ処理をしてみましょう。
■動画はこちら
■動画で使用しているソースコード
1.SELECT文(1)
サーブレット(Sv2.java)
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); } } } |
検索処理（Kensaku.java）
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; } } |
レコード（Todofuken.java）
1 2 3 4 5 | package yurufuwa.prog.sample; public record Todofuken (String kenCode, String kenName, String yomigana) { } |
JSP1（in.jsp）
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> <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> |
JSP2（out.jsp）
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> <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> |
JSP3（err.jsp）
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> <meta charset="UTF-8"> <title>都道府県一覧</title> <style>table, th, td { border-collapse: collapse; border: 1px black solid; }</style> </head> <body> 検索でエラーが発生しました。 </body> </html> |
web.xml
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> <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> |
2.SELECT文(2)
サーブレット(Sv2.java)
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); } } } |
検索処理（Kensaku.java）
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; } } |
レコード（Todofuken.java）
1 2 3 4 5 | package yurufuwa.prog.sample; public record Todofuken (String kenCode, String kenName, String yomigana) { } |
JSP1（in.jsp）
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> <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> |
JSP2（out.jsp）
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> <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> |
JSP3（err.jsp）
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> <meta charset="UTF-8"> <title>都道府県一覧</title> <style>table, th, td { border-collapse: collapse; border: 1px black solid; }</style> </head> <body> 検索でエラーが発生しました。 </body> </html> |
web.xml
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> <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> |
3.INSERT文
サーブレット(Sv3.java)
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); } } } |
追加処理（Tsuika.java）
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; } } |
JSP1（in.jsp）
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> <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> |
JSP2（out.jsp）
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> <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> |
JSP3（err.jsp）
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> <meta charset="UTF-8"> <title>都道府県データ追加</title> <style>table, th, td { border-collapse: collapse; border: 1px black solid; }</style> </head> <body> データの追加でエラーが発生しました。 </body> </html> |
web.xml
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> <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> |
4.共通
context.xml
1 2 3 4 5 6 7 8 9 10 11 12 | <?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> |
