GeeCON Prague 2014*
The moose likes Object Relational Mapping and the fly likes Help with HQL Insert and Joins using Hibernate 3 / JPA Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Help with HQL Insert and Joins using Hibernate 3 / JPA" Watch "Help with HQL Insert and Joins using Hibernate 3 / JPA" New topic
Author

Help with HQL Insert and Joins using Hibernate 3 / JPA

James Dekker
Ranch Hand

Joined: Dec 09, 2006
Posts: 215
Hello there,

Need help with some specific HQL queries and / or better techniques for Hibernate / JPA.

Created three JPA annotated classes:

(1) User:



(2) Device:



(3) Token



Details / Schema Info:

* User and Device contain a bi-directional one-to-one mapping.
* User contains a foreign key (device_id) connecting to Device's primary key.
* Device and Token contain a bi-directional one-to-one mapping.
* Device contains a foreign key (token_id) connecting to Token's primary key.

What I need help with is figuring out these following conditions where I think I could only do using HQL...

Am trying to create two methods in a utility class that does this:

(1) Insert string into the token.value (table.row notation) where user and device are joined by their ids.



This throws the following error:



(2) I need my next query to match the following conditions:

- Token currently exists (which I think I did by issuing token != null)
- A query or logic which checks if the Token.id matches specified Device.token_id (how can I do this?, I tried the SELECT statement but I am not sure about it)?



Would really appreciate it if someone could help me with these HQL queries (I couldn't find a good reference on HQL which gave an example using INSERT INTO).

Happy programming and thanks!

-James
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


"insert into token (value) Values " +
"(" + tokenString + ")" +
"where user.device_id = device.id";

HQL (or any other database language) aside, how are you going to insert into a table based on a condition of a record in that table? And how is user related to token?


A query or logic which checks if the Token.id matches specified Device.token_id (how can I do this?, I tried the SELECT statement but I am not sure about it)?

Could you not just use an IN clause?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
James Dekker
Ranch Hand

Joined: Dec 09, 2006
Posts: 215
User is not related to token... Lets just assume that when a person uses my program, he / she creates a user (by entering in the info on an online form) and also enters in the info for the Device (on an online form). If you take a look at my JPA annotated classes, you'll see that I have a OneToOne relationship with User and Device.

I am just trying to insert a token string into my Token class's value property (which is mapped to a database table called Token and has a field called value), for every pre-existing user who has a device. That's why I figured that the Insert HQL query would be simple and ideal for this situation... Is this the wrong way to be doing this with Hibernate3 / JPA? Should I not be using HQL?

-James
James Dekker
Ranch Hand

Joined: Dec 09, 2006
Posts: 215
Hi Paul,

Could you not just use an IN clause?


Could you elaborate on this? Sorry, I've never heard of an IN clause...

-James
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


I am just trying to insert a token string into my Token class's value property (which is mapped to a database table called Token and has a field called value), for every pre-existing user who has a device. That's why I figured that the Insert HQL query would be simple and ideal for this situation... Is this the wrong way to be doing this with Hibernate3 / JPA? Should I not be using HQL?

Putting JPA to one side for a moment, how would you do this in SQL? Your insert staements requires that there already is a record in token (in which case an insert statement can't work), or it is using a conditional clause based on unrelated data (i.e. its from a record for another user).

IN clauses are SQL (or in this case HQL) clauses with the syntax:

So I'd change the logic to start by using this construct to find out how many users who have devices are not already in the token table. Then you could go through this list and add token records accordinly.
James Dekker
Ranch Hand

Joined: Dec 09, 2006
Posts: 215
Paul,

My token table has records... I am just trying to populate the token.value property (database field) with a token string for every user who also has a device id associated. I figured a simple INSERT would do the trick.

The second issue is, how can I find every single token which has a device associated with it? That's just the criteria for seeing if the token exists (token != null) and if its associated with a device, to set my boolean flag to true.

-James
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


My token table has records... I am just trying to populate the token.value property (database field) with a token string for every user who also has a device id associated. I figured a simple INSERT would do the trick.

An insert statement (in SQL or HQL) creates a new record. You may be looking for an update statement.


The second issue is, how can I find every single token which has a device associated with it?

select all from your token table where the device is not null?

 
GeeCON Prague 2014
 
subject: Help with HQL Insert and Joins using Hibernate 3 / JPA