First, there is no reason to save letters in variable name, which is a bad programming practice.
Second, use the Try-with-Resources.
Third, avoid injection of SQL when using the PreparedStatement.
Fourthly, prefer to use the standard dao to accomplish this kind of thing.
Assuming that Oss fields from your table cliente 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 table results cliente are added to this same class. You will do something similar for the other classes. Public methods will correspond to each type of query your DAO is able to perform and will use private methods ler(PrepaparedStatement) and listar(PreparedStatement) to form the results.
Note that the results may be a List<Cliente> for the case where there may be several (or no) results. For the case where there can be only one or no results, you use Optional<Cliente>.
There also needs to be a method criar in class Cliente that creates an instance containing all the necessary data. I talk more about this in that other answer of mine.
Then you’ll join the 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 access layer to organized data, 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 would use the MVC design standard so that the presentation logic (which deals with JComboBoxes and JOptionPanes) do not get stuck with its modeling logic (which implements the rules and concepts of books, employees and customers). However, I would need more information about your project to show how to do this, and then it would be the case to ask another question, because that is well beyond the purpose of this.
Those get(0) that I had to put on 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 have done is simply to assume that there will always be one and only one outcome, but that is a dangerous thing to assume because the methods of listing by name may bring more than one result or may not bring any, which means that we might not find the idwanted or find more than one id, and if that happens then you’ll have to see what you’ll do.
In well-done professional projects using JDBC, you will never or almost never just search for the id in the database. Almost always you will want to take the whole tuple. That’s why the DAO methods return List<Cliente> and Optional<Cliente> instead of just int. So I also suspect that your approach to research the id is not correct.
Note that if the url, the usuario and the senha are fixed and immutable, you can put the ParametrosDeConexao in a variable private static final and always reuse the same instance.
Each time you use an object of the type CamadaDeDados within the Try-with-Resources, you will be running a database operation on a single connection. It is also the ideal place to implement transaction control when/if you need it.
What do you want to do with these ids later? Why do you want to read them? I ask this because having only the isolated ids is not something that has much use in practice and keep displaying the ids out there, especially if they are generated automatically, it is not good programming practice. So I assume you want to do something with these ids, and depending on what, you’ll want to research more than just the ids.
– Victor Stafusa
Ah, and read about injection of SQL. To avoid this problem, use
PreparedStatements.– Victor Stafusa
this id’s I want to store them within a variable and later save them in another table.
– Thimóteo Coelho
I’m trying to come up with a good answer, but I’m missing some information to do it. Without it, the most I can get is a goofy answer. Could you tell me the fields of these tables?
– Victor Stafusa