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 Chris Bowman <ch...@phoros.co.uk> on 2006/04/05 14:54:06 UTC
Dynamic Select problem
Hi All,
I have a problem with a dynamic select, and having searched the archive
I have found no answer.
The sutuation:
I have a database where customers can add user defined fields to each
table, so any data access framework I use must be able to dynamically
create select statements to handle these unknown fields (the framework
must work across different customer databases with different user
defined fields).
All user defined fields begin with 'U_', so I initially do a select to
get a List of these fields (the table is called Customer)
results = sqlMap.queryForList("mapper.getCustomerUserFields", null);
This worls fine, and I have a List of user defined Customer fields. Next
stage is to get the actual dynamic select of the Customer table done. I
use a parameter class which contains the List and a Customer ID, defined
as follows:
<parameterMap id="custParamMap"
class="com.aoi.data.params.CustomerParam">
<parameter property="list"/>
<parameter property="id"/>
</parameterMap>
I then use the param class to search for specific customers (and their
dynamic field values):
<statement id="getPageFields2" parameterMap="custParamMap"
resultClass="java.util.HashMap">
SELECT
Customer.Serial_Num
<iterate property="list">
,#list[]#
</iterate>
FROM
Customer
where Customer.id = #id#
</statement>
I turned debug on via Log4j, and got this output:
DEBUG 04-05 13:37:55 {pstm-100004} PreparedStatement: SELECT
Customer.Serial_Num ,? ,? FROM Customer where customer.id = ?
(Log4jImpl.java:22)
DEBUG 04-05 13:37:55 {pstm-100004} Parameters: [U_Destination, U_Resort,
1] (Log4jImpl.java:22)
DEBUG 04-05 13:37:55 {pstm-100004} Types: [java.lang.String,
java.lang.String, java.lang.Integer] (Log4jImpl.java:22)
DEBUG 04-05 13:37:55 {rset-100005} ResultSet (Log4jImpl.java:22)
DEBUG 04-05 13:37:55 {rset-100005} Header: [Serial_Num, expr2, expr3]
(Log4jImpl.java:22)
DEBUG 04-05 13:37:55 {rset-100005} Result: [1, U_Destination, U_Resort]
(Log4jImpl.java:22)
As you can see, the dynamic select is simply treating the List values as
fixed values rather than column names, and returning them as is. A
toString on the result map gives:
{expr2=U_Destination, expr3=U_Resort, Serial_Num=1}
Is there anything I am missing here? I seem to be doing this correctly,
but I am relatively new to iBatis.
Regards,
Chris Bowman
Re: Dynamic Select problem
Posted by Chris Bowman <ch...@phoros.co.uk>.
Larry,
that fixed it, thankyou.
Sometimes computers frustrate me more than I can possibly say!
Cheers,
Chris
Larry Meadors wrote:
> Try this:
>
> <iterate property="list">
> ,$list[]$
> </iterate>
>
> Larry
>
>
> On 4/5/06, Chris Bowman <ch...@phoros.co.uk> wrote:
>
>> Hi All,
>>
>> I have a problem with a dynamic select, and having searched the archive
>> I have found no answer.
>>
>> The sutuation:
>>
>> I have a database where customers can add user defined fields to each
>> table, so any data access framework I use must be able to dynamically
>> create select statements to handle these unknown fields (the framework
>> must work across different customer databases with different user
>> defined fields).
>>
>> All user defined fields begin with 'U_', so I initially do a select to
>> get a List of these fields (the table is called Customer)
>>
>> results = sqlMap.queryForList("mapper.getCustomerUserFields", null);
>>
>> This worls fine, and I have a List of user defined Customer fields. Next
>> stage is to get the actual dynamic select of the Customer table done. I
>> use a parameter class which contains the List and a Customer ID, defined
>> as follows:
>>
>> <parameterMap id="custParamMap"
>> class="com.aoi.data.params.CustomerParam">
>> <parameter property="list"/>
>> <parameter property="id"/>
>> </parameterMap>
>>
>> I then use the param class to search for specific customers (and their
>> dynamic field values):
>>
>> <statement id="getPageFields2" parameterMap="custParamMap"
>> resultClass="java.util.HashMap">
>> SELECT
>> Customer.Serial_Num
>> <iterate property="list">
>> ,#list[]#
>> </iterate>
>> FROM
>> Customer
>> where Customer.id = #id#
>> </statement>
>>
>> I turned debug on via Log4j, and got this output:
>>
>>
>> DEBUG 04-05 13:37:55 {pstm-100004} PreparedStatement: SELECT
>> Customer.Serial_Num ,? ,? FROM Customer where customer.id = ?
>> (Log4jImpl.java:22)
>> DEBUG 04-05 13:37:55 {pstm-100004} Parameters: [U_Destination, U_Resort,
>> 1] (Log4jImpl.java:22)
>> DEBUG 04-05 13:37:55 {pstm-100004} Types: [java.lang.String,
>> java.lang.String, java.lang.Integer] (Log4jImpl.java:22)
>> DEBUG 04-05 13:37:55 {rset-100005} ResultSet (Log4jImpl.java:22)
>> DEBUG 04-05 13:37:55 {rset-100005} Header: [Serial_Num, expr2, expr3]
>> (Log4jImpl.java:22)
>> DEBUG 04-05 13:37:55 {rset-100005} Result: [1, U_Destination, U_Resort]
>> (Log4jImpl.java:22)
>>
>> As you can see, the dynamic select is simply treating the List values as
>> fixed values rather than column names, and returning them as is. A
>> toString on the result map gives:
>>
>> {expr2=U_Destination, expr3=U_Resort, Serial_Num=1}
>>
>> Is there anything I am missing here? I seem to be doing this correctly,
>> but I am relatively new to iBatis.
>>
>> Regards,
>>
>> Chris Bowman
>>
>>
>
>
Re: Dynamic Select problem
Posted by Larry Meadors <lm...@apache.org>.
Try this:
<iterate property="list">
,$list[]$
</iterate>
Larry
On 4/5/06, Chris Bowman <ch...@phoros.co.uk> wrote:
> Hi All,
>
> I have a problem with a dynamic select, and having searched the archive
> I have found no answer.
>
> The sutuation:
>
> I have a database where customers can add user defined fields to each
> table, so any data access framework I use must be able to dynamically
> create select statements to handle these unknown fields (the framework
> must work across different customer databases with different user
> defined fields).
>
> All user defined fields begin with 'U_', so I initially do a select to
> get a List of these fields (the table is called Customer)
>
> results = sqlMap.queryForList("mapper.getCustomerUserFields", null);
>
> This worls fine, and I have a List of user defined Customer fields. Next
> stage is to get the actual dynamic select of the Customer table done. I
> use a parameter class which contains the List and a Customer ID, defined
> as follows:
>
> <parameterMap id="custParamMap"
> class="com.aoi.data.params.CustomerParam">
> <parameter property="list"/>
> <parameter property="id"/>
> </parameterMap>
>
> I then use the param class to search for specific customers (and their
> dynamic field values):
>
> <statement id="getPageFields2" parameterMap="custParamMap"
> resultClass="java.util.HashMap">
> SELECT
> Customer.Serial_Num
> <iterate property="list">
> ,#list[]#
> </iterate>
> FROM
> Customer
> where Customer.id = #id#
> </statement>
>
> I turned debug on via Log4j, and got this output:
>
>
> DEBUG 04-05 13:37:55 {pstm-100004} PreparedStatement: SELECT
> Customer.Serial_Num ,? ,? FROM Customer where customer.id = ?
> (Log4jImpl.java:22)
> DEBUG 04-05 13:37:55 {pstm-100004} Parameters: [U_Destination, U_Resort,
> 1] (Log4jImpl.java:22)
> DEBUG 04-05 13:37:55 {pstm-100004} Types: [java.lang.String,
> java.lang.String, java.lang.Integer] (Log4jImpl.java:22)
> DEBUG 04-05 13:37:55 {rset-100005} ResultSet (Log4jImpl.java:22)
> DEBUG 04-05 13:37:55 {rset-100005} Header: [Serial_Num, expr2, expr3]
> (Log4jImpl.java:22)
> DEBUG 04-05 13:37:55 {rset-100005} Result: [1, U_Destination, U_Resort]
> (Log4jImpl.java:22)
>
> As you can see, the dynamic select is simply treating the List values as
> fixed values rather than column names, and returning them as is. A
> toString on the result map gives:
>
> {expr2=U_Destination, expr3=U_Resort, Serial_Num=1}
>
> Is there anything I am missing here? I seem to be doing this correctly,
> but I am relatively new to iBatis.
>
> Regards,
>
> Chris Bowman
>