Executing aggregate function in ibatis

[Khushwinder]

Hi All,

I am trying to run the following query in the ibatis :

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:

<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

[Khushwinder]

I am getting the following exception in this:

BillingCustomer.getById

not found in the sql map file.

If any body has any idea plz help me out.

[JoseBovet]

this work for my

	<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

	<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

[Khushwinder]

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?

[m_auro1]

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

<?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:

<?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.

[Khushwinder]

Hi m_auro1,

It is already true in my case....

any other idea...

[JoseBovet]

try this

    <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

SELECT price_list_id,COUNT(*) as customer_id
        FROM bill_invoice_line
        GROUP BY price_list_id