I was doing a project and needed the following logic ... link and I came across the following situation ... and if it was with 2 different queries how would it look? in my case I have to compare the result of the first select and search in the second the items that correspond to the first one ... to be more specific I have a select with patients that has appointments and I need to seek the care of each patient
Connection
package geradorhtml3;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Conexao {
public Connection conFirebird() {
Connection con = null;
String driver = "org.firebirdsql.jdbc.FBDriver";
String user = "SYSDBA";
String senha = "masterkey";
String url = "jdbc:firebirdsql://localhost:3050/D:/firebird/meubanco.FDB";
try {
Class.forName(driver);
con = (Connection) DriverManager.getConnection(url, user, senha);
System.out.println("Conexão realizada com sucesso.");
} catch (ClassNotFoundException | SQLException ex) {
System.err.println(ex.getMessage());
}
return con;
}
}
Patient package geradorhtml3;
import java.sql.Date;
public class Paciente {
private static String nome;
private static String cpf;
private static String tel;
private static Date dataNasc;
public static String getNome() {
return nome;
}
public static void setNome(String aNome) {
nome = aNome;
}
public static String getCpf() {
return cpf;
}
public static void setCpf(String aCpf) {
cpf = aCpf;
}
public static String getTel() {
return tel;
}
public static void setTel(String aTel) {
tel = aTel;
}
public static Date getDataNasc() {
return dataNasc;
}
public static void setDataNasc(Date aDataNasc) {
dataNasc = aDataNasc;
}
}
Cycles
package geradorhtml3;
import java.sql.Date;
public class Ciclo {
private static String id;
private static String nome;
private static String cod_paciente;
private static Date data;
public static String getId() {
return id;
}
public static void setId(String aId) {
id = aId;
}
public static String getNome() {
return nome;
}
public static void setNome(String aNome) {
nome = aNome;
}
public static String getCod_paciente() {
return cod_paciente;
}
public static void setCod_paciente(String aCod_paciente) {
cod_paciente = aCod_paciente;
}
public static Date getData() {
return data;
}
public static void setData(Date aData) {
data = aData;
}
}
Here is my doubt
package geradorhtml3;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.logging.Level;
import java.util.logging.Logger;
public class Buscar {
private static ArrayList c;
private static ArrayList f;
public Buscar() {
try {
ResultSet[] rs = new ResultSet[2];
Connection con = new Conexao().conFirebird();
CallableStatement cs = null;
//String sql = "SELECT * FROM PACIENTE";
//String sql = "SELECT * FROM CICLOS";
/*
Estou tentando juntar esses dois selects os quais tem um relacionamento,
porem NÃO pode ser feito no banco, tem q ser feito em codigo a uniao deles
*/
String sql = "{CALL RETORNA_CLI_FORN()}";
cs = con.prepareCall(sql);
rs[0] = cs.executeQuery();
c = new ArrayList();
Paciente cl = new Paciente();
while (rs[0].next()) {
cl.setNome(rs[0].getString("NOME"));
cl.setCpf(rs[0].getString("CPF"));
cl.setTel(rs[0].getString("TEL"));
cl.setDataNasc(rs[0].getDate("DATA_NASCIMENTO"));
c.add(cl);
}
if (cs.getMoreResults()) {
rs[1] = cs.getResultSet();
f = new ArrayList();
Ciclo fo = new Ciclo();
while (rs[1].next()) {
fo.setNome(rs[1].getString("NOME"));
fo.setCod_paciente(rs[1].getString("CNPJ"));
f.add(fo);
}
}
} catch (SQLException ex) {
Logger.getLogger(Paciente.class.getName()).log(Level.SEVERE, null, ex);
}
}
public Iterator retornaPaciente() {
Iterator i = c.iterator();
return i;
}
public Iterator retornaCiclo() {
Iterator i = f.iterator();
return i;
}
}
MAIN
package geradorhtml3;
import java.util.Iterator;
public class GeradorHtml3 {
public static void main(String[] args) {
Buscar c = new Buscar();
Iterator _ic = null;
Iterator _if = null;
Paciente cl = new Paciente();
Ciclo fo = new Ciclo();
_ic = c.retornaPaciente();
_if = c.retornaCiclo();
while (_ic.hasNext()) {
cl = (Paciente) _ic.next();
System.out.println(
cl.getNome() + " - " +
cl.getCpf() + " -" +
cl.getTel() + " - " +
cl.getDataNasc());
}
while (_if.hasNext()) {
fo = (Ciclo) _if.next();
System.out.println(fo.getNome() + " - " + fo.getCod_paciente());
}
}
}