My code returns all users, verifying that they have paid the last month, however I connect two tables, one of all the clients with the registered payment table, I want it to print in jTable
even if it does not have left join
seems to be playing the same role as inner join
,
follows the piece of code:
public void preencherTabela(){
ArrayList dados = new ArrayList();
String [] Colunas = new String[]{"CPF", "NOME", "DATA", "PAGO"};
try{
Connection con = Conexao.getConexao();
Statement stmt = con.createStatement();
ResultSet RS = stmt.executeQuery("Select cadastrarpac.nome, cadastrarpac.cpf, max(pagamento.data) from cadastrarpac "
+ "left join pagamento on (cadastrarpac.IdPac=pagamento.id_pac)");
while(RS.next()){
String nome = RS.getString("nome");
String cpf = RS.getString("Cpf");
String data = RS.getString("max(pagamento.data)");
Date date = new SimpleDateFormat("dd/MM/yyyy").parse(data);
int data2 = date.getMonth()+1;
SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy");
Date now = new Date();
int data3 = now.getMonth()+1;
boolean pago;
if (data2==data3){
pago=true;
if (data.isEmpty()){
pago=false;
}
}else{
pago=false;
}
dados.add(new Object[]{cpf, nome, data, pago});
}
}catch(Exception e){
}
ModeloTabela modelo = new ModeloTabela(dados, Colunas);
table.setModel(modelo);
table.getColumnModel().getColumn(0).setPreferredWidth(80);
table.getColumnModel().getColumn(0).setResizable(false);
table.getColumnModel().getColumn(1).setPreferredWidth(176);
table.getColumnModel().getColumn(1).setResizable(false);
table.getColumnModel().getColumn(2).setPreferredWidth(75);
table.getColumnModel().getColumn(2).setResizable(false);
table.getColumnModel().getColumn(3).setPreferredWidth(80);
table.getColumnModel().getColumn(3).setResizable(false);
table.getTableHeader().setReorderingAllowed(false);
table.setAutoResizeMode(table.AUTO_RESIZE_OFF);
table.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
}
Tabela pagamento:
id_pag int not null primary key, //id do registro de pagamento
id_pac int not null //id do paciente(cliente)
data varchar(80) not null //data do pagamento
insert into pagamento (id_pac, data) values (1, 20/04/2016)
insert into pagamento (id_pac, data) values (2, 21/04/2016)
Tabela cadastrarpac:
IdPac int not null primary key, //id do paciente
Cpf varchar(12) not null, //cpf
Nome varchar(80) not null //nome
insert into cadastrarpac (Cpf, Nome) values (088.758.960-0, Matheus) //id 1
insert into cadastrarpac (Cpf, Nome) values (089.858.966-0, Miriam) //id 2
Any help is welcome, Thanks