How to compare Date to Mysql field

Question

I’m a beginner in Java and I have a table in Mysql that has a date field and the dates are stored in the format yyyy-mm-dd.

Now I need to recover only records whose value of the date field is greater than or equal to today’s date.

I’m trying to do the below, but it’s not working:

Date dataHoje = new Date();
Query consulta = em.createQuery("SELECT c FROM Consulta c WHERE c.dtConsulta >= dataHoje");

Someone could help out?

I’m sorry I didn’t post the code in full, but it goes below:

public List<Consulta> listarDatasLivresParaMarcarConsultas(int mes, int ano){

    em.getTransaction().begin();

    Date dataHoje = new Date();

    Query consulta = em.createQuery("select c from Consulta c " +
            "left outer join fetch c.terapeuta t " + 
            "left outer join fetch c.cliente cl " + 
            "SUBSTRING(c.dtConsulta, 1, 4) = ?1 AND " + 
            "SUBSTRING(c.dtConsulta, 6, 2) = ?2 AND " +
            "c.dtConsulta >= dataHoje AND " +
            "c.cliente = null AND " +
            "c.statusConsulta = 'C'");

        consulta.setParameter(1, ano);
        consulta.setParameter(2, mes);

        List<Consulta> consultas = consulta.getResultList();

        em.getTransaction().commit();
        emf.close();

        return consultas;
    }

User chooses month and year through Jmonthchooser and Jyearchooser components, which return two integers respectively.

Thanks for any help!!! Augusto

Answer 1

You can do this in the SQL query itself, example:

SELECT * FROM consulta t
WHERE t.dtconsulta >= curdate();

See here a minimalist example