基本增删改查(java)

此例子展示基于Datatables的最新版1.10.5的服务器处理的增删改查,弹窗效果,结合Bootstrap显示表格,局部刷新数据,还应用了dom,自定义按钮 使表格和按钮看起来是一个整体。这个例子中还是用模板引擎,目的是为了掩饰在使用render渲染函数时,如果拼接的html比较多,建议用模板的方式处理,这样代码看起来会比较简洁

注意:建议下载源码到本地运行,压缩包为java web工程包含 src和web两个目录

代码下载 »
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
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
<!--定义操作列按钮模板-->
<script id="tpl" type="text/x-handlebars-template">
    {{#each func}}
    <button type="button" class="btn btn-{{this.type}} btn-sm" onclick="{{this.fn}}">{{this.name}}</button>
    {{/each}}
</script>
 
<script>
    var table;
    var editFlag = false;
    $(function () {
 
        $('#start_date').datetimepicker();
 
        var tpl = $("#tpl").html();
        //预编译模板
        var template = Handlebars.compile(tpl);
 
        table = $('#example').DataTable({
            ajax: {
                url: "/list.jsp"
            },
            serverSide: true,
            columns: [
                {"data": "name"},
                {"data": "position"},
                {"data": "salary"},
                {"data": "start_date"},
                {"data": "office"},
                {"data": "extn"},
                {"data": null}
            ],
            columnDefs: [
                {
                    targets: 6,
                    render: function (a, b, c, d) {
                        var context =
                        {
                            func: [
                                {"name": "修改", "fn": "edit(\'" + c.name + "\',\'" + c.position + "\',\'" + c.salary + "\',\'" + c.start_date + "\',\'" + c.office + "\',\'" + c.extn + "\')", "type": "primary"},
                                {"name": "删除", "fn": "del(\'" + c.name + "\')", "type": "danger"}
                            ]
                        };
                        var html = template(context);
                        return html;
                    }
                }
 
            ],
            "language": {
                "lengthMenu": "_MENU_ 条记录每页",
                "zeroRecords": "没有找到记录",
                "info": "第 _PAGE_ 页 ( 总共 _PAGES_ 页 )",
                "infoEmpty": "无记录",
                "infoFiltered": "(从 _MAX_ 条记录过滤)",
                "paginate": {
                    "previous": "上一页",
                    "next": "下一页"
                }
            },
            "dom": "<'row'<'col-xs-2'l><'#mytool.col-xs-4'><'col-xs-6'f>r>" +
                    "t" +
                    "<'row'<'col-xs-6'i><'col-xs-6'p>>",
            initComplete: function () {
                $("#mytool").append('<button id="datainit" type="button" class="btn btn-primary btn-sm">增加基础数据</button>&nbsp');
                $("#mytool").append('<button type="button" class="btn btn-default btn-sm" data-toggle="modal" data-target="#myModal">添加</button>');
                $("#datainit").on("click", initData);
            }
 
        });
 
        $("#save").click(add);
 
        $("#initData").click(initSingleData);
 
    });
 
    /**
     * 初始化基础数据
     */
    function initData() {
        var flag = confirm("本功能将添加数据到数据库,你确定要添加么?");
        if (flag) {
            $.get("/objects.txt", function (data) {
                var jsondata = JSON.parse(data);
                $(jsondata.data).each(function (index, obj) {
                    ajax(obj);
                });
            });
        }
    }
 
    /**
     * 初始化基础数据
     */
    function initSingleData() {
        $("#name").val("http://dt.thxopen.com");
        $("#position").val("ShiMen");
        $("#salary").val("1");
        $("#start_date").val("2015/04/01");
        $("#office").val("Home");
        $("#extn").val("001");
    }
 
    /**
     * 清除
     */
    function clear() {
        $("#name").val("").attr("disabled",false);
        $("#position").val("");
        $("#salary").val("");
        $("#start_date").val("");
        $("#office").val("");
        $("#extn").val("");
        editFlag = false;
    }
 
    /**
     * 添加数据
     **/
    function add() {
        var addJson = {
            "name": $("#name").val(),
            "position": $("#position").val(),
            "salary": $("#salary").val(),
            "start_date": $("#start_date").val(),
            "office": $("#office").val(),
            "extn": $("#extn").val()
        };
 
        ajax(addJson);
    }
 
    /**
     *编辑方法
     **/
    function edit(name,position,salary,start_date,office,extn) {
        console.log(name);
        editFlag = true;
        $("#myModalLabel").text("修改");
        $("#name").val(name).attr("disabled",true);
        $("#position").val(position);
        $("#salary").val(salary);
        $("#start_date").val(start_date);
        $("#office").val(office);
        $("#extn").val(extn);
        $("#myModal").modal("show");
    }
 
    function ajax(obj) {
        var url ="/add.jsp" ;
        if(editFlag){
            url = "/edit.jsp";
        }
        $.ajax({
            url:url ,
            data: {
                "name": obj.name,
                "position": obj.position,
                "salary": obj.salary,
                "start_date": obj.start_date,
                "office": obj.office,
                "extn": obj.extn
            }, success: function (data) {
                table.ajax.reload();
                $("#myModal").modal("hide");
                $("#myModalLabel").text("新增");
                clear();
                console.log("结果" + data);
            }
        });
    }
 
 
    /**
     * 删除数据
     * @param name
     */
    function del(name) {
        $.ajax({
            url: "/del.jsp",
            data: {
                "name": name
            }, success: function (data) {
                table.ajax.reload();
                console.log("删除成功" + data);
            }
        });
    }
</script>
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
<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>
F12查看
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
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
<%@ 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);
%>
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
<%@ 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();
%>
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
<%@ 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();
%>
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
<%@ 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();
%>
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
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;
    }
}
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 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;
        }
    }
}