Search code examples
javahibernatespring-mvcoracle11ghql

Compare records from two tables using HQL

I have two tables named Reservation and Vehicle.

  1. Vehicle Table has a Vehicle ID which is it's Primary Key.
  2. Reservation Table also has a Vehicle ID column which is a foreign key referencing the Vehicle ID in the Vehicle table. I have specified the foreign key reference in my database.

Now, I want to retrieve the Vehicle Details of the vehicles whose Vehicle ID is not present in the Reservation Table using HQL. I have written the following query to achieve that:

Query vehicleQuery;
vehicleQuery = session.getCurrentSession().createQuery("from VehicleBean vbean,ReservationBean rbean where vbean.vehicleID NOT LIKE rbean.vehicleID");
        vehicleList=(ArrayList<VehicleBean>) vehicleQuery.list();

But, upon running the code, I'm getting the following error:

Hibernate: select vehiclebea0_.VEHICLEID as VEHICLEID3_0_, reservatio1_.RESERVATIONID as RESERVAT1_5_1_, vehiclebea0_.FAREPERKM as FAREPERKM3_0_, vehiclebea0_.NAME as NAME3_0_, vehiclebea0_.REGISTRATIONNUMBER as REGISTRA4_3_0_, vehiclebea0_.SEATINGCAPACITY as SEATINGC5_3_0_, vehiclebea0_.TYPE as TYPE3_0_, reservatio1_.BOARDINGPOINT as BOARDING2_5_1_, reservatio1_.BOOKINGDATE as BOOKINGD3_5_1_, reservatio1_.BOOKINGSTATUS as BOOKINGS4_5_1_, reservatio1_.DRIVERID as DRIVERID5_1_, reservatio1_.DROPPOINT as DROPPOINT5_1_, reservatio1_.JOURNEYDATE as JOURNEYD7_5_1_, reservatio1_.ROUTEID as ROUTEID5_1_, reservatio1_.TOTALFARE as TOTALFARE5_1_, reservatio1_.USERID as USERID5_1_, reservatio1_.VEHICLEID as VEHICLEID5_1_ from ATA_TBL_VEHICLE vehiclebea0_ cross join ATA_TBL_RESERVATION reservatio1_ where vehiclebea0_.VEHICLEID not like reservatio1_.VEHICLEID
Jun 04, 2015 9:05:07 AM org.apache.catalina.core.ApplicationDispatcher invoke
SEVERE: Servlet.service() for servlet jsp threw exception
java.lang.NumberFormatException: For input string: "vehicleID"
at java.lang.NumberFormatException.forInputString(Unknown Source)
at java.lang.Integer.parseInt(Unknown Source)
at java.lang.Integer.parseInt(Unknown Source)
at javax.el.ArrayELResolver.coerce(ArrayELResolver.java:166)
at javax.el.ArrayELResolver.getValue(ArrayELResolver.java:46)
at org.apache.jasper.el.JasperELResolver.getValue(JasperELResolver.java:104)
at org.apache.el.parser.AstValue.getValue(AstValue.java:183)
at org.apache.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:185)
at org.apache.jasper.runtime.PageContextImpl.proprietaryEvaluate(PageContextImpl.java:1026)
at org.apache.jsp.WEB_002dINF.views.seevehicles_jsp._jspx_meth_c_005fforEach_005f0(seevehicles_jsp.java:348)
at org.apache.jsp.WEB_002dINF.views.seevehicles_jsp._jspService(seevehicles_jsp.java:288)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:749)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:487)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:412)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:339)
at org.springframework.web.servlet.view.InternalResourceView.renderMergedOutputModel(InternalResourceView.java:238)
at org.springframework.web.servlet.view.AbstractView.render(AbstractView.java:264)
at org.springframework.web.servlet.DispatcherServlet.render(DispatcherServlet.java:1208)
at org.springframework.web.servlet.DispatcherServlet.processDispatchResult(DispatcherServlet.java:992)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:939)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:953)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:844)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:829)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1023)
at `enter code here`org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

Here's my POJO classes for Driver and Reservation:

@Entity
@Table(name = "ATA_TBL_RESERVATION")
public class ReservationBean {
    @Id
    @Column(name = "RESERVATIONID")
    private String reservationID;

    @Column(name = "USERID")
    private String userID;

    @Column(name = "ROUTEID")
    private String routeID;

    @Column(name = "BOOKINGDATE")
    private Date bookingDate;

    @Column(name = "JOURNEYDATE")
    private Date journeyDate;

    @Column(name = "VEHICLEID")
    private String vehicleID;

    @Column(name = "DRIVERID")
    private String driverID;

    @Column(name = "BOOKINGSTATUS")
    private String bookingStatus;

    @Column(name = "TOTALFARE")
    private Double totalFare;

    @Column(name = "BOARDINGPOINT")
    private String boardingPoint;

    @Column(name = "DROPPOINT")
    private String dropPoint;


@Entity
@Table(name = "ATA_TBL_VEHICLE")
public class VehicleBean {
    @Id
    @Column(name = "VEHICLEID")
    private String vehicleID;

    @Column(name = "NAME")
    private String name;

    @Column(name = "TYPE")
    private String type;

    @Column(name = "REGISTRATIONNUMBER")
    private Integer registrationNumber;

    @Column(name = "SEATINGCAPACITY")
    private Integer seatingCapacity;

    @Column(name = "FAREPERKM")
    private Double farePerKm;
Solution 
  • I found a way to do the same using subquery. Although, I'm not sure out of this and the left join, which one is the more efficient one.

vehicleQuery = session.getCurrentSession().createQuery("FROM VehicleBean v WHERE (v.type =:vehicleType) AND (v.vehicleID NOT IN (SELECT r.vehicleID  FROM ReservationBean r))");
            vehicleQuery.setString("vehicleType", vehicleType);
            vehicleList=(ArrayList<VehicleBean>) vehicleQuery.list();