CODEWITHSUNDEEP
javamysqlsqlhibernatejpa
S Singh
S Singh

Reputation: 1473

JPA query equivalent to mysql query

Below is mysql query which is working fine and giving me expected results on mysql console.

select * from omni_main as t where t.date_time BETWEEN STR_TO_DATE(CONCAT('2011', '08', '01'),'%Y%m%d') AND LAST_DAY(STR_TO_DATE(CONCAT('2012', '08','01'), '%Y%m%d')) group by year(date_time),month(date_time)

I need its JPA equivalent query. Below is what I am trying but its returning nothing.

String queryStr = "select * from OmniMainEntity o where o.dateTime BETWEEN STR_TO_DATE(CONCAT('"+fromYear+"', '"+fromMonth+"','01'), '%Y%m%d') AND " 
               +"LAST_DAY(STR_TO_DATE(CONCAT('"+toYear+"', '"+toMonth+"','01'), '%Y%m%d'))";

Query query = manager.createQuery(queryStr);
System.out.println("Result Size: "+query.getResultList().size());

Here fromYear, fromMonth, toYear, toMonth are method parameters using in creating queryStr.

Please suggest where I may wrong!

Any other way to achieve goal is also welcome!

Upvotes: 1

Views: 2965

Answers (2)

Baggio
Baggio

Reputation: 56

As you are using JPA Query, it would be better to not use database-specified sql function, such as STR_TO_DATE.

You can have a try by this way.(A Hibernate way, JPA should be similiar):

First, you can parse a java.util.Date object from "fromYear" and "fromMonth" like below:

DateFormat df = new SimpleDateFormat("yyyyMMdd");
Date startDate = df.parse(fromYear + "" + fromMonth + "01");
Date endDate = df.parse(.....);

Then, set them into the JPA query.

String queryStr = "select * from OmniMainEntity o where o.dateTime BETWEEN :startDate AND :endDate)"; // The query now changed to database independent
Query query = manager.createQuery(queryStr);
query.setDate("startDate", startDate);
query.setDate("endDate", endDate);

At last, doing the search:

System.out.println("Result Size: "+query.getResultList().size());

Upvotes: 1

chrylis -cautiouslyoptimistic-
chrylis -cautiouslyoptimistic-

Reputation: 77177

Your query doesn't have a verb in it. You probably want SELECT in there:

SELECT o FROM OmniMainEntity o WHERE...

Also, you should be using parameterized and typed queries, and it's usual to use short names (o instead of omniMainEnt) to make your queries readable.

Upvotes: 0

Related Questions