Fetch the data between Two date in Hibernate Jave with MySQL?

Question

Am new to Java server side and i creating JSON Links using Jersey library. In my project am Using Hibernate to Get the data from MySQL database. I want to fetch the data between two dates in MySQL.But Query is Not Working properly.

MySQL Table

CREATE TABLE `order_headers` (
 `bill_number` int(10) NOT NULL AUTO_INCREMENT,
 `order_number` varchar(100) DEFAULT NULL,
 `table_number` varchar(20) NOT NULL,
 `payment_type` varchar(20) DEFAULT NULL,
 `discount` double NOT NULL,
 `payment_status` varchar(30) DEFAULT NULL,
 `order_status` varchar(20) DEFAULT NULL,
 `order_date` date NOT NULL,
 `grand_total` decimal(7,2) NOT NULL,
 `upt_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`bill_number`)
)

Depends upon the order_date column in the table to fetch data.

Hibernate Annotation Class

@Entity
@Table(name = "order_headers")
public class Order_headers {

    @Id @GeneratedValue
    @Column(name = "bill_number")
    private String bill_number;

    @Column(name = "order_number")
    private String order_number;

    @Column(name = "order_value")
    private double order_value;

    //format YYYY-mm-dd
    @Column(name = "order_date")
    private String order_date;

    //All other Variables and Getter Setters

}

Query Used to fetch data in JAVA

//Query i used
/*FROM Order_headers oh WHERE oh.merchant_code = :merchant_code AND oh.branch_code = :branch_code AND DATE_FORMAT(oh.order_date,'%d-%m-%Y') >= :from_date AND DATE_FORMAT(oh.order_date,'%d-%m-%Y') <= :to_date ORDER BY DATE_FORMAT(oh.order_date,'%d-%m-%Y') ASC*/

Query query = session.createQuery("FROM Order_headers oh WHERE oh.merchant_code = :merchant_code AND oh.branch_code = :branch_code AND DATE_FORMAT(oh.order_date,'%d-%m-%Y') >= :from_date AND DATE_FORMAT(oh.order_date,'%d-%m-%Y') <= :to_date ORDER BY DATE_FORMAT(oh.order_date,'%d-%m-%Y') ASC");
query.setParameter( "merchant_code", merchant_code );
query.setParameter( "branch_code", branch_code );
query.setParameter( "from_date", from_date );
query.setParameter( "to_date", to_date );

In table have data till 22-06-2015

I check with two condition:

When i pass these parameters merchant_code=1&branch_code=1&from_date=10-02-2015&to_date=16-07-2015 it fetch all the data between two dates.
When i pass these parameters merchant_code=1&branch_code=1&from_date=10-02-2015&to_date=15-07-2015 this not even a single data.When i give date from 01-07-2015 to 15-07-2015 in to_date parameter same problem continues.

Am search it in Google all of them give same query what i used,but i am not able find out the issue.Can any one know help me to solve this issue.

Nitesh Soomani · Accepted Answer · 2015-06-24 06:59:22Z

3

Try to use Between

SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
String fromDate= format.parse(from_date);
String toDate= format.parse(to_date);


Query query = session.createQuery("FROM Order_headers oh WHERE oh.merchant_code = :merchant_code AND oh.branch_code = :branch_code AND oh.order_date BETWEEN fromDate AND toDate");

query.setParameter( "fromDate", fromDate);
query.setParameter( "toDate", toDate );

edited Jun 24, 2015 at 6:59

answered Jun 24, 2015 at 6:22

Nitesh Soomani

6522 gold badges6 silver badges13 bronze badges

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

5 Comments

Yugesh Over a year ago

fromDate is Date or String.

Yugesh Over a year ago

String fromDate= format.parse(from_date); Shows error as Type Mismatch : cannot convert from Date to String.

Nitesh Soomani Over a year ago

Ok keep your code same as it was and try BETWEEN function

Yugesh Over a year ago

After using BETWEEN also same problem persist.

Yugesh Over a year ago

I didn't give any format.Default one format like 2015-06-24

Collectives™ on Stack Overflow

Fetch the data between Two date in Hibernate Jave with MySQL?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related