Announcement Announcement Module
Collapse
No announcement yet.
Executing aggregate function in ibatis Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Executing aggregate function in ibatis

    Hi All,

    I am trying to run the following query in the ibatis :
    Code:
    SELECT price_list_id,COUNT(*) as customer_id
            FROM bill_invoice_line
            GROUP BY price_list_id
    As you can see that it is returning price_list_id along with the customer numbers in each price list. I have a doubt in mapping this query's output to my resultmap class. My result map class is billing_invoice _line which is price_list_id and customer_id as bean properties.
    So my mapping looks like:

    Code:
    <resultMap id="billingInvoiceLineResult" class="BillingInvoiceLine">
            <result property="billingCustomer" column="CUSTOMER_ID" select="BillingCustomer.getById"/>
            <result property="billingPriceList" column="PRICE_LIST_ID" select="BillingPriceList.getById"/>
    
        </resultMap>
    I just want to know that the way i am mapping it is it right or not?
    I am doubtful about the customer_id mapping as it is returning the count(*).
    Is there any other way out of doing this?
    Any help will be greatly appreciated.

    Thanks and Regards,
    Khushwinder

  • #2
    I am getting the following exception in this:

    Code:
    BillingCustomer.getById
    not found in the sql map file.

    If any body has any idea plz help me out.

    Comment


    • #3
      check

      this work for my

      Code:
      	<resultMap id="mapGetNotMenu" class="cl.sf.dto.NotMenuDTO">
      		<result column="ID_MENU" 		property="id_menu"		jdbcType="INTEGER" 		columnIndex="1" />		
      		<result column="SUBMENU" 		property="padre"		jdbcType="INTEGER" 		columnIndex="2" />		
      		<result column="TEXTO" 			property="nombre"		jdbcType="VARCHAR" 		columnIndex="3" />
      	</resultMap>
      and the query


      Code:
      	<select id="getNoMenusByRol" resultMap="mapGetNotMenu" >
      		SELECT
      		    DISTINCT menu.ID_MENU,menu.SUBMENU,menu.TEXTO
      		FROM
      		    safetower.menu_rol
      		    INNER JOIN safetower.menu ON (menu_rol.ID_MENU = menu.ID_MENU)
      		where menu.ID_MENU  not in(
      		SELECT menu.ID_MENU
      		FROM
      		    safetower.menu_rol
      		    INNER JOIN safetower.menu ON (menu_rol.ID_MENU = menu.ID_MENU)
      		where id_rol =#idrol#)
      	</select>
      I hope this help works for you
      Last edited by JoseBovet; May 14th, 2009, 03:05 PM.

      Comment


      • #4
        Thanks a lot for your reply JoseBovet.
        But my problem is this:
        In my query i am returning count(*) of the coustomer_id wrt to each price list.
        and count(*) does not represent any column type.
        So this query is a mixture of bean property and non-bean property.
        I hope i made myself clear.
        and while executing its giving err:
        Cause: com.ibatis.sqlmap.client.SqlMapException: There is no statement named BillingCustomer.getById in this SqlMap.

        Any idea how to do it?

        Comment


        • #5
          Hi Khushwinder,
          in your sqlMapConfig there is in settings section the property useStatementNamespaces="true"?
          For example:

          Code:
          <?xml version="1.0" encoding="UTF-8" ?>
          <!DOCTYPE sqlMapConfig PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
          <sqlMapConfig>
          	<settings
          		useStatementNamespaces="true"
          		.....
          	/>
          	<sqlMap resource="BillingCustomer.xml"/>	
          	<sqlMap resource="BillingPriceList.xml"/>	
          </sqlMapConfig>
          Then, in your BillingCustomer.xml and BillingPriceList.xml, you must specify the namespace, like this:

          Code:
          <?xml version="1.0" encoding="UTF-8" standalone="no"?>
          <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">
          <sqlMap namespace="BillingCustomer">
              <select id="getById">
                  ......
              </select>
          </sqlMap>
          
          
          
          <?xml version="1.0" encoding="UTF-8" standalone="no"?>
          <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">
          <sqlMap namespace="BillingPriceList">
              <select id="getById">
                  ......
              </select>
          </sqlMap>
          then you can refer select with namespaceGiven.nameofselect.

          Comment


          • #6
            Hi m_auro1,

            It is already true in my case....

            any other idea...

            Comment


            • #7
              try this
              Code:
                  <resultMap id="billingInvoiceLineResult" class="BillingInvoiceLine">
                   <result property="billingPriceList"   column="PRICE_LIST_ID"    columnIndex="1"/>
                   <result property="billingCustomer"     column="CUSTOMER_ID"    columnIndex="2" /> 
              
                  </resultMap>
              the columnIndex is the
              price_list_id -> column1
              customer_id -> column2
              Code:
              SELECT price_list_id,COUNT(*) as customer_id
                      FROM bill_invoice_line
                      GROUP BY price_list_id
              Last edited by JoseBovet; May 15th, 2009, 09:16 AM.

              Comment

              Working...
              X