DBconnect

/**
 * Created by elsemTim on 14.11.2016.
 */

import java.security.acl.Group;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;



public class DBConnect {

    private static DBConnect _ourInstance;
    private static Connection _ourCon;


    public static DBConnect getInstance() throws Exception {
        if (_ourInstance ==null)
        {
            _ourInstance = new DBConnect();
        }
        return _ourInstance;
    }

    private DBConnect() throws Exception {
        try
        {
            Class.forName("org.firebirdsql.jdbc.FBDriver");
            String strDatabasePath ="d:\\DBWork\\OURDB.FDB";
            String strUrl="jdbc:firebirdsql://localhost:3050/"+strDatabasePath;
            _ourCon=DriverManager.getConnection(strUrl,"SYSDBA","123");
        }
        catch (Exception e)
        {
            throw new Exception(e);
        }
    }

    //Employee
    public List<Employee> SelectEmp() throws SQLException{
        List<Employee> employ = new ArrayList<Employee>();
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = _ourCon.createStatement();
            //тут нужен запрос, который свяжент все таблицы и кинет их сюда
            rs = stmt.executeQuery( "SELECT * FROM employee;");
            while (rs.next()) {
                Employee emp = new Employee(rs);
                emp.SetDepID(rs.getInt(0));
                emp.SetPosID(rs.getInt(1));
                emp.SetFirstName(rs.getString(2));
                emp.SetLastName(rs.getString(3));
                employ.add(emp);
            }
        }
        finally {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
        }
        return employ;
    }

    public void UpdateEmp (Employee emp) throws SQLException
    {
        PreparedStatement stmt = null;
        try{
            stmt = _ourCon.prepareStatement(
                    "UPDATE employee SET "
                            +"DEP_ID=?, POS_ID=?  "
                            +"WHERE DEP_ID=? AND POS_ID=?;");
            stmt.setInt(1, emp.GetDepID());
            stmt.setInt(2, emp.GetPosID());
            stmt.setInt(3, emp.GetDepID());
            stmt.setInt(4, emp.GetPosID());
            stmt.executeUpdate();
        }
        finally {
            if (stmt!=null){
                stmt.close();
            }
        }
    }

    public void RemoveEmp (Employee emp) throws  SQLException
    {
        PreparedStatement stmt = null;
        try{
            stmt = _ourCon.prepareStatement(
                    "DELETE FROM employee WHERE "
                            +"DEP_ID=? AND POS_ID=?;");
            stmt.setInt(1, emp.GetDepID());
            stmt.setInt(2, emp.GetPosID());
            stmt.executeUpdate();
        }
        finally {
            if (stmt!=null){
                stmt.close();
            }
        }
    }

    public void InsertEmp (Employee emp) throws SQLException
    {
        PreparedStatement stmt = null;
        try{
            stmt = _ourCon.prepareStatement(
                    "INSERT INTO employee "
                            +"(DEP_ID, POS_ID, FIRST_NAME, LAST_NAME) "
                            +"VALUES (?, ?, ?, ?);");
            stmt.setInt(1, emp.GetDepID());
            stmt.setInt(2, emp.GetPosID());
            stmt.setString(3, emp.GetFirstName());
            stmt.setString(4, emp.GetLastName());
            stmt.executeUpdate();
        }
        finally {
            if (stmt!=null){
                stmt.close();
            }
        }
    }

    //Position OK
    public List<Position> SelectPos() throws SQLException{
        List<Position> posit = new ArrayList<Position>();
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = _ourCon.createStatement();
            //тут нужен запрос, который свяжент все таблицы и кинет их сюда
            rs = stmt.executeQuery( "SELECT * FROM positionn;");
            while (rs.next()) {
                Position pos = new Position(rs);
                pos.SetID(rs.getInt(0));
                pos.SetNamePosition(rs.getString(1));
                pos.SetSalary(rs.getString(2));
                posit.add(pos);
            }
        }
        finally {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
        }
        return posit;
    }

    public void UpdatePos(Position pos) throws SQLException
    {
        PreparedStatement stmt = null;
        try{
            stmt = _ourCon.prepareStatement(
                    "UPDATE positionn SET "
                    +"SALARY=?, NAME=?  "
                    +"WHERE ID=?;");
            stmt.setInt(1, pos.GetID());
            stmt.setString(2,pos.GetNamePosition());
            stmt.setString(3,pos.GetSalary());
            stmt.executeUpdate();
        }
        finally {
            if (stmt!=null){
                stmt.close();
            }
        }

    }

    public void RemovePos(Position pos) throws SQLException
    {
        PreparedStatement stmt = null;
        try{
            stmt = _ourCon.prepareStatement(
                    "DELETE FROM positionn WHERE "
                    +"ID=?;");
            stmt.setInt(1,pos.GetID());
            stmt.executeUpdate();
        }
        finally {
            if (stmt!=null){
                stmt.close();
            }
        }
    }

    //
    public void InsertPos(Position pos) throws SQLException
    {
        PreparedStatement stmt = null;
        try{
            stmt = _ourCon.prepareStatement(
                    "INSERT INTO positionn "
                    +"(ID, SALARY, NAME) "
                    +"VALUES (NULL, ?, ?);");
            stmt.setString(1,pos.GetSalary());
            stmt.setString(2,pos.GetNamePosition());
            stmt.executeUpdate();
        }
        finally {
            if (stmt!=null){
                stmt.close();
            }
        }
    }


    //Department OK
    public List<Department> SelectDep() throws SQLException{
        List<Department> depart = new ArrayList<Department>();
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = _ourCon.createStatement();
            //тут нужен запрос, который свяжент все таблицы и кинет их сюда
            rs = stmt.executeQuery( "SELECT * FROM department;");
            while (rs.next()) {
                Department dep = new Department(rs);
                dep.SetID(rs.getInt(0));
                dep.SetNameDepartment(rs.getString(1));
                dep.SetMail(rs.getString(2));
                dep.SetPhone(rs.getString(3));
                depart.add(dep);
            }
        }
        finally {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
        }
        return depart;
    }

    public void UpdateDep(Department dep) throws SQLException
    {
        PreparedStatement stmt = null;
        try{
            stmt = _ourCon.prepareStatement(
                    "UPDATE department SET "
                            +"DEPARTMENT=?, MAIL=?, PHONE=? "
                            +"WHERE ID=?;");
            stmt.setString(1,dep.GetNameDepartment());
            stmt.setString(2,dep.GetMail());
            stmt.setString(3,dep.GetPhone());
            stmt.setInt(4,dep.GetID());
            stmt.executeUpdate();
        }
        finally {
            if (stmt!=null){
                stmt.close();
            }
        }
    }

    public void RemoveDep(Department dep) throws SQLException
    {
        PreparedStatement stmt = null;
        try{
            stmt = _ourCon.prepareStatement(
                    "DELETE FROM department WHERE "
                            +"ID=?;");
            stmt.setInt(1,dep.GetID());
            stmt.executeUpdate();
        }
        finally {
            if (stmt!=null){
                stmt.close();
            }
        }
    }

    public void InsertDep(Department dep) throws SQLException
    {
        PreparedStatement stmt = null;
        try{
            stmt = _ourCon.prepareStatement(
                    "INSERT INTO department "
                            +"(ID, DEPARTMENT, MAIL, PHONE) "
                            +"VALUES (NULL, ?, ?, ?);");
            stmt.setString(0,dep.GetNameDepartment());
            stmt.setString(1,dep.GetMail());
            stmt.setString(2,dep.GetPhone());
            stmt.executeUpdate();
        }
        finally {
            if (stmt!=null){
                stmt.close();
            }
        }
    }
}