サーブレットでSqlite DBをDataTableに読み込む

登録

● データ入力フォームの作成

Insert.java

package sample.view;

import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class Insert extends HttpServlet {

  private static final long serialVersionUID = 1L;
  protected void insertRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
    request.setCharacterEncoding("UTF-8");
    response.setContentType("text/html; charset=UTF-8");
    PrintWriter out = response.getWriter();
    
    out.println("<html>");
    out.println("<head>");
    out.println("<title>Jsp & Servlet　-　Sample Sqlite DB ： datatableを扱う</title>");
    out.println("<link rel=\"stylesheet\" type=\"text/css\" href=\"SampleServlet.css\"/>");
    out.println("</head>");
    out.println("<body>");
    out.println("<h1>Entry</h1>");
    out.println("<br />");
    out.println("<form name=\"insertform\" action=\"Insert2\" method=\"post\">");
    out.println(""
      +"<table>"
      +"<tr><td><b>NAME</b></td><td> ： <input type=\"text\" name=\"name\" required></td></tr>"
      +"<tr><td><b>ADDRESS</b></td><td> ： <input type=\"text\" name=\"address\"></td></tr>"
      +"<tr><td><b>EMAIL</b></td><td> ： <input type=\"text\" name=\"email\"></td></tr>"
      +"<tr><td><b>TEL</b></td><td> ： <input type=\"text\" name=\"phone_number\"></td></tr>"
      +"<tr><td><b>MEMO</b></td><td> ： <input type=\"text\" name=\"memo\"></td></tr>"
      +"<tr rowspan=\"2\"><td colspan=\"2\"> </td></tr>"
      +"<tr><td colspan=\"2\"><input type=\"submit\" name=\"Insert\" value=\"save\" class=\"submit\"> <input type=\"reset\" value=\"reset\" class=\"submit\"></td></tr>"		    
      +"</table>"
    );
    out.println("</form>");
    out.println("<br />");
    out.println("<a href=\"Select\">Show AllData</a><br />");
    out.println("<a href=\"index.jsp\">Index</a>");
    out.println("</body>");
    out.println("</html>");
  }

  // このページにGETでアクセス
  @Override
  protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    insertRequest(request, response);
  }
}

登録データをDBへコミット

● 登録フォームから入力データを受取りDBへコミット

Insert2.java

package sample.view;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import sample.util.SqliteDB;

public class Insert2 extends HttpServlet {

  private static final long serialVersionUID = 1L;
  protected void insert2Request(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException, ClassNotFoundException, SQLException {
    request.setCharacterEncoding("UTF-8");
    response.setContentType("text/html; charset=UTF-8");
    PrintWriter out = response.getWriter();
    
    // このサーブレットにGET又はPOSTで送信されてきたデータをgetParameterメソットを使って受け取ります    
    String name = request.getParameter("name");
    String address = request.getParameter("address");
    String email = request.getParameter("email");
    String phone_number = request.getParameter("phone_number");
    String memo = request.getParameter("memo");
    
    // DBへデータを登録    
    SqliteDB db = new SqliteDB();
    String sql = "INSERT INTO exampleTbl" + 
      "(name, address, email, phone_number, memo)" + 
      "VALUES('" + name + "','" + address + "','" + email + "','" + phone_number + "','" + memo + "')";	  
    try {
      db.open();
      db.executeUpdate(sql);
    } catch (SQLException | ClassNotFoundException e){
    	System.out.println("executeUpdate Error : " + e);
    	throw e;
    } finally {
     db.descon();
    }
    
    // htmlの作成
    out.println("<html>");
    out.println("<head>");
    out.println("<title>Jsp & Servlet　-　Sample Sqlite DB ： datatableを扱う</title>");
    out.println("<link rel=\"stylesheet\" type=\"text/css\" href=\"SampleServlet.css\"/>");
    out.println("</head>");
    out.println("<body>");
    out.println("<h1>Entry Success</h1>");
    out.println("<br />");
    out.println("<a href=\"Insert\">Entry</a><br />");
    out.println("<a href=\"Select\">Show AllData</a><br />");
    out.println("<a href=\"index.jsp\">Index</a><br />");
    out.println("</body>");
    out.println("</html>");
  }

  // このページにPOSTでアクセス
  @Override
  protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    try {
      insert2Request(request, response);
    } catch (ServletException | IOException | ClassNotFoundException | SQLException e) {
      Logger.getLogger(Insert2.class.getName()).log(Level.SEVERE, null, e);
    }
  }
}

削除

● 全データ表示ページからデータ（レコード）を受取りDBへコミット

Delete.java

package sample.view;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import sample.util.SqliteDB;

public class Delete extends HttpServlet {

  private static final long serialVersionUID = 1L;
  protected void deleteRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException, ClassNotFoundException, SQLException {
    request.setCharacterEncoding("UTF-8");
    response.setContentType("text/html; charset=UTF-8");
    PrintWriter out = response.getWriter();
    
    // getParameterメソットで受け取ったデータ（文字型）を整数に変換します    
    int prefcd = Integer.parseInt(request.getParameter("delparam"));
    
    // DBデータの削除を実行	  	
    SqliteDB db = new SqliteDB();
    String sql = "DELETE FROM exampleTbl WHERE id =" + prefcd;
    try {
      db.open(); 	  
      db.executeUpdate(sql);
    } catch (SQLException e) {
        System.out.println("executeUpdate Error! : " + e);
        throw e;
    } finally {
        db.descon();
    }
    
    // htmlの作成
    out.println("<html>");
    out.println("<head>");
    out.println("<title>Jsp & Servlet　-　Sample Sqlite DB ： datatableを扱う</title>");
    out.println("<link rel=\"stylesheet\" type=\"text/css\" href=\"SampleServlet.css\"/>");
    out.println("</head>");
    out.println("<body>");
    out.println("<h1>Delete Success</h1>");
    out.println("<br />");
    out.println("<a href=\"Select\">Show AllData</a><br />");
    out.println("<a href=\"index.jsp\">Index</a></li>");
    out.println("</body>");
    out.println("</html>");
  }

  // このページにGETでアクセス 
  @Override
  protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    try {
      deleteRequest(request, response);
    } catch (ServletException | IOException | ClassNotFoundException | SQLException e) {
        Logger.getLogger(Insert2.class.getName()).log(Level.SEVERE, null, e);
    }
  }
}