Re: groupBy issue

Thu, 12 Jan 2006 17:55:15 -0800

Mark and Tim,
 
Thanks your help.  I checked all of your suggestions. It still doesn't work.
 
Here are the info for scaled down version.
 
class info
 
public class SessionCommon
{
    private List parameters;
    private String brandId;

    public void setBrandId(String id){
        this.brandId = id;
    }
    public String getBrandId(){
        return this.brandId;
    }
    public void setParameters(List elements){
        this.parameters = parameters;
    }
    public List getParameters(){
        return this.parameters;
    }
    public SessionCommon(){
        brandId = null;
        parameters = null;
    }
}

public class SessionElement
{
    private Parameter parameter;
    private String value;

    public void setParameter(Parameter parameter){
        this.parameter = parameter;
    }
    public Parameter getParameter(){
        return this.parameter;
    }
    public void setValue(String value){
        this.value = value;
    }
    public String getValue(){
        return this.value;
    }
   
    public String toString() {
        return ReflectionToStringBuilder.toString(this);
    }
 
    public SessionElement(){
          parameter = new Parameter();
          value = null;
}

public class Parameter
{
    private String parameterName;
    private String parameterId;
    private String description;

    public void setParameterName(String name){
        this.parameterName = name;
    }
    public String getParameterName(){
        return this.parameterName;
    }
    public void setParameterId(String id){
        this.parameterId = id;
    }
    public String getParameterId(){
        return this.parameterId;
    }

    public void setDescription(String id){
        this.description = id;
    }
    public String getDescription(){
        return this.description;
    }

    public Parameter(){
        //empty
    }

    public String toString() {
        return ReflectionToStringBuilder.toString(this);
    }
mapping info

<sqlMap namespace="CommonSession">

  <typeAlias alias="sessionCommon" type="com.netscape.isp.quickstart.SessionCommon"/>
  <typeAlias alias="element" type="com.netscape.isp.quickstart.SessionElement"/>
 
  <resultMap id="elementMap" class="element">
  <result property="value" column="parameter_value"/>
  <result property="parameter.parameterId" column="parameter_id"/>
  <result property="parameter.parameterName" column="parameter_name"/>
  <result property="parameter.description" column="description"/>
  </resultMap>
 
  <resultMap id="selectSessionCommon" class="sessionCommon" groupBy="brandId">
  <result property="brandId" column="brand_id"/>
  <result property="parameters" resultMap="CommonSession.elementMap"/>
  </resultMap>
 
  <select id="selectCommonSession" parameterClass="string" resultMap="selectSessionCommon">
      SELECT distinct
       SP.parameter_id,
       SP.parameter_name,
       SP.description,
       SC.parameter_value ,
       SC.brand_id
       FROM SESSION_INFO_COMMON as SC, SESSION_PARAMETER as SP where SP.parameter_id=SC.parameter_id and brand_id=#brandId#;
  </select>
 
</sqlMap>
log info

DEBUG 01-12 17:33:37 Created connection 3735543.  (JakartaCommonsLoggingImpl.jav
a:23)
DEBUG 01-12 17:33:37 {conn-100000} Connection  (JakartaCommonsLoggingImpl.java:2
3)
DEBUG 01-12 17:33:37 {pstm-100001} PreparedStatement:     SELECT distinct      S
P.parameter_id,     SP.parameter_name,     SP.description,     SC.parameter_valu
e ,     SC.brand_id      FROM SESSION_INFO_COMMON as SC, SESSION_PARAMETER as SP
 where SP.parameter_id=SC.parameter_id and brand_id=?;      (JakartaCommonsLoggi
ngImpl.java:23)
DEBUG 01-12 17:33:37 {pstm-100001} Parameters: [2]  (JakartaCommonsLoggingImpl.j
ava:23)
DEBUG 01-12 17:33:37 {pstm-100001} Types: [java.lang.String]  (JakartaCommonsLog
gingImpl.java:23)
DEBUG 01-12 17:33:37 {rset-100002} ResultSet  (JakartaCommonsLoggingImpl.java:23
)
DEBUG 01-12 17:33:37 {rset-100002} Header: [brand_id, parameter_value, parameter
_id, parameter_name, description]  (JakartaCommonsLoggingImpl.java:23)
DEBUG 01-12 17:33:37 {rset-100002} Result: [2, 78, 3, auth_challenge, Used by th
e client for hashing the user's password in preparation for web authentication.]
  (JakartaCommonsLoggingImpl.java:23)
DEBUG 01-12 17:33:37 {rset-100002} Result: [2, http://xx.office.c/
om:9580/session/session_start.jsp, 4, auth_url, Page where the client should dir
ect the user to start the web-based authentication process.]  (JakartaCommonsLog
gingImpl.java:23)
DEBUG 01-12 17:33:37 {rset-100002} Result: [2, 78, 5, ces_heartbeat, How often t
he client should monitor the connection in minutes]  (JakartaCommonsLoggingImpl.
java:23)
DEBUG 01-12 17:33:37 Returned connection 3735543 to pool.  (JakartaCommonsLoggin
The log info marked blue told us resultMap got data required to build SessionCommon. The funny thing is that only this query didn't work. Other queries using groupBy worked properly. is it possible that this is due to mismatch of data type of brandId (dataType of  brand_id is tinyint in database)?  
 
btw, I'm using latest version of iBATIS and MySQL 5.0 
 
thanks,
Tony

Reply via email to