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 ?

DharmanBot · Accepted Answer · 2022-04-29 09:29:25Z

2

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 );

edited Apr 29, 2022 at 9:29

DharmanBot

1,0742 gold badges7 silver badges10 bronze badges

answered Apr 29, 2022 at 9:20

Diallo Mamadou Pathé

413 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CodeSun Over a year ago

i am interested for a way with JpaRepository with method name query. Just for curiosity :).

Diallo Mamadou Pathé Over a year ago

public interface CurrencyRepository extends JpaRepository<Currency, Long> { Currency findByHireDateBetween(Date start, Date end); }

Collectives™ on Stack Overflow

Get user data between two date in Hibernate Java

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related