o meu DAO
public class MoradorDAO {
private final Connection connection;
public MoradorDAO(Connection connection){
this.connection = connection;
}
public Morador insert(Morador morador)throws SQLException{
String sql = "insert into morador(usuariocompleto,rg,cpf,nomedocondominio,numerodoapartamento,bloco,telefone,nomedoproprietario,nomedoveiculo,placa,marca,cor,entradacarro,saidacarro,entradamoradores,saidamoradores)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, morador.getUsuariocompleto());
statement.setString(2, morador.getRg());
statement.setString(3, morador.getCpf());
statement.setString(4, morador.getNomedacondominio());
statement.setString(5, morador.getNumerodoapartamento());
statement.setString(6, morador.getBloco());
statement.setString(7, morador.getTelefone());
statement.setString(8, morador.getNomedoproprietario());
statement.setString(9, morador.getNomedoveiculo());
statement.setString(10, morador.getPlaca());
statement.setString(11, morador.getMarca());
statement.setString(12, morador.getCor());
statement.setString(13, morador.getEntradacarro());
statement.setString(14, morador.getSaidacarro());
statement.setString(15, morador.getEntradamoradores());
statement.setString(16, morador.getSaidamoradores());
statement.execute();
ResultSet resultSet = statement.getGeneratedKeys();
if(resultSet.next()){
int id = resultSet.getInt("id");
morador.setId(id);
}
return morador;
}
public void update(Morador morador) throws SQLException{
String sql = "update morador set usuariocompleto = ?,rg = ?,cpf = ?,nomedocondominio = ?,numerodoapartamento = ?,bloco = ?,telefone = ?,nomedoproprietario = ?,nomedoveiculo = ?,placa = ?,marca = ?,cor = ?,entradacarro = ? ,saidacarro = ?,entradamoradores = ?,saidamoradores = ? where id = ? ";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, morador.getUsuariocompleto());
statement.setString(2, morador.getRg());
statement.setString(3, morador.getCpf());
statement.setString(4, morador.getNomedacondominio());
statement.setString(5, morador.getNumerodoapartamento());
statement.setString(6, morador.getBloco());
statement.setString(7, morador.getTelefone());
statement.setString(8, morador.getNomedoproprietario());
statement.setString(9, morador.getNomedoveiculo());
statement.setString(10, morador.getPlaca());
statement.setString(11, morador.getMarca());
statement.setString(12, morador.getCor());
statement.setString(13, morador.getEntradacarro());
statement.setString(14, morador.getSaidacarro());
statement.setString(15, morador.getEntradamoradores());
statement.setString(16, morador.getSaidamoradores());
statement.setInt(17, morador.getId());
statement.execute();
}
public void insertorUpdate(Morador morador) throws SQLException{
if(morador.getId() > 0){
update(morador);
}else{
insert(morador);
}
}
public void delete(Morador morador) throws SQLException{
String sql = "delete from morador where id = ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setInt(1, morador.getId());
statement.execute();
}
public ArrayList<Morador> selectAll() throws SQLException{
String sql = "select * from morador";
PreparedStatement statement = connection.prepareStatement(sql);
return pesquisa(statement);
}
private ArrayList<Morador> pesquisa(PreparedStatement statement) throws SQLException {
ArrayList<Morador> moradores = new ArrayList<Morador>();
statement.execute();
ResultSet resultSet = statement.getResultSet();
while(resultSet.next()){
int id = resultSet.getInt("id");
String usuariocompleto = resultSet.getString("usuariocompleto");
String rg = resultSet.getString("rg");
String cpf = resultSet.getString("cpf");
String nomedocondominio = resultSet.getString("nomedocondominio");
String numerodoapartamento = resultSet.getString("numerodoapartamento");
String bloco = resultSet.getString("bloco");
String telefone = resultSet.getString("telefone");
String nomedoproprietario = resultSet.getString("nomedoproprietario");
String nomedoveiculo = resultSet.getString("nomedoveiculo");
String placa = resultSet.getString("placa");
String marca = resultSet.getString("marca");
String cor = resultSet.getString("cor");
String entradacarro = resultSet.getString("entradacarro");
String saidacarro = resultSet.getString("saidacarro");
String entradamoradores = resultSet.getString("entradamoradores");
String saidamoradores = resultSet.getString("saidamoradores");
Morador moradorComDadosDoBanco = new Morador(id);
moradores.add(moradorComDadosDoBanco);
}
return moradores;
}
public Morador selectPorId(Morador morador) throws SQLException{
String sql = "select * from morador where id = ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setInt(1, morador.getId());
return pesquisa(statement).get(0);
}
public boolean existeNoBancoPorMorador(Morador morador) throws SQLException {
String sql = "select * from morador where usuariocompleto = ?,rg = ?,cpf = ?,nomedocondominio = ?,numerodoapartamento = ?,bloco = ?,telefone = ?,nomedoproprietario = ?,nomedoveiculo = ?,placa = ?,marca = ?,cor = ?,entradacarro = ?,saidacarro = ?,entradamoradores = ? and saidamoradores = ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, morador.getUsuariocompleto());
statement.setString(2, morador.getRg());
statement.setString(3, morador.getCpf());
statement.setString(4, morador.getNomedacondominio());
statement.setString(5, morador.getNumerodoapartamento());
statement.setString(6, morador.getBloco());
statement.setString(7, morador.getTelefone());
statement.setString(9, morador.getNomedoveiculo());
statement.setString(10, morador.getPlaca());
statement.setString(11, morador.getMarca());
statement.setString(12, morador.getCor());
statement.setString(13, morador.getEntradacarro());
statement.setString(14, morador.getSaidacarro());
statement.setString(15, morador.getEntradamoradores());
statement.setString(16, morador.getSaidamoradores());
statement.execute();
ResultSet resultSet = statement.getResultSet();
return resultSet.next();
}
public List<Morador> read() throws SQLException {
Connection conexao = Conexao.getConnection();
PreparedStatement statement = null;
ResultSet resultSet = null;
List<Morador> moradores = new ArrayList<>();
try {
statement = conexao.prepareStatement("select * from morador");
resultSet = statement.executeQuery();
while(resultSet.next()){
Morador morador = new Morador(0);
morador.setId(resultSet.getInt("id"));
morador.setUsuariocompleto(resultSet.getString("usuariocompleto"));
morador.setRg(resultSet.getString("rg"));
morador.setCpf(resultSet.getString("cpf"));
morador.setNomedacondominio(resultSet.getString("nomedocondominio"));
morador.setNumerodoapartamento(resultSet.getString("numerodoapartamento"));
morador.setBloco(resultSet.getString("bloco"));
morador.setTelefone(resultSet.getString("telefone"));
morador.setNomedoproprietario(resultSet.getString("nomedoproprietario"));
morador.setNomedoveiculo(resultSet.getString("nomedoveiculo"));
morador.setPlaca(resultSet.getString("placa"));
morador.setMarca(resultSet.getString("marca"));
morador.setCor(resultSet.getString("cor"));
morador.setEntradacarro(resultSet.getString("entradacarro"));
morador.setSaidacarro(resultSet.getString("saidacarro"));
morador.setEntradamoradores(resultSet.getString("entradamoradores"));
morador.setSaidamoradores(resultSet.getString("saidamoradores"));
moradores.add(morador);
}
} catch (SQLException ex) {
Logger.getLogger(MoradorDAO.class.getName()).log(Level.SEVERE, null, ex);
}
return moradores;
}
}