Search by dates

Question

Search by dates

Navigation

#1 by (3 votes)

2

I have a screen and on this screen I have a TextField that is populated 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 until there is quiet, but now I am not I was 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() + "%");

In my button that searches the dates I send the object and the code as a parameter. The error is as follows:

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?

mysql sql java

asked by anonymous 29.05.2015 / 21:12

1 answer

Using WebService + SQLite Photo saved in Directory does not appear in Solution Explorer

score 3 · Accepted Answer

You did not use single quotes to use the like, try changing the contents of the table variable to the one 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 '?' ");

You can also change in the parameter:

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