The datatable is not refreshed
The datatable is not refreshed
vicvil
Posts: 5Questions: 1Answers: 0
Hello, I need your help.
I'm trying to query for a date range in a datatable with two DataTimePickers and a button, all in jsp.
When the page is opened for the first time, it shows me the information, which I look up in the database through a stored procedure and returns a Json.
The problem is when I query for the date range, the process is done, it returns the new Json but the data in the Datatable is not refreshed. I have tried it in several ways but it still doesn't do it since when I do the query it doesn't go through Document.ready again.
I show them what I'm doing, so they can tell me what I'm missing or what I'm doing wrong, please
<body id="cuerpo">
<%
HttpSession sesion = request.getSession();
if (sesion.getAttribute("existe") != "true" || sesion.getAttribute("existe") == null) {
response.sendRedirect("index.jsp");
return;
}
//revisa si la sesion corresponde
String ses = busca_sesion.sesion(sesion.getAttribute("clave").toString(), sesion.getAttribute("usuario").toString());
String sesi = sesion.getAttribute("sesion").toString();
if (sesi != ""){
if (sesi.compareTo(ses)!= 0){
response.sendRedirect("index.jsp");
return;
}}
%>
<%
String idusuario = "";
idusuario = String.valueOf(sesion.getAttribute("idusuario"));
String fecha1 = request.getParameter("fecha1");
String fecha2 = request.getParameter("fecha2");
SimpleDateFormat formato = new SimpleDateFormat("DD-MM-YYYY");
Connection coneccion = null;
conexion conecta = new conexion();
coneccion=conecta.conectar();
JSONArray arreglo = new JSONArray();
String[] json = new String[0];
arreglo = consulta_ventas.consulta(fecha1, fecha2, idusuario);
<%@ include file="menu.jsp"%>
<div style="margin-bottom: 40px">
<h3 align="center">Compras por cliente</h3>
</div>
<div class="container mt-5" align="center" style="margin-bottom: 30px">
<div class="container">
<div class="row">
<div class="col-md-3">
<h4>Fecha de Inicio</h4>
</div>
<div class="col-md-2">
<div class="form-group">
<div class="input-group date" id="datetimepicker1"
data-target-input="nearest">
<input type="date" class="form-control" value="<%=fecha1%>"
id="dtpicker1">
</div>
</div>
</div>
<div class="col-md-3">
<h4>Fecha de Término</h4>
</div>
<div class="col-md-2">
<div class="form-group">
<div class="input-group date" id="datetimepicker2"
data-target-input="nearest">
<input type="date" class="form-control" value="<%=fecha2%>"
id="dtpicker2">
</div>
</div>
</div>
<div>
<button type="submit" class="btn btn-primary" id="consultar"
name="consultar">
Consultar <img alt="" src="img/12.png" />
</button>
</div>
</div>
</div>
</div>
<div class="table-responsive container mt-5" style="margin-top: 0px">
<table id="table_id" class="table table-striped display"
style="width: 100%">
<thead>
<tr>
<th data-field="rut" data-sortable="true">RUT</th>
<th data-field="nombre" data-sortable="true">CLIENTE</th>
<th data-field="fecha" data-sortable="true">FECHA</th>
<th data-field="total" data-sortable="true">TOTAL</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
<%
Date td = new Date();
String b = new String("");
SimpleDateFormat format = new SimpleDateFormat("dd/MM/YYYY");
b = format.format(td);
%>
<script type="text/javascript">
//var table = $("#table_id").DataTable();
$(document).ready(function() {
//llenartabla();
$('#table_id').DataTable({
data:<%=arreglo%>,
dataSrc: '',
destroy:true,
columns:[{data:'rut'},
{data:'nombre'},
{data:'fecha'},
{data:'total'},],
paging : "true",
lengthMenu : [ [ 10, 25, 50, 100, 500, -1 ],
[ 10, 25, 50, 100, 500, 'All' ] ],
language : {
url : '//cdn.datatables.net/plug-ins/1.12.1/i18n/es-CL.json'
},
responsive : "true",
dom : "<'row'<'col-sm-12 col-md-6'B><'col-sm-12 col-md-6'f>>"
+ "<'row'<'col-sm-12'tr>>"
+ "<'row'<'col-sm-12 col-md-4'l><'col-sm-12 col-md-4'i><'col-sm-12 col-md-4'p>>",
buttons : [ {
extend : 'pdfHtml5',
text : '<img src="img/file.png"/>',
titleAttr : 'Exportar a PDF',
className : 'btn btn-danger'
}, {
extend : 'excelHtml5',
text : '<img src="img/excel.png"/>',
titleAttr : 'Exportar a Excel',
className : 'btn btn-success'
}, {
extend : 'print',
text : '<img src="img/printer.png"/>',
titleAttr : 'Imprimir',
className : 'btn btn-info'
} ]
});
$("#consultar").click(function () {
var fecha1 = document.getElementById("dtpicker1").value;
var fecha2 = document.getElementById("dtpicker2").value;
var id = <%=idusuario%>;
$.ajax({
destroy:true,
processing: true,
serverSide:true,
serverMethod:'post',
searching:true,
dataType: 'json',
type : 'POST',
url : 'compras_por_cliente.jsp',
data : {
'idusuario' : id,
'fecha1' : fecha1,
'fecha2' : fecha2
},
//async : true,
success: function(data) {
var table = $('#table_id').DataTable();
//$('#table_id').DataTable().ajax.reload(null,false);
table.clear().rows.add(data).draw();
console.log(data);
//table.draw();
}
//dataType : 'json',
})
//console.log(data);
//window.location.reload();
});
});
</script>
and the java file that returns the json is the following
public class consulta_ventas {
public static JSONArray consulta (String fecha1, String fecha2, String id) {
JSONArray arreglo = new JSONArray();
Connection coneccion = null;
conexion conecta = new conexion();
coneccion=conecta.conectar();
Statement st = null;
ResultSet rs = null;
try {
st = coneccion.createStatement();
String sql = "call compras_por_cliente(" +"'"+ fecha1 +"'"+ "," +"'"+ fecha2 +"'"+ "," + id+ ")";
rs = st.executeQuery(sql);
while (rs.next()){
JSONObject json = new JSONObject();
json.put("rut", rs.getString("rut_cli")) ;
json.put("nombre", rs.getString("nombre_cli") + " " + rs.getString("apepat_cli") + " " + rs.getString("apemat_cli")) ;
json.put("fecha", rs.getString("detalles_fecha"));
json.put("total", rs.getInt("total")) ;
arreglo.put(json);
}
st.close();
rs.close();
coneccion.close();
} catch (Exception e) {
/*out.print("error mysql " + e);*/
}
~~~~
return arreglo;
}
}
Answers
Assuming
datais an array of data, then what you have there should work.If you post a link to a test case showing the issue I would be happy to take a look and help. Please also see the forum rules which requests a test case when posting a new question.
Allan
Unfortunately I can't upload a test case, because it's not implemented yet, I'm just creating it in Eclipse with localhost
Checking, the problem is occurring here,
table.clear().rows.add(data).draw();
because the data brings the entire html page and if I change it to
table.clear().rows.add(<%=arreglo%>).draw();
it brings the same arrangement with which it entered the first time
datahas the whole HTML for the page? If so, then yes, that is going to be a problem, since, per the documentationrows.add()expects an array of data that DataTables should display.If you have the HTML for the page / table, render that into the document first and then initialise DataTables on top of that.
Allan