Skip site navigation (1) Skip section navigation (2)

Re: in(...) clause and PreparedStatement

From: Sergey Pariev <egy(at)tnet(dot)dp(dot)ua>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: in(...) clause and PreparedStatement
Date: 2005-06-14 10:20:34
Message-ID: 42AEAF72.4050500@tnet.dp.ua (view raw or flat)
Thread:
Lists: pgsql-jdbc
Thanks to all for the instant and detailed replies.
 
I have to say I posted simplified example of the query I need to run, in 
reality I don't know how many items will be in the IN clause. I have to 
do 3 queries (update, then insert ... select from this table and delete 
) on particular table given the set of keys which I retrieve in the 
other query, which it complex so I can't dublicate it 3 times - so I 
can't just write

SELECT * FROM mytable WHERE t_id IN (select t_id from other_table where 
...).

Currently I retrieve keys at first and concatenate them into string like 
1,2,3,... , then I issue my queries like

st.executeUpdate("update mytable set a_field = 1 where t_id IN ("+keys+")" ;

and so on. It is working as for now, but is really ugly, so I desided to 
improve things and was just wandering if there a way to use variable 
number of parameters.

Considering the thing you guys wrote I'm propably will rewrite my code 
to use fixed number of parameters - it wouldn' t be too hard since I'm 
processing it in chunks anyway. It will be only in the next version of 
my app though - don't want to fix the thing which are working :).

Thanks again, Sergey.

Csaba Nagy пишет:

>Sergey,
>
>Additionally to what Dave wrote you: if you want to use the prepared
>statement with variable number of parameters, you can sometimes use a
>query with a fixed number of parameters, and if you have more
>parameters, execute it chunk-wise, if you have less parameters then set
>the additional ones to null. This variant complicates your code
>considerably though, but the query is prepared and reusable. I would
>think it only matters if you reuse the prepared statement for a large
>number of executions.
>
>This only works if you don't have to have all the parameters processed
>in one statement, i.e. you can chunk your query and the final cumulated
>results will be the same.
>
>In any other case you will not be able to use prepared statements, i.e.
>you'll need to build your query each time. It is still advisable to do
>it via JDBC prepared statements, and not build it directly by hand,
>because that will take care for all the escaping necessary for your
>parameter values. This means you should build a query with as many ?
>signs as many parameters you have, and then set them in a loop or so.
>
>HTH,
>Csaba.
>
>
>On Mon, 2005-06-13 at 15:07, Dave Cramer wrote:
>  
>
>>Yes, because it thinks "1,2,3" is a string
>>
>>you would have to do
>>
>>IN(?,?,?)
>>
>>then
>>
>>setObject(1, 1);
>>setObject(2, 2);
>>setObject(3, 3);
>>
>>Dave
>>
>>On 13-Jun-05, at 9:08 AM, Sergey Pariev wrote:
>>
>>    
>>
>>>Hi all.
>>>   It may be stupid question, but is there way to use  
>>>PreparedStatement with queries like
>>>SELECT * FROM mytable WHERE t_id IN (1,2,3) ?
>>>   I've googled but haven't found nothing explicitly said on this  
>>>topic.
>>>   I've tried the following
>>>   PreparedStatement st = conn.prepareStatement("SELECT * FROM  
>>>mytable WHERE t_id IN ( ? )");
>>>   st.setObject(1,"1,2,3");
>>>
>>>   and get error complaining on type mismatch.
>>>
>>>   Thanks in advance, Sergey.
>>>
>>>---------------------------(end of  
>>>broadcast)---------------------------
>>>TIP 2: you can get off all lists at once with the unregister command
>>>   (send "unregister YourEmailAddressHere" to  
>>>majordomo(at)postgresql(dot)org)
>>>
>>>
>>>      
>>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 6: Have you searched our list archives?
>>
>>               http://archives.postgresql.org
>>    
>>
>
>  
>


In response to

Responses

pgsql-jdbc by date

Next:From: Sergey ParievDate: 2005-06-14 13:36:47
Subject: Re: in(...) clause and PreparedStatement
Previous:From: Csaba NagyDate: 2005-06-14 08:29:35
Subject: Re: in(...) clause and PreparedStatement

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group