I'm trying to create a hibernate query by string using name bound parameters like ":name", etc. Unfortunately I keep getting this exception:
Quote:
org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: :
I have tried both of the following queries:
Code:
// assuming the parameters should be specified by class variables
Query q = s.createQuery("from UserInfoEntity as u where "+
"( u.loginName = :ln OR u.emailAddress = :ea ) AND "+
"u.id NOT :myid ")
.setString("ln", loginName)
.setString("ea", emailAddress)
.setLong("myid", id);
// assuming the parameters should be specified by sql column names:
Query q = s.createQuery("from UserInfoEntity as u where "+
"( u.login_Name = :ln OR u.email_Address = :ea ) AND "+
"u.user_id NOT :myid ")
.setString("ln", loginName)
.setString("ea", emailAddress)
.setLong("myid", id);
The query is part of my UserInfoEntity class in its "saveOrUpdate" function. Here is my class code:
Code:
package com.knapptech.entities;
import java.util.Date;
import java.util.List;
import javax.persistence.Transient;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import com.knapptech.web.ConflictException;
import com.knapptech.web.HibernateUtil;
public class UserInfoEntity {
private Long id = -1l;
private String emailAddress = "";
private String loginName = "";
private String encryptedPassword = "";
private String name = "";
private Date birthdate = null;
private AddressEntity residence = null;
private boolean female = false;
private PhoneNumberEntity phoneNumber = null;
public UserInfoEntity() {
}
public Long getId() {
return id;
}
@SuppressWarnings("unused")
private void setId(Long id) {
this.id = id;
}
public void saveOrUpdate() throws ConflictException {
residence = residence.loadExistingOrSave();
Session s = HibernateUtil.getSessionFactory().getCurrentSession();
Transaction t = null;
List<UserInfoEntity> conflicts = null;
try {
t = s.getTransaction();
if (t == null || !t.isActive())
t = s.beginTransaction();
Query q = s.createQuery("from UserInfoEntity as u where "+
"( u.loginName = :ln OR u.emailAddress = :ea ) AND "+
"u.user_id NOT :myid ")
.setString("ln", loginName)
.setString("ea", emailAddress)
.setLong("myid", id);
conflicts = q.list();
if (conflicts == null || conflicts.size()<1) {
s.saveOrUpdate(this);
}
} catch (Exception e) {
if (t != null)
t.rollback();
e.printStackTrace();
} finally {
if (t != null && t.isActive())
t.commit();
}
if (conflicts!=null && conflicts.size()>0)
throw new ConflictException(
"Cannot save or update, the user info is redundant.",
conflicts);
}
// Standard getters and setters ...
// other irrelevant code.
}
If I remove the query for conflicts from the "saveOrUpdate" function, the test code works, though it cannot prevent conflicts based on login name or email address. Here is the mapping:
Code:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.knapptech.entities">
<class name="UserInfoEntity" table="user_info">
<id name="id" column="USER_ID">
<generator class="native" />
</id>
<property name="emailAddress" type="string" column="EMAIL_ADDRESS" not-null="true" />
<property name="loginName" type="string" column="LOGIN_NAME" not-null="true" />
<property name="encryptedPassword" type="string" column="PASSWORD" not-null="true" />
<property name="name" type="string" column="NAME" />
<property name="birthdate" type="date" column="BIRTHDATE"/>
<property name="female" type="boolean" column="FEMALE" />
<many-to-one unique="true" name="phoneNumber" column="PHONENUMBER_ID" lazy="false"/>
<join table="ADDRESS_OCCUPANTS" optional="true" inverse="true" >
<key column="USER_ID"/>
<many-to-one name="residence" cascade="persist,merge,save-update" column="ADDRESS_ID" lazy="false"/>
</join>
</class>
</hibernate-mapping>
Through my test script I call the saveOrUpdate function found on the UserInfoEntity class.
Every time I get this exception:
Code:
Hibernate: select userinfoen0_.USER_ID as USER1_3_, userinfoen0_.EMAIL_ADDRESS as EMAIL2_3_, userinfoen0_.LOGIN_NAME as LOGIN3_3_, userinfoen0_.PASSWORD as PASSWORD3_, userinfoen0_.NAME as NAME3_, userinfoen0_.BIRTHDATE as BIRTHDATE3_, userinfoen0_.FEMALE as FEMALE3_, userinfoen0_.PHONENUMBER_ID as PHONENUM8_3_, userinfoen0_1_.ADDRESS_ID as ADDRESS2_1_ from user_info userinfoen0_ left outer join ADDRESS_OCCUPANTS userinfoen0_1_ on userinfoen0_.USER_ID=userinfoen0_1_.USER_ID where userinfoen0_.LOGIN_NAME=?
Hibernate: select addressent0_.ADDRESS_ID as ADDRESS1_0_, addressent0_.HOUSENUMBER as HOUSENUM2_0_, addressent0_.STREETADDRESS as STREETAD3_0_, addressent0_.CITY as CITY0_, addressent0_.STATE as STATE0_, addressent0_.COUNTRY as COUNTRY0_, addressent0_.ZIP as ZIP0_ from addresses addressent0_ where addressent0_.HOUSENUMBER=? and addressent0_.STREETADDRESS=? and addressent0_.CITY=? and addressent0_.STATE=? and addressent0_.COUNTRY=? and addressent0_.ZIP=?
org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: : near line 1, column 121 [from com.knapptech.entities.UserInfoEntity as u where ( u.login_Name = :ln OR u.email_Address = :ea ) AND u.user_id NOT :myid ]
at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:53)
at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:46)
at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:79)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:276)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:180)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:120)
at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:214)
at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:192)
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1527)
at sun.reflect.GeneratedMethodAccessor10.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.hibernate.context.internal.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:349)
at $Proxy7.createQuery(Unknown Source)
at com.knapptech.entities.UserInfoEntity.saveOrUpdate(UserInfoEntity.java:153)
at com.knapptech.entities.UserInfoEntityTest.testDelete(UserInfoEntityTest.java:177)
.... long stack trace ....
So I don't know why this is happening. It seems like the query is not able to parse my parameter names. What's weird is my query is not even 121 characters long, it must be talking about the hibernate modified query string. I looked at its modified string and don't see any colon in it. I looked closely at the examples here:
http://docs.jboss.org/hibernate/core/3. ... -executingbut I don't see anything that I am doing different.
Could you tell me why I'm getting this exception and how to fix it please?
Also, could you tell me if my query where statement should use sql column names or class variable names? In other words, should I use "where u.entityVariableName = ?" or "where u.sql_column_name = ?" ?
Login
FAQ
Search