Arquivo Discussoes de Programacao e Tecnologia 
Fala galera
estou desenvolvendo uma aplicação web e preciso do seguinte:
Exportar dados de uma tabela para um arquivo .CSV
tudo bem, fiz uma consulta paginada, monto uma lista a cada 100 objetos e insiro no meu CSV
ao completar essa consulta e preencher o csv completo, a conexão com o cliente dá um timeout, pois essa exportação demora um pouco
gostaria de saber se tem com eu aumentar esse timeout para poder fazer essa exportação
Obrigado! :shock:
oi,
Como você está criando a conexão com o Servidor, o que está usando?
Poste o código aqui.
abs
Utilizo Java+Spring+JPA
package com.iw.il.manager.controller.enrollment.event;
import java.io.IOException;
import java.text.MessageFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.web.servlet.ModelAndView;
import com.ingressolivre.core.dal.DAOFactory;
import com.ingressolivre.core.model.business.Attraction;
import com.ingressolivre.core.model.business.Category;
import com.ingressolivre.core.model.business.Event;
import com.ingressolivre.core.model.business.Media;
import com.ingressolivre.core.model.business.Order;
import com.ingressolivre.core.model.enums.ApprovalEnum;
import com.iw.il.manager.controller.AbstractController;
import com.iw.library.config.Configurator;
import com.iw.library.logging.Logger;
import com.iw.library.query.QueryAdapter;
import com.iw.library.report.csv.CSVElement;
import com.iw.library.report.csv.CSVFile;
import com.iw.library.report.csv.CSVPattern;
import com.iw.library.util.Filter;
import com.iw.library.util.Util;
import com.iw.library.validation.Convert;
import com.iw.library.validation.Format;
import com.iw.library.validation.Validator;
public class ListExportCsv extends AbstractController {
@Override
protected void handleRequest(HttpServletRequest request,
HttpServletResponse response, ModelAndView mv) throws Exception {
try {
response.setContentType("application/csv");
response.setHeader("Content-Disposition", getCsvFileName());
createCSVFile(mv, request, response.getOutputStream());
} catch (Exception e) {
long error = Util.getErrorCode();
Logger.error(e, "[", error, "] [EventExportCsv] handleRequest: ",
e.getMessage());
setErrorMsg(mv, "Codigo de erro: " + error);
mv.setViewName(JSP_ERROR);
}
setNoView();
}
private String getCsvFileName() {
String filename = Configurator.get("reports.events.filename");
filename = MessageFormat
.format(filename, Format.toTimestamp("dd-MM-yyyy_HH:mm:ss",
new java.util.Date()), ".csv;");
return Convert.toString("filename=", filename);
}
private void createCSVFile(ModelAndView mv, HttpServletRequest request,
ServletOutputStream servletOutputStream) throws IOException {
String sysdomain = Convert.toString("http://",
Configurator.get("system.site.domain"));
CSVFile f = new CSVFile();
f.setPattern(CSVPattern.EXCEL);
CSVElement e;
e = new CSVElement();
e.add("id_evento").add("nome_evento").add("data_evento")
.add("hora_evento").add("url_evento").add("categoria_evento")
.add("subcategorias_evento").add("imagem_url_evento")
.add("id_local").add("nome_local").add("capacidade_local")
.add("site_local").add("cep_local").add("rua_local")
.add("numero_local").add("complemento_local")
.add("referencia_local").add("bairro_local")
.add("cidade_local").add("sigla_estado_local")
.add("categoria_local").add("imagem_local")
.add("id_atracao_principal").add("nome_atracao_principal")
.add("site_atracao_principal")
.add("categoria_atracao_principal")
.add("subcategorias_atracao_principal")
.add("imagem_atracao_principal")
.add("outras_atracoes");
f.add(e);
long first = 0;
long maxResults = 200;
long totalEvents = getEventListSize(mv, request);
long totalPages = 1;
if (totalEvents < 200) {
totalPages = 1;
} else {
totalPages = totalEvents / maxResults;
}
for (int i = 0; i <= totalPages; i++) {
List<Event> events = getEventsList(mv, request, first, maxResults);
for (Event event : events) {
e = new CSVElement();
e.add(event.getId());
e.add(Util.removeAccents(event.getName()));
e.add(Format.toDate(event.getDate()));
e.add(Format.toTime(event.getDate()));
e.add(event.getPartnerUrl());
e.add(Util.removeAccents(event.getCategory().getName()));
String categories = "";
int count = 1;
for (Category category : event.getCategories()) {
if (count == 1) {
categories = Convert.toString(category.getName());
} else {
categories = Convert.toString("|", category.getName());
}
}
e.add(Util.removeAccents(categories));
e.add(Convert
.toString(sysdomain, event.getThumbnail().getUrl()));
if (Validator.isValid(event.getPlace())) {
e.add(event.getPlace().getId())
.add(Util.removeAccents(event.getPlace().getName()))
.add(event.getPlace().getCapacity())
.add(event.getPlace().getWebsite())
.add(event.getPlace().getAddress().getCep())
.add(Util.removeAccents(event.getPlace()
.getAddress().getStreet()))
.add(event.getPlace().getAddress().getNumber())
.add(Util.removeAccents(event.getPlace()
.getAddress().getComplement()))
.add(Util.removeAccents(event.getPlace()
.getAddress().getReference()))
.add(Util.removeAccents(event.getPlace()
.getAddress().getDistrict().getName()))
.add(Util.removeAccents(event.getPlace()
.getAddress().getCity().getName()))
.add(Util.removeAccents(event.getPlace()
.getAddress().getState().getSymbol()))
.add(Util.removeAccents(event.getPlace()
.getCategory().getName()))
.add(Convert.toString(sysdomain, event.getPlace()
.getThumbnail().getUrl()));
} else {
e.add("").add("").add("").add("").add("").add("").add("")
.add("").add("").add("").add("").add("").add("")
.add("");
}
if (Validator.isValid(event.getAttraction())) {
e.add(event.getAttraction().getId());
e.add(Util.removeAccents(event.getAttraction().getName()));
String sites = "";
count = 1;
for (Media site : event.getAttraction().getSites()) {
if (count == 1) {
sites = Convert.toString(site.getName());
}
count++;
}
e.add(Util.removeAccents(sites));
e.add(Util.removeAccents(event.getAttraction()
.getCategory().getName()));
categories = "";
count = 1;
for (Category category : event.getAttraction()
.getCategories()) {
if (count == 1) {
categories = Convert.toString(category.getName());
} else {
categories = Convert.toString("|",
category.getName());
}
}
e.add(Util.removeAccents(categories));
e.add(Convert.toString(sysdomain, event.getAttraction()
.getThumbnail().getUrl()));
} else {
e.add("").add("").add("").add("").add("");
}
for (Attraction attraction : event.getOtherAttractions()) {
if (Validator.isValid(attraction)) {
e.add(attraction.getId());
e.add(Util.removeAccents(attraction.getName()));
String sites = "";
count = 1;
for (Media site : attraction.getSites()) {
if (count == 1) {
sites = Convert.toString(site.getName());
}
count++;
}
e.add(Util.removeAccents(sites));
e.add(Util.removeAccents(attraction.getCategory()
.getName()));
categories = "";
count = 1;
for (Category category : attraction.getCategories()) {
if (count == 1) {
categories = Convert.toString(category
.getName());
} else {
categories = Convert.toString("|",
category.getName());
}
}
e.add(Util.removeAccents(categories));
e.add(Convert.toString(sysdomain, attraction
.getThumbnail().getUrl()));
}
}
f.add(e);
}
first = first + (maxResults);
Logger.info(first, "adicionados");
}
f.write(servletOutputStream);
}
private List<Event> getEventsList(ModelAndView mv,
HttpServletRequest request, long first, long max) {
String name = request.getParameter("name");
Date date = Convert.toDate(request.getParameter("date"));
long category = Convert.toLong(request.getParameter("category"));
String status = request.getParameter("status");
String place = request.getParameter("place");
String owner = request.getParameter("owner");
boolean checked = false;
java.util.List<Event> events = new java.util.ArrayList<Event>();
Filter filter = new Filter();
StringBuilder sql = new StringBuilder();
sql.append("SELECT o FROM Event AS o");
if (Validator.isValid(name) && (!"null".equalsIgnoreCase(name))) {
if (checked) {
sql.append(" AND o.searchName like :eventName");
} else {
sql.append(" WHERE o.searchName like :eventName");
}
checked = true;
filter.addFilter("eventName", Util.removeAccents(name)
.toUpperCase());
}
if (Validator.isValid(status) && (!"null".equalsIgnoreCase(status))) {
if (checked) {
sql.append(" AND o.status = :status");
} else {
sql.append(" WHERE o.status = :status");
}
if (status.equals("APPROVED")) {
filter.addFilter("status", ApprovalEnum.APPROVED);
}
if (status.equals("UNAPPROVED")) {
filter.addFilter("status", ApprovalEnum.UNAPPROVED);
}
if (status.equals("PENDING")) {
filter.addFilter("status", ApprovalEnum.PENDING);
}
checked = true;
}
if (date != null) {
Calendar lastCal = Calendar.getInstance();
lastCal.setTime(date);
lastCal.add(Calendar.DAY_OF_MONTH, 1);
if (checked) {
sql.append(" AND o.date BETWEEN :date AND :last");
} else {
sql.append(" WHERE o.date BETWEEN :date AND :last");
}
checked = true;
filter.addFilter("date", date);
filter.addFilter("last", lastCal.getTime());
}
if (category != 0) {
Category c = DAOFactory.create(Category.class).get(
Convert.toLong(category));
if (checked) {
sql.append(" AND :category MEMBER OF o.categories");
} else {
sql.append(" WHERE :category MEMBER OF o.categories");
}
checked = true;
filter.addFilter("category", c);
}
if (Validator.isValid(place) && (!"null".equalsIgnoreCase(place))) {
if (checked) {
sql.append(" AND o.place.searchName LIKE :place");
} else {
sql.append(" WHERE o.place.searchName LIKE :place");
}
checked = true;
filter.addFilter("place", Util.removeAccents(place).toUpperCase());
}
if (Validator.isValid(owner) && (!"null".equalsIgnoreCase(owner))) {
if (checked) {
sql.append(" AND UPPER(o.organizer.name) LIKE :owner");
} else {
sql.append(" WHERE UPPER(o.organizer.name) LIKE :owner");
}
checked = true;
filter.addFilter("owner", owner.toUpperCase());
}
sql.append(" ORDER BY o.date,o.name");
filter.setLimits(Convert.toInteger(first), Convert.toInteger(max));
events = DAOFactory.create(Event.class).executeAll(
new QueryAdapter(sql.toString()), filter);
return events;
}
public String cleanString(String str) {
String word = str;
word = word.replaceAll("\\n", "");
word = word.replaceAll("\\r", "");
word = word.replaceAll("\\t", "");
word = word.replaceAll("\"", "");
word = Util.removeAccents(word);
// word = removeCharsCSV(word, "");
return word;
}
public String removeCharsCSV(String str, String replace) {
String removed = "";
if (Validator.isValid(str)) {
removed = str;
char[] chars = { '-', '%', '$', '#', '@', '&', '*', ',', '{', '}',
'(', ')', '[', ']', '=', '+', '_', '|', '/', ',', '\\',
'<', '>', ';', ':', '\'', '"', '~', '^', '`', '\u00A2',
'\u00AC', '\u00B0', '\u00BA', '\u00AA', '\u00B9', '\u00B2',
'\u00B3', '\u00A7', '\u00A3', };
for (char c : chars) {
removed = removed.replaceAll("\\" + c, replace);
}
}
return removed;
}
private long getEventListSize(ModelAndView mv, HttpServletRequest request) {
String name = request.getParameter("name");
Date date = Convert.toDate(request.getParameter("date"));
long category = Convert.toLong(request.getParameter("category"));
String status = request.getParameter("status");
String place = request.getParameter("place");
String owner = request.getParameter("owner");
boolean checked = false;
Filter filter = new Filter();
StringBuilder sqlCountEvent = new StringBuilder();
sqlCountEvent.append("SELECT COUNT(o) FROM Event AS o");
if (Validator.isValid(name) && (!"null".equalsIgnoreCase(name))) {
if (checked) {
sqlCountEvent.append(" AND o.searchName like :eventName");
} else {
sqlCountEvent.append(" WHERE o.searchName like :eventName");
}
checked = true;
filter.addFilter("eventName", Util.removeAccents(name)
.toUpperCase());
}
if (Validator.isValid(status) && (!"null".equalsIgnoreCase(status))) {
if (checked) {
sqlCountEvent.append(" AND o.status = :status");
} else {
sqlCountEvent.append(" WHERE o.status = :status");
}
if (status.equals("APPROVED")) {
filter.addFilter("status", ApprovalEnum.APPROVED);
}
if (status.equals("UNAPPROVED")) {
filter.addFilter("status", ApprovalEnum.UNAPPROVED);
}
if (status.equals("PENDING")) {
filter.addFilter("status", ApprovalEnum.PENDING);
}
checked = true;
}
if (date != null) {
Calendar lastCal = Calendar.getInstance();
lastCal.setTime(date);
lastCal.add(Calendar.DAY_OF_MONTH, 1);
if (checked) {
sqlCountEvent.append(" AND o.date BETWEEN :date AND :last");
} else {
sqlCountEvent.append(" WHERE o.date BETWEEN :date AND :last");
}
checked = true;
filter.addFilter("date", date);
filter.addFilter("last", lastCal.getTime());
}
if (category != 0) {
Category c = DAOFactory.create(Category.class).get(
Convert.toLong(category));
if (checked) {
sqlCountEvent.append(" AND :category MEMBER OF o.categories");
} else {
sqlCountEvent.append(" WHERE :category MEMBER OF o.categories");
}
checked = true;
filter.addFilter("category", c);
}
if (Validator.isValid(place) && (!"null".equalsIgnoreCase(place))) {
if (checked) {
sqlCountEvent.append(" AND o.place.searchName LIKE :place");
} else {
sqlCountEvent.append(" WHERE o.place.searchName LIKE :place");
}
checked = true;
filter.addFilter("place", Util.removeAccents(place).toUpperCase());
}
if (Validator.isValid(owner) && (!"null".equalsIgnoreCase(owner))) {
if (checked) {
sqlCountEvent
.append(" AND UPPER(o.organizer.name) LIKE :owner");
} else {
sqlCountEvent
.append(" WHERE UPPER(o.organizer.name) LIKE :owner");
}
checked = true;
filter.addFilter("owner", owner.toUpperCase());
}
long size = DAOFactory.create(Order.class).execute(
new QueryAdapter(sqlCountEvent.toString()), filter);
return size;
}
}
esse é o controle que exporta o CSV
oi,
o que faz o método write da classe CSVFile ?
abs
escreve o csv direto no response
outra coisa, o erro que dá é esse aqui:
[EventExportCsv] handleRequest: java.io.IOException: Client is busy or timed out
org.apache.catalina.connector.ClientAbortException: java.io.IOException: Client is busy or timed out
at org.apache.catalina.connector.OutputBuffer.doFlush(OutputBuffer.java:382)
at org.apache.catalina.connector.OutputBuffer.flush(OutputBuffer.java:351)
at org.apache.catalina.connector.CoyoteOutputStream.flush(CoyoteOutputStream.java:176)
at sun.nio.cs.StreamEncoder.implFlush(StreamEncoder.java:278)
at sun.nio.cs.StreamEncoder.flush(StreamEncoder.java:122)
at java.io.OutputStreamWriter.flush(OutputStreamWriter.java:212)
at java.io.BufferedWriter.flush(BufferedWriter.java:236)
at com.iw.library.report.csv.CSVFile.write(CSVFile.java:141)
at com.iw.library.report.csv.CSVFile.write(CSVFile.java:118)
at com.iw.il.manager.controller.enrollment.event.ListExportCsv.createCSVFile(ListExportCsv.java:232)
at com.iw.il.manager.controller.enrollment.event.ListExportCsv.handleRequest(ListExportCsv.java:44)
at com.iw.il.manager.controller.AbstractController.handleRequest(AbstractController.java:75)
at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:807)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:571)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:511)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:754)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)
at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1523)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:279)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:188)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:641)
at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:97)
at com.sun.enterprise.web.PESessionLockingStandardPipeline.invoke(PESessionLockingStandardPipeline.java:85)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:185)
at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:325)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:226)
at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:165)
at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:791)
at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:693)
at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:954)
at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:170)
at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:135)
at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:102)
at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:88)
at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:76)
at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:53)
at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:57)
at com.sun.grizzly.ContextTask.run(ContextTask.java:69)
at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:330)
at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:309)
at java.lang.Thread.run(Thread.java:662)
Caused by: java.io.IOException: Client is busy or timed out
at com.sun.grizzly.util.OutputWriter.flushChannel(OutputWriter.java:127)
at com.sun.grizzly.util.OutputWriter.flushChannel(OutputWriter.java:74)
at com.sun.grizzly.http.SocketChannelOutputBuffer.flushChannel(SocketChannelOutputBuffer.java:321)
at com.sun.grizzly.http.SocketChannelOutputBuffer.flushBuffer(SocketChannelOutputBuffer.java:392)
at com.sun.grizzly.http.SocketChannelOutputBuffer.flush(SocketChannelOutputBuffer.java:370)
at com.sun.grizzly.http.ProcessorTask.action(ProcessorTask.java:1174)
at com.sun.grizzly.tcp.Response.action(Response.java:266)
at org.apache.catalina.connector.OutputBuffer.doFlush(OutputBuffer.java:378)
… 42 more
oi,
vá no diretório conf do seu tomcat e altere o arquivo server.xml
mais detalhes dê uma lida na documentação
http://tomcat.apache.org/tomcat-5.5-doc/config/http.html
abs
po, não estou rodando no Tomcat, estou rodando no Glassfish, vou procurar algo similar pro glassfish!
valeu pela ajuda
oi,
não sei porque mas achei que você estava usando o Tomcat
estes parâmetros acho que podem ser configurados no console administrativo do glassfish, dá uma lida no link abaixo
http://download.oracle.com/docs/cd/E19879-01/821-0185/gicpj/index.html
abs