Get user data between two date in Hibernate Java

Question

i have an hibernate entity called user.I want to get the list of users who are between two dates a date interval. Example, I have a date D1 and I want to know the people hired between my hiDate and my depDate. Here is the mapping of the hibernate file of my user entity

<class name="com.bam.model.User" table="USER">
        <composite-id class="com.bam.model.UserId" name="id">
            <key-property name="idUser" type="java.lang.String">
                <column name="ID_USER" not-null="true" precision="0" scale="30" sql-type="varchar"/>
            </key-property>
            <key-property name="idCompany" type="java.lang.String">
                <column name="ID_COMPANY" not-null="true" precision="0" scale="30" sql-type="varchar"/>
            </key-property>
        </composite-id> 
        <property name="gender" type="java.lang.String">
            <column name="GENDER" not-null="false" precision="0" scale="4" sql-type="varchar"/>
        </property>
        <property name="firstname" type="java.lang.String">
            <column name="FIRSTNAME" not-null="false" precision="0" scale="100" sql-type="varchar"/>
        </property>
        <property name="lastname" type="java.lang.String">
            <column name="LASTNAME" not-null="false" precision="0" scale="100" sql-type="varchar"/>
        </property>

        
</class>

Here is also the class of my user entity.

@XmlRootElement(name = "user")
public class User implements Serializable {

    @XmlElement(name = "id")
    private UserId id;

    @XmlElement(name = "gender")
    private String gender;

    @XmlElement(name = "firstName")
    private String firstname;

    @XmlElement(name = "lastName")
    private String lastname;
}

The query that I will write with the hql language is this one :

Query query = session.createQuery("SELECT * FROM USER us oh WHERE   DATE_FORMAT(us.HI_DATE,'%dd-%mm-%YYYY') <= :from_date <= DATE_FORMAT(us.DEP_DATE'%dd-%mm-%YYYY') ");
query.setParameter( "from_date ", from_date );

but it doesn't work, any idea ?

Answer 1

you can try this.

Do you need to format the dates?

Query query = session.createQuery("FROM USER us WHERE :from_date between us.hiDate and us.depDate");
query.setParameter( "from_date ", from_date );