//MySQLAccess.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class MySQLAccess {
    private Connection connect = null;
    private Statement statement = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;
    private Statement workerStatement = null;
    private ResultSet workerResultSet = null;

    public void readDepartments(ArrayList<Department> depts) throws Exception {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connect = DriverManager.getConnection("jdbc:mysql://localhost/workers?user=diana&password=start2016");
            statement = connect.createStatement();
            workerStatement = connect.createStatement();
            resultSet = statement.executeQuery("select * from departments");
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                //System.out.println("id: "+id+" name: "+name);
                Department d=new Department(id, name);
                workerResultSet = workerStatement.executeQuery("select * from employeers where employeers.id_dep = "+id);
                while (workerResultSet.next())
                {
                    int id1 = workerResultSet.getInt("id");
                    String first_name = workerResultSet.getString("first_name");
                    String last_name = workerResultSet.getString("last_name");
                    int age = workerResultSet.getInt("age");
                    int lofs = workerResultSet.getInt("length_of_service");
                    int sal = workerResultSet.getInt("salary");
                    Employee emp = new Employee(id1, first_name, last_name, age, lofs, sal);
                    d.AddWorker(emp);
                }
                depts.add(d);
            }
            //writeResultSetDep (resultSet);
            //resultSet = statement.executeQuery("select * from emplo...");
            //writeResultSetEmp (resultSet);
        }
        catch (Exception e) {
            throw e;
          } finally {
            close();

        }
    }

    private void close() {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connect != null) {
                connect.close();
            }
        } catch (Exception e) { }

    }

    public void writeEmployeers(Employee emp, int dep_id) throws Exception {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connect = DriverManager.getConnection("jdbc:mysql://localhost/workers?user=diana&password=start2016");
            preparedStatement = connect.prepareStatement("insert into employeers values (?, ?, ?, ?, ?, ?, ?)");
            preparedStatement.setInt(1,emp.id);
            preparedStatement.setString(2, emp.GetFirst_name());
            preparedStatement.setString(3, emp.GetLast_name());
            preparedStatement.setInt(4,emp.GetAge());
            preparedStatement.setInt(5,emp.GetLS());
            preparedStatement.setInt(6,emp.GetSal());
            preparedStatement.setInt(7,dep_id);
            preparedStatement.executeUpdate();
        }
        catch (Exception e) {
            throw e;
          } finally {
            close();

        }
    }
}