distinct column records

[taraka ninu]

hi all,

1) am new to hibernate. can anybody tell me how to get Distinct records (in my case distinct column) using hibernate query???
ex : i want something like we get in SQL
Select distinct column1 from table

2) is it possible to get only specific columns like in SQL query ?
ex : i want something like
Select column1 from table

Thanks

[Paul Sturrock]

Both are easy to do:

Select distinct column1 from table

in SQL would be something like:

select distinct foo.column1 from Object foo

You don't need to select a single column in your query - remember you are querying against the Object not the table, so the Object is always completely populated, and you choose which properties you are interested in.

[Sathya Srinivasan]

Originally posted by taraka ninu:

1) am new to hibernate. can anybody tell me how to get Distinct records (in my case distinct column) using hibernate query???
ex : i want something like we get in SQL
Select distinct column1 from table

If you are using the Criteria object, a more appropriate way to do it is

Criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

If you are using HQL or SQL, you can simply specify "distinct" in your query as suggested.

Originally posted by taraka ninu:

2) is it possible to get only specific columns like in SQL query ?
ex : i want something like
Select column1 from table

Hibernate (well, Gavin King) says that the time required to get one column as opposed to 10 columns for a row is not significant. So, Hibernate chooses to retrieve all the columns in a row.

If you are going to use only a subset of columns of a table in your application all the time, you can always map only those columns in your HBM file.

[David Harkness]

Originally posted by Sathya Srinivasan:
Hibernate (well, Gavin King) says that the time required to get one column as opposed to 10 columns for a row is not significant.

In general this is true. Compared to the cost of generating the SQL, sending the query to the database, having the database parse and execute the query, and pulling back the results, whether the results consist of 40 or 400 bytes will make no practicable difference.

I say in general because some people create tables of 400 columns. The other case is if the table contains CLOB or BLOB fields since they require more round trips to retrieve as they aren't returned in the result set.