Query sql with multiple java fields

Asked

Viewed 687 times

2

How to do a java sql search that involves multiple arguments?
Example: I want to research all people with Angolan nationality, with the Computer Science course and who have two or more years of professional experience.

I’m wearing it like this: select * from candidato where nome like ?, but when increase more fields returns error.

This is my research method:

public List<Dados_Cadastro> getLista(String nome) throws SQLException{
     String sql="select * from candidato where nome like ?";
 PreparedStatement stm = this.conexao.prepareStatement(sql);
 stm.setString(1, nome);
 ResultSet rs = stm.executeQuery();

 List<Dados_Cadastro>nova_lista = new ArrayList<Dados_Cadastro>();
 while(rs.next()){
  Dados_Cadastro cadastro =new Dados_Cadastro();

 cadastro.setNome_cadastro(rs.getString("nome"));
 cadastro.setGenero_cadastro(rs.getString("genero"));
 cadastro.setData_nasc_cadastro(rs.getString("nasc"));
 cadastro.setCbox_nacion_cadastro(rs.getString("nacionalidade"));
 cadastro.setCbox_nivel_cadastro(rs.getString("n_academico"));
 cadastro.setCurso_cadastro(rs.getString("curso"));
 cadastro.setEmpresa_cadastro(rs.getString("empresa"));
 cadastro.setCbox_exper_cadastro(rs.getString("experiencia"));
 cadastro.setIdent(Long.valueOf(rs.getString("ident")));
nova_lista.add(cadastro);
 }
 rs.close();
 stm.close();
 return nova_lista;
 }
java sql

  • This query goes to a production system or is only study?

2 answers

2

You can number the parameters, for example:

select * from candidato where nome like ?1 AND genero = ?2

and then inform the parameters:

stm.setString(1, nome);
stm.setString(2, genero);

  • Your solution is better than Ikaro’s, but you could develop it a little more to help @user44631.

1

SELECT * FROM candidato WHERE nome LIKE '%'+getNome_cadastrado+'%';

The sign % is used to define wildcards (letters missing) before and after the pattern.

  • I don’t know if I understand, I must do like this?

  • Specify the part you didn’t understand that I can explain to you better !

  • with this code: select * from candidate Where name like ?. I can only consult by stating the registered name, but I would like to include in the search other attributes such as: nationality and experience.

  • Ahh Yes ! Just you put AND

  • SELECT * FROM candidato WHERE nome LIKE '%'+getNome_cadastrado+'%' AND nacionalidade = '+getNacionalidade+'; Just go putting the AND and assigning the attributes you want to query

  • That is: I want him to show me all records of people with nationality x, with experience y

  • If you have attributes from another table, you have to search for INNER JOIN

  • Okay, I’ll try it.

  • SELECT * FROM candidato WHERE nome LIKE '%'+getNome_cadastrado+'%' AND nascionalidade = '+getNacionalidade+' AND experiencia = '+getExperiencia+';

  • It worked Thanks bro.

  • Anything, just call... to help

  • @user44631, take a look at Julio Neto’s answer. It uses Prepared statements, as its original code, which is safer and more efficient than concatenating strings that Ikaro proposes. String concatenation makes room for SQL Injection attacks and should never be used.

  • Thanks for the tip lpacheco, I really need all these lights. I’m just a beginner.

Show 8 more comments

Browser other questions tagged java sql

You are not signed in. Login or sign up in order to post.