今回は、「データベースアプリケーションの作成(13)―トランザクション処理―」です。
Javaからデータベースのトランザクション処理を使ってみましょう。
■動画はこちら
■動画で使用しているソースコード
サーブレット(Sv7.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.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 Sv7 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 zasekiNo = req.getParameter("txtZasekiNo"); String userId = req.getParameter("txtUserId"); String userName = req.getParameter("txtUserName"); try { //座席予約の処理 Yoyaku y = new Yoyaku(); boolean result = y.execute(zasekiNo, userId, userName); if(result) { //JSPへフォワード:予約に成功 //結果をリクエストにセット req.setAttribute("zaseki_no", zasekiNo); req.setAttribute("user_id", userId); req.setAttribute("user_name", userName); //結果を表示 forwardJsp("/WEB-INF/jsp/out.jsp", req, resp); } else { //JSPへフォワード:件数NG（予約済み） forwardJsp("/WEB-INF/jsp/err1.jsp", req, resp); } } catch(Exception e) { e.printStackTrace(); //JSPへフォワード:例外 forwardJsp("/WEB-INF/jsp/err2.jsp", req, resp); } } private void forwardJsp(String jspName, HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { ServletContext sc = getServletContext(); RequestDispatcher rd = sc.getRequestDispatcher(jspName); rd.forward(req, resp); } } |
座席予約の処理(Yoyaku.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 67 68 69 70 71 72 73 74 75 76 77 78 79 | package yurufuwa.prog.sample; import java.sql.Connection; import java.sql.PreparedStatement; import javax.naming.InitialContext; import javax.sql.DataSource; public class Yoyaku { private int updCount1 = -1; private int updCount2 = -1; public boolean execute(String zasekiNo, String userId, String userName) throws Exception { Connection conn = null; try { //DBに接続(トランザクション使用) InitialContext ctx = new InitialContext(); DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/mysql"); conn = ds.getConnection(); conn.setAutoCommit(false); //SQL発行（予約追加、座席更新） try { insertYoyaku(conn, userId, userName, zasekiNo); updateZaseki(conn, userId, zasekiNo); } catch(Exception e) { //更新失敗（例外）：取消（データを戻す） conn.rollback(); throw e; } //コミットと返り値の判断 if(updCount1 == 1 && updCount2 == 1) { //更新成功：データを確定 conn.commit(); return true; } else { //更新失敗（予約済み）：取消（データを戻す） conn.rollback(); return false; } } finally { try { //接続を閉じる conn.close(); } catch(Exception e) { } } } private void insertYoyaku(Connection conn, String userId, String userName, String zasekiNo) throws Exception { //SQL1 - 予約テーブルへ追加 PreparedStatement pstmt = conn.prepareStatement( "INSERT INTO yoyaku VALUES( ?, ?, ?)"); pstmt.setString(1, userId); pstmt.setString(2, userName); pstmt.setString(3, zasekiNo); updCount1 = pstmt.executeUpdate(); pstmt.close(); } private void updateZaseki(Connection conn, String userId, String zasekiNo) throws Exception { //SQL2 - 座席テーブルを更新 PreparedStatement pstmt = conn.prepareStatement( "UPDATE zaseki SET user_id= ? " + "WHERE zaseki_no = ? AND user_id=''"); pstmt.setString(1, userId); pstmt.setString(2, zasekiNo); updCount2 = pstmt.executeUpdate(); pstmt.close(); } } |
JSP1(in.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 | <%@ 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="./sv7" method="post"> <table> <tr><td>座席番号</td><td><input type="text" name="txtZasekiNo" /></td></tr> <tr><td>ユーザID</td><td><input type="text" name="txtUserId" /></td></tr> <tr><td>ユーザ名</td><td><input type="text" name="txtUserName" /></td></tr> </table> <input type="submit" value="予約" /> </form> </body> </html> |
JSP2(out.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 | <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <% String zasekiNo = (String)request.getAttribute("zaseki_no"); String userId = (String)request.getAttribute("user_id"); String userName = (String)request.getAttribute("user_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>予約を受け付けました</h3> <table> <tr><td>座席番号</td><td><%= zasekiNo %></td></tr> <tr><td>ユーザID</td><td><%= userId %></td></tr> <tr><td>ユーザ名</td><td><%= userName %></td></tr> </table> </body> </html> |
JSP3(err1.java)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>座席予約</title> </head> <body> <h3>席が予約済みです</h3> </body> </html> |
JSP4(err2.java)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>座席予約</title> </head> <body> <h3>予約に失敗しました</h3> </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>Sv7</display-name> <servlet-name>Sv7</servlet-name> <servlet-class>yurufuwa.prog.sample.Sv7</servlet-class> </servlet> <servlet-mapping> <servlet-name>Sv7</servlet-name> <url-pattern>/sv7</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> |
