Search by dates

Question

I have a screen and on this screen I have a Textfield that is filled by a date in the format dd/mm/yyyy and then I convert that date to yyyy-mm-dd which is the format of java.sql.date so far quiet, but now I’m not able to adjust my Sql to search the dates. This is an excerpt from my method:

public List<Arquivo> pegaArquivosPorPrestador(Long codigo, Arquivo a) throws Exception {
    System.out.println("DATA no pes: "+a.getDataGerado());
    Class.forName("com.mysql.jdbc.Driver");
    conn = DriverManager.getConnection("jdbc:mysql://localhost/db_prestadores", "root", "");
    consulta = (com.mysql.jdbc.Statement) conn.createStatement();
    tabela = consulta.executeQuery("SELECT a.cod_arquivo, a.nome_arquivo, a.caminho_arquivo, a.ext_arquivo, a.data_ger_arquivo, a.tbl_prestadores_cod_prestador, a.tbl_usuario_cod_usuario, p.email_prestador "
            + "FROM tbl_arquivos a "
            + "INNER JOIN  tbl_prestadores p ON a.tbl_prestadores_cod_prestador = p.cod_prestador "
            + "WHERE  p.cod_prestador = '" + codigo + "'OR a.data_ger_arquivo LIKE ? ");

    //List lista = new ArrayList();
    List<Arquivo> lista = new ArrayList<Arquivo>();
    PreparedStatement comando = conn.prepareStatement(tabela.toString());
    comando.setString(1, "%" + a.getDataGerado() + "%");

On my button that searches the dates I send the object and the code as parameter. The error is the following:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1

How can I fix SQL?

Answer 1

You did not use simple quotes to use the like, try changing the content of the table variable to the proposed below:

tabela = consulta.executeQuery("SELECT a.cod_arquivo, a.nome_arquivo, a.caminho_arquivo, a.ext_arquivo, a.data_ger_arquivo, a.tbl_prestadores_cod_prestador, a.tbl_usuario_cod_usuario, p.email_prestador "
            + "FROM tbl_arquivos a "
            + "INNER JOIN  tbl_prestadores p ON a.tbl_prestadores_cod_prestador = p.cod_prestador "
            + "WHERE  p.cod_prestador = '" + codigo + "'OR a.data_ger_arquivo LIKE '?' ");

It is also possible to change the parameter:

comando.setString(1, "'%" + a.getDataGerado() + "%'");