Hibenate query to retrieve one column with where clause

[Partheban Udayakumar]

Hi all,

I am fairly new to hibernate. I want to retrieve email from the customer table for a certain customer.
(ie) Select email from CustomerManagementDTO WHERE id=id;

I tried this

Criteria criteria = getSessionFactory().getCurrentSession()
.createCriteria(CustomerManagementDTO.class);
criteria.add(Restrictions.gt("id", id)).setProjection(
Projections.property("email"));
List<CustomerManagementDTO> list = criteria.list();

but the list is null. Where have I gone wrong or should I use something else

[Dave Tolls]

That's selecting where the db id > the given id.

And I'm not entirely sure what the Projection is for?
Why have you put that in?

[Roel De Nijs]

Dave Tolls wrote:That's selecting where the db id > the given id.

Agreed! The eq method should be used

Restrictions.eq("id", id)

Dave Tolls wrote:And I'm not entirely sure what the Projection is for?
Why have you put that in?

Hibernate Projections are used in order to query only a subset of the attributes of an entity or group of entities you're querying with Criteria. So it's probably added because the OP only wants to select the email. You can also use Projections to specify distinct clauses and aggregate functions like max, sum and so on. But that's not the case in this example

[Dave Tolls]

Roel De Nijs wrote:Hibernate Projections are used in order to query only a subset of the attributes of an entity or group of entities you're querying with Criteria. So it's probably added because the OP only wants to select the email. You can also use Projections to specify distinct clauses and aggregate functions like max, sum and so on. But that's not the case in this example

So the rest of the data in those returned DTOs is null then? If not then I don't see what's gained by telling Hibernate "I'm only interested in the email".
I probably ought to read up on this stuff.

[Roel De Nijs]

Dave Tolls wrote:So the rest of the data in those returned DTOs is null then? If not then I don't see what's gained by telling Hibernate "I'm only interested in the email".

I think it is. I have never used this feature myself.

[Tim Holloway]

Hibernate JPA can retrieve a single column or selected set of columns, but they are returned as scalar column mappings. Check out the @SqlResultSetMapping annotation.

Note that this is a feature of JPA. If you're using old-style (non-JPA) Hibernate then you'd have to look for a comparable mechanism.

[Partheban Udayakumar]

Roel De Nijs wrote:Hibernate Projections are used in order to query only a subset of the attributes of an entity or group of entities you're querying with Criteria. So it's probably added because the OP only wants to select the email. You can also use Projections to specify distinct clauses and aggregate functions like max, sum and so on. But that's not the case in this example

So this means what I have used is correct? But I am getting the empty list for the existing email. I tried unique result too, it too returns null.

Tim Holloway wrote:Hibernate JPA can retrieve a single column or selected set of columns, but they are returned as scalar column mappings. Check out the @SqlResultSetMapping annotation.

Note that this is a feature of JPA. If you're using old-style (non-JPA) Hibernate then you'd have to look for a comparable mechanism.

I am not using JPA. I am just starting hibernate now. So I decided to learn all basics first.


PS: For those who are confused why I am retrieving email, I am using it to check for duplicate users in my db.

[Dave Tolls]

Partheban Udayakumar wrote:
PS: For those who are confused why I am retrieving email, I am using it to check for duplicate users in my db.

If the email column is UNIQUE then you won't have duplicate users.
If this is when you are adding a new user then I would let the database do the work. That is, try and insert the user and, if it throws a constraint error, then inform the user they already have an account with that email address.

[Roel De Nijs]

Partheban Udayakumar wrote:So this means what I have used is correct? But I am getting the empty list for the existing email. I tried unique result too, it too returns null.

And you don't see any stack trace at runtime? You should go back to basics and first create a Criteria object which returns a list of all users in your database. When that's working, you add the necessary changes to return only the user with a given id. And when that's working you can make any other changes. Using such an approach it's much easier to pinpoint the possible issue you are facing.

Partheban Udayakumar wrote:PS: For those who are confused why I am retrieving email, I am using it to check for duplicate users in my db.

I have two remarks:
1/ If the email column should only contain unique values, you should add a UNIQUE constraint to this column. Then it's impossible to insert duplicate values (even from outside your application). And if you try to insert a duplicate value from your application, a ConstraintViolationException will be thrown which you can act upon appropriately.
2/ I wonder how the query from your initial post (Select email from CustomerManagementDTO WHERE id=id;) will help you to determine duplicate emails (users) in the database as it just returns the email for a given user.

[Paul Clapham]

Roel De Nijs wrote:2/ I wonder how the query from your initial post (Select email from CustomerManagementDTO WHERE id=id;) will help you to determine duplicate emails (users) in the database as it just returns the email for a given user.

I keep getting the feeling I'm missing something. Because to me it looks like that query returns the e-mail addresses of all users. Am I wrong? If so why?

[Roel De Nijs]

Paul Clapham wrote:

Roel De Nijs wrote:2/ I wonder how the query from your initial post (Select email from CustomerManagementDTO WHERE id=id;) will help you to determine duplicate emails (users) in the database as it just returns the email for a given user.

I keep getting the feeling I'm missing something. Because to me it looks like that query returns the e-mail addresses of all users. Am I wrong? If so why?

I assumed that one of the "id" occurences in the query will be replaced with an actual value (and thus is not just a column name). And from the code to create the Criteria object, that seems to be plausible. But if both "id" occurences are column names, the WHERE clause should be omitted and all emails will be returned.

[Partheban Udayakumar]

Dave Tolls wrote:If the email column is UNIQUE then you won't have duplicate users.

Roel De Nijs wrote:If the email column should only contain unique values, you should add a UNIQUE constraint to this column.

I feel like a dumbo now. I am actually using email retrieval for checking whether email exists in the server. I am using UNIQUE in database.
What I do is I use email to get the entire customer object and use the name from it to send mail in html template.

Roel De Nijs wrote:I wonder how the query from your initial post (Select email from CustomerManagementDTO WHERE id=id;) will help you to determine duplicate emails (users) in the database as it just returns the email for a given user

Initially, before knowing about UNIQUE, what I used to do is get the email and check the entered and retrieved emails in my java file.

Roel De Nijs wrote:You should go back to basics and first create a Criteria object which returns a list of all users in your database. When that's working, you add the necessary changes to return only the user with a given id. And when that's working you can make any other changes. Using such an approach it's much easier to pinpoint the possible issue you are facing.

You are absolutely correct I will check it from basics.

[Roel De Nijs]

Partheban Udayakumar wrote:I feel like a dumbo now. I am actually using email retrieval for checking whether email exists in the server. I am using UNIQUE in database.

Partheban Udayakumar wrote:Initially, before knowing about UNIQUE, what I used to do is get the email and check the entered and retrieved emails in my java file.

If you want you can use a query to check if the entered email is unique or not. But you should not be comparing the email with all retrieved emails in Java. Your database can count all occurences of this email address in the user table. If the count is greater than 0, the email address is not unique and you should show an error message. And also don't forget that upper and lower case could matter This query will be executed very quickly, so you can give feedback to the user almost immediately (if needed).
But you still need the UNIQUE constraint to protect data integrity! Between running the (very fast) email verification query and actually inserting the new user, another user might already be inserted with this email address.

[Partheban Udayakumar]

Roel De Nijs wrote: If you want you can use a query to check if the entered email is unique or not. But you should not be comparing the email with all retrieved emails in Java. Your database can count all occurences of this email address in the user table. If the count is greater than 0, the email address is not unique and you should show an error message. And also don't forget that upper and lower case could matter This query will be executed very quickly, so you can give feedback to the user almost immediately (if needed).
But you still need the UNIQUE constraint to protect data integrity! Between running the (very fast) email verification query and actually inserting the new user, another user might already be inserted with this email address.

Ya that is how i do it nowadays. I am actually using unique now.

Leave the email thing here, say what if I need address column from the same table to be retrieved for some purpose
SELECT address from User where id=:id
How to do this in hibernate?
I tried the step by step approach. I can get till this
SELECT * from User where id=:id
but I am not able to select a particular column. Any hint will do.

[Roel De Nijs]

Partheban Udayakumar wrote:but I am not able to select a particular column. Any hint will do.

Depending on the return value you'll have different options. This link, this one, and this one provide excellent explanation and code snippets about what's possible using JPQL.

[Partheban Udayakumar]

Roel De Nijs wrote:Depending on the return value you'll have different options. This link, this one, and this one provide excellent explanation and code snippets about what's possible using JPQL.

Roel,

The links you have given explains how to do things with manual queries (ie SELECT * FROM ...) but I need to do it in hibernate using criteria etc.

[Roel De Nijs]

Partheban Udayakumar wrote:but I need to do it in hibernate using criteria etc.

There's a specific article available on the Criteria API as well. You could have found this link yourself as it was very easy to find from the first link I provided...

[Partheban Udayakumar]

Roel De Nijs wrote:There's a specific article available on the Criteria API as well. You could have found this link yourself as it was very easy to find from the first link I provided...

Oh ok. I will definitely try it and post back if there are any questions.