是Java服务器端的新手,我使用Jersey库创建JSON链接。在我的项目中,正在使用Hibernate从MySQL数据库获取数据。我想在MySQL中的两个日期之间获取数据。但是查询无法正常工作。
MySQL表
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`)
)
取决于表中的order_date列以获取数据。
休眠注释类
@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
}
查询用于在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 );
表中有数据,直到 22-06-2015
我检查两个条件:
merchant_code=1&branch_code=1&from_date=10-02-2015&to_date=16-07-2015
它将获取两个日期之间的所有数据。merchant_code=1&branch_code=1&from_date=10-02-2015&to_date=15-07-2015
甚至没有单个数据。当我01-07-2015 to 15-07-2015
在to_date参数中给出日期时,同样的问题仍然存在。我在Google上搜索它时,所有人都给出了相同的查询,但我无法找出问题。有人能帮助我解决此问题吗?
尝试使用之间
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 );
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句