AtendimentoVirtual

import java.io.IOException;
import java.sql.Connection;
import java.util.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/AtendimentoVirtual.do")
public class AtendimentoVirtual extends HttpServlet {
	private static final long serialVersionUID = 1L;

	private static final String consulta_debitos = "select faturas.* from faturas"
			+ "inner join instalacoes on faturas.matricula_instalacao = instalacoes.matricula "
			+ "inner join clientes on instalacoes.cpf_cliente = clientes.cpf  " + " where cpf = ?"
			+ "and faturas.quitada = 0;";

	private static final String segunda_via = "select faturas.* from faturas"
			+ "inner join instalacoes on faturas.matricula_instalacao = instalacoes.matricula "
			+ "inner join clientes on instalacoes.cpf_cliente = clientes.cpf " + " where cpf = ?"
			+ "and faturas.data_leitura = ?; ";

	public AtendimentoVirtual() {
		super();
	}

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		String acao = request.getParameter("tipoAcao");
		if (acao.equalsIgnoreCase("2via")) {
			response.sendRedirect("http://localhost:8080/EstudoPI/segundaVia.html");
		} else if (acao.equalsIgnoreCase("debitos")) {
			response.sendRedirect("http://localhost:8080/EstudoPI/debitos.html");
		} else {
			response.sendRedirect("file:///C:/Users/acer/Documents/COSERN/WebContent/erro.html");
		}

	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		String cpfDoCliente = request.getParameter("cpf");
		String dataFatura = request.getParameter("data_da_fatura");

		try {
			Class.forName("com.mysql.jdbc.Driver");

			Connection conexao = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/EstudoPI?useLegacyDatetimeCode=false&serverTimezone=America/Fortaleza",
					"aluno", "xxxxx");

			if (conexao != null) {
				PreparedStatement statement = null;

				if (dataFatura == null) {
					statement = conexao.prepareStatement(consulta_debitos);
					statement.setString(1, cpfDoCliente);
				}

				else {
					statement = conexao.prepareStatement(segunda_via);
					statement.setString(1, cpfDoCliente);
					SimpleDateFormat formato = new SimpleDateFormat("yyyy-MM-dd");
					Date data;

					try {
						data = formato.parse(dataFatura);
						java.sql.Date data_sql = new java.sql.Date(data.getTime());
						statement.setDate(2, data_sql);
					} catch (ParseException e) {
						e.printStackTrace();
					}

				}

				ResultSet resultSet = statement.executeQuery();

				response.getWriter().append("<html>");
				response.getWriter().append("<body>");
				response.getWriter().append("<table>");

				response.getWriter().append("<tr>");

				response.getWriter().append("<th>Matrícula:</th>");
				response.getWriter().append("<th>Data:</th>");
				response.getWriter().append("<th>Consumo:</th>");
				response.getWriter().append("<th>Valor:</th>");

				response.getWriter().append("</tr>");

				while (resultSet.next()) {
					response.getWriter().append("<tr>");
					response.getWriter().append("<td>");
					response.getWriter().append(String.valueOf(resultSet.getInt("matricula_instalacao")));
					response.getWriter().append("</td>");
					response.getWriter().append("<td>");
					response.getWriter().append(String.valueOf(resultSet.getDate("data_leitura")));
					response.getWriter().append("</td>");
					response.getWriter().append("<td>");
					response.getWriter().append(String.valueOf(resultSet.getInt("consumo")));
					response.getWriter().append("</td>");
					response.getWriter().append("<td>");
					response.getWriter().append(String.valueOf(resultSet.getFloat("valor")));
					response.getWriter().append("</td>");
					response.getWriter().append("</tr>");
				}
				response.getWriter().append("</table>");
				response.getWriter().append("</body>");
				response.getWriter().append("</html>");
			} else {
				response.getWriter().append("bosta");
			}
			conexao.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

	}
}