How to retrieve metadata in ibatis

[Viidhya Kishore]

Hello all,
I am retrieving a hashmap out of ibatis.
However the map does not give me metadata information.

Is there a way to retrieve the metadata(eg.column names) through ibatis.

Thanks,
Viidhya

[Viidhya Kishore]

Well.
I found the solution.
have to use the TypeHandlerCallback.

Thanks.

[Rajesh Kumar S]

Hi Vidhya,

Please post me the way and code for retrieving metadata using iBatis. I'm battling regarding same for so many days.Thanks in Adv...:-)

I am using a Query "Select * from MyTable" . Do you know how to retrieve metadata from the same using iBatis . Result may contain multiple results (rows)

[Viidhya Kishore]

Rajesh,

The class you need is : TypeHandlerCallBack.

I created a ResultSetHandlerCallBack class which extends the above class.

package com.altum.jdbc.dao.support;


import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import com.altum.coding.data.CommonTokens;
import com.ibatis.sqlmap.client.extensions.ParameterSetter;
import com.ibatis.sqlmap.client.extensions.ResultGetter;
import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback;

public class ResultSetHandlerCallback implements TypeHandlerCallback {

	public Map<String, List> getResult(ResultGetter getter) throws SQLException {
		Map<String, List> result = new LinkedHashMap<String, List>();
		ResultSet rs = getter.getResultSet();
		ResultSetMetaData rsmd = rs.getMetaData();
		int numColumns = rsmd.getColumnCount();
		List<String> colNames = new ArrayList<String>();
		 //Get the column names; column indices start from 1
		for (int i=1; i<numColumns+1;i++) {
			colNames.add(rsmd.getColumnName(i)); 
		}
		List><Map><String,Object>> rows = new ArrayList<Map><String,Object>>();
		Map<String,Object> row;
		Object cellValue;
		// do while loop is used here since in iBatis, the resultset begins at the first record unlike jdbc where the cursor is positioned before the first record.
		do{
			row = new HashMap<String, Object>();
			for(String colName : colNames){			
				cellValue=rs.getObject(colName);
				row.put(colName, (cellValue instanceof String ? rs.getString(colName).trim() : cellValue));
			}
			if(row.size() != 0){
				rows.add(row);
			}
		}while (rs.next());
		result.put(CommonTokens.RESULT_ROWS_TOKEN,rows);
		return result;
	}

	public void setParameter(ParameterSetter arg0, Object arg1){
		//Not Used 
	}

	public Object valueOf(String arg0){
		//Not Used
		return null;
	}

}

I then have a ResultSetMap class which uses the above call back class.

package com.altum.jdbc.dao.support;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
 * This class just serves as a handle to the Map object populated through the ResultSetHandlerCallback class.
 * @author VIK
 *
 */

public class ResultSetMap {
	
	private Map<String, List>  result = new HashMap<String, List>  ();
	
	public Map<String, List> getResult() {
		return result;
	}
	public void setResult(Map<String, List> result) {
		this.result = result;
	}
	
}

This is how I use it in the dao.

public List<Map><String, Object>> getResultSetRows(String selectSqlCmd, Map reportParams){
		ResultSetMap resultSetMap = (ResultSetMap)getSqlMapClientTemplate().queryForObject(selectSqlCmd, reportParams);
		if(resultSetMap != null){
			return resultSetMap.getResult().get(CommonTokens.RESULT_ROWS_TOKEN);
		}else{
			return new ArrayList<Map><String,Object>>();
		}
	}

The ResultSetHandlerCallBack class puts the results in the form of multiple maps with colName and value as pairs. Then the row map is added to a rows list.
The ResultSetMap is needed as this class helps to get the handle for the metadata info we populated using the Handler class.

I am sure this will be enough for you.

[Viidhya Kishore]

The ibatis map :

<sqlMap namespace="jasper">

<typeAlias type="com.altum.jdbc.dao.support.ResultSetMap" alias="resultSetMap" />

<resultMap id = "ResultSetMapResult" class = "resultSetMap" > 	
	<result property = "result" typeHandler = "com.altum.jdbc.dao.support.ResultSetHandlerCallback" />
</resultMap>	
 
<select id="OB_COLLECTION_SELECT_SQL" parameterClass="map" resultMap="ResultSetMapResult">
	SELECT ob_disease ,sum(grant_dollars) as grant_dollars ,sum(contract_dollars) as contract_dollars 
       ,sum(project_dollars) as project_dollars 
    FROM  $sSessionTable$
    GROUP BY ob_disease 
    ORDER BY 1,2
</select>
</sqlMap>

[Rajesh Kumar S]

HI Vidhya,

Thanks a lot :-) You saved me from hell..

Also may i know the value of CommonTokens.RESULT_ROWS_TOKEN.

I'm not able to get Resultset as it is retrieving null eventhough results are there . please help me regarding this.

I'm just getting ResultSet from the ResultGetter
public class ResultSetHandlerCallback implements TypeHandlerCallback {

public ResultSet getResult(ResultGetter getter) throws SQLException {
ResultSet resultSet = getter.getResultSet();
System.out.println("resultSet "+resultSet ); // Here its returing null.
return resultSet;
}

@Override
public void setParameter(ParameterSetter arg0, Object arg1)
throws SQLException {
// TODO Auto-generated method stub

}

@Override
public Object valueOf(String arg0) {
// TODO Auto-generated method stub
return null;
}
}

public class ResultSetMap {


private ResultSet resultSet;
public ResultSet getResultSet() {

return resultSet;
}

public void setResultSet(ResultSet resultSet) {
this.resultSet = resultSet;
}
}


SQLMAP file :-
<typeAlias type="com.logo.customermemory.eclipse.dao.impl.ResultSetMap" alias="resultSetMap" />

<resultMap id = "resultSetMapResult" class = "resultSetMap">
<result property = "resultSet" typeHandler = "com.logo.customermemory.eclipse.dao.impl.ResultSetHandlerCallback" />
</resultMap>

<select id="defaultGroupOrderingQuery" parameterClass="groupSearchVO" resultMap="resultSetMapResult">
SELECT * FROM (
SELECT ROWNUM rnum ,CMG.* FROM (
SELECT * FROM (
<include refid="outerQuery"/>
(
<include refid="getRowNumInnerQuery"/>
AND EVENT_STATUS <![CDATA[ <= ]]> 3 ))
UNION ALL
SELECT * FROM (
<include refid="outerQuery"/>
(
<include refid="getRowNumInnerQuery"/>
AND EVENT_STATUS > 3 )
) )CMG )
</select>


Same query if i run in SQL Command ,result is 2 rows...So,please help me regarding this.

Thank you very much for your support.

[Viidhya Kishore]

CommonTokens.RESULT_ROWS_TOKEN is just a token.
You could have it as
in ResultSetHandlerCallback
Line 44 - result.put("rows",rows);

In my dao code :
line 4 - resultSetMap.getResult().get("rows");

The reason your resultSet is coming null is because of your ResultSetMap class.
You cant have this:

public ResultSet getResultSet() { 

This class HAS TO return a Map.

Please update this class to EXACTLY as I had written.

It should work then.

[Rajesh Kumar S]

Hi Vidhya,

Thank you very much for the help. But i stuck with the same. I am getting column names as cell values instead of results . Please help me regarding this. Below is my code.

public class ResultSetHandlerCallback implements TypeHandlerCallback {

	@Override
	public void setParameter(ParameterSetter arg0, Object arg1)
			throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public Object valueOf(String arg0) {
		// TODO Auto-generated method stub
		return null;
	}
	
	public Map<String, List> getResult(ResultGetter getter) throws SQLException
	{
		Map<String, List> result = new LinkedHashMap<String, List>();
		ResultSet rs = getter.getResultSet();
		ResultSetMetaData rsmd = rs.getMetaData();
		int numColumns = rsmd.getColumnCount();
		List<String> colNames = new ArrayList<String>();
		//Get the column names; column indices start from 1
		for (int i=1; i<numColumns+1;i++)
		{
			System.out.println(i+"		" + rsmd.getColumnName(i));
			colNames.add(rsmd.getColumnName(i));
		}
		List<Map<String,Object>> rows = new ArrayList<Map<String,Object>>();
		Map<String,Object> row;
		Object cellValue;
		// do while loop is used here since in iBatis, the resultset begins at the first record unlike jdbc where the cursor is positioned before the first record.
		do
		{
			row = new HashMap<String, Object>();
			for(String colName : colNames)
			{
				cellValue=rs.getObject(colName);
				System.out.println(colName+"		" +cellValue);
				row.put(colName, (cellValue instanceof String ? rs.getString(colName).trim() : cellValue));
			}
			if(row.size() != 0)
			{
				rows.add(row);
			}
		}while (rs.next());
		result.put("resultRows",rows);
		return result;
	}
}

public class ResultSetMap {
	
	private Map<String, List>  result = new HashMap<String, List>  ();
	
	public Map<String, List> getResult()
	{
		return result;
	}
	
	public void setResult(Map<String, List> result)
	{
		this.result = result;
	}
}

<typeAlias type="com.citi.customermemory.eclipse.dao.impl.ResultSetMap" alias="resultSetMap" />
	
	<resultMap id = "resultSetMapResult" class = "resultSetMap">
		<result property = "result" typeHandler = "com.citi.customermemory.eclipse.dao.impl.ResultSetHandlerCallback" />
	</resultMap>
	
	<select id="defaultGroupOrderingQuery" parameterClass="groupSearchVO" resultMap="resultSetMapResult">
		SELECT * FROM (
			SELECT ROWNUM rnum ,CMG.* FROM (
				SELECT * FROM (
					<include refid="outerQuery"/>
					(
					<include refid="getRowNumInnerQuery"/> 
					AND EVENT_STATUS <![CDATA[ <= ]]> 3 )) 
				UNION ALL 
				SELECT * FROM ( 
					<include refid="outerQuery"/> 
					(
					<include refid="getRowNumInnerQuery"/> 
					AND EVENT_STATUS > 3 ) 
				 ) )CMG )
	</select>

My Query in logs...

SELECT * FROM (    SELECT ROWNUM rnum ,CMG.* FROM (     SELECT * FROM (         SELECT            EVENT_TYPE   ,     EVENT_DESC    ,     GROUP_ID    ,    EVENT_CODE_ID    ,    ACCOUNT_NUMBER    ,    DW_CUSTOMER_ID   ,    CUST_SESS_START_DATE_TIME    ,    EXECUTIVE_DEPARTMENT    ,     REOPEN_FLAG    ,    CUSTOMER_SESSION_NUMBER    ,    CURRENT_WORKFLOW_ACTIVITY    ,    EVENT_STATUS    ,    CUST_SESS_END_DATE_TIME    ,     PRODUCT_DESC    ,     ENTR_CHL_NAME    ,     PRODUCT_CODE_ID    ,     PRODUCT_TYPE_CODE    ,     ORIGINATOR    ,     FOLLOW_UP_FLAG    ,     CASE_DETAIL_SOURCE_ID           FROM        (         SELECT ROWNUM AS RN, T.* FROM  CM_GROUPS T       WHERE                     CM_REGIONAL_ID = '10'                                 AND     COUNTRY_BUSINESS_UNIT = 'SVGCG'                              AND EVENT_STATUS  <=  3 ))      UNION ALL      SELECT * FROM (          SELECT             EVENT_TYPE    ,     EVENT_DESC    ,     GROUP_ID    ,     EVENT_CODE_ID    ,     ACCOUNT_NUMBER    ,     DW_CUSTOMER_ID    ,     CUST_SESS_START_DATE_TIME    ,     EXECUTIVE_DEPARTMENT    ,     REOPEN_FLAG    ,     CUSTOMER_SESSION_NUMBER    ,     CURRENT_WORKFLOW_ACTIVITY    ,     EVENT_STATUS    ,     CUST_SESS_END_DATE_TIME    ,     PRODUCT_DESC    ,     ENTR_CHL_NAME    ,     PRODUCT_CODE_ID    ,     PRODUCT_TYPE_CODE    ,     ORIGINATOR    ,     FOLLOW_UP_FLAG    ,     CASE_DETAIL_SOURCE_ID           FROM         (         SELECT ROWNUM AS RN, T.* FROM  CM_GROUPS T       WHERE                     CM_REGIONAL_ID = '10'                                 AND     COUNTRY_BUSINESS_UNIT = 'SVGCG'                              AND EVENT_STATUS > 3 )       ) )CMG )  

For above query , result is 2 rows

Log which i am printing in ResultSetHandlerCallBack

               [u][b]rsmd.getColumnName(i)[/b][/u]
               
1		RNUM
2		:1
3		:2
4		:3
5		:4
6		:5
7		:6
8		:7
9		:8
10		:9
11		:10
12		:11
13		:12
14		:13
15		:14
16		:15
17		:16
18		:17
19		:18
20		:19
21		:20

[u][b]colName           cellValue[/b][/u]

RNUM		1
:1		EVENT_TYPE
:2		EVENT_DESC
:3		GROUP_ID
:4		EVENT_CODE_ID
:5		ACCOUNT_NUMBER
:6		DW_CUSTOMER_ID
:7		CUST_SESS_START_DATE_TIME
:8		EXECUTIVE_DEPARTMENT
:9		REOPEN_FLAG
:10		CUSTOMER_SESSION_NUMBER
:11		CURRENT_WORKFLOW_ACTIVITY
:12		EVENT_STATUS
:13		CUST_SESS_END_DATE_TIME
:14		PRODUCT_DESC
:15		ENTR_CHL_NAME
:16		PRODUCT_CODE_ID
:17		PRODUCT_TYPE_CODE
:18		ORIGINATOR
:19		FOLLOW_UP_FLAG
:20		CASE_DETAIL_SOURCE_ID
RNUM		2
:1		EVENT_TYPE
:2		EVENT_DESC
:3		GROUP_ID
:4		EVENT_CODE_ID
:5		ACCOUNT_NUMBER
:6		DW_CUSTOMER_ID
:7		CUST_SESS_START_DATE_TIME
:8		EXECUTIVE_DEPARTMENT
:9		REOPEN_FLAG
:10		CUSTOMER_SESSION_NUMBER
:11		CURRENT_WORKFLOW_ACTIVITY
:12		EVENT_STATUS
:13		CUST_SESS_END_DATE_TIME
:14		PRODUCT_DESC
:15		ENTR_CHL_NAME
:16		PRODUCT_CODE_ID
:17		PRODUCT_TYPE_CODE
:18		ORIGINATOR
:19		FOLLOW_UP_FLAG
:20		CASE_DETAIL_SOURCE_ID

[vicente raul fernandez castneda]

Hi Viidhya.

I implemented all that you said and it works well,
But, i need to know how to get the metadata when there is no any row.

I execute the follow sentence:

select * from mytable where 1=0; //it doesnt return any row.

So, i just need to know the metadata.
With your code works well with data, but nothing happen when the sentece doesnt return anything.
Because i should know the metadata even theres no data retreiving.

Thank you so much.

Regards.