You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Da...@aol.com on 2006/01/13 02:54:46 UTC
Re: groupBy issue
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
Re: groupBy issue
Posted by Mark Bennett <ma...@gmail.com>.
Tony,
You are grouping by brand_id, but the data that comes back does not look
like an id. If I'm looking at it right, the value for the first row
brand_id is "Used by th
e client for hashing the user's password in preparation for web
authentication.". They are all unique and therefore will be no grouping.
Mark
On 1/12/06, DaqiQian2@aol.com <Da...@aol.com> wrote:
>
> 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
>