How to insert and return an instance of Employee (DAO pattern)

Question

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;
    }

Answer 1

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;
    }
}