Converting mysql query into hibernate query

Question

I have a MySQL query, and I want to convert this query in to Hibernate Query. This is what I've tried so far.

userInQuery = "SELECT * FROM log where userName in(";
userCountQuery = "SELECT count(*) FROM log where userName in(";

for (int i = 0; i < users.length; i++) {
    userInQuery += "'" + users[i] + "',";
    userCountQuery += "'" + users[i] + "',";
}

userInQuery = userInQuery.substring(0, userInQuery.lastIndexOf(","))+ ") and
    systemdate  >= STR_TO_DATE('" + fromDt+ "' ,'%Y-%m-%d') and systemdate  <=
    STR_TO_DATE('"+ toDate + "','%Y-%m-%d')";

userCountQuery = userCountQuery.substring(0, userCountQuery.lastIndexOf(","))+ ")
    and systemdate  >= STR_TO_DATE('"+ fromDt+ "' ,'%Y-%m-%d') and systemdate  <=
    STR_TO_DATE('"+ toDate + "','%Y-%m-%d')";

//System.out.println("Final userInQuery : " + userInQuery);

psmt = conn.prepareStatement(userInQuery);

rscount = stmt.executeQuery(userCountQuery);

Give your MySQL query to get clear idea about what u r asking. — OO7
– OO7, Commented Jul 11, 2014 at 5:28
@user000324 I want to ask how in query and STR_TO_DATE in hibernate — user3441816
– user3441816, Commented Jul 11, 2014 at 5:38

OO7 · Accepted Answer · 2014-07-11 07:26:01Z

1

Using Hibernate Query :

SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-YYYY");
String fromDate = "11-07-2014";
Date fromDt = formatter.parse(fromDate);
String toDate = "11-07-2014";
Date toDt = formatter.parse(toDate);

String userInQuery = "from log where userName in (:userList) and systemdate between :fromDate and :toDate";
Query q = s.createQuery(userInQuery);
q.setParameterList("usersList", users);
q.setParameter("fromDate", fromDt);
q.setParameter("toDate", toDt);
q.list();

Note : Here :userList, :fromDate & :toDate are query parameters.

Using Hibernate Criteria :

List<?> users = //get data from either a query or criteria
criteria.add(Restrictions.and(Restrictions.in("userList", users),Restrictions.between("dateField", new SimpleDateFormat("dd-MM-YYYY").parse(fromDate), new SimpleDateFormat("dd-MM-YYYY").parse(toDate)));

OR

criteria.add(Restrictions.in("userList", users));
criteria.add(Restrictions.ge("systemDate", fromDt));
criteria.add(Restrictions.lt("systemDate", toDt));

answered Jul 11, 2014 at 7:26

OO7

2,8131 gold badge24 silver badges33 bronze badges

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

4 Comments

user3441816 Over a year ago

thanks for reply.but I am getting this exception "13:38:57,282 ERROR [STDERR] java.lang.IllegalArgumentException: Illegal pattern character 'Y' 13:38:57,285 ERROR [STDERR] at java.text.SimpleDateFormat.compile(SimpleDateFormat.java:752) 13:38:57,286 ERROR [STDERR] at java.text.SimpleDateFormat.initialize(SimpleDateFormat.java:559)"

OO7 Over a year ago

If u r using jdk 1.6 then use (lowercase) y SimpleDateFormat - JDK1.6. With JDK 1.7 u can use the above pattern SimpleDateFormat - JDK1.7

user3441816 Over a year ago

from Log where userName in (:userList) and systemdate between :fromDate and :toDate. this query returning null?

OO7 Over a year ago

Have u set parameters properly ?

Ankur Singhal · Accepted Answer · 2014-07-11 05:32:56Z

0

Create a list of user id's as in your case, and pass that list to below query.

Something like below

Select x FROM X x WHERE x.y in :yList

answered Jul 11, 2014 at 5:32

Ankur Singhal

26.1k17 gold badges92 silver badges129 bronze badges

Collectives™ on Stack Overflow

Converting mysql query into hibernate query

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related