import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.HashSet;

public class EmployeeDAO_JDBCImpl implements EmployeeDAO {

    private Connection con = null;

    public EmployeeDAO_JDBCImpl() {

        String url = "jdbc:derby://localhost:1527/EmployeeDB";
        String user = "test";
        String pass = "tiger";

        try {
            // 建立 連線 物件
            con = DriverManager.getConnection(url, user, pass); // 連線失敗，con 為 null

        } catch (SQLException ex) { // 捕捉資料庫例外
            System.out.println(ex);
        }

    }

    @Override
    public void add(Employee emp) {

    }

    @Override
    public void update(Employee emp) {
    }

    @Override
    public void delete(int id) {
    }

    @Override
    public Employee findById(int id) {
        return null;
    }

    @Override
    public Employee[] getAllEmployees() {

        Collection<Employee> collection = new HashSet<>();

        String query = "select * from employee"; // 查詢所有員工

        // auto close
        try(Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(query);) {

            while (rs.next()) { // 取得下一筆資料，若有獲取到資料回傳 true
                // 讀取目前這筆資料的各項欄位
                int id = rs.getInt("id");
                String firstName = rs.getString("firstname");
                String lastName = rs.getString("lastname");
                java.util.Date birthdate = rs.getDate("birthdate");
                float salary = rs.getFloat("salary");

                Employee emp = new Employee(id, firstName, lastName, birthdate, salary);
                collection.add(emp);

            } // while

            return collection.toArray(new Employee[0]);

        } catch (SQLException ex) { // 捕捉資料庫例外
            System.out.println(ex);
        }
        return null;
    }

    @Override
    public void close() throws Exception {

        try { if (con != null) con.close(); } catch (SQLException ex) { System.out.println(ex); }
    }

}