最近客户要求在jquery datatable 通过自己输入条件读取后台数据,通过查询文档得知jquery datatable 可以通过ajax读取数据 ,具体代码如下。
一.jsp代码如下
<script>
function getpayment(){
var projectcode=$("#projectcode").val();
var stdate=$("#stdate").val();
var enddate=$("#enddate").val();
var url= "payment-list-ajax.jsp?projectcode="+projectcode+"&stdate="+stdate+"&enddate="+enddate;
//再次查询时先删除paymentlist,如果少了以下语句每次只能查询一次,第二次点击查询就不执行。
var table=$('#paymentlist').dataTable();
if(table){
table.fnDestroy();}
$('#paymentlist').dataTable( {
"processing": true,
"serverSide": true,
"bInfo":false,//是否显示是否启用底边信息栏
"sAjaxSource": url,
'autoWidth': true,
'pagingType': 'full_numbers',
"language": {
'lengthMenu': '每页显示 _MENU_ 记录',
'zeroRecords': '没有数据 - 抱歉',
'info': ' 第_PAGE_ 页/共 _PAGES_页',
'infoEmpty': '没有符合条件的记录',
'search': '查找',
'infoFiltered': '(从 _MAX_ 条记录中过滤)',
'paginate': { "first": "首页 ", "last": "末页", "next": "下一页","previous": "上一页"}
},
"retrieve":true,
"paging": true,
"ordering": true,
"info": true,
"columns": [
{ "data": "paydate" },
{ "data": "contractno" },
{ "data": "pursuercode" },
{ "data": "paytype" },
{ "data": "paid" },
{ "data": "samplesize" },
{ "data": "unitprice" },
{ "data": "paymethod" },
{ "data": "invice" },
{ "data": "receipt" },
{ "data": "remark" }
]
} );
}
</script>
</head>
<body>
<fieldset>
<div>
<div style="float: left; width: 200px;">
<input type="hidden" id=projectcode name="projectcode"
value="<%=request.getParameter("projectcode").toString()%>" />
<div class="widget">
<label>开始付款日期:</label> <input type="text" id=stdate
class="ui-corner-all" name="stdate" οnclick="WdatePicker({dateFmt:'yyyy-MM-dd'})"
value="" />
</div>
</div>
<div style="float: left; margin-left: 0px; width: 200px;">
<div class="widget">
<label for="paydate">结束付款日期:</label> <input id="enddate"
class="ui-corner-all required" name="enddate"
οnclick="WdatePicker({dateFmt:'yyyy-MM-dd'})" type="text"
title="日期必填" value="">
</div>
</div>
<div style="float: left; margin-top:25px; margin-left: 0px; width:0px;">
<input type="button" οnclick="getpayment()" id="select" value="查询" />
</div>
</div>
</fieldset>
<fieldset>
<table id='paymentlist' class='display cell-border' style='width: 100%'>
<caption>
<b>付款明细</b>
</caption>
<thead>
<tr>
<th>付款日期</th>
<th>合同编号</th>
<th>收款单位</th>
<th>名目</th>
<th>支付金额</th>
<th>样本量</th>
<th>单价</th>
<th>付款方式</th>
<th>发票</th>
<th>签收单</th>
<th>备注</th>
</tr>
</thead>
</table>
</fieldset>
</body>
</html>
二.payment-list-ajax.jsp要求反回的数据 客户为json格式 ,代码如下
<%@page import="java.math.BigDecimal"%>
<%@page import="com.biojia.entity.Payment"%>
<%@page import="com.biojia.service.*"%>
<%@page import="java.sql.ResultSet"%>
<%
PaymentService paymentService = new PaymentService();
String projectCode = request.getParameter("projectcode").toString();
String startDate = request.getParameter("stdate").toString();
String endDate = request.getParameter("enddate").toString();
String datastr="{ \"data\":[";
String paydate;
String contractno;
String pursuercode;
String paytype;
String paid;
String samplesize;
String unitprice;
String paymethod;
String invice;
String receipt;
String remark;
ResultSet resultSet = paymentService.getPayments(projectCode,startDate,endDate);
try{
while (resultSet != null & resultSet.next()) {
paydate = resultSet.getString("paydate");
contractno = resultSet.getString("contractno");
pursuercode=resultSet.getString("pursuerid");
paytype=resultSet.getString("paytype");
paid=resultSet.getString("paid");
samplesize=resultSet.getString("samplesize");
unitprice=resultSet.getString("unitprice");
paymethod=resultSet.getString("paymethod");
invice=resultSet.getString("invice");
receipt=resultSet.getString("receipt");
remark=resultSet.getString("remark");
if(paydate==null){paydate="";}
if(contractno==null){contractno="";}
if(pursuercode==null){pursuercode="";}
if(paytype==null){paytype="";}
if(paid==null){paid="";}
if(samplesize==null){samplesize="";}
if(unitprice==null){unitprice="";}
if(paymethod==null){paymethod="";}
if(invice==null){invice="";}
if(receipt==null){receipt="";}
if(remark==null){remark="";}
datastr=datastr+"{\"paydate\":\""+paydate+"\","
+"\"contractno\":\""+contractno+"\","
+"\"pursuercode\":\""+pursuercode+"\","
+"\"paytype\":\""+paytype+"\","
+"\"paid\":\""+paid+"\","
+"\"samplesize\":\""+samplesize+"\","
+"\"unitprice\":\""+unitprice+"\","
+"\"paymethod\":\""+paymethod+"\","
+"\"invice\":\""+invice+"\","
+"\"receipt\":\""+receipt+"\","
+"\"remark\":\""+remark+"\"},";
}
}catch(Exception e){
out.println(e.toString());
}finally{
datastr=datastr.substring(0,datastr.length()-1);
datastr=datastr+"]}";
}
out.print(datastr);
%>
注意事项:
1.javascript代码中一定要以下语句,因为不加每次只能查询一次,第二次点击查询就不执行。
var table=$('#paymentlist').dataTable();
if(table){
table.fnDestroy();}
2.jsp返回数据栏位一定要与javascript设置的完全一致。
{
"data": [
{
"paydate": "2015-09-09",
"contractno": "111111111",
"pursuercode": "001",
"paytype": "\u4e34\u5e8a\u8bd5\u9a8c\u8d39\u7528(\u533b\u9662\u7ecf\u8d39)",
"paid": "32.23",
"samplesize": "0.00",
"unitprice": "0",
"paymethod": "\u73b0\u91d1",
"invice": "\u6709",
"receipt": "\u6709",
"remark": " "
},
{
"paydate": "2015-09-14",
"contractno": "23131",
"pursuercode": "01",
"paytype": "\u4e34\u5e8a\u8bd5\u9a8c\u8d39\u7528(\u533b\u9662\u7ecf\u8d39)",
"paid": "888.00",
"samplesize": "999.00",
"unitprice": "0",
"paymethod": "\u73b0\u91d1",
"invice": "\u6709",
"receipt": "\u6709",
"remark": " "
}
]
}