aspose file tools*
The moose likes Object Relational Mapping and the fly likes Searching row in database by a column which is not a primary key Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Searching row in database by a column which is not a primary key" Watch "Searching row in database by a column which is not a primary key" New topic
Author

Searching row in database by a column which is not a primary key

Ismael Upright
Ranch Hand

Joined: Feb 15, 2007
Posts: 166
Hello,

I have an entity:




How can I find objectId by a surname given by user? In EntityManager I can use find(...) only with a primary key..
The surname is unique.
Ralph Jaus
Ranch Hand

Joined: Apr 27, 2008
Posts: 342
You have to use a query. For example:

select p from Person p where surname = :surname


SCJP 5 (98%) - SCBCD 5 (98%)
Ismael Upright
Ranch Hand

Joined: Feb 15, 2007
Posts: 166
Thank you Ralph, but unfortunately it doesn't work, and besides I'd like to do it with JPA 2 CriteriaBuilder and CriteriaQuery... and I still cannot figure out how to do that correctly.
Ismael Upright
Ranch Hand

Joined: Feb 15, 2007
Posts: 166
String jpql = "select p from Person p where surname = '" + givenSurname + "'";


it works - I forgot about ' marks...

Still I'm looking for JPA 2.0 sample witha CriteriaBuilder ansd so on which will do the same thing.
Ralph Jaus
Ranch Hand

Joined: Apr 27, 2008
Posts: 342
You can find some examples for using the criteria API in the following blog. Hope this helps.
Ismael Upright
Ranch Hand

Joined: Feb 15, 2007
Posts: 166
I've also found this blog. The problem is that the class QueryBuilder seem to not exist, at least you can't find in this Java EE 6 API:

http://technology-related.com/javaee/6/docs/api/

and I can't find it in the implementation I downloaded (although rest of these new classes exist, i.e. CriteriaBuilder).




Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42277
    
  64
Yes, that blog entry is obsolete, the API changed after it was written. Here's a good introduction: http://www.ibm.com/developerworks/java/library/j-typesafejpa/


Ping & DNS - my free Android networking tools app
Ismael Upright
Ranch Hand

Joined: Feb 15, 2007
Posts: 166
The code below finds and returns a whole Person object with given surname.



Almost there...

If you know how to return only objectId of this Person please share your knowledge
Ismael Upright
Ranch Hand

Joined: Feb 15, 2007
Posts: 166
Ulf Dittmer wrote:Yes, that blog entry is obsolete, the API changed after it was written. Here's a good introduction: http://www.ibm.com/developerworks/java/library/j-typesafejpa/


But still there's a QueryBuilder
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Ismael Upright wrote:The code below finds and returns a whole Person object with given surname.



Almost there...

If you know how to return only objectId of this Person please share your knowledge


Will this not do it:


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Ismael Upright
Ranch Hand

Joined: Feb 15, 2007
Posts: 166
It will, but I was rather thinking about not sending the whole Person from the database, but only it's Id.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Ismael Upright wrote:It will, but I was rather thinking about not sending the whole Person from the database, but only it's Id.


OK. What problem are you trying to solve by doing that?
Ismael Upright
Ranch Hand

Joined: Feb 15, 2007
Posts: 166
The problem of optimization of traffic between application and database. I want only needed data to be sent by database.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Given how ORMs work, your problem is probably not worth worrying about. If you need this level of optimization (i.e. the network between database and application is likely to be very poor) don't use an ORM. Have you profiled your applciation and checked this is a real problem, or are you just worrying about this as an early optimisation?
Ismael Upright
Ranch Hand

Joined: Feb 15, 2007
Posts: 166
Paul Sturrock wrote:Given how ORMs work, your problem is probably not worth worrying about. If you need this level of optimization (i.e. the network between database and application is likely to be very poor) don't use an ORM. Have you profiled your applciation and checked this is a real problem, or are you just worrying about this as an early optimisation?


Just worrying and wondering if there's a dedicated solution to get only 1 field from database instead of a whole object
Thank you for pointing out the ORM concept Probably it's indeed not worth worrying about.
 
 
subject: Searching row in database by a column which is not a primary key