Like does not return data

Asked

Viewed 82 times

0

I have a function in mysql that returns a decimal value. In this function it has a like. When I test sql in mysql the value is returned if I enter a letter or more than one. Ex: LIKE 'Home%'. As for the sql that is in class method on the Tomcat server, when a letter is inserted in an editText of an Android app the data is returned, "but if inserted more than one letter, the returned data is 0.00, that is, null", corresponding to the line of the function in mysql: ifnull(sum(cp.valuePaga), 0.00) as v into value. Detail, testing in the browser also returns 0.00. I ask: Why is null returning in the java class while in the mysql test returns the value? I’m waiting, thank you.

Follows the function:

CREATE DEFINER=`root`@`localhost` FUNCTION `somaContaNomeMesAno`(conta varchar(20), 
data varchar(10)) RETURNS double(8,2)
BEGIN
DECLARE valor double(8,2);

select ifnull(sum(cp.valorContaPaga), 0.00) as v into valor
from contaFixa cf, contapagar cp 
where
cf.contaFixa like (insert('%%', 1, 1, conta)) -- funfando assim hehehe...
and month(cp.dataContaPaga) = month(data)
and year(cp.dataContaPaga) = year(data) 
and cf.idContaFixa = cp.idContaFixa;    

return valor;  

END;

Countdown table:

IdContaFixa int(11)
contaFixa varchar(20)
dataContaFixa date
valorContaFixa double(6,2)
qtddParcela char(3)

Table countdown:

idContaPagar int(11)
idContaFixa int(11)
dataVencimento date
valorContaPagar double(6,2)
dataContaPagar date
parcela tinyint(3)

Class:

package br.com.restfull.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Locale;

import br.com.restfull.factory.ConnectionFactory;
import br.com.restfull.model.ContaPagar;

public class ContaPagarDAO {    

private Connection connection;
private static ContaPagarDAO instance;
static NumberFormat nf = NumberFormat.getCurrencyInstance(new Locale("pt", "BR"));
static SimpleDateFormat dbDateFormat = new SimpleDateFormat("dd/MM/yyyy");

public static ContaPagarDAO getIsntance(){

    if(instance == null){
        instance = new ContaPagarDAO();         
    }
    return instance;        
}

public ContaPagarDAO(){
    this.connection = new ConnectionFactory().getConnection();
}

public ContaPagarDAO(Connection connection) {
    this.connection = connection;
}

public ArrayList<ContaPagar> getListNomeContaPagaMesAno(String conta,String dataPagamento) 
        throws ParseException {

    String SQL =  "select cp.idContaPagar, cp.idContaFixa, cf.contaFixa, cp.dataVencimento, "
                + "cp.valorContaPaga, cp.dataContaPaga, cp.parcela, cf.qtddParcela, "
                // Aqui está o problema, buahahahaha...  !!!
                + "somaContaNomeMesAno(?,?) as 'Soma' " // <-----
                + "from contaFixa cf, contapagar cp "                   
                + "where "
                // like (?\"%\") <-- Vai ajudar muita gente hehehe...
                + "cf.contafixa like (?\"%\") " 
                + "and month(cp.dataContaPaga) = month(?) "
                + "and year(cp.dataContaPaga) = year(?) " 
                + "and cf.idContaFixa = cp.idContaFixa "
                + "order by cf.contaFixa, cp.dataContaPaga";

    try {
        ArrayList<ContaPagar> contas = new ArrayList<ContaPagar>();
        PreparedStatement stmt = this.connection.prepareStatement(SQL); 
        stmt.setString(1, conta);
// No banco o tipo é date, mas na classModel usei String... ué, q q tem?
        stmt.setString(2, dataPagamento); 
        stmt.setString(3, conta);
        stmt.setString(4, dataPagamento); 
        stmt.setString(5, dataPagamento);

        ResultSet rs = stmt.executeQuery();

        while (rs.next()){
            ContaPagar contaPaga = new ContaPagar();     
            contaPaga.setIdContaPagar(rs.getLong("idContaPagar"));           
            contaPaga.setIdContaFixa(rs.getLong("idContaFixa"));
            contaPaga.setContaFixa(rs.getString("contaFixa")); 
            contaPaga.setDataVencimento(convertMapedToSqlFormat(rs.getDate("dataVencimento")));
            contaPaga.setValorContaPaga(convertValor(rs.getString("valorContaPaga")).toString());
            contaPaga.setDataContaPaga(convertMapedToSqlFormat(rs.getDate("dataContaPaga")));
            contaPaga.setParcela(rs.getShort("parcela"));
            contaPaga.setQtddParcela(rs.getShort("qtddParcela"));
            contaPaga.setSomaContaNomeMesAno(convertValor(rs.getString("Soma"))); // alias

            contas.add(contaPaga);
        }

        rs.close();
        stmt.close();
        return contas;

    } catch (SQLException e) {
         throw new RuntimeException(e);
    }
}

/* 
* Converte para dd/MM/yyyy e contorna o problema do escape... blz !!!
* Pois estava dando pau no navegador...
* ... esses espaços entre as palavras nos deixam louUUucosss....
*/
public static String convertMapedToSqlFormat(Date date2) throws  ParseException { 
    Date date = date2; 
    Calendar cal = Calendar.getInstance(); 
    cal.setTime(date); 

    return dbDateFormat.format(cal.getTime()); 
}   

// Retorna formato Monetário
public static String convertValor(String valorServico){         
    String valorConvert = valorServico;     
    Double d = Double.valueOf(valorConvert);        
    return nf.format(d);
}
}

Classmodel: 

package br.com.restfull.model;
import java.io.Serializable;

import javax.xml.bind.annotation.XmlRootElement;

@SuppressWarnings("serial")
@XmlRootElement
public class ContaPagar implements Serializable{

private Long idContaPagar;
private Long idContaFixa;
private String contaFixa;
private String dataVencimento;
private String valorContaPaga; // No banco o tipo é double... e daí?!!  hehehe...
private String dataContaPaga;
private Short parcela;
private Short qtddParcela;
private Byte pago;
private String somaMesAno;
private String somaDia;
private String somaValorMesAno;
private String somaValorDia;
private String somaContaNomeMesAno;
private String somaContaNomeDia;    

public Long getIdContaPagar() {
    return idContaPagar;
}
public void setIdContaPagar(Long idContaPagar) {
    this.idContaPagar = idContaPagar;
}
public Long getIdContaFixa() {
    return idContaFixa;
}
public void setIdContaFixa(Long idContaFixa) {
    this.idContaFixa = idContaFixa;
}   
public String getContaFixa() {
    return contaFixa;
}
public void setContaFixa(String contaFixa) {
    this.contaFixa = contaFixa;
}
public String getDataVencimento() {
    return dataVencimento;
}
public void setDataVencimento(String dataVencimento) {
    this.dataVencimento = dataVencimento;
}
public String getValorContaPaga() {
    return valorContaPaga;
}
public void setValorContaPaga(String valorContaPaga) {
    this.valorContaPaga = valorContaPaga;
}
public String getDataContaPaga() {
    return dataContaPaga;
}
public void setDataContaPaga(String dataContaPaga) {
    this.dataContaPaga = dataContaPaga;
}
public Byte getPago() {
    return pago;
}
public void setPago(Byte pago) {
    this.pago = pago;
}   
public Short getParcela() {
    return parcela;
}
public void setParcela(Short parcela) {
    this.parcela = parcela;
}
public Short getQtddParcela() {
    return qtddParcela;
}
public void setQtddParcela(Short qtddParcela) {
    this.qtddParcela = qtddParcela;
}   
public String getSomaMesAno() {
    return somaMesAno;
}
public void setSomaMesAno(String somaMesAno) {
    this.somaMesAno = somaMesAno;
}
public String getSomaDia() {
    return somaDia;
}
public void setSomaDia(String somaDia) {
    this.somaDia = somaDia;
}
public String getSomaValorMesAno() {
    return somaValorMesAno;
}
public void setSomaValorMesAno(String somaValorMesAno) {
    this.somaValorMesAno = somaValorMesAno;
}
public String getSomaValorDia() {
    return somaValorDia;
}
public void setSomaValorDia(String somaValorDia) {
    this.somaValorDia = somaValorDia;
}   
public String getSomaContaNomeMesAno() {
    return somaContaNomeMesAno;
}
public void setSomaContaNomeMesAno(String somaContaNomeMesAno) {
    this.somaContaNomeMesAno = somaContaNomeMesAno;
}
public String getSomaContaNomeDia() {
    return somaContaNomeDia;
}
public void setSomaContaNomeDia(String somaContaNomeDia) {
    this.somaContaNomeDia = somaContaNomeDia;
}

public String toString(){

    return this.idContaPagar + 
            this.idContaFixa + 
            this.contaFixa + 
            this.dataVencimento + 
            this.valorContaPaga + 
            this.dataContaPaga + 
            this.pago;
}   

@Override
public int hashCode() {
    final int prime = 31;
    int result = 1;
    result = prime * result
            + ((contaFixa == null) ? 0 : contaFixa.hashCode());
    result = prime * result
            + ((dataContaPaga == null) ? 0 : dataContaPaga.hashCode());
    result = prime * result
            + ((dataVencimento == null) ? 0 : dataVencimento.hashCode());
    result = prime * result
            + ((idContaFixa == null) ? 0 : idContaFixa.hashCode());
    result = prime * result
            + ((idContaPagar == null) ? 0 : idContaPagar.hashCode());
    result = prime * result + ((pago == null) ? 0 : pago.hashCode());
    result = prime * result + ((parcela == null) ? 0 : parcela.hashCode());
    result = prime * result
            + ((qtddParcela == null) ? 0 : qtddParcela.hashCode());
    result = prime
            * result
            + ((somaContaNomeDia == null) ? 0 : somaContaNomeDia.hashCode());
    result = prime
            * result
            + ((somaContaNomeMesAno == null) ? 0 : somaContaNomeMesAno
                    .hashCode());
    result = prime * result + ((somaDia == null) ? 0 : somaDia.hashCode());
    result = prime * result
            + ((somaMesAno == null) ? 0 : somaMesAno.hashCode());
    result = prime * result
            + ((somaValorDia == null) ? 0 : somaValorDia.hashCode());
    result = prime * result
            + ((somaValorMesAno == null) ? 0 : somaValorMesAno.hashCode());
    result = prime * result
            + ((valorContaPaga == null) ? 0 : valorContaPaga.hashCode());
    return result;
}

@Override
public boolean equals(Object obj) {
    if (this == obj)
        return true;
    if (obj == null)
        return false;
    if (getClass() != obj.getClass())
        return false;
    ContaPagar other = (ContaPagar) obj;
    if (contaFixa == null) {
        if (other.contaFixa != null)
            return false;
    } else if (!contaFixa.equals(other.contaFixa))
        return false;
    if (dataContaPaga == null) {
        if (other.dataContaPaga != null)
            return false;
    } else if (!dataContaPaga.equals(other.dataContaPaga))
        return false;
    if (dataVencimento == null) {
        if (other.dataVencimento != null)
            return false;
    } else if (!dataVencimento.equals(other.dataVencimento))
        return false;
    if (idContaFixa == null) {
        if (other.idContaFixa != null)
            return false;
    } else if (!idContaFixa.equals(other.idContaFixa))
        return false;
    if (idContaPagar == null) {
        if (other.idContaPagar != null)
            return false;
    } else if (!idContaPagar.equals(other.idContaPagar))
        return false;
    if (pago == null) {
        if (other.pago != null)
            return false;
    } else if (!pago.equals(other.pago))
        return false;
    if (parcela == null) {
        if (other.parcela != null)
            return false;
    } else if (!parcela.equals(other.parcela))
        return false;
    if (qtddParcela == null) {
        if (other.qtddParcela != null)
            return false;
    } else if (!qtddParcela.equals(other.qtddParcela))
        return false;
    if (somaContaNomeDia == null) {
        if (other.somaContaNomeDia != null)
            return false;
    } else if (!somaContaNomeDia.equals(other.somaContaNomeDia))
        return false;
    if (somaContaNomeMesAno == null) {
        if (other.somaContaNomeMesAno != null)
            return false;
    } else if (!somaContaNomeMesAno.equals(other.somaContaNomeMesAno))
        return false;
    if (somaDia == null) {
        if (other.somaDia != null)
            return false;
    } else if (!somaDia.equals(other.somaDia))
        return false;
    if (somaMesAno == null) {
        if (other.somaMesAno != null)
            return false;
    } else if (!somaMesAno.equals(other.somaMesAno))
        return false;
    if (somaValorDia == null) {
        if (other.somaValorDia != null)
            return false;
    } else if (!somaValorDia.equals(other.somaValorDia))
        return false;
    if (somaValorMesAno == null) {
        if (other.somaValorMesAno != null)
            return false;
    } else if (!somaValorMesAno.equals(other.somaValorMesAno))
        return false;
    if (valorContaPaga == null) {
        if (other.valorContaPaga != null)
            return false;
    } else if (!valorContaPaga.equals(other.valorContaPaga))
        return false;
    return true;
}   
}

Class Connectionfactory

package br.com.restfull.factory;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mysql.jdbc.Statement;

/**
 * Classe responsável pela 
 * <br>Conexão com o db. 
 * @author ovoFrito
 * @since 05/07/2014
 * É isso aí... Trabalhando um ano e três meses nesse app!!!
 * @version 1.0 
 * */
public class ConnectionFactory {

public Connection getConnection(){
    try {
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());   
        return DriverManager.getConnection(
            "jdbc:mysql://127.0.0.1:3306/xxxxx", "xxxxx", "xxxxx"); // hehehe...
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

 public void closeConnection(Connection conn, Statement stmt, ResultSet rs) {
     try {
         close(conn, stmt, rs);
     } catch (Exception e) {
         System.out.println("Falha ao fechar conexao.");
         e.printStackTrace();
     }
    }

public void closeConnection(Connection conn, Statement stmt) {
     try {
         close(conn, stmt, null);
     } catch (Exception e) {
         System.out.println("Falha ao fechar conexao.");
         e.printStackTrace();
     }
}

private void close(Connection conn, Statement stmt, ResultSet rs) {

     try {
         if (rs != null) {
             rs.close();
         }
         if (stmt != null) {
             stmt.close();
         }
         if (conn != null) {
             conn.close();
         }
     } catch (Exception e) {
         System.out.println("Falha ao fechar conexao.");
         e.printStackTrace();
     }
    }   
}

If something is missing let me know! Thank you.

java android sql

  • Enable the option to print the query generated in Hibernate and share if possible.

  • When to use likein sql, try to leave all the characters in the same type. Try something like this. UPPER( campo ) like UPPER('?%')

  • This is the sql used, or an example? If it is the one used and you try to compare a word with a date, I find it difficult returns something...

  • Thanks Ricardo, Iiario and Gustavo. Iiario, UPPER did not change the algorithm. Gustavo, it was just an example, sorry there... hehehe... Taking Ricardo’s advice, I posted more information. Let’s try to find out what’s going on? We already know that the problem is not in the browser and also not in Android. It seems that is in the sql of the method getListNomeContaPagaMesAno, in line **somaContaNomeMesAno(? ,? ) as 'Soma' **. Only this is missing to finish this app here :( , I believe that more people tb are with this problem. Thanks....

1 answer

0

Solved!
A colleague indicated me in the mysql function the following: 

cf.contaFixa like (insert('%%', 1, 1, conta))

It really worked, but only in mysql, when searching for the java method only returns value if inserted only a letter, more than one letter returns 0.00, that is null. I studied a little more mysql and used it in the function: 

cf.contaFixa like concat('', conta,'%')

Although the original problem is in the mysql syntax and was solved I will continue searching, because in java presented problem in relation to Insert mysql function. Sorry for the jokes in the comments, it was just to break the extresse. I thank everyone and pt.stackoverflow.

Browser other questions tagged java android sql

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