Bom dia amigos,
Estou com o seguinte problema:
-Fazer uma consulta aninhada entre duas tabelas diferentes com JDBC, onde a segunda query pega um parametro da primeira para iniciar a segunda consulta.
Exemplo:
Query 1: Select id_maquina, nome_maquina, data, numero_chamado, problema from Maquinario where id_maquina = ‘parametro_id_maquina’;
Query 2: Select id_peca, nome_peca, data_saida, qtde_pecas, numero_chamado, justificativa from Pecas where numero_chamado = ‘numero_chamado_da_query_1’;
Eu fiz algo como loop encadeado tipo que segue abaixo.
Isso é a melhor forma de fazer consultas aninhadas? Vejam:
public List listHistorical(String type_busca, String parameter, String dataInitial, String dataFinal) throws SQLException, NamingException, ClassNotFoundException {
/*'parametro_id_maquina*/
String selectTOS = "";
/*cria a query de acordo com o tipo de busca e o parametro é o número da máquina*/
selectTOS = createQuery(type_busca, parameter, dataInitial, dataFinal, selectTOS);
/*a new DAOConnector*/
DAOConnector dao = new DAOConnector();
Statement stmtTOS = dao.getCon().createStatement();
ResultSet rsTOS = stmtTOS.executeQuery(selectTOS);
List list = new ArrayList();
while(rsTOS.next()) {
Historical historical = new Historical();
historical.getSummary().setTos(rsTOS.getString("TOS"));
/*este é o número do chamado a ser utilizado na query_2*/
historical.getSummary().setCallNbr(rsTOS.getString("CALL_NBR"));
historical.getSummary().setCreated(rsTOS.getString("CREATED"));
historical.getSummary().setClosed(rsTOS.getString("CLOSED"));
historical.getSummary().setCustomerNbr(rsTOS.getString("CUSTOMER_NBR"));
historical.getSummary().setCustomerName(rsTOS.getString("CUSTOMER_NAME"));
historical.getSummary().setCallECI(rsTOS.getString("CALL_ECI"));
historical.getSummary().setEmpName(rsTOS.getString("EMP_NAME"));
historical.getSummary().setCallType(rsTOS.getString("CALL_TYPE"));
historical.getSummary().setCallStatus(rsTOS.getString("CALL_STATUS"));
historical.getSummary().setCause(rsTOS.getString("CAUSE"));
historical.getSummary().setDefect(rsTOS.getString("DEFECT"));
historical.getSummary().setSolution(rsTOS.getString("SOLUTION"));
historical.getSummary().setReportedProblem(rsTOS.getString("REPORTED_PROBLEM"));
/*query_2*/
String selectPARTS = "SELECT A.PN AS PN, INTEGER(A.QTY) AS QTY, RTRIM(A.DESCRIPTION) AS DESC, DATE(A.REQUEST_TMS) AS DTREQ, TIME(A.REQUEST_TMS) AS TMREQ, FILIAL, A.REF_LCL AS REF_LCL, A.REF_RMT AS REF_RMT, B.DATA_ENTREGA AS DATA_ENTREGA, B.HORA_ENTREGA AS HORA_ENTREGA, RTRIM(B.ENTREGUE_PARA) AS ENTREGUE_PARA FROM ACB.PARTS AS A LEFT JOIN ACB.PARTS_DELIVERY AS B ON A.PN=B.PN AND A.REF_LCL=B.REF_LCL AND A.CALL_NBR=B.CALL_NBR WHERE A.CALL_NBR = '" + historical.getSummary().getCallNbr() + "' ORDER BY QTY,PN DESC fetch first 10 rows only with UR";
Statement stmtPARTS = dao.getCon().createStatement();
ResultSet rsPARTS = stmtPARTS.executeQuery(selectPARTS);
List lis = new ArrayList();
while(rsPARTS.next()) {
Parts parts = new Parts();
parts.setPn(rsPARTS.getString("PN"));
parts.setQty(rsPARTS.getString("QTY"));
parts.setDescription(rsPARTS.getString("DESC"));
parts.setRequestTms(rsPARTS.getString("DTREQ"));
parts.setRequestTms(rsPARTS.getString("TMREQ"));
parts.setFilial(rsPARTS.getString("FILIAL"));
parts.setRefLCL(rsPARTS.getString("REF_LCL"));
parts.setRefRmt(rsPARTS.getString("REF_RMT"));
parts.setShip(rsPARTS.getString("DATA_ENTREGA"));
parts.setStatusTms(rsPARTS.getString("HORA_ENTREGA"));
parts.setForUser(rsPARTS.getString("ENTREGUE_PARA"));
lis.add(parts);
historical.setList(lis);
}
/*
* Close the connection after execution
*/
rsPARTS.close();
stmtPARTS.close();
//partsDAO.connectionClosing();
list.add(historical);
}
/*
* Close the connection after execution
*/
rsTOS.close();
stmtTOS.close();
dao.connectionClosing();
/*
* return the populated list
*/
return list;
}