Like does not return data

0

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

Follow 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;

Fixed Account Table:

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

Contapagar table:

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

ClassDao:

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 was missing let me know! Thank you.

    
asked by anonymous 29.10.2015 / 02:15

1 answer

0

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

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

It really worked, but only in the test in mysql, in the java method search only returns value if only one letter is inserted, more than one

strong> letter returns 0.00, that is, null. I studied mysql a bit more and used it in the function:

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

Although the original problem is in the mysql syntax and was resolved I will continue to search, because in java it presented problem with the insert of the mysql function. Sorry for the jokes in the comments, it was just to break the stress. Thanks to everyone and to the .stackoverflow.

    
30.10.2015 / 02:19