//Здесь создаются каналы подключения к БД
package ConnectionPooling;

import javax.sql.DataSource;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.impl.GenericObjectPool;

import java.sql.Connection;

public class ConnectionPool {
    // JDBC Driver Name & Database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String JDBC_DB_URL = "jdbc:mysql://localhost:3306/office_datas?" +
            "useUnicode=true&characterEncoding=utf-8";
    // JDBC Database Credentials
    static final String JDBC_USER = "root";
    static final String JDBC_PASS = "";
    private static GenericObjectPool gPool = null;
    public static Connection sCon = null; //переменная с которой возможно проблема

    @SuppressWarnings("unused")
    public DataSource setUpPool() throws Exception {
        Class.forName(JDBC_DRIVER);
        // Creates an Instance of GenericObjectPool That Holds Our Pool of Connections Object!
        gPool = new GenericObjectPool();
        gPool.setMaxActive(2);
        // Creates a ConnectionFactory Object Which Will Be Use by the Pool to Create the Connection Object!
        ConnectionFactory cf = new DriverManagerConnectionFactory(JDBC_DB_URL, JDBC_USER, JDBC_PASS);
        // Creates a PoolableConnectionFactory
        // That Will Wraps the Connection Object Created by the ConnectionFactory to Add Object Pooling Functionality!
        PoolableConnectionFactory pcf = new PoolableConnectionFactory(cf, gPool,
                null, null, false, true);

        return new PoolingDataSource(gPool);
    }

    public GenericObjectPool getConnectionPool() {
        return gPool;
    }

    public void closePool() throws Exception {
        gPool.close();
    }

        // This Method Is Used To Print The Connection Pool Status
    public void printDbStatus() {
        System.out.println("Max.: " + getConnectionPool().getMaxActive() + "; Active: "
                + getConnectionPool().getNumActive() + "; Idle: " + getConnectionPool().getNumIdle());
    }

    public void createConnection() throws Exception {
        DataSource dataSource = this.setUpPool();
        this.printDbStatus();
        sCon = dataSource.getConnection();
        this.printDbStatus();
    }
}

//этот метод уже в другом классе, он обновляет БД
 public void update(Staff staff) {
        try {
            PreparedStatement preparedStatement =
                    ConnectionPool.sCon.prepareStatement("UPDATE staff "+
                            "SET " +
                            "surname = ?, " +
                            "name = ?, " +
                            "fatherName = ?, " +
                            "typeWork = ?, " +
                            "position = ?, " +
                            "addInfo = ? " +
                            "WHERE idStaff = ?");
            preparedStatement.setString(1, staff.getmSurname());
            preparedStatement.setString(2, staff.getmName());
            preparedStatement.setString(3, staff.getmFathName());
            preparedStatement.setInt(4, staff.getmTypeWork());
            preparedStatement.setString(5, staff.getmPosition());
            preparedStatement.setString(6, staff.getmAddInfo());
            preparedStatement.setInt(7, staff.getmIdstaff());


            preparedStatement.executeUpdate();
            preparedStatement.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

//этот метод создает окно для редактирования данных
private void createGui() {
        if (primaryStage == null) {
            primaryStage = new Stage();
            primaryStage.setTitle("Персональная информация");
            primaryStage.setScene(new Scene(fxmlPersonalInfo));
            primaryStage.initModality(Modality.WINDOW_MODAL);
            primaryStage.initOwner(mainStage);
            primaryStage.setMaxWidth(1020);
            primaryStage.setMaxHeight(660);
            primaryStage.setMinHeight(primaryStage.getHeight());
            primaryStage.setMinWidth(primaryStage.getWidth());
        }
        primaryStage.show();
    }
//Этот метод обновляет объект, и сюда же я пытаюсь перенести обновление данных в БД, но появляется ошибка
private void UpdateStaff(){
        Integer typeWork = 0;
        if (checkPed.isSelected()){
            typeWork = 1;
        }
        if (checkTech.isSelected()){
            typeWork =2;
        }
        staff.setmName(textName.getText());
        staff.setmSurname(textSurname.getText());
        staff.setmFathName(textFathNam.getText());
        staff.setmPosition(textPosition.getText());
        staff.setmAddInfo(textAddInfo.getText());
        staff.setmIdDoc(textNumPassp.getText());
        staff.setmDocPrivetNum(textPrivateNum.getText());
        staff.setmTel1(textTel1.getText());
        staff.setmTel2(textTel2.getText());
        staff.setmDateOfBirth(textDateOfBirth.getValue().toString());
        staff.setmTypeWork(typeWork);
        staff.setmAddress(textAddress.getText());
        staff.setmAnyAddress(textAnyAddress.getText());
//вот этот метод, если делать так - ошибка с потоками
        mCollectionListStaff.update(staff);


//пробовал это делать через потоки, если  делать так - nullPointexeption
        /*Thread t = new Thread(() -> {
            try {
			mCollectionListStaff.update(staff);

            } catch (Exception e) {
                e.printStackTrace();
            }

        });
        t.start();*/
    }