将mysql查询转换为hibernate查询 [英] Converting mysql query into hibernate query

问题描述

我有一个MySQL查询，我想将此查询转换为Hibernate Query。这是我到目前为止所尝试的。

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

推荐答案

使用Hibernate查询：

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

注意：此处：userList，：fromDate& ：toDate是查询参数。

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

使用Hibernate标准：

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

这篇关于将mysql查询转换为hibernate查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！