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 13:36:47
Message-ID: 42AEDD6F.5030501@tnet.dp.ua (view raw or flat)
Thread:
Lists: pgsql-jdbc
Thanks a lot for the insightful advice !!!
I can't understand how I haven't get such design by myself - it seems so 
clear now :). It will definitely improve my code and overall design a lot.

Thanks again and best regards, Sergey.

Csaba Nagy пишет:

>Sergey,
>
>Doing big processing in chunks is a good thing to do. It will avoid long
>running transactions, and allow you to interrupt the operation if
>needed.
>However, there might be a better way to do it then retrieving the id's
>and using "IN": use a temporary table to store the id's, and then use
>the subselect query on that table. It should be fast, cause it will only
>contain the id's you want to process. The "temporary" table could easily
>be a permanent table, and contain more than one set of processing ids,
>and a special "transactionid" field you can select on. After processing
>you can delete the unnecessary rows to keep the table small, and
>probably you want to vacuum it often.
>The big advantage is that with this solution you can use prepared
>statements, the code will be simpler, chunking is easily achieved by
>only selecting so many ids to the temp table, and it is probably also
>the fastest way you can process the data, as you don't have to move any
>data back and forth between the server and the client.
>
>HTH,
>Csaba.
>
>
>On Tue, 2005-06-14 at 12:20, Sergey Pariev wrote:
>  
>
>>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
>>>>   
>>>>
>>>>        
>>>>
>>> 
>>>
>>>      
>>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 9: the planner will ignore your desire to choose an index scan if your
>>      joining column's datatypes do not match
>>    
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>  
>


In response to

pgsql-jdbc by date

Next:From: David SiebertDate: 2005-06-14 15:07:17
Subject: Any example code of using passwords with JDBC?
Previous:From: Sergey ParievDate: 2005-06-14 10:20:34
Subject: Re: in(...) clause and PreparedStatement

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