In method save()
I receive as input an instance of Employee, and I want to add it to the table employee
and return this added instance. I read about this problem but I didn't find an answer to my problem.
public Employee save(Employee employee) throws SQLException {
Connection connection = ConnectionSource.instance().createConnection();
String sql = "insert into employee VALUES(" employee.getId() ", " "'employee.getFullName().getFirstName()'" ", " "'employee.getFullName().getLastName()'" ", " "'employee.getFullName().getMiddleName()'" ", " "'employee.getPosition()'" ", " "'employee.getHired()'" ", " employee.getSalary() ", " employee.getManagerId() ", " employee.getDepartmentId() ")";
connection.prepareStatement(sql);
PreparedStatement ps2 = connection.prepareStatement("select * from employee");
ResultSet resultSet = ps2.executeQuery();
resultSet.next();
Employee emp = new Employee(... );
return emp;
}
CodePudding user response:
First of all, better not use such approach:
String sql = "insert into employee VALUES(" employee.getId() ", " "'employee.getFullName().getFirstName()'" ", " "'employee.getFullName().getLastName()'" ", " "'employee.getFullName().getMiddleName()'" ", " "'employee.getPosition()'" ", " "'employee.getHired()'" ", " employee.getSalary() ", " employee.getManagerId() ", " employee.getDepartmentId() ")";
you can have an sql injection in that case.
Instead use
String sql = "insert into employee values (?, ?, ...)";
PreparedStatement statement = conn.prepareStatement(sql);
statement.setInt(1, employee.getId());
statement.setString(2, employee.getFullName().getFirstName());
...
For your problem you can try something like this:
public Employee save(Employee employee) throws SQLException {
try (Connection connection = ConnectionSource.instance().createConnection();;
PreparedStatement statement = connection.prepareStatement(SQL_INSERT,Statement.RETURN_GENERATED_KEYS);) {
statement.setInt(1, employee.getId());
statement.setString(2, employee.getFullName().getFirstName());
// ...
int affectedRows = statement.executeUpdate();
if (affectedRows == 0) {
throw new SQLException("Creating employee failed, no rows affected.");
}
try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
if (generatedKeys.next()) {
employe.setId(generatedKeys.getLong(1));
}
else {
throw new SQLException("Creating employe failed, no ID obtained.");
}
}
return employee;
}
}