今回は、「データベースアプリケーションの作成(13)―トランザクション処理―」です。
データベースには、いろいろなデータ型があります。
数値、日付のデータ型について見てみましょう。
■動画はこちら
■動画で使用しているソースコード
※量が多いので、動画より抜粋して載せています。
欲しいコードがないようでしたら、youtubeのコメント欄へどうぞ。
SQLのWHEREの条件にNULLを使う(IS NULLの場合)
サーブレット(Sv9.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 | 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 Sv9 extends HttpServlet { @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { try { Todofuken nt = new Todofuken(); nt.select(); //結果を表示 req.setAttribute("todofuken_list", nt.getRecList()); forwardJsp("/WEB-INF/jsp/out.jsp", req, resp); } catch(Exception e) { e.printStackTrace(); } } private void forwardJsp(String jspName, HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { ServletContext sc = getServletContext(); RequestDispatcher rd = sc.getRequestDispatcher(jspName); rd.forward(req, resp); } } |
データの検索(Todofuken.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 | 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 Todofuken { private ArrayList<TodofukenRecord> recList = null; public void select() 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 IS NULL" ); ResultSet rs = pstmt.executeQuery(); recList = new ArrayList<TodofukenRecord>(); while(rs.next()) { String kenCode = rs.getString(1); String kenName = rs.getString(2); String yomigana = rs.getString(3); recList.add( new TodofukenRecord(kenCode,kenName,yomigana) ); } rs.close(); pstmt.close(); } finally { try { //接続を閉じる conn.close(); } catch(Exception e) { } } } public ArrayList<TodofukenRecord> getRecList() { return recList; } } |
レコード(TodofukenRecord.java)
1 2 3 4 5 | package yurufuwa.prog.sample; public record TodofukenRecord(String kenCode,String kenName,String yomigana) { } |
JSP(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 | <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ page import="java.util.ArrayList" %> <%@ page import="yurufuwa.prog.sample.TodofukenRecord" %> <% ArrayList<TodofukenRecord> todofukenList = (ArrayList<TodofukenRecord>)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> <h3>都道府県一覧</h3> <% if(todofukenList.size() != 0) { %> <table> <tr><th>都道府県コード</th><th>都道府県名</th><th>読み仮名</th></tr> <% for(TodofukenRecord t : todofukenList) { %> <tr> <td><%= t.kenCode() %></td> <td><%= t.kenName() %></td> <td><%= t.yomigana() %></td> </tr> <% } %> <% } else { %> 検索結果がありませんでした。 <% } %> </table> </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>Sv9</display-name> <servlet-name>Sv9</servlet-name> <servlet-class>yurufuwa.prog.sample.Sv9</servlet-class> </servlet> <servlet-mapping> <servlet-name>Sv9</servlet-name> <url-pattern>/sv9</url-pattern> </servlet-mapping> </web-app> |
NULLの値の取得
サーブレット(Sv10.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 | 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 Sv10 extends HttpServlet { @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { try { Table1 nt = new Table1(); nt.select(); //結果を表示 req.setAttribute("rec_list", nt.getRecList()); forwardJsp("/WEB-INF/jsp/out.jsp", req, resp); } catch(Exception e) { e.printStackTrace(); } } private void forwardJsp(String jspName, HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { ServletContext sc = getServletContext(); RequestDispatcher rd = sc.getRequestDispatcher(jspName); rd.forward(req, resp); } } |
データの検索(Table1.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 | 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 Table1 { private ArrayList<Table1Record> recList = null; public void select() 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 table1 " ); ResultSet rs = pstmt.executeQuery(); recList = new ArrayList<Table1Record>(); while(rs.next()) { String id = rs.getString(1); String vInt = rs.getString(3); String edit = null; if(vInt == null) { edit = ""; } else { int iInt = rs.getInt(3); edit = Integer.toString(iInt); } recList.add(new Table1Record(id, edit)); } rs.close(); pstmt.close(); } finally { try { //接続を閉じる conn.close(); } catch(Exception e) { } } } public ArrayList<Table1Record> getRecList() { return recList; } } |
レコード(Table1Record.java)
1 2 3 4 5 | package yurufuwa.prog.sample; public record Table1Record(String d1,String d2) { } |
JSP(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 | <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ page import="java.util.ArrayList"%> <%@ page import="yurufuwa.prog.sample.Table1Record"%> <% ArrayList<Table1Record> recList = (ArrayList<Table1Record>)request.getAttribute("rec_list"); %> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>date_tableのデータ</title> <style>table, th, td { border-collapse: collapse; border: 1px black solid; }</style> </head> <body> <h2>SQLの実行結果</h2> <table> <tr> <th>d1</th> <th>d2</th> </tr> <% for(Table1Record rec : recList) { %> <tr> <td><%= rec.d1() %></td> <td><%= rec.d2() %></td> </tr> <% } %> </table> </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>Sv10</display-name> <servlet-name>Sv10</servlet-name> <servlet-class>yurufuwa.prog.sample.Sv10</servlet-class> </servlet> <servlet-mapping> <servlet-name>Sv10</servlet-name> <url-pattern>/sv10</url-pattern> </servlet-mapping> </web-app> |
NULLの値のセット
サーブレット(Sv10.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 | 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 Sv10 extends HttpServlet { @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { try { Table1 nt = new Table1(); nt.insert(); //結果を表示 forwardJsp("/WEB-INF/jsp/out.jsp", req, resp); } catch(Exception e) { e.printStackTrace(); } } private void forwardJsp(String jspName, HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { ServletContext sc = getServletContext(); RequestDispatcher rd = sc.getRequestDispatcher(jspName); rd.forward(req, resp); } } |
データの追加(Table1.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 | package yurufuwa.prog.sample; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.Types; import javax.naming.InitialContext; import javax.sql.DataSource; public class Table1 { public void insert() 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 table1 " + "( id , v_varchar , v_int , v_decimal , v_date , v_datetime ) " + "VALUES( ?, ?, ?, ?, ?, ? )" ); pstmt.setString(1, "10"); pstmt.setNull(2, Types.VARCHAR); pstmt.setNull(3, Types.INTEGER); pstmt.setNull(4, Types.DECIMAL); pstmt.setNull(5, Types.DATE); pstmt.setNull(6, Types.TIMESTAMP); pstmt.executeUpdate(); pstmt.close(); } finally { try { //接続を閉じる conn.close(); } catch(Exception e) { } } } } |
JSP(out.jsp)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ page import="java.util.ArrayList"%> <%@ page import="yurufuwa.prog.sample.Table1Record"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>date_tableのデータ</title> <style>table, th, td { border-collapse: collapse; border: 1px black solid; }</style> </head> <body> <h2>SQLの実行結果</h2> データを追加しました。 </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>Sv10</display-name> <servlet-name>Sv10</servlet-name> <servlet-class>yurufuwa.prog.sample.Sv10</servlet-class> </servlet> <servlet-mapping> <servlet-name>Sv10</servlet-name> <url-pattern>/sv10</url-pattern> </servlet-mapping> </web-app> |
共通
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> |
