First, there is no reason to save letters in variable names, which is a bad programming practice.
Second, use the try-with-resources .
Third, avoid SQL Injection using PreparedStatement .
Fourth, you'd prefer to use DAO standard to do this type of thing.
Assuming that the fields in your cliente table are id , nome , telefone and cpf , your code looks something like this:
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
public class ClienteDAO {
private static String POR_NOME =
"SELECT id, nome, telefone, cpf FROM cliente WHERE nome = ?";
private static String POR_ID =
"SELECT id, nome, telefone, cpf FROM cliente WHERE id = ?";
private final Connection conexao;
public ClienteDAO(Connection conexao) {
this.conexao = conexao;
}
private Cliente popular(ResultSet rs) throws SQLException {
int id = rs.getInt(1);
String nome = rs.getString(2);
String telefone = rs.getString(3);
String cpf = rs.getString(4);
return Cliente.criar(id, nome, telefone, cpf);
}
private List<Cliente> listar(PreparedStatement statment) throws SQLException {
try (ResultSet rs = statement.executeQuery()) {
List<Cliente> resultado = new ArrayList<>();
while (rs.next()) {
Cliente c = popular(rs);
resultado.add(c);
}
return resultado;
}
}
private Optional<Cliente> ler(PreparedStatement statment) throws SQLException {
try (ResultSet rs = statement.executeQuery()) {
if (rs.next()) return Optional.of(popular(rs));
return Optional.empty();
}
}
public List<Cliente> porNome(String nome) throws SQLException {
try (PreparedStatement statement = conexao.prepareStatement(POR_NOME)) {
statement.setString(1, nome);
return listar(statement);
}
}
public Optional<Cliente> porId(int id) throws SQLException {
try (PreparedStatement statement = conexao.prepareStatement(POR_ID)) {
statement.setInt(1, id);
return ler(statement);
}
}
}
Other methods that bring results from the cliente table are added to this same class. You will do something similar for the other classes. Public methods will correspond to each type of query that your DAO is able to do, and will use private methods ler(PrepaparedStatement) and listar(PreparedStatement) to form the results.
Note that the results can be List<Cliente> for the case where there may be several (or no) results. For the case where there can only be one or no result, you use Optional<Cliente> .
It is also necessary to have a criar method in the Cliente class that creates an instance containing all the necessary data. I'll tell you more about this on that other answer of mine .
Next, you'll join DAOs more or less like this:
import java.sql.DriverManager;
import java.sql.SQLException;
public class ParametrosDeConexao {
private final String url;
private final String usuario;
private final String senha;
public ParametrosDeConexao(String url, String usuario, String senha) {
this.url = url;
this.usuario = usuario;
this.senha = senha;
}
public Connection conectar() throws SQLException {
return DriverManager.getConnection(url, usuario, senha);
}
}
import java.sql.Connection;
import java.sql.SQLException;
public class CamadaDeDados implements AutoCloseable {
private final Connection conexao;
private final ClienteDAO clientes;
private final FuncionarioDAO funcionarios;
private final LivroDAO livros;
public CamadaDeDados(ParametrosDeConexao params) throws SQLException {
this.conexao = params.conectar();
this.clientes = new ClienteDAO(conexao);
this.funcionarios = new FuncionarioDAO(conexao);
this.livros = new LivroDAO(conexao);
}
public ClienteDAO clientes() {
return clientes;
}
public FuncionarioDAO funcionarios() {
return funcionarios;
}
public LivroDAO livros() {
return livros;
}
@Override
public void close() throws SQLException {
conexao.close();
}
}
Now that we have the data access layer organized, you can do this:
private ParametrosDeConexao params() {
return new ParametrosDeConexao(url, usuario, senha);
}
private void seuMetodo() {
String nomeCliente = (String) cbNomeCliente.getSelectedItem();
String nomeFuncionario = (String) cbNomeFuncionario.getSelectedItem();
String nomeLivro = (String) cbNomeLivro.getSelectedItem();
int idCliente, idFuncionario, idLivro;
try (CamadaDeDados c = new CamadaDeDados(params())) {
idCliente = c.clientes().porNome(nomeCliente).get(0).getId();
idFuncionario = c.funcionarios().porNome(nomeFuncionario).get(0).getId();
idLivro = c.livros().porNome(nomeLivro).get(0).getId();
} catch (SQLException x) {
JOPtionPane.showMessageDialog(null, x.getMessage());
}
}
Ideally, you should use the MVC design pattern so that presentation logic (which deals with JComboBox s and JOptionPane s) is not stuck with your modeling logic (which implements rules and concepts of books, employees and customers). However, I would need more information about your project to show you how to do this, and then it would be the case to ask another question, as this is well beyond the purpose of this one.
These get(0) I had to put are probably not ideal, being unwanted things. However, to get rid of them, I would need to have more information about your project. With these get(0) what I did was simply assume that there will always be one and only one result, but this is a dangerous thing to assume because the listing methods by name can bring more than one result or may bring none, which means that we might not find the% s of searched% s or find more than id , and if that happens, then you'll have to see what you're going to do.
In well-designed professional projects using JDBC, you'll never or almost never just search for the id in the database. You will almost always want to get the entire tuple. That's why DAO methods return id and List<Cliente> instead of just Optional<Cliente> . So I also suspect that your approach to searching for int is not correct.
Note that if id , url , and usuario are fixed and immutable, you can set senha to a variable ParametrosDeConexao and always reuse the same instance.
Each time you use an object of type private static final within try-with-resources , you are performing a database operation on a single connection. It is also the ideal place to implement transaction control when / if you need it.
