Help with HQL Insert and Joins using Hibernate 3 / JPA

[James Dekker]

Hello there,

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

Created three JPA annotated classes:

(1) User:

@Entity
@Table(name = "user", schema = "test")
public class User
{
	/* Data Fields */
	private int userid;
	private String username;
	private String password;
	private Device device;

	public User()
	{
	}

	@Id
	@GeneratedValue(strategy = IDENTITY)
	@Column(name = "userid", unique = true, nullable = false)
	public Integer getUserid()
	{
		return this.userid;
	}

	public void setUserid(Integer userid)
	{
		this.userid = userid;
	}

	@Column(name = "username", nullable = false, length = 45)
	public String getUsername()
	{
		return this.username;
	}

	public void setUsername(String username)
	{
		this.username = username;
	}

	@Column(name = "password", nullable = false, length = 45)
	public String getPassword()
	{
		return this.password;
	}

	public void setPassword(String password)
	{
		this.password = password;
	}
	
	@OneToOne(cascade=CascadeType.ALL,fetch=FetchType.LAZY)
	@JoinColumn(name="device_id") 
   	public Device getDevice()
	{
		return device;
	}

	public void setDevice(Device device)
	{
		this.device = device;
	}
}

(2) Device:

@Entity
@Table(name = "device", schema = "test")
public class Device
{
	private int id;
	private String uuid;
	private Token token;
	private User user;

	@Id
	@GeneratedValue
	@Column(name = "id", unique = true, nullable = false)
	public int getId()
	{
		return id;
	}

	public void setId(int id)
	{
		this.id = id;
	}

	@Column(name = "uuid")
	public String getUuid()
	{
		return uuid;
	}

	public void setUuid(String uuid)
	{
		this.uuid = uuid;
	}

	
	@OneToOne(cascade=CascadeType.ALL, fetch=FetchType.LAZY)
	@JoinColumn(name="token_id")
	public Token getToken()
	{
		return token;
	}
	
	public void setToken(Token token)
	{
		this.token = token;
	}

	@OneToOne(cascade=CascadeType.ALL, mappedBy="device")
	public User getUser()
	{
		return user;
	}

	public void setUser(User user)
	{
		this.user = user;
	}
}

(3) Token

@Entity
@Table(name = "token", schema = "test")
public class Token
{
	private int id;
	private String value;
	private Date creationTime;
	private Date expirationTime;
	private Device device;
	private Boolean expired;

	@Id
	@GeneratedValue
	@Column(name = "id", unique = true, nullable = false)
	public int getId()
	{
		return id;
	}

	public void setId(int id)
	{
		this.id = id;
	}

	@Column(name = "value")
	public String getValue()
	{
		return value;
	}

	public void setValue(String value)
	{
		this.value = value;
	}

	@Temporal(TemporalType.TIME)
	public Date getCreationTime()
	{
		return creationTime;
	}

	public void setCreationTime(Date creationTime)
	{
		this.creationTime = creationTime;
	}

	@Temporal(TemporalType.TIME)
	public Date getExpirationTime()
	{
		return expirationTime;
	}

	public void setExpirationTime(Date expirationTime)
	{
		this.expirationTime = expirationTime;
	}

	@Basic
	public Boolean isExpired()
	{
		return expired;
	}

	public void setExpired(Boolean expired)
	{
		this.expired = expired;
	}

	@OneToOne(cascade=CascadeType.ALL, mappedBy="token")
	public Device getDevice()
	{
		return device;
	}

	public void setDevice(Device device)
	{
		this.device = device;
	}
}

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.

public static Token generateToken(User user, Device device) 
{
	DataAccessObject dao = new DataAccessObject();
	Transaction tx = dao.beginTransaction();
	String tokenString = "1112222xxxxxx";
	user.setDevice(device);
	Token token = new Token();
	String hql =
	    "insert into token (value) values" +
	    "" + tokenString + "" +
	     "where user.device_id = device.id";
	dao.getSession().createQuery(hql).executeUpdate();
	System.out.println("Encrypted Token is: " + encryptedToken);
	tx.commit();
	dao.close();
    return token;
}

This throws the following error:

Exception in thread "main" org.hibernate.hql.ast.QuerySyntaxException: 
unexpected token: ( near line 1, column 26 [insert into token (value) values (1112222xxxxxx)where user.device_id = device.id]
	at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)
	at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47)
	at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:82)
	at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:284)
	at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:182)
	at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136)

(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)?

	 
public static boolean isValidToken(Device device, Token token)
{
	boolean validated = false;
		
	DataAccessObject dao = new DataAccessObject();
	Transaction tx = dao.beginTransaction();

	if (token != null) 
	{
		String hql = "SELECT token from device, token WHERE device.token_id = token.id";
		Query query = dao.getSession().createQuery(hql);
		validated = true;
	} 
	else 
	{
		validated = false;
	}
			
	tx.commit();
	dao.close();
	return validated;
}

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]

"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?

[James Dekker]

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]

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]

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:

select * from table where something IN (a list of values)

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]

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]

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?