此例子展示基于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>  '); $("#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() { $( "#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" >×</ 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 > |
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 ; } } } |