CODEWITHSUNDEEP
mysqlhibernate
hguser
hguser

Reputation: 36058

hibernate query nothing in a time range where data exist

Hi: I meet a very strange problem when I use hibernate to do some querying:

This is the core codes:

    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    start = sdf.parse("2011-02-22 10:00:00");
    end = sdf.parse("2011-02-22 16:00:00");


    Query q = sess.createQuery("select log.uri,count(log.uri) as num from LogEntry log where log.time between ? and ? group by log.uri order by num desc")
            .setDate(0, start).setDate(1, end);
    System.out.println(q.list()); //here I got 0 result,

    System.out.println("===== Make the start date smaller =========");
    start=sdf.parse("2011-02-21 10:00:00");
    q = sess.createQuery("select log.uri,count(log.uri) as num from LogEntry log where log.time between ? and ? group by log.uri order by num desc")
            .setDate(0, start).setDate(1, end);
    System.out.println(q.list()); //here I got 26 result

    System.out.println("===== Make the end date bigger =========");
    end=sdf.parse("2011-02-23 16:00:00");
    q = sess.createQuery("select log.uri,count(log.uri) as num from LogEntry log where log.time between ? and ? group by log.uri order by num desc")
            .setDate(0, start).setDate(1, end); //here I got 39 result
    System.out.println(q.list());

From the above ,it seems that in the db there is

00 results between "2011-02-22 10:00:00" and "2011-02-22 16:00:00",but there are

23 results between "2011-02-21 10:00:00" and "2011-02-23 16:00:00",

so this means there must be some result between "2011-02-22 16:00:00" and "2011-02-23 16:00:00",but when I do this test, the current time is "2011-02-22 14:45:00"!!!

Also,I have did a test in the db too,

select uri,count(uri) as num from xxx_log where time between 20110222100000 and 20110222160000 group by uri order by num desc

I got 23 rows in set.

So I wonder why it is so surprising?

Is there anything wrong in my codes?

BTW,I use mysql 5.1.

UPDATE:

This is another test code,first use hibernate ,then use the native sql:

@Test
public void testSQLAndHibernate() throws ParseException {
    start = sdf.parse("2011-02-22 10:00:00");
    end = sdf.parse("2011-02-22 16:00:00");
    // use hibernate
    Session sess = HibernateUtil.getSessionFactory().getCurrentSession();
    sess.beginTransaction();

    Query q = sess
            .createQuery(
                    "select log.uri,count(log.uri) as num from LogEntry log where log.time between ? and ? group by log.uri order by num desc")
            .setDate(0, start).setDate(1, end);
    System.out.println("get "+q.list().size()+" results by hibernate");

    System.out.println("++++++++++++++");
    // use sql
    SimpleDateFormat sdf_sql = new SimpleDateFormat("yyyyMMddHHmmss");
    String sql = "select uri,count(uri) as num from t_log where time between "
            + sdf_sql.format(start) + " and " + sdf_sql.format(end)
            + " group by uri order by num desc";
    try {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db",
                "root", "0000");
        Statement state = conn.createStatement();
        ResultSet rs = state.executeQuery(sql);
        int i=0;
        while (rs.next()) {
            //System.out.println(rs.getString(1) + " " + rs.getLong(2));
            i++;
        }
        System.out.println("get "+i+" results by sql query");
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

OutPut:

get 0 results by hibernate
++++++++++++++
get 24 results by sql query

Also from the hibernat log I get :

binding parameter [1] as [DATE] - Tue Feb 22 10:00:00 CST 2011
binding parameter [2] as [DATE] - Tue Feb 22 16:00:00 CST 2011

It seems that the start and end date are correct

Upvotes: 1

Views: 1246

Answers (2)

axtavt
axtavt

Reputation: 242726

Note that you pass parameters via setDate(), therefore they are interpreted as SQL type DATE. In the case of native query you pass dates as date literals, and I guess in that case they are interpreted as TIMESTAMPs. It's the only difference between HQL and native queries.

Therefore try to pass parameters via setTimestamp().

Upvotes: 1

gnur
gnur

Reputation: 4733

What is the result when you do System.out.println(end) and System.out.println(start) ? Are they the correct format?

Mysql can generate very weird results if the time format is bit off from what is expected.

Upvotes: 1

Related Questions