Hibernate Query Syntax exception : org.hibernate.hql.ast.QuerySyntaxException: unexpected token

Question

I have a query where i am joining two tables ROuteMaster and RouteHalts. When i perform inner join i am getting

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, 

column 169 [SELECT  rm.id , rm.routeCode , rm.startPlaceId , rm.endPlaceId , 
rm.active, rm.linkedRoute FROM com.oprs.pojo.routes.RouteMaster rm  INNER JOIN 
RouteHalts rh  on rm.id = rh.routeId  WHERE  rh.placeId = :PlaceId  
ORDER BY  rm.id  ASC]

I searched through the site and found similar question and the response for it. the question referred was

Hibernate: org.hibernate.hql.ast.QuerySyntaxException: unexpected token

I have many-to-one mapping in RouteHalts for RouteMaster, I have defined getter and setter methods for RouteMaster in RouteHalts

<many-to-one name="RouteMaster" class="com.oprs.pojo.routes.RouteMaster" 
                    fetch="join"
            foreign-key="id" column="ROUTE_ID" insert="false"
            update="false" lazy="false" />

but still getting the same error. Can please some one guide me.

Mapping files

<hibernate-mapping package="com.oprs.pojo.routes">
    <!-- Hibernate mapping for RouteMaster table -->
    <class name="RouteMaster" table="OPRS_ROUTE_MASTER">
        <id name="id" column="ROUTE_ID" type="java.lang.Long">
            <generator class="assigned" />
        </id>
        <property name="startPlaceId" column="START_PLACE_ID"/>
        <property name="endPlaceId" column="END_PLACE_ID"/>
        <property name="routeCode" column="ROUTE_CODE"/>
        <property name="routeName" column="ROUTE_NAME"/>
        <property name="active" column="IS_ACTIVE"/>
        <property name="linkedRoute" column="LINKED_ROUTE"/>
        <property name="returnRouteId" column="RET_ROUTE_ID"/>
        <!-- Auditor Information -->
        <component name="auditor" class="com.oprs.pojo.base.Auditor">
            <property name="createdBy" column="CREATED_BY" />
            <property name="createdDate" column="CREATED_DATE" />
            <property name="modifiedBy" column="MODIFIED_BY" />
            <property name="modifiedDate" column="MODIFIED_DATE" />
        </component>
        <many-to-one  name="RouteHalts" class="com.oprs.pojo.routes.RouteHalts" fetch="join"
            foreign-key="routeId" column="ROUTE_ID" insert="false"
            update="false" lazy="false" />
    </class>

    <!-- Hibernate mapping for RouteHalts table -->
    <class name="RouteHalts" table="OPRS_ROUTE_HALTS">
        <id name="id" column="HALTS_ID" type="java.lang.Long">
            <generator class="assigned" />
        </id>
        <property name="routeId" column="ROUTE_ID"/>
        <property name="placeId" column="PLACE_ID"/>
        <property name="seqNo" column="SEQ_NO"/>
        <property name="distanceKM" column="DISTANCE_KM"/>
        <property name="border" column="IS_BORDER"/>
        <property name="tolls" column="NO_OF_TOLLS"/>       
        <!-- Auditor Information -->
        <component name="auditor" class="com.oprs.pojo.base.Auditor">
            <property name="createdBy" column="CREATED_BY" />
            <property name="createdDate" column="CREATED_DATE" />
            <property name="modifiedBy" column="MODIFIED_BY" />
            <property name="modifiedDate" column="MODIFIED_DATE" />
        </component>
    </class>


POJO of Route Master

public class RouteMaster extends Persistent {

private static final long serialVersionUID = -5710336066048392949L;

private Long startPlaceId;
private Long endPlaceId;
private Long returnRouteId;
private String startPlaceCode;
private String endPlaceCode;
private String startPlaceName;
private String endPlaceName;
private String routeCode;
private String routeName;
private String active;
private Auditor auditor;
private boolean revervseRoute;
private String linkedRoute = AppConstants.N;
private Map<Double, RouteHalts> haltsMap;
private RouteHalts routeHalts;


public RouteHalts getRouteHalts() {
    return routeHalts;
}

public void setRouteHalts(RouteHalts routeHalts) {
    this.routeHalts = routeHalts;
}

public Long getStartPlaceId() {
    return startPlaceId;
}

public void setStartPlaceId(Long startPlaceId) {
    this.startPlaceId = startPlaceId;
}

public Long getEndPlaceId() {
    return endPlaceId;
}

public void setEndPlaceId(Long endPlaceId) {
    this.endPlaceId = endPlaceId;
}

public String getStartPlaceCode() {
return startPlaceCode;
}

public void setStartPlaceCode(String startPlaceCode) {
this.startPlaceCode = startPlaceCode;
}

public String getEndPlaceCode() {
return endPlaceCode;
}

public void setEndPlaceCode(String endPlaceCode) {
this.endPlaceCode = endPlaceCode;
}

public Long getReturnRouteId() {
    return returnRouteId;
}

public void setReturnRouteId(Long returnRouteId) {
    this.returnRouteId = returnRouteId;
}

public String getRouteCode() {
return routeCode;
}

public void setRouteCode(String routeCode) {
this.routeCode = routeCode;
}

public Auditor getAuditor() {
return auditor;
}

public void setAuditor(Auditor auditor) {
this.auditor = auditor;
}

public String getStartPlaceName() {
    return startPlaceName;
}

public void setStartPlaceName(String startPlaceName) {
    this.startPlaceName = startPlaceName;
}

public String getEndPlaceName() {
    return endPlaceName;
}

public void setEndPlaceName(String endPlaceName) {
    this.endPlaceName = endPlaceName;
}

public String getActive() {
    return active;
}

public void setActive(String active) {
    this.active = active;
}

public Map<Double, RouteHalts> getHaltsMap() {
    return haltsMap;
}

public void setHaltsMap(Map<Double, RouteHalts> haltsMap) {
    this.haltsMap = haltsMap;
}

public boolean isRevervseRoute() {
    return revervseRoute;
}

public void setRevervseRoute(boolean revervseRoute) {
    this.revervseRoute = revervseRoute;
}

public String getLinkedRoute() {
    return linkedRoute;
}

public void setLinkedRoute(String linkedRoute) {
    this.linkedRoute = linkedRoute;
}

public String getRouteName() {
    return routeName;
}

public void setRouteName(String routeName) {
    this.routeName = routeName;
}

}

POJO of RouteHalts

public class RouteHalts extends Persistent {

private static final long serialVersionUID = -1491637903595290895L;
private Long placeId;
private Long routeId;
private String placeCode;
private Double seqNo;
private Double distanceKM;
private boolean border;
private Auditor auditor;

private String placeName;
private String stateCode;
private String stopType;
private String departureTime;
private Integer tolls;
private String platformNo;
private Long stopTypeId;
private Integer linkSequenceNo;
private String actualTime;
private int arrivalDay;

public String getStateCode() {
    return stateCode;
}

public void setStateCode(String stateCode) {
    this.stateCode = stateCode;
}

public Long getRouteId() {
    return routeId;
}

public void setRouteId(Long routeId) {
    this.routeId = routeId;
}

public Long getPlaceId() {
    return placeId;
}

public void setPlaceId(Long placeId) {
    this.placeId = placeId;
}

public String getPlaceCode() {
return placeCode;
}

public void setPlaceCode(String placeCode) {
this.placeCode = placeCode;
}

public Double getDistanceKM() {
return distanceKM;
}

public void setDistanceKM(Double distanceKM) {
this.distanceKM = distanceKM;
}

public boolean isBorder() {
    return border;
}

public void setBorder(boolean border) {
    this.border = border;
}

public Auditor getAuditor() {
return auditor;
}

public void setAuditor(Auditor auditor) {
this.auditor = auditor;
}

public String getPlaceName() {
    return placeName;
}

public void setPlaceName(String placeName) {
    this.placeName = placeName;
}

public Double getSeqNo() {
    return seqNo;
}

public void setSeqNo(Double seqNo) {
    this.seqNo = seqNo;
}

public String getStopType() {
    return stopType;
}

public void setStopType(String stopType) {
    this.stopType = stopType;
}

public String getDepartureTime() {
    return departureTime;
}

public void setDepartureTime(String departureTime) {
    this.departureTime = departureTime;
}

public Integer getTolls() {
    return tolls;
}

public void setTolls(Integer tolls) {
    this.tolls = tolls;
}

public String getPlatformNo() {
    return platformNo;
}

public void setPlatformNo(String platformNo) {
    this.platformNo = platformNo;
}

public Long getStopTypeId() {
    return stopTypeId;
}

public void setStopTypeId(Long stopTypeId) {
    this.stopTypeId = stopTypeId;
}

public Integer getLinkSequenceNo() {
    return linkSequenceNo;
}

public void setLinkSequenceNo(Integer linkSequenceNo) {
    this.linkSequenceNo = linkSequenceNo;
}

public int getArrivalDay() {
    return arrivalDay;
}

public void setArrivalDay(int arrivalDay) {
    this.arrivalDay = arrivalDay;
}

public String getActualTime() {
    return actualTime;
}

public void setActualTime(String actualTime) {
    this.actualTime = actualTime;
}

}

dimas · Accepted Answer · 2012-08-17 10:05:21Z

11

You should not use explicit "JOIN ON" in HQL. Instead you can use implicit joining in HQL:

SELECT  rm.id , rm.routeCode , rm.startPlaceId , rm.endPlaceId , rm.active , rm.linkedRoute 
FROM com.abhibus.oprs.pojo.routes.RouteMaster rm  
INNER JOIN rm.routeHalts rh WHERE  rh.placeId = :PlaceId  ORDER BY  rm.id  ASC

or you can use Theta style for writing join:

SELECT  rm.id , rm.routeCode , rm.startPlaceId , rm.endPlaceId , rm.active , rm.linkedRoute 
FROM com.abhibus.oprs.pojo.routes.RouteMaster rm, RouteHalts rh
WHERE rm.id = rh.routeId AND rh.placeId = :PlaceId  ORDER BY  rm.id  ASC

Also you can execute your query as native SQL query, not HQL query. For this you should use

session.createSQLQuery(queryText);

instead of

session.createQuery(queryText);

And by the way, may be in your case in is better to fetch whole entity, not separated fields (columns)? For this you can use:

select rm from ...

This will return the List<RouteMaster> insted of List<Object[]>.

edited Aug 17, 2012 at 10:05

answered Aug 17, 2012 at 9:52

dimas

6,10339 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Srikanth Sridhar Over a year ago

I wrote as you have shown in the first example, but still getting the same error. I can't change it to SQL as this is one of the conditions for the search criteria. If I change this then i need to rework on all those.

Srikanth Sridhar Over a year ago

yes, org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ON near line 1, column 176 [SELECT rm.id , rm.routeCode , rm.startPlaceId , rm.endPlaceId , rm.active , rm.linkedRoute FROM com.oprs.pojo.routes.RouteMaster rm INNER JOIN rm.routeHalts AS rh ON rm.id = rh.routeId WHERE rh.placeId = :PlaceId ORDER BY rm.id ASC]

Srikanth Sridhar Over a year ago

I have added the mapping file too, please suggest if any modifications needed.

dimas Over a year ago

Why do you use ON? Where do you see ON in my example. You should delete "ON rm.id = rh.routeId" from query.

Srikanth Sridhar Over a year ago

hi dimas, thanks for your reply. I rewrote my query but getting error org.hibernate.QueryException: could not resolve property: routeHalts of: com.oprs.pojo.routes.RouteMaster. I am not able to understand what exactly is going wrong??

|

Armine · Accepted Answer · 2021-03-05 13:01:38Z

0

Change your query likes this;

SELECT rm.id, rm.routeCode, rm.startPlaceId, rm.endPlaceId, rm.active, rm.linkedRoute 
FROM RouteMaster rm 
   INNER JOIN rm.routeHalts AS rh ON rm.id = rh.routeId 
WHERE rh.placeId = :PlaceId 
ORDER BY rm.id ASC

edited Mar 5, 2021 at 13:01

Armine

1,6953 gold badges26 silver badges44 bronze badges

answered Aug 17, 2012 at 9:58

Sai Ye Yan Naing Aye

6,74812 gold badges49 silver badges65 bronze badges

4 Comments

Srikanth Sridhar Over a year ago

could you please explain me the Inner join used in the above query, INNER JOIN rm.routeHalts AS rh

Sai Ye Yan Naing Aye Over a year ago

@user1103504 According to your hibernate xml file, I assume RouteMaster has many RouteHalts.So it is the reason i wrote this query.

Srikanth Sridhar Over a year ago

yes you are right, i have many-to-one mapping in RouteHalts. Do I need to have RouteHalts defined in my RouteMaster pojo with getter and setter methods? May be it's a silly question but I am new to hibernate ?

Sai Ye Yan Naing Aye Over a year ago

@user1103504 Yes you should write RouteHalts list in your RouteMaster with getter & setter.

Collectives™ on Stack Overflow

Hibernate Query Syntax exception : org.hibernate.hql.ast.QuerySyntaxException: unexpected token

2 Answers 2

8 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related