JDBC1

/**
 * 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 void UpdateEmp () 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,...);
            stmt.setInt(2,...);
            stmt.setInt(3,...);
            stmt.setInt(4,...);
            stmt.executeUpdate();
        }
        finally {
            if (stmt!=null){
                stmt.close();
            }
        }
    }

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

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

    //Position
    //Поправить вх. данные, реализовать
    //методы для получения значений
    public void UpdatePos() throws SQLException
    {
        PreparedStatement stmt = null;
        try{
            stmt = _ourCon.prepareStatement(
                    "UPDATE positionn SET "
                    +"SALARY=?, NAME=?  "
                    +"WHERE ID=?;");
            stmt.setInt(1,...);
            stmt.setString(2,...);
            stmt.setInt(3,..);
            stmt.executeUpdate();
        }
        finally {
            if (stmt!=null){
                stmt.close();
            }
        }

    }

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

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


    //Department
    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 employee");
            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() throws SQLException
    {
        Department dep = new Department();

        PreparedStatement stmt = null;
        try{
            stmt = _ourCon.prepareStatement(
                    "UPDATE department SET "
                            +"DEPARTMENT=?, MAIL=?, PHONE=? "
                            +"WHERE ID=?;");
            stmt.setString(1,dep);
            stmt.setString(2,...);
            stmt.setString(3,...);
            stmt.setInt(4,...);
            stmt.executeUpdate();
        }
        finally {
            if (stmt!=null){
                stmt.close();
            }
        }
    }

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

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