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 Ashish Kulkarni <ku...@yahoo.com> on 2006/01/11 16:08:51 UTC
get Vector as queryForList option
Hi
I have defined a query as
<select id="selectLinesFromView"
resultClass="java.lang.String">
SELECT L083LINE FROM PL1083 WHERE L083CODE = #value#
ORDER BY L083SQNO
</select>
and in my java code i do
List list = client.queryForList("selectLinesFromView",
"ABC");
Suppose if i want a Vector as output, i just cannot
parse it as it gives me classcastexception,
so what is a best way of getting Vector as output, or
do i have to iterate through list and uild my own
vector
Ashish
A$HI$H
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Re: get Vector as queryForList option
Posted by Nathan Maves <Na...@Sun.COM>.
One question... Why do you want a Vector.class object? I have not
had any reason in the 5 years to even use one.
Nathan
On Jan 11, 2006, at 8:08 AM, Ashish Kulkarni wrote:
> Hi
> I have defined a query as
> <select id="selectLinesFromView"
> resultClass="java.lang.String">
> SELECT L083LINE FROM PL1083 WHERE L083CODE = #value#
> ORDER BY L083SQNO
> </select>
>
> and in my java code i do
> List list = client.queryForList("selectLinesFromView",
> "ABC");
>
> Suppose if i want a Vector as output, i just cannot
> parse it as it gives me classcastexception,
> so what is a best way of getting Vector as output, or
> do i have to iterate through list and uild my own
> vector
>
>
> Ashish
>
>
>
> A$HI$H
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
Re: dynamic sql map
Posted by Nathan Maves <Na...@Sun.COM>.
You are asking us if your code works?
A few things I might change...
1. change your <statement> tag to a <select>
2. add a parameter class attribute to the select
3. If you are going to have different numbers of columns selected
then you need to set reMapResults = true.
This is really the lazy mans way to things. How hard is it to copy
and paste your sqlMap and create a new one with different columns and
a unique name?
Nathan
On Jan 12, 2006, at 6:05 AM, Henry Lu wrote:
> Is this sql mapping works in the IBATIS?
>
> <statement id="select_list" resultClass="domain.NameValue">
> select $list_cols$
> FROM $table_name$
> </statement>
>
> Map map = new HashMap(2);
> map.put("list_cols", "STATE_ABBRV, STATE");
> map.put("table_name", "USSTATE_DESC");
>
> return getSqlMapClientTemplate().queryForList(
> "select_list", map);
>
> -Henry
>
dynamic sql map
Posted by Henry Lu <zh...@umich.edu>.
Is this sql mapping works in the IBATIS?
<statement id="select_list" resultClass="domain.NameValue">
select $list_cols$
FROM $table_name$
</statement>
Map map = new HashMap(2);
map.put("list_cols", "STATE_ABBRV, STATE");
map.put("table_name", "USSTATE_DESC");
return getSqlMapClientTemplate().queryForList(
"select_list", map);
-Henry
Re: dynamic sql map
Posted by Larry Meadors <lm...@apache.org>.
I always try to simplify first, then add stuff until it breaks.
For example, try this:
<statement id="select_list" resultClass="domain.NameValue">
select
STATE_ABBRV as m_name,
STATE as m_value
FROM USSTATE_DESC
</statement>
Does that work?
You may also want to enable logging, and/or try something like p6spy
to see the SQL. This is a SQL error, based on the message, not
something in Spring or iBATIS.
Larry
PS: Please, please, please do not hijack other threads (like you did
this one) with new questions. Send them to user-java@ibatis.apache.org
directly.
On 1/11/06, Henry Lu <zh...@umich.edu> wrote:
> I got errors:
>
> org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [] in
> task 'SqlMapClient operation'; nested exception is
> com.ibatis.common.jdbc.exception.NestedSQLException: --- The error
> occurred in edu/umich/med/umms/coreyp/dao/ibatis_map/misc.xml. --- The
> error occurred while applying a parameter map. --- Check the
> select_list-InlineParameterMap. --- Check the statement (query failed).
> --- Cause: java.sql.SQLException: ORA-00942: table or view does not exist
> org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator
>
> with the sql:
>
> <statement id="select_list"
> resultClass="domain.NameValue">
> select $col_code$ as m_name,
> $col_descr$ as m_value
> FROM $my_table_name$
> </statement>
>
> and java code:
>
> Map map = new HashMap(4);
> map.put("col_code", "STATE_ABBRV");
> map.put("col_descr", "STATE");
> map.put("my_table_name", "USSTATE_DESC");
> map.put("where_order_str",
> "ORDER BY STATE");
>
> return getSqlMapClientTemplate().queryForList(
> "select_list", map);
>
> Is there anyone can help me out this?
>
> -Henry
>
>
>
>
> Larry Meadors wrote:
>
> >Vector myVector = new Vector(theList);
> >
> >Larry
> >
> >On 1/11/06, Ashish Kulkarni <ku...@yahoo.com> wrote:
> >
> >
> >>Hi
> >>I have defined a query as
> >><select id="selectLinesFromView"
> >>resultClass="java.lang.String">
> >>SELECT L083LINE FROM PL1083 WHERE L083CODE = #value#
> >>ORDER BY L083SQNO
> >></select>
> >>
> >>and in my java code i do
> >>List list = client.queryForList("selectLinesFromView",
> >>"ABC");
> >>
> >>Suppose if i want a Vector as output, i just cannot
> >>parse it as it gives me classcastexception,
> >>so what is a best way of getting Vector as output, or
> >>do i have to iterate through list and uild my own
> >>vector
> >>
> >>
> >>Ashish
> >>
> >>
> >>
> >>A$HI$H
> >>
> >>__________________________________________________
> >>Do You Yahoo!?
> >>Tired of spam? Yahoo! Mail has the best spam protection around
> >>http://mail.yahoo.com
> >>
> >>
> >>
> >
> >
> >
> >
>
Re: dynamic sql map
Posted by Larry Meadors <lm...@apache.org>.
You need to get your SQL logging working - search the archives to get
that working.
The problem you are having are not iBATIS issues, they are because the
SQL that you are building is broken.
Get your logging working, and the resolution will be apparent.
Larry
On 1/11/06, Henry Lu <zh...@umich.edu> wrote:
> I made changes and it worked! But now I made more changes and it broken
> again:
>
> org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [] in
> task 'SqlMapClient operation'; nested exception is
> com.ibatis.common.jdbc.exception.NestedSQLException: --- The error
> occurred while applying a parameter map. --- Check the
> select_list-InlineParameterMap. --- Check the statement (query failed).
> --- Cause: java.sql.SQLException: ORA-00933: SQL command not properly ended
>
> <statement id="select_list"
> resultClass="domain.NameValue">
> select $col_code$ as m_name,
> $col_descr$ as m_value
> FROM $table_name$
> <isNotNull prepend="and" property="where_order_str">
> $where_order_str$
> </isNotNull>
> </statement>
>
> Map map = new HashMap(4);
> map.put("col_code", "STATE_ABBRV");
> map.put("col_descr", "STATE");
> map.put("table_name", "USSTATE_DESC");
> map.put("where_order_str",
> "ORDER BY STATE");
>
> return getSqlMapClientTemplate().queryForList(
> "select_list", map);
>
> -Henry
>
> Ben Munat wrote:
>
> > If you're using commons logging (and you should be), you can set
> > logging for "java.sql" to debug (in the properties file version:
> > "log4j.logger.java.sql=DEBUG"). This will print all the generated SQL,
> > along with parameters and result sets, in your log.
> >
> > Also, it looks like the error message says you've got a "[]" in your
> > sql... that might be a clue. Hmm, and I think you need to tell ibatis
> > that the input to the statement is a map (add a 'parameterClass="map"'
> > attribute to your statement element).
> >
> > Hope that helps...
> >
> > b
> >
> > Henry Lu wrote:
> >
> >> I got errors:
> >>
> >> org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar []
> >> in task 'SqlMapClient operation'; nested exception is
> >> com.ibatis.common.jdbc.exception.NestedSQLException: --- The error
> >> occurred in edu/umich/med/umms/coreyp/dao/ibatis_map/misc.xml. ---
> >> The error occurred while applying a parameter map. --- Check the
> >> select_list-InlineParameterMap. --- Check the statement (query
> >> failed). --- Cause: java.sql.SQLException: ORA-00942: table or view
> >> does not exist
> >> org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator
> >>
> >> with the sql:
> >>
> >> <statement id="select_list"
> >> resultClass="domain.NameValue">
> >> select $col_code$ as m_name,
> >> $col_descr$ as m_value
> >> FROM $my_table_name$
> >> </statement>
> >>
> >> and java code:
> >>
> >> Map map = new HashMap(4);
> >> map.put("col_code", "STATE_ABBRV");
> >> map.put("col_descr", "STATE");
> >> map.put("my_table_name", "USSTATE_DESC");
> >> map.put("where_order_str",
> >> "ORDER BY STATE");
> >> return getSqlMapClientTemplate().queryForList(
> >> "select_list", map);
> >>
> >> Is there anyone can help me out this?
> >>
> >> -Henry
> >>
> >>
> >>
> >>
> >> Larry Meadors wrote:
> >>
> >>> Vector myVector = new Vector(theList);
> >>>
> >>> Larry
> >>>
> >>> On 1/11/06, Ashish Kulkarni <ku...@yahoo.com> wrote:
> >>>
> >>>
> >>>> Hi
> >>>> I have defined a query as
> >>>> <select id="selectLinesFromView"
> >>>> resultClass="java.lang.String">
> >>>> SELECT L083LINE FROM PL1083 WHERE L083CODE = #value#
> >>>> ORDER BY L083SQNO
> >>>> </select>
> >>>>
> >>>> and in my java code i do
> >>>> List list = client.queryForList("selectLinesFromView",
> >>>> "ABC");
> >>>>
> >>>> Suppose if i want a Vector as output, i just cannot
> >>>> parse it as it gives me classcastexception,
> >>>> so what is a best way of getting Vector as output, or
> >>>> do i have to iterate through list and uild my own
> >>>> vector
> >>>>
> >>>>
> >>>> Ashish
> >>>>
> >>>>
> >>>>
> >>>> A$HI$H
> >>>>
> >>>> __________________________________________________
> >>>> Do You Yahoo!?
> >>>> Tired of spam? Yahoo! Mail has the best spam protection around
> >>>> http://mail.yahoo.com
> >>>>
> >>>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >
> >
>
Re: dynamic sql map
Posted by Henry Lu <zh...@umich.edu>.
I made changes and it worked! But now I made more changes and it broken
again:
org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [] in
task 'SqlMapClient operation'; nested exception is
com.ibatis.common.jdbc.exception.NestedSQLException: --- The error
occurred while applying a parameter map. --- Check the
select_list-InlineParameterMap. --- Check the statement (query failed).
--- Cause: java.sql.SQLException: ORA-00933: SQL command not properly ended
<statement id="select_list"
resultClass="domain.NameValue">
select $col_code$ as m_name,
$col_descr$ as m_value
FROM $table_name$
<isNotNull prepend="and" property="where_order_str">
$where_order_str$
</isNotNull>
</statement>
Map map = new HashMap(4);
map.put("col_code", "STATE_ABBRV");
map.put("col_descr", "STATE");
map.put("table_name", "USSTATE_DESC");
map.put("where_order_str",
"ORDER BY STATE");
return getSqlMapClientTemplate().queryForList(
"select_list", map);
-Henry
Ben Munat wrote:
> If you're using commons logging (and you should be), you can set
> logging for "java.sql" to debug (in the properties file version:
> "log4j.logger.java.sql=DEBUG"). This will print all the generated SQL,
> along with parameters and result sets, in your log.
>
> Also, it looks like the error message says you've got a "[]" in your
> sql... that might be a clue. Hmm, and I think you need to tell ibatis
> that the input to the statement is a map (add a 'parameterClass="map"'
> attribute to your statement element).
>
> Hope that helps...
>
> b
>
> Henry Lu wrote:
>
>> I got errors:
>>
>> org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar []
>> in task 'SqlMapClient operation'; nested exception is
>> com.ibatis.common.jdbc.exception.NestedSQLException: --- The error
>> occurred in edu/umich/med/umms/coreyp/dao/ibatis_map/misc.xml. ---
>> The error occurred while applying a parameter map. --- Check the
>> select_list-InlineParameterMap. --- Check the statement (query
>> failed). --- Cause: java.sql.SQLException: ORA-00942: table or view
>> does not exist
>> org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator
>>
>> with the sql:
>>
>> <statement id="select_list"
>> resultClass="domain.NameValue">
>> select $col_code$ as m_name,
>> $col_descr$ as m_value
>> FROM $my_table_name$
>> </statement>
>>
>> and java code:
>>
>> Map map = new HashMap(4);
>> map.put("col_code", "STATE_ABBRV");
>> map.put("col_descr", "STATE");
>> map.put("my_table_name", "USSTATE_DESC");
>> map.put("where_order_str",
>> "ORDER BY STATE");
>> return getSqlMapClientTemplate().queryForList(
>> "select_list", map);
>>
>> Is there anyone can help me out this?
>>
>> -Henry
>>
>>
>>
>>
>> Larry Meadors wrote:
>>
>>> Vector myVector = new Vector(theList);
>>>
>>> Larry
>>>
>>> On 1/11/06, Ashish Kulkarni <ku...@yahoo.com> wrote:
>>>
>>>
>>>> Hi
>>>> I have defined a query as
>>>> <select id="selectLinesFromView"
>>>> resultClass="java.lang.String">
>>>> SELECT L083LINE FROM PL1083 WHERE L083CODE = #value#
>>>> ORDER BY L083SQNO
>>>> </select>
>>>>
>>>> and in my java code i do
>>>> List list = client.queryForList("selectLinesFromView",
>>>> "ABC");
>>>>
>>>> Suppose if i want a Vector as output, i just cannot
>>>> parse it as it gives me classcastexception,
>>>> so what is a best way of getting Vector as output, or
>>>> do i have to iterate through list and uild my own
>>>> vector
>>>>
>>>>
>>>> Ashish
>>>>
>>>>
>>>>
>>>> A$HI$H
>>>>
>>>> __________________________________________________
>>>> Do You Yahoo!?
>>>> Tired of spam? Yahoo! Mail has the best spam protection around
>>>> http://mail.yahoo.com
>>>>
>>>>
>>>
>>>
>>>
>>>
>>>
>>>
>
>
Re: grougBy issue
Posted by Tony Qian <da...@aol.com>.
Sorry, never mind logging part. the doc has it.
Tony
Tony Qian wrote on 1/12/2006, 9:23 AM:
> Gary,
>
> I fixed the misspelling. I still cannot get value for the property,
> parameters. Does IBATIS have some kind of log which I can turn it on so
> that I can see each mapping step?
>
> appreciate all you guys' help,
> Tony
>
>
> Gary Bradshaw wrote on 1/12/2006, 8:59 AM:
>
> > I'm not trying to be funny, Tony, but could the problem be that you
> > misspelled 'parameter_value' in your result map?
> >
> > <result property="value" column="paramete_value" />
> >
> > - gary
> >
> >
> > At 01:32 PM 1/11/2006, you wrote:
> > >All,
> > >
> > >I know this has been posted before. I have read a number of archived
> > >emails regarding this issue and also the example by Clinton. I just
> > >cannot make it work. The property parameters is always null. (btw, i
> > >made it work using sub-select strategy). I just want to improve the
> > >performance. Here is my mapping:
> > >
> > ><resultMap id="select-sessionCommon" class="sessionCommon"
> > >groupBy="brandId">
> > > <result property="brandId" column="brand_id" />
> > > <result property="parameters"
> > resultMap="CommonSession.elementMap" />
> > > </resultMap>
> > >
> > > <resultMap id="elementMap" class="element">
> > > <result property="value" column="paramete_value" />
> > > <result property="parameter"
> > resultMap="CommonSession.parameterMap" />
> > > </resultMap>
> > >
> > > <resultMap id="parameterMap" class="parameter" >
> > > <result property="parameterId" column="parameter_id" />
> > > <result property="name" column="parameter_name" />
> > > <result property="description" column="description" />
> > > </resultMap>
> > >
> > > <select id="selectCommonSession" parameterClass="string"
> > >resultMap="select-sessionCommon" >
> > > 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
> > >SC.brand_id=#brandId# group by SP.parameter_id,SP.parameter_name,
> > >SP.description, SC.brand_id ;
> > > </select>
> > >
> > >
> > >please let me know what i did wrong.
> > >
> > >
> > >Appreciate your help,
> > >Tony
> >
> >
> >
> >
> ------------------------------------------------------------------------
> > The information contained in this communication is intended
> > only for the use of the recipient(s) named above. It may
> > contain information that is privileged or confidential, and
> > may be protected by State and/or Federal Regulations. If
> > the reader of this message is not the intended recipient,
> > you are hereby notified that any dissemination,
> > distribution, or copying of this communication, or any of
> > its contents, is strictly prohibited. If you have received
> > this communication in error, please return it to the sender
> > immediately and delete the original message and any copy
> > of it from your computer system. If you have any questions
> > concerning this message, please contact the sender.
> >
> ------------------------------------------------------------------------
> >
>
>
Re: grougBy issue
Posted by Tony Qian <da...@aol.com>.
Gary,
I fixed the misspelling. I still cannot get value for the property,
parameters. Does IBATIS have some kind of log which I can turn it on so
that I can see each mapping step?
appreciate all you guys' help,
Tony
Gary Bradshaw wrote on 1/12/2006, 8:59 AM:
> I'm not trying to be funny, Tony, but could the problem be that you
> misspelled 'parameter_value' in your result map?
>
> <result property="value" column="paramete_value" />
>
> - gary
>
>
> At 01:32 PM 1/11/2006, you wrote:
> >All,
> >
> >I know this has been posted before. I have read a number of archived
> >emails regarding this issue and also the example by Clinton. I just
> >cannot make it work. The property parameters is always null. (btw, i
> >made it work using sub-select strategy). I just want to improve the
> >performance. Here is my mapping:
> >
> ><resultMap id="select-sessionCommon" class="sessionCommon"
> >groupBy="brandId">
> > <result property="brandId" column="brand_id" />
> > <result property="parameters"
> resultMap="CommonSession.elementMap" />
> > </resultMap>
> >
> > <resultMap id="elementMap" class="element">
> > <result property="value" column="paramete_value" />
> > <result property="parameter"
> resultMap="CommonSession.parameterMap" />
> > </resultMap>
> >
> > <resultMap id="parameterMap" class="parameter" >
> > <result property="parameterId" column="parameter_id" />
> > <result property="name" column="parameter_name" />
> > <result property="description" column="description" />
> > </resultMap>
> >
> > <select id="selectCommonSession" parameterClass="string"
> >resultMap="select-sessionCommon" >
> > 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
> >SC.brand_id=#brandId# group by SP.parameter_id,SP.parameter_name,
> >SP.description, SC.brand_id ;
> > </select>
> >
> >
> >please let me know what i did wrong.
> >
> >
> >Appreciate your help,
> >Tony
>
>
>
> ------------------------------------------------------------------------
> The information contained in this communication is intended
> only for the use of the recipient(s) named above. It may
> contain information that is privileged or confidential, and
> may be protected by State and/or Federal Regulations. If
> the reader of this message is not the intended recipient,
> you are hereby notified that any dissemination,
> distribution, or copying of this communication, or any of
> its contents, is strictly prohibited. If you have received
> this communication in error, please return it to the sender
> immediately and delete the original message and any copy
> of it from your computer system. If you have any questions
> concerning this message, please contact the sender.
> ------------------------------------------------------------------------
>
Re: grougBy issue
Posted by Gary Bradshaw <gb...@davisvision.com>.
I'm not trying to be funny, Tony, but could the problem be that you
misspelled 'parameter_value' in your result map?
<result property="value" column="paramete_value" />
- gary
At 01:32 PM 1/11/2006, you wrote:
>All,
>
>I know this has been posted before. I have read a number of archived
>emails regarding this issue and also the example by Clinton. I just
>cannot make it work. The property parameters is always null. (btw, i
>made it work using sub-select strategy). I just want to improve the
>performance. Here is my mapping:
>
><resultMap id="select-sessionCommon" class="sessionCommon"
>groupBy="brandId">
> <result property="brandId" column="brand_id" />
> <result property="parameters" resultMap="CommonSession.elementMap" />
> </resultMap>
>
> <resultMap id="elementMap" class="element">
> <result property="value" column="paramete_value" />
> <result property="parameter" resultMap="CommonSession.parameterMap" />
> </resultMap>
>
> <resultMap id="parameterMap" class="parameter" >
> <result property="parameterId" column="parameter_id" />
> <result property="name" column="parameter_name" />
> <result property="description" column="description" />
> </resultMap>
>
> <select id="selectCommonSession" parameterClass="string"
>resultMap="select-sessionCommon" >
> 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
>SC.brand_id=#brandId# group by SP.parameter_id,SP.parameter_name,
>SP.description, SC.brand_id ;
> </select>
>
>
>please let me know what i did wrong.
>
>
>Appreciate your help,
>Tony
------------------------------------------------------------------------
The information contained in this communication is intended
only for the use of the recipient(s) named above. It may
contain information that is privileged or confidential, and
may be protected by State and/or Federal Regulations. If
the reader of this message is not the intended recipient,
you are hereby notified that any dissemination,
distribution, or copying of this communication, or any of
its contents, is strictly prohibited. If you have received
this communication in error, please return it to the sender
immediately and delete the original message and any copy
of it from your computer system. If you have any questions
concerning this message, please contact the sender.
------------------------------------------------------------------------
Re: grougBy issue
Posted by Tony Qian <da...@aol.com>.
Ok, I found where the problem is. But I don't know why and how to solve it.
resultMap="CommonSession.elementMap" is never got called so that
parameters is always null.
does anyone know why?
Thanks,
tony
Tony Qian wrote on 1/11/2006, 1:32 PM:
> All,
>
> I know this has been posted before. I have read a number of archived
> emails regarding this issue and also the example by Clinton. I just
> cannot make it work. The property parameters is always null. (btw, i
> made it work using sub-select strategy). I just want to improve the
> performance. Here is my mapping:
>
> <resultMap id="select-sessionCommon" class="sessionCommon"
> groupBy="brandId">
> <result property="brandId" column="brand_id" />
> <result property="parameters"
> resultMap="CommonSession.elementMap" />
> </resultMap>
>
> <resultMap id="elementMap" class="element">
> <result property="value" column="paramete_value" />
> <result property="parameter"
> resultMap="CommonSession.parameterMap" />
> </resultMap>
>
> <resultMap id="parameterMap" class="parameter" >
> <result property="parameterId" column="parameter_id" />
> <result property="name" column="parameter_name" />
> <result property="description" column="description" />
> </resultMap>
>
> <select id="selectCommonSession" parameterClass="string"
> resultMap="select-sessionCommon" >
> 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
> SC.brand_id=#brandId# group by SP.parameter_id,SP.parameter_name,
> SP.description, SC.brand_id ;
> </select>
>
>
> please let me know what i did wrong.
>
>
> Appreciate your help,
> Tony
>
grougBy issue
Posted by Tony Qian <da...@aol.com>.
All,
I know this has been posted before. I have read a number of archived
emails regarding this issue and also the example by Clinton. I just
cannot make it work. The property parameters is always null. (btw, i
made it work using sub-select strategy). I just want to improve the
performance. Here is my mapping:
<resultMap id="select-sessionCommon" class="sessionCommon"
groupBy="brandId">
<result property="brandId" column="brand_id" />
<result property="parameters" resultMap="CommonSession.elementMap" />
</resultMap>
<resultMap id="elementMap" class="element">
<result property="value" column="paramete_value" />
<result property="parameter" resultMap="CommonSession.parameterMap" />
</resultMap>
<resultMap id="parameterMap" class="parameter" >
<result property="parameterId" column="parameter_id" />
<result property="name" column="parameter_name" />
<result property="description" column="description" />
</resultMap>
<select id="selectCommonSession" parameterClass="string"
resultMap="select-sessionCommon" >
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
SC.brand_id=#brandId# group by SP.parameter_id,SP.parameter_name,
SP.description, SC.brand_id ;
</select>
please let me know what i did wrong.
Appreciate your help,
Tony
Re: dynamic sql map
Posted by Ben Munat <be...@munat.com>.
If you're using commons logging (and you should be), you can set logging for "java.sql" to
debug (in the properties file version: "log4j.logger.java.sql=DEBUG"). This will print all
the generated SQL, along with parameters and result sets, in your log.
Also, it looks like the error message says you've got a "[]" in your sql... that might be
a clue. Hmm, and I think you need to tell ibatis that the input to the statement is a map
(add a 'parameterClass="map"' attribute to your statement element).
Hope that helps...
b
Henry Lu wrote:
> I got errors:
>
> org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [] in
> task 'SqlMapClient operation'; nested exception is
> com.ibatis.common.jdbc.exception.NestedSQLException: --- The error
> occurred in edu/umich/med/umms/coreyp/dao/ibatis_map/misc.xml. --- The
> error occurred while applying a parameter map. --- Check the
> select_list-InlineParameterMap. --- Check the statement (query failed).
> --- Cause: java.sql.SQLException: ORA-00942: table or view does not exist
> org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator
>
> with the sql:
>
> <statement id="select_list"
> resultClass="domain.NameValue">
> select $col_code$ as m_name,
> $col_descr$ as m_value
> FROM $my_table_name$
> </statement>
>
> and java code:
>
> Map map = new HashMap(4);
> map.put("col_code", "STATE_ABBRV");
> map.put("col_descr", "STATE");
> map.put("my_table_name", "USSTATE_DESC");
> map.put("where_order_str",
> "ORDER BY STATE");
> return getSqlMapClientTemplate().queryForList(
> "select_list", map);
>
> Is there anyone can help me out this?
>
> -Henry
>
>
>
>
> Larry Meadors wrote:
>
>> Vector myVector = new Vector(theList);
>>
>> Larry
>>
>> On 1/11/06, Ashish Kulkarni <ku...@yahoo.com> wrote:
>>
>>
>>> Hi
>>> I have defined a query as
>>> <select id="selectLinesFromView"
>>> resultClass="java.lang.String">
>>> SELECT L083LINE FROM PL1083 WHERE L083CODE = #value#
>>> ORDER BY L083SQNO
>>> </select>
>>>
>>> and in my java code i do
>>> List list = client.queryForList("selectLinesFromView",
>>> "ABC");
>>>
>>> Suppose if i want a Vector as output, i just cannot
>>> parse it as it gives me classcastexception,
>>> so what is a best way of getting Vector as output, or
>>> do i have to iterate through list and uild my own
>>> vector
>>>
>>>
>>> Ashish
>>>
>>>
>>>
>>> A$HI$H
>>>
>>> __________________________________________________
>>> Do You Yahoo!?
>>> Tired of spam? Yahoo! Mail has the best spam protection around
>>> http://mail.yahoo.com
>>>
>>>
>>
>>
>>
>>
>>
dynamic sql map
Posted by Henry Lu <zh...@umich.edu>.
I got errors:
org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [] in
task 'SqlMapClient operation'; nested exception is
com.ibatis.common.jdbc.exception.NestedSQLException: --- The error
occurred in edu/umich/med/umms/coreyp/dao/ibatis_map/misc.xml. --- The
error occurred while applying a parameter map. --- Check the
select_list-InlineParameterMap. --- Check the statement (query failed).
--- Cause: java.sql.SQLException: ORA-00942: table or view does not exist
org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator
with the sql:
<statement id="select_list"
resultClass="domain.NameValue">
select $col_code$ as m_name,
$col_descr$ as m_value
FROM $my_table_name$
</statement>
and java code:
Map map = new HashMap(4);
map.put("col_code", "STATE_ABBRV");
map.put("col_descr", "STATE");
map.put("my_table_name", "USSTATE_DESC");
map.put("where_order_str",
"ORDER BY STATE");
return getSqlMapClientTemplate().queryForList(
"select_list", map);
Is there anyone can help me out this?
-Henry
Larry Meadors wrote:
>Vector myVector = new Vector(theList);
>
>Larry
>
>On 1/11/06, Ashish Kulkarni <ku...@yahoo.com> wrote:
>
>
>>Hi
>>I have defined a query as
>><select id="selectLinesFromView"
>>resultClass="java.lang.String">
>>SELECT L083LINE FROM PL1083 WHERE L083CODE = #value#
>>ORDER BY L083SQNO
>></select>
>>
>>and in my java code i do
>>List list = client.queryForList("selectLinesFromView",
>>"ABC");
>>
>>Suppose if i want a Vector as output, i just cannot
>>parse it as it gives me classcastexception,
>>so what is a best way of getting Vector as output, or
>>do i have to iterate through list and uild my own
>>vector
>>
>>
>>Ashish
>>
>>
>>
>>A$HI$H
>>
>>__________________________________________________
>>Do You Yahoo!?
>>Tired of spam? Yahoo! Mail has the best spam protection around
>>http://mail.yahoo.com
>>
>>
>>
>
>
>
>
Re: get Vector as queryForList option
Posted by Larry Meadors <lm...@apache.org>.
Vector myVector = new Vector(theList);
Larry
On 1/11/06, Ashish Kulkarni <ku...@yahoo.com> wrote:
> Hi
> I have defined a query as
> <select id="selectLinesFromView"
> resultClass="java.lang.String">
> SELECT L083LINE FROM PL1083 WHERE L083CODE = #value#
> ORDER BY L083SQNO
> </select>
>
> and in my java code i do
> List list = client.queryForList("selectLinesFromView",
> "ABC");
>
> Suppose if i want a Vector as output, i just cannot
> parse it as it gives me classcastexception,
> so what is a best way of getting Vector as output, or
> do i have to iterate through list and uild my own
> vector
>
>
> Ashish
>
>
>
> A$HI$H
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>