<div class="container">
    <table id="example" class="table table-striped table-bordered">
        <thead>
        <tr>
            <th>姓名</th>
            <th>地点</th>
            <th>薪水</th>
            <th>入职时间</th>
            <th>办公地点</th>
            <th>编号</th>
            <th>操作</th>
        </tr>
        </thead>
        <tbody></tbody>
        <!-- tbody是必须的 -->
    </table>
</div>
<!-- Button trigger modal -->


<!-- Modal -->
<div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span
                        aria-hidden="true">&times;</span></button>
                <h4 class="modal-title" id="myModalLabel">新增</h4>
            </div>
            <div class="modal-body">
                <div class="form-group">
                    <input type="text" class="form-control" id="name" placeholder="姓名">
                </div>
                <div class="form-group">
                    <input type="text" class="form-control" id="position" placeholder="位置">
                </div>
                <div class="form-group">
                    <input type="text" class="form-control" id="salary" placeholder="薪资">
                </div>
                <div class="form-group">
                    <input type="text" class="form-control" id="start_date" placeholder="时间"
                           data-date-format="yyyy/mm/dd">
                </div>
                <div class="form-group">
                    <input type="text" class="form-control" id="office" placeholder="工作地点">
                </div>
                <div class="form-group">
                    <input type="text" class="form-control" id="extn" placeholder="编号">
                </div>
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-info" id="initData">添加模拟数据</button>
                <button type="button" class="btn btn-default" data-dismiss="modal">关闭</button>
                <button type="button" class="btn btn-primary" id="save">保存</button>
            </div>
        </div>
    </div>
</div>

<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.Statement" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="com.thxopen.dt.bean.User" %>
<%@ page import="com.google.gson.*" %>
<%@ page import="java.util.*" %>
<%@ page import="com.thxopen.dt.sys.Config" %>
<%--
  Created by IntelliJ IDEA.
  User: Administrator
  Date: 2015/4/13
  Time: 11:59
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%

    ResultSet rs = null;
    Statement stmt = null;
    Connection conn = new Config(application).getConn();
    String table = "user";

    //获取请求次数
    String draw = "0";
    draw = request.getParameter("draw");
    //数据起始位置
    String start = request.getParameter("start");
    //数据长度
    String length = request.getParameter("length");

    //总记录数
    String recordsTotal = "0";

    //过滤后记录数
    String recordsFiltered = "";

    //定义列名
    String[] cols = {"name", "position", "salary", "start_date", "office", "extn"};
    //获取客户端需要那一列排序
    String orderColumn = "0";
    orderColumn = request.getParameter("order[0][column]");
    orderColumn = cols[Integer.parseInt(orderColumn)];
    //获取排序方式 默认为asc
    String orderDir = "asc";
    orderDir = request.getParameter("order[0][dir]");

   /* Map map = request.getParameterMap();
    Iterator<String> iter = map.keySet().iterator();
    while (iter.hasNext()) {
        String key = iter.next();
        System.out.println("key=" + key);
        String[] value = (String[]) map.get(key);
        System.out.print("value=");
        for (String v : value) {
//            out.print(v + "  ") ;
            System.out.println(v + "  ");
        }
    }*/

    //获取用户过滤框里的字符
    String searchValue = request.getParameter("search[value]");

    List<String> sArray = new ArrayList<String>();
    if (!searchValue.equals("")) {
        sArray.add(" name like '%" + searchValue + "%'");
        sArray.add(" position like '%" + searchValue + "%'");
        sArray.add(" salary like '%" + searchValue + "%'");
        sArray.add(" start_date like '%" + searchValue + "%'");
        sArray.add(" office like '%" + searchValue + "%'");
        sArray.add(" extn like '%" + searchValue + "%'");
    }

    String individualSearch = "";
    if (sArray.size() == 1) {
        individualSearch = sArray.get(0);
    } else if (sArray.size() > 1) {
        for (int i = 0; i < sArray.size() - 1; i++) {
            individualSearch += sArray.get(i) + " or ";
        }
        individualSearch += sArray.get(sArray.size() - 1);
    }

    List<User> users = new ArrayList<User>();
    if (conn != null) {
        String recordsFilteredSql = "select count(1) as recordsFiltered from " + table;
        stmt = conn.createStatement();
        //获取数据库总记录数
        String recordsTotalSql = "select count(1) as recordsTotal from " + table;
        rs = stmt.executeQuery(recordsTotalSql);
        while (rs.next()) {
            recordsTotal = rs.getString("recordsTotal");
        }


        String searchSQL = "";
        String sql = "SELECT * FROM " + table;
        if (individualSearch != "") {
            searchSQL = " where " + individualSearch;
        }
        sql += searchSQL;
        recordsFilteredSql += searchSQL;
        sql += " order by " + orderColumn + " " + orderDir;
        recordsFilteredSql += " order by " + orderColumn + " " + orderDir;
        sql += " limit " + start + ", " + length;


        rs = stmt.executeQuery(sql);
        while (rs.next()) {
            users.add(new User(rs.getString("name"),
                    rs.getString("position"),
                    rs.getString("salary"),
                    rs.getString("start_date"),
                    rs.getString("office"),
                    rs.getString("extn")));
        }

        if (searchValue != "") {
            rs = stmt.executeQuery(recordsFilteredSql);
            while (rs.next()) {
                recordsFiltered = rs.getString("recordsFiltered");
            }
        } else {
            recordsFiltered = recordsTotal;
        }
    }


    Map<Object, Object> info = new HashMap<Object, Object>();
    info.put("data", users);
    info.put("recordsTotal", recordsTotal);
    info.put("recordsFiltered", recordsFiltered);
    info.put("draw", draw);
    String json = new Gson().toJson(info);
    rs.close();
    stmt.close();
    conn.close();
    out.write(json);
%>

<%@ page import="java.sql.*" %>
<%@ page import="com.thxopen.dt.sys.Config" %>
<%--
  Created by IntelliJ IDEA.
  User: Administrator
  Date: 2015/4/13
  Time: 11:59
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%


    String name = request.getParameter("name");
    PreparedStatement stmt = null;
    Connection conn = new Config(application).getConn();
    if (conn != null) {
        String sql = "delete from user where name = ?";
        stmt = conn.prepareStatement(sql);
        stmt.setString(1,name);
        int flag = stmt.executeUpdate();
        out.print(flag);
    }
    stmt.close();
    conn.close();
%>

<%@ page import="java.sql.*" %>
<%@ page import="com.thxopen.dt.sys.Config" %>
<%--
  Created by IntelliJ IDEA.
  User: Administrator
  Date: 2015/4/13
  Time: 11:59
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%


    String name = request.getParameter("name");
    String position = request.getParameter("position");
    String salary = request.getParameter("salary");
    String start_date = request.getParameter("start_date");
    String office = request.getParameter("office");
    String extn = request.getParameter("extn");

    Connection conn = new Config(application).getConn();
    PreparedStatement stmt = null;

    if (conn != null) {
        String sql = "update user set position = ?,salary = ?,start_date = ?,office = ?,extn = ? where name = ?";
        stmt = conn.prepareStatement(sql);
        stmt.setString(6, name);
        stmt.setString(1, position);
        stmt.setString(2, salary);
        stmt.setString(3, start_date);
        stmt.setString(4, office);
        stmt.setString(5, extn);

        int flag = stmt.executeUpdate();
        out.print(flag);
    }
    stmt.close();
    conn.close();
%>

<%@ page import="java.sql.*" %>
<%@ page import="com.thxopen.dt.sys.Config" %>
<%--
  Created by IntelliJ IDEA.
  User: Administrator
  Date: 2015/4/13
  Time: 11:59
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%


    String name = request.getParameter("name");
    String position = request.getParameter("position");
    String salary = request.getParameter("salary");
    String start_date = request.getParameter("start_date");
    String office = request.getParameter("office");
    String extn = request.getParameter("extn");

    PreparedStatement stmt = null;
    Connection conn = new Config(application).getConn();
    if (conn != null) {
        String sql = "insert into user values (?,?,?,?,?,?)";
        stmt = conn.prepareStatement(sql);
        stmt.setString(1,name);
        stmt.setString(2,position);
        stmt.setString(3,salary);
        stmt.setString(4,start_date);
        stmt.setString(5,office);
        stmt.setString(6,extn);

        int flag = stmt.executeUpdate();
        out.print(flag);
    }
    stmt.close();
    conn.close();
%>

package com.thxopen.dt.bean;

/**
 * Created by Administrator on 2015/4/13.
 */
public class User {

    public String name;
    public String position;
    public String salary;
    public String start_date;
    public String office;
    public String extn;

    public User(String name, String position, String salary, String start_date, String office, String extn) {
        this.name = name;
        this.position = position;
        this.salary = salary;
        this.start_date = start_date;
        this.office = office;
        this.extn = extn;
    }

    public String getName() {

        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPosition() {
        return position;
    }

    public void setPosition(String position) {
        this.position = position;
    }

    public String getSalary() {
        return salary;
    }

    public void setSalary(String salary) {
        this.salary = salary;
    }

    public String getStart_date() {
        return start_date;
    }

    public void setStart_date(String start_date) {
        this.start_date = start_date;
    }

    public String getOffice() {
        return office;
    }

    public void setOffice(String office) {
        this.office = office;
    }

    public String getExtn() {
        return extn;
    }

    public void setExtn(String extn) {
        this.extn = extn;
    }
}

package com.thxopen.dt.sys;

import javax.servlet.ServletContext;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * Created by Administrator on 2015/4/16.
 */
public class Config {
    String deUrl= "jdbc:sqlite:/";
    public String url = "";
    public ServletContext application;

    public  Config(ServletContext application){
        this.application = application;
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public Connection getConn(){
        url = deUrl+application.getRealPath("").replace("\\", "/") + "/dt.sqlite3";
//        url = deUrl + "d:\\workspaces\\workspaces\\Datatables-serverSide\\web\\".replace("\\", "/") + "/dt.sqlite3";
        try {
            return DriverManager.getConnection(url);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
}