java - Display a pivot table on jsp -
i'm writing webapp there pivoted data has displayed in jsp table. query works fine in sql server. i'm unable understand how project same on jsp.
there 2 datepickers in jsp. when select 1 or both, table full data(based on date ranges) has displayed, if there no date selected, need pivoted data table displayed. below codes.
table.jsp
<html> <head> <link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css"> <script src="//code.jquery.com/jquery-1.10.2.js"></script> <script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script> <link rel="stylesheet" href="/resources/demos/style.css"> </head> <body> <marquee> <h1>this example of ajax</h1> </marquee> <p> date: <input type="text" id="startdatepicker"> </p> <p> date: <input type="text" id="enddatepicker"> </p> <span id="somediv"> <span id="err">select correct dates</span> </span> <script type="text/javascript" src="tablegenerator.js"></script> </body> </html> tablegenerator.js
var startdate = $("#startdatepicker").datepicker({ dateformat : 'yy-mm-dd', onclose : function(selecteddate) { $("#enddatepicker").datepicker("option", "mindate", selecteddate); } }); var enddate = $("#enddatepicker").datepicker({ dateformat : 'yy-mm-dd', onclose : function(selecteddate) { $("#startdatepicker").datepicker("option", "maxdate", selecteddate); } }); $('#startdatepicker,#enddatepicker').change( function() { $('#somediv > table').remove(); $.getjson('controller', { 'startdate' : startdate.val(), 'enddate' : enddate.val() }, function(searchlist) { var $table = $('<table>').appendto($('#somediv')); if (searchlist.length < 1) { $('#err').show(); $('#err').text("no records found in specified dates"); } else { alert("second block"); $.each(searchlist, function(index, userbean) { $('#err').hide(); $('<tr>').appendto($table).append( $('<td>').text(userbean.casenumber)).append( $('<td>').text(userbean.caseowner)).append( $('<td>').text(userbean.status)).append( $('<td>').text(userbean.issue)).append( $('<td>').text(userbean.reason)).append( $('<td>').text(userbean.age)); }); } }); }); datadao.java
package com.dao; import java.sql.connection; import java.sql.preparedstatement; import java.sql.resultset; import java.sql.sqlexception; import java.util.arraylist; import org.bean.userbean; public class datadao { private connection connection; public datadao() throws exception { connection = dbutility.getconnection(); } public arraylist<userbean> getframework(string startdate, string enddate) throws sqlexception { string startdatefromcontroller = startdate; string enddatefromcontroller = enddate; system.out.println("dao values " + startdatefromcontroller + " , " + enddatefromcontroller); arraylist<userbean> list = new arraylist<userbean>(); preparedstatement ps = null; try { string query; if (!(startdatefromcontroller == null) && enddatefromcontroller == null) { system.out.println("first block imvoked"); query = "select * statustable convert(date, [start time]) >= ?"; ps = connection.preparestatement(query); ps.setstring(1, startdatefromcontroller); } else if (startdatefromcontroller == null && !(enddatefromcontroller == null)) { system.out.println("end date " + enddatefromcontroller); system.out.println("second"); query = "select * statustable convert(date, [start time]) <= ?"; ps = connection.preparestatement(query); ps.setstring(1, enddatefromcontroller); } else if (startdatefromcontroller == null && enddatefromcontroller == null) { system.out.println("thuird block"); query = "with ctepivot (select * (select [case owner] agent, [final status] statustable) src pivot(count([final status])" + " [final status] in ([closed], [open], [reassigned])) piv) select * ctepivot union select 'total', sum([closed]), sum([open]), sum([reassigned]) ctepivot"; system.out.println("query invoked is" + query); ps = connection.preparestatement(query); } else { system.out.println("forth block"); query = "select * statustable convert(date, [start time]) between ? , ?"; ps = connection.preparestatement(query); ps.setstring(1, startdate); ps.setstring(2, enddate); } resultset rs = ps.executequery(); if (!rs.next()) { system.out.println("no records found"); } else { { userbean userbean = new userbean(); userbean.setcasenumber(rs.getstring("case number")); userbean.setcaseowner(rs.getstring("case owner")); userbean.setstatus(rs.getstring("status")); userbean.setissue(rs.getstring("issue")); userbean.setreason(rs.getstring("reason")); userbean.setdateopened(rs.getstring("date/time opened")); userbean.setage(rs.getint("age")); list.add(userbean); } while (rs.next()); } } catch (exception e) { system.out.println(e + " dao error"); } return list; } } controller.java
import java.io.ioexception; import java.text.simpledateformat; import java.util.arraylist; import java.util.date; import javax.servlet.servletexception; import javax.servlet.annotation.webservlet; import javax.servlet.http.httpservlet; import javax.servlet.http.httpservletrequest; import javax.servlet.http.httpservletresponse; import org.bean.userbean; import com.dao.datadao; import com.google.gson.gson; @webservlet("/controller") public class controller extends httpservlet { private static final long serialversionuid = 1l; protected void doget(httpservletrequest request, httpservletresponse response) throws servletexception, ioexception { try { /* date start */ string startdatestr = request.getparameter("startdate"); string enddatestr = request.getparameter("enddate"); system.out.println("start date got " + startdatestr + " , end date " + enddatestr); simpledateformat sdf = new simpledateformat("yyyy-mm-dd"); simpledateformat print = new simpledateformat("yyyy-mm-dd"); date startparseddate = null, endparseddate = null; string startdate = null, enddate = null; if (startdatestr != null && !startdatestr.equals("")) { startparseddate = sdf.parse(startdatestr); startdate = print.format(startparseddate); } if (enddatestr != null && !enddatestr.equals("")) { endparseddate = sdf.parse(enddatestr); enddate = print.format(endparseddate); } system.out.println(startdate + " value , " + enddate); /* date end */ datadao datadao = new datadao(); arraylist<userbean> list = datadao.getframework(startdate, enddate); string searchlist = new gson().tojson(list); response.setcontenttype("application/json"); response.setcharacterencoding("utf-8"); response.getwriter().write(searchlist); system.out.println("servlet done"); } catch (exception e) { system.err.println(e.getmessage() + " servlet errotr"); } } } mysqlquery working fine in sql server
with ctepivot ( select * ( select [case owner] agent, [final status] statustable ) src pivot ( count([final status]) [final status] in ([closed], [open], [reassigned]) ) piv ) select * ctepivot union select 'total', sum([closed]), sum([open]), sum([reassigned]) ctepivot unable underand how reframe below based on selection. error thrown here.
userbean userbean = new userbean(); userbean.setcasenumber(rs.getstring("case number")); userbean.setcaseowner(rs.getstring("case owner")); userbean.setstatus(rs.getstring("status")); userbean.setissue(rs.getstring("issue")); userbean.setreason(rs.getstring("reason")); userbean.setdateopened(rs.getstring("date/time opened")); userbean.setage(rs.getint("age")); list.add(userbean); the exception i'm getting
com.microsoft.sqlserver.jdbc.sqlserverexception: column name case number not valid. dao error below sql table.
when run query in sql server below result.
please let me know how can done.
Comments
Post a Comment