Cursos Empresas Blog Live Conf Precios

Inserción y actualización de empleados en base de datos

Clase 12 de 22 • Curso de Java SE: SQL y Bases de Datos

Contenido del curso

JDBC

CRUD

Transacciones

Conexiones Pool

JPA y ORM

Próximos pasos

22
Creación de un Clon de Netflix con JDBC y JPA
02:30 min

Resumen

En el método save(Employee employee) implementamos la funcionalidad para insertar un nuevo registro de empleado en la base de datos.

Dentro del método, definimos una consulta SQL de inserción y la almacenamos en una variable de tipo String. Luego, utilizamos un objeto PreparedStatement para preparar la consulta y asignar los valores de los parámetros.

Una vez que todos los valores se han asignado, ejecutamos el método executeUpdate() del PreparedStatement para ejecutar la consulta y realizar la inserción en la base de datos.

Inserción y actualización de empleados en base de datos

Ensayé diferentes opciones, de update o insert, buscando que no se re repliquen los mismos employees. definitivamente la opción que a mi me hizo más sentido fue pregundar directamente en la base de datos si ya existe un employee con el nombre y aplellido, si existe entonces debo hacer un update en caso contrario hago un insert. este es el código:

public void save(Employee employee) throws SQLException {
        //SELECT first_name, email FROM project.employees WHERE first_name = 'Diego Felipe' AND pa_surname = 'Sanchez' LIMIT 0, 1000
        Boolean update = false;
        Integer readId = 0;
        String sql = "SELECT first_name, email, id FROM employees WHERE first_name = ? AND pa_surname = ?";
        try(PreparedStatement myStamt = getConnection().prepareStatement(sql)) {
            myStamt.setString(1, employee.getFirst_name());
            myStamt.setString(2, employee.getPa_surname());
            try(ResultSet myRes = myStamt.executeQuery()) {
                if(myRes.next()) {
                    sql = "UPDATE employees SET first_name = ?, pa_surname = ?, ma_surname = ?, email = ?, salary = ? WHERE id = ?";
                    System.out.println("Cargando comando UPDATE");
                    update = true;
                    readId = myRes.getInt("id");

                } else {
                    sql = "INSERT INTO employees (first_name, pa_surname, ma_surname, email, salary) VALUES (?, ?, ?, ?, ?)";
                    System.out.println("Cargando comando INSERT");
                    update = false;
                }
            }
        }
        try(PreparedStatement myStamt = getConnection().prepareStatement(sql)){
            myStamt.setString(1, employee.getFirst_name());
            myStamt.setString(2, employee.getPa_surname());
            myStamt.setString(3, employee.getMa_surname());
            myStamt.setString(4, employee.getEmail());
            myStamt.setFloat( 5, employee.getSalary());
            if(update) {
                myStamt.setInt(6, readId);
            }
            myStamt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

Esta es mi solución (estoy usando PostgreSQL en vez de MySQL). Realicé una operación tipo UPSERT (INSERT Y UPDATE combinados) y consideré el caso en el que @Override public void save(Employee employee) throws SQLException { if (employee.getId() == null || employee.getId() <= 0) { String sql = "INSERT INTO employees (first_name, pa_surname, ma_surname, email, salary) VALUES (?,?,?,?,?)"; try (PreparedStatement stmt = getConnection().prepareStatement(sql)) { stmt.setString(1, employee.getFirst_name()); stmt.setString(2, employee.getPa_surname()); stmt.setString(3, employee.getMa_surname()); stmt.setString(4, employee.getEmail()); stmt.setFloat(5, employee.getSalary()); stmt.executeUpdate(); } } else { String sql = "INSERT INTO employees (id, first_name, pa_surname, ma_surname, email, salary) " + "VALUES (?, ?, ?, ?, ?, ?) " + "ON CONFLICT (id) DO UPDATE SET " + "first_name = EXCLUDED.first_name, " + "pa_surname = EXCLUDED.pa_surname, " + "ma_surname = EXCLUDED.ma_surname, " + "email = EXCLUDED.email, " + "salary = EXCLUDED.salary"; try (PreparedStatement stmt = getConnection().prepareStatement(sql)) { if (employee.getId() == null || employee.getId() <= 0) { stmt.setNull(1, Types.INTEGER); } else { stmt.setInt(1, employee.getId()); } stmt.setString(2, employee.getFirst_name()); stmt.setString(3, employee.getPa_surname()); stmt.setString(4, employee.getMa_surname()); stmt.setString(5, employee.getEmail()); stmt.setFloat(6, employee.getSalary()); stmt.executeUpdate(); } } }id == null :

 @Override
    public void save(Employee employee) throws SQLException {
        if (employee.getId() == null || employee.getId() <= 0) {
            String sql = "INSERT INTO employees (first_name, pa_surname, ma_surname, email, salary) VALUES (?,?,?,?,?)";
            try (PreparedStatement stmt = getConnection().prepareStatement(sql)) {
                stmt.setString(1, employee.getFirst_name());
                stmt.setString(2, employee.getPa_surname());
                stmt.setString(3, employee.getMa_surname());
                stmt.setString(4, employee.getEmail());
                stmt.setFloat(5, employee.getSalary());

                stmt.executeUpdate();
            }
        } else {
            String sql = "INSERT INTO employees (id, first_name, pa_surname, ma_surname, email, salary) " +
                    "VALUES (?, ?, ?, ?, ?, ?) " +
                    "ON CONFLICT (id) DO UPDATE SET " +
                    "first_name = EXCLUDED.first_name, " +
                    "pa_surname = EXCLUDED.pa_surname, " +
                    "ma_surname = EXCLUDED.ma_surname, " +
                    "email = EXCLUDED.email, " +
                    "salary = EXCLUDED.salary";

            try (PreparedStatement stmt = getConnection().prepareStatement(sql)) {
                if (employee.getId() == null || employee.getId() <= 0) {
                    stmt.setNull(1, Types.INTEGER);
                } else {
                    stmt.setInt(1, employee.getId());
                }

                stmt.setString(2, employee.getFirst_name());
                stmt.setString(3, employee.getPa_surname());
                stmt.setString(4, employee.getMa_surname());
                stmt.setString(5, employee.getEmail());
                stmt.setFloat(6, employee.getSalary());

                stmt.executeUpdate();
            }
        }
    }

Aqui mi implementacion del metodo save(Employee employee) para hacer o una inserción o una actualización: public void save(Employee employee) throws SQLException {

String sqlInsert = "INSERT INTO employees (first_name, pa_surname, ma_surname, email, salary) " + "VALUES (?, ?, ?, ?, ?)";

String sqlUpdate = "UPDATE employees set first_name = ?, pa_surname = ?, ma_surname = ?, email = ?, salary = ?" + " WHERE id = ?";

// Actualizacion if (employee.getId() > 0){ try( PreparedStatement myPreparedStatement = getConnection().prepareStatement(sqlUpdate); ){ myPreparedStatement.setString(1, employee.getFirstName()); myPreparedStatement.setString(2, employee.getPaSurname()); myPreparedStatement.setString(3, employee.getMaSurname()); myPreparedStatement.setString(4, employee.getEmail()); myPreparedStatement.setFloat(5, employee.getSalary()); myPreparedStatement.setInt(6, employee.getId()); myPreparedStatement.executeUpdate(); } }else { try( PreparedStatement myPreparedStatement = getConnection().prepareStatement(sqlInsert); ){ myPreparedStatement.setString(1, employee.getFirstName()); myPreparedStatement.setString(2, employee.getPaSurname()); myPreparedStatement.setString(3, employee.getMaSurname()); myPreparedStatement.setString(4, employee.getEmail()); myPreparedStatement.setFloat(5, employee.getSalary()); myPreparedStatement.executeUpdate(); } } }

 public void save(Employee employee) throws SQLException {

        String sqlInsert = "INSERT INTO employees (first_name, pa_surname, ma_surname, email, salary) " +
                "VALUES (?, ?, ?, ?, ?)";

        String sqlUpdate = "UPDATE employees set first_name = ?, pa_surname = ?, ma_surname = ?, email = ?, salary = ?" +
                " WHERE id = ?";

        // Actualizacion
        if (employee.getId() > 0){
            try(
                    PreparedStatement myPreparedStatement = getConnection().prepareStatement(sqlUpdate);
            ){
                myPreparedStatement.setString(1, employee.getFirstName());
                myPreparedStatement.setString(2, employee.getPaSurname());
                myPreparedStatement.setString(3, employee.getMaSurname());
                myPreparedStatement.setString(4, employee.getEmail());
                myPreparedStatement.setFloat(5, employee.getSalary());
                myPreparedStatement.setInt(6, employee.getId());
                myPreparedStatement.executeUpdate();
            }
        }else {
            try(
                    PreparedStatement myPreparedStatement = getConnection().prepareStatement(sqlInsert);
            ){
                myPreparedStatement.setString(1, employee.getFirstName());
                myPreparedStatement.setString(2, employee.getPaSurname());
                myPreparedStatement.setString(3, employee.getMaSurname());
                myPreparedStatement.setString(4, employee.getEmail());
                myPreparedStatement.setFloat(5, employee.getSalary());
                myPreparedStatement.executeUpdate();
            }
        }
    }
```&#x20;

// IRepository.java
public interface IRepository <T> {
    List<T> findAll() throws SQLException;
    T getById(Integer id) throws SQLException;
    boolean save(T record) throws SQLException;
    boolean update(int id, T record) throws SQLException;
    Integer delete(Integer id) throws SQLException;
}
// EmployeeRepository.java
@Override
public boolean update(int id, Employee record) throws SQLException {
    String sentence = "UPDATE employees SET first_name=?, last_name=? WHERE id=?";
    try(PreparedStatement statement = getConnection().prepareStatement(sentence)) {
        statement.setString(1, record.getFirst_name());
        statement.setString(2, record.getLast_name());
        statement.setInt(3, id);
        int rows = statement.executeUpdate();
        return rows == 1;
    }
}
// Main.java
public static void main(String[] args) throws SQLException {
  try {
      IRepository<Employee> repository = new EmployeeRepository();
      int idToUpdate = 30;
  
      Employee employee = repository.getById(idToUpdate);
  
      if (employee == null) {
          System.out.println("Employee not found");
          return;
      }
  
      employee.setFirst_name("Troncha");
      employee.setLast_name("Toro");
  
      boolean done = repository.update(idToUpdate, employee);
      String msg = done ? "An employee has been updated" : "Employee could not be updated";
      System.out.println(msg);
  } catch (SQLException e) {
      System.out.println("Something went wrong");
      e.printStackTrace();
  }
}
```// IRepository.java

public interface IRepository \<T> {

&#x20;   List\<T> findAll() throws SQLException;

&#x20;   T getById(Integer id) throws SQLException;

&#x20;   boolean save(T record) throws SQLException;

&#x20;   boolean update(int id, T record) throws SQLException;

&#x20;   Integer delete(Integer id) throws SQLException;

}

// EmployeeRepository.java

@Override

public boolean update(int id, Employee record) throws SQLException {

&#x20;   String sentence = "UPDATE employees SET first\_name=?, last\_name=? WHERE id=?";

&#x20;   try(PreparedStatement statement = getConnection().prepareStatement(sentence)) {

&#x20;       statement.setString(1, record.getFirst\_name());

&#x20;       statement.setString(2, record.getLast\_name());

&#x20;       statement.setInt(3, id);

&#x20;       int rows = statement.executeUpdate();

&#x20;       return rows == 1;

&#x20;   }

}

// Main.java

public static void main(String\[] args) throws SQLException {

&#x20; try {

&#x20;     IRepository\<Employee> repository = new EmployeeRepository();

&#x20;     int idToUpdate = 30;

&#x20;&#x20;

&#x20;     Employee employee = repository.getById(idToUpdate);

&#x20;&#x20;

&#x20;     if (employee == null) {

&#x20;         System.out.println("Employee not found");

&#x20;         return;

&#x20;     }

&#x20;&#x20;

&#x20;     employee.setFirst\_name("Troncha");

&#x20;     employee.setLast\_name("Toro");

&#x20;&#x20;

&#x20;     boolean done = repository.update(idToUpdate, employee);

&#x20;     String msg = done ? "An employee has been updated" : "Employee could not be updated";

&#x20;     System.out.println(msg);

&#x20; } catch (SQLException e) {

&#x20;     System.out.println("Something went wrong");

&#x20;     e.printStackTrace();

&#x20; }

}