Boa noite. Pessoal,
Estou recebendo o seguinte erro ao usar o método buscaPorId(id):
GRAVE: Servlet.service() for servlet [spring mvc] in context with path [/contas] threw exception [Request processing failed; nested exception is java.lang.RuntimeException: java.sql.SQLException: Connection is closed.] with root cause
java.sql.SQLException: Connection is closed.
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.checkOpen(PoolingDataSource.java:185)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:312)
at br.com.caelum.contas.dao.ContaDAO.buscaPorId(ContaDAO.java:121)
at br.com.caelum.contas.controller.ContaController.mostra(ContaController.java:70)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:749)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:689)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:938)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:870)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:852)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:521)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1096)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:674)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1500)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1456)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)
Não sei o porque. Segue as classes ContaDAO e ContasController respectivamente:
ContaDAO
package br.com.caelum.contas.dao;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import br.com.caelum.contas.modelo.Conta;
import br.com.caelum.contas.modelo.TipoDaConta;
@Repository
public class ContaDAO {
private Connection connection;
@Autowired
public ContaDAO(DataSource ds) {
try {
this.connection = ds.getConnection();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
public void adiciona(Conta conta) {
String sql = "insert into contas (descricao, paga, valor, tipo) values (?,?,?,?)";
PreparedStatement stmt;
try {
stmt = connection.prepareStatement(sql);
stmt.setString(1, conta.getDescricao());
stmt.setBoolean(2, conta.isPaga());
stmt.setDouble(3, conta.getValor());
stmt.setString(4, conta.getTipo().name());
stmt.execute();
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public void remove(Conta conta) {
if (conta.getId() == null) {
throw new IllegalStateException("Id da conta naoo deve ser nula.");
}
String sql = "delete from contas where id = ?";
PreparedStatement stmt;
try {
stmt = connection.prepareStatement(sql);
stmt.setLong(1, conta.getId());
stmt.execute();
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public void altera(Conta conta) {
String sql = "update contas set descricao = ?, paga = ?, dataPagamento = ?, tipo = ?, valor = ? where id = ?";
PreparedStatement stmt;
try {
stmt = connection.prepareStatement(sql);
stmt.setString(1, conta.getDescricao());
stmt.setBoolean(2, conta.isPaga());
stmt.setDate(3, conta.getDataPagamento() != null ? new Date(conta
.getDataPagamento().getTimeInMillis()) : null);
stmt.setString(4, conta.getTipo().name());
stmt.setDouble(5, conta.getValor());
stmt.setLong(6, conta.getId());
stmt.execute();
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public List<Conta> lista() {
try {
List<Conta> contas = new ArrayList<Conta>();
PreparedStatement stmt = this.connection
.prepareStatement("select * from contas");
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
// adiciona a conta na lista
contas.add(populaConta(rs));
}
rs.close();
stmt.close();
connection.close();
return contas;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public Conta buscaPorId(Long id) {
if (id == null) {
throw new IllegalStateException("Id da conta nao deve ser nula.");
}
try {
PreparedStatement stmt = this.connection.prepareStatement("select * from contas where id = ?");
stmt.setLong(1, id);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
connection.close();
return populaConta(rs);
}
rs.close();
stmt.close();
connection.close();
return null;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public void paga(Long id) {
if (id == null) {
throw new IllegalStateException("Id da conta nao deve ser nula.");
}
String sql = "update contas set paga = ?, dataPagamento = ? where id = ?";
PreparedStatement stmt;
try {
stmt = connection.prepareStatement(sql);
stmt.setBoolean(1, true);
stmt.setDate(2, new Date(Calendar.getInstance().getTimeInMillis()));
stmt.setLong(3, id);
stmt.execute();
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
private Conta populaConta(ResultSet rs) throws SQLException {
Conta conta = new Conta();
conta.setId(rs.getLong("id"));
conta.setDescricao(rs.getString("descricao"));
conta.setPaga(rs.getBoolean("paga"));
conta.setValor(rs.getDouble("valor"));
Date data = rs.getDate("dataPagamento");
if (data != null) {
Calendar dataPagamento = Calendar.getInstance();
dataPagamento.setTime(data);
conta.setDataPagamento(dataPagamento);
}
conta.setTipo(Enum.valueOf(TipoDaConta.class, rs.getString("tipo")));
return conta;
}
}
ContaController:
package br.com.caelum.contas.controller;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.RequestMapping;
import br.com.caelum.contas.dao.ContaDAO;
import br.com.caelum.contas.modelo.Conta;
@Controller
public class ContaController {
private ContaDAO dao;
@Autowired
ContaController(ContaDAO dao){
this.dao = dao;
}
@RequestMapping("/form")
public String formulario() {
return "contas/formulario";
}
@RequestMapping("/adicionaConta")
public String adicionaConta(@Valid Conta conta, BindingResult result) {
if(result.hasErrors()){
return "contas/formulario";
}
System.out.println("A conta adicionada é: " + conta.getDescricao());
dao.adiciona(conta);
return "contas/conta-adicionada";
}
@RequestMapping("lista")
public String lista(Model mv){
List<Conta> lista = dao.lista();
//ModelAndView mv = new ModelAndView("contas/lista");
//mv.addObject("listaContas", lista);
mv.addAttribute("listaContas", lista);
return "contas/lista";
}
@RequestMapping("removeConta")
public String removeConta(Conta conta){
dao.remove(conta);
return "redirect:lista";
}
@RequestMapping("pagaConta")
public void pagaConta(Conta conta, HttpServletResponse response){
dao.paga(conta.getId());
response.setStatus(200);
}
@RequestMapping("/mostraConta")
public String mostra(Long id, Model model){
model.addAttribute("conta", dao.buscaPorId(id));
return "contas/mostra";
}
@RequestMapping("/alteraConta")
public String altera(Conta conta){
dao.altera(conta);
return "redirect:lista";
}
}