Search between two dates columns using jpa

[Jrcastro Ribeiro]

Hi Guys!

I have two colunms: startDate and endDate, and i want to return all the objects between the startDate and the endDate

Using sql I can do the search, but with jpa I'm not getting due to syntax.

Thats my sql (working)

SELECT * FROM secomnew.ordemservico where startDate and endDate between '2015-10-05' and '2015-10-10'

JPQL (not working)

select u from Ordemservico u where u.startDate and u.endDate between '2015-10-05' and '2015-10-10'

And thats the method im using:

 public List<Ordemservico> findDates(Date date) {
        
        EntityManager em = getEntityManager();
        em.getTransaction().begin();
        TypedQuery<String> query = em.createQuery("select u from Ordemservico u where u.startDate between:date", String.class);
        query.setParameter("names", date);
        List layoutsSelected = query.getResultList();

        em.getTransaction().commit();

        return layoutsSelected;
    }

How can i do the same search using JPA?

[Roel De Nijs]

Jrcastro Ribeiro wrote:Thats my sql (working)

SELECT * FROM secomnew.ordemservico where startDate and endDate between '2015-10-05' and '2015-10-10'

On which database is that statement valid SQL?

I would expect to see something likeSELECT * FROM secomnew.ordemservico WHERE startDate BETWEEN '2015-10-05' AND '2015-10-10'And a similar condition if you want to check the endDate as well

[Roel De Nijs]

Jrcastro Ribeiro wrote:And thats the method im using:

You should of course use the correct syntax in your JPQL query. If you are using the BETWEEN operator, it should be something likeselect u from Ordemservico u where u.startDate between :beginDate and :endDateAnd you should set both parameters as well using setParameter. And you should of course use the appropriate parameter names as well! In your query you are using :date so you should query.setParameter("date", date); (and not using "names" as the first parameter)

[Jrcastro Ribeiro]

Roel De Nijs wrote:

Jrcastro Ribeiro wrote:Thats my sql (working)

SELECT * FROM secomnew.ordemservico where startDate and endDate between '2015-10-05' and '2015-10-10'

On which database is that statement valid SQL?

I would expect to see something likeSELECT * FROM secomnew.ordemservico WHERE startDate BETWEEN '2015-10-05' AND '2015-10-10'And a similar condition if you want to check the endDate as well

Using Mysql

But I cannot relate these two columns to show the objects that begin on startDate and ends at endDate ?

My idea was to have two text fields, one that would receive the end date and another with the start date, passing as parameter values, and then returning a list of objects with the relatives dates

[Roel De Nijs]

Jrcastro Ribeiro wrote:Using Mysql

Very hard to believe! The appropriate syntax of the BETWEEN operator is expr BETWEEN min AND max.

Jrcastro Ribeiro wrote:But I cannot relate these two columns to show the objects that begin on startDate and ends at endDate ?

Sure you can! But not with the BETWEEN operator. Just use startDate >= date1 AND endDate <= date2

[Jrcastro Ribeiro]

Roel De Nijs wrote:

Jrcastro Ribeiro wrote:Using Mysql

Very hard to believe! The appropriate syntax of the BETWEEN operator is expr BETWEEN min AND max.

Jrcastro Ribeiro wrote:But I cannot relate these two columns to show the objects that begin on startDate and ends at endDate ?

Sure you can! But not with the BETWEEN operator. Just use startDate >= date1 AND endDate <= date2

Here the SS, seem to be working

Something like that?

select u from Ordemservico u where startDate >= " + data1 + " AND endDate <= " + data2 + "

And date 1 and date 2 are the parameter




http://i.imgur.com/jdXPBcX.png

[Roel De Nijs]

Jrcastro Ribeiro wrote:Here the SS, seem to be working

Maybe you should verify your results a little bit better and with greater eye for detail before claiming it is working!

Jrcastro Ribeiro wrote:Something like that?

select u from Ordemservico u where startDate >= " + data1 + " AND endDate <= " + data2 + "

And date 1 and date 2 are the parameter

Yes! And depending if the dates should be included you use >= (included) or > (not included). But don't use string concatenation! Use parameter placeholders insteadselect u from Ordemservico u where startDate >= :date1 AND endDate <= :date2And then use TypedQuery<Ordemservico> query = em.createQuery(query, Ordemservico.class); query.setParameter("date1", date1); query.setParameter("date2", date2);

[Jrcastro Ribeiro]

Back with some problems =/

[35, 44] The identification variable 'startDate' cannot be used in conjunction with the >= operator.
[59, 66] The identification variable 'endDate' cannot be used in conjunction with the <= operator.
[35, 44] The identification variable 'startDate' is not defined in the FROM clause.
[59, 66] The identification variable 'endDate' is not defined in the FROM clause.

It's saying that i cannot use the operator with this type of variable, but why?

im using jDatachosser, And that's how I get the dates

  
        String startDate= ((JTextField) txtDatainicio.getDateEditor().getUiComponent()).getText();
        String endDate= ((JTextField) txtDataFim.getDateEditor().getUiComponent()).getText();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

public List<Ordemservico> findDates(String date1, String date2) {
        

        EntityManager em = getEntityManager();
        em.getTransaction().begin();
        TypedQuery<Ordemservico> query = em.createQuery("select u from Ordemservico u where startDate>= :date1 AND endDate<= :date2", 
                Ordemservico.class);
        query.setParameter("date1", date1);
        query.setParameter("date2", date2);
        List<Ordemservico> layoutsSelected = query.getResultList();

        em.getTransaction().commit();

        return layoutsSelected;
    }

public class Ordemservico implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "idordemservico")
    private Integer idordemservico;
    @Column(name = "identificadoros")
    private Integer identificadoros;
    @Column(name = "mes")
    private Integer mes;
    @Column(name = "ano")
    private Integer ano;
    @Column(name = "assunto")
    private String assunto;
    @Basic(optional = false)
    @Column(name = "publicitario")
    private String publicitario;
    @Basic(optional = false)
    @Column(name = "layoutos")
    private String layoutos;
    @Basic(optional = false)
    @Column(name = "startDate")
    @Temporal(TemporalType.DATE)
    private Date startDate;
    @Column(name = "endDate")
    @Temporal(TemporalType.DATE)
    private Date endDate;
    @Column(name = "dataevento")
    @Temporal(TemporalType.DATE)
    private Date dataevento;
    @Column(name = "producao")
    private String producao;
    @Lob
    @Column(name = "discricao")
    private String discricao;
    @Column(name = "responsavelos")
    private String responsavelos;
    @Column(name = "materiajornal")
    private String materiajornal;
    @ManyToMany(mappedBy = "ordemservicoList")
    private List<Ordemproducao> ordemproducaoList;
    @ManyToMany(mappedBy = "ordemservicoList")
    private List<Layoutsos> layoutsosList;
    @JoinTable(name = "ordemservico_has_usuario", joinColumns = {
        @JoinColumn(name = "ordemservico_idordemservico", referencedColumnName = "idordemservico")}, inverseJoinColumns = {
        @JoinColumn(name = "usuario_idusuario", referencedColumnName = "idusuario")})
    @ManyToMany
    private List<Usuario> usuarioList;
    @JoinColumn(name = "secretaria_idsecretaria", referencedColumnName = "idsecretaria")
    @ManyToOne(optional = false)
    private Secretaria secretariaIdsecretaria;
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "ordemservicoIdordemservico")
    private List<Materiajornal> materiajornalList;

    public Ordemservico() {
    }

    public Ordemservico(Integer idordemservico) {
        this.idordemservico = idordemservico;
    }

    public Ordemservico(Integer idordemservico, String publicitario, String layoutos, Date dataincio) {
        this.idordemservico = idordemservico;
        this.publicitario = publicitario;
        this.layoutos = layoutos;
        this.dataincio = dataincio;
    }

[Roel De Nijs]

Jrcastro Ribeiro wrote:It's saying that i cannot use the operator with this type of variable, but why?

Because date1 and date2 are strings! Should be java.util.Date of course (like in your entity mapping).

So your method signature should be

public List<Ordemservico> findDates(Date date1, Date date2) {
  // code
}

[Jrcastro Ribeiro]

Roel De Nijs wrote:

Jrcastro Ribeiro wrote:It's saying that i cannot use the operator with this type of variable, but why?

Because date1 and date2 are strings! Should be java.util.Date of course (like in your entity mapping).

So your method signature should be

public List<Ordemservico> findDates(Date date1, Date date2) {
  // code
}

Thank you, and sorry for taking your time
Yes, was forgetting the parse:

String startDate= ((JTextField) txtDatainicio.getDateEditor().getUiComponent()).getText();
String endDate= ((JTextField) txtDataFim.getDateEditor().getUiComponent()).getText();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

try {
            startDate= sdf.parse(dataIncio);
            endDate= sdf.parse(dataFim);
             listDates = new OrdemservicoJpaController().findDates(startDate, endDate);
      
                 System.out.println(listaDatas);
        } catch (ParseException ex) {
            Logger.getLogger(guiOrdemServicoPrincipal.class.getName()).log(Level.SEVERE, null, ex);
        }

 public List<Ordemservico> findDates(Date date1, Date date2) {
        

        EntityManager em = getEntityManager();
        em.getTransaction().begin();
        TypedQuery<Ordemservico> query = em.createQuery("select u from Ordemservico u where startDate>= :date1 AND endDate<= :date2", 
                Ordemservico.class);
        query.setParameter("date1", date1, TemporalType.DATE);
        query.setParameter("date2", date2, TemporalType.DATE);
        List<Ordemservico> layoutsSelected = query.getResultList();

        em.getTransaction().commit();

        return layoutsSelected;
    }

But same problem =/ , really don't know what is wrong.

As you can see at the ScreenShot, the database has the dates in the same format that I am sending



http://i.imgur.com/CNKODdt.png

[Roel De Nijs]

Jrcastro Ribeiro wrote:But same problem =/ , really don't know what is wrong.

Very weird! Should normally work without any problem. If you google for "jpql date less than", you'll see this solution is used each time.

Just to be sure: can you try qualifying the properties in your JPQL query and add space before comparison operators? So using u.startDate >= :date1 and u.endDate <= :date2.

If it doesn't work, please share the stack trace as well.

[Jrcastro Ribeiro]

Roel De Nijs wrote:

Jrcastro Ribeiro wrote:But same problem =/ , really don't know what is wrong.

Very weird! Should normally work without any problem. If you google for "jpql date less than", you'll see this solution is used each time.

Just to be sure: can you try qualifying the properties in your JPQL query and add space before comparison operators? So using u.startDate >= :date1 and u.endDate <= :date2.

If it doesn't work, please share the stack trace as well.

i dont know why I never see those little mistakes hehe, I was forgetting to put u.starDate, Now its working like a charm on the jtable.

[Roel De Nijs]

Jrcastro Ribeiro wrote:Now its working like a charm on the jtable.

Glad to hear it's working now!