datatables增删改查php,基本增删改查（java）

{{#each func}}

{{this.name}}

{{/each}}

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": "r>" +

"t" +

">",

initComplete: function () {

$("#mytool").append('增加基础数据&nbsp');

$("#mytool").append('添加');

$("#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);

}

});

}

姓名地点薪水入职时间办公地点编号操作

aria-hidden="true">×

新增

data-date-format="yyyy/mm/dd">

添加模拟数据

关闭

保存

F12查看

Created by IntelliJ IDEA.

User: Administrator

Date: 2015/4/13

Time: 11:59

To change this template use File | Settings | File Templates.

--%>

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 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 sArray = new ArrayList();

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 users = new ArrayList();

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 info = new HashMap();

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);

%>

Created by IntelliJ IDEA.

User: Administrator

Date: 2015/4/13

Time: 11:59

To change this template use File | Settings | File Templates.

--%>

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();

%>

Created by IntelliJ IDEA.

User: Administrator

Date: 2015/4/13

Time: 11:59

To change this template use File | Settings | File Templates.

--%>

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();

%>

Created by IntelliJ IDEA.

User: Administrator

Date: 2015/4/13

Time: 11:59

To change this template use File | Settings | File Templates.

--%>

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;

}

}

}