Olá galera, creio que minha dúvida é básica pra muitos, já procurei no google e aqui no guj mas não encontrei um resposta pra isso…
Tenho um formulário com vários campos (filtros) que se não selecionados ou preenchidos devem trazer todos os registros.
Acontece que quando não preencho o campo a consulta SQL não traz nada, como se tivesse vazio.
Vou tentar dar um exemplo simplificando:
Quero listar os clientes de um determinado estado, no formulário tenho o campo campo estado e se eu não preencher este campo alista deve retornar todos os clientes de todos os estados, mas o que está acontecendo é que a lista não está carregando nada, nem dá erro e nem carrega, como se ela estivesse vazia, se eu selecionar um estado ele trás o clientes corretamente.
Como faço para trazer todos os registros caso aquele campo seja enviado “vazio”?
Segue e código que estou usando
public List<Rua> selecionaRua(Rua rua, HttpServletRequest request,
HttpServletResponse response) throws IOException, ServletException {
try {
List<Rua> ruas = new ArrayList<Rua>();
String sql = "select * from plantas inner join ruas on plantas.id_planta = ruas.id_planta where sigla_planta = ?";//
PreparedStatement stmt = this.connection.prepareStatement(sql);
stmt.setString(1, (request.getParameter("planta")));//ESTA É O CAMPO DO FOMULÁRIO, SE EU NÃO PREENCHER NÃO TRAZ NADA MAS QUERO QUE TRAGA TUDO
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
// adiciona a Rua na lista
ruas.add(populaRua(rs));
}
rs.close();
stmt.close();
System.out.println("\n");
return ruas;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
Você precisa verificar se há ou não parâmetro. Pois isso,
String sql = "select * from plantas inner join ruas on plantas.id_planta = ruas.id_planta where sigla_planta = ?";
sem parâmetro é o mesmo que:
SELECT * from plantes INNER JOIN ruas on plantas.id_plante = ruas.id_planta WHERE sigla_planta = ''
E, creio que sigla_planta seja diferente de ‘’ em todas as ocasiões
[quote=drsmachado]Você precisa verificar se há ou não parâmetro. Pois isso,
String sql = "select * from plantas inner join ruas on plantas.id_planta = ruas.id_planta where sigla_planta = ?";
sem parâmetro é o mesmo que:
SELECT * from plantes INNER JOIN ruas on plantas.id_plante = ruas.id_planta WHERE sigla_planta = ''
E, creio que sigla_planta seja diferente de ‘’ em todas as ocasiões[/quote]
Desculpe minha ignorância, mas como farei a verificação, usando if? até pensei em fazer isso mas gostaria de saber se existe outro modo mais simples…
Trabalhei com o ASP vários anos (já o abandonei …rsss) e nas strings SQL quando o valor da variável era vazio ele retornava todos os registros… se não me engano o PHP também é assim…
Afinal no java, terei de “picotar” minha string sql pra verificar todos os campos que estão vazios?
Uma primeria coisa legal pra organizar o código seria isolar essas sql num DAO.
Dentro daquilo que eu conheço, usando sql, não tem muito como fugir de picotar a consulta. A opção que você tem é você mesmo picotar, ou deixar alguma biblioteca fazer isso pra você. Se você estivesse usando Criteria do Hibernate, ele mesmo geraria a sql seguindo as regras dele.
Como esse não é o caso, dá pra organizar um pouco esse código que picota a sql. Fiz um exemplo só pra ilustrar a idéia.
public class TestaCriteriolator {
public static void main(String[] args) throws SQLException {
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/base", "root", "");
Consulta consulta = new Consulta("select * from Aluno ", conn);
consulta.novoCriterio(new Criterio("id", null)); // o if foi pra tirar os nulls foi pra dentro do método novoCriterio
consulta.novoCriterio(new Criterio("nome", "Manolo"));
consulta.novoCriterio(new Criterio("cidade", ""));
PreparedStatement stmt = consulta.montaStatement();
ResultSet rs = stmt.executeQuery();
while(rs.next()) {
System.out.println(rs.getString(1));
}
}
}
Tem uma classe chamada Consulta que encapsula a montagem da query num método montaStatement()
public class Consulta {
List<Criterio> lista = new LinkedList<Criterio>();
private final Connection conn;
private StringBuilder sql;
public Consulta(String consultaBase, Connection conn) {
this.sql = new StringBuilder(consultaBase);
this.conn = conn;
}
public void novoCriterio(Criterio criterio) {
if(!criterio.ehNuloOuVazio()) {
lista.add(criterio);
}
}
public PreparedStatement montaStatement() throws SQLException {
if(!lista.isEmpty()) sql.append(" where 1=1 "); // gambi pra poder já colar o and no primeiro critério
for (Criterio criterio : lista) {
sql.append(format(" and %s = ? ", criterio.getColuna())); // sempre fazendo a conjunção (and) de todos os critérios - uma GRANDE limitação
}
PreparedStatement stmt = conn.prepareStatement(sql.toString());
ListIterator<Criterio> it = lista.listIterator();
while(it.hasNext()) {
Criterio criterio = it.next();
stmt.setObject(it.nextIndex(), criterio.getValor()); // pega a posicao em que o ? foi colocado no stmt e atribui valor correspondente
}
return stmt;
}
}
public class Criterio {
private final String coluna;
private final Object valor;
public Criterio(String chave, Object valor) {
if(chave == null) throw new IllegalArgumentException("A coluna é necessária para definir um critério");
this.coluna = chave;
this.valor = valor;
}
public boolean ehNuloOuVazio() {
return this.valor == null;
}
public String getColuna() {
return coluna;
}
public Object getValor() {
return valor;
}
}
Uma séria limitação dessa montagem de query (a parte a gambi do 1=1) é que ela usa sempre “and isso and aquilo …” . Dá pra pensar em abstrações melhores pra esse problema. E cedo ou tarde você acabaria reinventando a Criteria da JPA :]
Acho que essa é uma primeira organização pra história de montar a sql, dá pra sofisticar bastante em cima desse problema.
[quote=Ailton_Occhi][quote=drsmachado]Você precisa verificar se há ou não parâmetro. Pois isso,
String sql = "select * from plantas inner join ruas on plantas.id_planta = ruas.id_planta where sigla_planta = ?";
sem parâmetro é o mesmo que:
SELECT * from plantes INNER JOIN ruas on plantas.id_plante = ruas.id_planta WHERE sigla_planta = ''
E, creio que sigla_planta seja diferente de ‘’ em todas as ocasiões[/quote]
Desculpe minha ignorância, mas como farei a verificação, usando if? até pensei em fazer isso mas gostaria de saber se existe outro modo mais simples…
Trabalhei com o ASP vários anos (já o abandonei …rsss) e nas strings SQL quando o valor da variável era vazio ele retornava todos os registros… se não me engano o PHP também é assim…
Afinal no java, terei de “picotar” minha string sql pra verificar todos os campos que estão vazios?
[/quote]
Sinceramente, acho que esses “vários anos” de ASP e PHP não te ensinaram nada.
Isso aqui
select * from plantas inner join ruas on plantas.id_planta = ruas.id_planta where sigla_planta = SEU_PARAM_AQUI
Não é Java, PHP, ASP, COBOL, Fortran, Clipper, C#, VB, Ruby ou o que você imagina que seja. Isso é Structured Query Language, vulgarmente chamada SQL e, até onde os bancos de dados entendem, caso o valor de SEU_PARAM_AQUI seja:
Agora, se você quisesse que ele trouxesse registros onde sigla_planta fosse parecida com algo, deveria usar:
select * from plantas inner join ruas on plantas.id_planta = ruas.id_planta where sigla_planta LIKE SEU_PARAM_AQUI
E, neste caso, se o parâmetro SEU_PARAM_AQUI fosse:
Como assim, como isso é possível?
A cláusula WHERE tem como função determinar o que servirá de filtro, na combinação COLUNA VALOR.
Os operadores válidos para as cláusulas WHERE são:
= igual
<> diferente
maior
< menor
= maior ou igual
<= menor ou igual
LIKE aproximado, parecido com
NOT LIKE não parecido com
IN no meio de um intervalo de valores (no mínimo um valor)
NOT IN esteja fora do meio do intervalo de valores informado
Se você utilizar uma cláusula WHERE na query, provavelmente utilizará um destes operandos, sempe posicionados entre uma coluna e um valor que representará o parâmetro de comparação.
Ok, mas, como resolver seu problema?
Ora, simples. Para selecionar todos, basta não usar operandos e não usar a cláusula WHERE.
Uma primeria coisa legal pra organizar o código seria isolar essas sql num DAO.
Dentro daquilo que eu conheço, usando sql, não tem muito como fugir de picotar a consulta. A opção que você tem é você mesmo picotar, ou deixar alguma biblioteca fazer isso pra você. Se você estivesse usando Criteria do Hibernate, ele mesmo geraria a sql seguindo as regras dele.
Como esse não é o caso, dá pra organizar um pouco esse código que picota a sql. Fiz um exemplo só pra ilustrar a idéia.
public class TestaCriteriolator {
public static void main(String[] args) throws SQLException {
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/base", "root", "");
Consulta consulta = new Consulta("select * from Aluno ", conn);
consulta.novoCriterio(new Criterio("id", null)); // o if foi pra tirar os nulls foi pra dentro do método novoCriterio
consulta.novoCriterio(new Criterio("nome", "Manolo"));
consulta.novoCriterio(new Criterio("cidade", ""));
PreparedStatement stmt = consulta.montaStatement();
ResultSet rs = stmt.executeQuery();
while(rs.next()) {
System.out.println(rs.getString(1));
}
}
}
Tem uma classe chamada Consulta que encapsula a montagem da query num método montaStatement()
public class Consulta {
List<Criterio> lista = new LinkedList<Criterio>();
private final Connection conn;
private StringBuilder sql;
public Consulta(String consultaBase, Connection conn) {
this.sql = new StringBuilder(consultaBase);
this.conn = conn;
}
public void novoCriterio(Criterio criterio) {
if(!criterio.ehNuloOuVazio()) {
lista.add(criterio);
}
}
public PreparedStatement montaStatement() throws SQLException {
if(!lista.isEmpty()) sql.append(" where 1=1 "); // gambi pra poder já colar o and no primeiro critério
for (Criterio criterio : lista) {
sql.append(format(" and %s = ? ", criterio.getColuna())); // sempre fazendo a conjunção (and) de todos os critérios - uma GRANDE limitação
}
PreparedStatement stmt = conn.prepareStatement(sql.toString());
ListIterator<Criterio> it = lista.listIterator();
while(it.hasNext()) {
Criterio criterio = it.next();
stmt.setObject(it.nextIndex(), criterio.getValor()); // pega a posicao em que o ? foi colocado no stmt e atribui valor correspondente
}
return stmt;
}
}
public class Criterio {
private final String coluna;
private final Object valor;
public Criterio(String chave, Object valor) {
if(chave == null) throw new IllegalArgumentException("A coluna é necessária para definir um critério");
this.coluna = chave;
this.valor = valor;
}
public boolean ehNuloOuVazio() {
return this.valor == null;
}
public String getColuna() {
return coluna;
}
public Object getValor() {
return valor;
}
}
Uma séria limitação dessa montagem de query (a parte a gambi do 1=1) é que ela usa sempre “and isso and aquilo …” . Dá pra pensar em abstrações melhores pra esse problema. E cedo ou tarde você acabaria reinventando a Criteria da JPA :]
Acho que essa é uma primeira organização pra história de montar a sql, dá pra sofisticar bastante em cima desse problema.
Espero ter ajudado.
Valeu![/quote]
Sei que a intenção foi boa, mas, o inferno tá cheio de boas intenções.
Você pode perceber que ele está com sérios problemas para resolver um problema simples, certo?
Por que colocar coisas mais complexas, como conceitos de Criteria?
Só uma dica, tente nivelar a resposta pelo nível do autor do tópico, ok?
Galera, que viajem, realmente fiz uma confusão com o operador = … mesmo no asp quando uso = na string sql tenho que verificar se campo está preenchido ou não para remover o operador e o parametro da string .(Não sei da onde tinha tirado aquilo, o java está me deixando doido…kkkkkk :shock: )
Este exemplo que passei é um exemplo simplificado, meu formulário terá muitos campos para filtrar…
Eric, segui o que aprendi em aula, esta lista está numa RuaDAO.java… é que passei somente um trecho dela…
drsmachado,não quero passar o parâmetro direto na sintring sql, quero passar através do pstmt pois ele trata a sql e torna mais seguro, a minha dúvida era realmente se eu teria de picotar (não usar operadores) fazendo verificações tanto na string como no parametro do stmt pra saber qual campo está ou não preenchido.
Como o pstmt trata a sql achei que que teria alguma forma mais simples dele fazer isso, mas não, se eu remover um operador e uma ? da string, também tenho que remover o parâmetro abaixo dela…
Então pra fechar eu tenho 2 soluções, uso outra classe e funções pra montar a consulta SQL como o Eric passou o exemplo ou verifico se os campos estão preenchidos e picoto minha string e o stmt.
É isso aí…
(PS: drsmachado, “Sinceramente, acho que esses “vários anos” de ASP e PHP não te ensinaram nada”… eu aprendi cedo o que é humildade e como é bom tentar enteder as pessoas, principalmente quando elas tem dúvida, quem nunca errou ou confundiu algo na nossa área?
de qualquer forma valeu pela boa intenção, sua aulinha de SQL ajudou a relembrar algumas funções do like que faz muito tempo que não uso…)
