Ativ 3

DUMP SQL

CREATE TABLE `clientes` (  `codigo` int(11) NOT NULL AUTO_INCREMENT,  `nome` varchar(255) DEFAULT NULL,  `telefone` varchar(11) DEFAULT '',  `email` varchar(50) DEFAULT NULL,  PRIMARY KEY (`codigo`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `clientes` (`codigo`, `nome`, `telefone`, `email`)VALUES  (123124,'GUilherme','1999234','aowdiaj@awd.com'), (123125,'Teste','123213','dadawda@aw.com');

---
clientes.DataSource.java:

package clientes;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DataSource {
  private static Connection con = null;
  private static final String DRIVER = "com.mysql.jdbc.Driver";
  private static final String URL = "jdbc:mysql://127.0.0.1:3306/sistema";
  private static final String USER = "root";
  private static final String PASS = "123456";

    public static Connection getConnection() throws InstantiationException, IllegalAccessException, ClassNotFoundException {
        if (con == null){
          Class.forName(DRIVER).newInstance();
          try {
        con = DriverManager.getConnection(URL, USER, PASS);
      } catch (SQLException e) {
        e.printStackTrace();
      }
        }

        return con;

    }

}

-------

clientes.Clientes.java

package clientes;

import java.io.IOException;
import java.sql.SQLException;

import javax.servlet.RequestDispatcher;
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("/")
public class Clientes extends HttpServlet {
  private static final long serialVersionUID = 1L;


    public Clientes() {
    }

  public static final String INSERIR = "/inserir.jsp";
  public static final String LISTAGEM = "/listagem.jsp";

  protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    String forward = "";
    String action = request.getParameter("action");

      if(action != null && action.equalsIgnoreCase("inserir")) {
      forward = INSERIR;
    } else {
      try {
        forward = LISTAGEM;
        request.setAttribute("clientes", ClienteDao.todoClientes());
      } catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) {
        e.printStackTrace();
      }
    }

      RequestDispatcher view = request.getRequestDispatcher(forward);
    view.forward(request, response);
  }


  protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    Cliente cliente = new Cliente();
    cliente.setNome(request.getParameter("nome"));
    cliente.setEmail(request.getParameter("email"));
    cliente.setTelefone(request.getParameter("telefone"));

      try {
        ClienteDao.inserirCliente(cliente);
      } catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) {
        e.printStackTrace();
      }

    RequestDispatcher view = request.getRequestDispatcher(LISTAGEM);

    try {
      request.setAttribute("clientes", ClienteDao.todoClientes());
    } catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
    view.forward(request, response);
  }

}


----

clientes.Cliente.java

package clientes;

public class Cliente {
  private String nome;
  private String email;
  private int codigo;
  private String telefone;

  public void setCodigo(int codigo) {
    this.codigo = codigo;
  }

  public void setNome(String nome) {
    this.nome = nome;
  }

  public void setEmail(String email) {
    this.email = email;
  }

  public void setTelefone(String telefone) {
    this.telefone = telefone;
  }

  public String getNome() {
    return nome;
  }

  public String getEmail() {
    return email;
  }

  public int getCodigo() {
    return codigo;
  }

  public String getTelefone() {
    return telefone;
  }
}

---
clientes.ClienteDao.java

package clientes;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.PreparedStatement;

public class ClienteDao {

  public ClienteDao() {
  }

  public static List<Cliente> todoClientes() throws InstantiationException, IllegalAccessException, ClassNotFoundException {
        List<Cliente> clientes = new ArrayList<Cliente>();
        try {

          PreparedStatement stm = (PreparedStatement) DataSource.getConnection().prepareStatement("select * from clientes");

          ResultSet rs;

          rs = stm.executeQuery();

          while(rs.next()) {
            Cliente cliente = new Cliente();
            cliente.setCodigo(rs.getInt("codigo"));
            cliente.setNome(rs.getString("nome"));
            cliente.setEmail(rs.getString("email"));
            cliente.setTelefone(rs.getString("telefone"));
            clientes.add(cliente);
          }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return clientes;
    }

  public static void inserirCliente(Cliente cliente) throws InstantiationException, IllegalAccessException, ClassNotFoundException {
    try {
      String query = "insert into clientes (codigo, nome, email, telefone) values (?,?,?,?)";
      PreparedStatement preparedStatement = (PreparedStatement) DataSource.getConnection().prepareStatement( query );
      preparedStatement.setInt( 1, cliente.getCodigo() );
      preparedStatement.setString( 2, cliente.getNome() );
      preparedStatement.setString( 3, cliente.getEmail() );
      preparedStatement.setString( 4, cliente.getTelefone() );
      preparedStatement.executeUpdate();
      preparedStatement.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

}


---

inserir.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" import="java.util.List"
    import="clientes.Cliente"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
 <h1>Inserir de cliente</h1>

 <form action="Clientes" method="post">
  <fieldset>
    <div>
    <label for="nome">Nome</label>
    <input type="text" name="nome" />
    </div>
    <div>
    <label for="telefone">Telefone</label>
    <input type="text" name="telefone" />
    </div>
    <div>
    <label for="email">Email</label>
    <input type="text" name="email" />
    </div>
  </fieldset>
  <button type="submit">Cadastrar</button>
 </form>


</body>
</html>

----
listagem.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
import="clientes.ClienteDao" import="java.util.List"
    import="clientes.Cliente"
    pageEncoding="UTF-8"%>
    <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
 <h1>Lista de clientes</h1>
 <a href="/Clientes?action=inserir">Add Student</a>
 <table>
 <thead>
 <th>Codigo</th>
 <th>Nome</th>
 <th>Telefone</th>
 <th>Email</th>
 </thead>
  <tbody>
  <c:forEach items="${clientes}" var="cliente">
        <tr>
          <td><c:out value="${cliente.codigo}" /></td>
          <td><c:out value="${cliente.nome}" /></td>
          <td><c:out value="${cliente.telefone}" /></td>
          <td><c:out value="${cliente.email}" /></td>
        </tr>
</c:forEach>
        </tbody>
        </table>
</body>
</html>