Hi,

In MySQL, I can do this:



where mytable has a column named timestamp of type datetime, and I get back the count of the unique hours in my table (24 if every hour of the day has at least one record). But I cannot figure out how to do that in Hibernate (Hibernate-Version: 3.1.3):



produces the error:



where column 32 is the '(' after the substring. The statement works (but doesn't do what I need it to do...) without the 'substring' function:



and actually getting the data works with the substring:



gives me the rows of data.

Not sure if it is related, but I cannot coerce those results into a class:



produces an error regarding the 'distinct' keyword.

My suspicion is that I am missing something in the syntax for both problems. The first issue is a bit of a show stopper; the second one ('new MyClass(...)') I can work around by simply getting the default List object and then instantiating and populating my MyClass instances myself.

Can anyone point out the error of my ways, or suggest an alternative way of getting the count?

Thanks,
Doug.

Hi,
Try this.

sql = "select count(distinct substring(timestamp,12,2)) from MyTableName";
Query query = session.createSQLQuery(sql);

We need to use table name ( not entity name ) in SQL query.

-------------------------------------------
Rate the reply if you find it helpful

Hi Pramodkp

Thanks for the reply.

SQLQuery works - but means I lose the 'hibernate' layer for all of my queries. The code I'm writing is part of a generic library, so the queries which use it would have to use the raw table names and the raw column names, rather than the abstractions that hibernate provides. The actual query is more complex that I have shown (but that doesn't affect the problem I have illustrated), so the where clause etc. would also need the raw names. The rest of the code base uses the hibernate abstractions, so having a mix of the two isn't very attractive from a development or maintenance viewpoint.

I have considered adding a layer inside the library that would parse the query and map the hibernate names on to raw names, but would rather not do that if I don't have to - hibernate should be lightening my work load, not increasing it. Have not looked far enough to see if hibernate has any functionality that would make that easier to do, though.

The other worry with SQLQuery is whether it observes any caching/transaction management that hibernate might be doing - for example, if the application has started a transaction and updated some records but has not yet committed, does SQLQuery see the uncommitted data or does it see what is committed in the database? I have seen some comments that going to the JDBC layer with preparedStatement etc. would result in uncommitted changes being ignored, while Query and Criteria would observe those uncommitted changes, but have not seen anything about SQLQuery. Since SQLQuery is part of hibernate (vs preparedStatement being underneath hibernate) I would assume it would observe transactions, but would need to confirm that.

Thanks,
Doug.

Here is an example to count distinct records from a table. Let's say Product has entry for Item and a seller who sells that item. You want to find the count of distinct items for a given seller, here is the hibernate query:


Integer itemCount = (Integer) session.createCriteria(Product.class) .setProjection(Projections.countDistinct("item"))
.add(Restrictions.eq("seller", _seller))
.uniqueResult();

Thanks
Shyam

Hi Sjhyam,

Yes, that works for simple queries. But... I want to count distinct sets of substrings of the values in a column: i.e. if 'item' were an encoded string where the first 4 characters were the category of the item (maybe toys vs plumbing vs lumber) and the rest of the characters in 'item' was the stock number, I would like to find the count of the unique item categories.

This one could be solved by changing the database schema so the Product has two fields (itemCategory and stockNumber). This is not always feasible though. If the DB is part of a larger system, changing the schema could break lots of things. In my case, I want to process a date/time in various ways, and I don't want to have every possible combination of the date elements as separate columns in my table (a day column, a month column, a year column, a day and month column, an hour column, etc.) - I want to be able to create arbitrary queries for how many hours of all days have data, how many months of all years have data, etc.

Thanks,
Doug.