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

検索

● 検索フォームの作成
● 検索対象はカラムのNAMEのみ

Search.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 Search extends HttpServlet {
 
	private static final long serialVersionUID = 1L;
	protected void searchRequest(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>Search</h1>");
    out.println("<br />");
    // NAMEの検索
    out.println("<div>");
    out.println("<form method=\"POST\" name=\"Search2\" action=\"Search2\" target=\"_self\">");
    out.println("<span>NAME：</span>");
    out.println("<input type=\"text\" name=\"keyword\" size=\"20\" required>");
    out.println("<input type=\"radio\" checked=\"checked\" name=\"option\" value=\"AND\">AND");
    out.println("<input type=\"radio\" name=\"option\" value=\"OR\">OR");
    out.println("<br /><br />");
    out.println("<input type=\"submit\" name=\"searche\" value=\"search\" class=\"submit\">");
    out.println("</form>");
    out.println("</div>");
    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 {
    searchRequest(request, response);
  }
}

検索データをDBへコミット

● データは検索フォームから受取る
● 処理結果をDataTableに表示

Search2.java

package sample.view;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;
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 Search2 extends HttpServlet {

	private static final long serialVersionUID = 1L;
	protected void search2Request(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException, ClassNotFoundException, SQLException {
    request.setCharacterEncoding("UTF-8");
    response.setContentType("text/html; charset=UTF-8");
    PrintWriter out = response.getWriter();
    
    String keyword = request.getParameter("keyword");
    String option = request.getParameter("option");  
    // 検索抽出条件の作成
    String tempWhere = extraction( keyword, option );

    SqliteDB db = new SqliteDB();
    String sql = "SELECT * FROM exampleTbl "+tempWhere;
    ResultSet rs = null;
    String tempHtml = "";
    try {     
      db.open();
      rs = db.executeQuery(sql);
      while(rs.next()){
        tempHtml += "<tr>";
        tempHtml += "<td>" + rs.getInt("id") + "</td>";
        tempHtml += "<td>" + rs.getString("name") + "</td>";
        tempHtml += "<td>" + rs.getString("address") + "</td>";
        tempHtml += "<td>" + rs.getString("email") + "</td>";
        tempHtml += "<td>" + rs.getString("phone_number") + "</td>";
        tempHtml += "<td>" + rs.getString("memo") + "</td>";
        tempHtml += "<td>" + rs.getString("created_at") + "</td>";
        tempHtml += "<td>" + rs.getString("updated_at") + "</td>";
        tempHtml += "<td><a href='Update?upparam=" + rs.getInt("id") + "'>Edit</a></td>";
        tempHtml += "<td><a href='Delete?delparam=" + rs.getInt("id") + "' onclick=\"return deleteConfirm(" + rs.getInt("id") + ");\">Delete</a></td>";    
        tempHtml += "</tr>";
      }
    } catch (SQLException e) {
        System.out.println("executeQuery Error : " + e);
        throw e;
    } finally {
        if (rs != null) {
          rs.close();
        }
        db.descon();
    }

  out.println("<html>");
  out.println("<head>");
  out.println("<title>Jsp & Servlet　-　Sample Sqlite DB ： datatableを扱う</title>");
  out.println("<link rel=\"stylesheet\" href=\"https://cdn.datatables.net/v/bs4-4.1.1/jq-3.3.1/dt-1.10.18/sl-1.2.6/datatables.min.css\"/>");
  out.println("<script src=\"http://code.jquery.com/jquery-1.11.3.min.js\"></script>");
  out.println("<script src=\"https://cdn.datatables.net/v/bs4-4.1.1/jq-3.3.1/dt-1.10.18/sl-1.2.6/datatables.min.js\"></script>");                                                                    
  out.println("<link rel=\"stylesheet\" type=\"text/css\" href=\"SampleServlet.css\"/>");
  out.println("</head>");
  out.println("<body>");
  out.println("<h1>Search Result</h1>");
  out.println("<br />");
  out.println("<table id=\"example\" class=\"table table-striped table-bordered nowrap\" width=\"100%\">");
  out.println("<thead>");
    out.println("<tr>");
    out.println("<th>ID</th>");
    out.println("<th>NAME</th>");
    out.println("<th>ADDRESS</th>");
    out.println("<th>EMAIL</th>");
    out.println("<th>TEL</th>");
    out.println("<th>MEMO</th>");
    out.println("<th>CREATED</th>");
    out.println("<th>UPDATED</th>");
    out.println("<th width=\"10\" style=\"background:#dedede;\"> </th>");
    out.println("<th width=\"20\" style=\"background:#dedede;\"> </th>");
    out.println("</tr>");
  out.println("</thead>");
  out.println("<tbody>");
  out.println(tempHtml);
  out.println("</tbody>");
  out.println("</table>");

  // datatable Menu
  out.println("<script type=\"text/javascript\">"
  	+"$(document).ready(function() {" 	 
  	+"var table = $('#example').DataTable( {"  
  	+"\"lengthMenu\":[[5, 10, 25, 50, -1], [5, 10, 25, 50, \"All\"]]"
  	+"});"
  	+"});"
  	+"</script>");

  // 削除確認ダイアログ
  out.println("<script type=\"text/javascript\">"
  	+"function deleteConfirm (id) {" 
  	+"var id;"
  	+"var flag = confirm('[ '+id+' ]番のデータを削除します。間違いなければ[OK]ボタンを押して下さ。');"
  	+"return flag;"
  	+"}"
  	+"</script>"); 

  out.println("<br />");
  out.println("<a href=\"Search\">Search</a><br />");
  out.println("<a href=\"Select\">Show AllData</a><br />");
  out.println("<a href=\"index.jsp\">Index</a>");
  out.println("</body>");
  out.println("</html>");
  }

  // 検索抽出条件の作成
  public String extraction(String keyword, String option) {
    // keyword入力チェック（文字の先頭と末尾の全角半角の空白を取り除く）
    if(keyword != null && !keyword.equals("")) {
      keyword = trimLR(keyword);
    }

    // 半角の空白に置き換える。
    keyword = keyword.replaceAll("[ 　]+", " ");

    // 抽出条件の組み立て
    String[] array = keyword.split(" ");
    int cnt = array.length;
    String where = "WHERE ";
    for(int i=0; i < cnt; i++){
      where += "name LIKE '%" +array[i]+ "%'";
      if(i < cnt-1){
 	where +=" "+option+" ";
      }
    }
    return where;
  }	
	
  // 文字列の先頭末尾の空白を削除
  public String trimLR (String cs){
    if(cs == null){
      return "";
    } else {
      cs = cs.replaceAll("^ +","");
      cs = cs.replaceAll(" +$","");
      return cs;
    }
  }  
  
  // このページにPOSTでアクセス
  @Override
  protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    try {
      search2Request(request, response);
    } catch (ServletException | IOException | ClassNotFoundException | SQLException e) {
      Logger.getLogger(Select.class.getName()).log(Level.SEVERE, null, e);
    }
  }
}

更新

● 更新用フォームの作成
● 更新データは全データ表示ページ（Edit）から受取る
● カラムNAMEの更新はしない

Update.java

package sample.view;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;
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 Update extends HttpServlet {

	private static final long serialVersionUID = 1L;
	protected void updateRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException, ClassNotFoundException, SQLException {  
    request.setCharacterEncoding("UTF-8");
    response.setContentType("text/html; charset=UTF-8");
    PrintWriter out = response.getWriter();

    int prefid = Integer.parseInt(request.getParameter("upparam"));

    SqliteDB db = new SqliteDB();
    ResultSet rs = null;
    String sql = "SELECT * FROM exampleTbl WHERE id =" + prefid;
    int id = 0;
    String name = "";
    String address = "";
    String email = "";
    String phone_number = "";
    String memo = ""; 
    try {
      db.open();
      rs = db.executeQuery(sql);
      while(rs.next()){
	id = rs.getInt("id");
	name = rs.getString("name");
	address = rs.getString("address");
	email = rs.getString("email");
	phone_number = rs.getString("phone_number");
	memo = rs.getString("memo");	  	  	
      }
    } catch (SQLException e){
    	System.out.println("executeQuery Error! : " + e);
    	throw e;
    } finally {
        if (rs != null) {
           rs.close();
        }
  	db.descon();
    }    	
    	
    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>Edit</h1>");
    out.println("<br />");
    out.println("<form name=\"upform\" action=\"Update2\" method=\"post\">");
    out.println(""
      +"<table>"
      +"<tr><td><b>ID</b></td><td> ： <input type=\"text\" value=\"" + id + "\" disabled=\"disabled\"></td></tr>"
      +"<tr><td><b>NAME</b></td><td> ： <input type=\"text\" name=\"name\" value=\"" + name + "\" disabled=\"disabled\"></td></tr>"
      +"<tr><td><b>ADDRESS</b></td><td> ： <input type=\"text\" name=\"address\" value=\"" + address + "\"></td></tr>"
      +"<tr><td><b>EMAIL</b></td><td> ： <input type=\"text\" name=\"email\" value=\"" + email + "\"></td></tr>"
      +"<tr><td><b>TEL</b></td><td> ： <input type=\"text\" name=\"phone_number\" value=\"" + phone_number + "\"></td></tr>"
      +"<tr><td><b>MEMO</b></td><td> ： <input type=\"text\" name=\"memo\" value=\"" + memo + "\"></td></tr>"
      +"<tr rowspan=\"2\"><td colspan=\"2\"> </td></tr>"
      +"<tr><td colspan=\"2\"><input type=\"submit\" name=\"Save\" value=\"save\" class=\"submit\"> <input type=\"reset\" value=\"reset\" class=\"submit\"></td></tr>"
      +"</table>"
    );
    // id値は編集しないためこちらから送信
    out.println("<input type=\"hidden\" name=\"cd\" value =" + id + ">");
    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 {
    try {
      updateRequest(request, response);
    } catch (ServletException | IOException | ClassNotFoundException | SQLException e) {
      Logger.getLogger(Insert2.class.getName()).log(Level.SEVERE, null, e);
    }
  }
}

更新データをDBへコミット

● データは更新フォームから受取りDBへコミット

Update2.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 Update2 extends HttpServlet {

	private static final long serialVersionUID = 1L;
	protected void update2Request(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException, ClassNotFoundException, SQLException {
    request.setCharacterEncoding("UTF-8");
    response.setContentType("text/html; charset=UTF-8");
    PrintWriter out = response.getWriter();
    
    int id = Integer.parseInt(request.getParameter("cd"));
    
    String address = request.getParameter("address");
    String email = request.getParameter("email");
    String phone_number = request.getParameter("phone_number");
    String memo = request.getParameter("memo");
    String updatedTime = "datetime(CURRENT_TIMESTAMP,'localtime')"; // 編集日時を更新

    SqliteDB db = new SqliteDB();
    String sql = "UPDATE exampleTbl SET updated_at = " + updatedTime + ", address = '" + address + "', email = '" + email + "', phone_number = '" + phone_number + "', memo = '" + memo + "' WHERE id = " + id;		  
    try {
      db.open(); 			    
      db.executeUpdate(sql);
    } catch (SQLException | ClassNotFoundException e){
    	System.out.println("executeUpdate Error! : " + e);
    	throw e;
    } finally {	
        db.descon();
    }    
    
    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>Edit 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>");
  }

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