[FIXED] to use thread in a list with database data

Issue

I am trying to use a thread in a query that I make in the database that can be generated many records I do not know if it is necessary however it is not returning the values ​​in that list when I try to implement the thread (without the thread it returns the values ​​normally )

my controller: Here I call my method that will have this list of my DAO class database:

public ObservableList<Requisicao> atualizarTabela() {
    RequisicaoDAO dao = new RequisicaoDAO();
    requisicoes = FXCollections.observableArrayList(dao.getList());
    return requisicoes;
}

my method DAO:

public class RequisicaoDAO {

    private Connection con;
    Alerts alerts = new Alerts();
    public RequisicaoDAO() {
        this.con = new ConnectionFactory().getConnection();
    }

    private static RequisicaoDAO aRequisicaoDAO;

    public static RequisicaoDAO getInstancia() {

        if (aRequisicaoDAO == null) {
            aRequisicaoDAO = new RequisicaoDAO();
        }

        return aRequisicaoDAO;
    }
   public List<Requisicao> getList() {
        List<Requisicao> requisicoes = new ArrayList<>();
        new Thread() {
        @Override
        public void run() {
        String sql = "SELECT * FROM equipamento_requisicao equipreq INNER JOIN equipamento_user equipuser ON (equipreq.idequipamento_user = equipuser.id_equipamento_do_usuario)  INNER JOIN usuario user ON (user.id_usuario=equipuser.idusuario) INNER JOIN equipamentos equip ON (equip.id_equipamentos = equipuser.idequipamentos) INNER JOIN detalhe_status dStatus ON (dStatus.idequipamento_requisicao= equipreq.id_equipamento_requisicao) INNER JOIN status_requisicao statusreq on (statusreq.id_status= dStatus.idstatus) INNER JOIN permissao p ON(user.idpermissao= p.id_permissao) INNER JOIN departamentos dp ON(user.iddepartamento = dp.id_departamentos) INNER JOIN chefe_departamento cp ON(dp.id_chefe = cp.id_chefe) where statusreq.categoria='Ativa' ";
        try {
            PreparedStatement stmt = con.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {       
                Usuario usuario = new Usuario();
                usuario.setNome(rs.getString("user.nome"));
                usuario.setId(rs.getLong("user.id_usuario"));
                usuario.setMatricula(rs.getString("user.matricula"));
                // FIM TABELA USUARIO               
                //equipamento // equipamento user tabelas
                Equipamentos equipamento = new Equipamentos();
                equipamento.setEquipamento_nome(rs.getString("equip.equipamento_nome"));
                equipamento.setSerial_equipamento(rs.getString("equipuser.serial_equipamento"));
                equipamento.setId_equipamento_do_Usuario(rs.getLong("equipreq.idequipamento_user"));
                //status tabela
                Status status = new Status();
                status.setCategoria(rs.getString("statusreq.categoria"));
                status.setIdstatus(rs.getInt("statusreq.id_status"));
                //status detalhes tabela
                Usuario usuarioStatus = new Usuario();//id do usuaro na tabela detalhes status
                usuarioStatus.setId(rs.getLong("dStatus.idusuario"));
                StatusDetalhes statusDetalhes = new StatusDetalhes();
                statusDetalhes.setId_statusdetalhes(rs.getLong("dStatus.id_statusdetalhes"));
                statusDetalhes.setData_status(rs.getTimestamp("dStatus.data"));
                statusDetalhes.setObservacao_status(rs.getString("dStatus.observacao"));
                statusDetalhes.setIdUsuario(usuarioStatus);
                statusDetalhes.setIdStatus(status);
                // Id da requisicao na tabela detalhes status
                Requisicao requisicaoStatus = new Requisicao();
                requisicaoStatus.setId(rs.getLong("dStatus.idequipamento_requisicao"));
                //requisicao tabela
                Requisicao req = new Requisicao();
                req.setId(rs.getLong("equipreq.id_equipamento_requisicao"));
                req.setNome(rs.getString("equipreq.nome"));
                req.setData_criada(rs.getTimestamp("equipreq.data_requisicao"));
                req.setMotivo(rs.getString("equipreq.observacao"));
                req.setReqEquipamento(equipamento);
                req.setReqStatus(status);
                req.setReqUsuario(usuario);
                req.setReqStatus_Detalhes(statusDetalhes);
                requisicoes.add(req);
            }
            stmt.close();
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(RequisicaoDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        }
        }.start();
        return requisicoes;
    }
}

does not generate any errors, but I'm not getting this list in my tableview I think the problem is in the return requisicoes; but I can not solve.

Solution

The problem is, that you return the value of requisicoes before the thread has added anything to it.

Because the Thread is executed asynchronous, the execution looks kind of like this:

So the query is working correctly (you see the correct results when not using a thread), but they are added to the list to late, because the (empty) list is already copied to the new observable list and therefore the adding of the results doesn't affect the observable list in your controller anymore.

A solution could be to wait for the thread's execution before returning the list requisicoes (by calling the join method of the thread. But this will cause the method getList() to take much time (because it's waiting for the thread), which is probably what you wanted to avoid by using the thread.

Another (probably the better) solution would be to remove the multithreading from the method getList() and add the multithreading to your controller using a Task.

A solution could look like this:

public ObservableList<Requisicao> atualizarTabela() {
    RequisicaoDAO dao = new RequisicaoDAO();
    //create an empty list first
    requisicoes = FXCollections.observableArrayList();
    //execute the query in the task
    Task<List<Requisicao>> task = new Task<List<Requisicao>>() {

        @Override
        protected List<Requisicao> call() throws Exception {
            //getList has no thread anymore
            return dao.getList();
        }

    };
    //add the list values to the observable list, when the task succeeds
    task.setOnSucceeded(e -> requisicoes.addAll(task.getValue()));
    task.setOnFailed(e -> System.out.println("Task failed"));//better do some execption handling here...

    //start the task in a new thread
    Thread executor = new Thread(task, "get_list_task_thread");
    executor.start();

    //return the (empty) list, which will be filled when the task (that includes the query) has finished
    return requisicoes;
}

If you change you code like this (and remove the thread from the method getList() it should first return an empty ObservableList, which is displayed and filled after the task (that includes the query) has finished. The advantage of this solution is, that it doesn't stop the execution of the FX-Thread while the query is being executed, so the UI will not be stopping.

Answered By - Tobias
Answer Checked By - David Marino (JavaFixing Volunteer)