Problem on HQL Select query with composite-id?

mumbaimuru · **Posted:** Sun Nov 30, 2008 2:10 pm

Hi,
I have two tables with following structure in MySQL database and following codes. I want to select the payment details where payerid=1. I am trying to use HQL and SELECT queries with where condition. But it s giving error.

Selecting Record
Hibernate: select payment0_.payerId as col_0_0_, payment0_.todaydate as col_0_1_, payment0_.balance as col_0_2_, payment0_.todayamt as col_0_3_, payment0_.todaypaid as col_0_4_ from payment payment0_ where (payment0_.payerId, payment0_.todaydate, payment0_.balance, payment0_.todayamt, payment0_.todaypaid)='1'
23:33:15,937 WARN JDBCExceptionReporter:71 - SQL Error: 1241, SQLState: 21000
23:33:15,937 ERROR JDBCExceptionReporter:72 - Operand should contain 5 column(s)
org.hibernate.exception.GenericJDBCException: could not execute query using iterate
at org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:92)
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:80)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.hql.QueryLoader.iterate(QueryLoader.java:432)
at org.hibernate.hql.ast.QueryTranslatorImpl.iterate(QueryTranslatorImpl.java:281)
at org.hibernate.impl.SessionImpl.iterate(SessionImpl.java:935)
at org.hibernate.impl.QueryImpl.iterate(QueryImpl.java:41)
at org.apache.jsp.TestPayments_jsp._jspService(TestPayments_jsp.java:66)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:94)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:324)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Operand should contain 5 column(s)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3004)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1128)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1222)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1252)
at org.hibernate.loader.hql.QueryLoader.iterate(QueryLoader.java:411)
... 34 more

Code:

DROP TABLE IF EXISTS `rajmilk`.`payment`;
CREATE TABLE  `rajmilk`.`payment` (
  `payerId` int(10) unsigned NOT NULL auto_increment,
  `todaydate` datetime NOT NULL,
  `balance` int(10) unsigned NOT NULL,
  `todayamt` int(10) unsigned NOT NULL,
  `todaypaid` int(10) unsigned NOT NULL,
  KEY `payerid` (`payerId`),
  CONSTRAINT `payerid` FOREIGN KEY (`payerId`) REFERENCES `clients` (`clientid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `rajmilk`.`clients`;
CREATE TABLE  `rajmilk`.`clients` (
  `clientid` int(10) unsigned NOT NULL auto_increment,
  `peru` varchar(30) NOT NULL,
  `address` varchar(45) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `cell` varchar(11) NOT NULL,
  `type` varchar(45) NOT NULL COMMENT 'Litre or Grade',
  `discount` varchar(2) NOT NULL COMMENT 'A or B or C or D or E or F',
  PRIMARY KEY  (`clientid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Payment.hbm.xml

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Nov 28, 2008 11:48:15 PM by Hibernate Tools 3.1.0.beta4 -->
<hibernate-mapping>
    <class name="com.lcc.hibernate.pojo.Payment" table="payment">
        <composite-id name="id" class="com.lcc.hibernate.pojo.PaymentId">
            <key-property name="payerId" type="int">
                <column name="payerId" />
            </key-property>
            <key-property name="todaydate" type="timestamp">
                <column name="todaydate" length="19" />
            </key-property>
            <key-property name="balance" type="int">
                <column name="balance" />
            </key-property>
            <key-property name="todayamt" type="int">
                <column name="todayamt" />
            </key-property>
            <key-property name="todaypaid" type="int">
                <column name="todaypaid" />
            </key-property>
        </composite-id>
        <many-to-one name="clients" class="com.lcc.hibernate.pojo.Clients" update="false" insert="false" fetch="select">
            <column name="payerId" not-null="true" />
        </many-to-one>
    </class>
</hibernate-mapping>

Payment.java

Code:

package com.lcc.hibernate.pojo;
// Generated Nov 28, 2008 11:48:16 PM by Hibernate Tools 3.1.0.beta4



/**
 * Payment generated by hbm2java
 */

public class Payment  implements java.io.Serializable {


    // Fields    

     private PaymentId id;
     private Clients clients;


    // Constructors

    /** default constructor */
    public Payment() {
    }

    
    /** full constructor */
    public Payment(PaymentId id, Clients clients) {
        this.id = id;
        this.clients = clients;
    }
    

   
    // Property accessors

    public PaymentId getId() {
        return this.id;
    }
    
    public void setId(PaymentId id) {
        this.id = id;
    }

    public Clients getClients() {
        return this.clients;
    }
    
    public void setClients(Clients clients) {
        this.clients = clients;
    }
  
}

PaymentId.java

Code:

package com.lcc.hibernate.pojo;
// Generated Nov 28, 2008 11:48:16 PM by Hibernate Tools 3.1.0.beta4

import java.util.Date;


/**
 * PaymentId generated by hbm2java
 */

public class PaymentId  implements java.io.Serializable {


    // Fields    

     private int payerId;
     private Date todaydate;
     private int balance;
     private int todayamt;
     private int todaypaid;


    // Constructors

    /** default constructor */
    public PaymentId() {
    }

    
    /** full constructor */
    public PaymentId(int payerId, Date todaydate, int balance, int todayamt, int todaypaid) {
        this.payerId = payerId;
        this.todaydate = todaydate;
        this.balance = balance;
        this.todayamt = todayamt;
        this.todaypaid = todaypaid;
    }
    

   
    // Property accessors

    public int getPayerId() {
        return this.payerId;
    }
    
    public void setPayerId(int payerId) {
        this.payerId = payerId;
    }

    public Date getTodaydate() {
        return this.todaydate;
    }
    
    public void setTodaydate(Date todaydate) {
        this.todaydate = todaydate;
    }

    public int getBalance() {
        return this.balance;
    }
    
    public void setBalance(int balance) {
        this.balance = balance;
    }

    public int getTodayamt() {
        return this.todayamt;
    }
    
    public void setTodayamt(int todayamt) {
        this.todayamt = todayamt;
    }

    public int getTodaypaid() {
        return this.todaypaid;
    }
    
    public void setTodaypaid(int todaypaid) {
        this.todaypaid = todaypaid;
    }
   



   public boolean equals(Object other) {
         if ( (this == other ) ) return true;
       if ( (other == null ) ) return false;
       if ( !(other instanceof PaymentId) ) return false;
       PaymentId castOther = ( PaymentId ) other; 
         
       return (this.getPayerId()==castOther.getPayerId())
 && ( (this.getTodaydate()==castOther.getTodaydate()) || ( this.getTodaydate()!=null && castOther.getTodaydate()!=null && this.getTodaydate().equals(castOther.getTodaydate()) ) )
 && (this.getBalance()==castOther.getBalance())
 && (this.getTodayamt()==castOther.getTodayamt())
 && (this.getTodaypaid()==castOther.getTodaypaid());
   }
   
   public int hashCode() {
         int result = 17;
         
         result = 37 * result + this.getPayerId();
         result = 37 * result + ( getTodaydate() == null ? 0 : this.getTodaydate().hashCode() );
         result = 37 * result + this.getBalance();
         result = 37 * result + this.getTodayamt();
         result = 37 * result + this.getTodaypaid();
         return result;
   }   





}

TestPayments.jsp

Code:

<%@page import="java.util.*,org.hibernate.*,org.hibernate.cfg.*,com.lcc.hibernate.pojo.*" %>
<html>

  <body>
    <form name="myform" action="EnterGradePrice1.jsp" method="post">

      <%
      String clientid=request.getParameter("clientid");
      Session sess = null;

      try {
        SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
        sess = sessionFactory.openSession();
        System.out.println("Selecting Record");
        //String sql = "From Payment payment"; //It is fetched record. but only one recore.
        String sql = "From Payment payment where payment.id='1'";// ==> Not working
       // String sql = "select * from payment";// where payerid=1"; ==> Not working
        Query query = sess.createQuery(sql);
        
        for(Iterator it=query.iterate();it.hasNext();){
          Payment clnt = (Payment) it.next();
          PaymentId payid=(PaymentId)clnt.getId();
          if(payid.getPayerId() == 1)
          {
            out.println("Balance="+payid.getBalance()+"<br>");
             break;
          }
        }
        System.out.println("one");

      } catch (Exception e) {
        // System.out.println(e);
        e.printStackTrace();
      } finally {
        sess.flush();
        sess.close();
      }
      %>
      <br>
      <div id="totalarea">
      </div>
      <input type="submit" value="Submit">
    </form>

  </body>
</html>

Thx in Advance.