All geek questions in one place

Hibernate request syntax exception: org.hibernate.hql.ast.QuerySyntaxException: unexpected token

I have a query when I join two tables ROuteMaster and RouteHalts. When I do the inner join, I get

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 looked at the site and found a similar question and answer to it. the question was

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

I have many, one mappings in RouteHalts for RouteMaster, I 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. You might like someone to tell me.

File display

 <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

The public RouteMaster class 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

The open RouteHalts class extends durability {

 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; }

}

+6
source share
2 answers

You should not use explicit "JOIN ON" in HQL. Instead, you can use implicit union 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 to write 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

You can also execute your query as your own SQL query, rather than an HQL query. For this you should use

 session.createSQLQuery(queryText);

instead

 session.createQuery(queryText);

And by the way, maybe in your case it is best to extract the whole object, rather than individual fields (columns)? For this you can use:

 select rm from ...

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

+8
source

Change your request like it;

  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
0
source

Source: https://habr.com/ru/post/923105/

More articles:


All Articles