Ola pessoal!
Gostaria de uma ajuda de vcs. È o seguinte: Tenho uma pagina jsp que o usuario escolhe alguns parametros e pede para gerar um relatorio. Eu montei o relatorio no iReport, compilei, e testei no iReport passando alguns parametros na mao, que no caso funcionou. Eu peguei o .jasper gerado e coloquei no meu projeto. Quando rodo a pagina jsp, esta dando um erro na query sql que prepara alguns parametros para o .jasper, e logo apos o erro sou direcionado para uma pagina contendo o codigo do meu servlet. Estou meio que perdido, pois nunca mexi com relatorios e nem servlets. Por favor, peço que me ajudem, pois ja recorri a tutoriais, e exemplos, mas mesmo assim nao consegui resolver. Abaixo estão os meus codigos.
Primeira Pagina (report_filters.jsp)
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" session="true" pageEncoding="ISO-8859-1"%>
<%@page import="mcopy.lider.conection.*"%>
<%@page import="java.sql.*"%>
<%@page import="java.util.*"%>
<%@page import="java.text.*"%>
<%@include file="../index_topo.jsp"%>
<script type="text/javascript" src="../JS/POPUP/jquery-latest.pack.js"></script>
<script type="text/javascript" src="../JS/POPUP/jquery-dom.js"></script>
<script type="text/javascript" src="../JS/POPUP/tipos.js"></script>
<script type="text/javascript" src="../JS/POPUP/popup.js"></script>
<script type="text/javascript">
<!--
function mascaraData(objeto, evt){
if(objeto.value.length == 10){
return false;
}
var charCode = (evt.which) ? evt.which : event.keyCode;
if (charCode > 31 && (charCode < 48 || charCode > 57))
return false;
if (objeto.value.indexOf("/") == -1 && objeto.value.length > 5){ objeto.value = ""; }
if ((objeto.value.length == 2 || objeto.value.length == 5)&& charCode != 8){
objeto.value +="/";
}
return true;
}
//-->
</script>
<center style="position: relative">
<table width="700" align="center" cellpadding="1" cellspacing="1">
<tr style="background-color: #363636;">
<td height="30" width="100%" colspan="3" align="center" class="texto_normal">FILTRO GERAL DE RELATORIOS</td>
<tr style="background-color: #1C1C1C;" class="texto_normal_small" align="center">
<td height="10" width="200"><a href="#" onclick="popup.show();">RELATORIO POR USUARIO</a></td>
<td height="10" width="300"><a href="report_">RELATORIO POR CENTRO DE CUSTO</a></td>
<td height="10" width="200"><a href="report_">RELATORIO MISTO</a></td>
</table>
<div id="popup">
<div class="conteudo">
<table width="700" align="center" cellpadding="1" cellspacing="1">
<tr>
<form id="form1" name="form1" method="post" action="report_usuarios.jsp">
<tr style="background-color: #363636;">
<td height="30" width="100%" align="left" class="texto_normal">Filtro de Usuarios</td>
</tr>
<tr class="texto_normal_small" align="center">
<td height="10" width="400" align="left">Selecione:
<select name="user">
<option value="" selected="selected">Usuario</option>
<%
String sql = "SELECT * FROM USERS ORDER BY LOGIN ";
ConexaoBD conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
String username = "";
int matricula = 0;
conn = new ConexaoBD(ConfiguracoeBDDAO.LOGIN,ConfiguracoeBDDAO.SENHA, ConfiguracoeBDDAO.DRIVER, ConfiguracoeBDDAO.URL);
conn.conectar();
ps = conn.getStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
matricula = rs.getInt("MATRICULA");
username = rs.getString("LOGIN");
%>
<option value="<%=username%>"><%=username%></option>
<%
}
%>
</select>
</td>
<tr class="texto_normal_small" >
<td colspan="5">Período:
<input type=text name="datainicio" id="datainicio" size="12" align="left" onkeypress="return mascaraData(this, event);">
a
<input type="Text" name="datafinal" id="datafinal" size="12" align="left" onkeypress="return mascaraData(this, event);" >
</td>
</tr>
<tr>
<td>
<input type="submit" value="Gerar Relatorio" class="button">
</td>
</tr>
</form>
</tr>
</table>
</div>
</div>
<script type="text/javascript">
//<![CDATA[
var popup = new Popup("popup",300,300);
popup.setTitle("Relatorio por Usuarios");
popup.init();
//]]>
</script>
</center>
<%@include file="../index_fundo.jsp"%>
Segunda Pagina (report_usuarios.jsp)
<%@ page import="net.sf.jasperreports.view.*"%>
<%@ page import="net.sf.jasperreports.engine.*"%>
<%@ page import="java.util.*"%>
<%@ page import="java.io.*"%>
<%@ page import="javax.sql.*"%>
<%@ page import="java.sql.*,java.net.*"%>
<%@ page import="javax.naming.*"%>
<%@ page import="java.lang.String"%>
<%@ page import="java.sql.*"%>
<%@ page import="mcopy.lider.conection.ConexaoReport"%>
<%@ page session="true"%>
<%@ page import="java.text.DateFormat,java.text.SimpleDateFormat,java.util.Date"%>
<%@ page import="mcopy.lider.conection.*"%>
<%@ page import="java.sql.*"%>
<%@ page import="java.util.*"%>
<%@ page import="java.text.*"%>
<%
String username = request.getParameter("user");
String data1 = request.getParameter("datainicio").toString();
String data2 = request.getParameter("datafinal").toString();
ConexaoBD conn2 = null;
PreparedStatement ps2 = null;
ResultSet rs2 = null;
conn2 = new ConexaoBD(ConfiguracoeBDDAO.LOGIN,ConfiguracoeBDDAO.SENHA, ConfiguracoeBDDAO.DRIVER,ConfiguracoeBDDAO.URL);
conn2.conectar();
ps2 = conn2.getStatement("SELECT * FROM USERS WHERE LOGIN = ?");
ps2.setString(1, username);
System.out.println("Usuario "+username+"\n");
rs2 = ps2.executeQuery();
String cc = "";
if(rs2.next()){
cc = rs2.getString("CC"); //Pega o codigo de centro de custo do usuario selecionado.
}
System.out.println("Centro custo "+cc+"\n");
String[] cc_split = cc.split("");//Splita o codigo do centro de custo
System.out.println("CC split "+cc_split[1]+"\n");
String centro_custo_aplit = (cc_split[1]); // Pega o primeiro caracter do codigo, que representa o nome do centro de custo
String cc_para_consulta = centro_custo_aplit + "00"; // Concatena o codigo centro de custo com 00 para fazer a consulta na tabela "Centro_Custo" e retornar o nome do centro de custo
ConexaoBD conn3 = null;
PreparedStatement ps3 = null;
ResultSet rs3 = null;
conn3 = new ConexaoBD(ConfiguracoeBDDAO.LOGIN,ConfiguracoeBDDAO.SENHA, ConfiguracoeBDDAO.DRIVER, ConfiguracoeBDDAO.URL);
conn3.conectar();
ps3 = conn3.getStatement("SELECT * FROM CENTRO_CUSTO WHERE CC = ?");
ps3.setString(1, cc_para_consulta);
rs3 = ps3.executeQuery();
String cc_completo = "";
rs3.next();
cc_completo = rs3.getString("DESCRICAO"); //Pega o nome do centro de custo na tabela Centro_Custo
System.out.println(" Centro de custo completo "+cc_completo+"\n");
ConexaoBD conn4 = null;
PreparedStatement ps4 = null;
ResultSet rs4 = null;
int cont=0;
int cont_pag = 0;
int total_pag = 0;
float cont_valor =0;
float total_valor =0;
int total_jobs = 0;
conn4 = new ConexaoBD(ConfiguracoeBDDAO.LOGIN,ConfiguracoeBDDAO.SENHA, ConfiguracoeBDDAO.DRIVER, ConfiguracoeBDDAO.URL);
conn4.conectar();
String sql_totais= "SELECT SUM(BHZ.PAGES) AS TOTAL_PAG, SUM(BHZ.DOCCOST) AS VALOR_TOTAL, COUNT(*) AS TOTAL_JOBS, BHZ.UNIDADE FROM BASE_BHZ BHZ WHERE BHZ.USERNAME = '"+username+"' AND BHZ.SUBMITDATE between '"+data1+"' and '"+data2+"' GROUP BY BHZ.SERVERNAME, BHZ.UNIDADE ";
sql_totais+= " UNION (SELECT SUM(BSB.PAGES), SUM(BSB.DOCCOST), COUNT(*), BSB.UNIDADE FROM BASE_BSB BSB WHERE BSB.USERNAME = '"+username+"' and BSB.SUBMITDATE between '"+data1+"' and '"+data2+"' GROUP BY BSB.SERVERNAME, BSB.UNIDADE) ";
sql_totais+= " UNION (SELECT SUM(JAC.PAGES), SUM(JAC.DOCCOST), COUNT(*), JAC.UNIDADE FROM BASE_JAC JAC WHERE JAC.USERNAME = '"+username+"' and JAC.SUBMITDATE between '"+data1+"' and '"+data2+"' GROUP BY JAC.SERVERNAME, JAC.UNIDADE) ";
sql_totais+= " UNION (SELECT SUM(MEA.PAGES), SUM(MEA.DOCCOST), COUNT(*), MEA.UNIDADE FROM BASE_MEA MEA WHERE MEA.USERNAME = '"+username+"' and MEA.SUBMITDATE between '"+data1+"' and '"+data2+"' GROUP BY MEA.SERVERNAME, MEA.UNIDADE) ";
sql_totais+= " UNION (SELECT SUM(NVT.PAGES), SUM(NVT.DOCCOST), COUNT(*), NVT.UNIDADE FROM BASE_NVT NVT WHERE NVT.USERNAME = '"+username+"' and NVT.SUBMITDATE between '"+data1+"' and '"+data2+"' GROUP BY NVT.SERVERNAME, NVT.UNIDADE) ";
sql_totais+= " UNION (SELECT SUM(RIO.PAGES), SUM(RIO.DOCCOST), COUNT(*), RIO.UNIDADE FROM BASE_RIO RIO WHERE RIO.USERNAME = '"+username+"' and RIO.SUBMITDATE between '"+data1+"' and '"+data2+"' GROUP BY RIO.SERVERNAME, RIO.UNIDADE) ";
sql_totais+= " UNION (SELECT SUM(SAO.PAGES), SUM(SAO.DOCCOST), COUNT(*), SAO.UNIDADE FROM BASE_SAO SAO WHERE SAO.USERNAME = '"+username+"' and SAO.SUBMITDATE between '"+data1+"' and '"+data2+"' GROUP BY SAO.SERVERNAME, SAO.UNIDADE) ";
sql_totais+= " UNION (SELECT SUM(TME.PAGES), SUM(TME.DOCCOST), COUNT(*), TME.UNIDADE FROM BASE_TME TME WHERE TME.USERNAME = '"+username+"' and TME.SUBMITDATE between '"+data1+"' and '"+data2+"' GROUP BY TME.SERVERNAME, TME.UNIDADE) ";
sql_totais+= " UNION (SELECT SUM(VIX.PAGES), SUM(VIX.DOCCOST), COUNT(*), VIX.UNIDADE FROM BASE_VIX VIX WHERE VIX.USERNAME = '"+username+"' and VIX.SUBMITDATE between '"+data1+"' and '"+data2+"' GROUP BY VIX.SERVERNAME, VIX.UNIDADE) ";
try{
ps4 = conn4.getStatement(sql_totais);
try{
rs4 = ps4.executeQuery(); ///[color=red] Fiz um debug, e quando entro nesse ponto o programa da um erro(codigo_erro abaixo), ja testei a query no SQL SERVER e ela me retorna exatamente o que eu quero[/color]
while (rs4.next()){
cont_pag = rs4.getInt("TOTAL_PAG");
total_pag = total_pag + cont_pag;
cont_valor = rs4.getFloat("VALOR_TOTAL");
total_valor = total_valor + cont_valor;
total_jobs = rs4.getInt("TOTAL_JOBS");
}
}catch(Exception ex){
ex.printStackTrace();
System.out.println("Erro ao ..."+ex);
}
}catch(SQLException e){
e.printStackTrace();
System.out.println("Erro sql_totais>>"+e);
}
NumberFormat nf = new DecimalFormat("###,##0.00");
String valor_format = nf.format(total_valor);
String centro_custo = cc_completo;
session.setAttribute("USERNAME", username);
session.setAttribute("DATA1", data1);
session.setAttribute("DATA2", data2);
session.setAttribute("CC", centro_custo);
//session.setAttribute("QUERY", sql_report);
session.setAttribute("TOTAL_PAG", total_pag);
session.setAttribute("TOTAL_VALOR", valor_format);
session.setAttribute("TOTAL_JOBS", total_jobs);
response.sendRedirect("ServletRelatorio.java"); [color=red] // Redireciona para meu Sevlet o qual processa os dados e gera o relatorio[/color]
%>
Servlet (ServletRelatorio.java)
package mcopy.lider.servlet;
import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Date;
import java.util.HashMap;
import java.util.Locale;
import java.util.Map;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperReport;
import net.sf.jasperreports.engine.JasperRunManager;
import net.sf.jasperreports.engine.util.JRLoader;
import mcopy.lider.conection.*;
/**
* Servlet implementation class for Servlet: ServletRelatorio
*
*/
public class ServletRelatorio extends javax.servlet.http.HttpServlet implements javax.servlet.Servlet {
static final long serialVersionUID = 1L;
/* (non-Java-doc)
* @see javax.servlet.http.HttpServlet#HttpServlet()
*/
public ServletRelatorio() {
super();
}
/* (non-Java-doc)
* @see javax.servlet.http.HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
executaAcao(request, response);
}
/* (non-Java-doc)
* @see javax.servlet.http.HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
executaAcao(request, response);
}
@SuppressWarnings({ "unchecked", "deprecation" })
public void executaAcao(HttpServletRequest request, HttpServletResponse response) throws IOException{
Locale.setDefault(new Locale("pt", "br"));
HashMap<String, String>global = (HashMap<String, String>)request.getSession().getAttribute("global");
ConexaoReport conexao = new ConexaoReport();
Connection conn = null;
try {
conn = conexao.getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
System.out.println("Erro ao criar conexão!");
e.printStackTrace();
}
String pathJasper = getServletContext().getRealPath("/WebContent/REPORT/")+ "/";
// A variavel path armazena o caminho real para o contexto
// isso é util pois o seu web container pode estar instalado em lugares diferentes
String path = getServletContext().getRealPath("/");
String username = (String)request.getSession().getAttribute("USERNAME");
Date DATA1 = (Date)request.getSession().getAttribute("DATA1");
Date DATA2 = (Date)request.getSession().getAttribute("DATA2");
String centro_custo = (String)request.getSession().getAttribute("CC");
int total_pag = (Integer)request.getSession().getAttribute("TOTAL_PAG");
int total_jobs = (Integer)request.getSession().getAttribute("TOTAL_JOBS");
float total_valor = (Float)request.getSession().getAttribute("TOTAL_VALOR");
//Parametros
Map parametros = new HashMap();
//parametros.put("QUERY", query);
parametros.put("DATA1", DATA1);
parametros.put("DATA2", DATA2);
parametros.put("CC", centro_custo);
parametros.put("USERNAME", username);
parametros.put("TOTAL_PAG", total_pag);
parametros.put("TOTAL_VALOR", total_valor);
parametros.put("TOTAL_JOBS", total_jobs);
JasperReport jasperReport;
try {
// Aqui ele cria o relatório
JasperPrint impressao = JasperFillManager.fillReport(pathJasper + "RelatorioUsuario.jasper", parametros, conn);
// Grava o relatório em disco em pdf
JasperManager.printReportToPdfFile(impressao, path + "/RelatorioUsuario.pdf");
// Redireciona para o pdf gerado
response.sendRedirect("RelatorioUsuario.pdf");
} catch (JRException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
}
Erro gerado no console:
java.sql.SQLException: O tipo de dados de operando varchar é inválido para o operador sum.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:778)
at org.apache.jsp.REPORT.report_005fusuarios_jsp._jspService(report_005fusuarios_jsp.java:170)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
at java.lang.Thread.run(Unknown Source)
Erro ao ..java.sql.SQLException: O tipo de dados de operando varchar é inválido para o operador sum.
Alguem saberia me dizer qual o problema da query “sql_totais” na pagina report_usuarios, e porque que ao inves de gerar meu relatorio o programa esta exibindo meu codigo java?
Bom, desde ja agradeço pela ajuda pessoal.